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. — user10051234
– user10051234, Commented Mar 21, 2019 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
– tadman, Commented Mar 21, 2019 at 21:05

HumbleOne · Accepted Answer · 2019-03-21 20:38:39Z

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, 2019 at 20:38

answered Mar 21, 2019 at 20:23

HumbleOne

17010 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex · Accepted Answer · 2019-03-21 20:23:58Z

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, 2019 at 20:23

Alex

5074 silver badges16 bronze badges

6 Comments

master_yoda Over a year ago

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.

Alex Over a year ago

What do you mean by that?New html table or table in database?

master_yoda Over a year ago

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.

Alex Over a year ago

I need to see database structure in order to do that

master_yoda Over a year ago

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.

|

Honk der Hase · Accepted Answer · 2019-03-21 21:40:36Z

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 :)

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related