Kasun Siyambalapitiya Kasun Siyambalapitiya - 15 days ago 4
JSON Question

Why it is not querying the database

I wrote the following code to retrieve data from the database

<?php
$host = 'localhost';
$username = 'root';
$password = '';
$database = 'users';

$con = mysqli_connect($host, $username, $password, $database);

// catch the values that are passed by the POST method
$courseId=$_POST["courseId"];
$hall=$_POST["hall"];
$day=$_POST["day"];
$year=$_POST["year"];
$time=$_POST["time"];

$statement=mysqli_prepare($con,"SELECT * FROM Lectures WHERE day=? AND timeInterval=? AND courseId=? AND hall=? AND year=? ");

mysqli_stmt_bind_param($statement,"sssss",$courseId,$hall,$day,$year,$time);
mysqli_stmt_execute($statement);
// have to collect the results that are coming after the query is being executed
mysqli_stmt_store_result($statement); //storing the results in a buffer for temporary
// now we need to bind the results
mysqli_stmt_bind_result($statement,$day,$time,$courseId,$hall,$year,$noStudents,$courseRefName,$courseRefTelNo,$lecturer);

// to send the data via JSON string
$lectureDetails=array();
mysqli_stmt_fetch($statement);

$lectureDetails["day"]=$day;
$lectureDetails["time"]=$time;
$lectureDetails["courseId"]=$courseId;
$lectureDetails["hall"]=$hall;
$lectureDetails["year"]=$year;
$lectureDetails["noStudents"]=$noStudents;
$lectureDetails["courseRefName"]=$courseRefName;
$lectureDetails["courseRefTelNo"]=$courseRefTelNo;
$lectureDetails["lecturer"]=$lecturer;

// data are stored in the array. now we need to send them via a JSON string
echo json_encode($lectureDetails);
// the java file that calls this method will receive echo
//The PHP json_encode function returns a string, containing the JSON equivalent of the values passed to it
//so in here $lectureDetails array is passed throught throughe JSON String.
mysqli_stmt_close($statement); //closing the connection
mysqli_close($con); //closing the sql connection

?>


according to the working fetching file as below(this returns values as expected)

<?php
//database connection
$host = 'localhost';
$user1 = 'root';
$password1 = '';
$database = 'users';

$con = mysqli_connect($host, $user1, $password1, $database);


//checking the validity of the database
// if(!$con){
//die("connection Failed" . mysqli_connect_error());}
//echo "connected Successfully";

$userName=$_POST["userName"];
$password=$_POST["password"];


$statement=mysqli_prepare($con,"SELECT * FROM usersLogged WHERE userName=? AND password=?");
//to prevent from sql injection
mysqli_stmt_bind_param($statement,"ss",$userName,$password);
mysqli_stmt_execute($statement);

//after executing the command we will get all the results that were selected
mysqli_stmt_store_result($statement); //storing the results in a buffer for temporary
//we need to bind the results
mysqli_stmt_bind_result($statement, $userId, $userName, $firstName, $lastName, $password, $position,$birthDate,$qualification,$email);


//now we need to store them into an array inoder to send them via a JSON

$user=array();


mysqli_stmt_fetch($statement);
//fetch the result from a prepared statement into the variables bound by mysqli_stmt_bind_result.
//Data are transferred unbuffered without calling mysqli_stmt_store_result() which can decrease performance (but reduces memory cost).

//storing the values which are fetched from the database are kept in the array(#user)
$user["userName"]=$userName;
$user["firstName"]=$firstName;
$user["lastName"]=$lastName;
$user["password"]=$password;
$user["position"]=$position;
$user["birthDate"]=$birthDate;
$user["qualification"]=$qualification;
$user["email"]=$email;

//now we need to pass the content to the phone,we send the array in a json

echo json_encode($user); // the java file that calls this method will receive echo
//The PHP json_encode function returns a string, containing the JSON equivalent of the values passed to it
//so in here $user array is passed throught the JSON String.
mysqli_stmt_close($statement);
mysqli_close($con);







?>


but the above
php
file is not fetching data from the
mysql
database.
I have created a
Lectures
table at the current
users
database and
day
time
courseId
hall
year
courseRefName
and
lecuturer
are of String data type ,
noStudents
and
courseRefTelNo
are of integer datatype.
Is there any mistake in the
mysqli_prepare
or in the way this is encoded to JSON, because Iam not getting any return from this (above)
php
file to my
java
in
android
application

Answer

I found the problem, it was the mismatch between the data sent via POST and in the database had caused the problem. I have sent time in form of 08.00-10.00 and in database they were as 08-10 in form of 5 digits. So if anyone encounter problems like these be first check the data in the database and the values you passed through POST method