Very slow running code....
I'm getting the following error:
Fatal error: Maximum execution time of 30 seconds exceeded in \SERVER_PATH\functions.php on line 19
(where SERVER_PATH is the actual path).
I can't work out where the code is going wrong though (presumably there must be something I need to change), can anyone see which bit is inefficient (I appreciate it shows the row number, I just can't work out what is wrong though)?
<?php
function db_result_to_array($result){
//---------------------------------------------------------------------
$res_array = array();
for ($count=0; $row = $result->fetch_assoc(); $count++) {
$res_array[$count]=$row;
}
return $res_array;
}
//---------------------------------------------------------------------
function get_dataset($server, $user, $password, $database, $query7) {
//---------------------------------------------------------------------
$db1 = new mysqli($server, $user, $password, $database);
$query1 = $query7;
$result1 = $db1->query($query1);
$num_results = $result1->num_rows;
$result8 = db_result_to_array($result1);
return $result8;
$db1->close();}
//---------------------------------------------------------------------
function get_sub_dataset($server, $user, $password, $database, $query7,$sub_field_id) {
//---------------------------------------------------------------------
$db1 = new mysqli($server, $user, $password, $database);
$query1 = $query7;
$result1 = $db1->query($query1);
$num_results = $result1->num_rows;
$result8 = db_result_to_array($result1);
return $result8;}
//---------------------------------------------------------------------
function drop_down($array_name,$field_id, $query_field_id, $field_name,$server_name,$user_name,$password,$database_name,$query_string,$row,$multiple=0,$query_s tring2=0,$sub_field_id=0) {
//-------------------------------------------------------------------------------------------------------------------------
$stat_array=get_dataset($server_name,$user_name,$password,$database_name,$query_string);
//echo "<select name=".$array_name." width='100%'>";
if(empty($multiple)) {
echo "<select name=".$array_name." width='100%'>";
if ($row[$field_id] == $thiscat[$query_field_id]) {}{echo "<option>None Selected</option>";}
foreach ($stat_array as $thiscat)
{
echo '<option value="' . $thiscat[$query_field_id] . '"';
if ($row[$field_id] == $thiscat[$query_field_id]) {echo ' selected';}
echo '>' . $thiscat[$field_name] . '</option>';
}
}
else {echo "<select multiple size =".$multiple." name=".$array_name." width='100%'>";
foreach ($stat_array as $thiscat)
{
echo '<option value="' . $thiscat[$query_field_id] . '"';
$list_box_array=get_sub_dataset($server_name,$user_name,$password,$database_name,$query_string2,$sub _field_id);
foreach ($list_box_array as $list_box_row)
{
if ($list_box_row[$sub_field_id] == $thiscat[$query_field_id]) {echo ' selected';}
}
echo '>' . $thiscat[$field_name] . '</option>';
}
}
echo "</select>";
$db1->close;
}
//-------------------------------------------------------------------------------------------------------------------------
function simple_drop_down($drop_down_name,$field_id,$field_name,$server_name, $user_name, $password, $database_name, $query_string, $none, $default_value) {
//-----------------------------------------------------------------------------------------------------------------------------------
echo "<select name=".$drop_down_name.">";
$stat_array=get_dataset($server_name, $user_name, $password, $database_name,$query_string);
if ($none == 1) {echo "<option>None Selected</option>";}
foreach ($stat_array as $thisstat){
echo "<option value=\"".$thisstat[$field_id]."\"";
if ($default_value == $thisstat[$field_id]) {echo ' selected';}
echo ">".$thisstat[$field_name]."</option>";
}
echo "</select>";
}
$db1->close;
//-----------------------------------------------------------------------------------------------------------------------------------
?>
Re: Very slow running code....
Hard to say exactly which part is taking the most time.
Some things I would change:
— It looks like you're opening multiple connections to the database. You should open the connection once, at the start of the script.
— There should be no need to create an array from the query result. Insetad, just call fetch_assoc where you actually need the results.
— I would avoid using echo to emit HTML: just close the PHP tags to return to HTML mode. Keep the business logic (PHP) as separate from the presentation (HTML/CSS) as possible.
If you still have the problem after making these changes, post the code that calls the functions as well. It's hard to diagnose without seeing the full script.
Re: Very slow running code....
Ok, now I think I've confused myself! I tried to do what you said about only having one connection at the start, then closing it at the end, but I've done something wrong.
Here is the code:
<?php
//----------------------------------------------------------------------
$server_name='xxx';
$user_name='xxx';
$password='xxx';
$database_name='xxx';
$db1 = new mysqli($server_name, $user_name, $password, $database_name);
//----------------------------------------------------------------------
function db_result_to_array($result){
//---------------------------------------------------------------------
$res_array = array();
for ($count=0; $row = $result->fetch_assoc(); $count++) {
$res_array[$count]=$row;
}
return $res_array;
}
//---------------------------------------------------------------------
function get_dataset($db1, $query7) {
//---------------------------------------------------------------------
$query1 = $query7;
$result1 = $db1->query($query1);
$num_results = $result1->num_rows;
//$result8 = db_result_to_array($result1);
return $result1;
}
//---------------------------------------------------------------------
function get_sub_dataset($db1, $query7,$sub_field_id) {
//---------------------------------------------------------------------
$query1 = $query7;
$result1 = $db1->query($query1);
$num_results = $result1->num_rows;
//$result8 = db_result_to_array($result1);
return $result1;}
//---------------------------------------------------------------------
function drop_down($array_name,$field_id, $query_field_id, $field_name,$db1,$query_string,$row,$multiple=0,$query_string2=0,$sub_field_id=0) {
//-------------------------------------------------------------------------------------------------------------------------
$stat_array=get_dataset($db1,$query_string);
//echo "<select name=".$array_name." width='100%'>";
if(empty($multiple)) {
echo "<select name=".$array_name." width='100%'>";
if ($row[$field_id] == $thiscat[$query_field_id]) {}{echo "<option>None Selected</option>";}
foreach ($stat_array as $thiscat)
{
echo '<option value="' . $thiscat[$query_field_id] . '"';
if ($row[$field_id] == $thiscat[$query_field_id]) {echo ' selected';}
echo '>' . $thiscat[$field_name] . '</option>';
}
}
else {echo "<select multiple size =".$multiple." name=".$array_name." width='100%'>";
foreach ($stat_array as $thiscat)
{
echo '<option value="' . $thiscat[$query_field_id] . '"';
$list_box_array=get_sub_dataset($db1,$query_string2,$sub_field_id);
foreach ($list_box_array as $list_box_row)
{
if ($list_box_row[$sub_field_id] == $thiscat[$query_field_id]) {echo ' selected';}
}
echo '>' . $thiscat[$field_name] . '</option>';
}
}
echo "</select>";
}
//-------------------------------------------------------------------------------------------------------------------------
function simple_drop_down($drop_down_name,$field_id,$field_name,$db1, $query_string, $none=0, $default_value=0) {
//-----------------------------------------------------------------------------------------------------------------------------------
echo "<select name=".$drop_down_name.">";
$stat_array=get_dataset($db1,$query_string);
if ($none == 1) {echo "<option>None Selected</option>";}
foreach ($stat_array as $thisstat){
echo "<option value=\"".$thisstat[$field_id]."\"";
if ($default_value == $thisstat[$field_id]) {echo ' selected';}
echo ">".$thisstat[$field_name]."</option>";
}
echo "</select>";
}
//-----------------------------------------------------------------------------------------------------------------------------------
$db1->close;
?>
This doesn't seem to return any data to the drop down boxes :-(
If I uncomment the line:
//$result8 = db_result_to_array($result1);
and then return $result8;
Then I get the following when I view the html source:
<html>
<head>
<title>Issues- Reduced View</title>
</head>
<body marginheight="0" topmargin="0" marginwidth="0"
leftmargin="0" style="margin:0;padding:0" bgcolor="#B0E0E6">
<h1></h1>
<form action="reduced_view_issues.php" method="post">
<table border="0"; style="border-collapse:collapse; border:solid 1px black"; bgcolor='#C0C0C0' width=100%>
<tr bgcolor='#C0C0C0' width=100%>
<td align='center'>
<table style="border-collapse:collapse; border:solid 1px #C0C0C0"; bgcolor='#C0C0C0'>
<tr bgcolor='#C0C0C0' width=100%>
<td style="width: auto">
Select Sub Sector
<select name=ddsub_sector_id><br />
<b>Fatal error</b>: Call to a member function fetch_assoc() on a non-object in <b>C:\Program Files (x86)\Apache Software Foundation\Apache2.2\htdocs\Blue_Ocean\functions.php</b> on line <b>15</b><br />
I presume therefore the problem lies with the following:
function db_result_to_array($result){
//---------------------------------------------------------------------
$res_array = array();
for ($count=0; $row = $result->fetch_assoc(); $count++) {
$res_array[$count]=$row;
}
return $res_array;
}
//---------------------------------------------------------------------
This used to work, but now doesn't, so I guess I need a new way of returning the data.
If the best way is to not use an array like you say, are you able to advise how I can change my code to allow this?
Re: Very slow running code....
You need to import $db1 in the functions:
The code you have should produce error messages. Do you have them turned off?
PHP Code:
error_reporting(E_ALL);
I'm at work now, but I'll look at the the rest of your new code a bit later, or someone else might.
Re: Very slow running code....
I tried using the global, but that unfortunately doesn't seem to make any difference :-(
Re: Very slow running code....
Ok...all other problems aside...the key issue here (I think) is that now I'm only using the following line once:
$db1 = new mysqli($server_name, $user_name, $password, $database_name);
Only the first result set shows....
How can I get around this?
