how to simplify this to make less queries

jasondavis

I have the code that runs below which helps build a users photo album, Basicly if there is 5 photo's it creates 6 mysql queries and I think It can go down to 1 if someone can help me. I would like to somehow include the query that is in the while statement to be in the initial call, If a photo has more the 0 mcomments marked as NEW then I would like to show "New comments" for that photo

appreciate any help

$sql = 'SELECT * FROM friend_user_photo WHERE userid=' .$userid. ' ORDER BY ' .$sort.' ' .$order_by2. ' limit ' .$start. ',' .$pagesize. '';
$result_photo= executequery($sql);


while($line_photo=mysql_fetch_array($result_photo)){

$sql = "SELECT COUNT(*) FROM friend_photo_comment WHERE photoid='$photoid' AND alert_message='yes' LIMIT 1";
$result = executeQuery($sql);
if($result > 0){
	echo = 'New photo comment';
}

//more code here that shows user photo

}

Shrike

You could use an inner join on friend_photo_comment, and group by photoid to get the count.

jasondavis

I have been searching and reading up on joins all day and inner joins but I am at a lost on how to structure this to work with a join, for each photo there could be anywhere from 0-any number of comments and I need to just see if there is ANY comments that are marked as being NEW for each photo entry

Bjom

SELECT * FROM photo INNER JOIN comment ON photo.id = comment.photoid WHERE alert = 'yes';

you should be able to work from this...

jasondavis

Bjom;10920500 wrote:
SELECT * FROM photo INNER JOIN comment ON photo.id = comment.photoid WHERE alert = 'yes';
you should be able to work from this...

The problem with this it that it would only return a photo if it was marked as new, what I need to do is show all photo's for that user and on each photo mark if it is new or not, any ideas how to achieve this or is the current way with multiple queries the only way? thanks

Bjom

Replace INNER JOIN with LEFT JOIN

If you have MS ACCESS, make yourself two tables and experiment a little with the excellent query builder. It let's you design your queries with a GUI and lets you switch between GUI, SQL and results quickly, so it's pretty good to learn and understand some SQL basics fast. The SQL it produces is mostly ok - just get rid of the excess parentheses.

(In this special case you might need to go witha subquery SELECT fld1, fld2 FROM tblA LEFT JOIN (SELECT fld2 FROM tblB WHERE fld2 = 'whatever')...)

Bjom

johanafm

I'd assume the ellipsis to be: ON tblA.fld2 = tblB.fld2

No subquery needed in the simplified example above, since
SELECT fld1, fld2 FROM tblA LEFT JOIN (SELECT fld2 FROM tblB WHERE fld2 = 'whatever') ON tblA.fld2 = tblB.fld2)
is the same as
SELECT fld1, fld2 FROM tblA LEFT JOIN tblB ON tblA.fld2 = tblB.fld2 AND tbl.fld2 = 'whatever'

Since it's a LEFT JOIN, all results from tblA will be used, even if there are no results found in tblB. If there are no results for tblB, its fields will all be null. As such, the result of using a WHERE clause in the subquery corresponds to what you get by moving the things in the subqyery's WHERE clause to the ON clause in ordinary join.

Moreover, since it's a 1-N relation, each row in friend_photo_comment will relate only to one row in friend_user_photo, which means one simple query is enough to get everything you need.

$sql = "SELECT FUP.*, COUNT(FPC.photoid) AS newCommentCount FROM friend_user_photo AS FUP LEFT JOIN friend_photo_comment AS FPC ON FUP.photoid = FPC.photoid AND alert_message LIKE 'yes'
WHERE userid= $userid
GROUP BY FPC.photoid
ORDER BY $sort $order_by2
LIMIT $start, $pagesize";

And if you use a double quoted string, you don't have to use the concatenation operator to piece together string literals with string variables.

jasondavis

thanks for the help this stuff is very hard for me to understand even though I been dealing with it for years now

I tried the above but it returns this error message,

Column 'userid' in where clause is ambiguous'

so I changed userid to FUP.userid and the error went away also had to change FPC.photoid to FPC.auto_id because that is the correct name and the query now runs with no errors

For some reason though it is not returning all photo's for a user, example it seems to only return a max of 1 result per user

any idea what could be wrong?

$sql = "SELECT FUP.*, COUNT(FPC.photoid) AS newCommentCount FROM friend_user_photo AS FUP LEFT JOIN friend_photo_comment AS FPC ON FUP.auto_id = FPC.photoid AND alert_message LIKE 'yes'
	WHERE FUP.userid=$userid
	GROUP BY FPC.photoid
	ORDER BY $sort $order_by2
	LIMIT $start, $pagesize";
	$result_photo= executequery($sql);

johanafm

it's the line

"...
LIMIT $start, $pagesize";

It was somewhere in the original query you posted. $pagesize should be whatever is the max number you want per page, and $start is the offset (what page you're viewing). Removing it alltogether would give you all results.
i.e.

$sql = "SELECT FUP.*, COUNT(FPC.photoid) AS newCommentCount FROM friend_user_photo AS FUP LEFT JOIN friend_photo_comment AS FPC ON FUP.auto_id = FPC.photoid AND alert_message LIKE 'yes'
    WHERE FUP.userid=$userid
    GROUP BY FPC.photoid
    ORDER BY $sort $order_by2";

And to explain a bit about what happens, what the LEFT JOIN does is take the data in the table specified with "FROM fromtable" and for each of the rows in fromtable, append data found in the table specified with "LEFT JOIN jointable".
The ON "fromtable.somevar = jointable.othervar" means that if no match at all is found for these two variables for a specific row in fromtable with any row in jointable, then the result is the row in fromtable with null values appended for all columns in jointable.

If instead you user INNER JOIN, then ONLY when at least one match is found will the join be performed and included in the result.

person (fromtable in my generic explanation above)
id		name
1		mike
2		sue
3		john

dog (jointable above)
id		dogname		owner
1		carl		3
2		fluffy		1
3		betsy		3


SELECT person.name, dogname FROM person INNER JOIN dog ON person.id = owner;
Result:
mike		fluffy
john		carl
john		betsy


SELECT person.name, dogname FROM person LEFT JOIN dog ON person.id = owner;
Result:
mike		fluffy
sue		null
john		carl
john		betsy