Problem connecting to MtSQL db using a simple php script

[larghifra]

Hi everyone, i'm a new user and i'm making some test on 000webhost to host a database for a simple application.

I created a MySQL database that contains a simple table you can see here:

I made a simple android app which sends a Json request to this simple php file loaded into "public_html" folder in my 000webhost page.

 

<?php

    $conn = mysqli_connect("localhost", "myuser", "mypassword", "mydatabase");

    

    $name = $_POST["name"];

    $email = $_POST["email"];

    $password = $_POST["password"];

 

    $statement = mysqli_prepare($conn, "INSERT INTO user (name, email, password) VALUES (?, ?, ?)");

    mysqli_stmt_bind_param($statement, "siss", $name, $email, $password);

    mysqli_stmt_execute($statement);

    

    $response = array();

    $response["success"] = true;  

    

    echo json_encode($response);

?>

 

The application works correctly and receives the "success" response back equals to "true", so the php script runs correctly too, but in the database it doesn't happen anything. 

Maybe i made some error in the php script, i don't know, please help me!! 

 

 

[cyberRobot]

After the database connection is made, you'll need to select the database. More information can be found here:

http://php.net/manual/en/mysqli.select-db.php

[Barand]

@cyberRobot - the database is included in the connect parameters

 

@lrghifra - a couple of problems

• When you bind the the parameters (3 of them) you are defining 4 types ("siss")
• You are defining the response as empty array and not the query results

[larghifra]

@Barand so how i should modify the script? Removing 'siss' parameter? How can i put query result into array?

[mac_gyver]

your query is failing, because you are listing the wrong number of inputs in the type string for the bind_param() statement. this would either be throwing a (php?) error at the bind_param() statement or a mysql error at the execute() statement. you need to ALWAYS detect and handle statement errors.

 

the easiest way of detecting and handling database statement errors is to use exceptions. to enable exceptions for the php msyqli extension, add the following two lines before you make the database connection -  

$driver = new mysqli_driver(); // note the $driver variable name used here is separate from and not related to any variable your code may be using
$driver->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT; // MYSQLI_REPORT_ALL <-- w/index checking; w/o index checking --> MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT;

then, php will catch the exception when there is an error and if you have php's error_reporting set to E_ALL and display_errors set to ON, php will display the actual cause of the error and some back-trace information.

[cyberRobot]

@cyberRobot - the database is included in the connect parameters

 

Yep, sorry about that. 

[larghifra]

 

your query is failing, because you are listing the wrong number of inputs in the type string for the bind_param() statement. this would either be throwing a (php?) error at the bind_param() statement or a mysql error at the execute() statement. you need to ALWAYS detect and handle statement errors.

 

the easiest way of detecting and handling database statement errors is to use exceptions. to enable exceptions for the php msyqli extension, add the following two lines before you make the database connection -  

$driver = new mysqli_driver(); // note the $driver variable name used here is separate from and not related to any variable your code may be using
$driver->report_mode = MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT; // MYSQLI_REPORT_ALL <-- w/index checking; w/o index checking --> MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT;

then, php will catch the exception when there is an error and if you have php's error_reporting set to E_ALL and display_errors set to ON, php will display the actual cause of the error and some back-trace information.

 

 

Thank you for your answer! Sorry can you explain me where i can set the error reporting and display errors?

Edited November 29, 2016 by larghifra

[cyberRobot]

Thank you for your answer! Sorry can you explain me where i can set the error reporting and display errors?

 

Did you change the call to mysqli_stmt_bind_param() as suggested by Barand? If not, try changing this

mysqli_stmt_bind_param($statement, "siss", $name, $email, $password);

To this

mysqli_stmt_bind_param($statement, "sss", $name, $email, $password);

The other thing that Barand mentioned refers to this portion

$response = array();
$response["success"] = true;  
 
echo json_encode($response);

No matter what happens with the query, you will get "true" since it's hard coded. Instead, you need to test the return value of the call to mysqli_stmt_execute(). More information can be found here:

http://php.net/manual/en/mysqli-stmt.execute.php