IF in mysql query results problem

[jdulberg]

I have been trying to figure out how to fix this problem for about a month now and have posted on some other websites but haven't had any luck. So if you have any suggestions as to how I might fix the problem, please let me know as it becoming critical. Thanks.

I am working on a program that allows users to "bookmark" a particular item that they view. The bookmarks are stored in the database.

I would like to use an IF statement in the mysql query so when the user logs in and views the items, the option to bookmark the record has been removed if they have already bookmarked it.

Here's an example query:

SELECT p.id AS player_id, p.name, p.hs, c.id AS coach_id, co.cid AS college,
IF(co.pid=31,1,0) AS is_bookmarked
FROM player p, coach c, co_bookmarks co
WHERE p.id=31 AND p.hs=c.id AND co.cid=2

When I use the above query in a search, it comes up with 3 results, 1 of which is correct. If I GROUP BY p.id, there is only 1 result but its incorrect. The search should only come up with 1 correct result in this case.

+-----------+-------------+----+----------+---------+---------------+
| player_id | name | hs | coach_id | college | is_bookmarked |
+-----------+-------------+----+----------+---------+---------------+
| 31 | Paul Mantle | 24 | 24 | 2 | 0 |
| 31 | Paul Mantle | 24 | 24 | 2 | 1 |
| 31 | Paul Mantle | 24 | 24 | 2 | 0 |
+-----------+-------------+----+----------+---------+---------------+

+-----------+-------------+----+----------+---------+---------------+
| player_id | name | hs | coach_id | college | is_bookmarked |
+-----------+-------------+----+----------+---------+---------------+
| 31 | Paul Mantle | 24 | 24 | 2 | 0 |
+-----------+-------------+----+----------+---------+---------------+

The row with is_bookmarked = 1 is the only correct one. I am assuming that for some reason, its displaying 3 results because there are 3 id's associated with cid=2. But if I group them by the id, it takes the wrong row:

If anyone has any suggestions as to what I'm doing wrong, please let me know as I'm tapped out on ideas.

Thanks a TON!!

Jason

 

[Broccoli2]

Two points:
1. It looks as if you are doing a cross join to the

co

table. This might be a reason why you are getting too many rows. If you have a field from the

player

or

coach

tables, make a

LEFT JOIN

using that, ie:

SELECT
p.id AS player_id
, p.name
, p.hs
, c.id AS coach_id
, co.cid AS college
, IF(co.pid=31,1,0) AS is_bookmarked
FROM
player AS p
INNER JOIN coach AS c ON p.hs=c.id
LEFT JOIN co_bookmarks AS co ON xx.yy=co.zz

2. You can force the

WHERE

condition to be applied at the join, rather than after, by including it in the

ON

expression, ie

t1 LEFT JOIN t2 ON CONCAT("2",t1.x)=CONCAT(t2.y,t2.z)

Again this will enable you greater control over what gets matched (and the rows that are generated)

Please could you post the structure of your tables to know how best to apply both of these points.

-Rob

 

[jdulberg]

Thank you for your response

As per your request, here is the slimmed down, basic structure of the 3 tables involved.

CREATE TABLE co_bookmarks (
id tinyint(4) NOT NULL auto_increment,
cid tinyint(4) NOT NULL default '0',
pid tinyint(4) NOT NULL default '0',
KEY id (id)
)

cid identifies the person currently logged in.
pid identifies the player that is bookmarked

CREATE TABLE coach (
id int(11) NOT NULL auto_increment
PRIMARY KEY (id)
)

CREATE TABLE player (
id int(11) NOT NULL auto_increment,
hs int(11) NOT NULL default '0',
name varchar(75) NOT NULL default '',
PRIMARY KEY (id),
KEY hs (hs)
)

player display takes certain info from the coach table so player.hs links to coach.id

If you need any further explanation about what I am trying to do, please let me know.

Again, thank you for your help in this matter, it is greatly appreciated!!

Jason

 

[Broccoli2]

Try

SELECT DISTINCT
p.id AS player_id
, p.name
, p.hs
, co.cid AS college
, IF(co.pid=31,1,0) AS is_bookmarked
FROM player AS p
INNER JOIN coach AS c ON p.hs=c.id
LEFT JOIN co_bookmarks AS co ON CONCAT(p.id,2)=CONCAT(co.pid,co.cid)

Let me know what result set you get. I have assumed that

co_bookmarks.pid

is a reference to

player.id

.

-Rob

 

[jdulberg]

Hi Rob... based on your help and some tinker with other people's code as well, the following seems to do the trick.

SELECT p.*, c.coachname, c.hs_url, c.hs_name, IF(co.pid=p.id,1,0) AS is_bookmarked
FROM player p INNER JOIN coach c ON p.hs=c.id LEFT OUTER JOIN co_bookmarks co ON (p.id=co.pid AND co.cid=2)
WHERE p.hs=c.id
GROUP BY p.id

It certainly was a formidable task to get it working.

Thank you again for your help!

Jason