MySql – Select

SELECTING DATA

To grab a bunch of data from an SQL database you need to write a SELECT query.

The basic query looks like:

 SELECT * FROM `table_name`; 

But to run it via PHP it needs to be placed within a string and executed.

$sql = "SELECT * FROM `criminals`;"; $results = mysql_query($sql) or die('Could not query the database.'); 

However often you will need to specify some sort of criteria or WHERE clause that restricts the data returned:

SELECT * FROM `table_name` WHERE `fieldname` = 'some criteria'; 

WHERE clauses can become very complex and use terms such as AND, OR and LIKE:

SELECT * FROM `table_name` WHERE `fieldname` = 'some criteria' AND `another fieldname` = 'some criteria more'; 

LIKE allows you to look for wildcarded values:

SELECT * FROM `table_name` WHERE `fieldname` LIKE 'some criteria'; 

Further Information and examples.

ORDERING AND LIMITING

You can tell MySql to return information in a particular order using the ORDER or LIMIT instructions. More information on Ordering

JOINS AND MULTIPLE TABLE SELECTS

SELECT can be used across multiple tables but depending on the relationship between the data you may have to use the more complex JOIN method. More information on JOINS

ACCESSING RESULTS FROM A SELECT QUERY

Once you have constructed your SQL query string and stored it in an PHP variable

$sql = "SELECT * FROM `criminals`;" 

you will need to execute it. Executing sql queries is done by calling

$result = mysql_query($sql); 

From the result you can access each of the returned rows through

$row = mysql_fetch_row($result); 

$row = mysql_fetch_array($result); 

Both functions will return an array consisting of values from the row called. Each element in the array can then be reference using its associated location or field name e.g $row[0]; or$row['crime'];. More information can be found here

HOW MANY ROWS HAVE BEEN RETURNED?

If you need to know how many rows have been returned by your SELECT statement then you can call the following which will return the value as an integer.

mysql_num_rows($results) 

IF YOU WANT TO ACCESS ALL THE ROWS RETURNED IN A RESULT

If you want to loop through all the rows then use the PHP while loop

while($row = mysql_fetch_row($result) { //Process Row }