Jump to content

Archived

This topic is now archived and is closed to further replies.

mcmuney

Quering the DB

Recommended Posts

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]

Share this post


Link to post
Share on other sites
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,1
INTERSECT
SELECT * 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.

Share this post


Link to post
Share on other sites
You could try
[code]
SELECT mem_id, image_name FROM member_images
GROUP BY mem_id
ORDER BY mem_id DESC
[/code]

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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 a
WHERE a.id = (SELECT MAX(b.id) FROM member_images b
WHERE b.mem_id = a.mem_id)
ORDER BY mem_id DESC[/code]

Share this post


Link to post
Share on other sites
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???

Share this post


Link to post
Share on other sites
[quote]SELECT a.mem_id, a.image_name FROM member_images a
WHERE a.id = (SELECT MAX(b.id) FROM member_images b
WHERE 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_images
GROUP BY mem_id");

$sql = "SELECT a.mem_id, a.image_name
FROM member_images a INNER JOIN tmp_images b
ON b.id = a.id
ORDER BY mem_id DESC";[/code]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.