How do prepared statements even work?

[Aeonix]

$city = "Amersfoort";

if ($stmt = $mysqli->prepare("SELECT District FROM City WHERE Name=?")) {
    $stmt->bind_param("s", $city);
    $stmt->execute();
    $stmt->bind_result($district);
    $stmt->fetch();
    printf("%s is in district %s\n", $city, $district);
    $stmt->close();
}

This brings many questions:
- What is s on the line 4? Do I need to bother with that?
- What if I needed two conditions on SQL query? SELECT something FROM table WHERE x=? OR y=??
- Why bind_param("s", $city) and not bind_param("daniweb", $city)? How does that affect query anyhow if the only usage is below when echoing?

I've been told to used "bind params" in other topic on here. But I don't understand it.

[cereal]

Hi, s stands for string, if you want two conditions, for example a string and a digit you will write:

$city = 'Aurora';
$state_id = 1;
bind_param("si", $city, $state_id)

Read the documentation to see the data types you can define:

• http://php.net/manual/en/mysqli-stmt.bind-param.php

[Zagga]

Hi, CEREAL posted while I was typing, but expanding what he already said ...

Considering your first query:
$stmt = $mysqli->prepare("SELECT District FROM City WHERE Name=?")
you use bind_param to state that the first parameter (the ? in the query) type is a string ("s") and the value of that string is $city.
$stmt->bind_param("s", $city);

If there are multiple parameters like in your second query:
$stmt = $mysqli->prepare("SELECT something FROM table WHERE x=? OR y=?")
you extend the bind_param statement:
$stmt->bind_param("si", $x, $y);
This defines the first parameter as a string with the value $x and the second parameter is an integer with the value $y

See http://php.net/manual/en/mysqli-stmt.bind-param.php for more details and a list of the 4 parameter types.

Note (I have added backticks ` to your query to ensure the table name and field names are recognised as such. This prevents trouble if one of these names is the same as one of the 'special words' (see: http://dev.mysql.com/doc/refman/5.6/en/replication-features-reserved-words.html ).

[Aeonix]

Read the documentation to see the data types you can define: http://php.net/manual/en/mysqli-stmt.bind-param.php

Once again, I've been to manuals, this is where I got this example from :D

But yea, I see now, in bind_param I define the types in first string, and then pass the references for each as argument.

But what is $stmt->bind_result($district); does it just store result in $district?

Is this correct:

    $stmt = $mysqli->prepare("SELECT * FROM `members` WHERE username=? OR email=?");
    $stmt->bind_param("ss", $username, $email);
    $stmt->execute();

    if ($stmt->num_rows > 0) {
        echo "User found!"
    }
    $stmt->close();

?

[cereal]

But what is $stmt->bind_result($district); does it just store result in $district?

Yes, but only for the current result pointer.

Is this correct:

You have to store the result, otherwise you get 0.

[Aeonix]

$stmt = $dbconn->prepare("SELECT * FROM `members` WHERE username=? AND password=?");
$stmt->bind_param("ss", $username, $password);
$stmt->execute();
$dbresult = $stmt->fetch();
echo $dbresult; // null
$stmt->close();

Any ideas? I think I store variable, but I don't apparently... could you lend me a hand? ;)

[Aeonix]

var_dump($stmt->fetch()) returns true. LOL. No s#!t Sherlock!

How do I bind resulted array into the bind_param so far rifling through internet forces me to create external function. But there must be smaller way.

mysqli_stmt::get_result() and many others are "called to undefined method"...

[cereal]

After the execute() do:

$stmt->store_result();

Then you can call $num_rows:

$stmt->execute();
$stmt->store_result();
if ($stmt->num_rows > 0) {

http://php.net/manual/en/mysqli-stmt.num-rows.php

[Aeonix]

And what about getting results?
Also, I started new topic here https://www.daniweb.com/programming/web-development/threads/504756/return-array-from-prepared-statement
Since it seems to be seperate problem.

[diafol]

This is why I use PDO for general stuff :)