Funny thing

[psuplat]

Wonder if any of you had this before.

 

I'm pulling data from db for given day, and display them in a table. After, as extra feature, I'm running a quick query checking how many record are in the db for given day and dispaying result.

 

The count works correctly - it shows the actual number of records in db, but the first part of the code - the listing of record - always skips the first record.

 

So the count returns i.e. 7 record, but on the listing shows only 6.

 

Here's the listing part of the code. Also you might notice a bit "messy" use of odbc_fetch_row and odbc_fetch_array, but that had to do with controlling situation when there was no records in db - I'll clean it up later

 

Any ideas or solutions welcomed

 

echo "<table border=0 class=\"report-font-table\"><tr bgcolor=#CCCCCC><td><b>MODEL</b></td><td><b>SERIAL NUMBER</b></td><td><b>INSPECTOR</b></td><td><b>COMMENTS/FAILS</b></td></tr>";
$MySQL1 = 'select Model, Serial_no, Inspector, Comment from CM_Audit where Date=#'.$new_date.'#';
$MyCon=odbc_connect('SQA_Typewriter','','') ;         // use the SQA_Typewriter ODBC
$result=odbc_exec($MyCon,$MySQL1);

$check1=odbc_fetch_array($result);

if (!empty($check1)) {
   while (odbc_fetch_row($result)) {
    echo "<tr>
                <td>".odbc_result($result,"Model")."</td>
                <td >".odbc_result($result,"Serial_no")."</td>
                    <td >".odbc_result($result,"Inspector")."</td>
                        <td >".odbc_result($result,"Comment")."</td>
            </tr>";
  }
  echo "</table>";

      odbc_close($MyCon);
} else {
    echo "</table><p style=font-weight:bold;color:006699>No audits have been carried out on this day.</p>";
}

[PFMaBiSmAd]

$check1=odbc_fetch_array($result);

^^^ That line of code is fetching and discarding the first row in the result set. Why do you have that line of code in your program?

[psuplat]

Uhm,

 

i had problems with page when no record were returned from db, the page was crashing. So i tried few way to go around it, and first option that actually worked for me was using php's empty function, checking if returned array is empty.

If not list, if empty echo text.

 

You'll notice this in the code:

$check1=odbc_fetch_array($result);

if (!empty($check1)) {
   //list the records
  }
.
.
.
else {
    echo "</table><p style=font-weight:bold;color:006699>No audits have been carried out on this day.</p>";
}

 

Now if this is causing the first record to dissapear, do you know other way in which I can control returning of 0 records?

[PFMaBiSmAd]

odbc_num_rows() may work, depending on if the db driver you are using supports it.

 

odbc_fetch_array() and odbc_fetch_row() optionally take a second parameter than can be used to reset back to row 1, again depending on if the db driver you are using supports it.

 

You could also use most of your existing logic, but change the while(){} loop into a do{}while() loop so that you can use the first row that you are fetching as part of the test to see if there are any rows.

 

Also, you could just use your existing while(){} loop. Your existing code should produce an empty table when there are zero matching rows. Removing the extra fetch_array() won't change that. What sort of problems where you having that caused you to put in that extra fetch_array()?

 

[psuplat]

if no records were returned from db i was getting the following:

 

Warning: odbc_fetch_row(): 2 is not a valid QDBC result resource in report.php on line 48

Warning: odbc_close(): 1 is not a valid ODBC-Link resource in report.plip on line 62

 

and those 2 line where repeating in the loop causing the browser to crash

[helloworld2010]

You can code like this...

$result = odbc_exec($MyCon,$MySQL1) or die('sorry, db query failed...');

   while (odbc_fetch_row($result)) {
    ...
  }

 

hope it useful for you 

[psuplat]

strangely enough that was the most obvious way of doing it, but it still didn't work 

 

but i managed to go around the problem. original structure of report was:

1. code of audit

2. echo result

3. code to count audits using select count(*)

4. echo number

 

all i did was:

1. code to count audits using select count(*)

2. store result if variable $cma

3. code of audit

4. if ($cma != 0)

5. echo results

6. else echo "no records"

7. echo $cma

 

i guess not the perfect or most optimized way of doing it, but i got no idea what odbc drivers are on the server, so I just have to wing my way around it 

[PFMaBiSmAd]

if no records were returned from db i was getting the following:

 

Warning: odbc_fetch_row(): 2 is not a valid QDBC result resource in report.php on line 48

Warning: odbc_close(): 1 is not a valid ODBC-Link resource in report.plip on line 62

 

and those 2 line where repeating in the loop causing the browser to crash

 

No, you would not have. The first error means that your query failed due to an error (zero matching rows is not an error), probably due to not having a valid connection to your database, which is what the second error supports as well.

 

That you have this code inside of an outer loop that you did not bother to post and that the code you did post is closing the connection explains the errors.

 

If you really want help with what your code is doing, you would need to post all the actual relevant code. You are writing and rewriting unnecessary code to compensate for some crap code  earlier in your script.