Today I am going to show you various things that you can do with MySQL using PHP.
I am going to assume you have fairly good knowledge of PHP while following these instructions.
Connecting to MySQL
Connecting to MySQL is extremely simple. I am going to presume you know your MySQL credentials, if you don't know them, find them out 
.
$databasehost =
"localhost";
//put your MySQL host into this variable. It is usually localhost
$databaseusername =
"root";
//put your MySQL username into this variable.
$databasepassword =
"somethingreallydifficulttoguess";
//put your MySQL password into this variable.
// I have used long variable names because the likelihood of them being overwritten is unlikely.
$conn =
mysql_connect($databasehost,
$databaseusername,
$databasepassword) or
die (mysql_error());
And thats it. In the event that your credentials are incorrect and error will be produced.
Creating a MySQL database using PHP
Creating a database in PHP is extremely simple.
mysql_query("CREATE DATABASE `database_name`",
$conn) or
die (mysql_error());
//this will create a database called "database_name" and use the connection we established earlier to create that database.
Selecting the MySQL database
Selecting the MySQL database is even simpler. Once you know your database name you can use this code to select the database:
$databasename =
"database_name";
// the name of the database you want to select.
mysql_select_db($databasename,
$conn) or
die (mysql_error());
//the database you created earlier is now selected. If you already have a database created, you do not need to run the CREATE DATABASE code obviously.
Creating a MySQL table within a database
Things start to get a bit more complicated when it comes to creating tables. You may want to look at the MySQL website to get some more information on how to create tables using SQL.
Here is how we would create a table called "new_table" in the database.
mysql_query("CREATE TABLE `new_table` (`id` int NOT NULL AUTO_INCREMENT,`name` varchar(25),`age` int, PRIMARY KEY(id))",
$conn) or
die(mysql_error());
This code will create a table with 3 fields in (id - which is the primary key, name which allows up to 25 alphanumeric characters and age that allows an integer) .
Retrieving data from a MySQL table
I am going to show you a basic way of getting data from a table when you know all the field names of that database.
There are numerous ways of doing this (look into mysql_fetch_assoc() and mysql_fetch_array())
$select =
mysql_query("SELECT `id`,`name`,`age` FROM `new_table`") or
die (mysql_error());
if (mysql_num_rows($select) >
0)
{
while (list($_id,
$_name,
$_age) =
mysql_fetch_array($select)) //gets the data from the SELECT query and puts it into variables
{
echo "The ID for this record is: " .
$_id .
"\n";
echo "The Name for this record is: " .
$_name .
"\n";
echo "The Age for this record is: " .
$_age .
"\n";
}
}
else
{
echo "There are no records to display";
}
The above example is the way I sometimes chose to do my SELECT queries because it is quite self explanatory with the variables. I would not recommend this method however if you have a lot of variables in your script as it could pose problems with overwriting variables.
This is another way of performing the same query:
$select =
mysql_query("SELECT * FROM `new_table`") or
die (mysql_error());
if (mysql_num_rows($select) >
0)
{
while ($row =
mysql_fetch_assoc($select)) //gets the data from the SELECT query and puts it into the $row array
{
echo "The ID for this record is: " .
$row['id'] .
"\n";
echo "The Name for this record is: " .
$row['name'] .
"\n";
echo "The Age for this record is: " .
$row['age'] .
"\n";
}
}
else
{
echo "There are no records to display";
}
The above does the same thing as the example beforehand, but utilizes only one variable.
Deleting data from a MySQL table
The DELETE query is extremely simple. You simply define the table from which you'd like to delete a record, and specify a record. In this case we are specifying the record with an id of 1.
Dropping a table from a MySQL database
If you would like to remove a table and all its data from a MySQL database then use the following code:
Inserting data into a table
Inserting data into a table is extremely simple.
$insert =
mysql_query("INSERT INTO `tblname` (`name`,`username`,`email`) VALUES ('Andrew','papa_face','someone@somewhere.com')",
$conn) or
die(mysql_error());
if($insert)
{
echo "The record has been inserted into the database.";
}
Of course you can make insert queries dynamic by using variables, but that is one quick and easy example of how to insert data into a table 
.
And that's it!
I hope this blog post have been very informative. Any questions or comments are greatly appreciated.
Thanks for reading!
![[Slashdot]](http://slashdot.org/favicon.ico)
![[Digg]](http://digg.com/favicon.ico)
![[Reddit]](http://reddit.com/favicon.ico)
![[del.icio.us]](http://del.icio.us/favicon.ico)
![[Facebook]](http://www.facebook.com/favicon.ico)
![[Technorati]](http://technorati.com/favicon.ico)
![[Google]](http://www.google.com/favicon.ico)
![[StumbleUpon]](http://www.stumbleupon.com/favicon.ico)
