Network
Posted by John on 31-Aug-2010 at 09:08am
This is how I would like to spend my days 
2012 End of the World Predictions
Posted by John on 29-Aug-2010 at 04:08am
How many sites, posts, comments are there online stating that the World is going to end in 2012. In my opinion, the notion that an ancient civilisation could predict the end of the World several thousand years in advance of the event is completely ridiculous. Although, as my opinion probably doesn't count for much in these matters, I will hand you over to Professor Brian Cox.
PHP Object Oriented Programming (OOP) Tutorial 1
Posted by John on 29-Aug-2010 at 03:08am
This first tutorial is a basic introduction to Object Oriented Programming (OOP) in PHP. Basic OOP was first introduced in PHP 4 to move the language into the realm of high level languages such as Python and Java. OOP has been significantly improved in PHP 5 with a completely rewritten object model. PHP 6 looks to build on this even further, although I have yet to see a definitive list of new features.
As you probably already know, I am not a big fan of theory. I much prefer to dive straight in to an example and use comments to explain what my code is doing. So, have a look at the following basic example of PHP OOP in action. Save both files with the names given below and run index.php to see it working. Please let me know if you have any questions
class.text.php
index.php
In the next tutorial we will start to look at the visibility of class members.
As you probably already know, I am not a big fan of theory. I much prefer to dive straight in to an example and use comments to explain what my code is doing. So, have a look at the following basic example of PHP OOP in action. Save both files with the names given below and run index.php to see it working. Please let me know if you have any questions
class.text.php
<?php
// DECLARE CLASS
class text {
// DECLARE VARIABLE AND ASSIGN VALUE PHPfreak
var $siteTitle = "PHPfreak";
// INITIALISE FUNCTION siteTitle
function siteTitle() {
// RETURN $this->siteTitle USED TO GRAB THE VALUE OF THE VARIABLE
// siteTitle WITHIN THIS CLASS
return $this->siteTitle;
}
}
?>
index.php
<html>
<head>
<title>OOP Tutorial 1</title>
<style type="text/css">
.sitetitle {
font-family: arial;
color: #333333;
text-decoration: underline;
}
</style>
</head>
<body>
<?php
// INCLUDE THE TEXT CLASS
include('class.text.php');
// INSTANTIATE OBJECT - REFERENCE TO CLASS
$text = new text;
// CALL CLASS FUNCTION siteeTitle AND ECHO. NOTICE THAT THE HTML / CSS IS
// DONE IN THIS PAGE AND NOT IN THE CLASS SCRIPT. THIS SEPARATION IS
// IMPORTANT AS IT KEEPS CODE SPLIT INTO LOGICAL SEGMENTS. IT COULD BE DONE
// IN THE CLASS SCRIPT BUT WOULD MEAN THERE IS MULTIPLE LOCATIONS WHERE CSS
// AND HTML ARE DEFINED
echo '<h1 class="sitetitle">'.$text->siteTitle.'</h1>';
?>
</body>
</html>
In the next tutorial we will start to look at the visibility of class members.
Periodic Table of Elephants
Posted by John on 22-Aug-2010 at 06:08am
I just found this picture while blog hopping this morning. It combines two of my favourite things; Chemistry & Elephants, so I just had to post it
PHP Date Time vs. ADOdb Date Time
Posted by John on 22-Aug-2010 at 06:08am
I find myself using the standard PHP date / time functions as these suit most of my requirements. However, there may be a situation where you need to use dates far in the past / future. For this, PHP does not work very well 
PHP date functions use integer timestamps in calculations so POSIX operating systems are restricted to a range between 1901-2038 and Windows operating systems between 1970-2038.
If you do require dates outside this range, there is an excellent free PHP library called ADOdb Date Time Library. Just download and unzip to start using. The supported (tested) date range is 100A.D. - 3000A.D. which should be enough for most peoples requirements. The lower limit of 100A.D. is because if you type in 99 for the year, it will use the 2 digit to 4 digit conversion to display 1999. The upper limit of 3000A.D. is quite conservative as I tested with 9000A.D. without any problems. I am sure it will extend way beyond this too
The following example demonstrates the use of standard PHP date / time functions versus the ADOdb date time library. I have specified the year 2040 for this test as this is outside the range of the standard PHP functions.
You can see from the above example that the standard PHP functions are unable to handle this date and display the default "January 1st 1970, 01:00:00am". The ADOdb Date Time Library functions have no problem with this date and display "August 16th 2040, 10:59:30am" as expected.
PHP date functions use integer timestamps in calculations so POSIX operating systems are restricted to a range between 1901-2038 and Windows operating systems between 1970-2038.If you do require dates outside this range, there is an excellent free PHP library called ADOdb Date Time Library. Just download and unzip to start using. The supported (tested) date range is 100A.D. - 3000A.D. which should be enough for most peoples requirements. The lower limit of 100A.D. is because if you type in 99 for the year, it will use the 2 digit to 4 digit conversion to display 1999. The upper limit of 3000A.D. is quite conservative as I tested with 9000A.D. without any problems. I am sure it will extend way beyond this too
The following example demonstrates the use of standard PHP date / time functions versus the ADOdb date time library. I have specified the year 2040 for this test as this is outside the range of the standard PHP functions.
<?php
// SET TIME / DATE VARIABLES
$day = '16';
$month = '08';
$year = '2040';
$hour = '10';
$minute = '59';
$second = '30';
// ECHO TITLE
echo '<h1>ADOdb Date Time Library</h1>';
// ECHO DATE / TIME USING STANDARD PHP DATE / MKTIME
echo '<h3>Standard PHP</h3>';
echo date("F jS Y, H:i:sa", mktime($hour,$minute,$second,$month,$day,$year));
// INCLUDE ADODB DATE TIME LIBRARY
include('adodb-time.inc.php');
// ECHO DATE / TIME USING ADODB DATE TIME LIBRARY
echo '<h3>ADOdb Date Time Library</h3>';
echo adodb_date("F jS Y, H:i:sa", adodb_mktime($hour,$minute,$second,$month,$day,$year));
?>
You can see from the above example that the standard PHP functions are unable to handle this date and display the default "January 1st 1970, 01:00:00am". The ADOdb Date Time Library functions have no problem with this date and display "August 16th 2040, 10:59:30am" as expected.
PHP Copy Directory
Posted by John on 21-Aug-2010 at 18:08pm
The following PHP script echos out a list of directories. Clicking the "copy" link next to the folder name creates a duplicate with a unique name. For example, a folder called "folder1" would become "copy_TIMESTAMP_folder1." The code will also copy sub-directories.
The recurse_copy function is based on the code example at PHP.net. The code is currently set to copy folders in the current directory; however, this could be changed quite easily to work with other directories.
The recurse_copy function is based on the code example at PHP.net. The code is currently set to copy folders in the current directory; however, this could be changed quite easily to work with other directories.
<?php
// GET DIRECTORY NAME FROM URL
$src = $_GET['src'];
// DECLARE COPY FUNCTION
function recurse_copy($src) {
// SET NEW DIRECTORY NAME
$dst = 'copy_'.date('dmYHis').'_'.$src;
// OPEN DIRECTORY FOR READING
$dir = opendir($src);
// CREATE DIRECTORY COPY
@mkdir($dst);
// LOOP THROUGH DIRECTORY CONTENT
while(false !== ($file = readdir($dir))) {
// IF FILE NAME IS NOT . OR ..
if (($file != '.') && ($file != '..')) {
// IF IS A DIRECTORY
if (is_dir($src . '/' . $file)) {
// LOOP THROUGH FUNCTION AGAIN TO COPY SUB DIRECTORY
recurse_copy($src . '/' . $file,$dst . '/' . $file);
}
else {
// ELSE COPY FILE
copy($src . '/' . $file,$dst . '/' . $file);
}
}
}
// CLOSE DIRECTORY
closedir($dir);
}
// IF DIRECTORY PASSED IN URL
if(isset($src)) {
// RUN FUNCTION
recurse_copy($src);
// RELOAD PAGE
header('Location: '.$_SERVER['PHP_SELF']);
}
?>
<html>
<head>
<title>PHP Copy Directory</title>
</head>
<body>
<?php
// SET DIRECTORY PATH
$d = dir('./');
//
while (false !== ($entry = $d->read()))
// IF IS A DIRECTORY AND IS NOT . OR ..
if (is_dir($entry) && $entry != '..' && $entry != '.') {
// ECHO DIRECTORY NAME AND COPY LINK
echo $entry.' <a href="'.$_SERVER['PHP_SELF'].'?src='.$entry.'">Copy</a><br />';
}
// CLOSE
$d->close();
?>
</body>
</html>
Periodic Table of Videos
Posted by John on 10-Aug-2010 at 01:08am
As well as PHP, I also have a passion for science, particularly Chemistry & Physics. The other day, I found a great website called The Periodic Table of Videos. One of my favourite videos is for the element Caesium.
PHP alternative to mysql_real_escape_string
Posted by John on 09-Jul-2010 at 10:07am
The title kind of gives it away, but this thread offers an alternative to the PHP mysql_real_escape_string function.
Why would you want an alternative? Well, the main reason is if you do not have a database connection. If you submit a form using mysql_real_escape_string without a database connection, you will receive errors.
You may still want to validate the user input and escape certain characters though. To do this you can use the PHP addcslashes function with a list of characters to escape. This code:
will do exactly what mysql_real_escape_string does. Notice this is not addslashes, it is addcslashes (with a 'c'). If you want to echo out the data entered without slashes, you can either use stripslashes or stripcslashes (again with a 'c'). I tend to use stripcslashes just to keep it consistent but either is fine
Have a look at addcslashes / stripcslashes in the following example:
Why would you want an alternative? Well, the main reason is if you do not have a database connection. If you submit a form using mysql_real_escape_string without a database connection, you will receive errors.
Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'ODBC'@'localhost' (using password: NO) in C:examplesmres_altindex.php on line 8
Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in C:examplesmres_altindex.php on line 8
Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in C:examplesmres_altindex.php on line 8
You may still want to validate the user input and escape certain characters though. To do this you can use the PHP addcslashes function with a list of characters to escape. This code:
addcslashes($str, "\x00\n\r\'\"\x1a");
will do exactly what mysql_real_escape_string does. Notice this is not addslashes, it is addcslashes (with a 'c'). If you want to echo out the data entered without slashes, you can either use stripslashes or stripcslashes (again with a 'c'). I tend to use stripcslashes just to keep it consistent but either is fine
Have a look at addcslashes / stripcslashes in the following example:
<html>
<head>
<title>Form</title>
</head>
<body>
<?php
if(isset($_POST['submit'])) {
//$yourbrowser = mysql_real_escape_string($_POST['yourbrowser']);
$yourbrowser = addcslashes($_POST['yourbrowser'], "\x00\n\r\'\"\x1a");
echo 'Your browser is: '.$yourbrowser.' (with slashes)<br />';
echo 'Your browser is: '.stripcslashes($yourbrowser).' (without slashes)';
}
?>
<form action="<?php $_SERVER['PHP_SELF']; ?>" method="post">
Your Browser: <input type="text" name="yourbrowser" />
<input type="submit" name="submit" value="Submit" />
</form>
</body>
</html>
PHP working with text files
Posted by John on 07-Jul-2010 at 06:07am
I love coding in PHP but sometimes I struggle to think of things to write. During these times, I check out sites like vworker.com and freelancer.com to see what requests are being asked for. I don't bid on the projects but I do like to have a go at completing them on my own to improve my knowledge.
I saw a project yesterday to create a simple one page site that loads text files. The text files would be sequentially numbered (1.txt...n.txt) and the folder would be added to over time. When the page loads, a random file should be displayed. Pagination should be included to navigate through the files by numeric order (There was no mention that files would ever be deleted). There should also be an option to manually select a file.
Sounds pretty simple eh. Well, there are a few things to consider before starting to write the code.
1. How do you randomly select a file from a folder? The fact that the files are sequentially numbered and are all .txt files has advantages. This means that we can strip the .txt extension and use the file numbers to an array. Using the PHP array_rand function we can select a number from the array and store as a variable ($f). We can then echo out the file by appending the .txt extension to this variable.
2. What about the pagination? Again, we can use the number stored in the variable ($f). We know that logically the previous file will be $f-1 and the next file will be $f+1. We can create two variables ($next & $prev) to store these two values. We also know that it is not possible to have a file less that 1.txt. Therefore, we can make the assumption that we only need to display a link to the previous file if $f is greater than 1. We can use the PHP file_exists function to determine if there is a file with the filename $next.txt. If this file exists then we can display a link to the next file.
3. What happens to the variable ($f)? Good question When we use the pagination, we pass the variable along the address bar of the browser using the PHP $_GET function. When the page refreshes, we update the value assigned to $f. This is then used to load the correct file.
4. What about manually selecting a file? A simple form is displayed with a textbox to allow the user to enter the number of the file. When submitted, this uses $_GET to update the value assigned to $f.
5. How does the user know the range of files? We know the first file is 1.txt so our starting range is 1 to n. To know what 'n' is, we can use the array of filenames again. The PHP end function will grab the last value in the array which will be the highest sequential number.
6. What if a user enters a file that doesn't exist? If you don't account for this, you know it will happen
. The best way to deal with this is to use the PHP file_exists function again to check the input. If the file does not exist, an error message is displayed.
7. How are files added to the folder? Ok, so I added this part myself. It is not part of the specification but I thought it would be nice to include. A form has been added with a textarea and submit button. When this form is submitted, a new text file is created and added to the folder. A validation check is performed to ensure the textarea is not empty when submitted.
8. How do we know what filename to use? There is a hidden field on the form that is also submitted. This again uses the PHP end function to grab the last value in the array and store it is a variable, $last. We can logically assume that $last+1 will be the next sequential filename.
Ok, that went on for a bit so I hope at least some of it was useful. Before running the script, you need to create a folder called 'files' to store the text files. Now lets have a look at the code
One important point to mention is that you need to make sure the folder on the server (where you store your text files) is writeable. You can do this by changing the file permissions to CHMOD 777.
I saw a project yesterday to create a simple one page site that loads text files. The text files would be sequentially numbered (1.txt...n.txt) and the folder would be added to over time. When the page loads, a random file should be displayed. Pagination should be included to navigate through the files by numeric order (There was no mention that files would ever be deleted). There should also be an option to manually select a file.
Sounds pretty simple eh. Well, there are a few things to consider before starting to write the code.
1. How do you randomly select a file from a folder? The fact that the files are sequentially numbered and are all .txt files has advantages. This means that we can strip the .txt extension and use the file numbers to an array. Using the PHP array_rand function we can select a number from the array and store as a variable ($f). We can then echo out the file by appending the .txt extension to this variable.
2. What about the pagination? Again, we can use the number stored in the variable ($f). We know that logically the previous file will be $f-1 and the next file will be $f+1. We can create two variables ($next & $prev) to store these two values. We also know that it is not possible to have a file less that 1.txt. Therefore, we can make the assumption that we only need to display a link to the previous file if $f is greater than 1. We can use the PHP file_exists function to determine if there is a file with the filename $next.txt. If this file exists then we can display a link to the next file.
3. What happens to the variable ($f)? Good question
When we use the pagination, we pass the variable along the address bar of the browser using the PHP $_GET function. When the page refreshes, we update the value assigned to $f. This is then used to load the correct file.4. What about manually selecting a file? A simple form is displayed with a textbox to allow the user to enter the number of the file. When submitted, this uses $_GET to update the value assigned to $f.
5. How does the user know the range of files? We know the first file is 1.txt so our starting range is 1 to n. To know what 'n' is, we can use the array of filenames again. The PHP end function will grab the last value in the array which will be the highest sequential number.
6. What if a user enters a file that doesn't exist? If you don't account for this, you know it will happen
. The best way to deal with this is to use the PHP file_exists function again to check the input. If the file does not exist, an error message is displayed.7. How are files added to the folder? Ok, so I added this part myself. It is not part of the specification but I thought it would be nice to include. A form has been added with a textarea and submit button. When this form is submitted, a new text file is created and added to the folder. A validation check is performed to ensure the textarea is not empty when submitted.
8. How do we know what filename to use? There is a hidden field on the form that is also submitted. This again uses the PHP end function to grab the last value in the array and store it is a variable, $last. We can logically assume that $last+1 will be the next sequential filename.
Ok, that went on for a bit so I hope at least some of it was useful. Before running the script, you need to create a folder called 'files' to store the text files. Now lets have a look at the code
<html>
<head>
<title>Display Text Files</title>
<style type="text/css">
html, body {
font-family: arial, verdana, helvetica, sans-serif;
font-size: 12pt;
color: #333333;
background-color: #ffffff;
}
.col1 {
width: 49%;
display: inline;
position: relative;
float: left;
border: 1px solid #333333;
padding: 10px;
}
.col2 {
width: 49%;
display: inline;
position: relative;
float: right;
border: 1px solid #333333;
padding: 10px;
}
form {
padding: 0px;
margin: 0px;
}
textarea {
width: 100%;
height: 200px;
}
.errormsg {
font-weight: bold;
color: #990000;
}
</style>
</head>
<body>
<div class="col1">
<h1>Article</h1>
<?php
// GET SUBMITTED VALUE & STORE IN VARIABLE $f
$f = $_GET['f'];
// SET DIRECTORY WHERE .TXT FILES STORED
$dir = "files/";
// IF ADD ARTICLE FORM SUBMITTED
if(isset($_POST['submit'])) {
// GRAB EACH POSTED VALUE & ESCAPE ANY ILLEGAL CHARACTERS
foreach($_POST as $key=>$value) {
$$key = $value;
}
// IF ARTICLE FIELD EMPTY
if(!$article) {
// SET ERROR MESSAGE
$errormsg = '<div class="errormsg">Error, no article data entered</div>';
// ELSE
} else {
// INCREMENT $last BY ONE TO GET NEW FILENAME
$last = $last+1;
// SET FILENAME
$file = $dir.$last.'.txt';
// OPEN THE FILE FOR WRITING
$savefile = fopen($file, 'w');
// WRITE CONTENT TO FILE
fwrite($savefile, $article);
// CLOSE FILE
fclose($savefile);
}
}
// INITIALISE ARRAY TO STORE FILE NAMES
$filearray = array();
// IF DIRECTORY (SPECIFIED ABOVE) EXISTS
if (is_dir($dir)) {
// OPEN DIRECTORY
if ($dh = opendir($dir)) {
// LOOP THROUGH FILES IN DIRECTORY
while (($file = readdir($dh)) !== false) {
// IGNORE . & .. FILENAMES
if($file != '..' && $file != '.') {
// STRIP .TXT EXTENSION FROM FILENAME
$file = substr($file, 0, -4);
// STORE FILENAME IN ARRAY
$filearray[] = $file;
}
}
// CLOSE DIRECTORY
closedir($dh);
}
}
// IF $f IS EMPTY
if($f == '') {
// ASSIGN A RANDOM VALUE FROM THE ARRAY TO $f
$f = array_rand($filearray, 1);
// INCREMENT VALUE BY ONE AS ARRAY KEY STARTS AT ZERO
++$f;
// ASSIGN CONTENT OF FILE TO $data VARIABLE - nl2br MAINTAINS THE LINE BREAKS IN THE FILE
$data = nl2br(file_get_contents($dir.$f.'.txt'));
}
// IF $f IS NOT EMPTY AND FILE DOES NOT EXIST
if($_GET['f'] && !file_exists($dir.$f.'.txt')) {
// SET ERROR VARIABLE
$error = 1;
// ECHO ERROR MESSAGE
echo '<div class="errormsg">Sorry, the selected file ''.$f.'.txt' does not exist</div>';
// ELSE
} else {
// ASSIGN CONTENT OF FILE TO $data VARIABLE - nl2br MAINTAINS THE LINE BREAKS IN THE FILE
$data = nl2br(file_get_contents($dir.$f.'.txt'));
}
// IF ERROR VARIABLE NOT SET
if($error != 1) {
// ECHO $data VARIABLE TO BROWSER
echo $data;
// SET $next VARIABLE
$next = $f+1;
// SET $prev VARIABLE
$prev = $f-1;
echo '<p>';
// IF $f VARIABLE IS GREATER THAN ONE
if($f > 1) {
// ECHO PREVIOUS FILE LINK
echo '<a href="'.$_SERVER['PHP_SELF'].'?f='.$prev.'">« Previous</a> ';
}
// ECHO CURRENT FILE NUMBER
echo '['.$f.'] ';
// IF THE NEXT FILE (SEQUENTIALLY) EXISTS
if(file_exists($dir.$next.'.txt')) {
// ECHO NEXT FILE LINK
echo '<a href="'.$_SERVER['PHP_SELF'].'?f='.$next.'">Next »</a>';
}
}
echo '</p>';
// ECHO FILE SELECT FORM
echo '<form action="'.$_SERVER['PHP_SELF'].'" method="get">
Enter file #: <input style="width:30px;" type="text" name="f" />
<input type="submit" name="go" value="Go" /> (Available files: ';
// ECHO THE FIRST AND LAST FILES AVAILABLE IN THE ARRAY
echo $filearray[0].' - '.end($filearray);
echo ')</form>
</div>
<div class="col2">
<h1>Add Article</h1>
'.$errormsg.'
<form action="'.$_SERVER['PHP_SELF'].'" method="post">
<textarea name="article"></textarea><br />
<input type="hidden" name="last" value="'.end($filearray).'" />
<input type="submit" name="submit" value="Submit">
</form>
</div>';
?>
</body>
</html>
One important point to mention is that you need to make sure the folder on the server (where you store your text files) is writeable. You can do this by changing the file permissions to CHMOD 777.
PHP / MySQL INSERT ... ON DUPLICATE KEY UPDATE
Posted by John on 04-Jul-2010 at 08:07am
Sometimes you might want to only add a new record to your database if the record does not already exist. There are a couple of ways to do this.
The first is to perform a SELECT query to check if the record exists. Then either run an INSERT or UPDATE query. This works perfectly fine but does add a lot of unnecessary code to your script.
The second way (which we are going to look at) is to use MySQL's ON DUPLICATE KEY UPDATE functionality. This allows you to run a single query to check if there is a duplicate and either INSERT a new record or UPDATE the existing one.
For the example below, I have included the MySQL table and PHP script. You will notice that the 'url' field in the table is marked as a UNIQUE KEY. This is very important. This is the field we check against to see whether we will do the INSERT or UPDATE.
Lets have a look at the code:
MySQL table (tblurls)
index.php
The line we are interested in from the above script is:
The first part:
is the same as any standard INSERT query.
The second part:
is only executed if a duplicate URL is found. This code will update the duplicate record (description and lastupdate fields) rather than inserting a new record.
The first is to perform a SELECT query to check if the record exists. Then either run an INSERT or UPDATE query. This works perfectly fine but does add a lot of unnecessary code to your script.
The second way (which we are going to look at
) is to use MySQL's ON DUPLICATE KEY UPDATE functionality. This allows you to run a single query to check if there is a duplicate and either INSERT a new record or UPDATE the existing one.For the example below, I have included the MySQL table and PHP script. You will notice that the 'url' field in the table is marked as a UNIQUE KEY. This is very important. This is the field we check against to see whether we will do the INSERT or UPDATE.
Lets have a look at the code:
MySQL table (tblurls)
--
-- Table structure for table `tblurls`
--
CREATE TABLE `tblurls` (
`id` int(11) unsigned NOT NULL auto_increment,
`url` varchar(255) NOT NULL,
`description` text NOT NULL,
`lastupdate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `url` (`url`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
--
-- Dumping data for table `tblurls`
--
INSERT INTO `tblurls` (`id`, `url`, `description`, `lastupdate`) VALUES
(1, 'http://www.phpfreak.co.uk/', 'PHPFreak', '2010-07-04 13:39:42'),
(2, 'http://www.google.co.uk/', 'Google UK', '2010-07-04 13:39:18'),
(3, 'http://images.google.co.uk/', 'Google images', '2010-07-04 13:39:06');
index.php
<html>
<head>
<title>PHP / MySQL INSERT ... ON DUPLICATE KEY UPDATE Example</title>
<style type="text/css">
html, body {
font-family: arial, verdana, helvetica, sans-serif;
font-size: 10pt;
color: #333333;
}
.errormsg {
font-weight: bold;
color: #990000;
}
table, tr {
text-align: left;
border: 1px solid #333333;
}
</style>
</head>
<body>
<h1>PHP / MySQL INSERT ... ON DUPLICATE KEY UPDATE Example</h1><hr />
<?php
// DATABASE CONNECTION
$dbHost = "localhost";
$dbUser = "YOUR_USERNAME";
$dbPass = "YOUR_PASSWORD";
$dbName = "YOUR_DATABASE";
$db = mysql_connect($dbHost,$dbUser,$dbPass);
mysql_select_db($dbName,$db);
// IF FORM SUBMITTED
if(isset($_POST['submit'])) {
// GRAB EACH POSTED VALUE AND ASSIGN TO VARIABLE
foreach($_POST as $key=>$value) {
$$key = mysql_real_escape_string($value);
}
// IF URL FIELD NOT POPULATED THEN ERROR
if (!$url) {
$error_msg = '<div class="errormsg">Error: no data entered</div>';
echo $error_msg;
}
// IF NO ERRORS
if($error_msg == '') {
// INSERT RECORD IF NOT EXISTS ELSE UPDATE FIELDS
$sql = mysql_query("INSERT INTO tblurls (id, url, description, lastupdate) VALUES ('', '$url', '$description', NOW()) ON DUPLICATE KEY UPDATE description = '$description', lastupdate = NOW()") or die(mysql_error());
}
}
// DISPLAY INPUT FORM
echo '<h2>Insert Record</h2><form action="'.$_SERVER['PHP_SELF'].'" method="post">
URL:<br /><input style="width: 330px;" type="text" name="url" /><br /><br />
Description:<br /><textarea rows="5" cols="40" name="description"></textarea><br /><br />
<input type="submit" name="submit" value="Submit" />
</form>
<hr />';
// DISPLAY RESULTS IN TABLE
echo '<h2>MySQL Data</h2>
<table>
<tr>
<th style="width:100px;">ID</th>
<th style="width:300px;">URL</th>
<th style="width:400px;">Description</th>
<th style="width:150px;">Last Update</th>
</tr>';
// QUERY MySQL TABLE TO GET ALL ROWS
$tblquery = mysql_query("SELECT * FROM tblurls") or die(mysql_error());
// GET TOTAL NUMBER OF ROWS
$numrows = mysql_num_rows($tblquery);
// IF NO ROWS RETURNED ECHO 'No Data' MESSAGE
if($numrows <= 0) {
echo '<tr><td>No Data</td></tr>';
// IF ROWS RETURNED
} else {
// LOOP THROUGH ROWS AND ECHO IN TABLE
while($row = mysql_fetch_array($tblquery)) {
echo '<tr><td>'.$row['id'].'</td>
<td>'.$row['url'].'</td>
<td>'.$row['description'].'</td>
<td>'.$row['lastupdate'].'</td></tr>';
}
}
// CLOSE TABLE
echo '</table>';
?>
</body>
</html>
The line we are interested in from the above script is:
$sql = mysql_query("INSERT INTO tblurls (id, url, description, lastupdate) VALUES ('', '$url', '$description', NOW()) ON DUPLICATE KEY UPDATE description = '$description', lastupdate = NOW()") or die(mysql_error());The first part:
$sql = mysql_query("INSERT INTO tblurls (id, url, description, lastupdate) VALUES ('', '$url', '$description', NOW())is the same as any standard INSERT query.
The second part:
ON DUPLICATE KEY UPDATE description = '$description', lastupdate = NOW()")
is only executed if a duplicate URL is found. This code will update the duplicate record (description and lastupdate fields) rather than inserting a new record.
1 2 3 Next > Last »