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:

PHP Code:

<?

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.

PHP Code:

<?

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";
    }


}

?>