SQL

It Must Be Built with some Data in it

Handling umlauts (ö), accents (é) and other characters with mysqli / PHP

Quick tip post.   If you have set your data in MySQL to be utf-8 and have special characters such as umlauts (ö) and accents (é) then ensure you add the following:


mysqli_set_charset($mysqli, "utf8");

This could be added to your connection information as follows:


$mysqli = new mysqli($hostname, $username, $password, $database);
mysqli_set_charset($mysqli, "utf8");

This sets the character set used in the msqli method to utf8.  If this isn't done then data containing some special characters will be returned as null.

Dynamic Drop Down with PHP and mysqli

When you have a database make it work for you. This example shows how to use a database to create a simple HTML drop down list.

Assume we have a table such as:

Film Name Film Certificate
Tropic Thunder 15
TRON: Legacy PG
Inception 12
The Tourist 12
The Shawshank Redemption 15
The Usual Suspects 18
In Bruges 18
Hurt Locker 15
L.A. Confidential 18
WALL-E U

Using SQL DISTINCT we could acquire a list of the film certificates that feature in the data.

SELECT DISTINCT filmCertificate FROM movies ORDER BY filmCertificate

This would produce.

12
15
18
PG
U

From this data we would like to produce a drop down as follows:

<select name="filmCert">
<option value="12">12</option>
<option value="15">15</option>
<option value="18">18</option>
<option value="PG">PG</option>
<option value="U">U</option>
</select>

Tip: See this page for the a revision of how the HTML select and option are used to create a drop-down.

Now for the PHP. First I'll include a connection to the database.

<?php
$hostname = "myserver.com";
$user = "mylogin";
$password = "mypassword";
$connection = mysqli_connect($hostname, $user, $password);
?>

With the above saved in an 'includes' folder as 'conn.inc.php' we can now write our SQL.

include_once('includes/conn.inc.php');
$queryFilmCerts = "SELECT DISTINCT filmCertificate FROM movies ORDER BY filmCertificate";
$resultFilmCerts = $mysqli->query($queryFilmCerts);

With the results now stored in $resultFilmCerts we can loop the results and echo a HTML <option> tag.

        <select name="filmCert">
		<?php
			while ($rowCerts = $resultFilmCerts->fetch_assoc()) {
				echo "<option value=\"{$rowCerts['filmCertificate']}\">";
				echo $rowCerts['filmCertificate'];
				echo "</option>";
			}
		?>
        </select>

View Demo

Inserting Multiple Values with PDO and a Loop

Inserting multiple values with a prepare statement can be a little tedious. This solution uses PDO rather than mysqli as you can loop around the bindValue statement.

include_once('includes/pdoconn.inc.php');
$colNames = $conn->prepare("DESCRIBE movies");
$colNames->execute();
$tableFields = $colNames->fetchAll(PDO::FETCH_COLUMN);
$noFields = count($tableFields);
$filmArray = array("Argo","Argo is a 2012 historical drama thriller film directed, co-produced by, and starring Ben Affleck. This dramatization is adapted from U.S. Central Intelligence Agency operative Tony Mendez's book","argo.jpg",4,5,"PG");
$arrLen = count($filmArray);
$cols = "";
$holders = "";

for($i=1;$i<$noFields;$i++){
	$cols .= ($cols == "") ? "" : ", ";
	$cols .= $tableFields[$i];
	$holders .= ($holders == "") ? "" : ", ";
	$holders .= ":".$tableFields[$i];
}

$sql = "INSERT INTO movies ($cols) VALUES ($holders)";

$stmt = $conn->prepare($sql);

for($i=1;$i<$noFields;$i++){
	$j = $i-1;
	$field = ":".$tableFields[$i];
	$val =  $filmArray[$j];
	$stmt->bindValue($field, $val);
}
 
$stmt->execute();

The code also assumes a table structure where the first field is an auto-increment thus the loop is pushed on one so as to ignore this field. You could easily adapt this to take values from $_GET or $_POST

SQL - Query a Whole Month Based on One Date

Often with date based data, such as events listings, you may need to extract all the records that match a particular calendar month. Here all you need from the user is a month and year. Then you can let SQL do the rest, for example:

SELECT filmName, filmReleaseDate FROM films
WHERE filmReleaseDate BETWEEN '2013-06-1'
AND DATE_ADD('2013-06-1', INTERVAL 1 MONTH)

