WPII -PHP - Intro

9 - PHP:MySQL - Insert data

We're going to build on the page we built last time. But first ...

Server Variables

All servers maintain a set of variables that provide information such as where the user came from, and other useful information. You can access these variables by name in PHP. For example, if you wanted to know the link that the user clicked to get to the current page, you could use the $HTTP_REFERER server variable.

(from juicystudio)

Environment Variables

Beyond the variables you declare in your code, PHP has a collection of environment variables, which are system defined variables that are accessible from anywhere inside the PHP code, inside of functions or out.

All of these environment variables are stored by PHP as arrays. Some you can address directly by using the name of the index position as a variable name. Other can only be accessed through their arrays. Which can be accessed which way is heavily dependent on how strict the security settings are for PHP on your server.

Some of the environment variables include:

$HTTP_SERVER_VARS[ ]    Contains information about the server and the HTTP connection.
$HTTP_COOKIE_VARS[ ]     Contains any cookie data sent back to the server from the client. Indexed by cookie name.
$HTTP_GET_VARS[ ]           Contains any information sent to the server as a search string as part of the URL.
$HTTP_POST_VARS[ ]         Contains any information sent to the server as a POST style posting from a client form.
$HTTP_POST_FILES[ ]         Contains information about any uploaded files.
$HTTP_ENV_VARS[ ]            Contains information about environmental variables on the server.

(from here)

Here's a list of all the Server and Environment variables

Here's what we're working towards building in this exercise.

Ok, so let's start coding.

copy from just below this line....
<?
///This is the code to get the information from the table so it can be displayed
// You need to do all your database connection work before the html starts
// So first, we define the connection string
//with this syntax: mysql_connect($host, $username, $password)
//This gets us talking to mysql on the server
$conn = mysql_connect("localhost", "username","password") or die("Could not connect to database");

//Here we select the database we want to use with $theConnectionString
mysql_select_db("databaseName") or die("Could not select database");

//Define the SQL statement that gets sent to the database
$query = "SELECT * FROM userInfo";

//Put the results of your SQL statement into $aVariable, in this case $result.
//Note that the variables used in this statement, $query and $conn, are what
//we defined in the previous few steps.
$result = mysql_query($query, $conn);

//You can get the number of returned records this way
$num_rows = mysql_num_rows($result);

//Planning ahead here. When I tell how many record(s) were retrieved, I want proper grammar.
if ($num_rows > 1 || $num_rows == 0)
{
$recordWord = "records";
}
else
{
$recordWord = "record";
}

//Close your connection to the database once you've gotten the information you need
mysql_close($conn);

// From here down within the head we have new code for this exercise

// This is the code to insert a new record via the form
if ($HTTP_SERVER_VARS['QUERY_STRING']<> '') {

//Define the connection string
$conn = mysql_connect("localhost", "username","password") or die("Could not connect to database");

//Connect to "the database" with $theConnectionString
mysql_select_db("databaseName") or die("Could not select database");

//The values from the form get built into the query
$query = "INSERT INTO userInfo VALUES ('','$firstName','$lastName')";

}
?>

<head>
<title>PHP/MySQL</title>
</head>

<body>
<p>Here's some static text, just so we know the page is working.
</p>
<p>

<?
// This shows the records we retrieved in the PHP code in the head of the page.
//If the $result variable has any contents
if ($result){

//Notice the dot used for concatenation, and how you can mix
//variables and literal text strings
echo " You retrieved " . $num_rows . " " . $recordWord . ".";

// Now we let PHP start writing the html for our table
echo "<table border=1>";

// $row gets an array stuffed into it. Each record in our recordset is a row in the array.
// The while statement iterates its way through $row. Each time
// through, if there's another element in the array,
// it executes the code in the function block and uses the values in row[iteration number]
// of the array.
while ($row = mysql_fetch_array($result)) {

// The elements of the array are automatically named for the column they come from in the table
$firstName = $row['firstName'];
$lastName = $row['lastName'];
$id = $row['id'];

// Cycle color for each iteration.
//Note the shorthand if-then notation
$rowColor == "#ffdddd" ? $rowColor = "#ddffdd": $rowColor = "#ffdddd";

// Write the html to put the data of each record in its own table row.
echo "<tr bgcolor = '" . $rowColor . "'>\n<td width='30%'>";

// Write the variables into the table cell
echo $id . " " . $firstName . " " . $lastName ;

// Close the html table row
echo "</tr>\n</td width='30%'>";
}

//If we're done with the while loop, we're done with the table, so write the html to close the table.
echo "</table>";

}
else
{
//If no data was found in the database, give the user feedback explaining the situation.
echo " sorry no data found ";
}
?>

</p>

<form name="form1" id="form1" method="post" action="insert.php">
<table width="300" border="1" cellspacing="0" cellpadding="5">
<tr>
<td width="64"><div align="right">first name:</div>
</td>
<td width="210"><input name="firstName" type="text" id="firstName" /></td>
</tr>
<tr>
<td><div align="right">last name:</div>
</td>
<td><input name="lastName" type="text" id="lastName" /></td>
</tr>
<tr>
<td><div align="right"></div>
</td>
<td> </td>
</tr>
<tr>
<td> </td>
<td><input type="submit" name="Submit" value="submit" /></td>
</tr>
</table>
</form>
</body>
</html>
... to just above this line.

Now that we have this page made, we need to make the page the form sends it's information to, insert.php, the "putter page".

The putter page

Table of Contents