output skips first record

smeagal

hi all

i have this query which joins three tables. but the result it shows me is not all correct (in PHP page). meaning, if i have 4 records (query result), it will only show me 3 records. it will start from second record instead of result record number 1

eg:

$sql = "SELECT DISTINCT tblA.id,tblA.name, tblA.rank,tblB.rankId,
tblB.rankDesc,tblB.dept,tblC.deptId,tblC,deptDesc
FROM   tblA,tblB,tblC
WHERE  tblA.id = '".$HTTP_SESSION_VARS["stuId"]."'
 AND    tblA.rank = tblB.rankId
 AND    tblB.dept = tblC.deptId";

$doSql = mysql_query( $sql ) or die( mysql_error() );

while( $x = mysql_fetch_array( $doSql ) )
{
   echo $x["name"]."<BR>";
}

my 'should be' result:
Adam
Mary
John
Steve

but currently my result shows:
Mary
John
Steve

i use mysql_fetch_array() function to extract those data. it will start from Mary instead of Adam everytime. tried mysql_fetch_object() also but i got the same result.

to get those result in the 'should be' result, i used

echo mysql_result( $doSql, 0, "name")."<BR>";
echo mysql_result( $doSql, 1, "name")."<BR>";
echo mysql_result( $doSql, 2, "name")."<BR>";
echo mysql_result( $doSql, 3, "name")."<BR>";

and it showed correctly...

any ideas?

kevinsequeira

$sql = "SELECT DISTINCT tblA.id, tblA.name, tblA.rank, tblB.rankId, tblB.rankDesc, tblB.dept, tblC.deptId, tblC, deptDesc FROM tblA, tblB, tblC WHERE tblA.id = '".$HTTP_SESSION_VARS["stuId"]."' AND tblA.rank = tblB.rankId AND tblB.dept = tblC.deptId";
$doSql = mysql_query( $sql ) or die( mysql_error() );
echo $sql;
while( $x = mysql_fetch_array($doSql) ){
   echo $x["name"]."<BR>";
}

this seems fine, see what the output of the sql is and run it in the database directly.
also u are selecting distinct records, maybe thats got something to do with the way ure getting ure output

reg
kevin

smeagal

FYI, i am using MYSQL-front as a tool for my db and i use windows on my pc terminal. the server is running on LINUX.

i did tried running the query using MYSQL-front and the result is ok. it showed everyting. the problem occurs when i executed it in PHP using mysql_fetch_array() function. same thing happens when i used mysq_fetch_object().

kinda confusing.. any ideas?

tsinka

Hi,

do you have a local copy of the database on your pc or do you use MYSQL-front to check the query on the database on the Linux server ?

smeagal

i use it to check the database query on the Linux server

tsinka

Ok,

which version of php is installed on the server ?

A workaround could be

$doSql = mysql_query( $sql ) or die( mysql_error() );
$numRecords = mysql_num_rows($doSql);

for ($i=0;$i<$numRecords;$i++) {
    echo mysql_result( $doSql, $i, "name")."<BR>";
}

smeagal

I am using PHP ver 4.0.6

I tried that earlier.. ehehehe.. exactly the same code you are suggesting. but it looks like the page is forever loading when i execute the page. i don't know why. i have to ctrl+alt+del to stop it from crashing the db server.

kinda strange since the record displays starting from the second record instead of from the beginning. i also tried adding 'LIMIT 0, 100' so that the record pointer starts from 0 and not 1. still the same thing happens.

kevinsequeira

is that the only bit of code on the page ??

reg
kevin

tsinka

Ok,

seems to be a bug in php-4.0.6 (the same problem with oracle functions). You might either need to upgrade to a more recent version or try this one:

mysql_data_seek($doSql, 0);

This resets the internal result set pointer to the first position.

if (mysql_num_rows($doSql)) {
    mysql_data_seek($doSql, 0);
}

try it with and without the if statement (to check if mysql_num_rows causes your script to hang, too). You need the if statement to prevent php from printing some error messages if the result set is empty.

smeagal

alright!!

it worked man! thanx a lot. this means i have to check every query.. gosh..i think i'm gonna suggest to my boss to upgrade our PHP to the latest version.

thx a million guys!!