MySQL Query Not Recognizing PHP Variable

Question

I'm working on an SQL query that selects results based on a User ID number foreign key, and displays the result if there are any matching entries in the correlated table. When I try to use a PHP variable in my WHERE clause, it seems like the SQL query doesn't recognize a value at all, even though if I print the variable, there is a value there.

I've attempted changing the query with the "'. $variable .'" and ' . $variable . ', but I still get the same result. Here is my code:

$usernum = intval($_SESSION['usernum']);
$query = "SELECT * FROM dinosaurs WHERE UserNum = '$usernum'";

When I enter the variable manually (for example "SELECT * FROM dinosaurs WHERE UserNum = '6'"; it works, but when I attempt to run this query I get the following result:

SELECT * FROM dinosaurs WHERE UserNum = ''
returned 0 records.

It looks like the variable is just showing a blank value in the SQL statement? How would I get it to recognize the variable's value?

EDIT: SOLUTION I figured it out! It was a dumb error where I was declaring the variable inside of a different function instead of globally so I moved the declaration to the correct function like this:

  // print_r($json);
  $usernum = intval($_SESSION['usernum']);
  }

So that just goes to show that I should pay more attention to my own code :)

@Qirel I have a script that checks for an existing session, and redirects if there isn't one started, and that's working okay! And when I run this: ` echo $usernum ` it returns the correct value. — Madelyn, Apr 25 '19 at 16:14
use **parameterised statements** to solve all your query-building problems and protect your complete database from leaking by preventing **SQL injection attacks**. and check the *actual* content of `$usernum` — Franz Gleichmann, Apr 25 '19 at 16:17
@FranzGleichmann I ran `vardump($usernum)` and it returned **int(6)** which is what it should be. I'm using parameterized statements wherever I'm using user input in my SQL queries, but for this one I'm not using anything from a user input; is it necessary to use a parameterized statement in that scenario? — Madelyn, Apr 25 '19 at 16:21
With any variable of any kind, ***always*** use a prepared statement. It'll save you some grief. That said, if you dump the variable *right before* the query-variable, and it isn't appearing within the query, then you're doing something else that you're not showing us. It's impossible for anything else to happen. See https://3v4l.org/rB6ub for demo — Qirel, Apr 25 '19 at 16:35
Please add the solution as an answer and accept it, so others can see how you solved it :) — Alfabravo, Apr 25 '19 at 16:44
`$query = 'SELECT * FROM dinosaurs WHERE UserNum = '.(int) $usernum; ` — A.Marwan, Apr 25 '19 at 16:56

score 0 · Accepted Answer · answered Apr 25 '19 at 17:30

Here is my original code:

session_start();

if (isset($_SESSION['username'])) {
    $firstname = htmlspecialchars($_SESSION['firstname']); 
    $usernum = intval($_SESSION['usernum']);
    $username = $_SESSION['username'];
} else echo "<script> window.location.assign('uhoh.html'); </script>";
require_once 'connect.php';
require_once 'pretty_json.php';

submitQuery($conn, $json);

$conn->close();

I declared the $usernum variable in the original if statement, not globally. So when I tried to access it in the submitQuery function, it wasn't set to anything.

To solve it, I just declared the $usernum variable at the beginning of the submitQuery function.

MySQL Query Not Recognizing PHP Variable

1 Answers1