First we're going to make a database, and then create a table with 4 rows identical to whats shown in the image below
Auto-increment means everytime a record is added, it will automatically increase by 1. This can be useful if you are wanting to assign unique member ID's to different people, or if you're counting the amount of messages submitted to a shoutbox.
Next we're going to learn how to insert items into a DB using a form. After we cover that, we will learn how to:
- Delete records by typing in an ID number via a form.
- Query for any item using ID #, subject, body, or author.
- List the mysql content on homepage
Open up your conventional PHP/HTML/CSS editor, and copy/paste this backbone insert code
Name this file insert.php
CODE
<?php
$con = mysql_connect("localhost","username","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("dbname", $con);
$sql="INSERT INTO table (id, author, subject, body)
VALUES
('$_POST[id]','$_POST[author]','$_POST[subject]','$_POST[body]')";
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "Article added";
mysql_close($con)
?>
$con = mysql_connect("localhost","username","password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("dbname", $con);
$sql="INSERT INTO table (id, author, subject, body)
VALUES
('$_POST[id]','$_POST[author]','$_POST[subject]','$_POST[body]')";
if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "Article added";
mysql_close($con)
?>
What this does, is basically take the variables ID , author, subject, and body, and posts them into the table located in the MySQL database.
After you've filled in the appropriate information in INSERT.PHP , make a file called INDEX.PHP
CODE
<html>
<body>
<!-- Form-to-MYSQL insertion code-->
<form action="insert.php" method="post">
Author: <input type="text" name="author">
Subject: <input type="text" name="subject" />
Body: <input type="text" name="body" width=500 height=500/>
<input type="submit" />
</form>
<!-- MYSQL Delete Type in ID of post you want deleted-->
<form action="delete-db.php" method="post">
Delete Name: <input type="text" name="id" />
<input type="submit" />
</form>
<!-- MYSQL Query Type in ID of post you want to search for-->
<form action="query.php" method="post">
Search for ID: <input type="text" name="query" />
<input type="submit" />
</form>
<!-- Shows current DB content -->
Current List :
<?php
$con = mysql_connect("localhost","user","pass");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("dbname", $con);
$result = mysql_query("SELECT * FROM table");
echo "<table border='1'>
<tr>
<th>ID</th>
<th>Subject</th>
<th>Body</th>
<th>Author</th>
</tr>";
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['subject'] . "</td>";
echo "<td>" . $row['body'] . "</td>";
echo "<td>" . $row['author'] . "</td>";
echo "</tr>";
}
echo "</table>";
mysql_close($con);
?>
</body>
</html>
<body>
<!-- Form-to-MYSQL insertion code-->
<form action="insert.php" method="post">
Author: <input type="text" name="author">
Subject: <input type="text" name="subject" />
Body: <input type="text" name="body" width=500 height=500/>
<input type="submit" />
</form>
<!-- MYSQL Delete Type in ID of post you want deleted-->
<form action="delete-db.php" method="post">
Delete Name: <input type="text" name="id" />
<input type="submit" />
</form>
<!-- MYSQL Query Type in ID of post you want to search for-->
<form action="query.php" method="post">
Search for ID: <input type="text" name="query" />
<input type="submit" />
</form>
<!-- Shows current DB content -->
Current List :
<?php
$con = mysql_connect("localhost","user","pass");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("dbname", $con);
$result = mysql_query("SELECT * FROM table");
echo "<table border='1'>
<tr>
<th>ID</th>
<th>Subject</th>
<th>Body</th>
<th>Author</th>
</tr>";
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['subject'] . "</td>";
echo "<td>" . $row['body'] . "</td>";
echo "<td>" . $row['author'] . "</td>";
echo "</tr>";
}
echo "</table>";
mysql_close($con);
?>
</body>
</html>
Here's what it will look like (pretty bland , but we're just working on the code for now!
)
After you have the two files, INDEX.PHP and INSERT.PHP made, go ahead and make DELETE-DB.PHP
Insert this code in there
CODE
<?php
$con = mysql_connect("localhost","user","pw");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("dbname", $con);
mysql_query("DELETE FROM table WHERE id='$_POST[id]'");
mysql_close($con);
?>
$con = mysql_connect("localhost","user","pw");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("dbname", $con);
mysql_query("DELETE FROM table WHERE id='$_POST[id]'");
mysql_close($con);
?>
Basically what this does is it retrieves the query from the form ($_POST[id]) , and deletes it from the DB. If a member registered through your script and you wanted to delete him from the site, just find his #ID and submit it in this form
Next is the Query . . .
Open up editor and name the file QUERY.PHP
CODE
<?php
$con = mysql_connect("localhost","user","pass");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("dbname", $con);
$result = mysql_query("SELECT * FROM table
WHERE id='$_POST[query]'");
while($row = mysql_fetch_array($result))
{
echo $row['id'] . " " . $row['subject'] . " " . $row['body'] . " " . $row['author'];
echo "<br />";
}
?>
$con = mysql_connect("localhost","user","pass");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("dbname", $con);
$result = mysql_query("SELECT * FROM table
WHERE id='$_POST[query]'");
while($row = mysql_fetch_array($result))
{
echo $row['id'] . " " . $row['subject'] . " " . $row['body'] . " " . $row['author'];
echo "<br />";
}
?>
This queries the DB for the variable inserted in the form on INDEX.PHP , and then echoes the results in a form.
That's it for now, if you have any questions, feel free to reply / PM me .
