Matching Image To ID

[Bartman]

Wondering if someone could help me. What I am trying to do is display the information from each row. I am trying to read from 2 tables and display them on a page. I want them to show like this

 

name,make,type, price,"images/listings/a1.jpg,images/listings/a2.jpg,images/listings/a3.jpg,images/listings/a4.jpg,images/listings/a5.jpg,images/listings/a5.jpg"

name,make,type, price,"images/listings/b1.jpg,images/listings/b2.jpg,images/listings/b3.jpg,images/listings/b4.jpg,images/listings/b5.jpg,images/listings/b5.jpg"

 

Example. the table structures are as so:

 

listings
--------------------------------
id  name    make   type   price  (then a couple more columns I dont need data from)
--------------------------------
1   name1   make1  type1  price1
2   name2   make2  type2  price2
3   name3   make3  type3  price3


listimages
----------------------------------------------------
id  imagepath                 mainimage   listingid   
----------------------------------------------------
1   images/listings/a1.jpg     1            1
2   images/listings/a2.jpg     0            1
3   images/listings/a3.jpg     0            1
4   images/listings/a4.jpg     0            1
5   images/listings/a5.jpg     0            1
6   images/listings/a6.jpg     0            1
7   images/listings/b1.jpg     1            2
8   images/listings/b2.jpg     0            2
9   images/listings/b3.jpg     0            2
10  images/listings/b4.jpg     0            2
11  images/listings/b5.jpg     0            2
12  images/listings/b6.jpg     0            2
13  images/listings/c1.jpg     1            3

etc

 

Here is the code I am using. Everything works except some of the listing images don't match up to the names.

I think when certain names don't have pictures it just moves up the images to the next person. I need to somehow associate the listingid column from listimages table with the id column from the listings table. The mainimage column

tells what image of the group of images is default for that name. 1 being the default 0 not the default

 

Here is the code I am using.

 

<?php

// Make a MySQL Connection
mysql_connect("localhost", "xxxxxx", "xxxxxx") or die(mysql_error());
mysql_select_db("xxxxxx") or die(mysql_error());

$result = mysql_query("SELECT * FROM listings l JOIN listimages li ON l.id = li.listingid ORDER BY l.id DESC")
or die(mysql_error());

$last_listingid = null;
$curRow = array();
while($row = mysql_fetch_array($result)) {
    
    // Check for a difference in listing ID - and if it is, then we should show the previous row first
if ($last_listingid != $row['listingid'] && $last_listingid !== null)
{
        // Implode the array with quotes around it
echo $row['name'];
echo ",";
echo $row['make'];
echo ",";
echo $row['model'];
echo ",";
echo $row['type'];
echo ",";
echo $row['price'];
echo ",";
echo '"' . implode(',', $curRow) . '"<br />';
        
        // And start fresh
        $curRow = array();
}
    
    // Now we can save it to the array
    $curRow[] = $row['imagepath'];
    
    // And set the new ID
$last_listingid = $row['listingid'];
}
// Finally, we'll need to implode it one last time since there should always be at least one value in the array
echo '"' . implode(',', $curRow) . '"';

?>

 

Please let me know what I am doing wrong Anyone   Thanks

[scootstah]

Is listimages' id auto_increment, or is it added via mysql_insert_id when a listing is added?

 

If it is auto_increment then you need to make another column in listings with an image id, then join on that instead of the listings id.

[Dusaro]

Maybe try LEFT JOIN instead of just JOIN, Also, before the ORDER BY you need a GROUP BY.

 

This is what I currently have as my query and its working good(for halo clan kdr table).

SELECT table_members.name FROM table_members LEFT JOIN table_screenshot ON table_members.id = table_screenshot.memberid GROUP BY table_members.id ORDER BY table_members.id ASC

 

That is just to show what it should look similar to.

[Bartman]

Hi scootstah, isn't my column listingid the image id column?  There is both a auto_increment column and a listingid column. The listingid column numbers correspond with the auto_increment column from the Listings Table

 

Duraso, the LEFT JOIN just outputs the same thing.

 

I see where the error start.  right after the row that does not have an image.

 

So lets say the the ID number 5 has no images. All are fine up until 5 then ID number 6 images move up and are displayed with Number 5's info. Understand? Maybe there is a way to put something like if there is no images then display Default/images/no_image.gif  Is that possible?  I would rather have that row just display no images but if it is not possible then how about the Default/images/no_image.gif approach?

 

Anyone?

[SergeiSS]

You'd better show your code. Otherwise it's difficult to say what is wrong.

 

I can suggest only that you try to use the value from your selection (column with image name or something similar to it) without checking it's contents.

[Bartman]

OK Thanks for everyone's help I really, really appreciate it. Ok here exactly what it is I am trying to accomplish. I have 2 tables (listings & listimages) that I need to extract data from and display. There are a couple extra columns that I don't need to pull data from like price, date etc

 

listings
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
id   make   model   vehicle_type   doors   color   mileage   year   listing_condition   engine   trans   drive_train   fuel_type   adddesc   stock     
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
1    make1  model1  vehicle_type1  doors1  color1  mileage1  year1  listing_condition1  engine1  trans1  drive_train1  fuel_type1  adddesc1  stock1
2    make2  model2  vehicle_type2  doors2  color2  mileage2  year2  listing_condition2  engine2  trans2  drive_train2  fuel_type2  adddesc2  stock2
3    make3  model3  vehicle_type3  doors3  color3  mileage3  year3  listing_condition3  engine3  trans3  drive_train3  fuel_type3  adddesc3  stock3
4    make4  model4  vehicle_type3  doors4  color4  mileage4  year4  listing_condition4  engine4  trans4  drive_train4  fuel_type4  adddesc4  stock4


