In this tutorial we are going to learn about basic mySQL interaction by making a link database. Firstly lets setup a table for us to work from. Put this info into your MySQL DB, ask your host how to do this.
<?php
CREATE TABLE links (
id mediumint(10) NOT NULL auto_increment,
title varchar(80),
url varchar(80),
PRIMARY KEY (id)
);
?>
<?php
<form name="links" method="post" action="add_links.php">
Title :
<input type="text" name="title">
<br >
URL :
<input type="text" name="url">
<input type="submit" name="Submit" value="Submit"></form>
?>
Save this as add.html. Notice in input type="text" name="url", the "url" bit, this is important for later as the script below requires this as $_POST['url']. Now we have our form, lets make a script that inputs the date from the form into your MySQL DB.
<?php
<?php
$db = mysql_connect("localhost","user","password");
mysql_select_db ("database");
$query = "INSERT INTO links(title, url)
VALUES('".$_POST['title']."','".$_POST['url']."')";
$result = mysql_query($query);
echo 'Link entered.';
?>
?>
Replace all the text in pink with the settings your host requires, and save this as add_links.php . I think this script is fairly self explanatory.
Making sure you have added some links into the database we created earlier, put this script in a php file. Edit the settings in red to match your server and load it up in your browser, you should see the names of the links in your databases, and when you click on them you'll be taken to the URL.
<?php
<?php
// Connect to the database
$db = mysql_connect("localhost","user","password");
mysql_select_db ("database");
// Ask the database for the information from the links table
$result = mysql_query("SELECT * FROM links");
// Now we print out the results, starting by making a table
echo "<table border = '0' width = '100%' align='center'>";
while ($rows = mysql_fetch_row($result))
// Here we make the script keep making new rows until all the links in our database are shown, this is called a loop
{
echo "<tr><td><a href='$rows[2]'>$rows[1]</a></td></tr>";
}
// Finally we close off the table
echo "</table>";
?>
?>
Next we'll find out how to delete entries.
So we've entered our links into the database, and we can now view them. But mistakes are inevitable, and so you'll want to be able to delete some entrys. The delete scripts will be made up of two scripts, one, a form to enter the title of the link which needs to be deleted. Lets take a look at this.
<?php
<!-- Heres our form -->
<form action="delete_links.php" method ="POST">
<!-- delete_links.php is the script we'll make later to actually delete from the DB -->
<p>To delete a link, enter its title exactly as appears below, and press delete.</p>
<p>
<!-- We give the entered in the box the value "delete" so it can be identified later on -->
<input type = "text" name = "delete">
<input type = "submit" value = "Delete">
</p>
</form>
<p>Here is a list of the links that have been entered :</p>
<!-- Now we print out the title of all the links, so you don't forget which links are in your database -->
<?php
// Connect to DB
$db = mysql_connect("localhost","user","password");
mysql_select_db (database);
// Query DB, show all links
$result = mysql_query("SELECT * FROM links");
if(mysql_num_rows($result))
{
while($row = mysql_fetch_row($result))
{
// Show the title (which is stored in row 1, and put
between the titles.
echo "$row[1]
";
}
}
else
// Or if there are no links, say this :
{
echo "No links in the database";
}
?>
?>
Now save this as delete.php. We now need to make the script that takes the title you entered in the form, and deletes the link with that title from your database.
<?php
<?php
//Connect to DB
$db = mysql_connect("localhost","user","password");
mysql_select_db("database");
if($_POST['delete']) {
// If $_POST['delete'] ($_POST because we passed it to this script using a form with action set to 'post' is present show this :
// This query deletes from our table (links) where the title is $delete, which is the title you entered in the form.
$result = mysql_query("DELETE FROM links WHERE title = '".$_POST['delete']."'");
echo "You deleted the following link : ".$_POST['delete'];
} else {// Otherwise, show this :
echo 'You didnt enter any data';
}
?>
?>
Save this as delete_links.php, and test it out on your server. Now just take some time to familiarise yourself as to whats going on here. If you have any problems, post them on the forums. |
