| Welcome to Webforumz.com. |
|
Oct 21st, 2007, 10:46
|
#1 (permalink)
|
Join Date: Sep 2007
Location: Australia
Age: 24
Posts: 980
|
PHP & MYSQL -- Put it In -> Change it Around -> Pull it Out
Hello everyone, I do hope you are having a marvellous day.
I have been a member of Webforumz for a bit over a month now and it has become apparent that I am spending 50% of my time answering questions about.
Adding data from PHP to a mySQL database
Updating said data once it is in the database
Pulling that data from the database to use later.
I have decided to create this topic to go into some basic detail about these issues as c010depunk pointed out there is no such resource on Webforumz.
The following three posts will deal with each of the above-mentioned topics and will be using the same data in each situation. In all cases we will be dealing with a (x)HTML form and a single table in a mySQL database.
THE DATABASE DETAILS
Firstly the details of the example database:
Host : ‘localhost’
User : ‘root’
Password : ‘guineapigsrule’
Database: ‘guineapigs’
Here is the information for the table called petGuineapigs.
- Code: Select all
CREATE TABLE guineapigs`.`petGuineapigs` (
`PIG_ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`pigName` VARCHAR( 50 ) NOT NULL ,
`pigColour` VARCHAR( 20 ) NOT NULL ,
`pigAge` SMALLINT( 3 ) NOT NULL ,
`pigDescription` TEXT NOT NULL ,
INDEX ( `pigName` , `pigColour` , `pigAge` )
) ENGINE = MYISAM
It is beyond the scope of this post to explain what the above SQL means in intricate detail but it creates a table with the following columns:
PIG_ID – The unique id of each guinea pig. This will increment by one with each record inserted into the database using mySQL’s AUTO_INCREMENT feature.
pigName – guinea pig name. Max 50 characters
pigColour – guinea pig colour. Max 20 Characters
pigAge – guinea pig age. Max 999
pigDescription – guinea pig description. A text field for storing a guinea pig description.
You can create the table by running the query in phpMyAdmin or similar tool available on almost all hosting services and LAMP/WAMP home hosting setups.
THE (x)HTML FORM
- PHP: Select all
<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” /><br />
<label for=”pigColour”>Guinea Pig Colour</label>
<select name=”pigColour” id=”pigColour”>
<option value=”green”>Green</option>
<option value=”pink”>Pink</option>
<option value=”black”>Black</option
<option value=”blue”>Blue</option>
<option value=”polkadotted”>Polka Dots</option>
</select><br />
<label name=”pigAge”>Guinea Pig Age</label>
<input type=”text” name=”pigAge” id=”pigAge” maxlength=”3” size=”3” /><br />
<label name=”pigDescription”>Guinea Pig Description</label><br />
<textarea name=”pigDecription” id=”pigDescription”></textarea><br />
<input type=”submit” name=”submitPig” value=”Save Your Pig!” />
</form>
The form is fairly basic – difference you will see in the form action. This will send the form to the current page when submitted.
All the PHP in the following posts will beplace *before* any HTML so it will look like
<?php
// PHP CODE HERE
?>
<html>
<head>....etc. ect.
Without further delay..... The first post will show how to add the details in the form as a new record to the database. There will be no text, please read the comments in the file to see what is happening in the file. |
|
|
|
Oct 21st, 2007, 10:47
|
#2 (permalink)
|
Join Date: Sep 2007
Location: Australia
Age: 24
Posts: 980
|
Stick it In -- Inserting data via SQL with PHP
This is part one inserting data from a form into the database with a fair amount of data checking.
- PHP: Select all
<?php
// Okay, so we're only going to worry about adding anything to the database if the user has actually submitted the form
// We do this by checking of the submit button has been passed to the server we will check all data sent using
// $_POST becuase the action of out form is method="post" if it was method="get" we would replace $_POST with $_GET
// The name of our submit buttin is 'submitPig' so e check for that
if (isset($_POST['submitPig'])) // 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.
// for the purpose of this script, we will assume that all form fields are *required*
// this means that if there is something missing after our simple validation, we will
// not add anything to the database
$errors = array(); // initialise an error array
// The validation in use is very simple, it would be recommended to check in more detail in a live script
// Check for a guineapig name
$_POST['pigName'] = @trim($_POST['pigName']); // trim the whitespace from both ends
if (empty($_POST['pigName']) || !ctype_alpha($_POST['pigName']) || strlen($_POST['pigName']) > 50)
// empty checks to be sure that the field is 1)not set or 2) has a value other than '', false, 0 or NULL
// !ctype_aplha checks to make sure that the field only has alphabetic letters
// strlen to check that field is not too long
$errors[] = 'Your guinea pig must have a name that is only letters and is less than 50 characters';
else
// Everything checks out so we will escape the data ready to insert into the database
$_POST['pigName'] = mysql_real_escape_string($_POST['pigName']);
// Now check the rest of the fields
$_POST['pigColour'] = @trim($_POST['pigColour']);
if (empty($_POST['pigColour']) || !ctype_alpha($_POST['pigColour']) || strlen($_POST['pigName']) > 20)
$errors[] = 'Your piggie has no colour or has more than 20 letters or some crazy character in it. Please fix';
else
$_POST['pigColour'] = mysql_real_escape_string($_POST['pigColour']);
$_POST['pigAge'] = @trim($_POST['pigAge']);
if (empty($_POST['pigAge']) || !is_numeric($_POST['pigAge']) || $_POST['pigAge'] > 999)
// Different check for a number, check that it is a number with !is_numeric and check that it is less that max 999
$errors[] = 'And *how* old is your little piggie? Make sure it\'s just numbers and less than 999.';
else
$_POST['pigAge'] = mysql_real_escape_string($_POST['pigAge']);
// Now check the rest of the fields
$_POST['pigDescription'] = @trim($_POST['pigDescription']);
if (empty($_POST['pigDescription']))
$errors[] = 'Can you please tell us a little bit about your piggie? I would love to know. Phanks summuch';
else
$_POST['pigDescription'] = mysql_real_escape_string($_POST['pigDescription']);
// Now we have checked all of the fields, one of two this has happened
// 1 -- we have allrequired data (BONZA!!)
// 2 -- We don't
// We will not continue if we have any errors
$ne = count($errors); // Count the errors
if (!$ne) { // If there are no errors we will go to the database entry
// Okay, when we are inserting a new record into the database the format for the SQL is
// INSERT INTO tablename (column1, column2, column3)
// VALUES (value1, value2, value3)
// In our example the query will look something like this if we wrote it from scratch
// INSERT INTO petGuineapigs (pigName, pigColour, pigAge, pigDescription)
// VALUES ('Johnny', 'BabyPooBrown', 67, 'Johnny is a hyperactive scamp, always running around')
// Some things to note, you need to surround non-number values with quotes and you need to specify the values in
// the order you lay out the columns. If you have a column with AUTO_INCREMENT you *do not* specify this value
// It will increase by itself
// Now to contruct our query
// When we are inserting values into a string from an array surround them in {curly braces} so PHP knows they
// have special meaning
$query = "INSERT INTO petGuineaPigs (pigName, pigColour, pigAge, pigDescription)
VALUES ('{$_POST['pigName']}', '{$_POST['pigColour']}', {$_POST['pigAge']}, '{$_POST['pigDescription']}')";
// now we will run the query
mysql_query($query)
or
die ('OH NO! All that work for nuffin ' . mysql_error());
// Because we didn't die, we can call it a success. Becuase mySQL will increment the primary id for us we can
// can retrieve it
$insertedID = mysql_insert_id();
// Get the message to give to the user
$message = 'Hooray, we saved your guinea pig! It\'s unique id id ' . $insertedID;
} else {
// Now we are in here because we had some errors so we will add the errors to a message string
// to display later.
$message = '<strong>Something went awry!</strong>
<ol>';
foreach ($errors as $error)
$message .= '<li>' . $error . '</li>';
$message .= '</ol>';
}
}
?>
<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>
Last edited by Rakuli; Oct 28th, 2007 at 09:53.
Reason: Attached file for easier viewing
|
|
|
|
|
Oct 22nd, 2007, 12:34
|
#3 (permalink)
|
Join Date: Sep 2007
Location: Australia
Age: 24
Posts: 980
|
Change it Around -- The UPDATE query
Okay so this some information on the update query.
Because of how the UPDATE query works, mySQL will need to know what data to update or else it will change everything. For the purposes of this example we will be using a hidden element in our form.
The value of the hidden input will be the PIG_ID of your pig. You should have received this from example on the INSERT QUERY
The form should look similar to :
- PHP: Select all
<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” /><br />
<label for=”pigColour”>Guinea Pig Colour</label>
<select name=”pigColour” id=”pigColour”>
<option value=”green”>Green</option>
<option value=”pink”>Pink</option>
<option value=”black”>Black</option
<option value=”blue”>Blue</option>
<option value=”polkadotted”>Polka Dots</option>
</select><br />
<label name=”pigAge”>Guinea Pig Age</label>
<input type=”text” name=”pigAge” id=”pigAge” maxlength=”3” size=”3” /><br />
<label name=”pigDescription”>Guinea Pig Description</label><br />
<textarea name=”pigDecription” id=”pigDescription”></textarea><br />
<input type="hidden" name="PIG_ID" value="1" />
<input type=”submit” name=”submitPig” value=”Save Your Pig!” />
</form>
- PHP: Select all
<?php
// Okay, so we're only going to worry about adding anything to the database if the user has actually submitted the form
// We do this by checking of the submit button has been passed to the server we will check all data sent using
// $_POST becuase the action of out form is method="post" if it was method="get" we would replace $_POST with $_GET
// The name of our submit buttin is 'submitPig' so e check for that
if (isset($_POST['submitPig']) && !empty($_POST['PIG_ID']) && is_numeric($_POST['PIG_ID']))
// Isset checks that the variable has been set (does not check value its any usable type.. eg could be NULL or false)
// because we want to update a record with this script we will check to be sure that a PIG_ID has been sent and tha this id is a number
// You don't have to send the id in all cases, you could use a SELECT query to pull a record from the database based on criteria you specify
// Once you have retrieved the record you can then use that in the UPDATE query... For now though, we will use the PIG_ID sent in the form.
{
$_POST['PIG_ID'] = mysql_real_escape_string($_POST['PIG_ID']); // escape the data to be sure to be sure
// 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.
// For this update script we will assume that *nothing* is required. When we created the record, we required everything so updating
// won't need such a firm hand -- you can be firm if you'd like but I'd be afraid they would yell at me :(
$errors = array(); // initialise an error array... We can still have errors of course.. A guinea pig names %%44$$ wouldn't make much sense
$updates = array(); // When we write the query to update our database, we will need columnName=value pairs.. This array will have the columnName as the
// key of the array with the value as well, umm the value
// The validation in use is very simple, it would be recommended to check in more detail in a live script
// Check for a guineapig name
$_POST['pigName'] = @trim($_POST['pigName']); // trim the whitespace from both ends
if (!ctype_alpha($_POST['pigName']) || strlen($_POST['pigName']) > 50)
// !ctype_aplha checks to make sure that the field only has alphabetic letters
// strlen to check that field is not too long
$errors[] = 'How mean are you? You can\'t name your guinea pig that, image how much it will get teased at guinea pig school. Please make sure that the name is only letters and less that 50 at that.';
else if (!empty($_POST['pigName'])) // We will only add to out $updates array if there is a value sent to us..
{
// Everything checks out so we will escape the data ready to insert into the database
$_POST['pigName'] = mysql_real_escape_string($_POST['pigName']);
$updates['pigName'] = $_POST['pigName'];
}
// Now check the rest of the fields
$_POST['pigColour'] = @trim($_POST['pigColour']);
if (!ctype_alpha($_POST['pigColour']) || strlen($_POST['pigName']) > 20)
$errors[] = 'You poor piggie\'s colour has more than 20 letters or some crazy character in it. Please fix';
else if (!empty($_POST['pigColour']))
{
$_POST['pigColour'] = mysql_real_escape_string($_POST['pigColour']);
$updates['pigColour'] = $_POST['pigColour'];
}
$_POST['pigAge'] = @trim($_POST['pigAge']);
if (!is_numeric($_POST['pigAge']) || $_POST['pigAge'] > 999)
// Different check for a number, check that it is a number with !is_numeric and check that it is less that max 999
$errors[] = 'And *how* old is your little piggie? Make sure it\'s just numbers and less than 999.';
else if (!empty($_POST['pigAge']))
{
$_POST['pigAge'] = mysql_real_escape_string($_POST['pigAge']);
$updates['pigAge'] = $_POST['pigAge'];
}
// Now check the rest of the fields
$_POST['pigDescription'] = @trim($_POST['pigDescription']);
if (!empty($_POST['pigDescription']))
{
$_POST['pigDescription'] = mysql_real_escape_string($_POST['pigDescription']);
$updates['pigDescription'] = $_POST['pigDescription'];
}
// Obviously if they have sent nothing, we don't have to update anything so we will call this an error
if (!count($updates)) // count the updates we're making and make a fuss if they sent nuffin'
$errors[] = 'Did your guinea pig run across your keyboard? It would appear you have not sent any data... A shame that.';
// Now we have checked all of the fields, one of two this has happened
// 1 -- we have some data to update in our database
// 2 -- We don't
// We will not continue if we have any errors cause that would be reckless and I don't want to break any rules just now
$ne = count($errors); // Count the errors
if (!$ne) { // If there are no errors we will go to the database entry
// Okay, an update to the data base is slightly tricker syntax to the insert it looks like :
// UPDATE tableName SET thisColumn = 'thisValue', thisColumn2 = 'thisValue2', thisColumn3 = 'thisValue3'
// Now, the above is enough on it's own to change the data BUT****** This will change every single entry in the
// table. For an UPDATE query to be of any use at all, you really must tell mySQL what column to update you do this
// using the WHERE clause. eg :
//UPDATE tableName SET thisColumn = 'thisValue', thisColumn2 = 'thisValue2', thisColumn3 = 'thisValue3'
//WHERE thisColumn = 'thisValue'
// You don't have to use a column comparison you could use arbitrary statements like WHERE 1 = 1 but this is beyond the
// scope of this little lesson.
// This raises the question of how we are going to find out where to put OUR data.... In the text above this post
// You will see that I got you to add a hidden form element that will send our pig's unique ID to the FORM
// Later, when I get to the SELECT QUERY you will see how to dynamically add the PIG_ID to the form using SELECT
// but for now you can just hard code in the ID of your pig (which is given to you on a successful INSERT in the above post)
// The pig id will be in $_POST['PIG_ID']
// To write the UPDATE query from scratch it would look similar to
// UPDATE petGuineapigs SET pigName = 'The pig formally known as Johny', pigColour = 'purplybrown', pigAge = 136, pigDescription = 'Johnny did not like beging known as johnny so he changed his name.'
// WHERE PIG_ID = 1
// Because of the slightly more complex syntax of the UPDATE query, we have added the updates to the $updates array so we can use
// a foreach loop to create the updates in the list
$query = "UPDATE petGuineapigs SET "; // The first part of the query
$i = 0; // because we need a comma to separate values and don't want a trailing comma we will only add the comma to the second
// update....
foreach ($updates as $column => $value)
{
$query .= ($i > 0 ? ', ' : '') . $column . " = '" . $value . "'";
$i++; // increment one to add commas for all after the first
}
// finish off the query with the WHERE clause
$query .= " WHERE PIG_ID = {$_POST['PIG_ID']} ";
// now we will run the query
mysql_query($query)
or
die ('Shite! -- Go and sit in the corner for a minute ' . mysql_error());
// Okay, just because we didn't die does not mean it was a success. mysql will consider it a success even if it didn't add anything
// we have to check and see if any rows were affected
$success = mysql_affected_rows(); // How many rows were updated
if ($success)
// Successful? Jump up and down and clap excitedly
$message = 'Your guinea pig was successfully updated... the query looked like this <pre>' . $query . '</pre>';
else
// Oh! Punish yourself by biting your pinkie finger
$message = ' Nothing was updated, your pig must be having an identity crisis :( check your PIG_ID';
} else {
// Now we are in here because we had some errors so we will add the errors to a message string
// to display later.
$message = '<strong>Something went awry!</strong>
<ol>';
foreach ($errors as $error)
$message .= '<li>' . $error . '</li>';
$message .= '</ol>';
}
}
?>
<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>
I was a little tired writing this so if you spot an error please PM me and I will update.
Cheers
Last edited by Rakuli; Oct 28th, 2007 at 09:52.
|
|
|
|
|
Oct 28th, 2007, 09:50
|
#4 (permalink)
|
Join Date: Sep 2007
Location: Australia
Age: 24
Posts: 980
|
Pull it Out -- The SELECT Query
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...
Last edited by Rakuli; Oct 28th, 2007 at 11:14.
|
|
|
|
| | |
Login
Contact Us
Advertise
Page Info
pen Source of 
