Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Setup:

I have 4 tables:

  • guestList
  • eventList
  • inviteList
  • rsvpList

The descriptions of the tables:

guestList

+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| _id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| groupName | varchar(50) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

eventList

+---------------------+-------------+------+-----+---------+----------------+
| Field               | Type        | Null | Key | Default | Extra          |
+---------------------+-------------+------+-----+---------+----------------+
| _id                 | int(11)     | NO   | PRI | NULL    | auto_increment |
| description         | varchar(50) | YES  |     | NULL    |                |
| eventDate           | date        | YES  |     | NULL    |                |
| eventTime           | time        | YES  |     | NULL    |                |
| address             | varchar(50) | YES  |     | NULL    |                |
| locationDescription | varchar(50) | YES  |     | NULL    |                |
+---------------------+-------------+------+-----+---------+----------------+

inviteList

+---------+---------+------+-----+---------+----------------+
| Field   | Type    | Null | Key | Default | Extra          |
+---------+---------+------+-----+---------+----------------+
| _id     | int(11) | NO   | PRI | NULL    | auto_increment |
| guestId | int(11) | YES  |     | NULL    |                |
| eventId | int(11) | YES  |     | NULL    |                |
+---------+---------+------+-----+---------+----------------+

rsvpList

+---------+---------+------+-----+---------+----------------+
| Field   | Type    | Null | Key | Default | Extra          |
+---------+---------+------+-----+---------+----------------+
| _id     | int(11) | NO   | PRI | NULL    | auto_increment |
| guestId | int(11) | YES  |     | NULL    |                |
| eventId | int(11) | YES  |     | NULL    |                |
+---------+---------+------+-----+---------+----------------+

I have a mySQL statement to pull a list of all events that a given guest is invited to:

SELECT e.description,e.eventDate,e.eventTime,e.locationDescription,e.address, 
IF(r.eventId = e._id AND r.guestId = 1, 1, 0)
FROM eventList AS e 
JOIN inviteList AS i ON e._id = i.eventId 
LEFT JOIN rsvpList AS r ON e._id = r.eventId
WHERE i.guestId = 1;

Goal:

I want to insert the guestId value into the statement via object oriented interaction with mysqli.

I've altered the statement that I'm passing into the mysqli::prepare() method:

SELECT e._id,e.description,e.eventDate,e.eventTime,e.address,e.locationDescription,i.guestId,
IF(r.eventId = e._id AND r.guestId = ?, 1, 0) AS 'RSVP-ed'  
FROM eventList AS e 
JOIN inviteList AS i ON e._id = i.eventId  
LEFT JOIN rsvpList AS r ON r.eventId = e._id 
WHERE i.guestId = ?

The Problem:

After binding the guestId variable, executing the query, binding the result to a variable, and then fetching said result, I get null.

I re-read the documentation for the prepare method and saw that you cannot use the question mark place holder in an if statement.

IF(r.eventId = e._id AND r.guestId = ?, 1, 0) -- Invalid :/

The Question:

How can I use a place holder in this situation? I need the if statement so I can return a boolean flag for each row depending on if the given guest has rsvp-ed to the event, but I don't want to concatenate a variable and risk an injection attack. Is there another way of binding a variable that would allow me to use it within an if statement or would I need to rethink the entire query?

A second pair of eyes would be great :)

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

I'm more mysql than php but are you binding the $guestid php variable twice? You need to supply a variable for each ? placeholder in prepared statements and presumably so in mysqli. In a native prepared statement on the server, the ? is legal within an IF() but then again so is ? = ?, which apparently mysqli doesn't allow.

But also, you could rewrite the query:

IF(r.eventId = e._id AND r.guestId = i.guestId, 1, 0) AS 'RSVP-ed'

We know that i.guestId has to contain the guestId you're interested in, because you've specified it in the where clause so we should be able to compare it this way.

However, there seem to be other issues with your query.

LEFT JOIN rsvpList AS r ON r.eventId = e._id 

It seems like this should actually be:

LEFT JOIN rsvpList AS r ON r.eventId = e._id AND r.guestId = i.guestId

...because otherwise you're joining all of the rsvpList entries for that event, not just those for the guest in question. You may have tried to filter for this in the WHERE clause at some point, which doesn't work if they haven't rsvp'ed.

This change also illustrates that the first comparison in your IF() is actually unnecessary.

IF(r.eventId = e._id AND r.guestId = i.guestId, 1, 0) AS 'RSVP-ed'

We know that r.eventId will match e._id OR will be null if no RSVP, so we don't need to test it, because r.guestId will be what we want or null if no rsvp, so this becomes simply:

r.guestId IS NOT NULL as `RSVP-ed`

the truthiness of the assertion that r.guestId is not null in the joined-up result set is returned as a 1 or 0, and r.guestId will be null with no rsvp because there's no matching row in rsvpList for that guestId.

An equivalent and slightly more intuitive (but redundant) expression of that would be

IF(r.guestId IS NOT NULL,1,0) as `RSVP-ed`
share|improve this answer
    
Awesome. Thanks for not only answering the question but writing out the various permutations on achieving the goal. In the end your final statement will work the best so I'm going to go with that, but since I'm using this site partially as a learning experience I really appreciate the extra time you took to write out the reasoning and examples. Thanks! –  Chris Schmitz Feb 14 '13 at 14:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.