Connecting to the database
Code Box
mysql_connect('host','username','password');
To connect to the database simply call the mysql_connect() function specifiying the host to which you wish to connect (this is usually 'localhost'), the username and the password. The function also returns an identifier which can be useful if you need to connect to more than one database at once. This can be later used as an optional parameter in other mysql functions. For these examples we will only use it to test connectivity.
We can expand on this by integrating a custom error message and also selecting the database we wish to work with.
Code Box
$con = @mysql_connect('localhost','monkey56657','mypassword');
if ($con)
{
mysql_select_db('webstarters_db');
}
else
{
echo 'Connection to the database was unsuccessful. Please try reloading the page.';
}
if ($con)
{
mysql_select_db('webstarters_db');
}
else
{
echo 'Connection to the database was unsuccessful. Please try reloading the page.';
}
Okay so now we are connected what can we do with the database? First of all we should create a table. This is possible using code but most people prefer to use a tool called phpmyadmin to create the required table structure. Lets imagine we have a table created as below. The article_id field has been set as a primary key (which means every record must have this field unique) and the auto_increment flag has been set. This means that for each record we store the article_id will increase by 1 each time so that each record is uniquely identifiable. The table shall be called articles_table.
Insert new records
The process of inserting new records is relatively easy. We will use the mysql_query() function to send commands to the database.
Code Box
INSERT INTO `articles_table` VALUES (null, "An example article", "One day a man went to the shop. The End", "1203271857")
The above code illustrates an example SQL query we could use. The article_id column has been given a value of null as this allows the auto_increment to issue out the next ID number for us.
Code Box
$sql = 'INSERT INTO `articles_table` VALUES (null, "' . $title . '", "' . $body .'", "' . time() . '")';
$result = mysql_query($sql);
$result = mysql_query($sql);
Using what we previously learnt about joining together strings we can use variables in place of the values making the query dynamic. We can use function in the middle of a string as well. The time() function returns the unix time stamp which is inserted into the SQL string.
If the server you are using does not have magic_quotes enabled then there are several security issues that may arise. We can protect from the majority of SQL injection issues using the mysql_real_escape_string() function.
In the next 2 lessons we will cover more about databases and data control. Remember if you not fully confident that your script is secure from MySQL injection you can post it here for us to have a look. If you can provide a phpinfo() page from your server it will help as well.
?>
discuss this topic to forum
