Why is mysql query not returning correct amount

Question

This query keeps returning 2 entries and I can not work out why.

I have a database holding my members in a table called members. At the moment it has 5 records as it is just for testing. When I use the following query I get two results, my current id which I am logged into the website with and a blank result.

$memarray = mysql_query("SELECT id FROM members") or die ("unable to get info");
$fArray = mysql_fetch_array($memarray);

echo count($fArray);    //shows 2
echo $fArray[0];        //shows '102' my current id
echo $fArray[1];        //shows nothing

can you show the output? and use msqli_query instead of mysql_query — user2936213
– user2936213, Commented Jan 21, 2014 at 11:15
Do yourself a favor and replace mysql_* with mysqli_* as mysql_* is deprecated. us3.php.net/manual/en/changelog.mysql.php — Jason
– Jason, Commented Jan 21, 2014 at 11:25

Cobra_Fast · Accepted Answer · 2014-01-21 11:28:14Z

3

mysql_fetch_array() stores one row in an array. To retrieve all rows, you have to call it repeatedly.

while ($row = mysql_fetch_array($result))
{
    ...
}

To store all rows in an array, simply do

$rows = array();
while ($row = mysql_fetch_array($result))
    $rows[] = $row;

This, however, is rather inefficient. If possible, you should process your data within the while-loop.

edited Jan 21, 2014 at 11:28

answered Jan 21, 2014 at 11:19

Cobra_Fast

16.2k8 gold badges65 silver badges106 bronze badges

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

1 Comment

Benjio Over a year ago

Thanks. can i ask how i could push each value into a new array to hold all the ids? thanks

Community · Accepted Answer · 2023-11-17 19:51:26Z

0

By using mysql_fetch_array($memarray) you get the first row from the query result.
The default second parameter for the function is "MYSQL_BOTH".
This means you get [0=>'someID', 'id'=>"someID"]
You can use "MYSQL_ASSOC" instead.
See this php manual

edited Nov 17, 2023 at 19:51

CommunityBot

11 silver badge

answered Jan 21, 2014 at 11:20

lvil

4,3169 gold badges53 silver badges79 bronze badges

Comments

user2936213 · Accepted Answer · 2014-01-21 11:21:54Z

0

Use this:

$memarray = mysqli_query("SELECT id FROM members") or die ("unable to get info");
while ($fArray = mysqli_fetch_array($memarray))
{
   echo $fArray["id"];
}

answered Jan 21, 2014 at 11:21

user2936213

1,0111 gold badge8 silver badges19 bronze badges

Comments

Community · Accepted Answer · 2023-11-17 19:56:10Z

mysql_fetch_array — Fetch a result row as an associative array, a numeric array, or both http://php.net/mysql_fetch_array

$memarray = mysql_query("SELECT id FROM members") or die ("unable to get info");
$fArray = mysql_fetch_array($memarray);
echo '<pre>';
print_r($fArray);

result would be some thing like this :

Array ( [0] => 23 [id] => 23 )

obviously count($fArray) returns 2

to count all recors

$memarray = mysql_query("SELECT COUNT(id) as count FROM members") or die ("unable to get info");
$fArray = mysql_fetch_assoc($memarray);
echo $fArray['count'];

or

$memarray = mysql_query("SELECT id FROM members") or die ("unable to get info");
echo mysql_num_rows($memarray);

to fetch all your records

while($row = mysql_fetch_assoc($memarray))
{
    print_r($row);
}

and I also suggest take look at this notice :‌

Warning This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include: mysqli_fetch_array() PDOStatement::fetch()

https://www.php.net/manual/en/mysqli-result.fetch-array.php https://www.php.net/manual/en/pdostatement.fetch.php

Collectives™ on Stack Overflow

Why is mysql query not returning correct amount

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related