如何从SQL db中获取数据，在特定情况下使用PHP，AND＆BETWEEN子句

Question

I'm working on an assignment for school where I have an html form, a php file, and a SQL database. In the html form I have a dropdown that asks for the zipcode, a textfield that asks for the last name, and 2 additional textfields that ask for the range of call lengths (one for minimum, one for maximum). The code is as follows:

<form action="database.php">
<h3 class="prompt1"> Select Your Area Code </h3>
<select name="areaCode">
    <option value="111">111</option>
                       etc...
</select>

<h3 class="prompt1"> Enter Last Name </h3>
<input type="text" size="35" name="lastName">

<h3 class="prompt1"> Select Call Length Range </h3>
<input type="text" size="3" value="min" name="min">–
<input type="text" size="3" value="max" name="max">

    <input type="submit">
</form>

Then a php file reads the data from the form, then it queries a SQL database for rows that fulfill the criteria entered in the form, and then it is supposed to print out the results in a table, but where I am having a problem with is on line 14 of the php file. The code is as follows:

<?php 

require '../../connection.php'; /* connection to db is in another file*/

$min = $_GET['min'];
$max = $_GET['max'];

$sql = "SELECT * FROM project 
    WHERE (areaCode = :areaCode 
    AND lastName = :lastName 
    AND (callLength BETWEEN $min AND $max))";

$stmt = $dataconn -> prepare($sql); 
$stmt -> execute(array(":areaCode"=>$_GET['areaCode'])); /* line 14 */
$results = $stmt->fetchAll();

/* print out $results in a table down here /*

?>

I want to query the database for entire rows that have the area code, last name, and a call length (within the minimum and maximum values) inputed into the html form. I am getting no errors for the SQL query, but I am getting errors when I execute() on line 14. Here I is the error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid         parameter number: number of bound variables does not match number of tokens' in /home/CLASSES/martinezgregoryl/public_html/cst336/assignments/assn6/database.php:14 Stack trace: #0 /home/CLASSES/martinezgregoryl/public_html/cst336/assignments/assn6/database.php(14): PDOStatement->execute(Array) #1 {main} thrown in /home/CLASSES/martinezgregoryl/public_html/cst336/assignments/assn6/database.php on line 14

With the execute statement, I was taught I am supposed to (this is excerpt from a lecture):

1. Execute the statement, replacing id the named parameters from the query: $stmt -> execute ( array ( ':id' => '1') );

So since I queried for areacode, lastName, and a callLength between a min and a max. I am assuming I need to include those parameters in the execute statement, currently I only have the areaCode in the execute statement, but if I include the last name and callLength as well I should have something like (I think):

$stmt -> execute(array(":areaCode"=>$_GET['areaCode'], ":lastName"=>$_GET['lastName'], "callLength"=>...I don't know...));  

The thing is, I don't know how to include the callLength part into the execute statement, since it contained a BETWEEN clause, I can't just put callLength"=>$_GET['callLength']. Any help would be appreciated.

Answer 1

You can either build a query that has the right strings embedded in it and do a direct query without needing to prepare a statement then bind parameters, or use substitution (binding) in the execute() command. It's more confusing when you mix the two. It doesn't matter much which you do in this isolated case, but if you use execute() right then you can keep the query around and use it over and over with different parameters, so in some code that's a big performance win. But as the error message said, if you use substitution then the number of markers or tags in the SQL statement must match the number of parameters in the array passed to execute(). Examples of both:

$sql = "SELECT * FROM project 
    WHERE (areaCode = :areaCode 
    AND lastName = :lastName 
    AND (callLength BETWEEN :min AND :max))";

$stmt = $dataconn -> prepare($sql); 
$stmt -> execute(array(":areaCode"=>$_GET['areaCode'], ":lastName"=>$_GET['lastName'],
    ":min"=>$_GET['min'], ":max"=>$_GET['max']));
$results = $stmt->fetchAll();
/* print out $results in a table down here /*
?>

Or else:

<?php 
require '../../connection.php'; /* connection to db is in another file*/
$min = $_GET['min'];
$max = $_GET['max'];
$areaCode = $_GET['areaCode'];
$lastName = $_GET['lastName'];

$sql = "SELECT * FROM project 
    WHERE (areaCode = $areaCode 
    AND lastName = $lastName 
    AND (callLength BETWEEN $min AND $max))";

$results = $dataconn -> query($sql);
/* print out $results in a table down here /*
?>