2. PHPRunner General questions
  3. PHP Code slows the system
This topic is locked

PHP Code slows the system

 		8/1/2024 8:44:29 AM
PHPRunner General questions
B
 bluestrikealpha author

Hi,

I implement an event before display with the following PHP code, it works but when I made the refreshed to the page it takes to much time to do refresh

global $conn;

// SQL query to fetch inspection data including serial number
$sql = "SELECT id, Next inspection, serial number FROM inspection"; // Using serial number

$result = $conn->query($sql);

if ($result->num_rows > 0) {
// Get the current date and time
$currentDateTime = new DateTime();

// Loop through each inspection item
while ($row = $result->fetch_assoc()) {
$productItemId = $row['id']; // ID from inspection table
$nextInspection = $row['Next inspection'];
$serialNumber = $row['serial number']; // Using `serial number`

// Determine the status based on the Next inspection date
if (strcasecmp($nextInspection, "Not usable") == 0) {
$updateStatus = "Not usable";
} elseif (empty($nextInspection)) {
$updateStatus = "missing inspection date";
} else {
// Attempt to create DateTime object with the specific format
try {
// Create DateTime object assuming the format is d-m-y
$nextInspectionDate = DateTime::createFromFormat('d-m-y', $nextInspection);

// Check for errors in parsing
if ($nextInspectionDate === false) {
throw new Exception("Failed to parse date: '$nextInspection'");
}

// Compare Next inspection date with current date
if ($nextInspectionDate > $currentDateTime) {
$updateStatus = "Inpection Valid"; // Date is in the future
} else {
$updateStatus = "Out of Inspection"; // Date is in the past or now
}
} catch (Exception $e) {
// Handle parsing error, set status to indicate invalid date
$updateStatus = "invalid inspection date";
}
}

// SQL query to update the status in the Product item table where serial number matches
$updateSql = "UPDATE `Product item` SET status=? WHERE `serial number`=?"; // Using `serial number`

// Prepare the statement
if ($stmt = $conn->prepare($updateSql)) {
// Bind parameters (status, serial_number)
$stmt->bind_param('ss', $updateStatus, $serialNumber); // Assuming serial number is a string

// Execute the statement
$stmt->execute();

// Close the statement
$stmt->close();
}
}

}

// Close the connection
$conn->close();

Can someone try to help with some way to put it work faster?

Thank you
C
 		cristi 8/2/2024

  • never use query inside loop....believe me...your sql server will thank you.Use JOIN or MySQL IN and do your thing outside or even without the loop - just imagine that you are the SQL server and I am the query inside the loop and I knock at your door thousands or hundred of thousand of times tonight in order to ask what time is it - at some point you will grow tired no?


  • use LIMIT 1 in your update query if you know that only one record will be updated.


  • use indexes - is serial number indexed?
B
 		bluestrikealpha author 9/20/2024

Hi all,

OK, I change the code as suggested for this one:

global $conn;

$sql = "
UPDATE Product item AS pi
JOIN inspection AS i
ON pi.Serial number = i.Serial number
SET pi.status =
CASE
WHEN i.Next inspection = 'Not usable' THEN 'Not usable'
WHEN i.Next inspection IS NULL OR i.Next inspection = '' THEN 'missing inspection date'
WHEN STR_TO_DATE(i.Next inspection, '%d-%m-%Y') IS NOT NULL AND STR_TO_DATE(i.Next inspection, '%d-%m-%Y') > NOW() THEN 'Inspection Valid'
WHEN STR_TO_DATE(i.Next inspection, '%d-%m-%y') IS NOT NULL AND STR_TO_DATE(i.Next inspection, '%d-%m-%y') > NOW() THEN 'Inspection Valid'
WHEN STR_TO_DATE(i.Next inspection, '%Y-%m-%d') IS NOT NULL AND STR_TO_DATE(i.Next inspection, '%Y-%m-%d') > NOW() THEN 'Inspection Valid'
WHEN STR_TO_DATE(i.Next inspection, '%m/%d/%Y') IS NOT NULL AND STR_TO_DATE(i.Next inspection, '%m/%d/%Y') > NOW() THEN 'Inspection Valid'
WHEN STR_TO_DATE(i.Next inspection, '%m/%d/%y') IS NOT NULL AND STR_TO_DATE(i.Next inspection, '%m/%d/%y') > NOW() THEN 'Inspection Valid'
ELSE 'Out of Inspection'
END;
";

