SQL: record missing with DESC

[JanHolland]

Hi folks,
My SQL command (with PHP4 in MySQL) is:
"SELECT * FROM tablename
WHERE field1 =1 AND field2 = 'BLA'
ORDER BY id DESC LIMIT 0 , 60";

That works fine in the MySQL database.

In PHP4 however it works fine with ASC, but
not with DESC:
Then I miss the record with the highest number in the table (which was 55)
(if it has the right fieldvalues).

When I add a new record nr 56 with "field1 =1 AND field2 = 'BLA' " then #56 becomes the new "missing record" in my PHP4 output.
(but no problems again in MySQL itself).

A part of my program goes like this:

"while ( $ar = mysql_fetch_array( $resultQuery, MYSQL_ASSOC ) ) {

$a="<b> A ".$ar['id']."</b>";

//TEST echo "<p>$hier!L698! $z \$a=[$a] \$queryP=[$queryP] regel=[$hier!L".__LINE__."]<br>"; exit;

$firstLine ='<tr><td colspan="3">';
$dateYMjHi=date("Y:M:j H:i",$ar['timestamp']);
$firstLine.=" PostNr=[<b>$a</b>]";
$firstLine.=" Datum=[$dateYMjHi]";
$firstLine.=' Compliment/Klacht=['.$ar['n18compl']."]";
$firstLine.=' DraadEigenaar=['.$ar["n20clPsdo"]."]</td></tr>
....
echo '<tr><td>'.$firstLine.'</td></tr>';
.....
} // while ar

";"

The line after "// TEST" looks for the wanted record but it doesnt show up there ... (with/without TEST-line)

I think it is a PHP4 problem, but what can it be?

[JanHolland]

Addendum:
adding a new last record (#57)to the table where field2='BLUB' does not help to let #56 (with field2='BLA') to appear in the PHP list of 'BLA' records.

The MySQL database itself does give all record## (so 56 and 57 also), with ASC _and DESC.

So no problems there ...

[JanHolland]

Addendum 2:

Can it be that the PHP line:
[while ( $ar = mysql_fetch_array( $resultQuery, MYSQL_ASSOC ) ) ] (with DESC)

gives an array starting with a row/line numbered 0
while the listing starts with the line numbered 1?

[jatar_k]

are you positive asc works? it isn't dropping a record as well?

it isn't a php4 problem

if the query works when fed directly into mysql then it is probably your php logic that is messed up.

are you sure you are not advancing the pointer on your query before you go into your loop? that would stop the first record from appearing.

[JanHolland]

Thx 4Ur reaction!

Ad 1:"are you positive asc works? it isn't dropping a record as well?"

Well,,, ahumm..Eeh,,, Ur rite:
The full sequence ends were:
ASC: 4,5..55,56
DESC: 56,55..5,4 :-)

2. "it isn't a php4 problem"
Why yes/not?

"3.if the query works when fed directly into mysql"
Yes, MySQL does give the full sequences as specified above.
(rechecked!)

"4 then it is probably your php logic that is messed up."
Agree on that one!

"5 are you sure you are not advancing the pointer on your query before you go into your loop? that would stop the first record from appearing."

YOU ARE RIGHT!
mysql_num_rows seems to move the pointer
BUT
after some erroneous trials to count correctly,
I removed any counting:

$queryRslt=mysql_query($queryP) or die (" \"SELECT FROM\" is mislukt! MySQL_error=" . mysql_error());

$ar = mysql_fetch_array($queryRslt, MYSQL_ASSOC)
or die ("SELECT FROM mislukt! MySQL_error=".mysql_error());

and did a wild guess about the number of rows=cells in the array:

