Thanks!
-----Original Message-----
From: web400-bounces@xxxxxxxxxxxx [mailto:web400-bounces@xxxxxxxxxxxx] On
Behalf Of Mike Pavlak
Sent: Wednesday, January 13, 2010 11:44 AM
To: Web Enabling the AS400 / iSeries
Subject: Re: [WEB400] SQL Date Range
Shannon,
A quick word about quotes in PHP. The single quote will take the exact
character string while double quotes will interpret the variable. So if
I have the following code:
$x = 5;
$string1 = 'The number of variable x is ' . $x;
$string2 = "The number of variable x is $x";
echo $string1;
echo "<br><br>" . $string2;
//The output looks like this:
The number of variable x is 5
The output will be the same for both string1 and string2. The
difference is double quotes supports variable interpolation. This means
variable are expanded inside the double quotes and ONLY inside double
quotes.
Now addressing your specific issue, I would recommend the following:
Assume the following:
$startDate = '11/01/2009';
$endDate = '11/30/2009';
While your syntax would be correct it would be considered inefficient
because you used both double quotes AND concatenation. I would
recommend that you consider either of these two options:
$sql1 = "SELECT * FROM xmittalheader WHERE XMittalDate BETWEEN
$startDate and $endDate"; ......Or
$sql1 = 'SELECT * FROM xmittalheader WHERE XMittalDate BETWEEN ' .
$startDate . ' and ' . $endDate;
Both of these statements above will set $SQL1 to the following value:
SELECT * FROM xmittalheader WHERE XMittalDate BETWEEN 11/01/2009 and
11/30/2009
Then use the variable $sql1 in your database execution statement:
$results = mysqli_query($db, $sql1);
As you can see, the first example of $sql1 is a little easier to read
and you don't have to do the mental gymnastics of lining up quotes and
concatenation commands. I prefer this method, myself. But the double
quotes will introduce some overhead for the parser and to combine double
quotes and concatenation is inefficient and a poor coding practice in
PHP. In reality, the performance issue is EXTREMELY negligible but
still inefficient. Putting the SQL statement into a character string
also facilitates debugging as you can hover over the variable in the
debugger to see exactly what SQL statement was passed to the query
interpreter. Plus, you can dynamically build complex conditions in your
where clause by putting the SQL in a variable.
This works equally well with DB2, by the way <grin>.
Hope this helps!
Regards,
Mike
mike.p@xxxxxxxx Cell: (408)679-1011 Office: (815)722-3454
Zend Server for IBM i Beta avilable at
http://www.zend.com/en/products/server/zend-server-5-new-ibmi
-----Original Message-----
From: web400-bounces@xxxxxxxxxxxx [mailto:web400-bounces@xxxxxxxxxxxx]
On Behalf Of Shannon ODonnell
Sent: Wednesday, January 13, 2010 8:15 AM
To: 'Web Enabling the AS400 / iSeries'
Subject: [WEB400] SQL Date Range
I want to select a group of dates based on date range in a PHP app from
a
MySQL database.
Assume the table name is xmittalheader and the date field is named:
XMittalDate and is in the format in the MySQL table like this:
11/02/2009
Further assume $startDate and $endDate are being brought into the PHP
via a
POST from a form.
Would this be the correct syntax?
$results = mysqli_query($db, "SELECT * FROM xmittalheader WHERE
XMittalDate
BETWEEN ".$startDate." and ".$endDate);
Thanks!
Shannon O'Donnell
midrange.com
