Manipulating MySQL with PHP

Manipulating MySQL with PHP
Beginners Tutorial

Introduction

If you do not know what MySQL is, then I suggest reading the spoiler below.

MySQL is a widely used program that allows anyone to easily create and host their own database. MySQL is free and can be set up in a matter of minutes. When you create your own database, you will need to remember your host, your username, and your password. Many website hosting sites have included MySQL database support, which means you can use the MySQL database they provide for you and edit it within the host site's Control Panel without having to download MySQL onto your hard-drive. For simple needs, and for safety reasons, using provided MySQL service from a website hosting sites may be the best choice for you.

To begin, we will need to start our script. To do this, we will add two simple lines:

PHP Code:

<?php
?>

This is what we will use to surround our PHP code, thus allowing us to declare that this section of the document is PHP... obviously.

After this, we will need to declare several variables that will control what database the PHP file will be accessing. These variables are not necessary, but do make it easier when calling on the database information further into the code.

PHP Code:

Now that we have declared these variables, it will be easier to use them further into the code, as I said before. Now we can try to connect to the database. To do this, we will use the PHP function that will attempt to connect to our database using the information we have declared. After this, we will try to attempt to select a database from that host. We will also provide code for an instance where the connection to the database fails.

PHP Code:

mysql_connect($host,$username,$pwd);
@mysql_select_db($database) or die( "Attempt to connect has failed.");

Also, when you are finished with your code, put this line of code at the end to end the connection between the web page and your MySQL database:

PHP Code:

mysql_close();

Now that we have connected, we will now be able to manipulate the database.

Final Code:

PHP Code:

<?php
$pwd = 'database password'; //What is the password to access your database?
$username = 'database username'; //What username will be used to access the database?
$database = "database name"; //What is the name of the database?
$host = "database host"; //What is the local host of your database?

mysql_connect($host,$username,$pwd);
@mysql_select_db($database) or die( "Attempt to connect has failed.");

mysql_close();
?>

Manipulating The Database

We will now continue writing the code to add onto the instance of the attempt of connecting to the database succeeding. After the page has connected to the database, we are now given the ability to issue MySQL Queries. MySQL Queries are commands, such as in the CMD, which allow us to request and modify information from our MySQL databases.

Table Manipulation

Adding A New Table

The first we will be learning is how to create a new table. By adding a table to our database, we will be able to use the database to hold data. Without tables, holding data in MySQL is not possible.
To create a table, we need to use the PHP MySQL Query Function. Within the parameters of this function, we will provide a string that will be executed as a query to the MySQL database. In this case, the query will create a new table. To eliminate confusion, we will use a variable which will make it easier to distinguish between the MySQL Query and the PHP code. When you create a table, you will have different rows that will allow you to separate data. Two of the data types for a row are provided in the example code. In the VARCHAR data type, you can choose the maximum characters allowed. In this case, 25 text characters.

PHP Code:

$query = "CREATE TABLE `a8488903_cs`.`TableName` (
`number` int,
`text` varchar(25)
)";
mysql_query($query);

The table and the rows within the table should now be declared within the database.

Final Code:

PHP Code:

Reading A Table

With this code will be able to recall the information and print it onto the screen on our web page. First, we will declare a variable called 'data'. This variable is what will hold the information we retrieve from issuing a MySQL Query that will grab information from one of our tables. After we collect the data, we will declare the variable 'info'. This will hold the array that is created when we use the MySQL Fetch Array function on our variable 'data', which will convert the information we got from our table into an easy accessible list.

PHP Code:

$data = mysql_query("SELECT * FROM $user")
or die(mysql_error());
$info = mysql_fetch_array( $data );

Now that our information is in an array, we can easily recall the information by using some simple PHP variable skills. We will now use the array to print the rows of our table onto the web page. To do this, we will call upon the variable 'info', and then put brackets to call upon different rows of our table.

PHP Code:

Print $info['row1']."<br>";
Print $info['row2']."<br>";

Final Code:

PHP Code:

Updating A Table

With this code will update the information in a table of our database. It is very easy to do this, and the code is very simple. All you will need is two things - the correct MySQL Query and the MySQL Query function to issue it. It is very self explanatory. If you do not understand this at all, then please send me an instant message and I will gladly explain it to you piece by piece. First, we declare the variable 'query' to hold the string that is our MySQL Query. After this, we use the MySQL Query function to issue our MySQL Query.

PHP Code:

$query = "UPDATE TableName SET
row1 = 'For' ,
row2 = 'The' ,
row3 = 'Lulz' ";

mysql_query($query);

Final Code:

PHP Code:

Database Manipulation

Adding A New Database

If you do not already have a database or wish to create another, creating a new database in PHP is VERY simple. In fact, it is even more simpler than creating a table. To create a new database, we use the exact same method as when creating a table, except this time we use an even shorter query. The code for this simple operation is stated below. It is very self explanatory.

PHP Code:

mysql_query("CREATE DATABASE DatabaseName");

You should now have a new database.

Final Code:

PHP Code:

Shortcuts

Easier Travel From Database To Database

Though some of you are beginners, I would like to expose to you the option of declaring the connection to a database within the MySQL Query function itself. This will allow you, that is if you wish, to connect to the database as you issue the MySQL Query function. The necessity of this for some people would be to have the ability to go back and forth between multiple databases within the same code very quickly, without having to connect, close connection, connect to a different database, close connection, reconnect to a different database, etc.. To do this, we will first hold the different connections within variables. This will allow us to easily re-declare connections.

PHP Code:

$db1_connect = mysql_connect("database host","database username","database password");
$db2_connect = mysql_connect("database host","database username","database password");

After this, we can now issue MySQL Queries for different databases without even having to disconnect or reconnect any further! Isn't that great? I know right?!?! To do this, all you have to do is after you declare the query string in the parameters of your function, just tack on a comma and add on your connection variables.

PHP Code:

$query1 = "Query for first Database";
$query2 = "Query for second Database";

mysql_query($query1,$db1_connect);
mysql_query($query2,$db2_connect);

Using this code, you will have successfully sent two different MySQL Queries to two different databases without even having to reconnect or disconnect to anything. Though of this, there is one thing have to change about the code now that we have changed to this method. We have to change the MySQL Close Connection function so that we are able to choose which databases we want to close. This will allow us to close different databases at different times, without having to disconnect them all at the same time. To do this, all we do is add on a parameter to the MySQL Close Connection function. This parameter, if you were able to predict, is our connection variables.

PHP Code:

mysql_close($db1_connect);
mysql_close($db2_connect);

You now have the ability to use multiple databases at the same time without having to disconnect one before you use the next, AND the ability to close databases individually at different times.

Final Code:

PHP Code:

<?php
$db1_connect = mysql_connect("database host","database username","database password");
$db2_connect = mysql_connect("database host","database username","database password");

$query1 = "Query for first Database";
$query2 = "Query for second Database";

mysql_query($query1,$db1_connect);
mysql_query($query2,$db2_connect);

mysql_close($db1_connect);
mysql_close($db2_connect);
?>

End

from(http://www.hackforums.net/showthread.php?tid=415237)

Pages

Sunday, March 20, 2011

Manipulating MySQL with PHP

1 comment: