PHP Programing language

PHP MySQL WHERE
Previous Home Next

When we did a SELECT query to get all the data from our "tablefirst" table. If we wanted to select only certain entries of our table, then we would use the keyword WHERE.

WHERE is a keyword, so lets you specifing requirements that entries must meet in order to be returned in the MySQL result. These entries that do not pass the test will be left out.

To being Selective With Your MySQL Selection. Finally ,there are three entries in our "table first" table Aniket, Abhilash, and Ashwini. To select Abhilash only we could either specify Abhilash age (21) or we could use her name(). In the future there may be other people who are 21, so we will use her name as our requirement.

The WHERE keyword is used in conjuction with a mathematical statement.In this program we will want to select all rows that have the string "Amit Abhilash" in the "names" column (mathematically: {name column} = "Amit Abhilash"). Here's how to do it.

<?php
// Make a MySQL Connection
mysql_connect("localhost", "admin", "1admin") or die(mysql_error());
mysql_select_db("test") or die(mysql_error());

// Get a specific result from the "example" table
$result = mysql_query("SELECT * FROM example
WHERE name='Amit Abhilash'") or die(mysql_error());  

// get the first (and hopefully only) entry from the result
$row = mysql_fetch_array( $result );

// Print out the contents of each row into a table 
echo $row['name']." - ".$row['age'];
?>

Output:

Amit Abhilash - 21

 

MySQL Wildcard Using a sigh '%

If you wanted to select every person in the table who was in their 20's, how could you go about doing it? With the tools you have now, you could make 10 different queries, one for each age 20, 21, 22...but that seems like more work than we need to do.

In MySQL using a "wildcard" character '%' that can be using to search for partial matches in your database. The '%' tells MySQL to ignore the text that would normally appear in place of the wildcard. For example '2%' would match the following:

20, 25, 2000000, 2avkldj3jklsaf, and 2!

On the other hand, '2%' would not match the following: 122, a20, and 32.

In MySQL Query WHERE With Wildcard

To solve our problem from before, selecting everyone who is their 20's from or MySQL table, we can utilize wildcards to pick out all strings starting with a 2.

PHP & MySQL Code:
<?php
// Connect to MySQL
// Insert a row of information into the table "Amit Abhilash"
$result = mysql_query("SELECT * FROM example WHERE age LIKE '2%' ") 
or die(mysql_error());  

// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {

// Print out the contents of each row
echo $row['name']." - ".$row['age']. "<br />";
} 
?>

Output:

Amit Abhilash - 21

You can use this wildcard at the beginning, middle, and end of the string. Experiment with it so you can see for yourself how powerful this little trick can be.

Note: The wildcard was used for example purposes only. If you really wanted to explicitly select people who are in their 20's you would use greater than 19 and less than 30 to define the 20's range. Using a wildcard in this example would select unwanted cases, like a 2 year old and your 200 year old great-great-great-grandparents.

Previous Home Next

Tolal:0 Click:

Show All Comments

Did not find what you were looking for leave your name and message. We will revert within 24 hours
Name:
eMail:
Comment / Feedback: