Jump to content

Quering the DB


mcmuney

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]
Link to comment
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.
Link to comment
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]
Link to comment
Share on other sites

  • 2 weeks later...
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
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]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.