$rowCountInt=10;
// errormessage
if ($rowCountInt<1):
else:
while ( $ar = mysql_fetch_array( $queryRslt, MYSQL_ASSOC ) ) {

etc.

But it still gives me the wrong row :-(
ie DESC: 55,31..5,4

So I agree about the point-moving after mysql_num_rows, but
even when I don't use that any more,
I loose the first row.

Will search further tonite, but have a beer first ... :-)

drunken programmers? Uuugh...

[jatar_k]

try working with the query, maybe remove the limit

you could also try (though I am not sure this will change anything) moving the internal pointer back to the beginning before your fetch array loop using mysql_data_seek [php.net]

aside from that you could try something simpler and write a simple mysql_fetch_array loop to just pull it into an array, then dump the array. The only point to this would be to rule out simple display issues.

You could also remove the MYSQL_ASSOC from your fetch array call since it isn't required to further simplify and isolate the exact issue.

I try to make things work in the simplest possible way and then add complexity

you're also sure that you are testing the exact same query?

maybe simplify your query to SELECT * and see if that works

[JanHolland]

-------------
-1. try working with the query, maybe remove the limit
you could also try (though I am not sure this will change anything) moving the internal pointer back to the beginning before your fetch array loop using mysql_data_seek
I did on several places/combinations BUT
I get #56 as first record all the time _before
while ( $ar = mysql_fetch_assoc( $queryRslt) ) AND
#55 idem _after "while etc ..."
with PHP's reset($ar);

(seek is difficult because I dont know what to seek for beforehand)
-----------------
2. aside from that you could try something simpler and write a simple mysql_fetch_array loop to just pull it into an array, then dump the array. The only point to this would be to rule out simple display issues.

Agree, will work on a lab-sitauation for that.
-----------
3. You could also remove the MYSQL_ASSOC from your fetch array call since it isn't required to further simplify and isolate the exact issue.

I need the key's to use the values in my listings, but
OK for the lab-situation)

----------------
4. I try to make things work in the simplest possible way and then add complexity

I understand, thx 4 the effort U take :-)
-------------------

5. you're also sure that you are testing the exact same query?

Yep but important for the lab-situation.

----------
6. maybe simplify your query to SELECT * and see if that works

Yep and done, but
I will do in the lab situation and
report back to-morrow.
(Now 18.30 in Holland,Netherlands,Europe :-)
----------------

CU later!

[JanHolland]

(simple) test program made, no signific changes :-(

Made this test program,
--------------------
$query = "SELECT * FROM copage WHERE n02type='NWS' ORDER by id DESC";

$link=mysql_connect(............);
mysql_select_db(.........);

$queryRslt=mysql_query($query) or die ("MySQL_error=". mysql_error());

$ar = mysql_fetch_array($queryRslt) or die ("SELECT FROM error my_sql_error=[".mysql_error());

echo $ar['id']."<hr>";

$queryRslt=mysql_query($query) or die ("MySQL_error=". mysql_error());

$ar = mysql_fetch_array($queryRslt) or die ("SELECT FROM error my_sql_error=[".mysql_error());
echo $ar['id']."<hr>";

$rowCountInt=10;

if ($rowCountInt<1):
print "<h3> In the postings with type=[NWS] no postings are formulated. </h3>";

else:

$i=0;
echo "Before mysql_fetch_array".$ar['id']."<hr>";

$i=0;
while ( $ar = mysql_fetch_array( $queryRslt) ) {

if ($i==0) echo "After mysql_fetch_array".$ar['id']."<br>";
$a="<b>$i A ".$ar['id']."</b>";

$i++;
}
endif;
--------------------

which gave this output:

--------------------
Before mysql_fetch_array 56
After mysql_fetch_array 55
--------------------

So the prog still loses nr 56 after fetch_array :-(

[JanHolland]

An additional problem is that
the rowcount is wrong too
(it is 20 while it should be 28/29 rows or
28 fields)

Where does that 20 come from?

[JanHolland]

I found the how-to of mysql_data_seek($result, 0)

mysql_data_seek() was allready suggested above, buit
couldnt get it going ...

It seems to solve the problem.

Allthough the 20 of row-count doesn't bother me, I still wonder about it ...

Thx for all the support I got!

[edited by: jatar_k at 2:00 pm (utc) on Jan. 6, 2008]
[edit reason] no urls thanks [/edit]

[jatar_k]

it is a bit of a hack, you should take a look and see why your pointer was getting moved in the first place

either way though, I'm glad you got it working

[coopster]

It could be something in the WHERE clause or any number of other factors too. One of the quickest/easiest way to determine whether or not it is your PHP logic and/or query statement construction is to dump the query and results before doing anything other result set processing.

$query = "SELECT * FROM copage WHERE n02type='NWS' ORDER by id DESC"; 
// Dump the query to the command line: 
print "<pre>Query:\n$query</pre>"; 
while ($ar = mysql_fetch_array($resultQuery)) { 
 // Dump each result row to the command line: 
 print '<pre>'; print_r($row); print '</pre>'; 
} 
exit;

If you don't get the results you expect you can cut/paste the query that was dumped to the browser and run it from a MySQL command line and compare results. If they differ, then you really do have a problem to figure out, likely a configuration setting. If they are the same then you know you have something in your PHP logic messed up. 99.9999% of the time, it is going to be the latter.