Subscribe via email

Grab our RSS Feed

The WHERE clause

The WHERE clause, if given, indicates the condition or conditions that rows must satisfy to be selected. where_condition is an expression that evaluates to true for each row to be selected. The statement selects all rows if there is no WHERE clause.

Get started with The WHERE clause.

We will use the same test_tbl mysql table.

CREATE TABLE test_tbl (
id int NOT NULL auto_increment,
date varchar(20) NOT NULL,

name1 varchar (50) NOT NULL,
email varchar(55) NOT NULL,
PRIMARY KEY (id)
);
With the same records:

1/10-10-2008/leo/webmaster@iteachweb.net
2/10-10-2008/sarah/sarah@sarah.com
3/10-11-2008/john/john@johndomain.com
4/10-12-2008/jack/jack@jackdomaine.com

We will select only the record for sarah
<?php
//get connected to the data base
$db = mysql_connect('host','login','password') or die

("connexion error");
//select the data base
mysql_select_db('base name',$db) or die ("base connexion error");

//we search for the record with the name sarah.
$req = mysql_query("SELECT id,email,date from test_tbl
where name1 ='sarah' ");
//we sort the result as an array
$result = mysql_fetch_array($req);

// displaying the result
print 'The id -> '.$result['id'].' email ->

'.result['email'].' -> '.$result['date'].'<br>';

?>

// Output
The id 2 email -> sarah@sarah.com date -> 10-10-2008
Explanation: You can use multiple instruction with
the where clause, for example

<?php
$req = mysql_query("SELECT id,email from test_tbl
where prenom ='paul' AND date = '4-04-2002' ");

?>
You will get the same result

Note: If you want to select all the columns of a mysql table use the (*) instead of writing all the elements:

$req = mysql_query(“SELECT * from test_tbl where name1 =’sarah’ “);

Use The WHERE clause and LIKE as comparator elements:

<?php
//get connected to the data base
$db = mysql_connect('host','login','password') or die
("connexion error");
//select the data base
mysql_select_db('base name',$db) or die ("base connexion error");
//we look for emails that are starting with (j)
$req = mysql_query("SELECT email from test_tbl where email LIKE 'j%' ");
//sort the result in an array
while ( $result = mysql_fetch_array($req) )
{
// display the result
print 'The result of the requet is: '.$result[email].'<br />';
}
?>

The output will be all the emails that are starting with j

The result of the request is: john@johndomain.com
The result of the request is: jack@jackdomaine.com

More with LIKE: You can use as we mention in that example
p% for what is starting with p.
%p% any word that has a p anywhere.
%p any word that ends with p.
Keep that in mind, it will serve you later.

The mysql’s comparison operators

Operator Description Example = equal where name1 = ’sarah’ != not equal / different where name1 != ’sarah’ <= less or equal where id <= ‘2′ < strictly less where id < ‘2′ >= more or equal where id >= ‘2′ > strictly more where id > ‘2′

The mysql’s logical operators

Operator Description Example OR / || or where name1 = ’sarah’ || id =’2′ AND / && and where name1 = ’sarah’ AND id = ‘3′

Filed under PHP and MySQL. You can follow any responses to this entry through the RSS 2.0. You can leave a response by filling following comment form or trackback to this entry from your site

Leave a Reply

Name (required)

Mail (will not be published) (required)

(Maximum characters: 250)
You have characters left.