The explanation bit: the magic here is done by DATE_ADD and INTERVAL 1 MONTH.

DATE_ADD takes two parameters, the date we are starting from and then the interval in time to the new date.

DATE_ADD(startingDate, Interval-to-add)

The interval can be a range of values such as DAY, WEEK, MONTH and YEAR. So in our example we add one month to the query with:

DATE_ADD('2013-06-1', INTERVAL 1 MONTH)

PDO Cheatsheet

To accompany the mysqli cheatsheet here is a PDO cheatsheet.

Give me one record

$sql= "SELECT filmName, filmDescription FROM movies WHERE filmID = 10";
$stmt = $pdo->query($sql);
$row =$stmt->fetchObject();
echo $row->filmName;
echo $row->filmDescription;

Give me the whole lot

$sql= "SELECT * FROM movies";
$stmt = $pdo->query($sql);
while($row = $stmt->fetchObject()){
	echo $row->filmName;
}

One row from user input using prepare

$sql= "SELECT filmID, filmName, filmDescription, filmImage, filmPrice, filmReview FROM movies WHERE filmID = :filmID";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':filmID', $filmID, PDO::PARAM_INT);
$stmt->execute();
$row =$stmt->fetchObject();
//output
echo $row->filmName;

Multiple rows from user input using prepare

$filmName = "%".$_GET['filmName']."%";
$sql= "SELECT * FROM movies WHERE filmName LIKE :filmName";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':filmName', $filmName, PDO::PARAM_STR);
$stmt->execute();
$total = $stmt->rowCount();
//output
while ($row = $stmt->fetchObject()) {
// or could use
// while ( $row = $stmt->fetch( PDO::FETCH_OBJ ) ) {
	echo $row->filmName;
}

INSERT

$sql = "INSERT INTO movies(filmName,
			filmDescription,
			filmImage,
			filmPrice,
			filmReview) VALUES (
			:filmName,
			:filmDescription,
			:filmImage,
			:filmPrice,
			:filmReview)";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':filmName', $_POST['filmName'], PDO::PARAM_STR);
$stmt->bindParam(':filmDescription', $_POST['filmDescription'], PDO::PARAM_STR);
$stmt->bindParam(':filmImage', $_POST['filmImage'], PDO::PARAM_STR);
// use PARAM_STR although a number
$stmt->bindParam(':filmPrice', $_POST['filmPrice'], PDO::PARAM_STR);
$stmt->bindParam(':filmReview', $_POST['filmReview'], PDO::PARAM_STR);
$stmt->execute();

Get Last Insert ID

$stmt->execute();
$newId = $pdo->lastInsertId();

UPDATE

$sql = "UPDATE movies SET filmName = :filmName,
			filmDescription = :filmDescription,
			filmImage = :filmImage,
			filmPrice = :filmPrice,
			filmReview = :filmReview
			WHERE filmID = :filmID";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':filmName', $_POST['filmName'], PDO::PARAM_STR);
$stmt->bindParam(':filmDescription', $_POST['$filmDescription'], PDO::PARAM_STR);
$stmt->bindParam(':filmImage', $_POST['filmImage'], PDO::PARAM_STR);
// use PARAM_STR although a number
$stmt->bindParam(':filmPrice', $_POST['filmPrice'], PDO::PARAM_STR);
$stmt->bindParam(':filmReview', $_POST['filmReview'], PDO::PARAM_STR);
$stmt->bindParam(':filmID', $_POST['filmID'], PDO::PARAM_INT);
$stmt->execute();

DELETE

$sql = "DELETE FROM movies WHERE filmID =  :filmID";
$stmt = $pdo->prepare($sql);
$stmt->bindParam(':filmID', $_POST['filmID'], PDO::PARAM_INT);
$stmt->execute();

Debugging mysqli Prepare Statements - Output the SQL Statement Helper Function

To debug a mysqli prepare statement we could place the prepare statement within an if as follows:

if($stmt = $mysqli->prepare("SELECT filmID, filmName FROM movies WHERE filmID = ?")){
    $stmt->bind_param('i', $_GET['filmID']);
    $stmt->execute();
    $stmt->bind_result($filmName, $filmCert);
    $stmt->fetch();
    $stmt->close();
}else{
    echo $mysqli->error;
}

The above can help but it often you want to see the SQL that was created by the prepare statement. Unfortunately there is no easy way to do this. However, the function below might help. You can feed it with the three parameters used to build the prepare ie:

