Using ajax to list records from mysql database

Question

I'm having trouble producing the right output from my code, which dynamically list records from mysql db. This is the form:

<form action="lister.php" method="get" id="info" name="weboptions" class="lesser">
   <h2>List Products</h2>
    <input type="radio" name="xo" value="n" class="styled">Name<br/>
    <input type="radio" name="xo" value="c" class="styled">Category<br/>
    <input type="radio" name="xo" value="d" class="styled">Description
      <div id="dta-wrap" class="slider">
       <input type="text" id="neym" name="neym" onKeyUp="bypname(this.value)"   class="DEPENDS ON xo BEING n AND CONFLICTS WITH XO BEING d OR XO BEING c"/></span>
      </div><!--/#dta-wrap-->
   <div id="dta2-wrap" class="slider">
      <input type="text" id="c" name="c" onKeyUp="bycat(this.value)"  class="DEPENDS ON xo BEING c AND CONFLICTS WITH XO BEING d OR XO BEING n"/></span>
   </div><!--/#dta-wrap-->

    <div id="dta3-wrap" class="slider">
      <input type="text" id="dta3" name="dta3" onKeyUp="bydesc(this.value)"  class="DEPENDS ON xo BEING d AND CONFLICTS WITH XO BEING n OR XO BEING c"/></span>
   </div>
<a href="adminpage.php"><input type="button" id="btn" name="back" value="back"></a>
</form>
<div id="resultcat"></div>

And this is the javascript file which calls the php file where the query is located:

function bydesc(str)
{
if (str=="")
  {
  document.getElementById("resultcat").innerHTML="";
  return;
  }  
if (window.XMLHttpRequest)
  {
  xmlhttp=new XMLHttpRequest();
  }
else
  {
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("resultcat").innerHTML=xmlhttp.responseText;
    }
  }
xmlhttp.open("GET","listerdesc.php?dta3="+str,true);
xmlhttp.send();
}

function bypname(str)
{
if (str=="")
  {
  document.getElementById("resultcat").innerHTML="";
  return;
  }  
if (window.XMLHttpRequest)
  {
  xmlhttp=new XMLHttpRequest();
  }
else
  {
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("resultcat").innerHTML=xmlhttp.responseText;
    }
  }
xmlhttp.open("GET","listerpname.php?dta="+str,true);
xmlhttp.send();
}

function bycat(str)
{
if (str=="")
  {
  document.getElementById("resultcat").innerHTML="";
  return;
  }  
if (window.XMLHttpRequest)
  {
  xmlhttp=new XMLHttpRequest();
  }
else
  {
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("resultcat").innerHTML=xmlhttp.responseText;
    }
  }
xmlhttp.open("GET","listercat.php?dta2="+str,true);
xmlhttp.send();
}

And this is where the query is(listerpname.php):

<?php 
if(!empty($_GET['dta'])){
$pname=$_GET['dta'];  
$result1=query_database("SELECT * FROM prod_table WHERE PRODUCT LIKE'%$pname%'", "onstor", $link);
?>

<?php
if(mysql_fetch_assoc($result1)==0){
    echo "<center><h3 id='wyt'>Your query produced no results..</h3></center>";
}else{
?>          

<center>        
<table border="1">
<thead>
    <tr>
    <th>PID</th>
    <th>PRODUCT</th>
    <th>CATEGORY</th>
    <th>DESCRIPTION</th>

    <th>QTY_ON_HAND</th>
    <th>REORDER_QTY</th>
 <th>DEALER PRICE</th>
    <th>SELL PRICE</th>
    </tr>
</thead>

<?php
while($row=mysql_fetch_assoc($result1)) { 

?>
    <tbody>
            <tr>
        <td><a href="delprod.php?prodid=<?php echo $row['PID']; ?>"><?php echo $row['PID']; ?></td>
        <td><a href="updateprod.php?prodname=<?php echo $row['PRODUCT']; ?>" class="plain"><?php echo $row['PRODUCT']; ?></a></td>

        <td><?php echo $row['CATEGORY']; ?></td>
        <td><?php echo $row['P_DESC']; ?></td>

         <td><?php echo $row['QTYHAND']; ?></td>
         <td><?php echo $row['REORDER_LVL']; ?></td>
      <td><?php echo $row['B_PRICE']; ?></td>
         <td><?php echo $row['S_PRICE']; ?></td>
            </tr>
        </tbody>

<?php } ?>
<?php } ?>
<?php } ?>

The problem is that it outputs only one record if I type in a 3-letter word('Tos'). It should output 2 records. Because there are 2 records beginning with the word 'tos' in my database. It only outputs the most recent record that I have added. When I do:

SELECT * FROM prod_table WHERE PRODUCT LIKE'$pname%'

It worsens the case. Please help.

Is that how your SELECT query really is called (with no space between LIKE and the call to $pname)? Or is that a typo when pasting the question? — Anthony Forloney
– Anthony Forloney, Commented Nov 7, 2010 at 3:03

ryanlahue · Accepted Answer · 2010-11-07 03:15:51Z

4

It looks like the problem is that when you check for results in the following line, you are moving the cursor by 1 and are discarding the first record in the resultset:

if(mysql_fetch_assoc($result1)==0){

You probably want to use the mysql_num_rows function instead, to avoid losing the first record in the resultset.

if(mysql_num_rows($result1)==0){

answered Nov 7, 2010 at 3:15

ryanlahue

1,1511 gold badge6 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

littlegreen Over a year ago

I was just about to make the same comment, but you already had it :) Small addition: you could also fetch the data into $row first, and if that contains an error, then you stop. If not, you enter the while loop with $row already filled and you refill $row at the end of the loop. Saves you a call to mysql_num_rows(). Ah well.

Collectives™ on Stack Overflow

Using ajax to list records from mysql database

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related