select from the whole database after applying where condtion on one of the table

Question

I have a database consisting of 10 table listed in the $tables,

One of the table called tbl_module contain a column called delete_time, the following statment selects all data from these tables :

$statement = "SELECT * FROM tbl_".$table;

what i need to do is to add a condition to select all data from these table WHERE the value of delete_time in tbl_module IS NULL the php code is:

<?php
// alle relevanten Tabellen abfragen und als json zurückgeben.


$json["status"] = "running";
$details[] = "started get_tables ";
// Include confi.php
include_once('confi.php');
//var_dump($_POST);
$request_body = file_get_contents('php://input');
// first store the given set of data to keep it for future analysis
$statement = "INSERT INTO tbl_archive (content) VALUES ('$request_body' );";
mysql_query($statement);
$input = json_decode($request_body, true);
// now check if valid user
$user = $input["user"];

$username = $user["username"];
$password = $user["password"];

if($password and $username){


    $mySQLstring = "SELECT username, password, id FROM tbl_user where username = '$username' ;";
    $json["statement"][] = $mySQLstring;
    $qur = mysql_query($mySQLstring);
    //var_dump ( $qur );
    if ($qur){
        $res = mysql_fetch_assoc($qur);
    }
    if ($res){
        $json["res"] = $res;
        if ($res["password"] == $password){
            $json["username"] = $username;
            $json["id"] = $res["id"];
            $json["status"] = "ok";
            $tables = array("class", "class_user", "module", "module_class", "module_user", "rating", "student", "student_class");
            //$tables = array("class");
            foreach($tables as $table){
                $statement = "SELECT * FROM tbl_".$table;

                $qur = mysql_query($statement);
                if ($qur){
                    while($r = mysql_fetch_array($qur, MYSQL_ASSOC)){
                        //var_dump($r);
                        //echo (json_encode($r));
                        $result[$table][] = $r;
                    }
                }
            }
            $json = array("status" => "ok", "data" => $result);
        }
    }
}



@mysql_close($conn);

/* Output header */
header('Content-type: application/json');
echo json_encode($json);
?>

You should join the tables instead of retrieving them seperately. — KiwiJuicer, Dec 09 '15 at 10:24

score 0 · Accepted Answer · edited May 23 '17 at 12:07

That is fairly simple all you need to do is alter the $statement if the $table = module like this

        $tables = array("class", "class_user", "module", "module_class", 
                        "module_user", "rating", "student", "student_class");

        foreach($tables as $table){
            $statement = "SELECT * FROM tbl_".$table;

            if ( $table == 'module' ) {
                $statement .= ' WHERE delete_time IS NOT NULL ';

                // and to add not equal to 0000-00-00 00:00:00
                $statement .= " AND delete_time != '0000-00-00 00:00:00' ";
            }


            $qur = mysql_query($statement);
            if ($qur){
                while($r = mysql_fetch_array($qur, MYSQL_ASSOC)){
                    //var_dump($r);
                    //echo (json_encode($r));
                    $result[$table][] = $r;
                }
            }
        }

NOTE1:

Please dont use the mysql_ database extensions, it is deprecated (gone for ever in PHP7) Especially if you are just learning PHP, spend your energies learning the PDO or mysqli_ database extensions, and here is some help to decide which to use

NOTE2:

You have some other major problems in this code, mainly that you are using data straight from the $_POST array without doing any validation or sanity checks on it, and putting it directly into a query. This you should fix, but using PDO and named or ? positional parameters would go a long way to mitigating this problem.

Then the normal thing to do is accept the answer that helped you, for doing that you also get reputation points — RiggsFolly, Dec 09 '15 at 13:20
what if i want the delete_time in where to be not null or not 0000-00-00 00:00:00 is it posible to do it — george abousaydeh, Dec 10 '15 at 21:01
hey riggs do you have any idea how to use the data obtained from the same php file in another js file — george abousaydeh, Dec 22 '15 at 21:13

select from the whole database after applying where condtion on one of the table

1 Answers1