sql query does not return the first row

Question

Whenever i use: while($x = mysqli_fetch_array($y)) and then echo $x, it returns all except the first row.

The tables:

forum.users

users.user_id
users.username
users.password
users.image

forum.forums

forums.forum_id
forums.user_id
forums.name

Here's my code:

<?php
session_start();
$con = new mysqli("localhost", "root", "", "forum");
if(!isset($_SESSION["online"])==1)  {
    $_SESSION["offline"] = "true";
}
$username = $_SESSION["online"];
$u_query = "SELECT * FROM forum.users WHERE users.username = '".$username."'";
$u_result = $con -> query($u_query);
$u_row = mysqli_fetch_array($u_result);
if(isset($_POST["logout"])) {
    session_unset();
    session_destroy();
    header("Location: login.php");
}   
$f_search = "SELECT * FROM forum.forums WHERE user_id = '".$u_row['user_id']."'";
$f_result = $con -> query($f_search);
$f_row = mysqli_fetch_array($f_result);
?>
<html>
<head>
<title>Home</title>
<charset = "UTF-8">
<link href="style.css" rel="stylesheet" type="text/css">
</head> 
<body>

<div id="header">
    <div id="user">
        <img src="<?php echo $u_row['image']; ?>" id="user-image">
        <?php echo $u_row["username"]; ?>
        <form method="post">
            <input type="submit" name="logout" value="sign out">
        </form> 
    </div>
</div>

<?php if(mysqli_num_rows($f_result)>= 0) { ?>
<div>
    <div>Your forums:</div><table><tr>
    <?php while($f_row = mysqli_fetch_array($f_result)) { ?>
    <td><a href="forum.php?forumid=<?php echo $f_row['forum_id']; ?>"><?php echo $f_row["name"]; ?></a></td>
    <td></td>
    <?php } ?>
    </tr></table>
</div>
<?php } ?>
</body>
</html>

Why does it not return the first row?

Sidenote: `$_SESSION["offline"] = "true"` that might fail you if you're looking to check as boolean later on. — Funk Forty Niner, Apr 22 '16 at 14:33
[Little Bobby](http://bobby-tables.com/) says [your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Apr 22 '16 at 15:46

score 4 · Accepted Answer · answered Apr 22 '16 at 14:34

Calling mysqli_fetch_array fetches the row and advances the cursor. So your first

$f_row = mysqli_fetch_array($f_result);

has the first row. Then

while($f_row = mysqli_fetch_array($f_result)) {

advances to the second row, and loops through all the other results.

So either get rid of $f_row = mysqli_fetch_array($f_result); outside of the while or output the contents before the while. It doesn't look like you are using that array so I'd just remove it.

Additionally this:

$username = $_SESSION["online"];
$u_query = "SELECT * FROM forum.users WHERE users.username = '".$username."'";

is open to SQL injections. You should use prepared statements and parameterize the query. http://php.net/manual/en/mysqli.quickstart.prepared-statements.php

Thanks for the answer, and also for the note at the end. Don't think i'll need to do it for this asignment, since it's only for high school and my teacher did not understand why i got the first error either. If I have time i migth add it in, or else just keep it in mind next time ;) — Eirik, Apr 22 '16 at 18:25

score 1 · Answer 2 · answered Apr 22 '16 at 14:34

Remove $f_row = mysqli_fetch_array($f_result);

By calling that on its own, early in the script, you're advancing the pointer +1. Then in your while($f_row = mysqli_fetch_array($f_result)) you're actually starting at the second row.

$f_search = "SELECT * FROM forum.forums WHERE user_id = '".$u_row['user_id']."'";
$f_result = $con -> query($f_search);
$f_row = mysqli_fetch_array($f_result); // <---- remove this line

sql query does not return the first row

2 Answers2