select last id in table

[ouldfella]

Hello,

i need to replace this code :

$table = "orders";
$fields = "orderNumber,invoiceid, orderId";
$where = array("orderNumber" => $resultConfirm["OrderNumber"]);
$result = select_query($table, $fields, $where);
while ($data = mysql_fetch_array($result)) {
    $invoiceId = $data['invoiceid'];
}

because is deprecated, i used this code but doesn't work :

use WHMCS\Database\Capsule;

try {
  $v=  Capsule::table('orders')
            ->latest()->first();
} catch (\Exception $e) {
    // Handle any error which may occur
}
var_dump($v)

 

[brian!]

do you really need to get the last result? because that first block of code is passing a specific order number to the query and that returned value should be unique in the database table... therefore, the second block of code should include that where statement too and it should then only return one result (and will if you choose first).

with your secondary capsule code, there would be two errors in it...

Capsule::table('tblorders')->latest('date')->first();

the above would return the last (latest) record in tblorders.

[ouldfella]

Thank you for yor reply,

i tried your code but doesn't work,

try {
  $v=  Capsule::table('tblorders')->latest('date')->first();
} catch (\Exception $e) {
    // Handle any error which may occur
}

var_dump($v);exit();

first  i created a new table orders, because i can't use tblorders , the gateway must have for every  attempt i new order id

second i need to get the last result for list where id order=x,  not the last in table.

just i need replace this code :

$table = "orders";
$fields = "orderNumber,invoiceid, orderId";
$where = array("orderNumber" => $resultConfirm["OrderNumber"]);
$result = select_query($table, $fields, $where);
while ($data = mysql_fetch_array($result)) {
    $invoiceId = $data['invoiceid'];
}

 

[brian!]

1 hour ago, ouldfella said:

first  i created a new table orders, because i can't use tblorders , the gateway must have for every  attempt i new order id

then for the sake of simplicity, i'm going to assume that the structure of orders is vaguely similar to tblorders...

$resultConfirm["OrderNumber"] = "3603258351";
$v = Capsule::table('tblorders')->select('ordernum','invoiceid','id')->where('ordernum',$resultConfirm["OrderNumber"])->latest('date')->first();
var_dump($v);

it's worth adding that using latest in capsule would only work if the table has a date column (doesn't have to be called date though).

so if using latest is not an option, then sorting by an id value is an option - I assume 'orderId' in your orders table is the same as 'id' in tblorders, so you could do...

$resultConfirm["OrderNumber"] = "3603258351";
$v = Capsule::table('orders')->select('orderNumber','invoiceid','orderId')->where('orderNumber',$resultConfirm["OrderNumber"])->orderBy('orderId','desc')->first();
var_dump($v);

ordering by desc should return the last entry in the result; ordering by asc would give you the first (though ordering by asc shouldn't really be necessary).

this would work on v7.10 (and I think probably all v7 versions), but somewhere in the back of my mind, is the thought that you're still using an older version of WHMCS... if it's v6, then I think the second one should still work, but it's untested.

[ouldfella]

thanks for your reply,

Quote

I assume 'orderId' in your orders table is the same as 'id' in tblorders

no it's not the same and it can't be the same, because the gateway must have for every attempt a new idorder, so for the same invoice we can find many orderid(s).

i found in tblorders that for every invoiceid there is one orderid.

thanks a lot for your help.