mysqli_query database selection error

Question

Im trying to select text from a database but only the text that was posted by certain usernames. Basically I need someone to look at this PHP and MySQL code and tell me what they see is wrong with it. I hope I have given enough info. Also, I get this erro: Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, string given in... Thanks! Here is the code:

$followed = mysqli_query($con,"SELECT followed FROM follows WHERE follower = '$username'");

while($row = mysqli_fetch_array($followed)){

    echo $row['followed']."<br>";
    $followed = $row['followed'];

    $random = mysqli_query($con,"SELECT text FROM post WHERE user = '$followed'");

    while($row = mysqli_fetch_array($random)){
        echo "<ul><li id = 'stream-post'>";
        echo $row['text'];
        echo "</li></ul>";
        $user = $row['user'];
    }
}

Google the error message and let us know what you think it might be. — Kermit
– Kermit, Commented Sep 28, 2014 at 0:06

spencer7593 · Accepted Answer · 2014-09-28 00:52:12Z

The problem in the code is that $followed is the variable holding the SQL result. And after the first fetch, that gets overwritten with a string value. The next time through the loop, $followed is no longer a reference to the result set returned by the query.

There's also attempt to retrieve key 'user' from the array $row, and the key does not exist in the array.

Also, your code is vulnerable to SQL Injection, and there is no checking whether the return from a query is successful or not. We'd prefer to see prepared statements with bind placeholders, but at a minimum, you should be calling the mysqli_real_escape_string function on "unsafe" values, and include the return from the function in the SQL text.

Here's an example of the pattern I prefer to follow

# set the SQL text to a variable
$sql = "SELECT followed FROM follows WHERE follower = '" 
     . mysqli_real_escape_string($con, $username) . "' ORDER BY 1"; 
# for debugging
#echo "SQL=" . $sql; 

# execute the query
$sth = mysqli_query($con, $sql);

# check if query was successful, and handle somehow if not
if (!$sth) {
    die mysqli_error($con);
}

while ($row = mysqli_fetch_array($sth)) {
    $followed = $row['followed'];
    echo htmlspecialchars($followed) ."<br>";

    # set SQL text 
    $sql2 = "SELECT text FROM post WHERE user = '"
          . mysqli_real_escape_string($con, $followed) . "' ORDER BY 1";
    # for debugging
    #echo "SQL=" . $sql2;

    # execute the query
    $sth2 = mysqli_query($con, $sql2);

    # check if query execution was successful, handle if not
    if (!$sth2) {
       die mysqli_error($con);
    }

    while ($row2 = mysqli_fetch_array($sth2)) {
        $text = $row2['text'];

        echo "<ul><li id = 'stream-post'>" . htmlspecialchars($text) . "</li></ul>";
    }
}

Axel Amthor · Accepted Answer · 2014-09-28 00:06:38Z

0

Just as a hint: the second loop assigns the result to row which already holds the row from the first query. Use a different variable name:

    ...
    while($row = mysqli_fetch_array($followed)){

    echo $row['followed']."<br>";
    $followed = $row['followed'];

    $random = mysqli_query($con,"SELECT text FROM post WHERE user = '$followed'");

    while($subrow = mysqli_fetch_array($random)){
        echo "<ul><li id = 'stream-post'>";
        echo $subrow['text'];
        ....

second: column useris not part of either SELECT (... $user = $row['user'];)

I assume, it's the second query:

     "SELECT user, text FROM post WHERE user = '$followed'"

answered Sep 28, 2014 at 0:06

Axel Amthor

11.1k1 gold badge28 silver badges45 bronze badges

4 Comments

spencer7593 Over a year ago

It's not necessarily wrong to reuse $row. OP already grabbed the column values returned into the array by the fetch. A bigger issue is attempting to retrieve array key 'user' when that key is not in the array. The example code posted here is subject to SQL Injection, just like the OP code is.

Axel Amthor Over a year ago

@spencer7593 regarding the two row's: When the code stays as it is, you might be right. If it will be altered / evolved, these kind of things are reason for some night shift with debugging ...

spencer7593 Over a year ago

The real problem is the reuse of $followed. The first time through the loop, that gets overwritten. The second fetch is trying to fetch from a string, rather than the resultset. I prefer to use a variable name like $sth (statement handle) or $stmt for the resultset. (A throwback to my earlier Perl DBI days).

Max Over a year ago

Thanks Axel, the duplicated $follows was the problem.

Collectives™ on Stack Overflow

mysqli_query database selection error

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related