PHP

Creating Files with PHP via fopen(), fwrite(), fclose() and copy()

On a recent project I was getting some pages that were causing PHP to run slow.  Essentially the PHP was looping through various tables in the MySQL table to build a rather complex 'Mega Menu' navigation system.  As this navigation system was needed on all the pages of the site this was causing difficultly.  The site's Content Management System allowed the site user to add items to this menu system but this was done infrequently, so continually re-building of the menu system was pretty inefficient.  Therefore I rewrote the code so that when the data underlying the navigation system was amended in the Content Management System, a new include file would be built as a permanent file rather than a dynamic file.

This required the use of some of PHP's file creation tricks, essentially fopen to open/create the file and then fwrite to write data to the file. Once the data is written to the file we close it up with fclose.

// name the file
$File = "nav.inc.php";
$Handle = fopen($File, 'w');
$data = '<nav class="sixteen columns remove-bottom">'
// PHP logic to build the navigation from the database
$data .= '</nav>';
// write the data to the file
fwrite($Handle, $data);
fclose($Handle);

You can also use copy to move a file. This was useful to move the file to the location I wanted in my server file structure.

Four for out the Door - Essential PHP Extensions to finish that project

Image Manipulation

When uploading images this handy PHP extension will allow you to manipulate images on the fly - resizing, renaming, change format and even add effects.

PHP Image Magician

PDF Generation

Generate PDF's on the fly based on the content of your MySQL database.

TCPDF

Excel Generation

Generate Excel files on the fly based on the content of your MySQL database.

phpexcel

Email

Go beyond the simple PHP mail function with swiftmailer. You can even attached your dynamically created Excel or PDF as an attachment.

swiftmailer

Not PHP but what the heck if you want a robust HTML formatted email that will work across all those pesky mail clients, phones, tablets, fridges then Zurb have these handy templates.

HTML Email Templates for Zurb

Cleaning out Smart Curly Quotes with PHP

Smart curly quotes are the bane of my life and they most commonly cause grieve when users cut and paste from Microsoft Word into a web form and you end up with the funny box shapes.

This code was on toao.net and I've just added it to a function.


function cleanCurlies($string){
 $string = str_replace(array("\xe2\x80\x98", "\xe2\x80\x99", "\xe2\x80\x9c", "\xe2\x80\x9d", "\xe2\x80\x93", "\xe2\x80\x94", "\xe2\x80\xa6"), array("'", "'", '"', '"', '-', '--', '...'), $string);
 // Next, replace their Windows-1252 equivalents.
 $string = str_replace(array(chr(145), chr(146), chr(147), chr(148), chr(150), chr(151), chr(133)), array("'", "'", '"', '"', '-', '--', '...'), $string);
 return $string;
}

Original found here: http://www.toao.net/48-replacing-smart-quotes-and-em-dashes-in-mysql

Getting Path Information with PHP

Given a true path of:

/var/www/stuff/more/file.php
PHP Output Notes
$_SERVER['DOCUMENT_ROOT']; /var/www true document root
$_SERVER['HTTP_HOST'] www.mustbebuilt.co.uk Host name
$_SERVER['PHP_SELF'] /stuff/more/file.php Path of current file

So combining $_SERVER['HTTP_HOST'] with $_SERVER['PHP_SELF'] would get the full path to the file ie:

PHP Output
$_SERVER['HTTP_HOST'].$_SERVER['PHP_SELF']; www.mustbebuilt.co.uk/stuff/more/file.php

The pathinfo() method can then be used to return an associate array of useful path information. These include:

dirname
The true path to the directory ie /var/www/stuff/more
basename
The actual file name ie file.php
extension
The file extension ie php
filename
The file name minus the extension

So for example the following would retrieve the current directory.

The following creates an array using pathinfo.

$myPathInfo = pathinfo($_SERVER['DOCUMENT_ROOT'].$_SERVER['PHP_SELF']);

... and then we have access to:

PHP Output
$myPathInfo['dirname']; /var/www/stuff/more
$myPathInfo['basename']; file.php
$myPathInfo['extension']; php
$myPathInfo['filename']; file

The Referrer

Another couple of useful tricks to get referrer data - ie from whence you came. The value of $_SERVER['HTTP_REFERER'] gives the full url of the referrer. This URL can then be feed into the parse_url method to get an array of lovely values.

