Query times out

[kilbey1]

Not sure if this is better for the 'coding' area or a critique. I had much help with this but my query still times out with such large tables. I get the message:
This server is currently overloaded - please try again later

Obviously, this needs to be optimized; I tested this entering only a Social Security number.

The query in question:


<?

function results() {

$aWhere = array();
if($_POST['Claimno'] != '')
$aWhere[] = 'claimrecord.CRclaimNumber = "' . $_POST['Claimno'] . '"';
if($_POST['PTssn'] != '')
$aWhere[] = 'patient.PTssn = "' . $_POST['PTssn'] . '"';
if($_POST['PTnameLast'] != '')
$aWhere[] = 'patient.PTnameLast = "' . $_POST['PTnameLast'] . '"';
if($_POST['PTnameFirst'] != '')
$aWhere[] = 'patient.PTnameFirst = "' . $_POST['PTnameFirst'] . '"';
//test if date range was specified in the form
if($_POST['start_date'] && $_POST['end_date'] != '')
$aWhere[] = 'eosdetail.SDserviceDate BETWEEN "' . $_POST['start_date'] . '" AND "' . $_POST['end_date'] . '"';


// build where clause
if($sWhere == 'WHERE ') {
$sWhere .= ' AND eosdetail.SDserviceDate BETWEEN "' . $_POST['start_date'] . '" AND "' . $_POST['end_date'] . '"';
} else {
$sWhere = 'WHERE ' . implode(' AND ', $aWhere);
}

// Where clause done - add it to the SQL skeleton
$sql = "SELECT claimrecord.CRclaimNumber, patient.PTssn, patient.PTnameFirst, patient.PTnameLast, eosdetail.SDserviceDate
FROM claimrecord, patient, eosdetail " . $sWhere;

echo $sql . "<P>";
$result = mysql_query($sql) or die(mysql_error());

$num_rows = mysql_num_rows($result);

if($num_rows == 0) {
echo "<p>No results returned for this query.<P>";
} else {
echo "<P>found something";
}


}

?>

[kilbey1]

I have improved this, to force a constructed query based ONLY on what the user picks in the form.


<?

function results() {

//set up SELECT statement
$aSelect = array();
if($_POST['Claimno'] != '')
$aSelect[] = 'claimrecord.CRclaimNumber';
if($_POST['PTssn'] != '')
$aSelect[] = 'patient.PTssn';
if($_POST['PTnameLast'] != '')
$aSelect[] = 'patient.PTnameLast';
if($_POST['PTnameFirst'] != '')
$aSelect[] = 'patient.PTnameFirst';
//test if date range was specified in the form
if($_POST['start_date'] && $_POST['end_date'] != '')
$aSelect[] = 'eosdetail.SDserviceDate';

//set up FROM portion of sql statement
$aFrom = array();
if($_POST['Claimno'] != '')
$aFrom[] = 'claimrecord';
if($_POST['PTssn'] != '')
$aFrom[] = 'patient';
if($_POST['PTnameLast'] != '')
$aFrom[] = 'patient';
if($_POST['PTnameFirst'] != '')
$aFrom[] = 'patient';
//test if date range was specified in the form
if($_POST['start_date'] && $_POST['end_date'] != '')
$aFrom[] = 'eosdetail';

$resultFrom = array_unique($aFrom);

//set up WHERE portion of sql statement
$aWhere = array();
if($_POST['Claimno'] != '')
$aWhere[] = 'claimrecord.CRclaimNumber = "' . $_POST['Claimno'] . '"';
if($_POST['PTssn'] != '')
$aWhere[] = 'patient.PTssn = "' . $_POST['PTssn'] . '"';
if($_POST['PTnameLast'] != '')
$aWhere[] = 'patient.PTnameLast = "' . $_POST['PTnameLast'] . '"';
if($_POST['PTnameFirst'] != '')
$aWhere[] = 'patient.PTnameFirst = "' . $_POST['PTnameFirst'] . '"';
//test if date range was specified in the form
if($_POST['start_date'] && $_POST['end_date'] != '')
$aWhere[] = 'eosdetail.SDserviceDate BETWEEN "' . $_POST['start_date'] . '" AND "' . $_POST['end_date'] . '"';

//build SELECT clause
$sSelect = 'SELECT ' . implode(', ', $aSelect);

// build FROM clause
$sFrom = 'FROM ' . implode(', ', $resultFrom);

// build where clause
if($sWhere == 'WHERE ') {
$sWhere .= ' AND eosdetail.SDserviceDate BETWEEN "' . $_POST['start_date'] . '" AND "' . $_POST['end_date'] . '"';
} else {
$sWhere = 'WHERE ' . implode(' AND ', $aWhere);
}

// Select, Where and From clause done - add it to the SQL skeleton
$sql = "" . $sSelect . " " . $sFrom . " ". $sWhere;

//echo $sql . "<P>";
$result = mysql_query($sql) or die(mysql_error());

$num_rows = mysql_num_rows($result);

if($num_rows == 0) {
echo "<p>No results returned for this query.<P>";
} else {
echo "<P>found something";
}


}

?>