Jump to content

Get ID from jobs and insert it to Bookings


Moorcam
Go to solution Solved by Barand,

Recommended Posts

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

Link to comment
Share on other sites

  • Solution

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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>

 

Link to comment
Share on other sites

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>';
    }

}

 

Link to comment
Share on other sites

"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.

 

Link to comment
Share on other sites

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 by DanEthical
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

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.