Mysqli_Query - Return Values

mysqli_query - return values

Per the manual:

Returns FALSE on failure. For successful SELECT, SHOW, DESCRIBE or EXPLAIN queries mysqli_query() will return a mysqli_result object. For other successful queries mysqli_query() will return TRUE.

A query that runs but returns no results is still considered a "successful query", since the query did run in the database and an empty result set is a legitimate response. This means the query will still return a mysqli_result object, and if you check its row count (either via $result->num_rows in the object-oriented style or mysqli_num_rows($result) in the procedural style) it will return 0.

mysqli_query Return Value and Set as $variable

Calling mysqli_query returns a mysqli_result object - not a string. So you need to loop through the results of your query. This query will get all rows where the id of the post is the same as $post_id.

$post_id = mysqli_query($con, "SELECT id FROM posts WHERE id = '.$post_id.'");

while ($row = $post_id->fetch_assoc())
{
    foreach($row as $value) echo "<td>$value</td>";
}

You'd probably want to change the name of the variable from $post_id to something a bit more generic but you get the idea.

Check and Return Value in Mysqli_query

The below code will create an array with message and user_id.

 include("dbconnection.php");
    $email= $_GET['email'];
    $query = mysqli_query($conn, "SELECT * FROM tbl_user WHERE email='".$email."'");
    if (!$query){
       die('Error: ' . mysqli_error($con));
    }
    if(mysqli_num_rows($query) > 0){
       // assign message to response array
       $response['message']='success';
       // Get the results data
       while($row = mysqli_fetch_assoc($query)) {
          // assign user_id to response array
          $response['user_id'] = $row['user_id'];
       }
    }else{
       $sql = "INSERT INTO tbl_user(email) VALUES ('".$email."')";
       if (mysqli_query($conn, $sql)) {
          $response['message']='success';
          // assign last inserted id to response array
          $response['user_id'] = mysqli_insert_id($conn);
       }else {
          $response['message']='error';
       }
    } 
    echo json_encode($response);

Prepared statements help you secure your SQL statements from SQL Injection attacks.

MySQLi Query return value in case of Select with no matching rows

From MySQLi documentation:

Returns FALSE on failure. For successful SELECT, SHOW, DESCRIBE or EXPLAIN queries mysqli_query() will return a mysqli_result object. For other successful queries mysqli_query() will return TRUE

So basically, even if query does not return any rows, it is still a successful query. You should rather check for number of rows returned. Change your if condition to:

If ($result->num_rows) {

Sidenote:

1. Now is the right time to take correct initial steps in working with PHP-MySQL. Instead of using query function, you should rather use Prepared Statements.
2. Always use Exception handling (try-catch), to catch other errors during query execution.

Here is the equivalent code using prepared statements and exception handling:

try {

    // Prepare the query
    $stmt = "SELECT * FROM bank 
             WHERE name = ? 
               AND day = ? 
               AND time = ?";

    // Bind the parameters
    // assuming that your day and time are integer values
    $stmt->bind_param("sii", 'jack', '1', '2');

    // execute the query
    $stmt->execute();

    // Getting results:
    $result = $stmt->get_result();

    if ($result->num_rows === 0) {
        echo "0 results";
    } else {
        echo "success";

        // reading results
        while($row = $result->fetch_assoc()) {
            $name = $row['name'];
            $day = $row['day'];
            $time = $row['time'];
        }
    }

} catch (Exception $e) {

     // your code to handle in case of exceptions here
     // generally you log error details, 
     //and send out specific error message alerts
}

having problem with mysqli_query, return NULL but the query works in phpmyadmin

You are using the deprecated mysql_fetch function.Use the new one

<?php
    // Create connection
    $con=mysqli_connect("localhost","super","super","testdb");

    // Check connection
    if (mysqli_connect_errno())
    {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

    $action = "login";
    $username = "Peperoncino";
    $response = array();

    if ($action == "login")
    {
        $query = "SELECT psw AS pswrd,id FROM Activities WHERE nome = 'Peperoncino' LIMIT 1";

        if ($result = mysqli_query($con, $query))
        {
            $values = mysqli_fetch_assoc($result);
            $password = $values['pswrd'];
            $response["password"] = $password;
            $response["message"] = "Get information from db";
        }
        else
        {
            echo  mysqli_error($conn);  
        }

        echo json_encode($response);
    }

    // Close connections
    mysqli_close($con);
?>

Checking if mysqli_query returned any values?

Use mysqli_num_rows to check if any rows were returned or not.

Mysqli_query returns false and Mysqli_error returns NULL?

$query = "INSERT INTO appointmentinformation (ClientID, Length, Date, Month, Year, Time, Price) VALUES ('$clientid', '$length', '$date', '$month', '$year', '$time', '$price')";
    $result = mysqli_query($connection, $query);
    if ($result) {
        header("Location:UserCP.php");
    } else {
        echo ("Could not insert data : " . mysqli_error($result) . " " . mysqli_errno());
    }

The result returned from mysqli_query is null. This sends you down the else branch of the code. Then you coded mysqli_error($result) which equals mysqli_error(null).

The documentation I have read says to initialize a variable with a "link" to the query. You did this with:

 $connection = mysqli_connect("$servername", "$username", "$password", "$dbname") or die("Could not connect to the database");

You now want to code it as mysqli_error($connection) and mysqli_errno($connection).

An additional suggestion. Add this code right after your mysqli_connect statement.

if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

Mysqli_query returning 0 rows (character problem?)

Single quotes in PHP mean the contents of the string are taken literally. So it will not insert the value of the $user and $password variables - it will literally be checking for the username "$user".

You should enclose the entire query string in double quotes, then it should work.

mysqli_query() always return true

$r will only be false if there was SQL error. Otherwise it will always return an object, even if no rows are returned by your SELECT statement.

Use mysqli_num_rows() to count how many rows are returned. Zero means no one is using that email address.

if(mysqli_num_rows($r))
    echo "Email address exists!";
else
    echo "sss"; 

mysqli-query returns true on update, but no rows affected

Okay, so I found the problem.

I was starting a transaction within a method, and I never comitted.

My apologies for wasting your time, you could not have figured out the problem by looking at the code I provided. I was sure the issue was within that query method (but I was wrong).

I'll vote up each and everyone of you that tried to help out.

---

Related Topics