Populate a form from one table and populate inputs from a second table

[Adamhumbug]

I am creating a table that creates many rows based on what is in the equipment table.

There is a second table that has information pertaining to an order that has already been made.

 

equipment table is as follows:

equipment_id     equipment_name

          1                   Book

          2                    Dog

         3                     cat

         4                     hat

 

 

equipment order table is as follows:

job_id     equipment_id     equipment_quantity

    10                  1                           100

     10                  3                           100

 

There are 20 things in the equipment table.

When the page loads, i would like it to show all of the items from the equipment table but i would like the rows where there is a quantity defined in the equipment_order table to be populated.

 

So i should see

Eauipment           Quantity

         Book                   100

        Dog                

        cat                       100

        hat

 

The sql that i have used is this

SELECT a.equipment_id, equipment_name, equipment_category, b.equipment_quantity
FROM ssm_equipment a
LEFT JOIN ssm_equipment_order b
ON a.equipment_id = b.equipment_id

Quite rightly this select anything with a quantity regardless of whether it relates to the current job or not.

I have a variable $current_job_id that needs to be used to only get the values for the current job

I tried the following

SELECT a.equipment_id, equipment_name, equipment_category, b.equipment_quantity
FROM ssm_equipment a
LEFT JOIN ssm_equipment_order b
ON a.equipment_id = b.equipment_id
WHERE $current_job_id = 10

but this only get the rows where the ids match.

 

I tried moving the WHERE clause but i cant seem to figure this out with out getting many errors.

I have also tried the followin to no avail.

SELECT a.equipment_id, equipment_name, equipment_category, b.equipment_quantity, b.job_id
FROM ssm_equipment a
LEFT JOIN ssm_equipment_order b
ON a.equipment_id = b.equipment_id
where b.job_id =  '24' or b.job_id='NULL'

The above just shows row with 24

I have talked about this in another thread but as this is very different to the origional question, i have moved it.

Origional questions is below

 

Edited March 26, 2019 by Adamhumbug

[requinix]

The ON condition controls what rows from the new table are matched with the existing tables. A WHERE condition filters the results of everything to only those that match.

If you're wondering, yes: you can have multiple conditions in an ON.

[Adamhumbug]

4 minutes ago, requinix said:

The ON condition controls what rows from the new table are matched with the existing tables. A WHERE condition filters the results of everything to only those that match.

If you're wondering, yes: you can have multiple conditions in an ON.

Ahhhh

So something like this

SELECT a.equipment_id, equipment_name, equipment_category, b.equipment_quantity, b.job_id
FROM ssm_equipment a
LEFT JOIN ssm_equipment_order b
ON a.equipment_id = b.equipment_id and b.job_id = 24
                        

That looks like it would work.

[Adamhumbug]

Now that i have selected it and got it out of the database, i have written an update SQL.  However i now realise i need it to insert if there is nothing to update.

Good has shown me that i need to run an ON DUPLICATE KEY UPDATE but it fails.

 

<?php

