How to echo more than one row with same id

Question

I have a database that allows a user id to appear as many times as the user wants e.g. id = 1902 the items belonging to this id is fish, bread, milk. Same user has with same id on another row id = 1902 the items belonging to this id row is pepper, mat, mouse.

I want to try and output the user id and all its items but don't know the code to use on this.

<?php
$xyttt = $_SESSION['email'];

$sql = "SELECT id FROM users where username = '$xyttt'";

$result = mysqli_query($db, $sql);

// fetch the resulting rows as an array
$investment = mysqli_fetch_all($result, MYSQLI_ASSOC);
foreach($investment as $invest){ 
    // echo ($invest['id']); 
    $uidbb = ($invest['id']);
} 

// free the $result from memory (good practise)
mysqli_free_result($result);

$uidbb = ($invest['id']);

$sql = "SELECT * FROM investment where userid = '$uidb'";

$result = mysqli_query($db, $sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. " - Name: " . $row["amount1"]. " " . $row["status"]. "<br>";
    }
} else {
    echo "0 results";
}

$conn->close();
// free the $result from memory (good practise)
mysqli_free_result($result);

?>

I used this code but it was fetching all the rows of the database including the id of other users but I want it to fetch all rows of just a particular id that appears as many times on the table.

**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) 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, Dec 28 '20 at 13:42

Rodrigo Ramos · Answer 1 · 2020-12-28T22:51:40.407

0

You can get all items and user id from one request SQL

SELECT * FROM investment where userid = (SELECT id FROM users WHERE username = ?)

OR

SELECT 
* 
FROM 
    investment INV
INNER JOIN
    users U ON U.id = INV.userid
WHERE U.username = ?

To select custom rows you must replace ( * ) by the desired value from table

I did not understand your problem very well :/

edited Dec 28 '20 at 22:51

answered Dec 28 '20 at 13:12

Rodrigo Ramos

36
4

1

Would probably make more sense to do a JOIN than a sub query. – M. Eriksson Dec 28 '20 at 13:16
what i actually want is that i need i have a table that has two different rows from one user, so i want to output the two different rows that are in one table , these answers am getting here are actually confusing me the more – AKATUGBA Dec 28 '20 at 22:21

M. Eriksson · Answer 2 · 2020-12-28T13:29:08.293

You can do it in one query using an inner join, something like this:

SELECT 
    * 
FROM 
    investment 
INNER JOIN
    users ON users.id = investment.userid
WHERE users.username = ?

That will fetch all records from investment that has the username you provide.

You can read more about inner joins here: https://www.mysqltutorial.org/mysql-inner-join.aspx/

Note

As you can see, both me and the other answer use ? instead of the variable with the username. That's because we use parameterized prepared statements instead of injecting data directly into the queries. I would recommend that you read through that as well, since it's the recommended way of using dynamic data in your queries (and is a necessity when working with external data)

please am still new to this i do not really understand where to input the inner join in my code above and also what to remove — AKATUGBA, Dec 28 '20 at 22:12

How to echo more than one row with same id

2 Answers2

Note