$myURLInfo = parse_url($_SERVER['HTTP_REFERER']);

... and then we have access to:

PHP Access to
$myURLInfo['host']; host name ie www.myhost.com
$myURLInfo['query']; name/values pairs after the question mark ?arg=value
$myURLInfo['fragment']; anchors after the #

And an example would be:

// we arrived from http://www.myhost.com/stuff/more/file.php
$referringSite = $_SERVER['HTTP_REFERER'];
$myDomainInfo = parse_url($referringSite);
$myReferrerHost =  $myDomainInfo['host'];
// $myReferrerHost is equal to www.myhost.com

If you found these useful you might also like this post PHP String Manipulation: Retrieving Parent Directories from a URL

Getting the Video ID from a youtube URL

Here is a quick use case. If you have the URL of a youtube video but just want the v value from the query string this will do the trick.

$myURLInfo = parse_url('https://www.youtube.com/watch?v=pAFptrSuw7E');
$values = parse_str($myURLInfo['query']);
echo $v;
// output pAFptrSuw7E

... and here is a function version:

function getQSvalue($url, $val){
	$urlInfo = parse_url($url);
	parse_str($urlInfo['query']);
	return $$val;
}

echo getQSvalue('https://www.youtube.com/watch?v=pAFptrSuw7E', 'v');

Tip: See Referencing Dynamic Variables Names in PHP with Double Dollars to explain the double $$.

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

Quick PHP Function to Retrieve File Extension

As part of my occasional series on PHP string manipulation here is a quick PHP function designed to retrieve the file extension from a file path.

function getExt($file){;	
		$ext = substr(strrchr($file,"."), 1);
		return $ext;
}

If you get mixed case file extensions ie 'image1.jpg' and 'image2.JPEG' then use this variant that will return the file extension in lowercase.

function getExt($file){;	
		$ext = substr(strrchr($file,"."), 1);
		return strtolower($ext);
}

For others in this series try:

Object Oriented PHP Calendar Include

To demonstrate Objects in PHP I put together a calendar include.

Screen Shot 2014-03-05 at 21.48.35

View Demo.

The calendar was created in a PHP file to use as an include. The code was as follows:

<?php
class DisplayCal
{
	public $day;
	public $month;
	public $year;
	public $timestamp;
	private $prevYearNav;
	private $nextYearNav;
	private $prevYear;
	private $nextYear;
	private $prevMonth;
	private $nextMonth;
	private $dayOfMonth;
	private $fullMonth;
	private $noMonth;
	private $firstDayTimeStamp;
	private $dateArray;
	private $firstDay;
	private $lengthMonth;
	private $totalSlots;
	private $endSlots;
	private $addToEnd;
	private $gridLength;
	private $displayHTML;

	public function __construct($d = 0, $m = 0, $y = 0){

			if($d == 0){
				$this->day = date("j");
			}else{
				$this->day = $d;
			}
			if($m == 0){
				$this->month = date("n");
			}else{
				$this->month = $m;
			}

			if($y == 0){
				$this->year = date("Y");
			}else{
				$this->year = $y;
			}
			########## build values #############
			$this->prevYearNav= $this->year -1;
			$this->nextYearNav= $this->year + 1;
			$this->prevYear= $this->year;
			$this->nextYear= $this->year;
			$this->prevMonth= $this->month-1;
			$this->nextMonth= $this->month+1;
			if ($this->prevMonth == 0 ) {
				$this->prevMonth= 12;
			    $this->prevYear = $this->year - 1;
			}
			if ($this->nextMonth == 13 ) {
				 $this->nextMonth = 1;
			     $this->nextYear = $this->year + 1;
			}

			$this->timestamp = mktime(0,0,0,$this->month,$this->day,$this->year);
			$this->dayOfMonth = date('j',$this->timestamp);
			$this->fullMonth = date('F',$this->timestamp);
			$this->noMonth = date('n',$this->timestamp);
			$this->firstDayTimeStamp = mktime(0,0,0,$this->noMonth,1,$this->year);
			$this->dateArray = getdate($this->firstDayTimeStamp );
			$this->firstDay = $this->dateArray['wday'];
			if($this->firstDay == 0){
				$this->firstDay = 6;
			}else{
				$this->firstDay--;
			}
			$this->lengthMonth = date('t',$this->timestamp);
			$this->totalSlots = $this->firstDay + $this->lengthMonth;
			$this->totalSlots--;
			$this->endSlots = $this->totalSlots % 7;
			$this->addToEnd = 7 - $this->endSlots;
			$this->gridLength = $this->totalSlots + $this->addToEnd;

	}

