Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
06-16-2012, 08:08 PM #1
- Join Date
- Feb 2009
- Thanked 0 Times in 0 Posts
Mysqli - using select case but other data is not bound
I'm using Mysqli OO to search the database for 2 specific values using the MySQL statement CASE. If both values are found then it will return the value 'true', if only one is found the value is 'false', if neither are found then the result is neither. Anyway I've got this working but my problem is I need it to also display the values that are related in the column.
$query = "SELECT 'userName','email','code',
WHEN (email=? AND code=?) THEN 'true'
WHEN (email=?) THEN 'false'
END AS answer
FROM users WHERE email=?";
$stmt = $mysqli->prepare($query);
$stmt->bind_param("ssss", $email,$code, $email,$email);
$email = $mysqli->real_escape_string($email);
$code = $mysqli->real_escape_string($code);
echo "User: ".$user." Email: ".$email." Code: ".$code." Answer: ".$answer;
User: ryan Email: local@localhost Code: 12 Answer: true
User: userName Email: email Code: code Answer: true
BTW I have posted it in the PHP area as well just for the simple reason I'm not entirely sure which part it is, so sorry if I'm not allowed to do this
Last edited by kenno69; 06-16-2012 at 08:10 PM.
06-16-2012, 09:08 PM #2
Has nothing to do with bind or mysqli.
When you use apostrophes around anything in SQL (any SQL, not just MySQL), you are creating a STRING. A literal string. Exactly what is in the apostrophes.
In other words, if you useCode:
You surely need to and meant to use backticks (the ` character that shares a keyboard key with the ~ tilde).
$query = "SELECT `userName`,`email`,`code`, ...
$query = "SELECT userName,email,code, ...Be yourself. No one else is as qualified.
Users who have thanked Old Pedant for this post:
06-17-2012, 12:59 AM #3
You have another problem in the SQL, by the by.
CASE WHEN (email=? AND code=?) THEN 'true' WHEN (email=?) THEN 'false' END AS answer
You have no WHEN to handle that, so answer will be NULL.
Not sure your CASE really makes sense, as given.Be yourself. No one else is as qualified.