Syntax

debug_bind_param(sqlFromPrepare:string, flagsFromBindParams:string, listOfValuesAsUsedInBindParams)

Hopefully someone might find this useful:

//////////// Test Data //////////////
$_GET['filmID'] = 232;
$_GET['filmName'] = "Titanic";
$_GET['filmPrice'] = 10.99;

//////////// Helper Function //////////////
function debug_bind_param(){
	$numargs = func_num_args();
	$numVars = $numargs - 2;
	$arg2 = func_get_arg(1);
	$flagsAr = str_split($arg2);
	$showAr = array();
	for($i=0;$i<$numargs;$i++){
		switch($flagsAr[$i]){
		case 's' : 	$showAr[] = "'".func_get_arg($i+2)."'";
		break;
		case 'i' : 	$showAr[] = func_get_arg($i+2);
		break;	
		case 'd' : 	$showAr[] = func_get_arg($i+2);
		break;	
		case 'b' : 	$showAr[] = "'".func_get_arg($i+2)."'";
		break;	
		}
	}
	$query = func_get_arg(0);
	$querysAr = str_split($query);
	$lengthQuery = count($querysAr);
	$j = 0;
	$display = "";
	for($i=0;$i<$lengthQuery;$i++){
		if($querysAr[$i] === '?'){
			$display .= $showAr[$j];
			$j++;	
		}else{
			$display .= $querysAr[$i];
		}
	}
	if($j != $numVars){
		$display = "Mismatch on Variables to Placeholders (?)";	
	}
	return $display;
}

//////////// Test and echo return //////////////

echo debug_bind_param("SELECT filmName FROM movies WHERE filmID = ? AND filmName = ? AND price = ?", "isd", $_GET['filmID'], $_GET['filmName'], $_GET['filmPrice']);

And if that doesn't help then why not try this little tool.

Mysqli Prepare Statement Checker

Just add in the three components of the prepare - the SQL, the flags and the variables and it should build the SQL for you to debug in the likes of phpMyAdmin.

How to add a new record or update an existing one in MySQL with SQL DUPLICATE

Many web applications require the developer to add data, if none is there, but amend data if some exists.

Take the example of an online survey. The user answers a question, and in our SQL we use INSERT to add the data to MySQL.

The first time a record is added we could use 'auto-increment' in MySQL to add a primary key.

The user now wants to edit that value - so in our SQL we would use UPDATE.

The UPDATE statement would need to know the primary key of that record.

These two tasks can be managed by one set of SQL. Here we can deploy the SQL command DUPLICATE to spot a duplicate record, and then use UPDATE rather than INSERT.

INSERT INTO surveyResults(id, answer2) VALUES ('Brill', 'Poor')
ON DUPLICATE KEY UPDATE answer = 'Brill';

In the above code sample the 'id' is the 'surveyResults' primary key. If no duplicate 'id' value found then the INSERT statement runs, if a duplicate is spotted then the UPDATE statement runs.

Amending Records in MySQL with CONCAT

Concatenation - such a wonderful word to say. Also a real get out of jail card when manipulating data in your MySQL database.

Take a scenario where you want to create a 'fullname' field from a 'firstname' and 'surname' field. This can be achieved with the CONCAT command.

The syntax is

CONCAT('string1', 'string2', 'string3') etc

A SQL SELECT statement could be produced such as

SELECT CONCAT(firstname, ' ', surname)
AS fullname FROM staff;

This would concatenate the 'first name' and 'surname' fields together with a white space.

You can also use CONCAT with UPDATE statements to amend records. For example the following would append 'Mr' to all the firstname fields that were flagged as Male.

UPDATE staff SET first name = CONCAT('Mr ', first name)
WHERE gender = 'M';

Hope this provides an excuse for dropping the odd concatenation into your casual conversation.

MySQLi Cheatsheet

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();

How to Change VAT from 17.5% to 20% with SQL

VAT goes up from 17.5% to 20% today (4 Jan 2011). If you are running a MySQL database with prices in it then this may be useful. My tables had prices in them that already included VAT at 17.5%, so to update to the new rate I ran two queries. First set the price back to non-VAT.

UPDATE stock SET price = price / 1.175

And then run the prices through the maths to update to the new rate of 20%.

UPDATE stock SET price = price * 1.2

A lovely New Year's present from good old Nick Clegg and David Cameron.