1 2 3 4 5 6 7 8 9 10 | function convert_datetime($stamp){ list($date, $time) = explode(' ', $stamp); list($year, $month, $day) = explode('-', $date); list($hour, $minute, $second) = explode(':', $time); $timestamp = mktime($hour, $minute, $second, $month, $day, $year); return $timestamp; } |
Just stick that in somewhere and you’re good to go, this is useful when converting between SQL and more localised formats.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | if(isset($_GET["page"])){ $page = $_GET["page"]; }else{ $page=1; } $perPage = 20; $start = ($page-1) * $perPage; $results = mysql_query("SELECT * FROM people ORDER BY ID ASC LIMIT $start, $perPage"); echo "<table>"; echo "<tr><td>ID</td><td>FirstName</td></tr>"; while($row = mysql_fetch_assoc($results)){ echo "<tr>"; echo "<td>".$row["ID"]."</td>"; echo "<td>".$row["FirstName"]."</td>"; echo "</tr>"; } echo "</table>"; |
Line 1: Here we detect if a custom page is being used
Line 2: If there is we use it
Line 4: If not then jsut set to the default
Line 6: This is a variable for how many records to display per page
Line 7: A simple expression to work out which record to start at
Line 8: Get the query
And for now, that’s all you need to know! I do hope it’s useful!
In order for us to even think about using PHP and SQL we need to know the four main MySQL functions.
mysql_connect/strong>
1 2 3 4 | $link = mysql_connect('localhost', 'mysql_user', 'mysql_password'); if(!$link){ die('Could not connect: ' . mysql_error()); } |
All you need to do here is replace ‘mysql_user’ with a username, ‘mysql_password’ with a password and assuming that it will be running on the same server then it should be enough to get started with.
Then there’s:
mysql_error
As you can see above mysql_error can be used to debug code and should be used thoroughly throughout the code.
And there’s also:
mysql_select_db
1 2 3 4 5 6 7 8 9 10 11 | $link = mysql_connect('localhost', 'mysql_user', 'mysql_password'); if (!$link) { die('Not connected : ' . mysql_error()); } $db_selected = mysql_select_db('people', $link); if (!$db_selected) { die ('Can\'t select the database people : ' . mysql_error()); } ?> |
Where you would replace ‘people’ with any database that that username and password combination has access to.
And finally, the golden one…
mysql_query
This is where all the actions are performed:
1 2 3 4 | $result = mysql_query('SELECT * FROM `Builders` WHERE 1'); if (!$result) { die('Invalid query: ' . mysql_error()); } |
As you can see, it is all performed by writing SQL into the query function and we will discuss this more in the near future.
Throughout this series, I have been using the ‘/’ character as a start and end of my pattern, however there are other perfectly valid ones too…
Thats right, we may want to know if something is behind something else… And a positive lookbehind looks like ?.
1 2 3 | $string = 'I live in the yellowhouse'; if(preg_match("/(?<!white)house/i", $string)) |
So we are trying to not match house preceded by white. And guess what, we find it isn’t. This time the string is different you see 
Thats right, we may want to know if something is behind something else… And a positive lookbehind looks like ?<=.
1 2 3 | $string = 'I live in the whitehouse'; if(preg_match("/(?<=white)house/i", $string)) |
So we are trying to match house preceded by white. And guess what, we find it!
We have successfully worked out what to do when we don’t want matches to happen. But what about when we do..?
$string = 'A simple string is often short, eg: this.'; if(preg_match("/eg+(?=:)/", $string, $match))
Can we find eg followed by a colon? I think so, hence the match.
So, we look ahead to find matches, negative and positive…
This tutorial is negative…
A negative look ahead which is denoted with ?!. This is useful for checking if a pattern is not in front of the match we wish.
1 2 3 4 | $string = 'I hope you like dogfood!'; // Try to match dog not followed by food if(preg_match("/dog+(?!food)/i", $string)) |
And it should not match
Now lets try a simple search and replace again, this time with an array and the e modifier!
1 2 3 4 5 6 7 | $string = '_GOOD_ is better than _BAD_'; $replacements = array("GOOD" => "Google", "BAD" => "Ask"); $string = preg_replace("/_(.*?)_/ime", "\$replacements['$1']", $string); echo $string; |
and in real:
Google is better than Ask
Overall, “template” systems can be quickly used in order to ensure that the right data is returned.
A lot of the regex in php can be applied into preg_* functions such as preg_replace!
1 2 3 | $string = 'Yahoo is the best search engine'; $string = preg_replace("/Yahoo/", 'Google', $string); |
And bam:
Google is the best search engine!
Now, lets see it in practice!
Written by