ArchivesPHP and MySQLi Tutorials of

MySQLi, what’s the difference between standard queries and prepared statements?

You may have heard of prepared statements, but don’t quite understand the differences. In this post I’m going to try and help you with that. Well, with your standard query, you would get your information, escape it for safety, build your query and execute.

<?php
/* instantiate our class, and select our database automatically */
$sql = mysqli('localhost','user','password','database');
 
/*
let's assume we've just received a form submission.
so we'll receive the information, and we'll escape it
*/
$name  = $sql->real_escape_string($_POST['name']);
$age   = $sql->real_escape_string($_POST['age']);
$email = $sql->real_escape_string($_POST['email']);
 
/* build the query, we'll use an insert this time */
$query = "INSERT INTO `tablename` VALUES ('$name','$age','$email');";
 
/* execute the query, nice and simple */
$sql->query($query) or die($query.'<br />'.$sql->error);
?>

So, what is an advantage to doing this? An advantages is you can view the full query prior to running it, simply by echoing it out. How does this help? It lets you make sure that your query is building properly before you start executing it.
What is a disadvantage? Well, you have to escape each string for security purposes, that takes a little more time than some people may like to spend.

And how about prepared statements?

<?php
/* instantiate our class, and select our database automatically */
$sql = mysqli('localhost','user','password','database');
 
/*
let's assume we've just received a form submission.
so we'll receive the information, and we'll escape it
this step is not necessary, but why not.
*/
$name  = $_POST['name'];
$age   = $_POST['age'];
$email = $_POST['email'];
 
/* build the query, we'll use an insert this time */
$query = $sql->prepare("INSERT INTO `tablename` VALUES ('?','?','?');");
 
/*
bind your parameters to your query
in our case, string integer string
*/
$query->bind_param("sis",$name,$age,$email);
/* execute the query, nice and simple */
$query->execute();
?>

The beauty of these prepared statements is there are fewer steps, and you need not escape the parameters because the bind_param() will do that for you.

So prepared statements are best suited for queries that take user input, such as an INSERT or UPDATE query, or a DELETE or SELECT with a WHERE clause.

Hope that helps.

By Mike on April 16, 2011 | MySQLi, PHP
Tags: ,

PHP Tutorial: Ternary Logic, (?:) shorthand if/else

So you may have been looking through a PHP script you downloaded and seen a bit of logic that you didn’t even know was possible. What you probably saw was Ternary Logic, it had a question mark and a colon (?:) in use, making it more confusing for you. Well not any more, here comes the clarifier. The easiest way to do this is take a long hand if/else and then create a ternary logic statement to match it. So let’s try that.

<?php
/* we'll set a $gender variable, typically you will already
 have your variable set elsewhere */
$gender = 'male';
 
/* so we'll create a basic if/else */
if ( $gender == 'male' ) {
    $title = 'Mr';
} else {
    $title = 'Mrs';
}
echo $title;
/* that makes sense right? */
/* we can shorten that easily, using ternary logic */
$title = ($gender == 'male' ? 'Mr' : 'Mrs');
echo $title;
/* Also instead of putting our result into a variable, we can just
as easily put it in-line with our code, if we want to */
echo  "Hello " .($gender == 'male' ? 'Mr' : 'Mrs'). ". Smith, how are you?";
/* in our case this will output: "Hello Mr. smith, how are you?" */
 
?>

So as you can see the ternary logic form, if you were to read it as english, would read something like “$title gets the value of ‘Mr’ if $gender is equal to male, and ‘Mrs’ if $gender is not male.”

“Ok, that’s fine, but what if I want to return a true or false value?” Well that’s even easier! Let’s try it:

<?php
/* explicitly tell PHP to return a true or false */
$result = (1+1==2 ? true : false);
 
/* or imply that you want a true or false like so: */
$result = (1+1==2);
echo ($result ? '$result is true' : '$result is false');
 
?>

The above second example $result will set $result to have the return value of the expression (1+1==2) which, as far as I know is true.

And there we go, a very basic example of ternary logic, practice makes you better, so practice lots. I hope you found this helpful.

By Mike on April 1, 2011 | PHP, Tutorials
Tags: , , , ,