Join two mysql tables for populate datatable

[lexer]

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 mysql

table:

This my datatable outpout:

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

$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):

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

 

[spamjim]

Focus on the query, not the PHP. There's even a Tek-Tips forum to focus on MySQL.

https://www.mysqltutorial.org/mysql-left-join.aspx

SELECT 
    `order_id`, `customer_id`, `order_item`, `order_date`, `order_value`, `customer_name`
FROM
    `tbl_order_id`
LEFT JOIN `customers` ON 
    `tbl_order_id.customer_id` = `customers.customer_id`;

 

[lexer]

Thanks for your answer, I did the following and It works:

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

This is the whole code:

<?php
//fetch.php
$connect = mysqli_connect("localhost", "root", "mysq1passw0rd", "testing");
$columns = array('order_id', 'customer_id', 'order_item', 'order_value', 'order_date', 'customer_name');

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


}

if(isset($_POST["search"]["value"]))
{
 $query .= '
  (order_id LIKE "%'.$_POST["search"]["value"].'%" 
  OR tbl_order_id.customer_id LIKE "%'.$_POST["search"]["value"].'%" 
  OR order_item LIKE "%'.$_POST["search"]["value"].'%" 
  OR order_value LIKE "%'.$_POST["search"]["value"].'%"
  OR order_date LIKE "%'.$_POST["search"]["value"].'%"
  OR customer_name LIKE "%'.$_POST["search"]["value"].'%")
 ';
}

if(isset($_POST["order"]))
{
 $query .= 'ORDER BY '.$columns[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].' 
 ';
}
else
{
 $query .= 'ORDER BY order_id ASC ';
}

$query1 = '';

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

$number_filter_row = mysqli_num_rows(mysqli_query($connect, $query));

$result = mysqli_query($connect, $query . $query1);

$data = array();

//$total_order = 0;


while($row = mysqli_fetch_array($result))
{
 $sub_array = array();
 $sub_array[] = $row["order_id"];
 $sub_array[] = $row["customer_id"];
 $sub_array[] = $row["order_item"];
 $sub_array[] = $row["order_value"];
 $sub_array[] = $row["order_date"];
 $sub_array[] = $row["customer_name"];
 $data[] = $sub_array;
}

function get_all_data($connect)
{
 $query = "SELECT * FROM tbl_order_id";
 $result = mysqli_query($connect, $query);
 return mysqli_num_rows($result);
}

$output = array(
 "draw"    => intval($_POST["draw"]),
 "recordsTotal"  =>  get_all_data($connect),
 "recordsFiltered" => $number_filter_row,
 "data"    => $data
);

echo json_encode($output);

?>

 

[spamjim]

Great! Now you can take it a step further by using prepared statements (or other data sanitation methods) to avoid SQL injection.