Join to mysql tables

lexerpv

Hi, I've got two mysql tables "tbl_order_id" and "customers", the mysql tables have in common the field "customer_id", I want to join tables using customer_id for showing customer_name field in datatable (customer_name is in table customers)
This is my tbl_order_id table:

This is my customers table:

My datatable now:

This is my jquery-ajax-id.php file where I do mysql query:

    <?php
    include 'config/db-config.php';
    global $connection;

    if($_REQUEST['action'] == 'fetch_data'){

        $requestData = $_REQUEST;
        $start = $_REQUEST['start'];

        $initial_date = $_REQUEST['initial_date'];
        $final_date = $_REQUEST['final_date'];
        $order_item = $_REQUEST['order_item'];

        if($order_item == 'All'){
            $order_item =  '' ;
        }


        if(!empty($initial_date) && !empty($final_date)){
            $date_range = " AND order_date BETWEEN '".$initial_date."' AND '".$final_date."' ";
        }else{
            $date_range = "";
        }

        if($order_item != ''){
            $order_item = " AND order_item = '$order_item' ";
        }



        $columns = ' customer_id , order_item, order_date, order_value ';
        $table = ' tbl_order_id ';
        $where = " WHERE customer_id !='' ".$date_range.$order_item;

        $columns_order = array(
            0 => 'customer_id',
            1 => 'order_item',
            2 => 'order_date',
            3 => 'order_value'
        );



        $sql = "SELECT ".$columns." FROM ".$table." ".$where;

        $result = mysqli_query($connection, $sql);
        $totalData = mysqli_num_rows($result);
        $totalFiltered = $totalData;

        if( !empty($requestData['search']['value']) ) {
            $sql.=" AND ( order_item LIKE '%".$requestData['search']['value']."%' ";
            $sql.=" OR order_date LIKE '%".$requestData['search']['value']."%' )";
            $sql.=" OR order_value LIKE '".$requestData['search']['value']."'";
        }

        $result = mysqli_query($connection, $sql);
        $totalData = mysqli_num_rows($result);
        $totalFiltered = $totalData;

        $sql .= " ORDER BY ". $columns_order[$requestData['order'][0]['column']]."   ".$requestData['order'][0]['dir'];

        if($requestData['length'] != "-1"){
            $sql .= " LIMIT ".$requestData['start']." ,".$requestData['length'];
        }

        $result = mysqli_query($connection, $sql);
        $data = array();
        $counter = $start;

        $count = $start;
        while($row = mysqli_fetch_array($result)){
            $count++;

            $nestedData = array();
            $nestedData['counter'] = $count;
            $nestedData['customer_id'] = $row["order_value"];
            $nestedData['order_item'] = $row["order_item"];
            $nestedData['order_value'] = $row["order_value"];
            $nestedData['order_date'] = $row["order_date"];

            $data[] = $nestedData;
        }

        $json_data = array(
            "draw"            => intval( $requestData['draw'] ),
            "recordsTotal"    => intval( $totalData),
            "recordsFiltered" => intval( $totalFiltered ),
            "records"         => $data
        );

        echo json_encode($json_data);
    }
    ?>

I want to make a query joining the two mysql tables: tbl_order_id and customers like this (But It doesn't work):

 $columns = ' customer_id , order_item, order_date, order_value, customer_name ';
    $table = ' tbl_order_id, customers ';
    $where = " WHERE tbl_order_id. customer_id=customers.customer_id OR .customer_id !='' ".$date_range.$order_item;

    $columns_order = array(
        0 => 'customer_id',
        1 => 'order_item',
        2 => 'order_date',
        3 => 'order_value'
    );
    $sql = "SELECT ".$columns." FROM ".$table." ".$where;

Please Any ideas?

allan

If you want to use this approach I would suggest echoing out the SQL statements that your code is generating so you can debug them directly.

However, I would strongly suggest you consider using the Editor server-side libraries. They are free and open source, you don't need an Editor license to use them and they have full support for joins with server-side processing enabled. Have a look at this blog post which discusses how to use them.

Allan