I have database consist of different tables, the table I care about are, user and module, after the user login the second query must return the max(id) from the module user. What I am trying to do is: I want to get the max(id) of the same user who is logged in, the user are stored in module table in create_user column, is possible to do that and how should the where condition be?
The table structure:
user table: (id int pri.key, username varchar, password varchar).
module table: (id int pri.key, name varchar, creat_time date, create_user int)
When inserting new module the user id is stored in create_user.
The code:
<?php
$json["status"] = "running";
$details[] = "started get_tables ";
include_once('confi.php');
$request_body = file_get_contents('php://input');
$statement = "INSERT INTO tbl_archive (content) VALUES ('$request_body' );";
mysql_query($statement);
$input = json_decode($request_body, true);
$user = $input["user"];
$password = $user["password"];
$mySQLstring = "SELECT username, password, id FROM tbl_user where username = '$username' ;";
$json["statement"][] = $mySQLstring;
$qur = mysql_query($mySQLstring);
if ($qur){
$max = mysql_fetch_assoc($qur);
}
if ($max){
$json["max"] = $max;
if ($max["password"] == $password){
$json["username"] = $username;
$json["id"] = $max["id"];
$json["status"] = "ok";
$tables = array("class", "class_user", "module", "module_class", "module_user", "rating", "student", "student_class");
foreach($tables as $table){
$id= $max["id"];
if ( $table == 'module' ){
$statement ='SELECT create_user, MAX(id) AS id FROM tbl_'.$table;
$statement .= ' WHERE create_user =$id' ;
$qur = mysql_query($statement);
if ($qur){
while($r = mysql_fetch_array($qur, MYSQL_ASSOC)){
$result[$table][] = $r;
}
}
}
}
$json = array("status" => "ok", "data" => $result);
}
}
}
@mysql_close($conn);
header('Content-type: application/json');
echo json_encode($json);
?>
