How to Select a Specific Row(s) from a MySQL Table Using PHP



PHP


In this tutorial, we will show how you can select a specific row (or rows) of a MySQL table using PHP based on some criterion.

Say if you're looking for the row which contains the word "Oreos" in it or you're looking for the rows which contains prices that are under $5 or between a certain price range, you can do this also using the WHERE clause of PHP.



Below is a table of list of snack foods:

Snacks

Banana
$0.79
Mango
$1.99
Cantaloupe
$3.99
Honeydew
$4.99

Below is the complete code to create the table above:



In this above code, we're going to alter the 3rd block of code, which has the comments "ATTENTION." This is the block of code which will allow us to specifically decide which rows are selected.

How to Choose a Specific Row of a MySQL Table Using PHP Based on a Criterion

Now we're going to alter the 3rd block of code above to only select specific rows.

Let's say that we only want fruits that are under $2. In order to only show snacks that are under $5, we use the following lines of code:


The statement ORDER BY Price just categorizes the items by price from lowest to highest.

This will now show the table below.:

Banana
$0.79
Mango
$1.99


How to Select Rows In Between a Certain Range

Let's now adjust the table again so that we show only items in between a certain price range.

In our MySQL database, we have a list of every piece of data on Snack chips. We now want to show only the snacks that are between the price range of $2 and $4. Just like all those price search websites like Travelocity, where a user is looking for an airplane ticket between $300 and $500. This is the extreme value and use of knowing how to conduct these searches from within databases.

The line of code to show only queries matching in between the $2 and $4 price range is:

This will produce the table below:

Cantaloupe
$3.99


How to Select a Row that Has By a Certain Word

Say if a user loves honeydew and wants to search just for this fruit within the database. What we would do is create a search textbox in which the user can type the word 'Honeydew' into it. We then extract that search term using PHP and look to see if any terms in our database has that term the user is looking for, which in this case is 'Honeydew.'

The line of code to look for a certain keyword is:

To search for a keyword, the keyword LIKE is used, followed by single quotes and percentage signs, as such, '% %', with the word we're searching for in between these.

The line of code to search our MySQL table by the keyword, 'Honeydew', is:

This produces the following table below:

Honeydew
$4.99


This only shows the field for the honeydew because that's the only field which contains the word 'honeydew' in it.

There are many ways in PHP where you can effectively search and narrow down the results, all using the WHERE clause. It is a very useful clause when a user is only looking for specific data within a MySQL database.


Related Resources

How to Limit the Number of Rows Returned from a MySQL Table

How to Return Random Rows from a MySQL Table Using PHP

How to Delete a Row of a MySQL Table Using PHP

How to Delete All Rows of a MySQL Table Using PHP




HTML Comment Box is loading comments...