Creating and inserting data into a PHP and MySQL Database

[Peter L]

A MYSQL database can be used to store tons of information and can quickly be retrieved through the use of a query. I have not debugged this code because I don't feel like creating a new database to test with. There may be errors, if you find one please tell me.

Connecting to your mysql server! I like to use variables for the host, username and password, you can however choose differently just replace the text in for the variables.

PHP Code:

<?
//Database info
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "root";
//End Database info

$con = mysql_connect('$dbhost', '$dbuser', '$dbpass');
if(!$con) //The ! means Does/Can Not. Basically if it fails it does the first {} instead of last.
{
die ("Failed to Connect to MYSQL Database"); //Stops the execution of scripts because there is no connection
} 


Now for creating a DB. Make sure you have the previous code in your file prior to this so you can create tables.

PHP Code:

if (!mysql_query("CREATE DATABASE test_db",$con))
{
die ("Failed to create DB!");
}
else
{
echo "Database created succesfully";
} 


Now for creating a table, which is what stores the actual data in rows.

PHP Code:

mysql_select_db("test_db",$con); //Replace "test_db" with your DB.
//Info for DB
$table = "CREATE TABLE Users
(
User_ID int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (User_ID),
Username varchar(16),
Password varchar(32),
)";

$query = mysql_query($table,$con);
if (!$query){
die ("Failed to create Table");
}
else
{
echo "Created table succesfully";
}
?>

Now you need to insert data? I have not debugged this code. I do not have the time set up a separate db to do this so beware errors could exist.

First setup your connection to the database.

PHP Code:

<?
//Database info
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "root";
//End Database info

$con = mysql_connect('$dbhost', '$dbuser', '$dbpass');
if(!$con) //The ! means Does/Can Not. Basically if it fails it does the first {} instead of last.
{
die ("Failed to Connect to MYSQL Database"); //Stops the execution of scripts because there is no connection
}
?>

Make sure you have your table and database already created. See how to do that here. I will be using the database I made which I just linked.

PHP Code:

<?

$user = "admin";
$pass = "admin";

mysql_select_db('test_db',$con);

$insert = mysql_query("INSERT INTO Users (Username, Password) //What you what to be inserted.
VALUES ($user, $pass)");

$query = mysql_query($insert);

if(!$query){
die ("Failed to insert user");
}
else
{
echo "User added successfully";
}
?>

If we wanted to get data from another page that uses a form simply replace our variables user and pass with this. Make sure you sanitize them, this example is NOT sanitized.

PHP Code:

$user = "$_POST['user']";
$pass = "$_POST['pass']"; 


Hope you find this tutorial useful.