Using Ajax to bring data from database

Question

What I'm looking for basically,is a way to bring data from the db, using ajax.

Each time person put the mouseover the category name, I would like the data of this category to pop inside a div.

For some reason the while loop is doing nothing.

It's the first time I'm using ajax, and also I'm not that pro at php.

function showCat(str) {
    if (str == "") {
        document.getElementById("txtHint").innerHTML = "";
        return;
    } else { 
        if (window.XMLHttpRequest) {
            // code for IE7+, Firefox, Chrome, Opera, Safari
            xmlhttp = new XMLHttpRequest();
        } else {
            // code for IE6, IE5
            xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
        }
        xmlhttp.onreadystatechange = function() {
            if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
                document.getElementById("txtHint").innerHTML = xmlhttp.responseText;
            }
        };
        xmlhttp.open("GET","getcat.php?q="+str,true);
        xmlhttp.send();
    }
}

<?php
$q = intval($_GET['q']);

$con = mysqli_connect('localhost','ipattcoi','6#uP!AR3G_','patt_db');
if (!$con) {
    die('Could not connect: ' . mysqli_error($con));
}

mysqli_select_db($con,"patt_db");
$sql="SELECT * FROM products WHERE category = '".$q."'";
$result = mysqli_query($con,$sql);

echo "<table>
<tr>
<th>Firstname</th>
<th>Lastname</th>
<th>Age</th>
<th>Hometown</th>
<th>Job</th>
</tr>";

while($row = mysqli_fetch_array($result)) 
{
    echo "<tr>";
    echo "<td>" . $row['product_id'] . "</td>";
    echo "<td>" . $row['product_name'] . "</td>";
    echo "<td>" . $row['product_pic'] . "</td>";
    echo "<td>" . $row['category'] . "</td>";
    echo "</tr>";
}
echo "</table>";
mysqli_close($con);
?>

This may not be the main issue but you have 5 columns defined above while loop whereas only 4 are being echoed inside while loop.Also have you tried to print and execute the query seperatly? — Varun, Jan 24 '16 at 10:47
Not the cause of the problem,but thanks for pointing that amigo — Boiko, Jan 24 '16 at 14:22

score 0 · Answer 1 · edited May 23 '17 at 12:30

0

Your code works, I was able to copy and run it with minimal modifications (just to re-use my existing table), so check the following:

1) Check you have data in your table, use any mysql client (command line tool, phpmyamdin, etc)

2) Call your getcat.php script directly and see if it actually returns the table (like http://my.server.name/getcat.php?q=something'), just open URL like this in the browser

3) If the above works - open your HTML page which does the ajax request, open developer tools (F12 in chrome and firefox), find the "console" where you can see the js errors. Trigger the request and check for errors.

Besides that, I hope that your code is just for some learning purpose and not for the live application / web-site. Because the way you compose the $sql string is really dangerous, see for example this.

There are also other things I would do differently in the real application:

on the client side - use jquery or other library to do ajax requests, while XMLHttpRequest is a vaild way to do requests, you may find that in real application you'll have to write more code to handle different browsers and different use cases.
on the server - read and convert data to json, send json to the client and format / insert it there. Again there are libraries to help you with that. What is not good at the moment is that you have part of your html in the php code
also for a real app I would use some framework (Yii, Laravel, etc) on the php side

edited May 23 '17 at 12:30

Community

1
1

answered Jan 24 '16 at 11:36

Borys Serebrov

15,636
2
38
54

Thats an awesome answer,thanks. The "console" says that the "getcat.php?q=" is undefined. What could this meen – Boiko Jan 24 '16 at 14:19
@Boiko I can't say, it may help if you show the full error text; the error should also point to the place where it occurs. Also [here](https://gist.github.com/serebrov/6e8fa5312f80c9d53102#file-testcat) is the test html I used. – Borys Serebrov Jan 24 '16 at 14:56
Finaly the data coming from the db as i wanted,but when it happens the menu that send the value is gone. Any idea why ? – Boiko Jan 25 '16 at 10:48
@Boiko it's complex to say without seeing the code, can it be that the menu is inside the "txtHint" element and gets replaced with the response? In such case just move the menu out of "txtHint". – Borys Serebrov Jan 25 '16 at 10:59
Actualy the "txtHint" and menu in diffrent 's – Boiko Jan 25 '16 at 11:04
@Boiko I can check if you update your question with the complete HTML example like [this](https://gist.github.com/serebrov/6e8fa5312f80c9d53102#file-testcat) – Borys Serebrov Jan 25 '16 at 11:15
I dont know how to send it as a tag - https://gist.github.com/anonymous/4d98344b76fde1ac4a2d – Boiko Jan 25 '16 at 11:25
@Boiko Not sure what is the problem, it works for me like [this](http://imgur.com/y671FM0), menu stays in place. – Borys Serebrov Jan 25 '16 at 11:44
@Boiko also check the html you send from the server, if there is some closing tag missing, the HTML can break. – Borys Serebrov Jan 25 '16 at 11:48

score 0 · Answer 2 · answered Jan 24 '16 at 11:53

I assume “txtHint” is the div under which you want to have the category details displayed via AJAX request.

Make sure your select statement retrieves values for the category. If no records exist for it, while loop does nothing.

a. Queries are case-sensitive. It might that $q supplies the category as ‘Samsung’ but in your database it is stored as ‘samsung’ and vice versa. Fix the problem by converting the comparison in sql into uppercase or lowercase. b. Check whether $q value is already present in the database. Fix the problem by adding or removing the items in category list in your webpage or database.

This has nothing to do with the problem - the table headers are not matching the columns from the database.

Using Ajax to bring data from database

2 Answers2