This While loop is Driving me Crazy

I want to simply display something using a while loop with php, but it is not working.

Here is the code -

<?php 

mysqli_select_db($connect,"users");

$select_title = "select title, message from messages where user = '$u' LIMIT 4 ";

$querying = mysqli_query($connect,$select_title) or die ('Whops! Something went wrong.');

$line = mysqli_fetch_assoc($querying);

    //$title = mysqli_real_es开发者_如何学Pythoncape_string($connect,trim($line['title']));

while(($rows = mysqli_fetch_assoc($querying)))
{

    echo $rows['title'];
}

?>

Now I have two titles, but only one is being displayed. Why so?

You have fetched one row here:

$line = mysqli_fetch_assoc($querying);

The cursor will be moved to the next row, hence

while(($rows = mysqli_fetch_assoc($querying)))
{

    echo $rows['title'];
}

will display the second row only.

The best solution is simply comment that line:

 //$line = mysqli_fetch_assoc($querying);

Delete this line:

$line = mysqli_fetch_assoc($querying);

Comment this line will solve your problem

//$line = mysqli_fetch_assoc($querying);

... because you fetch the first title with the line $line = mysqli_fetch_assoc($querying); but never echo it and then start a loop to go through the rest of the titles.

Delete/uncomment the line and you should be fine.

you already fetch one record with

$line = mysqli_fetch_assoc($querying);

so there is only one left in while

Debug

My MySQL is a bit rusty(Long time ago since I used SQL => Moved to NoSQL camp...), but this is what I would do:

• First I would test if query returns correct result using phpmyadmin console or just mysql console from commandline.

But I guess the query is correct because a lot of people are saying that

$line = mysqli_fetch_assoc($querying);

is already fetching a row. But still you could use it for debugging purposes I guess.

PDO

Also I would like to point out you should PDO instead of mysqli(almost deprecated I guess). PDO prepared statements also protect you against SQL-injections and could be faster because it is precompiled.

Every time you call mysqli_fetch_assoc() you "take" one record from the result set. When there are no more records, mysqli_fetch_assoc() returns false. This is, by the way, the reason you can use while loop.

When you called $line = mysqli_fetch_assoc($querying);, you took one record from the result set, leaving only 1 in the result set. So your while loop iterates over this one record only. As everyone else pointed out - you just need to comment that line.

I can also recommend using mysqli_fetch_all() function, this will give you an ordinary array that you can use, well, like a normal array

$results = mysqli_fetch_all($querying, MYSQLI_ASSOC);