Page 1 of 1

Multiple copies of the same field name overwrite each other

Posted: Sat May 22, 2004 10:39 am
by DuFF
Wow, haven't been around here in a loooong time but this one has me stumped. I just recently created my first real relational database but I'm having a problem retrieving all the images stored in one of the tables.

To explain this I will use an example of the problem I am having. I have a table called images. Here is the setup:

id (unique id)
fileid (this relates it to a certain file)
largeimg (URL of larger image)
smallimg (URL of thumbnail image)

Now, a file can have 0, 1 or even multiple images associated with it. The problem is that if a file has 2 images associated with it:

Code: Select all

id   	  fileid   	  largeimg   	                       smallimg
1 	     1 	         http://www.example.com/image1.jpg 	http://www.example.com/thumbnail1.jpg
2 	     1 	         http://www.example.com/image2.jpg 	http://www.example.com/thumbnail2.jpg

(notice fileid is the same for both)
but when I query the database it only returns an array like this:

Code: Select all

Array
(
    їlargeimg] => http://www.example.com/image2.jpg
    їsmallimg] => http://www.example.com/thumbnail2.jpg
)
instead of returning both! How can I get it so its more like:

Code: Select all

Array
(
    їlargeimg] => http://www.example.com/image1.jpg
    їsmallimg] => http://www.example.com/thumbnail1.jpg
    їlargeimg2] => http://www.example.com/image2.jpg
    їsmallimg2] => http://www.example.com/thumbnail2.jpg
)
Here is a sample query I am using:

Code: Select all

$sql = "SELECT
                    f.*, img.*
            FROM
                    files as f,
                    images as img
            WHERE
                    f.id           = 1
                    AND img.fileid = 1
                    ";
If it's too confusing, please post and i'll try to explain it more clearly. Thank you in advance for any help on this!

Posted: Sat May 22, 2004 11:08 am
by kettle_drum

Code: Select all

$sql = "SELECT * FROM blah";
$result = mysql_query($sql);
while($row = mysql_fetch_assoc($result)){
   echo $row['database_field_name'];
}

Posted: Sat May 22, 2004 12:23 pm
by DuFF
The problem kettledrum is that I am returning multiple copies of the same database field name, 'largeimg' and 'smallimg', so they overwrite each other in the mysql array. This results in only 1 copy of 'largeimg' and 'smallimg' instead of 2 or 3 or whatever.

My question is, how can I get it so that multiple copies of the same field name don't overwrite each other.

Posted: Sat May 22, 2004 12:40 pm
by Nay
Hey DuFF, long time no see. How's Hilary?

Anyway, I think I know what you want. I wrote this rough one out, I hope it'll give you an idea.

Code: Select all

<?php

$fileid = 1;

$link = mysql_connect('localhost', 'nay', '') or die(mysql_error());
$db = mysql_select_db('test', $link) or die(mysql_error());

$query = "SELECT id, fileid, smallimg, largeimg FROM images WHERE fileid = $fileid";
$result = mysql_query($query, $link) or die(mysql_error());

$all = array();
$counter = 1;

while($rows = mysql_fetch_array($result)) {
   if ($counter == 1) {
      $all['largeimg'] = $rows['largeimg'];
      $all['smallimg'] = $rows['smallimg'];
   } else {
      $largeimg = 'largeimg' . $counter;
      $smallimg = 'smallimg' . $counter;

      $all[$largeimg] = $rows['largeimg'];
      $all[$smallimg] = $rows['smallimg'];
   }
$counter++;
}

print_r($all);

?>
I prints out:
-Nay

Posted: Sat May 22, 2004 1:11 pm
by DuFF
Alright, I got it all figured out. Sorry I dismissed your idea without even trying Kettledrum. It didn't exactly work for what I was trying but when I broke the image query off from my other query it worked fine. Thanks a lot guys.

:D

BTW, the problem was that I did the query like this:

Code: Select all

<?php
$sql = "SELECT . . .";
$rows = mysql_fetch_assoc($result);
echo $rows['smallimg'];
?>
instead of looping through it. I didn't need to loop through the rest because I was only returning 1 set of data.