MySQL PHP将数据从两行插入到多维数组中

问题描述：

I am trying to insert data into a multidimensional array from two tables in my database, but it does something that I don't really know how to fix.

First of all, here is my PHP script:

if ($stmt = $mysqli->prepare('SELECT objects_price.object_id, materials.material_id, materials.name, material_amount
                              FROM objects_price
                              INNER JOIN materials ON objects_price.material_id=materials.material_id
                             ')) {
    $stmt->execute();

    $stmt->bind_result($object_id, $material_id, $material_name, $material_amount);
    $j = 1;
    $arr = array();

    while ($stmt->fetch()) {
        $k = 1;
        if ($object_id == $k) {
            $arr[] = array($j, $material_name, $material_amount);
            $k++;
            $j++;
        }
    }

    $stmt->close();

This script takes out some values from the database and inserts them in the array. My database looks like this: (objects_price)

This is objects_price

(materials)

This is materials

What happens right now is that it only outputs:

What the code above outputs

If I change the database query to:

if ($stmt = $mysqli->prepare('SELECT objects_price.object_id, materials.material_id, materials.name, material_amount
                            FROM objects_price
                            INNER JOIN materials
                            ')) {

it outputs this:

with only INNER JOIN materials

So the left side of this table is shown correct, but the right side shows only the first row of the price, (47). It should look like this:

enter image description here

I don't know if this is easily achieveable by changing the database query. But I hope I can get some advice, suggestions or help in here. Thanks in advance.

答

Try using a left join for your table:

$query = "SELECT m.name, op.objects_id
    FROM objects_price op 
    LEFT JOIN materials m USING(material_id)";
$result = $mysqli->query($query);

The next thing you need to change is the way you handle the results. It's quite possible you're going through the results and skipping many, which is why you only end up with one. Try this:

$arr = array();
while($row = $result->fetch_assoc()) {
    $arr[$row['objects_id']] = $row['name'];
}

If you have more than 1 material per object then just use this slight adjustment for a multidimensional array

$arr = array();
while($row = $result->fetch_assoc()) {
    $arr[$row['name']][] = $row['objects_id'];
}

Then to produce your table from this:

echo '<table>';
foreach($arr as $name => $objects) {
    echo '<tr><td>' . $name . '</td><td>';
    $space = '';
    foreach($objects as $id) {
        echo $space . $id;
        $space = ", ";
    }
    echo '</td></tr>';
}
echo '</table>';

A few tips:

if you're not putting any variables in, you don't need to prepare the query. You can just run it as above.
Don't select more columns than you need. Why grab all columns from the table when you only use 2?
if you're joining on a column that has the same name in both tables you can use USING(column_name).

答

Everything looks good in your code except for these lines:

 while ($stmt->fetch()) {
    $k = 1;

What is happening is that $k is being declared and given the value of 1. Because it is in the loop, each time that line is run the value goes back to 1. To solve this simply put that value outside the loop like this:

 $k = 1;
 while ($stmt->fetch()) {
     //blah blah blah
 }

MySQL PHP将数据从两行插入到多维数组中

相关推荐