Join MySQL Tables with PHP
147   2  

Join MySQL Tables with PHP

  21 Nov 2017
  Coding Category
Join MySQL Tables with PHP

I try to avoid join tables with making optimized tables when start a project, but sometimes need to join them like for exporting to XLS for special report! Suppose that we have two tables with one connected similar column. I try get some parameters and use them to make a XLS (special for Excel) from two tables. This is a simple example for this purpose and force to download it by user:


header('Content-type: application/octet-stream');

header('Content-Type: application/vnd.ms-excel; charset=utf-8'); //make sure to right extenstion for output

header('Content-Disposition: attachment; filename=exported.xls'); //name downloadable file

if(isset($_POST["download"])) {

    //get some parameters from a form, you can get it by POST them in URL

    $t1 = $_SESSION['t1'];

    $t2 = $_SESSION['t2'];

    $p1 = $_SESSION['p1'];

    $p2 = $_SESSION['p2'];

    $data = fopen('php://output', 'w'); //make temp writable file

    fputs($data, $bom =( chr(0xEF) . chr(0xBB) . chr(0xBF) )); //writes file header for correct encoding

    fputcsv($data,array('First Name', 'Family Name', 'International Code', 'Father Name', 'Carpet Count', 'Price', 'Is Special Transportation', 'Date and Time')); //just write header of Excel Table

    $rows = mysqli_query($db,"SELECT form1.first_name, form1.family_name, form1.international_code, form1.father_name, form2.carpet_count, form2.price, form2.is_special, form2.cDate FROM form1 JOIN form2 ON form1.id = form2.fid WHERE form2.cDate >= '$t1' AND form2.cDate <= '$t2' AND (form2.price * form2.carpet_count) >= '$p1' AND (form2.price * form2.carpet_count) <= '$p2' AND form2.is_special = '0'"); //select custom columns. JOIN means INNER JOIN. fid in table 2 plays FKey for id in table 1

    while($row = mysqli_fetch_assoc($rows)){

        fputcsv($data, $row); //write rows in file

    }

    exit();

}

Author: Ismael Azaran

Do you like it? Share it with your friends.

Categories

Tags