Project after project connecting to databases. What I need is a cheatsheet for the PHP MySQLi extension to cover the most common querying scenarios. Here we go:
Give me one record
$queryFilms = "SELECT filmName, filmDescription FROM movies WHERE filmID = 10";
$resultFilms = $mysqli->query($queryFilms);
$rowFilms = $resultFilms->fetch_assoc();
// then to output
echo "<p>{$rowFilms['filmName']}</p>";
Give me the whole lot
$queryFilms = "SELECT * FROM movies ORDER BY movieName";
$resultFilms = $mysqli->query($queryFilms);
while ($rowFilms = $resultFilms->fetch_assoc()) {
echo $rowFilms['movieName'];
}
One row from user input using prepare
When you have variables coming via $_POST or $_GET ($_GET in this sample) and expect one value back.
$stmt = $mysqli->prepare("SELECT filmID, filmName FROM movies WHERE filmID = ?");
$stmt->bind_param('i', $_GET['filmID']);
$stmt->execute();
$stmt->bind_result($filmName);
$stmt->fetch();
$stmt->close();
echo $filmName;
Notice the use of the flags ‘sssdi’ in the bind_param() method and the position placeholders (?) in the prepare statement.
For a reminder as to how these relate take at look at this interactive example.
Multiple rows from user input using prepare
When you have variables coming via $_POST or $_GET ($_GET in this sample) and expect mulitple values back.
$stmt = $mysqli->prepare("SELECT filmID, filmName, filmCertificate FROM movies WHERE filmCertificate = ? ORDER BY filmName");
$stmt->bind_param('s', $_GET['filmCertificate']);
$stmt->execute();
$stmt->bind_result($filmID, $filmName, $filmCertificate);
while ($stmt->fetch()) {
echo "$filmName $filmCertificate";
}
Count number of rows with prepare
When you need to count the number of rows returned.
$stmt = $mysqli->prepare("SELECT filmID, filmName, filmCertificate FROM movies WHERE filmCertificate = ? ORDER BY filmName");
$stmt->bind_param('s', $_GET['filmCertificate']);
$stmt->execute();
$stmt->bind_result($filmID, $filmName, $filmCertificate);
$stmt->store_result();
$numRows = $stmt->num_rows;
//optional $stmt->close();
INSERT
An INSERT with prepare.
$stmt = $mysqli->prepare("INSERT INTO movies(filmName,
filmDescription,filmImage,filmPrice,filmReview) VALUES (?, ?, ?, ?, ?)");
$stmt->bind_param('sssdi', $_POST['filmName'],
$_POST['filmDescription'],
$_POST['filmImage'],
$_POST['filmPrice'],
$_POST['filmReview']);
$stmt->execute();
$stmt->close();
UPDATE
An UPDATE with prepare.
$stmt = $mysqli->prepare("UPDATE movies SET filmName = ?,
filmDescription = ?,
filmImage = ?,
filmPrice = ?,
filmReview = ?
WHERE filmID = ?");
$stmt->bind_param('sssdii',
$_POST['filmName'],
$_POST['filmDescription'],
$_POST['filmImage'],
$_POST['filmPrice'],
$_POST['filmReview'],
$_POST['filmID']);
$stmt->execute();
$stmt->close();
DELETE
Finally a DELETE statement. Like the UPDATE a WHERE clause ensure the correct record is removed.
$stmt = $mysqli->prepare("DELETE FROM movies WHERE filmID = ?");
$stmt->bind_param('i', $_POST['filmID']);
$stmt->execute();
$stmt->close();
Hey thanks for this! Just saved me a lot of head banging & hair tearing 🙂
I have one question tho… I am trying to debug some code I have and I want to see the final sql query that was passed to the execute statement as I am using prepared statements and using the ? placeholder. I tried var_dumping a couple of variables but it is not telling me anything relevant.
How do I go about this? Thanks 🙂
Hi Zoey – Thanks for your comments. You could try using the error() method of the mysqli extension. To do so place your $mysqli->prepare in an ‘if’ as follows:
if($stmt = $mysqli->prepare("SELECT filmID, filmName FROM movies WHERE filmXXID = ?")){ $stmt->bind_param('i', $_GET['filmID']); $stmt->execute(); $stmt->bind_result($filmName, $filmCert); $stmt->fetch(); $stmt->close(); }else{ echo $mysqli->error; }In this case you would get an error stating:
“Unknown column ‘filmXXID’ in ‘where clause'”
… flagging up the fact that I had the wrong field name in my SQL.
Hope that helps. You raise an interesting point though and I am going to investigate other methods to use and will put up a post about it shortly.
Hey thanks for the quick response! You were right, there was an error in the sql statement. I am using the LIKE keyword and didn’t put quotes around the placeholder ? (btw, is that the right thing to do? In terms of preventing things like SQL injection, because I thought that eliminating the quotes and using a placeholder is the method to prevent SQL injection)
Hi Zoey – you are right you don’t need to put quotes around a string when using prepared statements with mysqli. Just ensure that you have the ‘s’ flag set up in the bind_param() method. Also with a LIKE because you are using wildcards (%s) then it is a idea to concatenate yourself a variable first ie:
$stmt = $mysqli->prepare("SELECT filmID, filmName FROM movies WHERE filmName LIKE ?"); //build a variable using query value and wildcards $filmNameSearch = "%".$_GET['filmName']."%"; $stmt->bind_param('s', $filmNameSearch)Note this is a very simple example. Some security / validation against the $_GET value is always a good idea.
Hey thanks for the feedback… I just implemented this literally 2mins ago before I came here to check out other tips. Now everything is alright 🙂 thanks a lot…
Hi Zoey
Just made a new post on the debugging issue here. Includes a link to a prepare statement checker tool that you might also find useful.