problem with row number after updating mysql

PHPMaker General Discussion (PHPMaker)
1

Hi,
I updated MySQL to the latest version. After that I see and error with my code.My original code:executeQuery(“SET @row_num = 0”);
$sql = “SELECT @row_num := @row_num + 1 as row_number,appointments.*, treatmenthistory.BodyArea, treatmenthistory.BarcodeUPC as Barcode, patients.BarcodeUPC, patients.Name, patients.LastName, patients.mobile FROM appointments join treatmenthistory on appointments.TreatmentID = treatmenthistory.TreatmentID join patients on appointments.PatientID = patients.PatientID $hquery2 ORDER BY appointments.VisitHour ASC”;
$stmt = ExecuteQuery($sql);
if ($stmt->rowCount() > 0) {
while ($row = $stmt->fetch()) {
///
}
}I used $row[‘row_number’] to print the number of row.

I receive an error with “@row_num := @row_num + 1 as row_number” in my query.How I can print the row number and correct my original code?Thanks

2

You may try do it in one call to ExecuteQuery(), e.g.ExecuteQuery(“SET @row_num = 0; SELECT…”);

3

Hi,
Thank you for reply.
I changed the query but the rows did NOT listed as before.
////
$sql = “SET @row_num = 0; SELECT @row_num := @row_num + 1 as row_number, appointments.*, treatmenthistory.BodyArea, treatmenthistory.BarcodeUPC as Barcode, patients.BarcodeUPC, patients.Name, patients.LastName, patients.mobile FROM appointments join treatmenthistory on appointments.TreatmentID = treatmenthistory.TreatmentID join patients on appointments.PatientID = patients.PatientID $hquery2 ORDER BY appointments.VisitHour ASC”;I updated to MySQL 8.0.28 yesterday.
Thanks

4

mansour wrote:

the rows did NOT listed as before.

Please explain what it means.

You better enable Debug to check for errors.

5

Hi,
the problem was related to MySQL 8+ .
I changed the query:
$sql = "SELECT ROW_NUMBER() OVER ( ORDER BY faqs.FaqQuestion ) row_num,…the problem solved
thanks