if ($conn->query($sql) === TRUE) {
echo "Product item status updated successfully.";
} else {
echo "Error updating record: " . $conn->error;
}

$conn->close();

Now it is fast, but when I change the page appear the server error on the top of the page and still on the page one.

img alt

Can someone help me with this?

Thanks
C
 		cristi 9/20/2024

What do you see when you press on that blue link "see details"?
B
 		bluestrikealpha author 9/20/2024

Appear a log of the updated of "status" field with all the information about the about the "product item table". Here you have the begining of the log:

Product item status updated successfully.{"success":true,"idStartFrom":104,"pagesData":{"1":{"proxy":[],"pageName":"list","helperFormItems":{"formItems":{"above-grid":["add","inline_add","inline_save_all","inline_cancel_all","delete","update_selected","advsearch_link1","details_found","page_size","print_panel","columns_control"],"below-grid":["pagination"],"left":["logo","expand_button","menu","search_panel"],"supertop":["expand_menu_button","collapse_button","breadcrumb","simple_search","list_options","loginform_login","username_button"],"grid":["simple_grid_field26","simple_grid_field","simple_grid_field27","simple_grid_field25","simple_grid_field28","simple_grid_field18","simple_grid_field29","simple_grid_field1","simple_grid_field30","simple_grid_field2","simple_grid_field31","simple_grid_field3","simple_grid_field33","simple_grid_field5","simple_grid_field34","simple_grid_field6","simple_grid_field35","simple_grid_field7","simple_grid_field36","simple_grid_field8","simple_grid_field37","simple_grid_field9","simple_grid_field38","simple_grid_field10","simple_grid_field40","simple_grid_field12","simple_grid_field41","simple_grid_field13","simple_grid_field43","simple_grid_field15","simple_grid_field46","simple_grid_field19","simple_grid_field49","simple_grid_field22","simple_grid_field50","simple_grid_field23","simple_grid_field51","simple_grid_field24","details_preview","grid_edit","grid_inline_edit","grid_inline_save","grid_inline_cancel","grid_view","grid_checkbox_head","grid_checkbox","grid_alldetails_link","grid_details_link","grid_field_label","grid_field","grid_field_label1","grid_field1"],"top":["master_info"]},"formXtTags":{"below-grid":["pagination"],"top":["mastertable_block"]},"itemForms":{"add":"above-grid","inline_add":"above-grid","inline_save_all":"above-grid","inline_cancel_all":"above-grid","delete":"above-grid","update_selected":"above-grid","advsearch_link1":"above-grid","details_found":"above-grid","page_size":"above-grid","print_panel":"above-grid","columns_control":"above-grid","pagination":"below-grid","logo":"left","expand_button":"left","menu":"left","search_panel":"left","expand_menu_button":"supertop","collapse_button":"supertop","breadcrumb":"supertop","simple_search":"supertop","list_options":"supertop","loginform_login":"supertop","username_button":"supertop","simple_grid_field26":"grid","simple_grid_field":"grid","simple_grid_field27":"grid","simple_grid_field25":"grid","simple_grid_field28":"grid","simple_grid_field18":"grid","simple_grid_field29":"grid","simple_grid_field1":"grid","simple_grid_field30":"grid","simple_grid_field2":"grid","simple_grid_field31":"grid","simple_grid_field3":"grid","simple_grid_field33":"grid","simple_grid_field5":"grid","simple_grid_field34":"grid","simple_grid_field6":"grid","simple_grid_field35":"grid","simple_grid_field7":"grid","simple_grid_field36":"grid","simple_grid_field8":"grid","simple_grid_field37":"grid","simple_grid_field9":"grid","simple_grid_field38":"grid","simple_grid_field10":"grid","simple_grid_field40":"grid","simple_grid_field12":"grid","simple_grid_field41":"grid","simple_grid_field13":"grid","simple_grid_field43":"grid","simple_grid_field15":"grid","simple_grid_field46":"grid","simple_grid_field19":"grid","simple_grid_field49":"grid","simple_grid_field22":"grid","simple_grid_field50":"grid","simple_grid_field23":"grid","simple_grid_field51":"grid","simple_grid_field24":"grid","details_preview":"grid","grid_edit":"grid","grid_inline_edit":"grid","grid_inline_save":"grid","grid_inline_cancel":"grid","grid_view":"grid","grid_checkbox_head":"grid","grid_checkbox":"grid","grid_alldetails_link":"grid","grid_details_link":"grid","grid_field_label":"grid","grid_field":"grid","grid_field_label1":"grid","grid_field1":"grid","master_info":"top"},"itemLocations":{"simple_grid_field26":{"location":"grid","cellId":"headcell_field"},"simple_grid_field":{"location":"grid","cellId":"cell_field"},"simple_grid_field27":{"location":"grid","cellId":"headcell_field1"},"simple_grid_field25":{"location":"grid","cellId":"cell_field1"},"simple_grid_field28":{"location":"grid","cellId":"headcell_field2"},"simple_grid_field18":{"location":"grid","cellId":"cell_field2"},"simple_grid_field29":{"location":"grid","cellId":"headcell_field3"},"simple_grid_field1":{"location":"grid","cellId":"cell_field3"},"simple_grid_field30":{"location":"grid","cellId":"headcell_field4"},"simple_grid_field2":{"location":"grid","cellId":"cell_field4"},"simple_grid_field31":{"location":"grid","cellId":"headcell_field5"},"simple_grid_field3":{"location":"grid","cellId":"cell_field5"},"simple_grid_field33":{"location":"grid","cellId":"headcell_field7"},"simple_grid_field5":{"location":"grid","cellId":"cell_field7"},"simple_grid_field34":{"location":"grid","cellId":"headcell_field8"},"simple_grid_field6":{"location":"grid","cellId":"cell_field8"},"simple_grid_field35":{"location":"grid","cellId":"headcell_field9"},"simple_grid_field7":{"location":"grid","cellId":"cell_field9"},"simple_grid_field36":{"location":"grid","cellId":"headcell_field10"},"simple_grid_field8":{"location":"grid","cellId":"cell_field10"},"simple_grid_field37":{"location":"grid","cellId":"headcell_field11"},"simple_grid_field9":{"location":"grid","cellId":"cell_field11"},"simple_grid_field38":{"location":"grid","cellId":"headcell_field12"},"simple_grid_field10":{"location":"grid","cellId":"cell_field12"},"simple_grid_field40":{"location":"grid","cellId":"headcell_field14"},"simple_grid_field12":{"location":"grid","cellId":"cell_field14"},"simple_grid_field41":{"location":"grid","cellId":"headcell_field15"},"simple_grid_field13":{"location":"grid","cellId":"cell_field15"},"simple_grid_field43":{"location":"grid","cellId":"headcell_field17"},"simple_grid_field15":{"location":"grid","cellId":"cell_field17"},"simple_grid_field46":{"location":"grid","cellId":"headcell_field20"},"simple_grid_field19":{"location":"grid","cellId":"cell_field20"},"simple_grid_field49":{"location":"grid","cellId":"headcell_field23"},"simple_grid_field22":{"location":"grid","cellId":"cell_field23"},"simple_grid_field50":{"location":"grid","cellId":"headcell_field24"},"simple_grid_field23":{"location":"grid","cellId":"cell_field24"},"simple_grid_field51":{"location":"grid","cellId":"headcell_field25"},"simple_grid_field24":{"location":"grid","cellId":"cell_field25"},"details_preview":{"location":"grid","cellId":"cell_dpreview"},"grid_edit":{"location":"grid","cellId":"cell_icons"},"grid_inline_edit":{"location":"grid","cellId":"cell_icons"},"grid_inline_save":{"location":"grid","cellId":"cell_icons"},"grid_inline_cancel":{"location":"grid","cellId":"cell_icons"},"grid_view":{"location":"grid","cellId":"cell_icons"},"grid_checkbox_head":{"location":"grid","cellId":"headcell_checkbox"},"grid_checkbox":{"location":"grid","cellId":"cell_checkbox"},"grid_alldetails_link":{"location":"grid","cellId":"cell_details"},"grid_details_link":{"location":"grid","cellId":"cell_details"},"grid_field_label":{"location":"grid","cellId":"headcell_field18"},"grid_field":{"location":"grid","cellId":"cell_field18"},"grid_field_label1":{"location":"grid","cellId":"headcell_field6"},"grid_field1":{"location":"grid","cellId":"cell_field6"}},"itemVisiblity":{"breadcrumb":5,"expand_menu_button":2,"print_panel":5,"expand_button":5}},"helperItemsByType":{"page_size":["page_size"],"details_found":["details_found"],"breadcrumb":["breadcrumb"],"logo":["logo"],"menu":["menu"],"simple_search":["simple_search"],"pagination":["pagination"],"search_panel":["search_panel"],"list_options":["list_options"],"show_search_panel":["show_search_panel"],"hide_search_panel":["hide_search_panel"],"grid_field":["simple_grid_field22","simple_grid_field19","simple_grid_field18","simple_grid_field15","simple_grid_field6","simple_grid_field13","simple_grid_field5","simple_grid_field12","simple_grid_field3","simple_grid_field10","simple_grid_field2","simple_grid_field9","simple_grid_field1","simple_grid_field8","simple_grid_field","simple_grid_field7","simple_grid_field23","simple_grid_field24","simple_grid_field25","grid_field","grid_field1"],"search_panel_field":["search_panel_field22","search_panel_field19","search_panel_field18","search_panel_field15","search_panel_field7","search_panel_field6","search_panel_field13","search_panel_field5","search_panel_field12","search_panel_field3","search_panel_field10","search_panel_field2","search_panel_field9","search_panel_field1","search_panel_field8","search_panel_field","search_panel_field23","search_panel_field24","search_panel_field25","search_panel_field16","search_panel_field4"],"username_button":["username_button"],"loginform_login":["loginform_login"],"userinfo_link":["userinfo_link"],"logout_link":["logout_link"],"adminarea_link":["adminarea_link"],"changepassword_link":["changepassword_link"],"-":["-","-1","-2","-3","-4","-5","-6"],"expand_menu_button":["expand_menu_button"],"collapse_button":["collapse_button"],"add":["add"],"grid_edit":["grid_edit"],"grid_view":["grid_view"],"print_panel":["print_panel"],"print_scope":["print_scope"],"print_button":["print_button"],"print_records":["print_records"],"grid_checkbox":["grid_checkbox"],"grid_checkbox_head":["grid_checkbox_head"],"export":["export"],"export_selected":["export_selected"],"import":["import"],"delete":["delete"],"delete_selected":["delete_selected"],"advsearch_link":["advsearch_link","advsearch_link1"],"print_details":["print_details"],"details_preview":["details_preview"],"grid_details_link":["grid_details_link"],"grid_inline_cancel":["grid_inline_cancel"],"inline_save_all":["inline_save_all"],"inline_cancel_all":["inline_cancel_all"],"edit_selected":["edit_selected"],"grid_inline_edit":["grid_inline_edit"],"grid_inline_save":["grid_inline_save"],"columns_control":["columns_control"],"lang_selector":["lang_selector"],"inline_add":["inline_add"],"master_info":["master_info"],"update_selected":["update_selected"],"grid_field_label":["simple_grid_field26","simple_grid_field27","simple_grid_field28","simple_grid_field29","simple_grid_field30","simple_grid_field31","simple_grid_field33","simple_grid_field34","simple_grid_field35","simple_grid_field36","simple_grid_field37","simple_grid_field38","simple_grid_field40","simple_grid_field41","simple_grid_field43","simple_grid_field46","simple_grid_field49","simple_grid_field50","simple_grid_field51","grid_field_label","grid_field_label1"],"expand_button":["expand_button"],"grid_alldetails_link":["grid_alldetails_link"]},"helperFieldItems":{"ID":["simple_grid_field22","simple_grid_field49"],"Order nr.":["simple_grid_field19","simple_grid_field46"],"Registration number":["simple_grid_field18","simple_grid_field28"],"Type of Embroidery":["simple_grid_field15","simple_grid_field43"],"Type":["simple_grid_field6","simple_grid_field34"],"Colour pocket":["simple_grid_field13","simple_grid_field41"],"Department":["simple_grid_field5","simple_grid_field33"],"Colour Backside":["simple_grid_field12","simple_grid_field40"],"Brand":["simple_grid_field3","simple_grid_field31"],"Colour outside":["simple_grid_field10","simple_grid_field38"],"Size":["simple_grid_field2","simple_grid_field30"],"Company ID":["simple_grid_field9","simple_grid_field37"],"Embroidery":["simple_grid_field1","simple_grid_field29"],"Remarks":["simple_grid_field8","simple_grid_field36"],"RFID":["simple_grid_field","simple_grid_field26"],"Reference":["simple_grid_field7","simple_grid_field35"],"Sector":["simple_grid_field23","simple_grid_field50"],"Leq":["simple_grid_field24","simple_grid_field51"],"Serial number":["simple_grid_field25","simple_grid_field27"],"Periodicity":["grid_field","grid_field_label"],"Status":["grid_field1","grid_field_label1"]},"buttons":[],"fieldItems":{"ID":["simple_grid_field22","simple_grid_field49"],"Order nr.":["simple_grid_field19","simple_grid_field46"],"Registration number":["simple_grid_field18","simple_grid_field28"],"Type of Embroidery":["simple_grid_field15","simple_grid_field43"],"Type":["simple_grid_field6","simple_grid_field34"],"Colour pocket":["simple_grid_field13","simple_grid_field41"],"Department":["simple_grid_field5","simple_grid_field33"],"Colour Backside":["simple_grid_field12","simple_grid_field40"],"Brand":["simple_grid_field3","simple_grid_field31"],"Colour outside":["simple_grid_field10","simple_grid_field38"],"Size":["simple_grid_field2","simple_grid_field30"],"Company ID":["simple_grid_field9","simple_grid_field37"],"Embroidery":["simple_grid_field1","simple_grid_field29"],"Remarks":["simple_grid_field8","simple_grid_field36"],"RFID":["simple_grid_field","simple_grid_field26"],"Reference":["simple_grid_field7","simple_grid_field35"],"Sector":["simple_grid_field23","simple_grid_field50"],"Leq":["simple_grid_field24","simple_grid_field51"],"Serial number":["simple_grid_field25","simple_grid_field27"],"Periodicity":["grid_field","grid_field_label"],"Status":["grid_field1","grid_field_label1"]},"renderedMediaType":0,"notifications":false,"mobileSub":false,"urlParams":{"goto":2},"gridTabs":"","resizableColumnsData":{"gridWidth":4940.13,"thWidths":
C
 		cristi 9/20/2024

First: $conn->query($sql) doesn't return a boolean type result if you look in the php manual.
You have to define a variable, let's say $result for that query and play with it in a conditional.
Second I see an echo of what you wanted if the query succeded" Product item status updated successfully." in that message
It is your choice to echo an succes message for the update query that triggers the Server error message - that is not an error - it is the result of the echo.