Okay so here is the final installment of this simple look at
PHP & MySQL..
Again, the code is commented for explanation.. you will see tha the SELECT query is the easiest of the three but the most versatile as you can use the results any way you like...
The
PHP processing at the top...
- PHP: Select all
<?php
// Right now we get to see some more exciting things with PHP and mySQL..
// All of the hard work from above is now going to come to fruition because we will now actually grab some information
// from the database and prepopulate our form.
// For the purposes of this example we will assume that the PIG_ID we are retrieving will be sent as part of the URL so
// we can access it from $_GET... we will send it with the key of 'pig_id' so it is accessed via $_GET['pig_id']
// Not worth the effort if the little piggie ID hasn't been sent or if it's not a number
// We know the pig_id needs to be a number because this is going to reference the primary index
// from our guinepigs table
if (isset($_GET['pig_id']) && is_numeric($_GET['pig_id'])) // Isset checks that the variable has been set (does not check value its any usable type.. eg could be NULL or false)
{
// Let's connect to the database
$con = mysql_connect('localhost', 'root', 'guineapigsrule')
or
// By adding the or die line we make it so the script will not continue if we cannot connect.
die ('Oh no! We could not connect');
// now select the database to be used
mysql_select_db('guineapigs', $con) // databasename and then the connection id from above
or
die (mysql_error()); // die with mysql error response if we cannot select the database.
// SELECTING records from a database is a very simple process
// You create the query asking for the columns you want and optionally,
// what criteria have to be met for a match to be returned
// A few examples
// SELECT * FROM guineapigs
// The above query will select EVERYTHING from the guineapigs table, the asterix * advises that we want all the columns from the table
// SELECT pigName, PIG_ID FROM guinepigs LIMIT 3
// The above will select 3 of the guinea pig name and ID's from the database. The LIMI 3 tells Mysql how many you want returned
// SELECT pigName FROM guineapigs
// WHERE pigColour = 'pink' ORDER BY pigName ASC LIMIT 0, 2
// The aobve query will select the pigName from the database but only where the pig is the colour pink
// ORDER BY is the optional argument telling mySQL how to arrange the data -- in this case it is saying, please arrange in ascending (ASC) order of pigNames
// The LIMIT 0, 2 says we should start from the first record (0 indexed) and give 2 records
// Now it's time to build the query.... For our example, we will select only one record (with all columns) and only where PIG_ID is the same as $_GET['pig_id']
$_GET['pig_id'] = mysql_real_escape_string($_GET['pig_id']);
$query = "SELECT * FROM guineapigs WHERE PIG_ID = {$_GET['pig_id']} LIMIT 1";
// run the query
$result = mysql_query($query) or die('Woops, that\'s not good - It broke.. mysql says ' . mysql_error());
// We can check how many results were returned using mysql_num_rows()
if (!mysql_num_rows($result))
{
// okay so we have a match -- by using the mysql_fetch_assoc() function to get an array
// that has the column names as the key.... then we are free to use the result however we wish later in the script
// for the purposes of this script we will use the results to pre-fill our form
$pigDetails = mysql_fetch_assoc($result);
} else {
// Oh we didn't match anything so the PIG_ID must have been wrong...
$message = "Your pig id is fake! :-( Can't get the details"; // create a message to tell the user that their pig id has been refused entry
}
}
?>
<html>
<head>
<!-- Head stuff here //-->
</head>
<body>
<?php
// Echo the message from above it is set
echo !empty($message) ? $message : '';
?>
<!-- The form goes below etc.... //-->
</body>
</html>
Now because we are using the information we retrieved the pre-populate a form, the form should look like this...
- PHP: Select all
<!--
*********USING THE VALUES OBTAINED FROM THE SCRIPT ABOVE WE WILL FILL THE FORM**********
//-->
<form name="guineapigForm" action="<?php echo $_SERVER[‘php_self’]; ?>" method="post">
<label for="pigName">Guinea Pig Name</label>
<input type="text" name="pigName" id="pigName" maxlength="50" size="30" value="<?php echo isset($pigDetails) ? $pigDetails['pigName'] : '';?>/><br />
<label for="pigColour">Guinea Pig Colour</label>
<select name="pigColour" id="pigColour">
<option value="green" <?php echo isset($pigDetails) && $pigDetails['colour'] == 'green' ? 'selected="selected"' : ''; ?>>Green</option>
<option value="pink" <?php echo isset($pigDetails) && $pigDetails['colour'] == 'pink' ? 'selected="selected"' : ''; ?>>Pink</option>
<option value="black" <?php echo isset($pigDetails) && $pigDetails['colour'] == 'black' ? 'selected="selected"' : ''; ?>>Black</option
<option value="blue" <?php echo isset($pigDetails) && $pigDetails['colour'] == 'blue' ? 'selected="selected"' : ''; ?>>Blue</option>
<option value="polkadotted" <?php echo isset($pigDetails) && $pigDetails['colour'] == 'polkadotted' ? 'selected="selected"' : ''; ?>>Polka Dots</option>
</select><br />
<label name="pigAge">Guinea Pig Age</label>
<input type="text" name="pigAge" id="pigAge" maxlength="3" size="3" value="<?php echo isset($pigDetails) ? $pigDetails['pigAge'] : '';?>" /><br />
<label name="pigDescription">Guinea Pig Description</label><br />
<textarea name="pigDecription" id="pigDescription">
<?php echo isset($pigDetails) ? $pigDetails['pigDescription'] : '';?>
</textarea><br />
<?php
// Becuase we are filling the form, we need to add a hidden form element that holds the pig_id... This was, when the form is submitted, our update
//script will know what pig to update
if (isset($pigDetails))
echo '<input type="hidden" name="PIG_ID" value="', $pigDetails['PIG_ID'], '" />';
?>
<input type="submit" name="submitPig" value="Save Your Pig!" />
</form>
This concludes this simple look at
PHP & Mysql... I hope you have found it useful..
Please reply with any questions specifically relating to the code in this lesson or create a new thread in the
PHP forum for more general questions on this subject.
Cheers,
Rakuli...