echo selected SQL row data using PHP

Question

If I collect multiple row data from SQL, I will have to use foreach loop or something else to display that data properly. How to use these PHP strings without using foreach loop ?

Following is my SQL query

$results = $mysqli->query('SELECT * FROM specs where id in ('.$id1.','.$id2.','.$id3.','.$id4.') ');

It will result data from 4 different rows.

The above code is just an example, below is the exact code I am using in my page.

try{
    $pdo = new PDO("mysql:host=localhost;dbname=databse", "user", "password");
    // Set the PDO error mode to exception
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
    die("ERROR: Could not connect. " . $e->getMessage());
}
 
// Attempt select query execution
try{
    $sql = "SELECT * FROM specs WHERE id IN($id1,$id2,$id3,$id4)";
    $result = $pdo->query($sql);
    $results = $result->fetchAll();

} catch(PDOException $e){
    die("ERROR: Could not able to execute $sql. " . $e->getMessage());
}
 
// Close connection
unset($pdo);

Currently, the data is displayed using the following

<tr>
<td>Brand</td>
<?php foreach ($results as $result){ ?>
    <td> <strong><?php echo $result['brand']; ?></strong> </td>
<?php } ?>
</tr>

There will be total 4 results, I wan to use that 4 results separately. If the results are Samsung, Sony, Apple, LG - how can I echo the "Apple" only using PHP string ?

You cannot, if you have result set you need to iterate it, that's programming. — biesior, Aug 03 '20 at 18:10
**Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Aug 03 '20 at 18:12
@biesior, You mean there is no way to select the first or second data alone ? like echo $brand[0]; — Shijil, Aug 03 '20 at 18:12
return it as array so you'll be able to get it by index, That are PHP basics, sorry I won't paste samples for that. — biesior, Aug 03 '20 at 18:13
@Dharman , thank you for the warning, this was only my test code, not the original code. — Shijil, Aug 03 '20 at 18:13
@Dharman, in the question, I have added the original SQL code. — Shijil, Aug 04 '20 at 04:09
It's still broken. You are mixing PDO and MySQLi. You need to parameterize the query — Dharman, Aug 04 '20 at 09:21

AbraCadaver · Accepted Answer · 2020-08-03T18:22:18.410

Fetch all the rows. If you have the MySQL Native Driver:

$rows = $results->fetch_all(MYSQLI_ASSOC);

Otherwise, fetch with a loop:

while($rows[] = $results->fetch_assoc());

Then you can access them by row:

echo $rows[0]['brand'];  // first row

echo $rows[1]['brand'];  // second row

But that's not very useful. If you have something unique that you want to use (I use brand as an example though it's probably not), then just index on that:

$rows = array_column($results->fetch_all(MYSQLI_ASSOC), null, 'brand');
// or
while($row = $results->fetch_assoc()) { $rows[$row['brand']] = $row; }
// then
echo $rows['apple']['model'];

echo selected SQL row data using PHP

1 Answers1