slow PHP AJAX MySQL - database example w3schools

Question

kanoy83 0 Junior Poster in Training

11 Years Ago

hi guys,
1. i use this script from w3schools it works with real escape string. But my problem is it is slow displaying the table and i dont know how to check the problem. assuming password and table is correct.
2. can i change the "ajax_demo" value or delete?
reference: http://www.w3schools.com/php/php_ajax_database.asp

Index.php

<html>
<head>
<script>
function showUser(str)
{
if (str=="")
  {
  document.getElementById("txtHint").innerHTML="";
  return;
  }
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","getuser.php?q="+str,true);
xmlhttp.send();
}
</script>
</head>
<body>

<!-- <form enctype="multipart/form-data"> -->
    <form>            
        Category: <select name="select_cat" onChange="showUser(this.value)">
        <?php                    
            $con = mysql_connect("localhost", "root", "password") or die(mysql_error()); 
            mysql_select_db("table");

            $sql = "SELECT * FROM contacts";
            $data = mysql_query($sql,$con);

            while($category = mysql_fetch_array($data)){
                $catname = $category['firstname'];
                $valueid = $category['id'];
                echo '<option value="'.$valueid.'">'.$catname.'</option>';
            }                       
        ?>        

        </select>

        <br />

    </form>
    <div id="txtHint"><b>Data</b></div>

</body>
</html>

getuser.php

<?php
$q=$_GET["q"];

$con = mysqli_connect('localhost','root','password','table');
if (!$con)
  {
  die('Could not connect: ' . mysqli_error($con));
  }

//mysqli_select_db($con,"ajax_demo");
mysqli_select_db($con,"ajax_demo");
//$sql="SELECT * FROM user WHERE id = '".$q."'";
$sql="SELECT * FROM contacts WHERE id = '" . mysql_real_escape_string($q) ."'";

$result = mysqli_query($con,$sql);

echo "<table border='1'>
<tr>
<th>Firstname</th>
<th>Lastname</th>
<th>Age</th>
<th>Phone</th>
<th>Email</th>
</tr>";

