Jump to content


Photo

Quering the DB


  • Please log in to reply
7 replies to this topic

#1 mcmuney

mcmuney
  • Members
  • PipPipPip
  • Advanced Member
  • 358 posts

Posted 21 August 2006 - 06:55 PM

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:






idmem_idimage_name
1123image1.jpg
2123image2.jpg
3555imagex.jpg
4555imagey.jpg

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???

<?
$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);
?>


#2 pachelbel101

pachelbel101
  • Members
  • PipPip
  • Member
  • 11 posts

Posted 21 August 2006 - 07:15 PM

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):

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


You may need parentheses around each entire SELECT statement:

(SELECT ......)
INTERSECT
(SELECT ......)
Hope this works out for you.

#3 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 21 August 2006 - 07:29 PM

You could try
SELECT mem_id, image_name FROM member_images
GROUP BY mem_id
ORDER BY mem_id DESC

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#4 mcmuney

mcmuney
  • Members
  • PipPipPip
  • Advanced Member
  • 358 posts

Posted 21 August 2006 - 07:36 PM

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?

#5 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 21 August 2006 - 07:49 PM

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+
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

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#6 mcmuney

mcmuney
  • Members
  • PipPipPip
  • Advanced Member
  • 358 posts

Posted 21 August 2006 - 08:01 PM

Excellent, that solved it. Thank you!!!

#7 mcmuney

mcmuney
  • Members
  • PipPipPip
  • Advanced Member
  • 358 posts

Posted 02 September 2006 - 12:38 AM

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???

#8 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 02 September 2006 - 09:30 AM

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


To make it v4.0 compatible, replace subquery with a temporary table
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";

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users