PHP: DB Scripts

The following is some sample PHP code that pertains to reading and writing to databases. It covers the basis of how I read and write to MySQL databases. Here is what’s included:

• Reading, Inserting & Updating Records
• Defeating SQL injection
• MySQL DateTime, Timestamp, Date, Time

Simple DB Connection:

<!--?php <br ?-->// SET CONNECTION VARIABLES
$hostname="localhost";
$username="USERNAME";
$password="PASSWORD";
$dbname="DATABASE-NAME";
// CONNECT TO DATABASE
$con = mysql_connect($hostname, $username, $password) or die("</pre>
<h1>Website Error!</h1>
<pre>
");
mysql_select_db($dbname);
// Display the first ten results
$sql1 = "SELECT FIELDS FROM TABLE WHERE CRITERIA ORDER BY FIELD FIELD LIMIT 0,10";
$rs1 = mysql_query($sql1);
$numRows = mysql_num_rows($rs1); // Get the number of results
while($d = mysql_fetch_row($rs1)) {
     print "$d[0], $d[1]"; // the number corresponds to the field order searched for (starting w/ 0)
}
?>

Get A Single Result From a Database:

<?php
$sql1 = "SELECT * FROM TABLE WHERE CRITERIA='SOMETHING'";
$rs1 = mysql_query($sql1);
if(mysql_num_rows($rs1)==1){ // IF THERE WERE ANY RECORDS
print mysql_result($rs1,0,"FIELD-NAME"); // 0 = the first record returned.
}else{ // NO RECORDS FOUND
print "No Records Found!";
}
?>

Preventing SQL Injection

<?php
// TYPICAL INSERT:
$sql1 = "INSERT INTO TABLE SET FIELD='',FIELD='',FIELD=''";
// INSERT USING SQL INJECTION PREVENTION:
$sql1 = "INSERT INTO TABLE SET FIELD='".mysql_real_escape_string($value)."'";
$rs1 = mysql_query($sql1);
// AT THIS POINT, THE RECORD HAS BEEN CREATED!
// IF WE WANT TO GET THE AUTO-GENERATED ID OF THE PREVIOUSLY INSERTED RECORD, WE CAN DO THIS:
$newID = mysql_insert_id();
// NOTES: This method of SQL Injection Prevention also works with UPDATE
?>

Updating DB Records

<?php
// URGENT: Always use a conditional WHERE clause when doing an update, otherwise you will wipe out your DB
$sql1 = "UPDATE TABLE SET FIELD='',FIELD='' WHERE FIELD=''";
$rs1 = mysql_query($sql1);
// THE DATA HAS NOW BEEN CHANGED!
// IF WE WANT TO GET THE AUTO-GENERATED ID OF THE PREVIOUSLY INSERTED RECORD, WE CAN DO THIS:
$newID = mysql_insert_id();
// NOTES: This method of SQL Injection Prevention also works with UPDATE
?>