Help with querying MySQL database


(Chris Hardy) #1

Hi Everyone,

I’ve had a lot of success sending data to my database using Ajax but now I need to work out how to retrieve information from my database so its displayed / can be used in my hype website.

I read a few things posted on this forum and tried a few different things but I can’t seem to achieve receiving data from my data base. Here the coding I’m using:

This is what I’m using in hype. I have a text element with the ID:Data which holds a number of the relevant table in my database (in this example the number is ‘12345’). I also have a text element withe the ID:results to display the retrieved information from the database

$.ajax({

url: 'test.php',
type: 'POST',
dataType: 'JSON',
data: {
      data: $('#data').text(),
      },
 success: function (res) {
   
 $('#results').text(JSON.stringify(res));

} });

Here’s the PHP code I’m using. (database / login information changed from what is actually being used)

<?php
$hostname = "10.11.12.16";
$username = "Chris";
$password = "Password";
$databaseName = "datatables";
    
     // get values form input text and number

$data = $_POST['data'];

    
    // connect to mysql database using mysqli

$connect = mysqli_connect($hostname, $username, $password, $databaseName);
    
    // mysql query to insert data

$query = "SELECT * FROM `$data` ";

$result = mysqli_query($connect,$query);

echo "<table>
<tr>
<th>entry</th>
<th>hand</th>
<th>digit</th>
</tr>";

while($row = mysqli_fetch_array($result)) {
    echo "<tr>";
    echo "<td>" . $row['entry'] . "</td>";
    echo "<td>" . $row['hand'] . "</td>";
    echo "<td>" . $row['digit'] . "</td>";
    echo "</tr>";
}
echo "</table>";
    
mysqli_close($connect);

?>

I know I’m doing something completely wrong here but I’m not sure what. I generally learn by using examples other people have done and then alter them to achieve what I need to do. However, I just can’t seam to get this working.

Anyone got any idea?

I’ve included my test hype document which can be downloaded below:

https://www.dropbox.com/s/boem98wqy3646z2/test.hype.zip?dl=0

Thanks,

Chris


(Loves Hype) #2

To retrieve data from your database you don’t have to actually encode it into an HTML table… just try to pass it back as JSON format. Also make sure to read about sanitizing your user data you received before writing it into your database because you can easily be hacked otherwise.


(Jonathan Deutsch) #3

The first question, is where is this failing?

If you visit the .php page in your browser, do you get the expected result? If not, it is an issue with the PHP. Is there an error or debug message it gives?

If it does give the expected result, then in the JavaScript, are you getting the “res” that you expect? I’d recommend doing some console logs and see. You can also check the console for errors.

If the res is what you expect, it may be an issue in your IDs not being setup correctly, or the call happening at the wrong time.


(Chris Hardy) #4

Hi @Jonathan

The php file works to get the data and display it in a table however only if I set:

$query = "SELECT * FROM 12345 ";

12345 is the name of the database table. Even when I set this, this information isn’t being sent to the hype page.

When I run the hype page and click the button to send and retrieve the data, the console states:

Error in undefined: ReferenceError: Can’t find variable: $

Retrieving data from a database seams a lot more difficult than sending it :grimacing:


(Jonathan Deutsch) #5

It sounds like you don’t have jquery if it cannot find $.


(Chris Hardy) #6

oh my goodness! clearly having an off day! That’s resolved that error haha Still not pulling any information through to the hype page though :persevere:


(Chris Hardy) #7

So I’ve just checked the developer console in safari and I can see that it’s working just the information isn’t showing within the hype page however the button click is working and returning the correct data. Anyone got an idea how I can get this information to show within a hype element?


(Jonathan Deutsch) #8

Looking at the code above, the first question is if the success function is being called, and if the result inside res is as expected. Like I said before, I’d try doing a console log to see if that is the case.

Your line is this:

 $('#results').text(JSON.stringify(res));

The first question is if results is an ID in the document? Is $('#results') something?

I’d also guess you really want the code to be something like:

$('#results').html(res);

(but I think your code would probably output the raw result to that div okay)


(Chris Hardy) #9

results is the ID for the box element that I want the data to display in.

When I press the button on the live website, on console it shows that the data is being pulled from test.php but is just not showing in the ‘results’ box on the hype page.

So the function is working and its getting the information that’s needed. I just need it to be displayed on the live webpage :grimacing:

No warnings are showing up in the console either. Its just not displaying the information obtained from test.php within the hype element with the ID ‘results’


(Jonathan Deutsch) #10

I would first try a simple button where you run the code to insert a simple string into the results field and verify that is working as expecting. It sounds like there is a disconnect in inserting it if indeed the success function is being triggered with the correct “res” data.


(Chris Hardy) #11

my code is similar to the one found on this feed:

However, they have managed to actually get the information to display within the hype document. I can’t really see what I’m doing wrong / different here. I just get a blank box where the data should be displayed no matter what I try.


(Chris Hardy) #12

Here’s my files so far. I’ve changed them a bit and tried a few things with still no results. I think it’s something really simple that I’m missing but I just can’t work it out. Getting data from a database to display in hype is a lot harder than I expected especially as sending data to the database was pretty simple.

data recieve from database.zip (41.5 KB)

If anyone has any solutions then I’ll be really grateful.

Thanks,

Chris


(Jonathan Deutsch) #13

The zip doesn’t seem to extract for me; can you try again/a different method?


(Chris Hardy) #14

Hi @Jonathan

I’ve loaded both files onto drop box and created 2 share links:

The PHP file:

https://www.dropbox.com/s/3x7plneojc4e84r/test.php?dl=0

The HYPE file:

https://www.dropbox.com/s/6nbkb9ekclq46vy/test2.hype.zip?dl=0

Hope this works and thanks again,

Chris


(strmiska) #15

hi! i got success with this php and hype in combination.sqlHype.zip (104,1 KB)


(Chris Hardy) #16

hi @strmiska

have you created that on hype 4.0? I’m unable to open it on 3.6.7 as it’s saying it was created in a newer version.

Thanks,

Chris


(strmiska) #17

sorry, ialways forget, that documents aere autosaved in newest versions.
sqlHype.zip (104,5 KB)


(Chris Hardy) #18

Thanks for this! I’ll have a play around with it to see if I can get this working on my server and then attempt to use the code for my project :slight_smile:


(strmiska) #19

great! i use it for creating a score- list automatically after finishing a game.
it´s working really cool.


#20

@Chriswhardy

When sending an Ajax request as dataType: JSON you’ll have to return (or echo out) the response as JSON as well so in your PHP file you should be using json_encode($variable) that way your AJAX success property (function) will contain the echo’d content.

There are inherent problems in my opinion though as you’re echoing out HTML too and aren’t really dealing with arrays / objects of information so JSON may not be the best in this case. Maybe look into using the dataType: HTML with AJAX.