How to stop the output of duplicate database query results PHP

Question

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 vehicle images for each car. I could only get the main image to show for each car 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

vehicles - veh_id, cat_id, _make, _model, sub_model 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 vehicles and pictures but did not like the output. It listed each car on the data feed multiple times. If an car had 12 photos it would list that single car 12 times on the data feed. Any help would be greatly appreciated.

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

$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';

Try add: GROUP BY veh_id in your vehicle and pictures JOIN query. — Grant
– Grant, Commented Feb 26, 2014 at 8:10
Try GROUP_CONCAT(pic_id) with GROUP BY veh_id, joining vehicles and pictures table — avisheks
– avisheks, Commented Feb 26, 2014 at 8:14

Nouphal.M · Accepted Answer · 2014-02-26 08:20:59Z

1

Try using the following SQL:

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

The above query will return all vehicles with or without an image. And you could simply explode the pictures field with comma to get all pictures

See working demo here

answered Feb 26, 2014 at 8:20

Nouphal.M

6,3281 gold badge20 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3354780 Over a year ago

Thank you that worked great. I entered the pictures in an array and then used a foreach to explode them.

user3354780 · Accepted Answer · 2014-02-26 17:49:57Z

This how my datafeed.php script looks now. Thank you for all your help it is much appreciated.

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

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

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

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

$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';

Collectives™ on Stack Overflow

How to stop the output of duplicate database query results PHP

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related