listimages
----------------------------------------------------
id  imagepath                 mainimage   listingid   
----------------------------------------------------
1   images/listings/a1.jpg     1            1
2   images/listings/a2.jpg     0            1
3   images/listings/a3.jpg     0            1
4   images/listings/a4.jpg     0            1
5   images/listings/a5.jpg     0            1
6   images/listings/a6.jpg     0            1
7   images/listings/b1.jpg     1            2
8   images/listings/b2.jpg     0            2
9   images/listings/b3.jpg     0            2
10  images/listings/b4.jpg     0            2
11  images/listings/b5.jpg     0            2
12  images/listings/b6.jpg     0            2
13  images/listings/c1.jpg     1            4

 

Ok I need a php to display all the above information in simple rows on a page. So the 4 listings.id rows above will look like this on a page (I don't need the auto_increment number to shwo)

 

make1,model1,vehicle_type1,doors1,color1,mileage1,year1,listing_condition1,engine1,trans1,drive_train1,fuel_type1,"adddesc1",stock1,"a1.jpg,a2.jpg,a3.jpg,a4.jpg,a5.jpg,a6.jpg"

make2,model2,vehicle_type2,doors2,color2,mileage2,year2,listing_condition2,engine2,trans2,drive_train2,fuel_type2,"adddesc2",stock2,"b1.jpg,b2.jpg,b3.jpg,b4.jpg,b5.jpg,b6.jpg"

make3,model3,vehicle_type3,doors3,color3,mileage3,year3,listing_condition3,engine3,trans3,drive_train3,fuel_type3,"adddesc3",stock3,""

make4,model4,vehicle_type4,doors4,color4,mileage4,year4,listing_condition4,engine4,trans4,drive_train4,fuel_type4,"adddesc4",stock4,"c1.jpg"

 

right now when I use this code:

 

<?php

// Make a MySQL Connection
mysql_connect("localhost", "XXXXXXXX", "XXXXXXXX") or die(mysql_error());
mysql_select_db("XXXXXXXX") or die(mysql_error());

$result = mysql_query("SELECT * FROM listings l LEFT JOIN listimages li ON l.id = li.listingid ORDER BY l.id DESC")
or die(mysql_error());

$last_listingid = null;
$curRow = array();
while($row = mysql_fetch_array($result)) {

    // Check for a difference in listing ID - and if it is, then we should show the previous row first
if ($last_listingid != $row['listingid'] && $last_listingid !== null)
{
        // Implode the array with quotes around it
echo $row['year'];
echo ",";
echo $row['make'];
echo ",";
echo $row['model'];
echo ",";
echo $row['vehicle_type'];
echo ",";
echo $row['doors'];
echo ",";
echo $row['color'];
echo ",";
echo $row['mileage'];
echo ",";
echo $row['listing_condition'];
echo ",";
echo $row['engine'];
echo ",";
echo $row['trans'];
echo ",";
echo $row['drive_train'];
echo ",";
echo $row['fuel_type'];
echo ",";
echo "Please contact";
echo ",\"";
echo $row['adddesc'];
echo "\",";
echo $row['stock'];
echo ",";
echo '"http://www.website.com/' . implode(',http://www.website.com/', $curRow) . '"<br />';
        
        // And start fresh
        $curRow = array();
}
    
    // Now we can save it to the array
    $curRow[] = $row['imagepath'];
    
    // And set the new ID
$last_listingid = $row['listingid'];
}
// Finally, we'll need to implode it one last time since there should always be at least one value in the array
echo '"http://www.website.com/' . implode(',', $curRow) . '"';
?>

 

I get this outcome. (Notice the row that was blank for images is now replaced with the next image down? (c1.jpg should have stayed on 4) all the images from that point move up so no rows from 3 down match the rest of the content for the images.

 

make1,model1,vehicle_type1,doors1,color1,mileage1,year1,listing_condition1,engine1,trans1,drive_train1,fuel_type1,"adddesc1",stock1,"a1.jpg,a2.jpg,a3.jpg,a4.jpg,a5.jpg,a6.jpg"

make2,model2,vehicle_type2,doors2,color2,mileage2,year2,listing_condition2,engine2,trans2,drive_train2,fuel_type2,"adddesc2",stock2,"b1.jpg,b2.jpg,b3.jpg,b4.jpg,b5.jpg,b6.jpg"

make3,model3,vehicle_type3,doors3,color3,mileage3,year3,listing_condition3,engine3,trans3,drive_train3,fuel_type3,"adddesc3",stock3,"c1.jpg"

make4,model4,vehicle_type4,doors4,color4,mileage4,year4,listing_condition4,engine4,trans4,drive_train4,fuel_type4,"adddesc4",stock4,"d1.jpg,d2.jpg,d3.jpg"

 

[SergeiSS]

I tried to understand your logic but failed... Maybe it's my problem, of course.

 

But in any case, did you hear about MVC (Model-View-Controller)? It means that it's better to separate the part where you get your data from the presentation part.

 

In this case it would be better not to use 'echo' inside the 'while' loop. Just get data, create arrays with data and then print them. It also will be more convenient to debug.

 

 

Another thing, can be even more important... It seems, that you may aggregate all information in one SELECT and then just take this info. Check for GROUP_CONCAT in the MySQL help.