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:
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:
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.