MySQLi Query Returns Only One Row

MySQLi query returns only one row

You need to loop through the results (as TheSmose mentioned)

while ($request_list_row = $request_list_result->fetch_array()) {
    echo $request_list['user_fullname'];
}

AND you need to send the resulting array $request_list to the template rather than $request_list_row.

Change this

'request_list' => $request_list_row //right here

to this

'request_list' => $request_list //right here

If you want more than just user_fullname in your template (and you don't have PHP >= 5.3 required for mysqli_result::fetch_all), then you will need to build up your own array inside the loop.

I don't know what your template code expects, but you could try

while ($request_list_row = $request_list_result->fetch_array()) {
    echo $request_list[] = $request_list_row;
}

Mysqli query is only returning one row (last one)

My initial method of approaching this was very similar to your method and like your it failed. A little research suggested that this method would not work as we both found as each item in the $comments string really needed it's own placeholder and associated type string which lead me to this:

/* output */
$votes=array();

/* original string of IDS */
$comments='1,3,4,5,6,7,11,12,13';

/* create an array from IDS */
$array=explode(',',$comments);

/* create placeholders for each ID */
$placeholders=implode( ',', array_fill( 0, count( $array ), '?' ) );

/* create a type string for each - all going to be `i` */
$types=implode( '', array_fill( 0, count( $array ), 'i' ) );

/* create the sql statement */
$sql=sprintf( 'select `id` from `comment_votes` where `vote` > 0 and `item_id` in ( %s );', $placeholders );

/* create the prepared statement */
$stmt = $db->prepare( $sql );

/* Add the type strings to the beginning of the array */
array_unshift( $array, $types );

if( $stmt ){

    /* bind types and placeholders - 2nd arg passed by reference */
    call_user_func_array( array( $stmt, 'bind_param'), &$array );

    /* execute the query */
    $result=$stmt->execute();

    /* success */
    if( $result ){

        $stmt->store_result();
        $stmt->bind_result( $id );
        $rows=$stmt->num_rows;

        printf( 'rows found: %d<br />',$rows );

        /* add found IDs to output */
        while( $stmt->fetch() ) {
            $votes[]=$id;
        }
        /* tidy up */
        $stmt->free_result();
        $stmt->close();


        /* do something with output */
        printf( '<pre>%s</pre>', print_r( $votes, true ) );

    } else{
        exit('Error: No results');
    }

} else exit('Error: Prepared statement failed');

MySQLi only showing one result

Try a while loop:

while($row = $sql->fetch_row())
{
   var_dump($row);
}

Because fetch_row(), fetch_array(), fetch_assoc() will all return one row every singe time it's being called untill it is 'out of rows'.

Single result from database using mysqli

When just a single result is needed, then no loop should be used. Just fetch the row right away.

• In case you need to fetch the entire row into associative array:

    $row = $result->fetch_assoc();
  

• in case you need just a single value

    $row = $result->fetch_row();
    $value = $row[0] ?? false;
  

The last example will return the first column from the first returned row, or false if no row was returned. It can be also shortened to a single line,

$value = $result->fetch_row()[0] ?? false;

Below are complete examples for different use cases

Variables to be used in the query

When variables are to be used in the query, then a prepared statement must be used. For example, given we have a variable $id:

$query = "SELECT ssfullname, ssemail FROM userss WHERE id=?";
$stmt = $conn->prepare($query);
$stmt->bind_param("s", $id);
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc();

// in case you need just a single value
$query = "SELECT count(*) FROM userss WHERE id=?";
$stmt = $conn->prepare($query);
$stmt->bind_param("s", $id);
$stmt->execute();
$result = $stmt->get_result();
$value = $result->fetch_row()[0] ?? false;

The detailed explanation of the above process can be found in my article. As to why you must follow it is explained in this famous question

No variables in the query

In your case, where no variables to be used in the query, you can use the query() method:

$query = "SELECT ssfullname, ssemail FROM userss ORDER BY ssid";
$result = $conn->query($query);
// in case you need an array
$row = $result->fetch_assoc();
// OR in case you need just a single value
$value = $result->fetch_row()[0] ?? false;

By the way, although using raw API while learning is okay, consider using some database abstraction library or at least a helper function in the future:

// using a helper function
$sql = "SELECT email FROM users WHERE id=?";
$value = prepared_select($conn, $sql, [$id])->fetch_row[0] ?? false;

// using a database helper class
$email = $db->getCol("SELECT email FROM users WHERE id=?", [$id]);

As you can see, although a helper function can reduce the amount of code, a class' method could encapsulate all the repetitive code inside, making you to write only meaningful parts - the query, the input parameters and the desired result format (in the form of the method's name).

PHP mysqli query returning result 1 on joins

After hours of research, I have come to an working example and it works just fine.
Someone else might face the same problems in the future and make mistakes, so I am posting an answer to the problem here:

---

Problems:

1. Having same column names in two joined tables

