mysqli_fetch_array returning less results than expected

Question

I'm using the code below to return and echo an array. If I define mysqli_fetch_array($results, MYSQLI_BOTH) then my array is truncated by one result. The 1st result in the array drops off the list. If I remove the MYSQLI_BOTH then I get the results that I expect, but my hosting company (Dreamhost) throws this error:

Warning: mysqli_fetch_array() [function.mysqli-fetch-array]: The result type should be either MYSQLI_NUM, MYSQLI_ASSOC or MYSQLI_BOTH in /blah/blah/blah.co.uk/index.php on line 14

What I really want is to use mysqli_fetch_array($results, 0) so that I catch all of the results, but do not get this error message.

CODE:

$dbc = mysqli_connect($dbhost, $dbuser, $dbpass, $dbname) or die('This is the die connect error');

$query = "SELECT DISTINCT continent FROM tour";
$result = mysqli_query($dbc, $query) or die('This is the die query error');

$row = mysqli_fetch_array($result, MYSQLI_BOTH); // was ($result, 0) to start at 0, now in error, starts at 1 missing results

while($row = mysqli_fetch_array($result)) {
    echo '<a href="country.php?continent=' . $row['continent'] . '">' . $row['continent'] . "</a><br />\n"; 
}

mysqli_close($dbc);

This is not really answerable because you're not telling us about the nature of that first record: Is it a duplicate of the second one (which would be ok)? — Pekka
– Pekka, Commented Jul 27, 2010 at 19:09

Fosco · Accepted Answer · 2010-07-27 19:49:22Z

2

The difference in those parameters is only in how the array will be defined.

MYSQLI_NUM = Array items will use a numerical index key.
MYSQLI_ASSOC = Array items will use the column name as an index key.
MYSQLI_BOTH = Array items will be duplicated, with one having a numerical index key and one having the column name as an index key.

You're not losing results.

You are however fetching twice before outputting, which meant you were skipping a row... do it like this:

$query = "SELECT DISTINCT continent FROM tour"; 

$result = mysqli_query($dbc, $query) or die('This is the die query error'); 

while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) { 

    echo '<a href="country.php?continent=' . $row['continent'] . '">' . $row['continent'] . "</a><br />\n";  
    } 

mysqli_close($dbc);

edited Jul 27, 2010 at 19:49

answered Jul 27, 2010 at 19:08

Fosco

38.6k7 gold badges90 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

njwrigley Over a year ago

Thanks for the reply. However, when I tried it like this, I was again 'losing' the 1st result drawn from the db. I'm not too sure why this is still happening. Further to that, I still get the error message when using MYSQLI_ASSOC that I mentioned above. Very perculiar!

Fosco Over a year ago

There is no way. Run that query in MySQL directly and see what you're getting back. Remove the MYSQLI_ASSOC parameter, just have the query as a param.

Amit Sharma · Accepted Answer · 2019-12-27 11:35:50Z

0

You are using distinct in your query. Ditinct return the unique records only. please check your database if you have multiple continents name in your table or not.

$query = "SELECT DISTINCT continent FROM tour"; 

 $result = mysqli_query($dbc, $query) or die('This is the die query error'); 

 while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) { 

    echo '<a href="country.php?continent=' . $row['continent'] . '">' . $row['continent'] . "</a><br />\n";  
} 

mysqli_close($dbc);

answered Dec 27, 2019 at 11:35

Amit Sharma

1,7853 gold badges13 silver badges21 bronze badges

Collectives™ on Stack Overflow

mysqli_fetch_array returning less results than expected

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related