Join two mysql tables for populate datatable

[levisperez2000]

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 my datatable outpout:

Now I just show “customer_id” in datatable, this the Query I do for populating datatable:

<?php

$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;

I want to do a query like below for joining mysql tables (but It doesn’t work):

<?php

$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',
	4 => 'customer_name'
    );
 
    $sql = "SELECT ".$columns." FROM ".$table." ".$where;

This is my current whole php fetch code for populating my datatable (works, but just show customer_id):

<?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'];
	//$total = $_REQUEST['total'];
	
	if($order_item == 'Todos'){
        $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['tipodegasto'] = $row["tipodegasto"];
       // $nestedData['codigo'] = $row["codigo"];

       // $nestedData['nombregasto'] = '<a href="mailto:'.strtolower($row["nombregasto"]).'">'.strtolower($row["nombregasto"]).'</a>';
	//   $nestedData['nombregasto'] = $row["nombregasto"];

        $nestedData['customer_id'] = $row["order_value"];
        $nestedData['order_item'] = $row["order_item"];

       // $time = strtotime($row["order_valuepago"]);
       // $nestedData['order_valuepago'] = date('h:i:s A - d M, Y', $time);
		$nestedData['order_value'] = $row["order_value"];
		$nestedData['order_date'] = $row["order_date"];
	//	$nestedData['nombrepaciente'] = $row["nombrepaciente"];



        $data[] = $nestedData;
    }

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

    echo json_encode($json_data);
}
?>

Please anyideas?

[mabismad]

I recommend that you use explicit JOIN syntax -

... FROM table1
    JOIN table2 ON join_condition
...

I also recommend that you use 1-2 character table alias names and use alias.column syntax for every column reference even in the cases where they are not needed to make the query work, so that anyone reading the sql statement can tell what it is trying to do without needing to know your table definitions.

Also, don’t put external, unknown, dynamic values directly into the sql query statement, where sql special values can break the sql query syntax, which is how sql injection is accomplished. Use a prepared query instead.

Don’t use $_REQUEST. Use the correct $_GET, $_POST, or $_COOKIE variables that you expect the data to be in.

Don’t copy variables to other variables for nothing. Just use the original variables.

You should trim all input data before validating it.

You should validate all input data before using it. If a ‘required’ input is an empty string, that’s an error. If a value must have a specific format, such an email or date/time, if it doesn’t, that’s an error.

If you add the WHERE terms that are being AND’ed to an array, you can simple implode those terms with the ’ AND ’ keyword to produce the WHERE clause. This will simplify and clarify the logic. This will also help you to see and eliminate the extra execution of the query when there is a search term.

Edit: For pagination, to get the count of the total number of matching rows, don’t SELECT all the matching data, use a SELECT COUNT() … query, then fetch and use the COUNT() value.

Edit2: if the ‘search’ input is not empty, you would add the search term to the WHERE clause and only execute the first query once.

Edit3: you must validate that the ‘order’ input values are only and exactly permitted values before using them in the sql query statement.

[levisperez2000]

Thanks for your answer, I did the following:

$query = "SELECT * FROM tbl_order_id,customers WHERE ";


$query .= 'tbl_order_id.customer_id=customers.customer_id AND ';

if($_POST["is_date_search"] == "yes")
{
	if($_POST["customer_name"] != "")
{
 $query .= ' customer_name="'.$_POST["customer_name"].'" AND ';
}

if($_POST["start_date"] != "" && $_POST["end_date"] !="")
{
 $query .= ' order_date BETWEEN "'.$_POST["start_date"].'" AND "'.$_POST["end_date"].'" AND ';
}