PHP, SQL: How to use WHERE field in Query to get multiple values?

Question

I have a database with prices of mangos, apples, and oranges, arranged by date.

I want to filter the prices in a table by ID. I'm able to filter the results using one ID, and display them in a table.

How can I filter by more than one ID value using WHERE in SQL, and display the resulting data in tabular form?

<table style="float:left;">
  <tr>
    <th>Mango </th>
    <th>Apple</th>
    <th>Orange </th>
    <th>Date</th>

  </tr>
  <?php
  $dbhost ="localhost";  // hostname
  $dbuser="root"; // mysql username
  $dbpass="****"; // mysql password
  $db="****"; // database you want to use
  $conn= mysqli_connect( $dbhost, $dbuser, $dbpass, $db ) or die("Could not connect: " .mysqli_error($conn) );
  $sql = "SELECT MANGO, APPLE, ORANGE, DATE FROM dataTable WHERE ID='3' ORDER BY Date DESC LIMIt 1";
  $result = $conn->query ($sql);
  if($result ->num_rows>0){
    while($row = $result ->fetch_assoc()){
      echo "<tr><td>". $row["MANGO"]."</td><td>".$row["APPLE"]."</td><td>".$row["ORANGE"]."</td><td>".$row["DATE]."</td> </tr>";
    }
    echo "</table>";
  }else {echo "0 result";}
  $conn->close();
   ?>
</table>

"How to pass more than one IDs and get the data in different tables?" tables or column? the latter seems more likely, but you ask for the former, so i thought i would check. — , Mar 21 '19 at 20:44
Note: The object-oriented interface to `mysqli` is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface. Before you get too invested in the procedural style it’s worth switching over. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is an artifact from the PHP 4 era when `mysqli` API was introduced and ideally should not be used in new code. You're using both interfaces here inconsistently. Use one and one only. — tadman, Mar 21 '19 at 21:05

HumbleOne · Answer 1 · 2019-03-21T20:38:39.533

4

Consider using the IN operator to filter using a sequence of ID values.

For example, if you want to get results where ID=1 or ID=2 or ID=3:

$sql = "SELECT MANGO, APPLE, ORANGE, DATE FROM dataTable WHERE ID IN (1,2,3) ORDER BY Date DESC";

Please see Select Multiple Ids from a table, for more information.

You can also check out more information on the IN operator here.

edited Mar 21 '19 at 20:38

answered Mar 21 '19 at 20:23

HumbleOne

170
10

score 2 · Answer 2 · answered Mar 21 '19 at 20:23

2

You can do it like this SELECT MANGO, APPLE, ORANGE, DATE FROM dataTable WHERE ID='3' OR ID='4' and you can add as many ORs as you want

answered Mar 21 '19 at 20:23

Alex

477
4
14

but how to get different tables? it will add a new row to the same table? I want to get different tables for different ID. – master_yoda Mar 21 '19 at 20:41
What do you mean by that?New html table or table in database? – Alex Mar 21 '19 at 20:45
not in Database. i wanna display the result. for example ID=1 should give a table with 3 columns (MANGO, APPLE, ORANGE) and 1 row (Prices). similarly, ID=2 should give another table. I would like to arrange the tables side by side. – master_yoda Mar 21 '19 at 20:48
I need to see database structure in order to do that – Alex Mar 21 '19 at 20:55
ID row=(1 3 7 1 3 7 1 3 7 1 3 7 .........) DATE row =( 21.03.2019 20.03.2019 19.03.2019........) APPLE row = (5 3 6 5 3 2 .........) MANGO row= (1 2 1 3 2 ........) ORANGE row = (1 2 3 5 6 2......) this is the structure. I would like to get the prices for each ID but the latest date. a table for ID=1 with latest date and corresponding prices for each item. ID=3 with the latest date and corresponding prices for each item. ID=7 with the latest date and corresponding prices for each item. – master_yoda Mar 21 '19 at 21:00
Don't chain `OR` unless the comparison is different. Instead use `ID IN (3,4,5,6)` etc. – tadman Mar 21 '19 at 21:06

score 1 · Answer 3 · answered Mar 21 '19 at 21:40

Your table design is broken. The fruits are not supposed to be columns.

Databases like their data nicely separated, with relations expressed through internal keys (primary key, foreign key). If you do that, they will be superfast and reliable in finding it, even if you have hundred thousands of records stored.

table "fruits"
id | name
 1, 'Mango'
 2, 'Orange'
 3, 'Apple'

table "fruits_date_prices"
id | fruit_id | price | date   
 1, 1, 1.30, '2019-03-20'
 2, 1, 1.35, '2019-03-21'
 3, 1, 1.37, '2019-03-22'
 4, 2, 0.87, '2019-03-20'
 5, 2, 0.86, '2019-03-21'
 6, 2, 0.84, '2019-03-22'
 7, 3, 0.34, '2019-03-20'
 8, 3, 0.36, '2019-03-21'
 9, 3, 0.35, '2019-03-22'

Query for prices on a day, using a JOIN (eg. "two tables in one call"):

SELECT
    f.name AS fruit,
    fdp.price 
FROM 
    fruits AS f
LEFT JOIN
    fruits_date_prices AS fdp ON f.id = fdp.fruit_id
WHERE
    fdp.date = '2019-03-21'

Hope that helps a bit :)

PHP, SQL: How to use WHERE field in Query to get multiple values?

3 Answers3