Problem with $row

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Problem with $row

Post by kdidymus »

This is a little tricky to explain but I'll do my best.

For reasons that I won't go into right now (for fear of boring you all to death) I have written the following code which interrogates a table named "photos" and uses a filename to call a second PHP page.

Code: Select all

$query = "SELECT * FROM photos WHERE code='$scode'";
$result = mysql_query($query)
          or die ("Couldn't execute query.");
if (mysql_num_rows($result) == 0)
{echo "<tr><td class='photolink' $al colspan='2'>&nbsp;</td></tr>";}
else
{
while($row = mysql_fetch_assoc($result))
$filename = $row['filename'];
echo "<tr><td class='photolink' $al colspan='2'><a href='gallery.php?category=$catsel&scode=$scode&autostart=$filename'><img src='../graphics/services/gallery.gif' title='Click here to view our $service image gallery!' alt=''></img></a></td></tr>";}
Here's the clincher. The $scode array has multiple instances through the table.

What I WANT the code to do is find the first instance of $scode (i.e. the first row where code = $scode) and use THAT filename. As it stand the code works fine but it sets $filename as the LAST instance of $scode that matches.

Am I making any sense whatsoever?!
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Problem with $row

Post by requinix »

Code: Select all

while($row = mysql_fetch_assoc($result))
$filename = $row['filename'];
You must be one of those people who learned to use mysql_fetch_array only inside of a loop. And without understanding why.

Code: Select all

$row = mysql_fetch_assoc($result);
$filename = $row['filename'];
User avatar
DigitalMind
Forum Contributor
Posts: 152
Joined: Mon Sep 27, 2010 2:27 am
Location: Ukraine, Kharkov

Re: Problem with $row

Post by DigitalMind »

tasairis wrote:You must be one of those people who learned to use mysql_fetch_array only inside of a loop. And without understanding why.

Code: Select all

$row = mysql_fetch_assoc($result);
$filename = $row['filename'];
append LIMIT 1 to the SQL statement for using without a loop
kdidymus wrote:What I WANT the code to do is find the first instance of $scode (i.e. the first row where code = $scode) and use THAT filename. As it stand the code works fine but it sets $filename as the LAST instance of $scode that matches.
use ORDER BY to get really first instance. For example, ORDER BY id
SQL works with set of data but not with lists
kdidymus
Forum Contributor
Posts: 196
Joined: Tue May 13, 2008 3:37 am

Re: Problem with $row

Post by kdidymus »

Tasairis / Digitalmind.

Thank you both. Not only did your suggestions work to solve the problem but I now understand that I can use mysql_fetch_array OUTSIDE of a loop. It makes perfect sense now.

I used..

Code: Select all

$query = "SELECT * FROM photos WHERE code='$scode' LIMIT 1"...
followed by..

Code: Select all

...
$row = mysql_fetch_assoc($result);
$filename = $row['filename'];
...
You're absolute stars. Thank you.

KD.
User avatar
DigitalMind
Forum Contributor
Posts: 152
Joined: Mon Sep 27, 2010 2:27 am
Location: Ukraine, Kharkov

Re: Problem with $row

Post by DigitalMind »

You're welcome :)
I would use mysqli->prepare.
Try to imagine the query result if

Code: Select all

$scode = "' or code like '%'  or code = '"
without LIMIT of course. It will show all table rows
Post Reply