Multiple copies of the same field name overwrite each other

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
DuFF
Forum Contributor
Posts: 495
Joined: Tue Jun 24, 2003 7:49 pm
Location: USA

Multiple copies of the same field name overwrite each other

Post 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!
Last edited by DuFF on Sat May 22, 2004 12:26 pm, edited 1 time in total.
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post 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'];
}
User avatar
DuFF
Forum Contributor
Posts: 495
Joined: Tue Jun 24, 2003 7:49 pm
Location: USA

Post 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.
Nay
Forum Regular
Posts: 951
Joined: Fri Jun 20, 2003 11:03 am
Location: Brisbane, Australia

Post 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
User avatar
DuFF
Forum Contributor
Posts: 495
Joined: Tue Jun 24, 2003 7:49 pm
Location: USA

Post 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.
Post Reply