[RESOLVED] database to array...

mOleCuleZz

hello all... I am not sure about the syntax of this.. so I figured I'd ask here 🙂

I have an sql query that gives me a table of 2 columns and numerous rows.
like so...
vill_id photo_name

1 blabla.jpg
1 bla1.jpg
1 blabla2.jpg

how can I select the 2nd column first row... 2nd row etc. ??

$sqlphoto = 'SELECT * FROM vill WHERE vill_id=1';
$photo = mysql_query($sqlphoto);
$photo = mysql_fetch_assoc($photo);

echo '<img src="/photos/'.$photo['photo_name'][0].'" />
<img src="/photos/'.$photo['photo_name'][1].'" />';

I tried this.. but it's not working.. can anyone help me out here ?

mOleCuleZz

I tried something different.... I selected just that one column.
But it's still not working like this. I tried to do echo $photo[0]
but it doesn't output anything.

$sqlphoto = 'SELECT photo_name FROM vill WHERE vill_id=1';
$photo = mysql_query($sqlphoto);
$photo = mysql_fetch_assoc($photo);

echo '<img src="/photos/'.$photo[0].'" />
<img src="/photos/'.$photo[1].'" />';

bradgrafelman

First of all, you're storing the output of [man]mysql_fetch_assoc/man into $photo, but you later try to access $sqlphoto as if it held the data. Still, even after correcting this, mysql_fetch_assoc() only retrieves one row at a time, so you basically need to do one of two things:

Loop through the result set (commonly done with a [man]while/man statement) and output the HTML as you go.

Use a while() loop, but instead of outputting anything, build an array. Example:

$photos = array();
while($data = mysql_fetch_assoc($photo))
    $photos[] = $data['photo_name'];

Also, note that if you're only using the photo_name column, you should simply SELECT that column. I always avoid using SELECT *, since it's generally wasting resources plus it isn't scalable.

EDIT: I was typing this post as you posted again, so a few points I made can be ignored! :p

mOleCuleZz

$sqlphoto = 'SELECT photo_name FROM vill WHERE vill_id=1;';
$photos = mysql_query($sqlphoto);
$photos = mysql_fetch_assoc($photos);

$a_photos = array();
while($data = mysql_fetch_assoc($photos))
    	$a_photos[] = $data['photo_name'];

I tried this but it gives me an error:
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result on line --> while($data = mysql_fetch_assoc($photos))

mOleCuleZz

It's working now
Thanks
I removed the line:
$photos = mysql_fetch_assoc($photos);