CODEWITHSUNDEEP
phpmysqlmysqli
Chathurika
Chathurika

Reputation: 419

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 ?

Upvotes: 0

Views: 2311

Answers (5)

Logan Wayne
Logan Wayne

Reputation: 5991

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_*.

Upvotes: 1

Ponilz
Ponilz

Reputation: 119

To begin with, I suggest changing all your mysql_ functions to MySQLi functions. However, for the purpose of your question -> The problem with your code is that you have spaces after the variable of your loop. $res_qry['variable'] is not the same as $res_qry ['variable'].

$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());

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

Upvotes: 1

Denis Bhojvani
Denis Bhojvani

Reputation: 794

actually you have used 2 times mysql_fetch_assoc function if you are using inside loop then why are using outside if you are using ones out side loop means you have already fetched one record then cursor move to next record fetch. and if in case in your db there is only one record then in loop you will get nothing because you have already fetched one record outside the loop. in second case if there is more the one record is present in your database then you will get output from second record inside loop due you have use 2 times mysql_fetch_assoc. so please replace your code with below code.

$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());

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

try this may your problem will solved.

Upvotes: 1

Poiz
Poiz

Reputation: 7617

Your Construct seems to be malformed. Try a different one: 

    <?php
        $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);    // <== NOT NECESSARY SINCE YOU ARE GOING TO LOOP OVER THE RESULT
        //$number_date    = $rowqry ['number_of_date'];     // <== NOT NECESSARY SINCE YOU ARE GOING TO LOOP OVER THE RESULT

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

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

Remove the first mysql_fetch_assoc()

 $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());

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

Upvotes: 1

Related Questions