current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

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.

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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:

  1. if you're not putting any variables in, you don't need to prepare the query. You can just run it as above.
  2. Don't select more columns than you need. Why grab all columns from the table when you only use 2?
  3. if you're joining on a column that has the same name in both tables you can use USING(column_name).
share|improve this answer
    
Ah nice, but what if I have more rows in objects_price than in material, how can I then post everything out? Maybe using material_id as JOIN isn't the right solution. Thanks! –  owwyess Apr 14 at 19:17
    
Maybe it would be smarter to make a twodimensional array and put all material values that has an object_id of 1 inside space 0 (in the array) and all material values that has an object_id of 2 inside space 1 in the array etc. –  owwyess Apr 14 at 19:24
    
@owwyess I've updated my answer for if you have multiple objects per material. You just have an array of objects per material. –  Styphon Apr 14 at 20:53
    
like this: $arr[$row['name']][$row['material_amount']] = $row['object_id']; ? and do I then echo it like: $arr[1][1] (example) –  owwyess Apr 14 at 21:02
1  
Modify the query and the while loop to store the material amount instead of the id in the multidimensional array. –  Styphon Apr 15 at 6:17
up vote 1 down vote

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
 }
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.