PHP select multiple tables from MYSQL

Question

I'm beginner at PHP & MYSQL I don't know where is the error at this code first MySQL database contains 6 tables I've no equation to JOIN them together and I want to show the last added value of each table

<?php
$servername = "localhost";
$username = "project";
$password = "pro2018";
$dbname = "project";


$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql_temp = "select * from temp order by id desc limit 1  ";
$sql_hum = "select * from hum order by id desc limit 1  ";
$sql_soil1 = "select * from soil1 order by id desc limit 1  ";
$sql_soil2 = "select * from soil2 order by id desc limit 1  ";
$sql_soil3 = "select * from soil3 order by id desc limit 1  ";
$sql_soil4 = "select * from soil4 order by id desc limit 1  ";
$result_temp = $conn->query($sql_temp);
$result_hum = $conn->query($sql_hum);
$result_soil1 = $conn->query($sql_soil1);
$result_soil2 = $conn->query($sql_soil2);
$result_soil3 = $conn->query($sql_soil3);
$result_soil4 = $conn->query($sql_soil4);



//if ($result_->num_rows > 0) {
// output data of each row
          $row_temp = $result_temp->fetch_assoc()) ;
          $row_hum = $result_hum->fetch_assoc()) ;
          $row_soil1 = $result_soil1->fetch_assoc()) ;
          $row_soil2 = $result_soil1->fetch_assoc()) ;
          $row_soil3 = $result_soil1->fetch_assoc()) ;
          $row_soil4 = $result_soil1->fetch_assoc()) ;
          $x = $row_temp["value"];
          $y = $row_hum["value"];
          $a = $row_soil1["value"];
          $b = $row_soil2["value"];
          $c = $row_soil3["value"];
          $d = $row_soil4["value"];

          echo " $x" . " Degree";
          echo " $y" . " %";
          echo " $a" . " %";
          echo " $b" . " %";
          echo " $c" . " %";
          echo " $d" . " %";
          header("Refresh:5");
?>

_i don't know where is the error_ What error? What is actually your question? — B001ᛦ, Feb 07 '18 at 15:08
just looking at the names of the tables suggests you need to refine your knowledge of databases a little - can you add the schema ? — Professor Abronsius, Feb 07 '18 at 15:10
I think you have to send the headers **first** and **then** output your whole html page. — Pierre François, Feb 07 '18 at 15:22
ok the actual problem i want to write code to show the values of each table then i write this code to do this task and tried to run it on browser as usual but i doesn't work the question clearly how to modify this code to do this task without error :) — Hamdy A. Hamdy, Feb 07 '18 at 15:30
When you set each of the variables $row_soil1 through to $row_soil4 you are using the same connection ie $result_soil1->fetch_assoc() so $a, $b, $c, $d will all have the same value. — Paul Campbell, Feb 07 '18 at 15:32
ok these are readings of sensors each one of the four sensors in different sites and each sensor have different readings .. and sorry for my english — Hamdy A. Hamdy, Feb 07 '18 at 15:36
Do all of the tables have the same schema? If so, you can just use `UNION` to grab all the data from multiple queries into a single recordset. Note, it is best practice to specify the fields you want, rather than using `select *`, because it prevents your program's behaviour from changing unexpectedly when you make database schema changes. This is particularly true if you're using `UNION`, because it will break badly if the resulting recordsets have different schemas. — Simba, Feb 07 '18 at 17:07

Snappawapa · Answer 1 · 2018-02-07T16:14:39.150

Welcome to Stack overflow!

First off, consider using PDO objects in PHP. PDO are the latest way to talk to databases in PHP. It is a more generalized platform that can talk to many more diffrent types of database. PHP manual here. Outline of differences between MySqli and PDO here(external).

Second, without joining, you can't return the data from more than one table at a time in one SQL SELECT statement. You will need to do multiple requests, as you have in your code example.

You could make the process more streamlined, though, by having an array of the tables you want to get data from:

$tableArray = array(
    'tableOne'=>"<name of table one>",
    'tableTwo'=>"<name of table two>",
    ...
);
$resultArray = array();

//get results from server
foreach($tableArray as $curKey=>$curTable){
    //ONLY SAFE IF $tableArray IS NEVER CHANGED
    $curStmt = "SELECT * FROM ".$curTable." order by id desc limit 1;";

    $resultArray[$curKey] = //set this to the result from the sql statement
}


//print them out
foreach($resultArray as $curKey=>$curValue){
    echo $curKey. " " . $curValue;
}

This method lets you setup the tables you want to get the info from in one place and do everything you need for you.

Make sure that your list of table names can never be edited though (just never assign it beyond the initial definition). Unfortunately, you cannot use a table name as a parameter for prepared statements, so you must build the sql statements yourself (as shown in my example), opening up the possibility for SQL injection. If you never assign the values of your table array outside of the initial definition, you should be fine though.

( I know the link explaining that tables as parameters doesn't work is specifically for java, but the issue there is SQL querying, not Java based)

I'm curious about the first part of the answer. What's wrong exactly with mysqli in terms of maintenance and security? — Álvaro González, Feb 07 '18 at 16:00
Mysqli doesn't support prepared statements or named parameters. It's also just the most recently made and up to date tool to use. Nothing really 'wrong' with mysqli, but PDO supports a lot more data sources, and key features that really help keeping your statements more secure (prepared statements, named parameters) — Snappawapa, Feb 07 '18 at 16:08
Huh? [MySQLi / Quick start guide / Prepared Statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Are you by chance confusing mysqli with the legacy mysql extension from 2005? — Álvaro González, Feb 07 '18 at 16:10
Mysqli *does* support prepared statements. It doesn't name the parameters, so PDO is better in that respect, but that's a relatively small point; it most certainly does support prepared statements, which is the main thing. — Simba, Feb 07 '18 at 16:11
I see now that the page I was looking at referred to _client side_ prepared statements. And I know mysqli/msql differences. Updated answer to be more impartial — Snappawapa, Feb 07 '18 at 16:13
Fair enough! PDO has its quirks too but its API design is not as horrible as mysqli's. — Álvaro González, Feb 08 '18 at 09:40

PHP select multiple tables from MYSQL

1 Answers1