Problem with prepared statement....2 variables with same name

I have this SQL statement which I want to run:

select * from holidays WHERE holid_date >=(?)
                 and holid_date <=(?)'

The above statement just selects a range of dates between a beg_date and an end_date…
The problem is what variables to put inside bind_param and bind_result

For instance what am I going to put inside bind_result…the asterisk?
And what about bind_param…both parameters here have the same name.

  1. you could use the clearer between and condition: … WHERE holid_date BETWEEN ? AND ?

  2. bind_param(): you obviously need the start end end dates

  3. that’s why you shouldn’t use the asterisk. name the fields you want to retrieve and then you have the sequence of bound result variables.

Did you mean something like this:

SELECT FROM holidays WHERE holid_date BETWEEN '2015-01-01' AND '2015-04-10';

Because if you did…I have to inform you that I get a syntax error message

...near 'FROM holidays WHERE holid_date BETWEEN '2015-01-01' AND '2015-04-10''...

The error points to a place just before FROM not into the WHERE clause.

I do not disagree with what you say about the error…which make me think that maybe the asterisk is inevitable.

you should list all the fields you want to have in the result. that’s inevitable.

Maybe use named parameters instead of question marks? Like this

$stmt = $dbh->prepare("SELECT * FROM holidays WHERE holid_date BETWEEN :begin_date AND :end_date");
$stmt->bindParam(':begin_date', $begin_date);
$stmt->bindParam(':end_date', $end_date);

Or if you wanna do it in the question mark way…

$stmt = $dbh->prepare("SELECT * FROM holidays WHERE holid_date BETWEEN ? AND ?");
$stmt->bindParam(1, $begin_date);
$stmt->bindParam(2, $end_date);

what about $stmt->bind_result();…What am I going to put inside the parentheses?

that only works with PDO.

what the Manual tells you to put in there:

True that. Didn’t notice what system OP was using.

there is still a problem with bindparam…I do not use PDO and as such the above code is useless.

If you don’t show us the problem we cannot help.

Ok…in short…here is the code that I tried:

stmt = $connection->prepare('select * from holidays WHERE holid_date >=(?)
                                        and holid_date <=(?)'))

And here is the error message…
Warning: mysqli_stmt::bind_result(): Number of bind variables doesn’t match number of fields in prepared statement in C:\Apache24\htdocs\Appointments\Administrator\admin_db_code.php on line 398

Let us focus on this…

that error is quite simple. the statement returns all fields of the table, but you give it only one variable to hold all these values.

another reason to not use SELECT *.

then what to use,still do not get it…sorry…

do something like SELECT id FROM table for whatever fields you have in your table and you need in that query.

I don’t believe you should have parenthesis around your placeholders.

$stmt = $connection->prepare("select * from holidays WHERE holid_date >= ?
                                        and holid_date <= ?");

AND make sure $stmt is unique on the page. If you have another query using $stmt that also has bind_param you will have a problem. Use unique variables, e.g. $stmt1, $stmt2 etc.

yes this is better…targeting a specific column…instead of using an asterisk.

I have another problem now…instead of getting back 4 dates I get 3…the first date is skipped.

If I run the query separate in the IDE(Netbeans) I get all 4 dates…but when run by PHP and the prepared statement the first date is skipped.

it depends on your used code.