Extracting data from the database. how to write a loop function PHP MYSQL

Question

The problem is the following. I have two tables users and cards. How to write a loop function when a user has two or more cards? Example: user bane has cards 222222 and 454545. however, when I send a question to the search field, it only throws out the first card.

<?php 
    require "connection.php";
    $search = $_POST['search'];

    $sql = "SELECT users.first_name, users.second_name, cards.card_number
            FROM users
            INNER JOIN cards
            ON cards.user_id = users.id
            WHERE cards.card_number = '$search'
            OR users.first_name = '$search'
            OR users.second_name = '$search'";

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

    echo $result['first_name']." ".$result['second_name']." ",
    $result['card_number']; ?>

https://i.stack.imgur.com/0VvXF.jpg

**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 06 '20 at 13:27

score 0 · Answer 1 · answered Aug 05 '20 at 21:38

0

Run

$results = mysqli_fetch_assoc($query);

foreach( $results as $result) {
    echo .....
}

answered Aug 05 '20 at 21:38

wiseone

99
6

user3502640 · Accepted Answer · 2020-08-06T10:46:28.143

You are returning a single value. If I had to show all the user cards, I would do something like this.

<?php 
    require "connection.php";
    $search = $_POST['search'];

    $sql = "SELECT users.first_name, users.second_name, cards.card_number
            FROM users
            INNER JOIN cards
            ON cards.user_id = users.id
            WHERE
            users.first_name = '$search'
            OR users.second_name = '$search'";

    $query = mysqli_query($db, $sql);
while ($row = mysqli_fetch_assoc($query)) {

    echo $row['first_name']." ".$row['second_name']." ",
    $row['card_number']; 
} 
?>

This should print a line for each card.

You could also change the where clause for something like this to find partial strings:

users.first_name LIKE '%$search%'

But if you would like to be able to search for a card number and find all associated cards to its owner, then you must take some extra steps.

I would go for something like this:

 SELECT t1.customerIDi, cards.number, customer.* 
 FROM CARDS
 LEFT JOIN customer ON cards.CustomerID = customer.id
 INNER JOIN (
     SELECT customer.id AS customerIDi
     FROM customer
     LEFT JOIN cards
     ON cards.CustomerID = customer.id
     WHERE
     Cards.Number = "$search"
 ) AS t1
 ON t1.customerIDi = cards.CustomerID

http://sqlfiddle.com/#!9/82ad8/23/0

I nested a select to find the card owner and then get all the associated cards.

Extracting data from the database. how to write a loop function PHP MYSQL

2 Answers2