Bartman Posted December 2, 2011 Share Posted December 2, 2011 I am looking to display image paths in a row separated by commas. There are 6 images that goes to each user and I would like only the 6 images at be in each " " Like this: "images/listings/listing_516013019A-only.jpg,images/listings/listing_848813453A-1.jpg,images/listings/listing_664613453A-2.jpg,images/listings/listing_520313453A-3.jpg,images/listings/listing_690513453A-4.jpg,images/listings/listing_125113453A-5.jpg,images/listings/listing_641013453A-6.jpg," "images/listings/listing_736913186A-1.jpg,images/listings/listing_822713186A-2.jpg,images/listings/listing_136513186A-3.jpg,images/listings/listing_700313186A-4.jpg,images/listings/listing_716013186A-5.jpg,images/listings/listing_213113186A-6.jpg," "images/listings/listing_292113254A..-1.jpg,images/listings/listing_854413254A..-2.jpg,images/listings/listing_446013254A..-3.jpg,images/listings/listing_676313254A..-4.jpg,images/listings/listing_563413254A..-5.jpg,images/listings/listing_341513254A..-6.jpg," Right now it is displaying them like this "images/listings/listing_516013019A-only.jpg," "images/listings/listing_848813453A-1.jpg," "images/listings/listing_664613453A-2.jpg," "images/listings/listing_520313453A-3.jpg," "images/listings/listing_690513453A-4.jpg," "images/listings/listing_125113453A-5.jpg," "images/listings/listing_641013453A-6.jpg," "images/listings/listing_736913186A-1.jpg," "images/listings/listing_822713186A-2.jpg," "images/listings/listing_136513186A-3.jpg," "images/listings/listing_700313186A-4.jpg," "images/listings/listing_716013186A-5.jpg," "images/listings/listing_213113186A-6.jpg," "images/listings/listing_292113254A..-1.jpg," "images/listings/listing_854413254A..-2.jpg," "images/listings/listing_446013254A..-3.jpg," "images/listings/listing_676313254A..-4.jpg," "images/listings/listing_563413254A..-5.jpg," "images/listings/listing_341513254A..-6.jpg," Here is the code I have: <?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 listimages ORDER BY listimages.listingid DESC ") or die(mysql_error()); while($row = mysql_fetch_array($result)) { echo "\""; echo "$row[imagepath],"; echo "\""; echo "<br>"; } ?> My tables for the images is "listimages" and the columns are: id (which are the auto_increments) imagepath (which shows the path/image1.jpg) mainimage (which just shows 0 or 1 depending on the picture that is the default for that listing, 1 being default) listingid (shows numbers 1 2 3 etc corresponding to the Id in the listings table to show what images go with what listing) There are up to 6 images for each listing. Any idea how to fix this? Quote Link to comment Share on other sites More sharing options...
trq Posted December 3, 2011 Share Posted December 3, 2011 Remove the echo that produces <br> Quote Link to comment Share on other sites More sharing options...
Bartman Posted December 3, 2011 Author Share Posted December 3, 2011 Thanks thorpe for the help, but that just lists them all one after another without a break. I need it to break after all the images for that listingid number. So 6 images all have listingid 1 to correspond with the auto_increment id from another table to show that user uploaded 6 images. Can it be done? Quote Link to comment Share on other sites More sharing options...
Pandemikk Posted December 3, 2011 Share Posted December 3, 2011 You only want it to break after each listing? Store the listingid value in a variable and in the while loop if the current listingid is different than the previous listingid then echo <br /> Example: $last_listingid = ''; while($row = mysql_fetch_array($result)) { echo "\""; echo "$row[imagepath],"; echo "\""; if ($last_listingid != $row['listingid']) { echo "<br />"; } $last_listingid = $row['listingid']; } Quote Link to comment Share on other sites More sharing options...
Bartman Posted December 3, 2011 Author Share Posted December 3, 2011 Oh you the man...that almost did it. Thanks so much. The only thing now is the first image is there then it breaks and then shows the next five in a row. If I can just get those five up one line that would be it. it looks like this now: "images/listings/listing_516013019A-only.jpg," "images/listings/listing_848813453A-1.jpg","images/listings/listing_664613453A-2.jpg","images/listings/listing_520313453A-3.jpg","images/listings/listing_690513453A-4.jpg,images/listings/listing_125113453A-5.jpg","images/listings/listing_641013453A-6.jpg," Also how can I get the quotations " one at the begging and one at the last image....not inbetween like above...I just need the comma separating them. You have help me tremendously. Thank you Quote Link to comment Share on other sites More sharing options...
Bartman Posted December 3, 2011 Author Share Posted December 3, 2011 Sorry I mean the images look like this: "images/listings/listing_848813453A-1.jpg", "images/listings/listing_664613453A-2.jpg","images/listings/listing_520313453A-3.jpg","images/listings/listing_690513453A-4.jpg,images/listings/listing_125113453A-5.jpg","images/listings/listing_641013453A-6.jpg," Quote Link to comment Share on other sites More sharing options...
Philip Posted December 3, 2011 Share Posted December 3, 2011 A small change to the above code will prevent it from having a <br> on the first run. <?php $last_listingid = null; while($row = mysql_fetch_array($result)) { echo "\""; echo "$row[imagepath],"; echo "\""; if ($last_listingid != $row['listingid'] && $last_listingid !== null) { echo "<br />"; } $last_listingid = $row['listingid']; } Quote Link to comment Share on other sites More sharing options...
Bartman Posted December 3, 2011 Author Share Posted December 3, 2011 That moved it up but the break is still there on the first one. Now it looks like this: "images/listings/listing_848813453A-1.jpg,''' "images/listings/listing_664613453A-2.jpg,'''images/listings/listing_520313453A-3.jpg,'''images/listings/listing_690513453A-4.jpg,'''images/listings/listing_125113453A-5.jpg,'''images/listings/listing_641013453A-6.jpg,''"images/listings/listing_390313190A-1.jpg," "images/listings/listing_286713190A-2.jpg,""images/listings/listing_265013190A-3.jpg,""images/listings/listing_620513190A-4.jpg,""images/listings/listing_337713190A-5.jpg,""images/listings/listing_317013190A-6.jpg,""images/listings/listing_555213016A-1.jpg," Notice the 1.jpg image is always appearing at the end of the 6. like 2,3,4,5,6 then 1 but the 1 is the image for the next row Quote Link to comment Share on other sites More sharing options...
Philip Posted December 3, 2011 Share Posted December 3, 2011 That moved it up but the break is still there on the first one. Now it looks like this: [...] Notice the 1.jpg image is always appearing at the end of the 6. like 2,3,4,5,6 then 1 but the 1 is the image for the next row Uhh, that's not what I'm getting (replaced your mysql with a static array): <?php $array = array( array('listingid' => 1, 'imagepath' => "images/listings/listing_848813453A-1.jpg"), array('listingid' => 1, 'imagepath' => "images/listings/listing_664613453A-2.jpg"), array('listingid' => 1, 'imagepath' => "images/listings/listing_520313453A-3.jpg"), array('listingid' => 2, 'imagepath' => "images/listings/listing_690513453A-4.jpg"), array('listingid' => 2, 'imagepath' => "images/listings/listing_125113453A-5.jpg"), array('listingid' => 2, 'imagepath' => "images/listings/listing_641013453A-6.jpg") ); $last_listingid = null; foreach($array as $row) { echo "\""; echo "$row[imagepath],"; echo "\""; if ($last_listingid != $row['listingid'] && $last_listingid !== null) { echo "<br />"; } $last_listingid = $row['listingid']; } Outputs: "images/listings/listing_848813453A-1.jpg,""images/listings/listing_664613453A-2.jpg,""images/listings/listing_520313453A-3.jpg,""images/listings/listing_690513453A-4.jpg," <br> "images/listings/listing_125113453A-5.jpg,""images/listings/listing_641013453A-6.jpg," In any case, you'll want to fix your comma/quote problem, so here is one solution: <?php $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 '"' . 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) . '"'; ...which outputs... "images/listings/listing_848813453A-1.jpg,images/listings/listing_664613453A-2.jpg,images/listings/listing_520313453A-3.jpg" <br> "images/listings/listing_690513453A-4.jpg,images/listings/listing_125113453A-5.jpg,images/listings/listing_641013453A-6.jpg" <br> "images/listings/listing_641013453A-7.jpg" Quote Link to comment Share on other sites More sharing options...
ngreenwood6 Posted December 3, 2011 Share Posted December 3, 2011 You could also use a group_concat for this allowing you to get all of the images for a listing in one query comma separated. Example off the top of my head: SELECT GROUP_CONCAT(imagepath SEPARATOR ',') FROM listimages GROUP BY listingid Quote Link to comment Share on other sites More sharing options...
Bartman Posted December 3, 2011 Author Share Posted December 3, 2011 WOW KingPhilip that worked....all is listed exactly how I wanted. Thank you so much. Could I bother you with one more question? If I want to add another Echo item such as $row['name'] or $row['price"] that matches up with the group of 6 images from one other table called "listings" how can I add that? I tried adding the $row['name'] and adding the listing to the mysql_query("SELECT * FROM listing, listimages ORDER BY listimages.listingid DESC ') But it made like 2 copies of the same thing repeating. that is awesome thanks King Quote Link to comment Share on other sites More sharing options...
Philip Posted December 3, 2011 Share Posted December 3, 2011 You could also use a group_concat for this allowing you to get all of the images for a listing in one query comma separated. Yup, this is also a method if you're just wanting to return what the comma delimited values of the images. If you're needing to use anything else within the row on a per-image basis, then this wouldn't work. If I want to add another Echo item such as $row['name'] or $row['price"] that matches up with the group of 6 images from one other table called "listings" how can I add that? I tried adding the $row['name'] and adding the listing to the mysql_query("SELECT * FROM listing, listimages ORDER BY listimages.listingid DESC ') You'd need to use a MySQL join for that and would want to list out the names of the columns (for performance and for clarity): SELECT l.id, l.name, li.imagepath FROM listing l JOIN listimages li ON l.id = li.listingid ORDER BY l.id DESC Quote Link to comment Share on other sites More sharing options...
ngreenwood6 Posted December 3, 2011 Share Posted December 3, 2011 Reply to king, I believe that you are mistaking if I understood what you said correctly. Yup, this is also a method if you're just wanting to return what the comma delimited values of the images. If you're needing to use anything else within the row on a per-image basis, then this wouldn't work. The following code does what he needs to do and only returns one of each row instead of all of the rows. So if there are 5 listings it will return 5 rows and if each row has 5 images then the 5 images will be comma separated in the images key. SELECT l.id, l.name, GROUP_CONCAT(li.imagepath SEPARATOR ',') AS images FROM listing l LEFT JOIN listimages li ON l.id=li.listingid GROUP BY l.id ORDER BY l.id DESC; Quote Link to comment Share on other sites More sharing options...
Philip Posted December 3, 2011 Share Posted December 3, 2011 Reply to king, I believe that you are mistaking if I understood what you said correctly. I guess I should clarify, you are indeed correct. For whatever reason I was thinking that if there was more to grab within the listimages table that would not work. But this is not the case (since we're grabbing one listing from the listings table) Example. if the table structure is as so: listings -------- id name -------- 1 test1 2 test2 3 test3 [...] listimages --------------------------------- listingid imagepath --------------------------------- 1 images/listings/1.jpg 1 images/listings/2.jpg 2 images/listings/3.jpg 2 images/listings/4.jpg 2 images/listings/5.jpg 3 images/listings/6.jpg ngreenwood6's method would be great. However, if it looked like: listimages ---------------------------------------------------- listingid imagepath imagedescription ---------------------------------------------------- 1 images/listings/1.jpg description 1 1 images/listings/2.jpg description 2 2 images/listings/3.jpg description 3 2 images/listings/4.jpg description 4 2 images/listings/5.jpg description 5 3 images/listings/6.jpg description 6 And at some point you wanted to grab "imagedescription" for each image when you're outputting the imagepaths, the solution would no longer be valid. Quote Link to comment Share on other sites More sharing options...
Bartman Posted December 3, 2011 Author Share Posted December 3, 2011 ok King I joined like you said...everything comes up like I wanted except the first row (which is actual the last item ID number because it is DESC). Every row appears except the last entry at the end is missing so row 102 is not showing...any ideas? I changed the mysql_query to this: $result = mysql_query("SELECT * FROM listings l JOIN listimages li ON l.id = li.listingid ORDER BY l.id DESC") I used * because I wanted to add like 14 other columns to it. Quote Link to comment Share on other sites More sharing options...
ngreenwood6 Posted December 3, 2011 Share Posted December 3, 2011 You do not want to use * because you will have column name conflicts. In your case you will want to use l.*, li.imagepath which will give you all of the listings information and the image associated. Also I think that you are not getting the last row because there are no images associated with it. To fix this problem you should be able to change JOIN to LEFT JOIN and it should work. Quote Link to comment Share on other sites More sharing options...
Bartman Posted December 4, 2011 Author Share Posted December 4, 2011 Thanks for your help. here exactly is is what I am trying to do. I am trying to 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 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 thanks. Quote Link to comment Share on other sites More sharing options...
Bartman Posted December 5, 2011 Author Share Posted December 5, 2011 Please does anyone know? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.