[RESOLVED] What parameter type is NULL

halojoy

I am working with the [man]MySQLi[/man] Database Class.
I thought I would have a look at [man]MySQLi_STMT[/man] possibilities.

You do:
$stmt = mysqli->prepare(SQL)
$stmt->bind_param( type, $var )
$stmt->execute()
$stmt->bind_result( $vars .... )
$stmt->fetch()
echo $vars ....

My questioning regards what type I should set for $var with a NULL value?

http://www.php.net/manual/en/mysqli-stmt.bind-param.php
There are 4 types:

i corresponding variable has type integer
d corresponding variable has type double
s corresponding variable has type string
b corresponding variable is a blob and will be sent in packets

My tests show that both 's' and 'i' works without Warning.
And so I guess that NULL has no special type.
Or maybe can be used in ALL 4 types.
And in any sort of field in database.
And in mysqli::bind_param method corresponding to any 'type'.
Is this correct?

laserlight

halojoy wrote:
My questioning regards what type I should set for $var with a NULL value?

The type that you would have set if the variable did not contain NULL.

halojoy wrote:
And so I guess that NULL has no special type.

In PHP, the type of NULL is NULL, and considering how specially NULL values are treated in SQL, perhaps it applies for SQL as well. But this is largely academic.

halojoy wrote:
And in mysqli::bind_param method corresponding to any 'type'.
Is this correct?

This should be correct. I note that for the PDO extension, one can be absolutely explicit about this by using PDO:😛ARAM_NULL.

halojoy

thanks, laserlight
I later found out on the web, that just about what you say is the case regarding NULL.
As any type of column can be declared NULL or NOT NULL
it has to be that NULL fits in most places.
At least for MySQL.

About MySQLi $stmt: prepare-bind_param-execute-bind_result-fetch.
There lots of steps and coding lines between prepare and get result fetched.

I am sure there are situations where prepared Statements are absolutely great.
But most of the time I will use normal methodMySQLi: query-fetch
It works well.

thanks again 🙂
I mark resolved.

<?php

require 'mysqli.php';
$db = new mysqli();

//where name has 'brian'
$like  = '%brian%';
$like = $db->real_escape_string($like);

//start
$stmt = $db->prepare("SELECT name,band FROM bands WHERE name LIKE ?");
$stmt->bind_param('s', $like);
$stmt->execute();
$stmt->bind_result($name,$band);
$stmt->fetch();
$stmt->close();
echo $name.' '.$band; // Output: Brian Jones The Rolling Stones
//finished

echo '<br />';

//start
$row = $db->rowObject("SELECT name,band FROM bands WHERE name LIKE '$like'");
echo $row->name.' '.$row->band; // Output: Brian Jones The Rolling Stones
//finished

?>

laserlight

halojoy wrote:
I am sure there are situations where prepared Statements are absolutely great.
But most of the time I will use normal methodMySQLi: query-fetch
It works well.

You should not use real_escape_string() on a variable that will be bound to a prepared statement's parameter. Furthermore, looking at the PHP manual on MySQLi, I cannot find a rowObject() method. Therefore, it looks like you made an unfair comparison (e.g., either it does not work, or it is an additional method added after inheriting from mysqli, and perhaps the same could be done for a prepared statement version). It would be more correct to compare with:

//start
$like = $db->real_escape_string($like);
$result = $db->query("SELECT name, band FROM bands WHERE name LIKE '$like'");
$row = $result->fetch_object();
echo $row->name . ' ' . $row->band; // Output: Brian Jones The Rolling Stones
//finished

halojoy

You right on all 3:

No real_escape_string when using prepared bind_params statements
thanks. I did not think of this. Even if I have heard it, I think.

rowObject() is my own extension to MySQLi
= query()+res->fetch_object()+custom query error checking

[man]MySQLi[/man] supports Class extends completely well

Unfair comparison yes, it is, but your example has corrected me

Cheers!