Bryon
I Fix Things
- Messages
- 8,149
- Reaction score
- 101
- Points
- 48
Introduction: This tutorial is going to be about communicating with a MySQL server with PHP. I am going to try and cover connecting to database servers, querying servers (SELECT/UPDATE/INSERT/DELETE queries), and a few other related things. This tutorial will assume that you have *at least basic* PHP knowledge, and maybe even some HTML knowledge. Ok, now to begin.
In order to use anything in this tutorial, you will need access to a MySQL database, as well as MySQL support compiled into PHP. Most web host's have MySQL enabled and compiled, so it should work most of the time. If you are unsure, ask your host.
Section 1 ~All About MySQL:
What is MySQL? MySQL is a multithreaded, multi-user, SQL (Structured Query Language) relational database server (RDBMS). MySQL is open source software available either under the GNU General Public License (GPL) or under other licenses when the GPL is inapplicable to the intended use. (Definition provided by http://en.wikipedia.org/wiki/Mysql)
Why would I want to use MySQL on my website? There are many reasons why you would need/want to use a MySQL database for your site. Using a database means that you can easily store, retrieve, and update data, for basically anything you want. Databases allow you to create scripts on your site that can for example, let visitors register an account, and log in to it, and update their personal information for it. Once you learn how to communicate with a MySQL server fluently, a lot of new possibilities open up on what you can do with PHP.
Section 1 ~MySQL Database Set Up/Layout/Arrangement:
I'm going briefly explain how MySQL databases are set up, so you can get an understanding on how data is stored, and where. Each "part" I am going to talk about here goes in order, the first being the "highest", the second being "inside" the first, and so on.
First, MySQL servers contain databases. Think of the database as a "main storage" area. Second, the tables. Each database consists of tables. Each table consists of rows of data that each different "value" is seperated into columns. Data is stored in tables in "rows". I probably have confused you, so here is a small "diagram" of how databases are set up.
That image shows a database, named "Database" that contains 3 tables, Table 1, Table 2, and Table 3. I only showed Table 1, as an example, but table 2, 3, and so on would also have columns and rows in it also. Column names can be anything, Within reason
. Columns have different types, which determine what kind of data is stored in them. I will explain a few of the basic column types here:
Varchar (Num): The varchar column type can hold any type of data, up to "Num" length. (Letters, numbers, meta-characters) The max length of a string a varchar column can hold is 255 characters long.
Int (Num): The int (Integer.. ) column type can hold any integers, up to "Num" length. The max length of a string a int column can hold is 255 characters long.
Text: The text column type can hold as many characters as you want, and does not have a "limit" on the number of characters that can be in it. (Well it does when you get up to high numbers of GB's.. But never mind that for now.)
Those are three of the most basic column types for a MySQL table. For a beginner, I would just recommend using these for now. If you want to check out all of the column types, take a look at: http://dev.mysql.com/doc/refman/4.1/en/column-type-overview.html
Section 2 ~The Basics:
Ok so now, you know what MySQL is, and a little about MySQL. I'll now begin to teach you how to use PHP and MySQL together.
A - Connecting To The MySQL Server
To connect to a MySQL database, you use the php function mysql_connect(). This function establishes a connection to the MySQL server and enables you to send and receive data to and from the server. The basic syntax to use this function is:
That "snippet" of code right there will establish a connection to $server, using the account $username and password $password. The reason that the result's are placed into the variable $mysqlconnection is quite simple. If you ever come across the need to connect to more then 1 MySQL server, you use the returned data from mysql_connect() to differentiate between the multiple connections. Also, the returned data is checked to be true/false, to have a simple check to make sure the connection was successful. If it was not, an error will be displayed that will contain the exact error returned from the server. I will talk about the mysql_error() function later on in the tutorial.
B - Selecting a MySQL Database To Use:
Now that you are connected to a MySQL Server, you need to select a database to send queries to, get data from, etc. To do this, you use the php function mysql_select_db(). Mysql_select_db() takes two arguments, the first being the database you wish to use, and the second, the mysql_connect() resource link. (Remember the $mysqlconnection variable from before? That is what I mean by "resource link".) The "resource link" is optional. The only reason will need it is if you are dealing with multiple MySQL server connections, each one being in a seperate "resource link". Here is an example using mysql_select_db() with one connection:
Remember that the $mysqlconnection being used in that mysql_select_db() is only required if you need to have more then one server connection at a time. Any other time, you don't need it and can omit it completely. The mysql_select_db() function will use the current connection and select the database under that. Example:
You should be able to understand that completly by now. If you do not, go back and read the last few paragraphs.
One more thing that I wish to talk about in this section is the use of mysql_error(). Whenever you use a mysql function in PHP, if it errors at all, the error returned from the MySQL server will be placed into mysql_error(). When you echo this function, that is what you will see. The reason I am telling you this is because this function is very helpful when debugging mysql_query()'s, which you will learn about later in the tutorial. Mysql_error() holds only the last error returned from a MySQL server, and gets "overwritten" when a new error is triggered.
....
The rest of this tutorial is on my site. The reason I'm only posting the first third of it is because 1, the tutorial is too long to put in one post, and 2, to gain more hits on my site. :-D
The URL to the rest of this tutorial is:
http://www.nedren.elementfx.com/vie...cat=1&scat=1&sdv=VUVoUUlHRnVaQ0JOZVZOUlRBPT0=
In order to use anything in this tutorial, you will need access to a MySQL database, as well as MySQL support compiled into PHP. Most web host's have MySQL enabled and compiled, so it should work most of the time. If you are unsure, ask your host.
Section 1 ~All About MySQL:
What is MySQL? MySQL is a multithreaded, multi-user, SQL (Structured Query Language) relational database server (RDBMS). MySQL is open source software available either under the GNU General Public License (GPL) or under other licenses when the GPL is inapplicable to the intended use. (Definition provided by http://en.wikipedia.org/wiki/Mysql)
Why would I want to use MySQL on my website? There are many reasons why you would need/want to use a MySQL database for your site. Using a database means that you can easily store, retrieve, and update data, for basically anything you want. Databases allow you to create scripts on your site that can for example, let visitors register an account, and log in to it, and update their personal information for it. Once you learn how to communicate with a MySQL server fluently, a lot of new possibilities open up on what you can do with PHP.
Section 1 ~MySQL Database Set Up/Layout/Arrangement:
I'm going briefly explain how MySQL databases are set up, so you can get an understanding on how data is stored, and where. Each "part" I am going to talk about here goes in order, the first being the "highest", the second being "inside" the first, and so on.
First, MySQL servers contain databases. Think of the database as a "main storage" area. Second, the tables. Each database consists of tables. Each table consists of rows of data that each different "value" is seperated into columns. Data is stored in tables in "rows". I probably have confused you, so here is a small "diagram" of how databases are set up.
That image shows a database, named "Database" that contains 3 tables, Table 1, Table 2, and Table 3. I only showed Table 1, as an example, but table 2, 3, and so on would also have columns and rows in it also. Column names can be anything, Within reason
. Columns have different types, which determine what kind of data is stored in them. I will explain a few of the basic column types here:Varchar (Num): The varchar column type can hold any type of data, up to "Num" length. (Letters, numbers, meta-characters) The max length of a string a varchar column can hold is 255 characters long.
Int (Num): The int (Integer.. ) column type can hold any integers, up to "Num" length. The max length of a string a int column can hold is 255 characters long.
Text: The text column type can hold as many characters as you want, and does not have a "limit" on the number of characters that can be in it. (Well it does when you get up to high numbers of GB's.. But never mind that for now.)
Those are three of the most basic column types for a MySQL table. For a beginner, I would just recommend using these for now. If you want to check out all of the column types, take a look at: http://dev.mysql.com/doc/refman/4.1/en/column-type-overview.html
Section 2 ~The Basics:
Ok so now, you know what MySQL is, and a little about MySQL. I'll now begin to teach you how to use PHP and MySQL together.
A - Connecting To The MySQL Server
To connect to a MySQL database, you use the php function mysql_connect(). This function establishes a connection to the MySQL server and enables you to send and receive data to and from the server. The basic syntax to use this function is:
PHP:
<?php
$username = ""; // MySQL Username
$password = ""; //MySQL Password
$server = ""; // MySQL server you wish to connect to. Usually "localhost"
$mysqlconnection = mysql_connect($server, $username, $password);
if (!$mysqlconnection) {
die('There was a problem connecting to the mysql server. Error returned: '. mysql_error());
}
?>That "snippet" of code right there will establish a connection to $server, using the account $username and password $password. The reason that the result's are placed into the variable $mysqlconnection is quite simple. If you ever come across the need to connect to more then 1 MySQL server, you use the returned data from mysql_connect() to differentiate between the multiple connections. Also, the returned data is checked to be true/false, to have a simple check to make sure the connection was successful. If it was not, an error will be displayed that will contain the exact error returned from the server. I will talk about the mysql_error() function later on in the tutorial.
B - Selecting a MySQL Database To Use:
Now that you are connected to a MySQL Server, you need to select a database to send queries to, get data from, etc. To do this, you use the php function mysql_select_db(). Mysql_select_db() takes two arguments, the first being the database you wish to use, and the second, the mysql_connect() resource link. (Remember the $mysqlconnection variable from before? That is what I mean by "resource link".) The "resource link" is optional. The only reason will need it is if you are dealing with multiple MySQL server connections, each one being in a seperate "resource link". Here is an example using mysql_select_db() with one connection:
PHP:
<?php
$mysqlconnection = mysql_connect($server, $username, $password);
$databaseconnection = mysql_select_db($database, $mysqlconnection);
if (!$databaseconnection) {
die('There was a problem using that mysql database. Error returned: '. mysql_error());
}
?>Remember that the $mysqlconnection being used in that mysql_select_db() is only required if you need to have more then one server connection at a time. Any other time, you don't need it and can omit it completely. The mysql_select_db() function will use the current connection and select the database under that. Example:
PHP:
<?php
$mysqlconnection = mysql_connect($server, $username, $password);
$databaseconnection = mysql_select_db($database);
if (!$databaseconnection) {
die('There was a problem using that mysql database. Error returned: '. mysql_error());
}
?>You should be able to understand that completly by now. If you do not, go back and read the last few paragraphs.
One more thing that I wish to talk about in this section is the use of mysql_error(). Whenever you use a mysql function in PHP, if it errors at all, the error returned from the MySQL server will be placed into mysql_error(). When you echo this function, that is what you will see. The reason I am telling you this is because this function is very helpful when debugging mysql_query()'s, which you will learn about later in the tutorial. Mysql_error() holds only the last error returned from a MySQL server, and gets "overwritten" when a new error is triggered.
....
The rest of this tutorial is on my site. The reason I'm only posting the first third of it is because 1, the tutorial is too long to put in one post, and 2, to gain more hits on my site. :-D
The URL to the rest of this tutorial is:
http://www.nedren.elementfx.com/vie...cat=1&scat=1&sdv=VUVoUUlHRnVaQ0JOZVZOUlRBPT0=
Last edited:
