NULL value

Take a look at this code:

if($stmt = $connection->prepare("SELECT usertype FROM users WHERE email=? "))
     {
         $stmt->bind_param('s',$email);
         $stmt->execute();
         $stmt->bind_result($usertype);
         $stmt->store_result();
    
                  
         var_dump($usertype);return;//this gives null

the above SQL statement runs OK in the netbeans console but here in the prepared statement NULL is returned and I cannot understand why.
Any ideas?

I’m not up on mysqli, but don’t you need to call fetch() to actually get the results into $usertype? The doc suggests that store_result just buffers the results so that when you call fetch() they come from the buffer rather than from the database engine.

1 Like

I never mentioned anything about fetch()…which by the way IS the solution to my problem after all.
Thanks.

you can only fetch from a mysqli prepared statement if you call the get_result() method first, for which you need to have PHP compiled with the mysqlnd.

Yes, that’s what I meant - you’ve assigned a variable for the row value to be placed in, but you haven’t called fetch() to actually retrieve a row. Hence your variable is NULL until you do.

This statement is not true and is irrelevant to the question asked.
One cannot fetch an array without get_result, but nowhere the OP is doing so.

A note which is irrelevant to your current question but important by itself.
A condition before $stmt = $connection->prepare is superfluous and rather harmful, depends on the code in the else clause. Either way, better avoid this extra condition, and configure mysqli to report errors by itself instead.

@designtrooper
Close, but you have to switch some things around and add a few things. You have to move bind_result after store_result and you have to put the results within a while loop.

Like so

$sql = "SELECT usertype FROM users WHERE email = ?";
if($stmt = $connection->prepare($sql)) {

	$stmt->bind_param('s',$email);
	$stmt->execute();
	$stmt->store_result();

	if($stmt->num_rows) {

		$stmt->bind_result($usertype);

		while($stmt->fetch()) {

			var_dump($usertype);return;//this gives null

		}

	} else {

		// No data

	}

}

Take a look at the code again,with the else clause now and tell me what you think:

 if($stmt = $connection->prepare("SELECT...)"))
   {...}
else
 {  $connection->error.);return  false; }

@designtrooper, beware, this code adds nothing useful to one you have already, yet it will bloat it for no reason.

This one is extremely harmful, as it does nothing, and therefore makes you unaware of any error that may happen. Like I said above, you should avoid such a condition. All the code you need is

$stmt = $connection->prepare("SELECT usertype FROM users WHERE email=? ");
$stmt->bind_param('s',$email);
$stmt->bind_result($usertype);
$stmt->execute();
$stmt->store_result();
$stmt->fetch();

// whatever you want to do with $usertype
var_dump($usertype);

while your mysqli should be properly configured to report errors by itself.

Note that PDO is less laborious to use. Just look - it takes two times less code to get your variable:

$stmt = $connection->prepare("SELECT usertype FROM users WHERE email=? ");
$stmt->execute([$email]);
$usertype = $stmt->fetchColumn();

// whatever you want to do with $usertype
var_dump($usertype);
2 Likes

@colshrapnel, your definition of “bloat” and “extremely harmful”, are far different than mine.

Sure the IF statement and the while loops are not necessary. But that is as far as it goes (from what I can tell). Should the call throw an exception, that exception would still bubble up.

So sure there is some unnecessary conditions being checked, but it is hardly “extremely harmful”. I’d really hate to see how you classify a code snippet that leaves an obvious SQL injection, as I personally would have used “extremely harmful” for that scenario.

Sure, one could argue the use of mysqli as “harmful”, but I’d disagree with them on that too. Why? Because the PHP Manual does not have the mysqli methods/classes marked as deprecated. Surely, if the PHP community felt the use of it was harmful, they would encourage others to not use it by eventually removing it from the language (hint: what happened with mysql). When/Should PDO be the defacto/only supported method for connecting and communicating with Databases, then I could see your argument towards code implementing mysqli as harmful to be valid. Until then, I’d encourage you to figure out better adjectives for describing your thoughts, as they are definitely in the hyperbole area at this point.

4 Likes

@designtrooper, if you need further assistance, I encourage you to open a new topic, as the end of this topic deviated quite a bit from where I think you were wanting to go.