PHP and MySQL coding tips

The great “PHP and MySQL coding tips” thread has been restructured. The posts from the [thread=54074]old thread[/thread] have been sorted into four categories and are quoted below, with a link to the original post. Not all posts have been quoted.

Before you create a new tip, please make sure it’s not already in here.

Please do not create fluff posts. Instead of a thank you post here, send the person a Private Message (PM).

It is Falesh that has done the heavy work here pulling the original thread apart. We would like to say a really big thank you for doing it. Great work! Send him a [url=http://www.sitepoint.com/forums/private.php?do=newpm&u=130952]PM and say thanks!

Strings

[RULE=50%]Red[/RULE]
[RULE=60%]Red[/RULE]

[CENTER]As of PHP version 5.3.0 the POSIX Regular Expression functions have been Deprecated and should no longer be used.
ereg_replace and eregi_replace
ereg and eregi
split and spliti
sql_regcase
http://php.net/manual/en/reference.pcre.pattern.posix.php

Differences from POSIX regex

As of PHP 5.3.0, the POSIX Regex extension is deprecated. There are a number of differences between POSIX regex and PCRE regex. This page lists the most notable ones that are necessary to know when converting to PCRE.
[/CENTER]
[RULE=60%]Red[/RULE]
[RULE=50%]Red[/RULE]

MySQL

[RULE=50%]Red[/RULE]
[RULE=60%]Red[/RULE]

[CENTER]As of PHP version 5.5 the MySQL functions have been Deprecated and should no longer be used.
all mysql_ functions
http://php.net/manual/en/migration55.deprecated.php

ext/mysql deprecation

The original MySQL extension is now deprecated, and will generate E_DEPRECATED errors when connecting to a database. Instead, use the MySQLi or PDO_MySQL extensions.
[/CENTER]
[RULE=60%]Red[/RULE]
[RULE=50%]Red[/RULE]

GLOBALS

[RULE=50%]Red[/RULE]
[RULE=60%]Red[/RULE]

[CENTER]http://php.net/manual/en/security.globals.php

Using Register Globals
Warning

This feature has been DEPRECATED as of PHP 5.3.0 and REMOVED as of PHP 5.4.0.
[/CENTER]
[RULE=60%]Red[/RULE]
[RULE=50%]Red[/RULE]

Misc.

[RULE=50%]Red[/RULE]
[RULE=60%]Red[/RULE]

[CENTER]As of PHP version 5.5 the MySQL functions have been Deprecated and should no longer be used.
all mysql_ functions
http://php.net/manual/en/migration55.deprecated.php

ext/mysql deprecation

The original MySQL extension is now deprecated, and will generate E_DEPRECATED errors when connecting to a database. Instead, use the MySQLi or PDO_MySQL extensions.
[/CENTER]
[RULE=60%]Red[/RULE]
[RULE=50%]Red[/RULE]

[RULE=50%]Red[/RULE]
[RULE=60%]Red[/RULE]

[CENTER]As of PHP version 5.5 the MySQL functions have been Deprecated and should no longer be used.
all mysql_ functions
http://php.net/manual/en/migration55.deprecated.php

ext/mysql deprecation

The original MySQL extension is now deprecated, and will generate E_DEPRECATED errors when connecting to a database. Instead, use the MySQLi or PDO_MySQL extensions.
[/CENTER]
[RULE=60%]Red[/RULE]
[RULE=50%]Red[/RULE]

My tip (I noticed nobody ever wrote this in any articles anywhere which gave me lots of problems until I found it out my self):

When using mysql_real_escape_string() or addslashes() it changes ’ and " to \’ and \" which we all know, right? But when adding the variables to the mysql database it doesnt actually put the backslashes into the table. You DONT need to use strip slashes when bringing data out of the table because the backslashes arent added!

Probably sounds like I’m stupid, but there is not one article that I learned from which told me “the backslashes arent actually added to the database”.

Well, the name of the thread is “PHP and MySQL coding tips” but I see many people submitting code that can be useful. And now the debug class I’m using in conjunction with the above db class and the q() function:

class debug
{
	function explain($var, $echo = true)
	{
		$out = '<table border="1" cellspacing="0" cellpadding="3" style="text-align:left;margin:1em;">';
		$type = gettype($var);

		switch ($type)
		{
			case 'boolean':
				$var = ($var?'true':'false');
			case 'integer':
			case 'double':
			case 'NULL' :
			case 'string':
				$out .= '<tr><th style="color:#a71">'.$type.' :</th><td>'.htmlspecialchars($var).'</td></tr>';
				break;
			case 'array':
				$out .= '
					<tr>
						<th colspan="2" style="color:#a71">ARRAY</th>
					</tr>
					<tr style="color:#196">
						<th>KEY</th>
						<th>VALUE</th>
					</tr>';
				foreach ( $var as $key => $value )
				{
					$out .= '<tr><th>'.htmlspecialchars($key).'</th><td>'.nl2br(htmlspecialchars($value)).'</td></tr>';
				}
				break;
			case 'unknown type':
				$out .= '<tr><th colspan="2" style="color:#a71">'.$type.' :</th></tr>';
				break;
			case 'object':
				$className = get_class($var);
				$vars = get_class_vars($className);
				$methods = get_class_methods($className);
				$out .= '
				<tr>
					<th colspan="2" style="color:#a71; text-align:center;">'.$type.'</th>
				</tr>
				<tr>
					<th>instance of :</th>
					<td>'.$className.'</td>
				</tr>
				<tr>
					<th colspan="2" style="color:#966;">PROPERTIES :</th>
				</tr>
				<tr>
					<td colspan="2">'.(empty($vars)?'none':debug::explain($vars,false)).'</td>
				</tr>
				<tr>
					<th colspan="2" style="color:#36b;">METHODS :</th>
				</tr>
				<tr>
					<td colspan="2">'.(empty($methods)?'none':debug::explain($methods,false)).'</td>
				</tr>
				';
				break;
			case 'resource':
				$out .= '
				<tr>
					<th colspan="2" style="color:#a71;text-align:center;">'.$type.'</th>
				</tr>
				<tr>
					<th>'.get_resource_type($var).' :</th>
					<td>'.$var.'</td>
				</tr>';
				break;
		}

		if ( $type == 'string' && function_exists($var) )
			$out .= '<tr><th colspan="2" style="color:#36b">also a function name</th></tr>';

		if ( $type == 'string' && class_exists($var) )
			$out .= '<tr><th colspan="2" style="color:#36b">also a class name</th></tr>';

		$out .= '</table>';

		if ( $echo )
			echo $out;

		return $out;
	}

	function dbgWriteDb($msg)
	{
		db::q( 'insert into debug values(null, &#37;se)', $msg );
	}

	function showMysqlError($where = 'screen')
	{
		$err = mysql_error();
		if ( $where == 'db' )
			debug::dbgWriteDb('last mysql error : '.$err);
		else
			echo $err;
	}
}

The explain() function is a nice replacement for native functions like var_dump() etc. It can explain all type of variables in a better format. It returns the output and if $echo is true, which is the default value, it also prints to the screen.

dbgWriteDb() -> This one obviously writes the input value to a table including two columns : an id and a message of type text.

And the showMysqlError(). You will need the function mysql_error() frequently in case of errors. And there can be times that it is impossible to output the error to the screen. One example is a custom class that uses the database as the session storage instead of the default file system. Since some session job have to happen before any output sent, if there is a database error, you cannot see it on your screen. Instead, you can direct it to a database table. This function writes the error either to the screen (default) or to the database.

Examples:

debug::explain($_SERVER);

prints all $_SERVER variables to the screen in a nice format.

debug::showMysqlError('db');

records the last MySQL error to the database.

This might have been mentioned before, but I didn’t see it. Anyways, it’s a small performance increase trick - unnoticeable in 99% of the cases, but could make a bit of difference in very large apps. echo actually takes parameters, unlike print, which means if you are using it to print out strings and variables, you don’t have to concatenate, you can do it like this:

echo "Hi, my name is ", $name, " and I live in ", $town, ".";

Which is actually just a tid bit faster than using .

[construed from the php manual]

if you have a simple statement with something that needs to happen if some criteria is set, you could do this:

if ($a==1) {
$b='blue';
} else {
$b='yellow';
}

However, using ternary operators, you could shrink it down to this:

$b = $a==1 ? 'blue' : 'yellow';

Hi guys. Here is a simple function I often use to format text from textarea into nice XHTML.

What it basically does - it removes newlines and inserts HTML paragraph tags (<p>, </p>) and breaks (<br />).

/*
Function: nl2p
Purpose: Format a plain text with newlines into an XHTML markup with paragraphs
*/
function nl2p($string) {
  $string = "<p>" . $string . "</p>";
  $string = str_replace("\\r\
\\r\
", "</p><p>", $string);
  $string = str_replace("\\r\
", "<br />", $string);
  $string = str_replace("</p><p>", "</p>\
<p>", $string);
  $string = str_replace("\\r", '', $string);
  $string = str_replace("<p></p>", '', $string);
  return $string;
}

You can also use reversed function to replace paragraphs and break tags with newlines:

/*
Function: p2nl
Purpose: Format an XHTML markup with paragraphs into a plain text with newlines
*/
function p2nl($string) {
  $string = str_replace("</p>\
<p>", "\\r\
\\r\
", $string);
  $string = str_replace("<br />", "\\r\
", $string);
  $string = strip_tags($string);
  return $string;
}

I hope some of you will find it useful :slight_smile:

Here’s how to insert an UNIX timestamp into MySQL. I learned it yesterday:

$date = $time() // This produces an UNIX timestamp
$sql = "INSERT INTO table (column) VALUES (FROM_UNIXTIME($date))"; // And this is a query you need to run

This is probably trivial to most of you but I was very surprised to find out about this approach. Until yesterday I have been keeping date and time information in database just as strings.

In addition, here is how to select an UNIX timestamp from a database:

$sql = "SELECT UNIX_TIMESTAMP(column) FROM table WHERE ..."; // You need to run this query

The most important tip any PHP beginner must have is: to turn the error reporting on when developing script. Putting this single line

error_reporting(E_ALL);

at the top of your code is a life-savier.

When error reporting is not on, PHP masks many warnings that could potentially bring down your application, and when that happens, it is hard to find the problems which are often misspelt variables and variables that have not been set or not available in that part of the code.

Equally impotant is that you take the error reporting off when you put your code in a live website.

Hope that helps somebody.

Rageh

Email Attachment Coding Tip

Just recently I’ve been trying to send an email with an attachment (an image file) using php. I know how to send an email with no attachment so this was new to me and after a few days work and looking at other scripts I’ve came up with the code below. I had a look at Phpmailer after so many attempts and failing. The script worked a treat but I did wondered how?

After looking through the script it became clear that more than one random hash code was needed. This does not mean two different codes had to be randomly made but (as you will see below) before the codes different names can be used to split them both apart. In this case “b1_” and “b2_” are used.


// Send an email with the fileattached
$file_dir = "images"; // image directory
$file_name = "myimage.jpg"; // file

$file = fopen($file_dir.'/'.$file_name,'rb');
$data = fread($file,filesize($file_dir.'/'.$file_name));
fclose($file);
 
$data = chunk_split(base64_encode($data));
                 
$email_from = "from@website.com"; // Who the email is from 
$email_to = "to@website.com"; // Who the email is too
$email_subject = "Subject/Title Goes here"; //The subject

$random_hash = md5(date('r', time())); 

// Now build up the email starting with the headers
$headers = "From: from@website.com\\r\
Reply-To: to@website.com\\r\
"; 
$headers .= "MIME-Version: 1.0
Content-Type: multipart/mixed;
    boundary=\\"b1_{$random_hash}\\"";

$email_message = "--b1_{$random_hash}
Content-Type: multipart/alternative;
    boundary=\\"b2_{$random_hash}\\"

--b2_{$random_hash}
Content-Type: text/plain; charset = \\"iso-8859-1\\"
Content-Transfer-Encoding: 8bit


Email message goes here.

    
";

$email_message .= "--b2_{$random_hash}--
--b1_{$random_hash}
Content-Type: image/jpeg; name=\\"{$file_name}\\"
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename=\\"{$file_name}\\"

{$data}--b1_{$random_hash}--";            
    
mail($email_to, $email_subject, $email_message, $headers);

The above code shows it starts with “b1_” first which is for the file attachment then the email message then ends “b1_” and starts “b2_” for the text part of the email. After the message ends “b2_” is ended then “b1_” is started again for the file attachment. Each code uses the same random code throughout.

The content type can be changed to suit the file being attached.

spence

Email() & Hotmail

If you are having trouble with sending email with mail() to hotmail, then please try the following:

  1. Log into your Cpanel and open your webmail (you will need an email account thats been created in the Cpanel), create and send an email to your hotmail account. If no email arrives (and it’s fairly quick) then it’s your hotmail account.
  2. Open your hotmail account through your web browser. Mine has the ‘Options’ tab on the right handside, click that to reveal the drop down menu. The last tab is another link called ‘More Options’, click this to open up another page.
  3. Under the heading ‘Junk E-mail’ click onto the link ‘Filters and reporting’, now choose ‘Standard’ (if it’s not already chosen) under the junk e-mail filter heading.
  4. Now try again to send an email through your webmail account.

If the above fails, try the lower setting in your junk e-mail filter, other than that it will tell you if your ip address for your website is blocked, it’s at this point to contact the webhost.

Single or Double quotes for strings?

Here are the results of combing through the top ten search results for php code conventions, as they apply to quoting strings.

  1. Zend Framework - Coding Style
    When a string is literal (contains no variable substitutions), the apostrophe or “single quote” should always be used to demarcate the string

  2. PHP Coding Standard (dagblato.no)
    [no preference]

  3. Stack Overflow - Which coding conventions to follow for PHP
    For PHP, i’d suggest to follow Zends suggestions [as in #1]

  4. OrangeHRM PHP Coding Standards
    When a string is literal (contains no variable substitutions), the apostrophe or “single quote” should always be used to demarcate the string

  5. PEAR Coding Standards
    [no preference]

  6. PHP Chapter Conventions
    [unrelated]

  7. Drupal Coding Standards
    Drupal does not have a hard standard for the use of single quotes vs. double quotes. Where possible, keep consistency within each module, and respect the personal style of other developers.

With that caveat in mind: single quote strings are known to be faster because the parser doesn’t have to look for in-line variables. Their use is recommended except in two cases:

[list][]In-line variable usage, e.g. “<h2>$header</h2>”.
[
]Translated strings where one can avoid escaping single quotes by enclosing the string in double quotes. One such string would be “He’s a good person.” It would be 'He\‘s a good person.’ with single quotes. Such escaping may not be handled properly by .pot file generators for text translation, and it’s also somewhat awkward to read.[/list]

  1. cartoweb .org Code Convention
    Coding Style - Developers should use the PEAR coding standards as the coding style reference in CartoWeb [which state] Things like “single quotes vs double quotes” are features of PHP itself to make programming easier and there are no reasons not use one way in preference to another. Such best practices are left solely on developer to decide.

  2. TuVinh PHP Coding Convention
    [no preference]

  3. evolt.org PHP coding guidelines
    Strings in PHP can either be quoted with single quotes (’’) or double quotes (""). The difference between the two is that the parser will use variable-interpolation in double-quoted strings, but not with single-quoted strings. So if your string contains no variables, use single quotes and save the parser the trouble of attempting to interpolate the string for variables

Summary
Prefer single quotes: Zend, Stack Overflow, orangeHRM, Drupal, Evolt
No preference: Dagblato, Pear, Cartoweb, TuVinh
Prefer double quotes: none

Handling Input and Output

Due to the demise of magic quotes, we need to change how we approach and handle global values from places such as _GET and _POST.
Here’s what we’re going to be looking at:

[list=1][]Getting the values
[
]Disabling magic quotes
[]Handling values
[
]Protect the database
Output to the page[/list]

1. Getting the values

You can use one of the two following sets of code to retrieve a value:


// retrieve a value without filter_input
$email = '';
if (isset($_GET['email'])) {
    $email = $_GET['email'];
}


// filter_input can be used from PHP 5.2 onwards
$email = filter_input(INPUT_GET, 'email');

The benefit of using filter_input is that you can also apply [url=“http://www.php.net/manual/en/filter.filters.php”]filters to the values. For example, with an email address there is the FILTER_SANITIZE_EMAIL filter, which removes all characters except letters, digits and !#$%&’*±/=?^_`{|}~@..


$email = filter_input(INPUT_GET, 'email', FILTER_SANITIZE_EMAIL);

2. Disabling magic quotes

None of the above yet protects your code from potentially malicious input. Up to PHP 5.3 it’s magic quotes that attempted to provide the protection, but they were flawed. The slashes that magic quotes added, are normally stripped out in favour of using mysql_real_escape_string or prepared statements, which provide better protection. As of PHP 5.3 the magic quotes are officially deprecated. In PHP 6.0 they won’t exist at all. This means changing your mindset so that the code you write now, will have a better chance to be issue-free later on.

We now need to approach our code with the assumption that magic quotes are no longer active. If they just-so-happen to be enabled, you can remove the added slashes from the values so that you don’t run the risk of double-escaping the values. Also, if your code is going to be run in an unknown environment, you can apply added protection so that it still works as-per-normal, or even dies with an appropriate error message.


if (get_magic_quotes_gpc()) {
    $email = stripslashes($email);
}

If you don’t want to perform such checks for each variable, you can instead exit out of your code stating that “no magic quotes” is a requirement for your code. That way you can ensure that your code only runs in an environment that doesn’t have magic quotes.

When it comes to disabling magic quotes, there are many techniques available. My favourite being to disable them completely at the server.

The preferred order of preference for disabling magic quotes is:


                  PHP 5.2  PHP 5.3  PHP 6.0
php ini settings     1       N/A      N/A
.htaccess            2        1       N/A
runtime code         3        2       N/A

3. Handling values

The values that you now have must still be considered to be untrusted, and potentially dangerous. When these values are passed to the database they may contain attempted SQL Injection code, and when passed to the web page they may contain XSS code. Your code needs to now treat them as untrusted values, as they came from an untrusted source, the user. This is not to say that they will contain bad values. It’s only to say that there exists the potential for bad values.

While there are some ways to protect against bad values when getting input values, the only effective way to provide proper protection is to make sure that the values are safe during the output process, whether that be to the database, the web page, or other places like email, XML, files, url, etc.

4. Protect the database

[RULE=50%]Red[/RULE]
[RULE=60%]Red[/RULE]

[CENTER]As of PHP version 5.5 the MySQL functions have been Deprecated and should no longer be used.
all mysql_ functions
http://php.net/manual/en/migration55.deprecated.php

ext/mysql deprecation

The original MySQL extension is now deprecated, and will generate E_DEPRECATED errors when connecting to a database. Instead, use the MySQLi or PDO_MySQL extensions.
[/CENTER]
[RULE=60%]Red[/RULE]
[RULE=50%]Red[/RULE]

Sending values to the database is fraught with issues, but if you ensure that your database values are escaped only once, you should be safe. The appropriate ways to do that are to use mysql_real_escape_string at the database query itself, as in the example on the mysql_real_escape_string documentation page, or by using prepared statements with for example, [url=“http://www.php.net/manual/en/mysqli.prepare.php”]mysqli_prepare.

5. Output to the page

Functions such as htmlspecialchars and [url=“http://www.php.net/manual/en/mysqli.prepare.php”]htmlentities are useful for outputting values to the page. The former only converts ampersands, double quotes and angled brackets, which provides a useful minimum of protection. The latter function converts every single character that has an html entity equivalent, which can sometimes be considered to be too heavy-handed.


echo 'An email has been sent to you at ' .
   '<strong>' . htmlspecialchars($email) . '</strong>';