Deleting Data
Deleting data is quite easy and here is the code to do it:
<?php
// Open Connection To mySQL.
// Replace 'username' and 'password' with appropiate login values for your server.
$mysqlLink = mysql_connect( 'localhost' , 'username' , 'password' );
// Select mySQL Database.
mysql_select_db( 'examples' , $mysqlLink );
// Create Query To Create Table.
$sql = 'DELETE FROM `table0` WHERE `ID` = 1;';
// Issue Query.
mysql_query( $sql );
// Close mySQL Connection.
mysql_close( $mysqlLink );
?>
|
The DELETE command in SQL deletes an entire
row from the table, not individual fields like most novice people would
think. This command is used to delete an entire row when it is no longer
needed, such as when a user deletes his account. His user information is
no longer needed, so it is removed from the database.
The code above is pretty simple to understand compared to other queries.
Can you figure out how it works? It is pretty simple by just looking at
it.
Selecting Data
The best for last! Selecting data from a database is a multi step
process in PHP. We usually select data from a database to use somewhere
in our script; to display it, use it in some sort of calculation, or
compare it to other data.
Selecting data can be done in one of two ways. Firstly, we can prompt to
select specific rows based on some sort of condition *OR* we can prompt to
select all rows in the table. The latter is easier, so let's see some
code on how to do it:
<?php
// Open Connection To mySQL.
// Replace 'username' and 'password' with appropiate login values for your server.
$mysqlLink = mysql_connect( 'localhost' , 'username' , 'password' );
// Select mySQL Database.
mysql_select_db( 'examples' , $mysqlLink );
// Create Query To Create Table.
$sql = 'SELECT * FROM 'table0`';
// Issue Query.
$result = mysql_query( $sql );
// Loop through results and print them.
while( $row = mysql_fetch_array( $result ) ) {
echo $row[ 'ID' ] . $row[ 'Name' ] . $row[ 'Age' ];
}
// Close mySQL Connection.
mysql_close( $mysqlLink );
?>
|
That was a lot of some new code but the basic
concept is the same. Let's break down the code into segments and explain
what exactly is going on to avoid confusion.
| $sql = 'SELECT * FROM `table0`'; |
This is the SQL command to select data from a table
as a whole. It contains the fields we want to select as well as the
table we want to select them from. We said before we want to select ALL
fields from the table. The * is the wildcard symbol that we need.
| $sql = 'SELECT * FROM `table0`'; |
This is the table we want to select data from.
Because we did not provide a specific row or set of rows to select,
mySQL by default will select all rows in the database.
| $result = mysql_query( $sql ); |
This is new. We have never before saved the result
of a mysql_query function call to a variable so why now? PHP
returns a resource of the selected data in the table. To access this
data we have to save it in a variable as if it were a normal value.
Now let's take a look on how through PHP we can actually use the data
selected by mySQL. For this example we will simply just print the data.
<?php
while( $row = mysql_fetch_array( $result ) ) {
echo $row[ 'ID' ] . $row[ 'Name' ] . $row[ 'Age' ];
}
?>
|
Couple of things to notice here. First there is a
new function used called mysql_fetch_array. How does this
function work? Well, mysql_fetch_array takes a query result
resource as an argument. In our case it is the selected data from the
database. It returns an associative array of field values in each row
selected.
mysql_fetch_array however returns ONE ROW AT A TIME, that is why
we started a while loop. So that we can loop through each row
selected.
The associative array has the field names selected as keys and their
values as the array value itself. That is why when we printed each row
we did this:
| echo $row[ 'ID' ] . $row[ 'Name' ] . $row[ 'Age' ]; |
Notice that each key in the array $row is the
name of the fields in our table!
Other Examples of Data Selection
We can select data from a table using more complicated queries, but the
basic idea to use them in code is the same as explained before. Here are
several more examples of selecting data:
| $sql = 'SELECT * FROM `table0` LIMIT 0, 30 '; |
Select all fields and all records from a table, but
only display 30 records starting with the first record.
| $sql = 'SELECT * FROM `table0` LIMIT 10, 30 '; |
Select all fields and all records from a table, but
skip the first 10 records, and display the first 30 records after them.
| $sql = 'SELECT `ID` , `Name` FROM `table0`'; |
Select fields ID and Name and all
records from a table.
| $sql = 'SELECT `ID` , `Name` FROM `table0` Where `ID` > 10'; |
Select fields ID and Name from a
table, where the field ID has a value bigger than 10.
Conclusion
In this tutorial we covered the basics of using mySQL from PHP as well
as covered the most basic and most frequently used SQL queries to
insert, edit, delete, and select data.
- Tutorial written by Limitless
| 
