How to use php multiple query select to put into an array

Table

barcode(barcode, food_code, company_code)
food(food_code, food_name)
company(company_code, company_name)

It's like this, but it takes too much time to do it using join at first.

So, I tried to use select 3 times to make it faster, but I searched how to write multiple queries in php and tried, but I couldn't get the result I wanted...

The first query statement tries to get food.food_name through barcode.food_code when you enter a barcode.

The second query statement imports only the barcode.

The third query statement reads barcode.company_code and imports company.company_name when you enter a barcode.

Finally, the result I want is to enter a barcode in url and get the information that fits the barcode and put it in the array. How do I write multiple query select to enter the array?

The bottom is the php door that I made.

<?php
require_once 'conn.php';

if (isset($_GET['barcode'])) {
    $barcode = $_GET['barcode'];
    $query = "select FOOD_NAME FROM food WHERE FOOD_CODE=(select FOOD_CODE FROM barcode WHERE BARCODE=(select BARCODE FROM barcode WHERE BARCODE='$barcode')); ";
    $query .= "select BARCODE FROM barcode WHERE BARCODE='$barcode'; ";
    $query .= "select COMPANY_NAME FROM company WHERE COMPANY_CODE=(select COMPANY_CODE FROM barcode WHERE BARCODE=(select BARCODE FROM barcode WHERE BARCODE='$barcode'));";
    $response = [];

    if (mysqli_multi_query($conn, $query)) {
        do {
            if ($result = mysqli_store_result($conn)) {
                while ($row = mysqli_fetch_row($result)) {
                    array_push(
                        $response, [
                            'barcode' => $row['BARCODE'],
                            'food_name' => $row['FOOD_NAME'],
                            'company_name' => $row['COMPANY_NAME']
                        ]
                    );
                }
                mysqli_free_result($result);
            }
        } } while (mysqli_next_result($conn));
    }
    echo json_encode($response);
}

mysqli_close($conn);

php sql

2022-09-20 20:45

1 Answers

There is a thing called mysqli_multi_query() when you use it, it is highly likely that the following simple data inquiry questioning task will go astray.

SELECT b.barcode, f.food_name, c.company_name
  FROM barcode b
  JOIN food f ON f.food_code = b.food_code
  JOIN company c ON c.company_code = b.company_code
 WHERE b.barcode = 'Barcode value'
 GROUP BY b.barcode, f.food_name, c.company_name

I think the problem is that this simple query spins slowly. PK for each table, what is the FK between the tables? If it's well indexed, you can pull it out with a simple PHP code like this.

$barcode = $_GET['barcode'];
$query =
"SELECT b.barcode, f.food_name, c.company_name
  FROM barcode b
  JOIN food f ON f.food_code = b.food_code
  JOIN company c ON c.company_code = b.company_code
 WHERE b.barcode = ?
 GROUP BY b.barcode, f.food_name, c.company_name";

// It is better to use PDO than mysqli_* function.
$dsn = "mysql:host=localhost;port=3306;dbname=testdb;charset=utf8";
$db = new PDO($dsn, "testuser", "testpassword");

// Binds the input value (barcode value) to the place of the question mark in the query statement and executes it.
// If you inject user input into the $query variable like now, you can get a hacking attack called SQL injection.
$stmt = $db->prepare($query);
$stmt->execute([$barcode]);

// That's it.
$result = $stmt->fetchAll(PDO::FETCH_OBJ);
var_dump($result);

If you upload the DDL of three tables, other masters may watch and help you.

2022-09-20 20:45

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656