while($row = mysqli_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['firstname'] . "</td>";
  echo "<td>" . $row['lastname'] . "</td>";
  echo "<td>" . $row['age'] . "</td>";
  echo "<td>" . $row['phone'] . "</td>";
  echo "<td>" . $row['email'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysqli_close($con);
?>

Thanks in advance!

javascript mysql php

Edited 11 Years Ago by kanoy83 because: thanking

5 Contributors
18 Replies
2K Views
1 Week Discussion Span
Latest Post 11 Years Ago Latest Post by kanoy83

Atli 182 Posting Pro

11 Years Ago

Since you're using MySQLi, you shouldn't be manually escaping things like that anyway. That technique is dying with the old MySQL API extension. (Thankfully!)

In MySQLi and PDO what you do is use prepared statements. For the proceedural version of MySQLi, that would look something like this.

$sql = "SELECT firstname, lastname, age, phone, email
        FROM contacts WHERE id = ?";

$stmt = mysqli_prepare($con, $sql);
mysqli_bind_param($stmt, 'i', $q);

if (mysqli_execute($stmt)) {
    mysqli_bind_result($stmt, $firstname, $lastname, $age, $phone, $email);

    while (mysqli_fetch($stmt)) {
        // Here the variables in the bind_result above will contain
        // the values for each of the fetched rows.
    }
}

This is completely immune to SQL Injection, because the user input is never actually a part of the query. That risk never really goes away when you manually escape input.

But my problem is it is slow displaying the table

How slow? Have you tried observing the query using browser based dev tools like Firebug? They will provide a lot of useful info on AJAX queries. And all the major browsers have them now. (Try pressing F12. Should show up. Except on Firefox, there you have to install Firebug first.)

One thing that may speed things up marginally is returning the data from PHP using JSON instead of as a full HTML table. Saves bandwidth. JavaScript is more than capable of creating the HTML table on the client side.

Atli 182 Posting Pro

11 Years Ago

There's also the .load method. It's specifically designed for this type of thing, and makes the code even shorter.

function showUser(str)
{
    $("#txtHint").load('getuser.php', {q: str});
}

broj1 356 Humble servant

11 Years Ago

There are two ID's so use a form that includes table name:

WHERE table1.id = '".$q."'

Put the where clause before the ORDER clause (and after the JOIN clauses).

And remove the comma after the table3.file_name.

Edited 11 Years Ago by broj1

Atli 182 Posting Pro

11 Years Ago

You'd put the WHERE clause after the joins. The FROM and JOIN clauses create the initial result set, and the WHERE clause then filters it. It wouldn't make sense to put the filtering in before the result set is finished being build.

Also, about your error. It tells us you aren't properly checking the result of the query before you try to use it. You should alwasy check the result of a query before you attempt to read data from it, in case the query fails.

diafol

11 Years Ago

INNER JOIN ensures that data has to be present in both tables (a related record exists). LEFT JOIN and RIGHT JOIN also exist, so that if a record exists in the first table but not in the seconds - LEFT will return the data of the first regardless. Opposite is true for RIGHT JOIN (show all second table, regarldess of first). Missing data fields return as NULL

Edited 11 Years Ago by diafol

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

veedeoo 474 Junior Poster Featured Poster · Answer 1 · 2013-07-17T23:43:03+00:00

Hi,

The mysqli instance is this

$con = mysqli_connect('localhost','root','password','table');

Then your mysql_real_escape_string should also be a mysqli method like so;

$sql="SELECT * FROM contacts WHERE id = '" . mysqli_real_escape_string($q) ."'";

ADDED LATER:

I just noticed on your index.php you are using the regular mysql connector, try using mysqli on it also. These must be change to mysqli construct..

    $con = mysql_connect("localhost", "root", "password") or die(mysql_error());
    mysql_select_db("table");
    $sql = "SELECT * FROM contacts";
    $data = mysql_query($sql,$con);
    while($category = mysql_fetch_array($data)){
    $catname = $category['firstname'];
    $valueid = $category['id'];
    echo '<option value="'.$valueid.'">'.$catname.'</option>';

If you need quick start guides in implementing mysqli, please read here ...

That should be the case in the w3scool's tutorial.

broj1 356 Humble servant Featured Poster · Answer 2 · 2013-07-18T06:48:49+00:00

If you are learning to use ajax it might be right time to look at jquery also. jquery has nice support for ajax and will save you from using xmlhttp object directly making sure you cover all browsers and have the code right.

Of course, all above about safe querying still applies.

diafol · Answer 3 · 2013-07-18T14:15:48+00:00

I second the jQuery idea:

this could be your ajax entire request after including a link to the jquery cdn:

function showUser(str)
{
    $.get('getuser.php',{q: str},function(data){ $('#txtHint').html(data); });
}

diafol · Answer 4 · 2013-07-18T16:46:27+00:00

load() method - agreed - more applicable if you just want to dump the contents into the tag. If you need to do any further processing / formatting this data via js, then I suggest the get(). The load() method also has the 'complete' function, which allows you do do stuff after ajax response is finished.

Anyway, think we're going off-piste with this :)

kanoy83 0 Junior Poster in Training · Answer 5 · 2013-07-22T02:14:15+00:00

hi guys, thanks for the reply.
i managed to remove the 'real escape string' and use mysqli, but i stumble now to MySQL JOIN. let u know the progress. cheers!

diafol · Answer 6 · 2013-07-22T07:49:20+00:00

LIst your tables' fields and then tell us which fields you want from your SQL. Here's a simple e.g.

SELECT u.username, p.prizename, a.award_date 
    FROM users AS u 
        INNER JOIN awards AS a 
            ON u.user_id = a.user_id 
        INNER JOIN prizes AS p 
            ON a.award_id = p.award_id

Joins data from three tables.

kanoy83 0 Junior Poster in Training · Answer 7 · 2013-07-22T21:11:54+00:00

WORKING 1:

$sql = "SELECT * FROM table1 WHERE id = '".$q."'";

WORKING 2: but no (WHERE id = '".$q."')
Where can i insert this code? or is there a proper code for this?

$sql = "SELECT
                table1.id,
                table1.cat_name,
                table2.file_title,
                table2.content,
                table3.filepage_id, 
                table3.file_size,       
                table3.file_name, 

            FROM table3
            JOIN table2 ON table3.filepage_id = table2.id
            JOIN table1 ON table2.category_id = table1.id       
            ORDER BY table3.file_date 
            DESC LIMIT 0 , 30  
            ";

If i put {WHERE id = '".$q."'} between FROM table3 and JOIN, some error showing this:
Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in

LINE:

while($row = mysqli_fetch_array($result))

im almost there, thanks

broj1 356 Humble servant Featured Poster · Answer 8 · 2013-07-22T22:16:24+00:00

mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in LINE:...

This means that mysqli_fetch_assoc() returned FALSE which it does on error. To find out the error you can also add this temporary debug line after line 15:

die($sql);

which will display the constructed query and stop the script. Now copy the displayed script to phpmyadmin and test it there. The above Atli's advice still applies, of course.

kanoy83 0 Junior Poster in Training · Answer 9 · 2013-07-22T22:25:52+00:00

guys this works!

1 minor problem, i checked that it shows data only with complete data in its row but if 1 row data is incomplete it is not showing that row, is there somewhere in the code that is checking this?

also,i will double check and let u know. again thanks! cheers!

kanoy83 0 Junior Poster in Training · Answer 10 · 2013-07-22T22:35:16+00:00

i checked that if no Data in table3, 1 row will be missing/not showing. is there way to correct this?
tnx

kanoy83 0 Junior Poster in Training · Answer 11 · 2013-07-26T18:50:30+00:00

LEFT JOIN works well it shows row that is incomplete and im using this style

SELECT t1.col, t3.col FROM table1 join table2 ON table1.primarykey = table2.foreignkey
                                  join table3 ON table2.primarykey = table3.foreignkey

source:
http://javarevisited.blogspot.ae/2012/11/how-to-join-three-tables-in-sql-query-mysql-sqlserver.html

but i have encounter another problem:

for rows/data that is incomplete, it doesnt show the ID of ".$row['filepage_id']."

while ($row = mysqli_fetch_assoc($result))
{
echo"<tr>
<td>".$row['id']."</td>
<td>".$row['category']."</td>
<td>".$row['file_title']."</td>
<td>".$row['content']."</td>
<td>".$row['file_type']."</td>
<td>".$row['file_size']." KB</td>
<td>".$row['file_name']."</td>
<td>".date("j F, Y",strtotime($row['file_date']))."</td>
<td>
<a href='edit_filepage.php?filepage=".$row['filepage_id']."'>Edit</a></td>
<td>
<a href='delete_filepage.php?filepage=".$row['filepage_id']."'>Delete</a>
</td>
</tr>";
$x++; }
the date is showing 1 January 1970 for incomplete rows, maybe its default?

Thanks, cheers!

Atli 182 Posting Pro · Answer 12 · 2013-07-27T05:34:05+00:00

for rows/data that is incomplete, it doesnt show the ID of ".$row['filepage_id']."

Is that filepage_id value in the row that you receive, or did the LEFT JOIN not find a value for it? If it's not, then it's of course not going to show. If you are pulling in fields from secondary tables using a LEFT JOIN, then you need to make sure those fields have values before using them. Otherwise you may be passing nulls to function calls or print commands, where actual values are expected.

the date is showing 1 January 1970 for incomplete rows, maybe its default?

Consider the following:

If your pass an invalid date string to strtotime, it'll return FALSE.
The second parameter of the date function expects a Unix timestamp measured in seconds, represented by an integer.
If you pass a boolean FALSE where an integer is expected, PHP will silently convert the boolean to an integer. In the case of FALSE, PHP will convert that to 0.
A Unix timestamp represents the number of seconds (or milliseconds, depending on the implementation) since January 1st, 1970. So by passing 0 to the date function, you are telling PHP to use that date.

Again, like I mentioned above, the solution to this problem is to test the field in question and make sure it has a value.

kanoy83 0 Junior Poster in Training · Answer 13 · 2013-07-29T07:41:32+00:00

i think am almost done with issue but as for my comparision with my ajax table with w3school:

w3school: .9 seconds
my code: 2 seconds (average 6 rows)

anyways, let u know any progress i made.

thanks guys,
cheers!