Select column name not fetching desired result

Hi all.

I dunno where to put this question, in mysql forum or php forum, as my problem is related with fetching data into php script from mysql database.

So if this forum is inappropriate then please move this thread to the appropriate location.

Now my problem is that I want to select a specific column from mysql database and want to fetch the result.

I’ve tried very different ways. and after cutting and slashing that code I tested by selecting single column without any where clause or condition but that too is not working.

Here is my code


require_once "includes/database.php";

			$sql = "SELECT username FROM users";
			$res = mysql_query($sql);
			$row = mysql_fetch_assoc($res);
			
			print_r($row);

I’ve some 10 rows, hence I’m expecting it to fetch all 10 username.

I’ve tested my code in phpmyadmin and mysql cli, and they are fetching the result as expected. the only problem is in fetching with php script.

I’m using Wampserver 2.0i, Apache/2.2.11 (Win32), PHP-5.3.0, MySQL Server version: 5.1.36-community-log, Protocol version: 10.

Help me and advice me where my script lags or where it needs to be corrected.

One more thing I wanna add even

select * from users

is not fetching all the rows, only one row. What is the problem here ?

mysql_fetch_assoc will return the first row of the result set and increment the result set pointer. So you will need to run a loop to retrieve the entire result set.


$users = array();
while($row = mysql_fetch_assoc($res)) {
$users[] = $row;
}
echo '<pre>',print_r($users),'</pre>';

MySQL_Fetch_Assoc fetches one row at a time. It then moves an internal pointer, so that the second time you call it, it returns the second row, the third time it fetches the third row, etc.

So if you want them all in an array like the following:

Array(
    [0] => Array(
        'username' => 'User1',
        'name' => 'User One',
        'email' => 'user1@niceandlongdomainname.com'
    ),
    [1] => Array(
        'username' => 'User2',
        'name' => 'User Two',
        'email' => 'user2@niceandlongdomainname.com'
    ),
    [2] => Array(
        'username' => 'User3',
        'name' => 'User Three',
        'email' => 'user3@niceandlongdomainname.com'
    )
)

Then you need the following:

require_once "includes/database.php";
$sql = "SELECT username FROM users";
$res = mysql_query($sql);
$rows = array();
while($row = mysql_fetch_assoc($res)){
    $rows[] = $row;
}
print_r($rows);

The while loop simply calls the mysql_fetch_assoc function until it returns false. To MySQL, it’s basically saying ‘if row n+1 exists, return row n+1, else return false’, and when it returns false (i.e. there are no more results to fetch) the while() loop ends. Beautiful, isn’t it.

Edit:

@Oddz: Beat me to it, by minutes! I’m getting slow :smiley:

hey it’s working cool now. At the current moment I was not able think why it’s not working.

You have shown a really good way. fill the empty array and then echo out that where needed.

Thanx Jake.

Your replies are really helpful. They are very well explained. I really don’t need to google out for searching any help. :slight_smile:

But by this I’m getting more n more addicted to sp. and leaving the good ol google.

then it’s not a mysql problem, is it

:slight_smile: