mcmuney Posted August 21, 2006 Share Posted August 21, 2006 I'm using the code below to display 2 most recent photos that are updated by members, linked to the member id.PROBLEM: When the most 2 photos are submitted by same member, both links to the same member (this code is doing its job). Based on the sample DB below, member 555 would appear twice with two different images.DB SAMPLE:[table][tr][td]id[/td][td]mem_id[/td][td]image_name[/td][/tr][tr][td]1[/td][td]123[/td][td]image1.jpg[/td][/tr][tr][td]2[/td][td]123[/td][td]image2.jpg[/td][/tr][tr][td]3[/td][td]555[/td][td]imagex.jpg[/td][/tr][tr][td]4[/td][td]555[/td][td]imagey.jpg[/td][/tr][/table]QUESTION: What do I need to do to this code so that the 2 results show 1) imagey.jpg from row 4 and then it skips to row 2 to show 2) image2.jpg so that the same member does not repeat??? [code]<?$sel_image="select * from sc_member_images order by sci_id DESC LIMIT 0,2"; $rs_image=$db->select_data($sel_image);if(count($rs_image)>0) { for($i=0;$i<count($rs_image);$i++) { $rate_flag=0; $mid=$rs_image[$i][scm_mem_id]; $sel_info="select * from sc_member where scm_mem_id=$mid"; $rs_info=$db->select_data($sel_info);?>[/code] Quote Link to comment Share on other sites More sharing options...
pachelbel101 Posted August 21, 2006 Share Posted August 21, 2006 Try this (I must admit I'm not sure if it's valid SQL, but I think it'll work, as long as you can correlate queries across an INTERSECT):[code]SELECT * FROM sc_member_images smi1 ORDER BY sci_id DESC LIMIT 0,1INTERSECTSELECT * FROM sc_member_images smi2 WHERE smi2.mem_id!=smi1.mem_id ORDER BY sci_id DESC LIMIT 0,1[/code]You may need parentheses around each entire SELECT statement:[code](SELECT ......)INTERSECT(SELECT ......)[/code]Hope this works out for you. Quote Link to comment Share on other sites More sharing options...
Barand Posted August 21, 2006 Share Posted August 21, 2006 You could try [code]SELECT mem_id, image_name FROM member_imagesGROUP BY mem_idORDER BY mem_id DESC[/code] Quote Link to comment Share on other sites More sharing options...
mcmuney Posted August 21, 2006 Author Share Posted August 21, 2006 I actually tried GROUP BY earlier, at first it appeared to have done what I needed it to do. But as new rows were inserted, I found that it occassionally skipped new rows. Any idea why it would do that? Quote Link to comment Share on other sites More sharing options...
Barand Posted August 21, 2006 Share Posted August 21, 2006 Values in fields other that the GROUP BY field normally come from the first row but I guess it cant be guaranteed :(If you have MySql 4.1+[code]SELECT a.mem_id, a.image_name FROM member_images aWHERE a.id = (SELECT MAX(b.id) FROM member_images bWHERE b.mem_id = a.mem_id)ORDER BY mem_id DESC[/code] Quote Link to comment Share on other sites More sharing options...
mcmuney Posted August 21, 2006 Author Share Posted August 21, 2006 Excellent, that solved it. Thank you!!! Quote Link to comment Share on other sites More sharing options...
mcmuney Posted September 2, 2006 Author Share Posted September 2, 2006 Barand, Hey, I really appreciated your help on this but just came across an issue and would greatly appreciate any help. The code you gave me worked perfectly, but my host just went from mysql 5.0 and back to 4.0 due to customer complaints. As a result, it is no longer working. Is there a way to do the same function in 4.0 version??? Quote Link to comment Share on other sites More sharing options...
Barand Posted September 2, 2006 Share Posted September 2, 2006 [quote]SELECT a.mem_id, a.image_name FROM member_images aWHERE a.id = (SELECT MAX(b.id) FROM member_images bWHERE b.mem_id = a.mem_id)ORDER BY mem_id DESC[/quote]To make it v4.0 compatible, replace subquery with a temporary table[code]mysql_query("CREATE TEMPORARY TABLE tmp_images SELECT mem_id, MAX(id) as id FROM member_imagesGROUP BY mem_id");$sql = "SELECT a.mem_id, a.image_name FROM member_images a INNER JOIN tmp_images bON b.id = a.idORDER BY mem_id DESC";[/code] 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.