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] Link to comment https://forums.phpfreaks.com/topic/18222-quering-the-db/ 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. Link to comment https://forums.phpfreaks.com/topic/18222-quering-the-db/#findComment-78234 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] Link to comment https://forums.phpfreaks.com/topic/18222-quering-the-db/#findComment-78241 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? Link to comment https://forums.phpfreaks.com/topic/18222-quering-the-db/#findComment-78248 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] Link to comment https://forums.phpfreaks.com/topic/18222-quering-the-db/#findComment-78253 Share on other sites More sharing options...
mcmuney Posted August 21, 2006 Author Share Posted August 21, 2006 Excellent, that solved it. Thank you!!! Link to comment https://forums.phpfreaks.com/topic/18222-quering-the-db/#findComment-78259 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??? Link to comment https://forums.phpfreaks.com/topic/18222-quering-the-db/#findComment-84378 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] Link to comment https://forums.phpfreaks.com/topic/18222-quering-the-db/#findComment-84497 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.