Need Help with multiple DB query

[TiwstedNy]

I am having an issue and was wondering if anyone could help me out. I will try to explain my issue as clearly as possible. I am trying to make a data feed so that I can implement my inventory to another website automatically. I have completed the data feed but the issue I am having is adding all inventory images for each product. I could only get the main image to show for each product on the data feed.

 

This is how my database tables are set up

 

categories - cat_id, cat_name

 

pictures - key_id, veh_id, pic_name

 

inventory - veh_id, cat_id, _thumbnail - There are a lot of fields in this table but those are the main ones. This table also has all the information for the products ie.: name, price, description etc....

 

 

Below is the code from my datafeed.php

 

I want to be able to show all the images that are in table pictures for each item on one line. I tried to JOIN tables inventory and pictures but did not like the output. It listed each item on the data feed multiple times. If an item had 12 photos it would list that single item 12 times on the data feed.  Any help would be greatly appreciated.

<?php
     include 'config/config.php';
     include 'config/opendb.php';

     $myFile = "../datafeed.txt";

     if(!file_exists("$myFile"))
     {
      die("File not found");
     }
     else
     {

     $fh = fopen($myFile, 'w');

     echo "Data Feed  Has Been Updated<br /><br /><a href='/datafeed.txt' target='_blank'>View Text File</a>";
     }

     $query  = "SELECT * FROM vehicles WHERE _sold = 'STOCKED' ORDER BY _make ASC";                            
     $result = mysql_query($query) or die(mysql_error());
              
     while($row = mysql_fetch_array($result, MYSQL_ASSOC)){ 

     $current_date = date('Y-m-d');
     $due_date = date("Y-m-d", strtotime( date( "Y-m-d", strtotime( date("Y-m-d") ) ) . "+2 week" ) );

     $stringData = "|{$row['_make]}|{$row['_model']}| |{$row['_body']} |Damaged|{$row['_year']}| |{$row['_price']} ||{$row['_desc']} ||{$row['_title']} |{$row['_miles']}| |{$row['veh_id']}|$due_date|http://www.mywebsite.com/{$row['_year']}/{$row['_make']}/{$row['veh_id']}|http://www. mywebsite.com/{$row['veh_id']}{$row['_thumbnail']}| | | | | | | | | |Buy it Now \n";

     fwrite($fh, $stringData);

     } ;

     fclose($fh);
     include 'config/close.php';

?>

[ginerjm]

The join was probably doing everything correctly.  Your output scheme is probably to blame.  You need to add some code to recognize when a new record is the same (key?) as the last one, and in that case only output the image from that record.  When the new record has a new key (diff from the last key), only then do you display all the data from the new record.

[WebStyles]

I would try a JOIN with a GROUP BY... just sayin'

[TiwstedNy]

SOLVED

<?php
include 'config/config.php';
include 'config/opendb.php';

$myFile = "../datafeed.txt";

if(!file_exists("$myFile"))
{
die("File not found");
}
else
{

$fh = fopen($myFile, 'w');

echo "Data Feed Has Been Updated<br /><br /><a href='/datafeed.txt' target='_blank'>View Text File</a>";
}

$query = "SELECT C.cat_name, V. * , pictures 
          FROM vehicles AS V 
          LEFT JOIN categories AS C ON C.cat_id = V.cat_id 
          LEFT JOIN ( 
                 SELECT P.veh_id, GROUP_CONCAT( pic_name ) AS pictures 
                 FROM pictures AS P 
                 LEFT JOIN vehicles AS V ON P.veh_id = V.veh_id 
                 GROUP BY P.veh_id 
                 ) AS P ON P.veh_id = V.veh_id";

$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result, MYSQL_ASSOC)){

$picname[]  = $row['pictures'];

foreach($picname as $pic)
{
   $carPics = explode(',',$pics);
}

$current_date = date('Y-m-d');
$due_date = date("Y-m-d", strtotime( date( "Y-m-d", strtotime( date("Y-m-d") ) ) . "+2 week" ) );

$stringData = "|{$row['_make]}|{$row['_model']}| |{$row['_body']} |Damaged|{$row['_year']}| |{$row['_price']} ||{$row['_desc']} ||{$row['_title']} |{$row['_miles']}| |{$row['veh_id']}|$due_date|http://www.mywebsite.com/{$row['_year']}/{$row['_make']}/{$row['veh_id']}|http://www. mywebsite.com/$carPic[0]|http://www. mywebsite.com/$carPic[1]|http://www. mywebsite.com/$carPic[2]|http://www. mywebsite.com/$carPic[3]|http://www. mywebsite.com/$carPic[4]|http://www. mywebsite.com/$carPic[5]|http://www. mywebsite.com/$carPic[6]|http://www. mywebsite.com/$carPic[7]|http://www. mywebsite.com/$carPic[8]|http://www. mywebsite.com/$carPic[9]|Buy it Now \n";

fwrite($fh, $stringData);

} ;

fclose($fh);
include 'config/close.php';

[ginerjm]

And this works?