php_mysql_assoc ignores first record

Question

Hi there I'm trying to query my database to get all the records that share the same "ITEMID" the table is a transaction list of operations done on items in the database.

I've checked the SQL query in phpmyadmin and it works correctly but when I run the php code it ignores the first record every time. Here's my code

<?php 
 // Get the quantity of each item 
 $sql1 = 'SELECT  `IDPENTERED` ,  `ITEMID` ,  `QTY` ,  `VALUE` FROM  `tblpieceentered` 
  WHERE  `ITEMID` =307 ' ;

 echo $sql1."<br>";

 // Retrieve all the data from the table
 $result1 = mysql_query($sql1)
 or die(mysql_error()); 

 // store the record of the table into $row1
 $row1 = mysql_fetch_array( $result1 ); 

 $i = 1; 
 $num = mysql_num_rows ($result1);

 echo "Num rows: $num <br>"; 

 while ($row1 = mysql_fetch_assoc ( $result1)) {
    $data_array[$i] = $row1;
$i++;
 }

echo "<pre>";
print_r ($data_array);
echo "</pre>";
?>

The Result is:

SELECT `IDPENTERED` , `ITEMID` , `QTY` , `VALUE` FROM `tblpieceentered` WHERE `ITEMID`     =307 
Num rows: 5 

Array
(
    [1] => Array
        (
            [IDPENTERED] => 1999
            [ITEMID] => 307
            [QTY] => -1
            [VALUE] => -0.21
        )

    [2] => Array
        (
            [IDPENTERED] => 2507
            [ITEMID] => 307
            [QTY] => -10
            [VALUE] => -2.1
        )

    [3] => Array
        (
            [IDPENTERED] => 3039
            [ITEMID] => 307
            [QTY] => 1
            [VALUE] => 0.21
        )

[4] => Array
        (
            [IDPENTERED] => 3040
            [ITEMID] => 307
            [QTY] => -1
            [VALUE] => -0.21
        )

)

Any Ideas?

Thanks Sean

Off topic addition to my answer: unless you want your array to start at '1', you can just use $data_array[] = $row and skip the $i counter. It will append every row to the end of the array. — GolezTrol
– GolezTrol, Commented Feb 9, 2011 at 23:56

GolezTrol · Accepted Answer · 2011-02-09 23:52:25Z

1

mysql_fetch_array fetches the first row already. You don't add this row in your array. The while loop continues with the second row.

answered Feb 9, 2011 at 23:52

GolezTrol

116k19 gold badges186 silver badges215 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 10:24:24Z

1

use mysqli and fetch_all will be much more efficient:

mysql_fetch_array add all rows?

edited May 23, 2017 at 10:24

CommunityBot

11 silver badge

answered Feb 10, 2011 at 0:00

Jon Black

16.6k5 gold badges45 silver badges42 bronze badges

1 Comment

Sean Smith Over a year ago

Thanks @f00 I'll give it a try later today as the query returns over 1500 records and the database is growing all the time.

The Scrum Meister · Accepted Answer · 2011-02-10 00:01:19Z

1

That is because you are fetching the first item before the while loop.

Remove the $row1 = mysql_fetch_array( $result1 ); line, and you should be good.

Alternatively, if you wish to access the first row before the loop, you can use a do-while loop instead:

// store the record of the table into $row1
$row1 = mysql_fetch_array( $result1 ); 
$i = 1; 
$num = mysql_num_rows ($result1);
echo "Num rows: $num <br>";
do {
   $data_array[$i] = $row1;
   $i++;
} while ($row1 = mysql_fetch_assoc ( $result1));

edited Feb 10, 2011 at 0:01

answered Feb 9, 2011 at 23:53

The Scrum Meister

30.2k8 gold badges69 silver badges65 bronze badges

Comments

deceze · Accepted Answer · 2011-02-10 01:23:12Z

1

There goes your first record:

$row1 = mysql_fetch_array( $result1 );

The loop starts on the next (second) record:

while ($row1 = mysql_fetch_assoc ( $result1))

edited Feb 10, 2011 at 1:23

answered Feb 9, 2011 at 23:53

deceze♦

525k89 gold badges807 silver badges954 bronze badges

Collectives™ on Stack Overflow

php_mysql_assoc ignores first record

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related