if ($_SERVER['REQUEST_METHOD']=='POST') {
    $jobId = $_SESSION['current_job_id'];
    $qty = $_POST['equipmentQty'];
    // prepare insert and update query
  
    $stmt = $conn->prepare("INSERT INTO ssm_equipment_order (equipment_quantity, job_id, equipment_id) VALUES (?,?,?) 
        ON DUPLICATE KEY UPDATE ssm_equipment_order SET equipment_quantity = ? WHERE job_id = ? and equipment_id = ?");
    
    
    foreach ($_POST['equipmentId'] as $k => $eid) {

        if ($qty[$k] > 0) {
            $stmt->bind_param("sss", $qty[$k], $jobId, $eid);
            $stmt->execute();
        }
    } 
}

header("location: ../order-equipment.php");

Is there something that i am missing here?

[requinix]

Drop the WHERE and make sure (job_id, equipment_id) is UNIQUE.

[Adamhumbug]

6 minutes ago, requinix said:

Drop the WHERE and make sure (job_id, equipment_id) is UNIQUE.

Thanks for the quick reply, neither the job_id or the equipment_id will be unique in that table.  The combination will be unique, there will only ever be one row with the job_id 10 and the equipment_id 10

Edited March 26, 2019 by Adamhumbug

[requinix]

That's right, yes.

[Barand]

Does your table have a UNIQUE constraint on those columns?

UNIQUE (job_id, equipment_id) 

 

[Barand]

Plus you query syntax is wrong. INSERT queries do not have a WHERE clause. Also your parameters are numbers, not strings

$stmt = $conn->prepare("INSERT INTO ssm_equipment_order (equipment_quantity, job_id, equipment_id) 
                        VALUES (?,?,?) 
                        ON DUPLICATE KEY UPDATE 
                            equipment_quantity = VALUES(equipment_quantity)"
                        ); 
    
    
    foreach ($_POST['equipmentId'] as $k => $eid) {

        if ($qty[$k] > 0) {
            $stmt->bind_param("iii", $qty[$k], $jobId, $eid);
            $stmt->execute();
        }
    } 

Note: If you have "... UPDATE equipment_quantity = ? " using another placeholder then you would need to add the quantity a second time in the parameters. Hence my use of the VALUES() function.

[Adamhumbug]

15 hours ago, Barand said:

Plus you query syntax is wrong. INSERT queries do not have a WHERE clause. Also your parameters are numbers, not strings

$stmt = $conn->prepare("INSERT INTO ssm_equipment_order (equipment_quantity, job_id, equipment_id) 
                        VALUES (?,?,?) 
                        ON DUPLICATE KEY UPDATE 
                            equipment_quantity = VALUES(equipment_quantity)"
                        ); 
    
    
    foreach ($_POST['equipmentId'] as $k => $eid) {

        if ($qty[$k] > 0) {
            $stmt->bind_param("iii", $qty[$k], $jobId, $eid);
            $stmt->execute();
        }
    } 

Note: If you have "... UPDATE equipment_quantity = ? " using another placeholder then you would need to add the quantity a second time in the parameters. Hence my use of the VALUES() function.

Hi Barand,

Thanks for this - i have tried this in my code and have found that when i add a value to a new equipment ID, the insert works but at the same time, everything else in the table gets duplicated.

When i try and update an existing value, the value gets added rather than updating the original and everythig else is duplicated

The reason i had the where in orginally was due to the update that i had written which worked perfectly that i tried to modify and combine with the second set of code below which was the origional submission

$stmt = $conn->prepare("UPDATE ssm_equipment_order SET equipment_quantity = ? WHERE job_id = ? and equipment_id = ?");

$stmt = $conn->prepare("INSERT INTO ssm_equipment_order (equipment_quantity, job_id, equipment_id) VALUES (?,?,?) ");

 

Currently, using the code that you have provided and attempts that i have had myself, everytime i click the update button, the whole table content gets duplicated.

Kind Regards

 

ADam

Edited March 27, 2019 by Adamhumbug

[Barand]

16 hours ago, Barand said:

Does your table have a UNIQUE constraint on those columns?

UNIQUE (job_id, equipment_id) 

 

 

[Adamhumbug]

4 hours ago, Barand said:

 

Ok, so if i follow you correctly, i have run the follwing on my table

 

ALTER TABLE `ssm_equipment_order` ADD UNIQUE( `job_id`, `equipment_id`);

Is this what you were referring to?

[Adamhumbug]

1 minute ago, Adamhumbug said:

Ok, so if i follow you correctly, i have run the follwing on my table

 

ALTER TABLE `ssm_equipment_order` ADD UNIQUE( `job_id`, `equipment_id`);

Is this what you were referring to?

Ok tested and that did the trick.  Thanks for all of your help.  I didnt even know that was a thing.

 

Kind Regards

 

Adam