PapaFace.com

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.

$del = mysql_query("DELETE FROM `new_table` WHERE `id`= '1' ",$conn) or die(mysql_error());
if (mysql_affected_rows() == 1)
 {
 echo "Record Deleted";
 }
else
 {
 echo "There was a problem deleting the specified record.";
 }

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:

$drop = mysql_query("DROP TABLE `new_table` ",$conn) or die(mysql_error()); 
  if($drop)
   { 
    echo "new_table has been dropped from the database.";
   }

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!

One of the most basic things when writing code in PHP is to use the PHP tags which signify the beginning and the ending of the PHP code. You must have the tags in order for PHP to process the code you write. The tags are:

beginning tag:

<?php

ending tag:

?>

e.g

<?php
//code here
?>

You can have anything proceeding/preceding the PHP tags, but anything in between them must be valid PHP.

I advise you to only use the tags that I have stated above. Try not to use tags like:

<? 
//some code
?>

or

<?= "Hello!" ?>

Shorthand tags are not 100% compatible on 100% of servers, however:

<?php
//some code
?>

is! 😉

We’ll start with something pretty simple and it is the echo statement.
This is an example of the echo statement:

<?php
echo "Hello you!";
?>

When the script is run, the page will display “Hello you!”.
Notice the semicolon? The semicolon (;) is a line terminator. It tells PHP that you are finished with that particular set of instructions, and that PHP can continue with the script until it reaches the end. If the semicolon is not present at the end of instructions/commands/statements/variables a error may be triggered as a result.

Now lets move on to something a little more useful, the if/else statement.

This is an example of an if/else statement:

<?php
if (5 > 10)
 {
 echo "5 is greater than 10";
 }
else
 {
 echo "5 is not greater than 10";
 }
?>

Okay with an if statement you must define the conditions of the if between the brackets. The aim of an if statement, is to get true returned (a boolean). If the condition returns false, then it will run the code in the else statement (if there is one).

In the code above the if statement will return false and display “5 is not greater than 10” – understand?

Yeah its pretty easy at this level.
If I used this code:

<?php
if (5 < 10)
 {
 echo "5 is less than 10";
 }
else
 {
 echo "5 is NOT less than 10";
 }
?>

The if statement would return true, and therefore we would get “5 is less than 10” printed out on the page.

When writing an if statement there are two important parts.

1. The condition – this is contained in between brackets (5 < 10)
2. The curly braces – { } – the code you want to execute should be placed in between the two curly braces. The only exception to this is when you only have one line of instructions like this:

<?php
if (5 < 10)
echo "5 is less than 10";
?>

That concludes this part of my help guide I hope it was useful to those starting to learn PHP.
If you want to look into the if/else statement more, look into elseif. The PHP manual is a great resource, so make good use of it. It contains everything you need to know about PHP (literally).

Feel free to comment on this post, I appreciate all the comments I receive! 😀