How do I use a variable in the WHERE condition of a MySQL query using a php variable?

Question

I'm using PHP session variable to track character ID's between two tables, characters and character_data_store.

The session ID definitely has the correct ID as I have had to print its value before it goes into the mySQL query.

For testing I selected a user I knew had a rapsheet and used

$usersql = "SELECT * 
 FROM character_data_store 
 WHERE character_data_store.`key` = 'RapSheet' 
 AND character_data_store.character_id = '216'";

Obviously I can't use this for all users as I need to confirm the right one has been selected so thats where the session variable comes in.

I've tried using:

$correctPlayer = $_SESSION['selpid'];
echo $correctPlayer; #confirm it's the right id and then remove

$usersql = "SELECT * 
  FROM character_data_store 
  WHERE character_data_store.'key' = 'RapSheet' 
  AND character_data_store.character_id = '$correctPlayer'";

I did some searching on SO and I found that int's need to have double quotes around them not single quotes, I tried that and had no luck but someone else suggested putting the session ID in exactly which I tried next:

$usersql = "SELECT * 
  FROM character_data_store 
  WHERE character_data_store.'key' = 'RapSheet' 
  AND character_data_store.character_id = {$_SESSION['selpid']}";

Each time I do this I get mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given which SO tells me is because this operation results to false, I assume because it's not accepting the playerID from selpid or $correctPlayer?

It definitely works with the testing user where the playerID is inserted directly into the query. But I can't think of a way to do that since I need to match the playerID from table "characters" where the search is done against their first and last name and then pull the rapsheet data against the same playerID in table "character_data_store".

How do I use a variable in the WHERE condition of a MySQL query using a php variable?

You use a placeholder (`?`) and the means of the mysql-layer you're using (PDO or mysqli_X) to bind the variable to it. — Tom Regner, Jul 07 '17 at 06:21
can you echo the `$usersql` ? See what it actually contains once you've added the session variable to it — Florian Humblot, Jul 07 '17 at 06:21
@FMashiro I echoed it and it came out as SELECT * FROM character_data_store WHERE character_data_store.'key' = 'RapSheet' AND character_data_store.character_id = '376' which is what I want. — DrauL, Jul 07 '17 at 06:23
@DrauL could you post the `mysql_fetch_assoc` part of your code too? — Florian Humblot, Jul 07 '17 at 06:26
It's quite long to put in a comment: https://pastebin.com/Yr4xXUY0 — DrauL, Jul 07 '17 at 06:30
$userresult = mysqli_query($conn, $usersql); also which is the connection and the query — DrauL, Jul 07 '17 at 06:30
Have a look at this answer, it shows how to use a placeholder in the where-clause and bind a value to it: https://stackoverflow.com/a/23417997/594138 — Tom Regner, Jul 07 '17 at 06:37
Thanks Tom, how would I use the placeholder in this instance? `$usersql = "SELECT * FROM character_data_store WHERE character_data_store.'key' = 'RapSheet' AND character_data_store.character_id = ?";` and on a second line $stmt->bind_param('', $correctPlayer);` Unfortunately the answer to that question is too complex for me — DrauL, Jul 07 '17 at 06:48
"I assume because it's not accepting..." - you assume wrong. Your code has an error in it. MySQL will give you more details about that error if you ask it. — symcbean, Jul 07 '17 at 08:00

score 0 · Answer 1 · answered Jul 07 '17 at 07:14

0

There are multiple ways to do this. A naive way to do this would be-

$usersql = "SELECT * FROM character_data_store WHERE character_data_store.'key' = 'RapSheet' AND character_data_store.character_id = ".$correctPlayer;

But to avoid sql injections I would recommend you use bindparam function to bind paramaters in a statement.

$sql="SELECT * FROM character_data_store WHERE character_data_store.'key' = 'RapSheet' AND character_data_store.character_id = ?";
if($stmt = $dbh->prepare($sql)){

    $stmt->bindParam(1, $correctPlayer, PDO::PARAM_STR);
    $ql = $stmt->execute() or die("ERROR: " . implode(":", $dbh->errorInfo()));
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    $result['data'] = $row;

answered Jul 07 '17 at 07:14

Prashant Gupta

1
1

Could you explain this by line for me? What does $dbh do? – DrauL Jul 07 '17 at 07:38
$dbh here is a PHP Data Object that can be created as- $dbh = new PDO('mysql:host=localhost;dbname='.$dbname, $dbsettings['user'], $dbsettings['password']);. In the first line we are creating an SQL statement to which we will later bind our parm. if $dbh->prepare() successfully creates the statement(no sql syntax error) then we bind our param to the query and then execute it. Wecan use fetch to fetch the data from the query. if multiple rows are there in the resultset use the fetch statement in a while loop(while($row=$stmt->fetch(PDO::FETCH_ASSOC){....}). – Prashant Gupta Jul 07 '17 at 09:02

Saral · Accepted Answer · 2017-07-07T07:48:31.957

You have obvious error in your code. You are missing quotes in {$_SESSION['selpid']} and you are using quotes in column name. Your query should be

$usersql = "SELECT * FROM character_data_store WHERE character_data_store.`key` = 'RapSheet' AND character_data_store.character_id = '{$_SESSION['selpid']}'";

You should not use quotes in column name, instead use backquotes(`) if you really need. I recommend prepared statements.

How do I use a variable in the WHERE condition of a MySQL query using a php variable?

2 Answers2