Jump to content

Adamhumbug

Members
  • Content Count

    66
  • Joined

  • Last visited

Community Reputation

0 Neutral

About Adamhumbug

  • Rank
    Regular Member
  1. Adamhumbug

    MySQL pulling results twice

    Thanks so much for your help. I will for sure take your suggestions on board and have a look at how i am doing things. Thanks again Barand, i will take yours on board also. How would i go about this without having two sets of code? Would i have another document with an include?
  2. Adamhumbug

    MySQL pulling results twice

    I have just realised, that i dont have job id in that query. I need to only select items that have the job id that has been set. In the current case, i only have one job id in the table but i dont use this variable in the second query $jid = $_SESSION[current_job_id]
  3. Adamhumbug

    MySQL pulling results twice

    Just as an fyi, when i run this in mysql this is what i see. I changed the ? in the query to be 1.
  4. Adamhumbug

    MySQL pulling results twice

    Table A - ssm_menu This is where the menu name and price are defined menu_id menu_name menu_price 1 menu 1 22.50 2 menu 2 51.50 Table B - ssm_menu_connection This is where the menu items are connected to a menu menu_id menu_item_id surrogate_id 1 1 1 1 4 2 2 1 3 Table C - ssm_menu_item This is where the individual items for each menu are defined and given a catagory menu_item_id menu_item_name menu_item_catagory 1 soup starter 2 pudding desert Table D - ssm_menu_order When the menu items are given a qantity, this is the table that they go into for later use. job_id menu_id menu_item_id menu_item_qty surrogate_id 1 1 1 100 1 1 1 2 100 2 This is the basic layout of all of the tables that are used in the query. If the user selects menu one and submits the form with quantities for some of the items, when the page reloads i want it to display all items in the menu that the user select but also show the quantities that they submitted. This will mean that some items have quantities and some do not. Either way all menu items for that menu should be shown. I hope this helps. Kind Regards
  5. Adamhumbug

    MySQL pulling results twice

    Hi all, I am pulling info from my database but for some reason the results are being populated twice. The code i have is below: $jid = $_SESSION['current_job_id']; $sql = "SELECT * FROM ssm_menu_order where job_id = $jid"; $result = mysqli_query($conn, $sql); if(mysqli_num_rows($result)>0){ $sql = "SELECT menu_name, menu_price, menu_item_name, menu_item_catagory, b.menu_item_id, a.menu_id, d.job_id, d.menu_item_qty FROM ssm_menu a INNER JOIN ssm_menu_connection b on a.menu_id = b.menu_id INNER JOIN ssm_menu_items c on b.menu_item_id = c.menu_item_id left join ssm_menu_order d on c.menu_item_id = d.menu_item_id WHERE d.menu_id = ? ORDER BY (CASE c.menu_item_catagory WHEN 'Starter' THEN 1 WHEN 'Main' THEN 2 WHEN 'Dessert' THEN 3 ELSE 100 END) ASC, c.menu_item_name ASC"; $stmt = $conn->prepare($sql); $stmt->bind_param("i", $_GET['q']); $stmt->execute(); $stmt->store_result(); $stmt->bind_result($mname, $mprice, $mitname, $mitcat, $miid, $mid, $jid, $qty); echo"<form action='actions/update-menu-action.php' method='post'>"; echo "<button type='submit' name='select_menu_update_button' class='btn btn-primary float-right mb-2'>Update</button>"; echo "<table class='table table-striped table-hover table-bordered mt-3'>"; $first = true; while($stmt->fetch()){ if($first) { echo "<tr>"; echo "<th class='text-center' colspan='5'><div>".$mname." - £".$mprice."</div></th>"; echo "</tr>"; echo "<tr class='text-center'><th>Course</th><th>Dish</th><th>Notes</th><th>Quantity</th></tr>"; $first = NULL; } echo "<tr>"; echo "<td class=''>" . $mitcat . "</td>"; echo "<td class=''>" . $mitname . "</td>"; echo "<td><input type='text' /></td>"; echo "<input type='hidden' name='menuItemId[]' value='".$miid."'>"; echo "<input name='menuId' type='hidden' value='".$mid."'/>"; echo "<td><input class='text-center' name='menuItemQty[]' value='".$qty."' /></td>"; echo "</tr>"; } echo "</table>"; echo "</form>"; $stmt->close(); } else { $sql = "SELECT menu_name, menu_price, menu_item_name, menu_item_catagory, b.menu_item_id, a.menu_id FROM ssm_menu a INNER JOIN ssm_menu_connection b on a.menu_id = b.menu_id INNER JOIN ssm_menu_items c on b.menu_item_id = c.menu_item_id WHERE a.menu_id = ? ORDER BY (CASE c.menu_item_catagory WHEN 'Starter' THEN 1 WHEN 'Main' THEN 2 WHEN 'Dessert' THEN 3 ELSE 100 END) ASC, c.menu_item_name ASC"; $stmt = $conn->prepare($sql); $stmt->bind_param("i", $_GET['q']); $stmt->execute(); $stmt->store_result(); $stmt->bind_result($mname, $mprice, $mitname, $mitcat, $miid, $mid); echo"<form action='actions/submit-menu-action.php' method='post'>"; echo "<button type='submit' name='select_menu_submit_button' class='btn btn-primary float-right mb-2'>Submit</button>"; echo "<table class='table table-striped table-hover table-bordered mt-3'>"; $first = true; while($stmt->fetch()){ if($first) { echo "<tr>"; echo "<th class='text-center' colspan='5'><div>".$mname." - £".$mprice."</div></th>"; echo "</tr>"; echo "<tr class='text-center'><th>Course</th><th>Dish</th><th>Notes</th><th>Quantity</th></tr>"; $first = NULL; } echo "<tr>"; echo "<td class=''>" . $mitcat . "</td>"; echo "<td class=''>" . $mitname . "</td>"; echo "<td><input type='text' /></td>"; echo "<input type='hidden' name='menuItemId[]' value='".$miid."'>"; echo "<input name='menuId' type='hidden' value='".$mid."'/>"; echo "<td><input name='menuItemQty[]' /></td>"; echo "</tr>"; } echo "</table>"; echo "</form>"; $stmt->close(); } For some reason, the reasults are being echo'd twice.
  6. Ok tested and that did the trick. Thanks for all of your help. I didnt even know that was a thing. Kind Regards Adam
  7. 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?
  8. 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
  9. 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
  10. 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?
  11. 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.
  12. Adamhumbug

    Submit large form created with select statement

    I have moved this question to here Thanks for all of your help so far
  13. 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
  14. Adamhumbug

    Move tr to top of table if input changed

    Ahh great idea, thanks
  15. Adamhumbug

    Move tr to top of table if input changed

    I have been using the following code to move the tr to the top if the input is changed. $('#equipmentTable input.eqQty').focusout(function() { var row = $(this).closest('tr'); if ($(this).hasClass('up')) row.insertBefore( row.parent().find('tr:first-child') ) .find('label').html('move to bottom'); else row.next().after(row); }); I have actually found it really annoying and have decided to look at another approach. I would actually like it to move to the top of the table but appear underneath all of the other rows that have something in the input box. This will mean that the first changed textbox will be at the top, second will be second and so on. Ideally it wont move if you change it. This is well above my javascript knowledge so would appreciate some assistance. I have been playing with the insertBefore and making it insertAfter but have been unable to get it to do what i wanted. Thanks in advance, Kind Regards Adam
×

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.