0

Can someone re-write the below code as a prepared statement?

result = mysqli_query($con,"SELECT * FROM note_system WHERE note = '$cnote'") 
or die("Error: ".mysqli_error($con));

while($row = mysqli_fetch_array($result))
{
$nid = $row['id']; 

}

I am trying to learn prepared statements and am having trouble understanding how it works from the many examples I have found while searching. I am hoping that if I see some code I am familiar with re-written as a prepared statement that it might click for me. Please no PDO, that is too confusing for me at my current level of knowledge. Thanks.

ButterDog
  • 243
  • 1
  • 3
  • 10

4 Answers4

4

Hello ButterDog let me walk you through PDO step by step.

Step 1)

create a file called connect.php (or what ever you want). This file will be required in each php file that requires database interactions.

Lets start also please note my comments :

?php

//We set up our database configuration
$username="xxxxx"; // Mysql username
$password="xxxxx"; // Mysql password


// Connect to server via PHP Data Object
$dbh = new PDO("mysql:host=xxxxx;dbname=xxxxx", $username, $password); // Construct the PDO variable using $dbh
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Set attributes for error reporting very IMPORTANT!
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE); // Set this to false so you can allow the actual PDO driver to do all the work, further adding abstraction to your data interactions.
?>

Step 2) Require the connect.php please take a look : 

require ('....../........./...../connect.php'); // Require the connect script that made your PDO variable $dbh

Step 3)

to start database interactions just do the following also please read the code comments. For the moment we will not worry about arrays! Get the full gyst of PDO then worry about making it easier to work with! With repetition the "long way" comes more understanding of the code. Do not cut corners to begin with, cut them once you understand what you are doing! 

$query = $dbh->prepare("SELECT * FROM note_system WHERE note = :cnote"); // This will call the variable $dbh in the required file setting up your database connection and also preparing the query!

$query->bindParam(':cnote', $cnote); // This is the bread and butter of PDO named binding, this is one of the biggest selling points of PDO! Please remember that now this step will take what ever variable ($cnote) and relate that to (:cnote)

$query->execute(); // This will then take what ever $query is execute aka run a query against the database

$row = $query->fetch(PDO::FETCH_ASSOC); // Use a simple fetch and store the variables in a array

echo $row['yourvalue']; // This will take the variable above (which is a array) and call on 'yourvalue' and then echo it.

Thats all there is to PDO. Hope that helped!

Also take a look at this. That helped me so so much!

I also use this as a reference (sometimes) - The web site looks like crap but there is quality information on PDO on there. I also use this and I swear this is the last link! So after this any questions just ask, but hopefully this can turn into a little reference guide on PDO. (hopefully lol)

Rixhers Ajazi
  • 1,303
  • 11
  • 18
  • THANKS!!!! That makes a ton of sense to me. I am really appreciative of you taking the time to help me understand this better. Sometimes the PHP manual just makes zero sense to me. This really helps. Thanks again, I will check out that link. – ButterDog Apr 18 '13 at 00:20
  • Of course and that is why SO is here for. Every one started some where, some start understanding faster, others slower, and them some in the middle! - Lol. If you have any questions please feel free to ask at any time! – Rixhers Ajazi Apr 18 '13 at 00:21
  • Added a couple more links for you sir. I think you will find them quite useful! – Rixhers Ajazi Apr 18 '13 at 00:26
  • Enjoy and happy PDO'ing (and if done right happy not being SQLi'd) :P – Rixhers Ajazi Apr 18 '13 at 00:44
1

This is one way to do it with PDO:

$sel = $db->prepare("SELECT * FROM note_system WHERE note=:note");
$sel->execute(array(':note' => $_POST['note']));
$notes = $sel->fetchAll(PDO::FETCH_ASSOC);

See the placeholder :note in the query in line 1, which is bound to $_POST['note'] (or any other variable for that matter) in line 2.

If I want to run that query again, with a different value as :note, I'll just call lines 2 and 3.

Displaying the results:

foreach ($notes as $note) {

    echo $note['id'] . ": " . $note['text'] . "<br />";
}
michi
  • 6,565
  • 4
  • 33
  • 56
1

Use pdo:

http://php.net/manual/en/book.pdo.php

from various docs:

/* Connect to an ODBC database using driver invocation */
$dsn = 'mysql:dbname=testdb;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';

try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

$sql = 'SELECT name, colour, calories
FROM fruit
WHERE calories < :calories AND colour = :colour';
$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$sth->execute(array(':calories' => 150, ':colour' => 'red'));
$red = $sth->fetchAll();
Zak
  • 24,947
  • 11
  • 38
  • 68
  • ISn't there someway to do it without PDO? I am a new student to PHP. PDO from looking at it seems a bit over my head. – ButterDog Apr 17 '13 at 23:58
  • @ButterDog Yes. You can use `mysqli_` functions. – Kermit Apr 18 '13 at 00:05
  • @FreshPrinceOfSO isn't PDO a php extension? Can't a prepared statement be written that is not PDO? Am I just more confused than I thought? Thanks. – ButterDog Apr 18 '13 at 00:10
  • Try to think of a "prepared" statement as something that "PHP does not handle" in fact the PDO driver handles it if you construct the proper attributes. This will then further seperate your interaction with the database through mean old PHP and let the only interaction go through the "firewall" that is PDO. I hope this made some sense to you @ButterDog – Rixhers Ajazi Apr 18 '13 at 00:20
1

This should help you on the right path...

$link = mysqli_connect("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query = "SELECT id FROM note_system WHERE note = ?";

$stmt = mysqli_stmt_init($link);
if(!mysqli_stmt_prepare($stmt, $query)) {
    print "Failed to prepare statement\n";
}
else {
    $note = "mynote";
    mysqli_stmt_bind_param($stmt, "s", $note);

    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    while ($row = mysqli_fetch_array($result))
    {
        $nid = $row['id'];
    }
}

mysqli_stmt_close($stmt);
mysqli_close($link);
Kermit
  • 33,827
  • 13
  • 85
  • 121