Unable To View All Entries In Table

Question

I wonder whether someone may be able to help me please.

Firstly my apologies. I would have liked to provide a link to this rather than printing the code below, but I need this to run off my live server tables.

I'm using the code below to generate a table of all location records pertinent to the current user.

<form name="locationsconsole" id="locationsconsole" method="post" action="locationsaction.php">
   <table width="864" cellpadding="0" cellspacing="0">
      <thead>
        <tr>
            <th width="17"></th>
            <th width="99"><div align="center">Location Name</div></th>
            <th width="287"><div align="left">Location Address</div></th>
            <th width="88"><div align="center">No. Of Finds Made </div></th>
            <th width="86"></th>
            <th width="72"></th>
            <th width="84"></th>
        </tr>
      </thead>
      <tbody>
 <?php

    $query = "SELECT l.locationid, f.locationid, l.locationname, l.userid, l.returnedaddress, count(f.locationid) as totalfinds FROM detectinglocations as l left join finds as f on l.locationid=f.locationid WHERE l.userid='$idnum' ORDER BY l.locationname";  
    $result = mysql_query($query) or die('error');

    if (mysql_num_rows($result) == 0)

    echo"<tr>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
         </tr>
         <tr>
            <td>&nbsp;</td>
            <td colspan='6'><div align='center'><strong>There are no records set up</strong></div></td>
            <td>&nbsp;</td>
         </tr>
         <tr>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
            <td>&nbsp;</td>
         </tr>";

    else    

    {

    while($obj=mysql_fetch_object($result))
    {
            ?>

    <tr>
        <td><input type="hidden" name="lid" value="<?php echo $obj->locationid;?>"/></td>
        <td><div align="center"><?php echo $obj->locationname;?></div></td>
        <td><div align="left"><?php echo $obj->returnedaddress;?></div></td>
        <td><div align="center"><?php echo $obj->totalfinds;?></div></td>
        <td><div align="center"><input name="type" type="submit" value="View Details"/></div></td>
        <td><div align="center"><input name="type" type="submit" value="Add Finds"/></div></td>
        <td><div align="center"><input name="type" type="submit" value="Add Images"/></div></td>
      <td width="129"><div align="left"><input name="type" type="submit" value="View Location Finds"/></div></td>
    </tr>
<?php
    }   
    }           
?>
</tbody>
</table>
</form>

Although the query is retrieving the right information and the buttons on the row work, the problem I'm having is that although there should 3 records shown in the list, only the first is shown.

I'm the first to admit that I'm certainly no expert when it comes to PHP, but I've been working on this for days and written the script many, many times, but I just can't seem to find a solution.

I just wondered whether someone could possibly look at this please and let me know where I'm going wrong.

Are you certain the query is returning all three row? Have you tried running it in PHPMyAdmin or similar to confirm? — Dan Smith, Jul 03 '12 at 13:43
Hi @Bulk, thank you for taking the time to reply to my post. For confirmation I've checked that the query works by running it through the browser. Kind regards — IRHM, Jul 03 '12 at 13:47
I agree, why don't you log/print out how many rows are being returned, you already check to see if they are 0, just print out how many rows there are to be sure there are 3. Programming throws up the unexpected so I always try not to assume. Hope that helps — Shawn Vader, Jul 03 '12 at 13:48
Hi @ShawnVader, many thanks for this. I'm not overly confident with PHP,but I'll give this a try. Kind regards — IRHM, Jul 03 '12 at 13:52
Hi, you are both correct, there is a problem with my query. I'm not sure how to fix it, but thank you for teaching not to `assume`. Kind regards — IRHM, Jul 03 '12 at 14:16
Hi both, I've managed to get the query to work. Thank you very much for your help. Kind regards — IRHM, Jul 03 '12 at 14:26
PHP's `mysql_*` functions are [deprecated](http://www.php.net/manual/en/faq.databases.php#faq.databases.mysql.deprecated). The [suggested alternatives](http://www.php.net/manual/en/mysqlinfo.api.choosing.php) also happen to be [easier to use safely](http://stackoverflow.com/a/60496/132382). — pilcrow, Jul 03 '12 at 15:39

score 0 · Accepted Answer · edited Jul 11 '18 at 10:57

0

As pointed out by both @Bulk and @ShawnVaser there was a problem with my query. This is the solution:

Query

$query = "SELECT  l.*, COUNT(f.locationid) as totalfinds FROM detectinglocations l LEFT JOIN finds f ON f.locationid = l.locationid WHERE l.userid = '28' GROUP BY l.locationname"; 
$result = mysql_query($query);

edited Jul 11 '18 at 10:57

Peter

777
2
13
34

answered Jul 03 '12 at 14:27

IRHM

1,326
11
77
130

Unable To View All Entries In Table

1 Answers1