You are currently viewing our boards and having a limited access. Feel free to register so you can express your feelings with us. You need to register before you can post in forums. Registration takes only a minute. No validation via mail required in registration.

PHP MySQL - Beginners Tutorial

View previous topic View next topic Go down

PHP MySQL - Beginners Tutorial

Post by Alex on Fri Jun 11, 2010 10:36 am

Manipulating MySQL with PHP
Beginners Tutorial


Special Thanks/Appreciations

First and foremost, I would like to say that I am extremely excited for there to finally be a PHP dedicated forum. I did not know why there wasn't one in the first place, and I knew that PHP was supposed to be included in one of the forums in the Webmaster's section, but I didn't really see much strictly PHP threads there. I am very glad of this forum's creation.

Introduction

If you do not know what MySQL is, then I suggest reading the spoiler below.
Spoiler (Click to Hide)
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:
?>

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:
$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?
?>

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:
$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
Spoiler (Click to Hide)
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. To see more data types, go Here. 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:
$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.");

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

mysql_close();
?>
* Reading A Table
Spoiler (Click to Hide)
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']."
";
Print $info['row2']."
";

If you do not understand why I used the variable 'info' the way I did, I suggest you go Here.

Final Code:
PHP Code:
$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.");

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

Print $info['row1']."
";
Print $info['row2']."
";

mysql_close();
?>
* Updating A Table
Spoiler (Click to Hide)
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:
$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.");

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

mysql_query($query);

mysql_close();
?>


Database Manipulation

* Adding A New Database
Spoiler (Click to Hide)
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:
$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_query("CREATE DATABASE DatabaseName");

mysql_query($query);

mysql_close();
?>


Shortcuts

* Easier Travel From Database To Database
Spoiler (Click to Hide)
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:
$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);
?>


The End

Thank you for viewing my guide. I do hope to those of you who are beginners to PHP found this guide helpful. If you have any suggestions on further tutorials or any additions I could add to this tutorial, please send me a private message or post your suggestions in this thread. Again, thanks for reading! ^^

Spoiler (Click to Hide)
DISCLAIMER: I did not leech this tutorial off of anyone or any site. I will admit that I did visit my own MySQL server to check the credibility of my thread, for I know that PHP files in my server are a reliable source of information to recall upon, BEING that my MySQL server runs on my own working PHP files. I will also admit that I did not know PHP when I was born, so inbetween the time of this tutorial and the time that I left my mothers womb, I did somehow visit a source that taught me these things. That is all.

Alex

Posts : 2
Join date : 2010-06-11

Back to top Go down

Re: PHP MySQL - Beginners Tutorial

Post by Justin_Romile on Fri Jun 11, 2010 11:29 am

Very nice article. I have learn a lot from this one. Thank you for making this guide.
avatar
Justin_Romile

Posts : 219
Join date : 2010-05-11
Age : 22
Location : Antipolo City, Philippines

http://viostech.forumotion.com

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum