As well as showing the whole database table, PHP can be used to select individual records or records which match certain criteria. To do this you should use a variation of the SELECT query. To display the whole table, use:
SELECT * FROM tablenameIf you just want to select records which have value=1 in the field1-name row, use the following query:
SELECT * FROM tablename WHERE field1-name='1'In the same way, you could select records based on any field in the database. You can also search in more fields by adding more:
field='value'sections into the query.
For example, the following query will select all records which have value=1 in the field1-name row and value=2 in the field2-name row:
SELECT * FROM tablename WHERE field1-name='1' AND field2-name='2'For further reference, visit the official websites of PHP and MySQL.
Suppose we have a table ‘Order123’ having ProductName, Quantity and OrderDate columns as follows −
mysql> Select * from Order123; +-------------+----------+------------+ | ProductName | Quantity | OrderDate | +-------------+----------+------------+ | A | 100 | 2017-05-25 | | B | 105 | 2017-05-25 | | C | 55 | 2017-05-25 | | D | 250 | 2017-05-26 | | E | 500 | 2017-05-26 | | F | 500 | 2017-05-26 | | G | 500 | 2017-05-27 | | H | 1000 | 2017-05-27 | +-------------+----------+------------+ 8 rows in set (0.00 sec)Now if we want to search how many orders received on a particular date say 25th June 2017 then it can be done as follows −
mysql> Select ProductName, Quantity from Order123 where OrderDate = '2017-05-25'; +-------------+----------+ | ProductName | Quantity | +-------------+----------+ | A | 100 | | B | 105 | | C | 55 | +-------------+----------+ 3 rows in set (0.00 sec)Suppose we have stored the OrderDate with time as well then with the help of following query we can get the specified output −
mysql> Select ProductName, Quantity from Order123 where date(OrderDate) = '2017-05-25';
Updated on 28-Jan-2020 09:22:39
- Related Questions & Answers
- How can we search a record from MySQL table having a date as a value in it?
- How to insert date record to the same table with different date formats with MySQL?
- MySQL query to search record with apostrophe?
- How to search for ^ character in a MySQL table?
- Update MySQL table column by matching date using date() function?
- How to search for a date in MySQL timestamp field?
- Delete a specific record from a MySQL table by using AND in WHERE clause
- Insert record in a MySQL table with Java
- How to search a MySQL table for a specific string?
- Update record on a specific date matching the current date in MySQL
- Display record with today and tomorrow’s date from a column with date record in MySQL
- How to delete last record (on condition) from a table in MySQL?
- How to get the second last record from a table in MySQL?
- Order by selected record in MySQL?
- How to search by specific pattern in MySQL?
Almost everyone has been in a circumstance when it is necessary to check whether a record exists in the MySQL database or not.
Let’s look through how to do that with the most suitable and easy command. Below you can find the right command and a common mistake that almost every beginner can make while trying to figure out the problem.
If you want to check whether a record already exists in your database or not, you should run the following code:
$query = "SELECT * FROM products WHERE code = '$code'"; $result = mysql_query($query); if ($result) { if (mysql_num_rows($result) > 0) { echo 'found!'; } else { echo 'not found'; } } else { echo 'Error: '.mysql_error(); }
Now, let’s check out a common mistake that we recommend you to overcome:
//Will NOT work $query = "SELECT * FROM products WHERE code = '$code'"; $result = mysql_query($query); if ($result) { echo 'found'; } else { echo 'not found'; }
MySQL is considered an open-source relational database management system. It is aimed at organizing data into one or more data tables.
Several popular applications such as Twitter, Facebook YouTube, Google apply MySQL for data storage purposes. It was initially made for limited usage. Yet, now it is compatible with many essential computing platforms such as macOS, Linux Ubuntu, and Microsoft Windows.