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