How to get multiple recordset rows passed to ajax?

[johnbo100]

Hi all,
So I have 2 files where one is an ajax script that calls another file that does some mysql database query.
What is the best way to echo out multiple recordset rows to ajax?.

Please see the source code below:

ajax code:

<form name="form1" method="post">

Please enter name <input type="text" id="name" name="name" />
<input type="submit" value="get data" name="submit" id="submit"/>
</form>

<div id="output"></div>



<script type="text/javascript">

$('#submit').click(function(){
    
    $.ajax({
           url:'data.php',
           data: 'firstname=' + name,
           type:'post',
           success: function(data) {

            $('#output').html(data);

           }
    });
});

data.php code

<?php
$firstname = $_post['firstname'];
$query_Recordset1 = "SELECT * FROM cust WHERE name='$firstname'";
$Recordset1 = mysql_query($query_Recordset1, $tests) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

echo $row_Recordset1;
?>

[svilla]

I have no idea what your column names are so substittute your column names for 'fld1'. You can concatenate any series of column names and HTML together to get your desired results.

<?php
$firstname = $_post['firstname'];
$query_Recordset1 = "SELECT * FROM cust WHERE name='$firstname'";
$Recordset1 = mysql_query($query_Recordset1, $tests) or die(mysql_error());
while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)) {

echo $row_Recordset1['fld1'] , '<br />';
}
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
echo '<br />Total records: ' , $totalRows_Recordset1;
?>

This will read through the entire result set and echo back whatever is in 'fld1' for each record. It will also print Total records: xxx at the end of the list

[diafol]

Bit confused here. You're returning the total number of records, but do you want to return the actual data?

If so return the data to an array and json_encode it. Then you use the json data in js (jquery) to parse out the output.

while($data = mysql_fetch_array($rs){
  $outputdata[] = $data;
}
//that gives a multidimensional array, which can then be jsonified:

echo json_encode($outputdata);

Alternatively, you can output all the data and the html directly and just pass on the whole lot:

$output= "";
while($data = mysql_fetch_array($rs){
  $output .= "<tr><td>" . $data['field1'] . "</td><td>" . $data['field2'] . "</td></tr>";
}
echo $output;

[johnbo100]

Wow! thankyou both, I love this forum!
I trawled the internet for a simple explaination for days. Should have come here first, doh!