How to use php with MySQL
How to use php with mysql
First, what is MySQL? Mysql is the most used open source database engine on the internet. At the current time I believe MySQL community is being owned by Oracle, however it is still maintain as an open source software. In this article I’m going to show you basically how to use php with MySQL. I will provide simple examples, nothing too complicated and very easy to understand.
What is a Database Table?
But first, a little understanding on what is MySQL and how it is structured. As you already know, MySQL is a database, the data in a database is stored in database objects named tables. These tables have columns and rows and contain the actual data from your database!
An example of mysql database (name “students”) would be similar to this:
| LastName | FirstName | YearsOld |
|---|---|---|
| Andy | Brute | 20 |
| Jorge | Medy | 20 |
| Jason | Credy | 20 |
| Chris | Indy | 20 |
What are SQL Queries ?
A query is similar to a question or request, when you create a query to your MySQL database, you request the information needed from the tables within your database.
For example, you can get specific information from a specific row or directly from a column:
|
1 |
SELECT LastName FROM Students |
The query will return the following information from the database:
| LastName |
|---|
| Andy |
| Jorge |
| Jason |
| Chris |
How to create a connection to a MySQL Database
Contrary to how big it sounds, it’s really simple to create a connection using php, you just use the function mysql_connect() and the correct parameters:
- servername – the server where you need to connect to, by default this is localhost:3036
- username – the username that connects to the mysql server, by default this is the owner of the server process.
- password – the password of the mysql user, by default this is blank, “”
|
1 2 3 4 5 6 7 |
< ?php $connection = mysql_connect("localhost","username","password"); if (!$connection) { die('Could not connect: ' . mysql_error()); } ?> |
How to close a mysql connection
This is done really simple, by using mysql_close(), for example, using the first example we now add:
|
1 2 3 4 5 6 7 8 9 |
< ?php $connection = mysql_connect("localhost","username","password"); if (!$connection) { die('Could not connect: ' . mysql_error()); } mysql_close($connection); // This line here closes the connection ?> |
Using the function mysql_close() will close the mysql connection automatically at the end of each query. It is really important to close mysql connections as these are not unlimited and you can have problems if you leave them open.
So this is basically how you create and close a mysql connections using php.
How to create a mysql database name
The SQL sintax to create a database is quite simple:
|
1 |
CREATE DATABASE DB_NAME |
For example, doing this using php following the example so far would be:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
$connection = mysql_connect("localhost","username","password"); if (!$connection) { die('Could not connect: ' . mysql_error()); // mysql_error will let us know the exact error it occured } if (mysql_query("CREATE DATABASE DB_NAME",$connection)) { echo "Database created successfully"; } else { echo "There was an error creating the database: " . mysql_error(); // mysql_error will let us know the exact error it occured } mysql_close($connection); |
How to create a table
The SQL sintax to create a database table is:
|
1 2 3 4 5 6 7 |
CREATE TABLE TABLE_NAME ( field_name1 data_type, field_name2 data_type, field_name3 data_type, .... ) |
The same as the other code so far, we need to use mysql_query() to run the SQL statement:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
$connection = mysql_connect("localhost","username","password"); if (!$connection) { die('Could not connect: ' . mysql_error()); // mysql_error will let us know the exact error it occured } if (mysql_query("CREATE DATABASE DB_NAME",$connection)) { echo "Database created successfully"; } else { echo "There was an error creating the database: " . mysql_error(); // mysql_error will let us know the exact error it occured } // Creating the table mysql_select_db("DB_NAME", $connection); // Needs to be selected before making any queries! $sql = "CREATE TABLE TABLE_NAME ( field_name1 varchar(100), field_name2 varchar(100), field_name3 int )"; // Execute query mysql_query($sql,$connection); mysql_close($connection); |
It is important to not forget adding the mysql_select_db() function for you to select the database you’re going to work with.
How to insert data into table
In any case, the same would be for all mysql queries. The next SQL statement I’m going to explain is the INSERT INTO statement.
Bellow you will find an example of this:
|
1 2 3 4 5 |
INSERT INTO TABLE_NAME VALUES (value1, value2, value3,...) INSERT INTO TABLE_NAME (field1, field2, field3,...) VALUES (row_value1, row_value2, row_value3,...) |
You can use two options for inserting data into a table, first statement in the above example is not stating the fields where each row value should be inserted, however you need to use the exact same number of row_value and positions of the row_value as your table structure field is. It is not recommended to use this if your table has a lot of fields.
The second statement specifies which fields to insert into, this is the most used and really the safest as you can’t go wrong here. I could also show you an example of php code but it is the same as the examples so far, just replace the mysql_query() value with your insert statement.
|
1 2 3 4 |
< ?php mysql_query("INSERT INTO DB_NAME (field1, field2, field3) VALUES ('row_value1', 'row_value2','row_value2')"); ?> |
How to select from a database
Using the SELECT statement you can select any data from your mysql database from any specific table.
The SQL synstax that is used here is:
|
1 2 |
SELECT field(s) FROM TABLE_NAME |
In the above example, it will select all rows from the specified fields from your table TABLE_NAME. You can also select all row values from table using
|
1 |
SELECT * FROM TABLE_NAME |
A php code example would be similar to this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
< ?php $connection = mysql_connect("localhost","username","password"); if (!$connection) { die('Could not connect: ' . mysql_error()); // mysql_error will let us know the exact error it occured } mysql_select_db("DB_NAME", $connection); $result = mysql_query("SELECT * FROM students"); while($row = mysql_fetch_array($result)) { echo $row['LastName'] . " " . $row['FrstName']; echo "<br />"; } mysql_close($connection); ?> |
The result would be:
Andy Brute
Jorge Medy
etc. …
How to update data in a table
The next statement I’m going to show you is the UPDATE statement. This is used when you want to alter a row from field in a table or even all rows from a specific field.
An example of UPDATE SQL code is the following:
|
1 2 3 |
UPDATE TABLE_NAME SET field1=value1, field2=value2,... WHERE field3=specific_value |
So the above code, will update all rows from field1 and field2 with their specific values where field3 matches “specific_value”.
A php code will look similar to the bellow example:
|
1 2 |
mysql_query("UPDATE students SET YearsOld=36 WHERE FirstName='Andy' AND LastName='Brute'"); |
The result will have Andy Brute have 36 in the field YearsOld of the table students.
How to delete from a database table
To delete records from a database table, you need to use the SQL syntax “DELETE FROM” along with the table name.
An example can be seen bellow:
|
1 2 |
DELETE FROM TABLE_NAME WHERE field3 = specific_value |
The above means, that you delete from TABLE_NAME where a field1 has a specific value that mathes your queries. For example, you want to remove the student who is 36 Years old.
Since we already have Andy Brute with 36 years old from our last UPDATE query, the record containing this information will be removed.
That’s it for now, if you have anything to add or recomandations, please comment bellow.
