Member Avatar for stonest123

I'm not sure if this belongs in PHP or in SQL forum but basically...
when using odbc_result_all, the bottom results is missed all the time, i don't know why it does this but i think its something to do with the driver. So, to get the last result from the query i have done, is there a way i can maybe sort the fields descending (so i get the last row first) and then print only that row out in a separate query from the one i use to obtain all the results from the database?

Thanks

  • 2 Contributors
  • 8 Replies
  • 123 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by stonest123

Dani AI

Generated

Short answer: the symptom you saw is almost always caused by mixing cursor-based fetches with a helper that prints the whole set. offered a workable SQL trick; s original loop combined per-row fetching and the helper printer, which moves the cursor and can leave one row unprinted. Two clean fixes follow.

Use the result-printer once (capture if you need the HTML)

  • Let the ODBC helper produce the full table from a fresh result set. If you need the generated HTML later (or need to show the table and also show the last record separately), capture its output with buffering so you can reuse or inspect it without losing the cursor.
$res = odbc_exec($conn, $sqlQuery);

// capture HTML that the helper writes
ob_start();
odbc_result_all($res);
$table = ob_get_clean();

echo $table;

// re-run a focused query to fetch only the "last" row (see note below)

Build the HTML yourself

  • If you want fine control, iterate the rows and render every cell. That keeps the cursor behaviour explicit and avoids surprises from helper functions.
$res = odbc_exec($conn, $sqlQuery);
echo "<table border='1'>";
while ($row = odbc_fetch_array($res)) {
    echo "<tr>";
    echo "<td>" . htmlspecialchars($row['SomeColumn']) . "</td>";
    echo "</tr>";
}
echo "</table>";

Notes and troubleshooting

  • odbc_result_all prints the table and returns a row count; do not treat its return as HTML. See the PHP manual for details: odbc_result_all and odbc_fetch_array.
  • To get "the last record" reliably, query the database for a single row ordered by a stable column (auto-increment ID or timestamp) using the DB engine's single-row syntax (Access supports TOP + ORDER BY). Re-executing a short, single-row query is simpler and safer than adding dummy rows or unions.
  • Check return values from odbc_exec and use odbc_errormsg/odbc_error when things behave oddly.

Recommended Answers

All 8 Replies

Member Avatar for pritaeas

If you want such a workaround than a simple UNION with a single row would be sufficient (assuming MySql):

SELECT * FROM mytable
UNION
SELECT * FROM mytable LIMIT 1
Edited by pritaeas because: n/a
Member Avatar for stonest123

So, if i wanted to the last record of the database how would i go about getting it?
(As the amount of records per statement would change, or would i still need to sort descending?)
Thanks

Edited by stonest123 because: n/a
Member Avatar for pritaeas

My demo shows how to add one dummy record. If you use that construct your last result would be accessible by your loop (or however you do it).

Member Avatar for stonest123

ohhh i see now! If i show you my current statement and the loop could you show me how to apply it?

$postlocation = $_POST['t1'];
$postresponsible = $_POST['t2'];


$results="SELECT * FROM BackupLog WHERE 
Location = '$postlocation' 
AND Responsible = '$postresponsible'";

$rs=odbc_exec($conn, $results);
$last=odbc_exec($conn, $lastresult);

while (odbc_fetch_array($rs)){

$results2 = odbc_result_all($rs, "border=1");

echo $results2;

echo "</table>";	

}

Thats the code i use in order to run and loop out the statement

Thanks for your help

Member Avatar for pritaeas 
$results="SELECT * FROM BackupLog WHERE Location = '$postlocation' AND Responsible = '$postresponsible' UNION SELECT * FROM BackupLog LIMIT 1";
Member Avatar for stonest123

I'm now getting this error:

PHP Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.

I did forget to mention its not mySQL its Access that I'm using, is there an equivalent?

Edit, also is there a script i can add to remove the row after it has been created, just so i don't start populating the database with dummy fields

Edited by stonest123 because: n/a
Member Avatar for pritaeas

I think this will work:

$results="SELECT * FROM BackupLog WHERE Location = '$postlocation' AND Responsible = '$postresponsible' UNION SELECT TOP 1 * FROM BackupLog";

This is just a select statement. Nothing is created in the database.

Member Avatar for stonest123

Thanks so much got it working now!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.