Mysql – PHP and theSQL – Binding a parameter in an if statement

MySQLPHP

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 🙂

Best Answer

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`
Related Question