Moorcam Posted July 16, 2021 Share Posted July 16, 2021 Hi guys, I am trying to get data from the table "jobs" and insert its id and name into table "bookings" I can get the job "name" from "jobs", which is fine, using the following: <select class="form-control" id="tour_name" name="tour_name"> <option value="Select">== Select Tour or Charter ==</option> <?php $sql = "SELECT name FROM jobs"; $result = $con->query($sql); while(list($name) = mysqli_fetch_row($result)){ ?> <option value="<?php echo $name ?>"><?php echo $name;?></option> <?php } ?> </select> However, when I click submit to insert into "bookings" everything goes in except "booking_id" which is "id" in "jobs". Hope this makes sense. I am using the following to insert: if(isset($_POST['new']) && $_POST['new']==1){ $sql = "SELECT id FROM jobs"; $result = $con->query($sql); while(list($id) = mysqli_fetch_row($result)){ } $tour_id = isset($_GET['id']) ? $_GET['id'] : ''; $tour_name = mysqli_real_escape_string($con, $_POST['tour_name']); $customer_name = mysqli_real_escape_string($con, $_POST['customer_name']); $customer_address = mysqli_real_escape_string($con, $_POST['customer_address']); $customer_email = mysqli_real_escape_string($con, $_POST['customer_email']); $customer_phone = mysqli_real_escape_string($con, $_POST['customer_phone']); $total_pax = mysqli_real_escape_string($con, $_POST['total_pax']); $status = mysqli_real_escape_string($con, $_POST['status']); $order_at = mysqli_real_escape_string($con, date("Y-m-d H:i:s")); $total_amount = mysqli_real_escape_string($con, $_POST['total_amount']); $query="insert into bookings (`tour_id`, `tour_name`, `customer_name`, `customer_address`, `customer_email`, `customer_phone`, `total_pax`, `status`, `order_at`, `total_amount`)values ('$tour_id', '$tour_name', '$customer_name', '$customer_address', '$customer_email', '$customer_phone', '$total_pax', '$status', '$order_at', '$total_amount')"; mysqli_query($con,$query) or die(mysqli_error($con)); if(mysqli_affected_rows($con)== 1 ){ $message = '<p class="text-success"><i class="fa fa-check"></i> - Record Inserted Successfully</p>'; } } Can anyone please lend a hand with this? 2 Days at it now and ready to hit the Guinness. Cheers, Dan Quote Link to comment https://forums.phpfreaks.com/topic/313392-get-id-from-jobs-and-insert-it-to-bookings/ Share on other sites More sharing options...
Solution Barand Posted July 16, 2021 Solution Share Posted July 16, 2021 You're making it difficult for yourself. The tour/job name should not be in the booking table. The only place that should occur in your database is in the tour/job table. It is just the tour id that be in the booking table. +------------------+ | booking | +----------------+ +------------------+ | tour/job | | booking_id | +----------------+ | tour_id |>----------------| tour_id | | cust_name | | tour_name | | cust_address | +----------------+ | order_at | | ... | | etc | +------------------+ When you create your dropdown options, the values of the options should be the id and not the name. Then all you do is insert $_POST['tour_name'] (which will actually be the id) into the booking data. Job done. Quote Link to comment https://forums.phpfreaks.com/topic/313392-get-id-from-jobs-and-insert-it-to-bookings/#findComment-1588360 Share on other sites More sharing options...
Moorcam Posted July 16, 2021 Author Share Posted July 16, 2021 Just now, Barand said: You're making it difficult for yourself. The tour/job name should not be in the booking table. The only place that should occur in your database is in the tour/job table. It is just the tour id that be in the booking table. +------------------+ | booking | +----------------+ +------------------+ | tour/job | | booking_id | +----------------+ | tour_id |>----------------| tour_id | | cust_name | | tour_name | | cust_address | +----------------+ | order_at | | ... | | etc | +------------------+ When you create your dropdown options, the values of the options should be the id and not the name. Then all you do is insert $_POST['tour_name'] (which will actually be the id) into the booking data. Job done. Hi mate That is fine but the issue is, if an end user, such as a non-techy type person, was to create a booking from the backend, selecting an ID will make no sense to them. They will need to see the name of the job they are booking onto. Quote Link to comment https://forums.phpfreaks.com/topic/313392-get-id-from-jobs-and-insert-it-to-bookings/#findComment-1588361 Share on other sites More sharing options...
Barand Posted July 16, 2021 Share Posted July 16, 2021 1 minute ago, DanEthical said: They will need to see the name of the job they are booking onto. They will see the name of the job they are booking in the dropdown, just as they do now. The only difference is what gets stored. Quote Link to comment https://forums.phpfreaks.com/topic/313392-get-id-from-jobs-and-insert-it-to-bookings/#findComment-1588362 Share on other sites More sharing options...
Moorcam Posted July 16, 2021 Author Share Posted July 16, 2021 4 minutes ago, Barand said: They will see the name of the job they are booking in the dropdown, just as they do now. The only difference is what gets stored. Ah I see. Tried that. Getting undefined variable id in the <option value part: <?php $sql = "SELECT id, name FROM jobs"; $result = $con->query($sql); while(list($name) = mysqli_fetch_row($result)){ ?> <option value="<?php echo $id ?>"><?php echo $name;?></option> <?php } ?> I appreciate your help. Quote Link to comment https://forums.phpfreaks.com/topic/313392-get-id-from-jobs-and-insert-it-to-bookings/#findComment-1588363 Share on other sites More sharing options...
Barand Posted July 16, 2021 Share Posted July 16, 2021 (edited) You need while (list($id, $name) = mysqli_fetch_row($result)) { Edited July 16, 2021 by Barand Quote Link to comment https://forums.phpfreaks.com/topic/313392-get-id-from-jobs-and-insert-it-to-bookings/#findComment-1588364 Share on other sites More sharing options...
Moorcam Posted July 16, 2021 Author Share Posted July 16, 2021 Just now, Barand said: You need whie (list($id, $name) = mysqli_fetch_row($result)) { Did that. Now dropdown only shows id Quote Link to comment https://forums.phpfreaks.com/topic/313392-get-id-from-jobs-and-insert-it-to-bookings/#findComment-1588365 Share on other sites More sharing options...
Barand Posted July 16, 2021 Share Posted July 16, 2021 The you didn't do it right. Post current code Quote Link to comment https://forums.phpfreaks.com/topic/313392-get-id-from-jobs-and-insert-it-to-bookings/#findComment-1588366 Share on other sites More sharing options...
Moorcam Posted July 16, 2021 Author Share Posted July 16, 2021 I call it Irish Brain Syndrome. Here is the fill add-booking.php <?php include_once('includes/header.php'); if(isset($_POST['new']) && $_POST['new']==1){ $sql = "SELECT id FROM jobs"; $result = $con->query($sql); while(list($id) = mysqli_fetch_row($result)){ } $tour_id = isset($_GET['id']) ? $_GET['id'] : ''; $tour_name = mysqli_real_escape_string($con, $_POST['tour_name']); $customer_name = mysqli_real_escape_string($con, $_POST['customer_name']); $customer_address = mysqli_real_escape_string($con, $_POST['customer_address']); $customer_email = mysqli_real_escape_string($con, $_POST['customer_email']); $customer_phone = mysqli_real_escape_string($con, $_POST['customer_phone']); $total_pax = mysqli_real_escape_string($con, $_POST['total_pax']); $status = mysqli_real_escape_string($con, $_POST['status']); $order_at = mysqli_real_escape_string($con, date("Y-m-d H:i:s")); $total_amount = mysqli_real_escape_string($con, $_POST['total_amount']); $query="insert into bookings (`tour_id`, `tour_name`, `customer_name`, `customer_address`, `customer_email`, `customer_phone`, `total_pax`, `status`, `order_at`, `total_amount`)values ('$tour_id', '$tour_name', '$customer_name', '$customer_address', '$customer_email', '$customer_phone', '$total_pax', '$status', '$order_at', '$total_amount')"; mysqli_query($con,$query) or die(mysqli_error($con)); if(mysqli_affected_rows($con)== 1 ){ $message = '<p class="text-success"><i class="fa fa-check"></i> - Record Inserted Successfully</p>'; } } ?> <!-- Header--> <div class="breadcrumbs"> <div class="col-sm-4"> <div class="page-header float-left"> <div class="page-title"> <h1>Bookings</h1> </div> </div> </div> <div class="col-sm-8"> </div> </div> <div class="content mt-3"> <div class="animated fadeIn"> <div class="row"> <div class="col-lg-12"> <div class="card"> <div class="card-header"><strong>Add </strong><small>Booking <?php if($message = isset($message) ? $message : ''){ printf($message); } ?></small></div> <div class="card-body card-block"> <form role="form" method="post" name="add_tour" id="add_tour" action"" > <input type="hidden" name="new" value="1" /> <div class="modal-body"> <div class="row form-group"> <div class="col-6"> <div class="form-group"><label for="customer_name" class=" form-control-label">Customer Name</label><input type="text" id="customer_name" name="customer_name" placeholder="Customer Name" class="form-control"> </div> </div> <div class="col-6"> <div class="form-group"><label for="customer_address" class=" form-control-label">Customer Address</label> <input type="text" class="form-control" id="customer_address" name="customer_address" placeholder="Customer's Address" > </div> </div> </div> <div class="row form-group"> <div class="col-6"> <div class="form-group"><label for="total_pax" class=" form-control-label">Pax</label> <input type="text" id="total_pax" name="total_pax" placeholder="How many passengers?" class="form-control"> </div> </div> <div class="col-6"> <div class="form-group"><label for="total_amount" class=" form-control-label">Price</label><input type="text" id="total_amount" name="total_amount" placeholder="0.00" class="form-control"> </div> </div> </div> <div class="row form-group"> <div class="col-6"> <div class="form-group origin" ><label for="customer_email" class=" form-control-label">Customer Email</label><input type="email" id="customer_email" name="customer_email" placeholder="Customer Email" class="form-control"></div> </div> <div class="col-6"> <div class="form-group"><label for="customer_phone" class=" form-control-label">Customer Phone</label><input type="phone" id="customer_phone" name="customer_phone" placeholder="Customer Phone" class="form-control"></div> </div> </div> <div class="row form-group"> <div class="col-6"> <div class="form-group origin" ><label for="status" class=" form-control-label">Payment Status</label> <select class="form-control" id="status" name="status"> <option value =""> == Choose One ==</option> <option value="PAID">PAID</option> <option value="UNPAID">UNPAID</option> </select> </div> </div> <div class="col-6"> <div class="form-group"><label for="tour_name" class=" form-control-label">Tour or Charter</label> <select class="form-control" id="tour_name" name="tour_name"> <option value="Select">== Select Tour or Charter ==</option> <?php $sql = "SELECT name, id FROM jobs"; $result = $con->query($sql); while(list($id, $name) = mysqli_fetch_row($result)){ ?> <option value="<?php echo $id ?>"><?php echo $name;?></option> <?php } ?> </select> </div> </div> </div> <div class="modal-footer"> <button type="reset" class="btn btn-warning">Clear Form</button> <button type="submit" name="submit" id="submit" class="btn btn-primary">Confirm</button> </div> </form> </div> </div> </div><!-- .animated --> </div><!-- .content --> </div><!-- /#right-panel --> <!-- Right Panel --> <script src="assets/js/vendor/jquery-2.1.4.min.js"></script> <script src="assets/js/popper.min.js"></script> <script src="assets/js/plugins.js"></script> <script src="assets/js/main.js"></script> <script src="assets/js/bing.js"></script> <script src="assets/js/lib/data-table/datatables.min.js"></script> <script src="assets/js/lib/data-table/dataTables.bootstrap.min.js"></script> <script src="assets/js/lib/data-table/dataTables.buttons.min.js"></script> <script src="assets/js/lib/data-table/buttons.bootstrap.min.js"></script> <script src="assets/js/lib/data-table/jszip.min.js"></script> <script src="assets/js/lib/data-table/pdfmake.min.js"></script> <script src="assets/js/lib/data-table/vfs_fonts.js"></script> <script src="assets/js/lib/data-table/buttons.html5.min.js"></script> <script src="assets/js/lib/data-table/buttons.print.min.js"></script> <script src="assets/js/lib/data-table/buttons.colVis.min.js"></script> <script src="assets/js/lib/data-table/datatables-init.js"></script> <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script> <script src="https://cdn.tiny.cloud/1/sw6bkvhzd3ev4xl3u9yx3tzrux4nthssiwgsog74altv1o65/tinymce/5/tinymce.min.js" referrerpolicy="origin"></script> </body> </html> Quote Link to comment https://forums.phpfreaks.com/topic/313392-get-id-from-jobs-and-insert-it-to-bookings/#findComment-1588367 Share on other sites More sharing options...
Moorcam Posted July 16, 2021 Author Share Posted July 16, 2021 Working now. I am getting the ID now into the tour_id but also into the tour_name field. I need tour_name to go into tour_name. if(isset($_POST['new']) && $_POST['new']==1){ $tour_id = mysqli_real_escape_string($con, $_POST['tour_name']); $tour_name = mysqli_real_escape_string($con, $_POST['tour_name']); $customer_name = mysqli_real_escape_string($con, $_POST['customer_name']); $customer_address = mysqli_real_escape_string($con, $_POST['customer_address']); $customer_email = mysqli_real_escape_string($con, $_POST['customer_email']); $customer_phone = mysqli_real_escape_string($con, $_POST['customer_phone']); $total_pax = mysqli_real_escape_string($con, $_POST['total_pax']); $status = mysqli_real_escape_string($con, $_POST['status']); $order_at = mysqli_real_escape_string($con, date("Y-m-d H:i:s")); $total_amount = mysqli_real_escape_string($con, $_POST['total_amount']); $query="insert into bookings (`tour_id`, `tour_name`, `customer_name`, `customer_address`, `customer_email`, `customer_phone`, `total_pax`, `status`, `order_at`, `total_amount`)values ('$tour_id', '$tour_name', '$customer_name', '$customer_address', '$customer_email', '$customer_phone', '$total_pax', '$status', '$order_at', '$total_amount')"; mysqli_query($con,$query) or die(mysqli_error($con)); if(mysqli_affected_rows($con)== 1 ){ $message = '<p class="text-success"><i class="fa fa-check"></i> - Record Inserted Successfully</p>'; } } Quote Link to comment https://forums.phpfreaks.com/topic/313392-get-id-from-jobs-and-insert-it-to-bookings/#findComment-1588368 Share on other sites More sharing options...
Barand Posted July 16, 2021 Share Posted July 16, 2021 "Tour_name" should not be in the booking table, just the id 30 minutes ago, Barand said: The tour/job name should not be in the booking table. The only place that should occur in your database is in the tour/job table. It is just the tour id that be in the booking table. Quote Link to comment https://forums.phpfreaks.com/topic/313392-get-id-from-jobs-and-insert-it-to-bookings/#findComment-1588369 Share on other sites More sharing options...
Moorcam Posted July 16, 2021 Author Share Posted July 16, 2021 (edited) 1 minute ago, Barand said: "Tour_name" should not be in the booking table, just the id I see. Oh well. Not a biggie. Just wanted it displayed in the booking table on the site so end user can see which tour was booked. Thanks as always for your help. Always appreciated. Edited July 16, 2021 by DanEthical Quote Link to comment https://forums.phpfreaks.com/topic/313392-get-id-from-jobs-and-insert-it-to-bookings/#findComment-1588370 Share on other sites More sharing options...
Barand Posted July 16, 2021 Share Posted July 16, 2021 You do that with a JOIN SELECT b.customer_name , j.tour_name FROM bookings b JOIN jobs j ON b.tour_id = j.id Quote Link to comment https://forums.phpfreaks.com/topic/313392-get-id-from-jobs-and-insert-it-to-bookings/#findComment-1588371 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.