	public function displayCal(){

		$this->displayHTML = "<table>";
		$this->displayHTML .= "<tr><td colspan=\"7\" class=\"center\">{$this->fullMonth} - {$this->year}</td></tr>";
		$this->displayHTML .= "<tr class=\"mainCal\"><td>M</td><td>T</td><td>W</td><td>T</td><td>F</td><td>S</td><td>S</td></tr>";

		for ($i=0; $i<$this->gridLength; $i++) {
			if(($i % 7) == 0 ){
				$this->displayHTML .=  "<tr>";
			}
			if($i < $this->firstDay || ($i) > ($this->totalSlots)){
				$this->displayHTML .=  "<td>-</td>";
			}else{
				if(($i - $this->firstDay + 1) == $this->day){
					$this->displayHTML .=  "<td><span class=\"pickedDay\">". ($i - $this->firstDay + 1) . "</span></td>";
				}else{
					if(($i % 7) == 6){
						$this->displayHTML .=  "<td class=\"sun\">". ($i - $this->firstDay + 1) . "</td>"	;
						}else{
						$this->displayHTML .=  "<td>". ($i - $this->firstDay + 1) . "</td>";
					}
				}
			}
			if(($i % 7) == 6 ){
				$this->displayHTML .=  "</tr>";
			}
		}

		$this->displayHTML .= "<tr class=\"mainCal\">";
		$this->displayHTML .= "<td><a href=\"{$_SERVER['PHP_SELF']}?month={$this->month}&year={$this->prevYearNav}\">&lt;</a></td>";
		$this->displayHTML .= "<td colspan=\"5\">Years</td>";
		$this->displayHTML .= "<td><a href=\"{$_SERVER['PHP_SELF']}?month={$this->month}&year={$this->nextYearNav}\">&gt;</a></td>";
		$this->displayHTML .= "</tr>";
		$this->displayHTML .= "<tr class=\"mainCal\">";
		$this->displayHTML .= "<td><a href=\"{$_SERVER['PHP_SELF']}?month={$this->prevMonth}&year={$this->prevYear}\">&lt;</a></td>";
		$this->displayHTML .= "<td colspan=\"5\">Months</td>";
		$this->displayHTML .= "<td><a href=\"{$_SERVER['PHP_SELF']}?month={$this->nextMonth}&year={$this->nextYear}\">&gt;</a></td>";
		$this->displayHTML .= "</tr></table>";

		return $this->displayHTML;

	}

}
?>

This was then included on the page with:

<?php
include('table-cal.php');

if(!isset($_GET["day"])){
	$_GET["day"] = date("j");
}
if(!isset($_GET["month"])){
	$_GET["month"] = date("n");
}
if(!isset($_GET["year"])){
	$_GET["year"] = date("Y");
}
$cDay = $_GET["day"];
$cMonth = $_GET["month"];
$cYear = $_GET["year"];

$obj = new DisplayCal($cDay, $cMonth, $cYear);

echo $obj->displayCal();

?>

And the resultant table was styled up with the following CSS:

table{
	font-family: Helvetica, arial, sans-serif;
	font-size: 13px;
	border-collapse: collapse;
	border: 1px solid #C24704;
	box-shadow: 4px 4px 4px #555;
}
th, td{
	padding:5px;
}
tr:nth-of-type(odd){
	background-color:#AD6C49;
	color:#fff;
}
.center{
	text-align:center;
}
tr.mainCal td{
	width:30px;
	text-align:center;
}
table a:link{
	text-decoration:none;
	color:#333;
}
table a:hover{
	color:#6C3;
}
.pickedDay{
	border:1px solid #f00;
	display:inline-block;
	padding:5px;
}
td.sun{
	color:#ff0000;
}

Now how many days is it until Christmas?

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