After the connection to mysql engine is established, and database is selected, you can start performing operations to delete, add & retrieve data, and perform other tasks. The basic tasks can be done with
mysql_query()
function.
This function takes a SQL query as argument, which will be using the same syntax as native SQL queries, when MySQL is used straight from commandline without php.
Retrieving data:
The most used way to get data from database is to use SELECT statement.
It's syntax is
SELECT columnname FROM 'tablename'.
This returns kind of a pointer to a structure holding the information we asked for. Actually I am not sure if it's a real pointer, or just somekind of odd php thingy
Information can be retrieved for example by using the following functions:
mysql_fetch_array()
mysql_fetch_row()
mysql_fetch_array and mysql_fetch_row both take one row from selected data, and create an array out of it in following way.
mysql_fetch_row inserts the data in that line so that data in first column will be in array location 0, next column in location 1 and so on. When using mysql_fetch_array function, user can ask data to be stored in named array, so that instead of array[0], array[1]... there will be array['name_of_the_first_column'], array['name_of_the_second_colum']...
A typical example of that would be for example
Code: Select all
$sql="SELECT name_of_the_first_col, name_of_the_second_col FROM 'exampledatabase'";
$result=mysql_query($sql) or die('querying first & second col in exampletable failed because: ' . mysql_error());
while($row=mysql_fetch_row($result))
{
echo 'first column in this row is ' . $row[0] .'<br>';
echo 'second column in this row is ' . $row[0] . '<br>';
}
Or if using fetch array, it could be like:
Code: Select all
$sql=...
$result=mysql_query...
while($row=mysql_fetch_array($result, MYSQL_ASSOC))
{
echo 'first column in this row is ' . $row['name_of_the_first_column'] .'<br>';
echo 'second column in this row is ' . $row['name_of_the_second_column'] . '<br>';
}
You probably noticed that fetch array took one argument more than fetch row. That's true. with this argument you can specify whether you wish to have data stored in 'named' array, or in 'numeric' array. And as the example shows, MYSQL_ASSOC makes data to be stored in named array. Other possible arguments are MYSQL_NUM and MYSQL_BOTH. And as the names suggest, ASSOC_NUM stores data in numeric array, in fact, it's similair to fetch row then, and MYSQL_BOTH stores data in both arraytypes.
Default value here is MYSQL_BOTH. (IE, if that argument is not specified, it uses MYSQL_BOTH).
Sometimes you wish to get all fields (columns) from table. In that case you can write your SQL query as SELECT * FROM tablename. But this is HEAVY query if table is large, and it should be avoided. (Never retrieve more information than you need).
Now, usually it's not wanted to get all the data from the table. Let's take an example.
We have table of members of OG AA association, and their favourite drinks in a table as follows:
Code: Select all
col1 col2 col3
id name drink
line1 1 Bald Guinness
line2 2 Maz homebrew
line3 3 Pitk Tea
Of course secent people do not remember anything tea related, so they wan't to get the pitk's favourite drink out of the table. Sure it would be possible to retrieve names and drinks, and check the names in for loop & find the drink corresbonding to the pitk's name with php... But php is not optimized for this kind of tasks, whereas the database engines are. So let's look how to let the MySQL to get the data...
For that we use new word in query. It's WHERE. Query would be as follows:
Code: Select all
$sql="SELECT drink FROM 'og_aa' WHERE 'name'='pitk'";
$result=mysql_query($sql);
while($row=mysql_fetch_array($result))
{
echo 'Ptkin\'s favourite drink is tar, although he claims it to be' . $row['drink'];
}
Other usefull keywords for getting data are
LIMIT number which tells the maximum amount of row's to be retrieved.
ORDER BY 'columname' DESC which sorts the results according to column 'columname' in ascending (default/use ASC) or descending order (DESC).
You can also use other comparation operators with WHERE clause. Those are
< (smaller than)
> (greater than)
<= (smaller or equal)
>= (greater or equal)
!= ('different from')
More will follow when I find time... :/
)