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!

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.

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.

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.

Member Avatar for diafol

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); });
}   

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});
}   
Member Avatar for diafol

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 :)

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!

Member Avatar for diafol

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.

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

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.

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.

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.

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!

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

Member Avatar for diafol

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

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:

  1. 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++; }

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

Thanks, cheers!

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.

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!

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.