Table relationship, dont know where to start

[genista]

Hi,

I have a suppliers table that contains a lot of fields and for the sake of performance and all the other reasons I want to start stripping this table down into other tables.

I have created a table called supplierimages and have put the supplierid in the table like the supplierid that I have in the original suppliers table.

At the moment supplierimages has no data in it, so I need to create the relationship based on supplierid or something like that.

The following script pulls the image1 field from the Suppliers database, but this image1 field is now in the supplierimages table and I don't need that duplicated. The query needs to check the supplierimages table where there is an image1 relative to the supplierid which is taken from the session variable.

[code=php:0]
$id = $_SESSION['username'];
echo $id;
$query = "select username, supplierid, image1 from suppliers where username='$id'";

    $result=mysql_query($query, $link) or die("MySQL query $query failed.  Error if any: ".mysql_error());

echo $query;
    //get the first (and only) row from the result
    $row = mysql_fetch_array($result, MYSQL_ASSOC);

  $username=$row['username'];
  $image1 = $row['image1'];
[/code]

Once this has been retrieved I then need to update supplierimages with the image1 and a supplierid if it is currently null, this is how it looks currently:

[code=php:0]

$query = "UPDATE `suppliers`
              SET `image1` = '$image1' WHERE `username` = '". mysql_real_escape_string($_SESSION['username']). "'
              LIMIT 1";
  $result = mysql_query($query, $link) or die('Update failed: ' . mysql_error());
//echo $query;
//print_r($query); 
mysql_info($link) ;
    if(mysql_affected_rows($link) == 0); 

        }
}
[/code]

Please help me with this as I have no idea and once I get my head around it I can really start to break down my big suppliers table.

Thanks,

G

[Barand]

So if tables are now like this, where a supplier has many images,
[pre]
supplier            supplier_images
----------          ---------------
supplier_id  <------ supplier_id
username            image_id
                    image
[/pre]
then
[code]                   
SELECT s.username, si.image
FROM supplier AS s
INNER JOIN supplier_images AS si ON s.supplier_id = si.supplier_id
WHERE s.supplier_id = '$id'
[/code]

[genista]

Ok, I will give it a go and let you know.


Thanks,

G