Chathurika Chathurika - 6 months ago 9
SQL Question

Display the results of mysql_fetch_assoc in a table

I'm using the function

mysql_fetch_assoc
to return the data in database, but I don't know how to print its result in a table.
Here is the code that does the process.

$number_of_date= "SELECT SUM(number_of_date) AS number_of_date FROM emp_leaves WHERE emp_id='$userID' AND leave_category='Annual' AND apply_year='$year'";
$res_qry = mysql_query($number_of_date) or die ('Invalid query :: <br/>'.$number_of_date.' <br/>'.mysql_error());

$rowqry = mysql_fetch_assoc($res_qry);
$number_date = $rowqry ['number_of_date'];
<tr>
<?php while($rowqry = mysql_fetch_assoc($res_qry)) { ?>
<td><?php echo $rowqry ['number_of_date']?></td>
<?php } } ?>
</tr>





It does not work. What is the correct way to display this result ?

Answer

By using mysql_fetch_assoc() twice, you assign the first row of result to $rowqry already. In your query, you will only have a single row of result because of selecting the SUM() of all the results, so that leaves no more results in the while loop().

You can just remove your while loop() because it is unnecessary as you will only get a single row.

Also, the fact that your provided code, an HTML entity is inside a PHP. Use echo:

$number_of_date= "SELECT SUM(number_of_date) AS number_of_date FROM emp_leaves WHERE  emp_id='$userID' AND leave_category='Annual' AND apply_year='$year'";
$res_qry = mysql_query($number_of_date) or die ('Invalid query :: <br/>'.$number_of_date.' <br/>'.mysql_error());

$rowqry = mysql_fetch_assoc($res_qry);
$number_date = $rowqry['number_of_date'];

echo '<tr>
          <td>'.$number_date.'</td>
      </tr>';

Note: Use mysqli_* extension instead of deprecated mysql_*.

Comments