Jump to content
Adamhumbug

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

Recommended Posts

Posted (edited)

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 by Adamhumbug

Share this post


Link to post
Share on other sites

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.

  • Thanks 1

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
Posted (edited)
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 by Adamhumbug

Share this post


Link to post
Share on other sites

That's right, yes.

Share this post


Link to post
Share on other sites

Does your table have a UNIQUE constraint on those columns?

UNIQUE (job_id, equipment_id) 

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
Posted (edited)
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 by Adamhumbug

Share this post


Link to post
Share on other sites
16 hours ago, Barand said:

Does your table have a UNIQUE constraint on those columns?


UNIQUE (job_id, equipment_id) 

 

 

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.