How do I search for row in database using AJAX and PHP?

Post reply
0 favourites
  • 13 posts
From the Asset Store
Using the GooglePlay Games native plugin
$2.49 USD
With this template you will learn how to use the GooglePlay Games native plugin
iagolira's avatar
iagolira

  • I have a database that is accessed using the following php code: 

    // Connect to server and select database.
$link = mysqli_connect("$host", "$username", "$password", "$db_name");

// Retrieve data from database 
$sql="SELECT * FROM drawings ORDER BY name";
$result=mysqli_query($link,$sql);

// Start looping rows in mysql database.
while($rows=mysqli_fetch_array($result)){
echo $rows['name'] . "#" . $rows['artist'] . "|" . $rows['score'] . "|";

// close while loop 
}

// close MySQL connection 
mysqli_close($link);
?>

    I'm currently using an AJAX GET command such as: "https://address/phpname.php" to grab the data.

    This was fine at first, but the more data I have, the longer it's taking to download it all.

    Does anybody know what changes I need to make to my AJAX GET and the PHP file to grab a specific row, instead of the entire database?

    Say for example I want to grab the row that is titled "Banana" or "John" or "Penguin". What do I need to change the AJAX GET command to so that it states what it's looking for? As well as the changes required to make it work with the php.

    Sorry if this isn't really the right place for such a question, but I've been searching all over the internet, and can't really see how to tie any of their suggestions into Construct 2.

  • https://www.w3schools.com/sql/sql_select.asp

  • Thank you for the message, but that's pretty much what I already have. My issue appears to be the Construct 2 side of things. Where/How do I tell Construct to tell the Database what word to search for?

    From that example you provided, how do I tell Construct to only download the row associated with the CustomerName Antonio Moreno Taquería?

    AJAX - Request - "https://address/phpname.php?CustomerName=Antonio Moreno Taquería"

    ?

  • Thank you for the message, but that's pretty much what I already have. My issue appears to be the Construct 2 side of things. Where/How do I tell Construct to tell the Database what word to search for?

    From that example you provided, how do I tell Construct to only download the row associated with the CustomerName Antonio Moreno Taquería?

    AJAX - Request - "https://address/phpname.php?CustomerName=Antonio Moreno Taquería"

    ?

    If I’m reading the following correctly, I’d imagine you append ?q=“3” to the end of your get request to complete your example above

    w3schools.com/php/php_ajax_database.asp

  • If I’m reading the following correctly, I’d imagine you append ?q=“3” to the end of your get request to complete your example above

    https://www.w3schools.com/php/php_ajax_database.asp

    Hmmm, I see... So it's not quite that simple, as that's basically searching for the ID. So I need to add a GET command to my php, then have the q command linked to something in the AJAX GET.

  • Try Construct 3

    Develop games in your browser. Powerful, performant & highly capable.

    Try Now Construct 3 users don't see these ads

  • Is it a publicly hosted dB? If you post the url we could have a play around - this definitely seems like a solvable problem

  • Is it a publicly hosted dB? If you post the url we could have a play around - this definitely seems like a solvable problem

    Yeah, I was thinking about that, but unfortunately it isn't :(

    I've altered the php file so that it reads: 

    $q = intval($_GET['q']);
$link = mysqli_connect("$host", "$username", "$password", "$db_name");

// Retrieve data from database 
$sql="SELECT * FROM drawings WHERE name = '".$q."'";
//DESC LIMIT 10";
$result=mysqli_query($link,$sql);

// Start looping rows in mysql database.
while($rows=mysqli_fetch_array($result)){
echo $rows['name'] . "#" . $rows['artist'] . "|" . $rows['score'] . "|";

    Along with changing the end of my AJAX request so that it has ".php?q=Pickaxe" on the end (as that's one of the words I want to grab.

    Now, instead of grabbing the database, it's grabbing a comma (,) which it's not done before :D

    Obviously that's not what I was hoping for... but a different result is at least interesting.

  • Wait... intval is referring to integer variables. Might that be throwing things off?

    In the example you provided, they're searching for an ID number, aren't they?

  • Yes that's correct

  • Well... I'm confused then...

  • This works I have tested it.

    first you must put the "name" you want to get like this.

    The &name& is your variable.

    Then the php is.

    $user=$_GET['name'];

    $select = "SELECT * FROM drawings WHERE name = '$user' LIMIT 0, 1";

    $query = mysql_query($select) or die(mysql_error());

    $result = mysql_fetch_assoc($query);

    //echos the array back to construct

    echo $result['name'] . "|";

    echo $result['artist'] . "|";

    echo $result['score'] . "|";

    // close MySQL connection

    mysql_close();

  • Nice one sizcoz !

    Quick question. Why do you have &"" at the end? I haven't tested it, but I was told by a friend today that if I replaced intval with spritef it would then work.

    Interesting that there are so many ways to get the same result.

    Thanks again! I'll implement it soon!

  • Nice one sizcoz !

    Quick question. Why do you have &"" at the end? I haven't tested it, but I was told by a friend today that if I replaced intval with spritef it would then work.

    Interesting that there are so many ways to get the same result.

    Thanks again! I'll implement it soon!

    Let me know if you get it working, The quotes thing is really confusing, especially if you have a lot of variables to get from a database.

    Cheers

Post reply
  • 13 posts
Return to board index
Jump to:
Active Users
There are 1 visitors browsing this topic (0 users and 1 guests)