Prepared Statements in MySQLi

[mattyd]

I posted earlier today about converting my MySQL to MySQLi - Upon further research I came across the following, Prepared Statements. It seems that this may be a good way to go but I am a bit confused about how to implement it.

I am simply taking User-entered data from a form and adding it to my database. I need to find an example of how to do this using this method.

I found the following example, but it appears to me that it is manually adding it via prepared code. Can anyone please explain explain this to me?

`

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// prepare and bind
$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);

// set parameters and execute
$firstname = "John";
$lastname = "Doe";
$email = "[e-mail]";
$stmt->execute();

`

The area of the above code example concerns the section below:

`

// set parameters and execute
    $firstname = "John";
    $lastname = "Doe";
    $email = "[e-mail]";
    $stmt->execute();

`
Would I just write one of these statements for each of the pre-existing rows in my table? That snippet looks to me that it is assigning values to the variables in advance. Am I missing something about this?

Any help would be much appreciated.

Thank you,
Matthew

[pritaeas]

I need to find an example of how to do this using this method.

https://www.daniweb.com/web-development/php/code/462098/mysqli-binding-example

Would I just write one of these statements for each of the pre-existing rows in my table?

You'd only need to prepare this statement once, and execute it after you change the bound parameters.

[mattyd]

Pritaeas:

Thank you for that link.

My one question: I will not be supplying values in advance, (The User will be doing that via a submitted form) so what do I make the variables in this case, just 0 to begin with before execution? The fields in my table are varchars and ints.

Thank you,
Matthew

[pritaeas]

Am not sure I understand, you prepare the query after the user has submitted the form. The binding for the prepare binds that variable to a specific position in the query, which gets the actual value when you call execute. Personally, I prepare with variables with null values (before a loop of execution).

[mattyd]

What I am confused about (And I have seen this in other examples) is contained in the following:

`

// you can now give values to your variables
// whether this happens now, or before the query doesn't matter,
// as long as it happens before execute.
$name = 'pritaeas';
$email = '[e-mail]';
$dob = '2013-08-30';
$level = 0;

`

In relation to $name, for example, in my MySql at this point nothing like this exists - I have this:

`

$userName = $_POST['user'];

`

I am not manually loading data into the database myself, the User is via a form, so I cannot put something like:

`

$email = '[e-mail]';

`

The variable must I assume in this case be empty in order to do what I am doing - My question is what do I put there, a zero? I hope this makes sense, if not I will try to clarify more.

Thanks.

[pritaeas]

$userName = $_POST['user'];

If it comes from a form, use that. Can you post a piece of code illustrating your flow. I think we're going in circles.

[mattyd]

This is what I am currently using:

`

function NewUser()
{

    $userName = $_POST['user'];
    $birthYear = $_POST['birthYear'];
    $email = $_POST['email'];
    $password =  $_POST['pass'];
    $countries = $_POST ['countries'];
    $state = $_POST ['state'];
    $city =  $_POST['city'];
    $zip =  $_POST['zip'];
    $company = $_POST ['company'];
    $manager1 = $_POST ['manager1'];
    $manager2 = $_POST ['manager2'];

    $query = "INSERT INTO Table4  (userName,birthYear,email,password,countries,state,city,zip,company,manager1,manager2) 
    VALUES ('$userName','$birthYear','$email','$password','$countries','$state','$city','$zip','$company','$manager1','$manager2')";

    $data = mysql_query ($query)or die(mysql_error());
    if($data)
    {
    echo "YOUR REGISTRATION IS COMPLETED...";
    }
}

`

For example, $userName = $_POST['user'];, looks very different than the example in an above post of:

`

$email = '[e-mail]';

`

I am assuming that by using prepared statements it is completely different from what I am currently using (as illustrated above).

There is a variable. It can be set to zero for an int. For a string is the variable for a prepared statement set to '' OR a ""? That is my main question.

I am a bit confused by this in general - I have done research but have not yet changed my code to see what works. I do not want to guess at it as it involves security.

Thank you.

[pritaeas]

I am assuming that by using prepared statements it is completely different from what I am currently using

No. It's not. Only lines 17-19 are different. Prepare, bind, execute.