PHP - The Mysql stuff
From Global Programming Syntax
Contents |
Introduction
Mysql is a widely popular database storage solution used on many websites as it is a easy and normally reliable way to store the data on a server. When comparing mysql to a text base storage system, you will find that mysql will always win on large demand as functions such as file_put_contents() will often fail to update data as a result of mass processing. And the reason for this? - Mysql and other database solutions are done on a binary level of code which can be processed more easily in a way that the computer can understand more of what is happening. That is the basic version of a long and more complex description. So if you are making a Content Management System (cms) - need to store lots of data or are frequently swapping data while storing it then Mysql may be one of the best solutions for you.
Setting up my first mysql script
First you will need to create your tables. Usually instead of creating a script for a one time use, it is easier just to use PhpMyAdmin to do all the hard work for you. PhpMyAdmin is a Mysql interface written in php and is available on most servers that have Mysql. It is very simple to use. Just follow the links in the PhpMyAdmin interface to make the one time database modifications you need. The documentation for PhpMyAdmin can be found at http://www.phpmyadmin.net/documentation/
So using the PhpMyAdmin Interface, trying creating a table called 'mytable' (without the quotes) and 3 columns (named 'column1', 'column2', 'column3' - all text and no quotes). The table structure for this tutorial is as follows:
mytable
column1
column2
column3
With the help of PhpMyAdmin, you should now have your tables setup ready for php to read and write. So this is when php comes into play. Below is a simple script showing how to display the table in the browser:
$username='admin'; //Mysql access username
$password=''; //Mysql access password
$database='mydatabase'; //Database to connect to - not the table name.
//configure above variables
mysql_connect('localhost', $username, $password) or die('Invalid login details.<hr>'.mysql_error());
mysql_select_db($database) or die('Cannot connect to selected database.<hr>'.mysql_error());
//now the database is connected, time to display it.
$result = mysql_query('SELECT * FROM `mytable`') or die(mysql_error());
if (mysql_num_rows($result)>0) {
echo '<table border=1 cellpadding=5 cellspacing=0>';
while ($row=mysql_fetch_array($result)) {
echo '<tr><td>'.$row['column1'].'</td><td>'.$row['column2'].'</td><td>'.$row['column3'].'</td></tr>';
}
echo '</table>';
} else {
echo 'No rows to display.';
}
If you run that script now, because there is nothing in the table, nothing will display nothing if configured correctly. If however errors are displayed then you -a, haven't configured the variables correctly or -b, do not have the correct table structure which was mentioned earlier. Either way the error message will say what the problem is as this test script gives extra debugging info.
Also just as a note, if you use PhpMyAdmin to insert a few rows into the mytable table they will appear in the table that is displayed via the above script.
Inserting some values
For inserting values into a database, there are two ways to do so (whichever suits you). So first is first, lets check the easier method:
mysql_query('INSERT INTO `mytable` SET `column1`="'.mysql_real_escape_string($var)
.'", `column2`="'.mysql_real_escape_string($var).'", `column3`="'.mysql_real_escape_string($var).'"')
or die(mysql_error());
As you can see in the example above, it is a very simple process however all values must be escaped! This is both for security and to remove potential bugs. Also, if you are using $_POST as the input and magic quotes are enabled, then you must use stripslashes on each $_POST input. So below is an example of placing $_POST data into mysql while magic quotes are enabled.
mysql_query('INSERT INTO `mytable` SET `column1`="'.mysql_real_escape_string(stripslashes($_POST['field1'])).'", `column2`="'.
mysql_real_escape_string(stripslashes($_POST['field2'])).'", `column3`="'.
mysql_real_escape_string(stripslashes($_POST['field3'])).'"') or die(mysql_error());
And as mentioned earlier, there are two methods of inserting data into a mysql database. The equivelant of the above script but using the second method is as follows:
mysql_query('INSERT INTO `mytable` (column1, column2, column3) VALUES("'.mysql_real_escape_string(stripslashes($_POST['field1'])).
'", "'.mysql_real_escape_string(stripslashes($_POST['field2'])).'", "'.mysql_real_escape_string(stripslashes($_POST['field3'])).'") ')
or die(mysql_error());
Display the values with style
If you wish to add alternating colors on each line and to have the row number on the first line, below is an example of how to do that. Also with the below example, it has been designed to be easy to configure with only 5 variables to alter.
<?
$username='admin'; //Mysql access username
$password=''; //Mysql access password
$database='mydatabase'; //Database to connect to - not the table name.
$columnnames=array('column1','column2','column3'); //table column names
$tablename='mytable'; //The name of the table - not the database.
//configure above variables
mysql_connect('localhost', $username, $password) or die('Invalid login details.<hr>'.mysql_error());
mysql_select_db($database) or die('Cannot connect to selected database.<hr>'.mysql_error());
//now the database is connected, time to display it.
$result = mysql_query('SELECT * FROM `'.$tablename.'`') or die(mysql_error());
if (mysql_num_rows($result)>0) {
echo '<table border=1 cellpadding=5 cellspacing=0>';
for ($row=1;$data=mysql_fetch_assoc($result);$row++) {
if (($row/2)==round($row/2)) {
echo '<tr><td bgcolor="#BBAAAA">'.$row.'</td>';
foreach ($columnnames AS $val) {
echo '<td bgcolor="#FF6600">'.$data[$val].'</td>';
}
} else {
echo '<tr><td bgcolor="#DDCCCC">'.$row.'</td>';
foreach ($columnnames AS $val) {
echo '<td bgcolor="#FFFF00">'.$data[$val].'</td>';
}
}
echo '</tr>';
unset($val);
}
echo '</table>';
} else {
echo 'No rows to display.';
}
?>
However, there are a few basic things that most newbies should be able to learn from this code despite the complexity. One is the if statement used for alternating colors show above like the following:
if (($rownumber/2)==round($rownumber/2)) {
//do color 1
} else {
//do color 2
}
Very simple and effective. Also for the row numbers, the mysql_fetch_array was placed into a for loop where during each round of the loop, the $row variable would add +1. You also may want to note that for loops when used correctly can be faster than while loops.
Then the last piece to explain in that code is the use of the foreach loop. A foreach loop works like the following:
foreach (array('1','2','3') AS $value) {
echo $value.'<br>';
}
The way a foreach loop works is it checks an array for every value contained by that array then sends it to a variable where each value can be used in a loop. So in the example above, during the first round of the loop, $value will equal 1 then on the second round of the loop $value will equal 2 and just keeps going through the array place in the left of the foreach loop.
Updating a table
Making an 'Update' query
Using the update feature of Mysql is just like the 'Insert' query but with a where clause/statement. The update feature is to alter selected existing rows to the desired value. An example of the update feature is as follows:
mysql_query('UPDATE `mytable` SET `column1`="value2", `column2`="valueb" WHERE `column1`="value" AND `column2`="value"');
The only problem with the above example is that if the WHERE clause matches multiple rows then all of those matching rows will be updated to the new values. That is where the limit parameter comes in. So below is a modified version of the above query but will only affect one row:
mysql_query('UPDATE `mytable` SET `column1`="value2", `column2`="valueb" WHERE `column1`="value" AND `column2`="value" LIMIT 1');
Update the id field
Sometimes to save cpu, an id field may be used to identify each row with a number. However, every so often after some rows have been deleted, you start to get some skipped numbers where the row numbers might go 1, 2, 3, 6, 8, 9 etc. To realign these numbers so that they go 1, 2, 3, 4, 5, 6 etc, you need to submit a mysql query and perhaps a loop. A simple example of a script that realigns the numbers into sequence is as follows:
<?
$username='admin'; //Mysql access username
$password=''; //Mysql access password
$database='mydatabase'; //Database to connect to - not the table name.
$tablename='mytable'; //the name of the table - not the database.
$idfieldname='id'; //the column name for the id field.
//configure above variables
mysql_connect('localhost', $username, $password) or die('Invalid login details.<hr>'.mysql_error());
mysql_select_db($database) or die('Cannot connect to selected database.<hr>'.mysql_error());
mysql_query('UPDATE `'.$tablename.'` SET `'.$idfieldname.'`=""') or die(mysql_error());
$result=mysql_query('SELECT * FROM `'.$tablename.'`');
if (mysql_num_rows($result)>0) {
for ($row=1; $data=mysql_fetch_assoc($result); $row++) {
$where='';
foreach ($data AS $key => $val) {
$where.='`'.$key.'`="'.mysql_real_escape_string($val).'" AND';
}
$where=substr($where,0,-3);
$where.='LIMIT 1';
mysql_query('UPDATE `'.$tablename.'` SET `'.$idfieldname.'`="'.$row.'" WHERE '.$where) or die(mysql_error());
unset($where);
unset($key);
unset($val);
}
echo 'ID field updated';
} else {
echo 'Nothing to update';
}
?>
And so as soon as that script is called, all the number in the id field (or specified field) will change to numbers counting up from 1.
Display data from a database
When retrieving data from a database, there are two main functions to do this in addition to the mysql_query function. They are mysql_fetch_array() and mysql_fetch_assoc(). They both do basically the same thing however mysql_fetch_array() offers additional features with a flag/option field. To explain more clearly, they both fetch data. They both can display the column name in the array key. They both have the column value as each array value. The difference, mysql_fetch_array by default not only fetches the value mysql_fetch_assoc() does but it doubles the number of arrays and replaces the second set of array keys with column numbers. Below is an example:
Variable dump for output of mysql_fetch_array(); [0]=value [column]=value [1]=differentvalue [anothercolumn]=differentvalue [2]=valuesss [columna]=valuesss [3]=another [testcolumn]=another Variable dump for output of mysql_fetch_assoc() with same table: [column]=value [anothercolumn]=differentvalue [columna]=valuesss [testcolumn]=another
As you can see, there are 2 different systems applied in mysql_fetch_array with one system being that each column name is a number. Hope that is all explained clearly now.
Using mysql_fetch_array()
Generally used when beginning to learn mysql since it is an easy to remember function which does the job. If however you are dealing with a database and all the columns add up to 100mb per row then there could be a lot of ram/memory saved if the array isn't produced twice. Same applies for if you receive large chunks of traffic with only a few kilobytes per row. For this reason it is best that you use the mysql_fetch_assoc() function unless the appropriate flag is specified in this function. Basically the default of this function is so terrible that your server will overload twice as fast than if you didn't use the default. Below is a visual of how the function is set out. And although the second parameter is optional, the default should never be used unless absolutely necessary.
mysql_fetch_array ( resource $result [, int $result_type= MYSQL_BOTH ] )
The two alternative options for the second parameter are as follows: MYSQL_ASSOC MYSQL_NUM
Also one of the two alternative options (MYSQL_ASSOC) is what the mysql_fetch_assoc() function has set for default. So it is pretty useless for an expert to use that option in this function when there is a better function that does the same job. And in general sense, MYSQL_NUM is the only flag you should really need in this function. As for MYSQL_BOTH, as previously mentioned, uses double the ram/memory making it useless too. So whenever using the mysql_fetch_array() function, it should look something like the following:
$row = mysql_fetch_array ( $result , MYSQL_NUM)
Now the above example is about all that you should have to use the mysql_fetch_array() for. A more thorough example is as follows:
$result=mysql_query('SELECT * FROM `table`') or die(mysql_error());
echo '<table border=1><tr><td>Book</td><td>Author</td><td>Price</td></tr>';
while ($row = mysql_fetch_array ( $result , MYSQL_NUM)) {
echo '<tr><td>'.$row[0].'</td><td>'.$row[1].'</td><td>'.$row[2].'</td></tr>';
}
echo '</table>';
Using mysql_fetch_assoc()
This function is the recommended one more so than mysql_fetch_array() as it does only what you need. The equivelent example to the one described in mysql_fetch_array() is as follows:
$result=mysql_query('SELECT * FROM `table`') or die(mysql_error());
echo '<table border=1><tr><td>Book</td><td>Author</td><td>Price</td></tr>';
while ($row = mysql_fetch_assoc($result)) {
echo '<tr><td>'.$row['book'].'</td><td>'.$row['author'].'</td><td>'.$row['price'].'</td></tr>';
}
echo '</table>';
As you can see, the $row array is much different. Although by default you can call both methods (numbers and column names) with mysql_fetch_array() that is what doubles the server load and the amount of ram/memory being used. So best practice is to use the mysql_fetch_assoc() function for this purpose.
Also note to remember to place in the mysql connections.
Now it's time to learn how to actually make the queries for this function. Below is a simple query that will select all contents from a table called table:
$result=mysql_query('SELECT * FROM `table`') or die(mysql_error());
Then lets say you wanted to select only the rows containing the text abcd in the column testcolumn. The following is the mysql query to do that:
$result=mysql_query('SELECT * FROM `table` WHERE `testcolumn`="abcd"') or die(mysql_error());
Piecing together the code
Official Documentation link - As mentioned above, there are two functions to use with the mysql_query which each have a subtitle above. Now the aim is to make sense of all of this. First lets start with the select element in the mysql_query and below is a typical example:
$result = mysql_query('SELECT * FROM `table`') or die(mysql_error());
//or translate to english and not php
//variable = mysql_query(select all columns from table) or report error
The above example is very simple and even has a commented translation to English in case you don't understand. So basically the above example will retrieve all results from the specified table ready for use within the variable. Now for the next bit...
You will not always want to select all the results. Say there are two columns (name, age) and you only wanted to select the name column but also wanted to only select the rows where the age is 35. Well below is an example on how to do that and to explain more more clearly on what the below script does, only the name column will be selected and only people aged 35 in the list will be sent to the results.
$result = mysql_query('SELECT `name` FROM `table` WHERE `age`="35"') or die(mysql_error());
Now you may say, while it is filtered, that is good and all but there are hundreds of matching rows with that query. To limit the number of rows it can simply be done with the limit clause/statement. So the following will limit it to the top ten rows.
$result = mysql_query('SELECT `name` FROM `table` WHERE `age`="35" LIMIT 10') or die(mysql_error());
