PHP and MySQL Basics
Now that you have your table, you are ready to fill it. If you have not created your table, you should probably go back and do so now.
As you may have figured out from the table name, this article is going to walk through the skeleton of a news script... so for inserting data, we will insert an article. Before we do that though, we need to know how to do an insert.
<?
// connect to mysql and select database
$db = mysql_pconnect("localhost","USER","PASS");
mysql_select_db("DATABASENAME",$db);
// build query
$sql = "INSERT INTO news (author,content,posted) VALUES ('Fred','This is my first article!',now())";
// run the query
mysql_query($sql);
?>
If you actually tried that, you will now have an entry in your database. Notice we don't have to specify todays date, in MySQL using now() in a DATE column will automaticly insert the current date for you.
We specify the column names before the values to make sure they match up. If you do not specify the column names, you have to make sure the values are entered in the same order as they are in the database. This can cause problems if the order gets changed later. As a rule, you should always specifiy the column names. You may have also noticed that we did not enter a value for id. This will automaticlly get added as a result of our auto_increment in the table definition.
Putting it together
Now that we are experts on inserting data into MySQL, we can build an HTML form that interacts with the script. When an HTML form is posted to a PHP file, all the inputs are passed as variables, using the input name.
The contents of <input name="title"> will be accesable by calling $_POST['title'] in the called PHP script. Using this knowledge, we can come up with the following code:
<?
// check to see if we have an author and some content
if (!empty($_POST['content']) && !empty($_POST['author'])) {
// we do, that means the form has been submited, and we can insert the data. build the query
$sql = "INSERT INTO news (author,content,posted) VALUES ('{$_POST['author']}','{$_POST['content']}',now())";
// run the query
mysql_query($sql);
echo 'Done!';
} else {
// we don't have an author and content.
// Either the form was not submited, or it was submited without enough data. show the form
// $_SERVER['PHP_SELF'] as the form action will cause the script to post to itself
echo '<form method="post" action="'.$_SERVER['PHP_SELF'].'">
<input type="text" name="author">
<textarea name="content" rows="4" cols="20"></textarea>
<input type="submit" value="Add Article!">
</form>';
}
?>
This code will handle both displaying the form, and adding the article. If there is not enough data to add the article, it will show a prompt for data. Once you get this working, add a few test articles so that we have data to extract in the next part of this article.