The first problem was the the table interactions had the same column name id as that of the table box. Since I used SELECT * FROM box along an inner join with interactions, it resulted in the return of the results based on the second id which was of interactions rather than box.

2. Not storing the results before showing record count

Secondly, the problem was the results were not being stored as in http://php.net/manual/en/mysqli-stmt.num-rows.php, which was a silly mistake.

---

Solutions:

• Addressing the column repition

So, firstly I decided to change the column name of the table interactions for id, and I changed it to inter_id to avoid further conflicts. Though not a very smart step, it will avoid such silly mistakes in the future. Then as pointed out by the previous answer, I had to specify the column names of the results I wanted to output rather than using a SELECT *, so I changed it to

              SELECT box.id, box.time_created FROM box 
              INNER JOIN interactions 
              ON box.u_id=interactions.actorid 
              WHERE interactions.actorid=? 
              AND interactions.type IN ('1','5') 
              ORDER BY box.time_created";

That pretty much solved the first problem.

• Counting the number of results

The second one was just about adding the $stmt->store_result(); to the code and it worked flawlessly just before I did a echo $stmt->num_rows; and it showed the results perfectly.

Thank you, again.

mysqli query only returning first row

Use this code:

$rows = array();
if ($type == 'assoc') {
    while($row = $result->fetch_assoc()) {
      $rows[] = $row;
    }
} else {    
    while($row = $result->fetch_object()) {
      $rows[] = $row;
    }   
}
return $rows;

You are using the return inside the while and return terminates the while loop after first iteration that's why you are getting only one row.

MySQLi - Always Returning 1 Row

It has been my experience that MySQL can return some funky values for row_count

maybe

 result = $db->query($mysqli, "SELECT COUNT(*) AS cnt FROM users WHERE username = '$user'")
 while($row = $result->fetch_assoc()){
    echo $row['username'] . '<br />';
} 

or try

/* Select queries return a resultset */
if ($result = $mysqli->query("SELECT Name FROM City LIMIT 10")) {
    printf("Select returned %d rows.\n", $result->num_rows);

    /* free result set */
    $result->close();
}

http://php.net/manual/en/mysqli.query.php

mysqli DESCRIBE returns only one row

Ah, found out the problem myself, looks like my fetch() function returned only first row. Changed it a little and it works:

public function fetch($all = false, $query_id = NULL){
    if($query_id)
        $this->query_id = $query_id;
    if($this->query_id){
        if($all){
            while($row = mysqli_fetch_assoc($this->query_id)){
                $data[] = $row;
            }
        }else{
            $data = mysqli_fetch_assoc($this->query_id);
        }
    }else{
        psyo::error("Error while fetching results (query id: {$this->query_id}).");
    }
    $this->free();
    return $data;
}

Damn those heedless mistakes...

mysqli query returns the column name as one row in php

I don't know what do you mean but thats how i query mysqli

$query = mysqli_query($db, "SELECT * FROM users WHERE name='$name' AND password='$password'");

if($query && mysqli_affected_rows($db) >= 1) { //If query was successfull and it has 1 or more than 1 result 
echo 'Query Success!';
//and this is how i fetch rows
while($rows = mysqli_fetch_assoc($query)) {
   echo $rows['name'] . '<br />' ;
}

} else {
echo 'Query Failed!';
}

i think thats what you mean

EDIT:

<?php require ("database_connect.php");?>
<!DOCTYPE html>
<html>
<body>  
    <form method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"])?>">
    Name : <input type="text" name="name"><br/>
    Password : <input type = "text" name="password"><br/>
    <input type="submit" name="login" value="Log In">   
    </form>

    <?php
    $name = null ;
    $password= null ;
        if($_SERVER["REQUEST_METHOD"]=="POST" and isset($_POST["login"])){
            $name = mysqli_real_escape_string($conn, $_POST["name"]); //I updated that because your variables are not safe
            $password = mysqli_real_escape_string($conn, $_POST["password"]);
        }//if ends here

        //testInput function
        function testInput($data){
            $data = trim($data);
            $data = stripslashes($data);
            $data = htmlspecialchars($data);
        }//testInput ends here


        if(isset($_POST["login"]) && isset($_POST["name"]) && isset($_POST["password"]) && !empty($_POST["name"]) && !empty($_POST["password"])){

        if($result = mysqli_query($conn,"SELECT * FROM users WHERE name='{$name}' and password='{$password}'")){            

            print "rows are ".mysqli_num_rows($result)"<br>";//number of rows

            if($result && mysqli_affected_rows($conn) >= 1){//If query was successfull and it has 1 or more than 1 result
            echo "you are logged in<br>";
                while ($row = mysqli_fetch_assoc($result)){
                    echo "Name ".$row["name"]."-Password ".$row["password"];
                }//while loop ends here  
            }//if ends here


            /* free result set */
            mysqli_free_result($result);
        }       
        else{
            print "Wrong Credentials "."<br>";
            die(mysqli_error($conn));
        }
        }       
        //close connection
        mysqli_close($conn);
    ?>
</body>
</html>

---

Related Topics