PHP Date Search Question

**Dave** · Jul 17 '05, 01:29 PM

Re: PHP Date Search Question

Greg (greg@basssax.c om) decided we needed to hear...[color=blue]
> I have a page that searches a database by a repairman's name and by a
> date range. It pulls info by the repairman's name but pulls all info in
> the database regardless of the date. Below is the code of the two
> pages. What am I missing?[/color]
<snip>

I've noted a few problems below - there may be more but I only gave it
a fairly quick look.

Lack of indentation makes your code hard to read, and all those
multi-line echo with all the HTML make matters worse IMO.

Your queries look like they are valid, but without seeing the table
definition, and samples of your input, its tough to guess why all dates
and not those you expect are returned.
[color=blue]
> <?php
> $search = $_REQUEST['name'];
> $from = $_REQUEST['from'];
> $to = $_REQUEST['to];[/color]

You don't validate any of the above vars. What happens if someone
enters invalid dates or other nonesense instead of dates? What
happens if from date comes after to date?
[color=blue]
> if($search) // perform search only if a string was entered.[/color]

It is best to use isset on your $_REQUEST variables, *then* validate
and use their contents.
You use $_REQUEST which gives both GET and POST variables, but your
form is set to POST. You should really be using $_POST.
[color=blue]
> {
> mysql_connect(" 192.168.1.8","r oot","passwordh ere");
> mysql_select_db ("repair"); //database name
> $srch = "%".$search."%" ;
> $query = "SELECT * FROM repair WHERE repairman LIKE '$srch' AND
> daterepaired BETWEEN '$from' AND '$to'";[/color]

from and to are not validated to it would be easy for someone to
delete your data via SQL-injection.
[color=blue]
> $total = "SELECT SUM(price) as price FROM repair WHERE repairman LIKE
> '$srch' AND daterepaired BETWEEN '$from' AND '$to'";
> $result = mysql_db_query( "repair", $query) or die("MySQL error
> #".mysql_errno( ).":".mysql_err or());[/color]

You've already selected the repair database above, so you might as
well use mysql_query() instead.
[color=blue]
> $addtotal = mysql_db_query( "repair", $total) or die("MySQL error
> #".mysql_errno( ).":".mysql_err or());
> if ($result)[/color]

Consider testing mysql_num_rows( ). You've already determined that the
query worked (it dies on error), so you may as well proceed based on
number of rows returned.
[color=blue]
> {
> echo "<CENT ER><IMG SRC='images/MCLogo.jpg
> WIDTH='576' HEIGHT='87' NATURALSIZEFLAG ='0'
> ALIGN='BOTTOM'> </CENTER>
> 
> 
> echo "<table border=0 cellpadding=3 cellspacing=5>[/color]
<snip some code>[color=blue]
> <td width=10> face=tahomo> M&R</td>
> </tr>;[/color]

There is a " missing in the line above.
[color=blue]
>
> while ($r = mysql_fetch_arr ay($result)) {//Begin while[/color]
<snip some code>[color=blue]
> <?
> include("footer .php";[/color]

There is a ) missing above. Did you copy/paste or retype this code? It
won't run at all in its present form.
[color=blue]
> ?>[/color]
<snip>

--
Dave <dave@REMOVEbun dook.com>
(Remove REMOVE for email address)

**Alex** · Jul 17 '05, 01:29 PM

Re: PHP Date Search Question

You forgot to tell him that if he uses text inputs for dates he would
have to use strtotime function or something like that because if you do
a query like SELECT * FROM test WHERE date='Jan 1 2005' mysql would
never return what you want ... or use postgresql :D.
The mysql data format is this 2005-12-31 and the dates are compared as
strings so it wouldn't convert 'Jan 1 2005' to '2005-01-01'
automatically.

I would tell him to use 3 selects and compose the date like
'{$_POST['to_year']}-{$_POST['to_month']}-{$_POST['to_day']}' because
even if he uses strtotime it wouldn't cover any date syntax.

--
Alexandru Mincu <mincua@gmail.c om>
Tel: +40745515505/+40723573761

**Dave** · Jul 17 '05, 01:29 PM

Re: PHP Date Search Question

Alex (mincua@gmail.c om) decided we needed to hear...[color=blue]
> You forgot to tell him that if he uses text inputs for dates he would
> have to use strtotime function or something like that because if you do
> a query like SELECT * FROM test WHERE date='Jan 1 2005' mysql would
> never return what you want ... or use postgresql :D.
> The mysql data format is this 2005-12-31 and the dates are compared as
> strings so it wouldn't convert 'Jan 1 2005' to '2005-01-01'
> automatically.[/color]

Exactly. Thats why I explained to the OP that it wasn't possible to
guess what was going on with the select without seeing what the
datatypes were in the table definition, and some of the sample inputs
from the form.[color=blue]
>
> I would tell him to use 3 selects and compose the date like
> '{$_POST['to_year']}-{$_POST['to_month']}-{$_POST['to_day']}' because
> even if he uses strtotime it wouldn't cover any date syntax.[/color]

I agree, thats a much better way than expecting users to type in a
date - and expecting them not to make mistakes ;)
--
Dave <dave@REMOVEbun dook.com>
(Remove REMOVE for email address)

**Greg** · Jul 17 '05, 01:29 PM

Re: PHP Date Search Question

I retyped the code, therefore the typo's. Thanks to both of you for
your suggestions. I changed the date columns in the db and that did the
trick. All is good now. I've set all of the date fields to
automatically fill in so the employees will not have to type them. I'm
new to php so I don't understand "validating vars" and "testing
mysql_num_rows( )" yet. Got time for the readers digest version on how
to do this or a suggestion on where to find a good tutorial?

Thanks for your help,

Greg

**Dave** · Jul 17 '05, 01:30 PM

Re: PHP Date Search Question

Greg (greg@basssax.c om) decided we needed to hear...[color=blue]
> I retyped the code, therefore the typo's. Thanks to both of you for
> your suggestions. I changed the date columns in the db and that did the
> trick. All is good now. I've set all of the date fields to
> automatically fill in so the employees will not have to type them. I'm
> new to php so I don't understand "validating vars" and "testing
> mysql_num_rows( )" yet. Got time for the readers digest version on how
> to do this or a suggestion on where to find a good tutorial?
>
> Thanks for your help,
>
> Greg[/color]

Glad its working for you now.

By validating vars, I meant checking (for example) that a variable you
expect to contain a valid date, actually does contain a valid date and
not some other nonesense value that would break your code. You can do
this pretty much any way you like with if statements, string and number
functions, date functions etc. For the valid date example, you might
start by checking out the strtotime function which tries to parse a
date and lets you know if it could or not -

PHP: strtotime - Manual

http://ca.php.net/manual/en/function.strtotime.php

Parse about any English textual datetime description into a Unix timestamp

mysql_num_rows( ) returns the number of rows selected in the most
recently executed query. Your testing of $result didn't accomplish
much because by that point in the code your query had already worked
(your program dies if it did not). Because you know the query worked,
what you really need to know at this point is were any rows returned
or not. Thats where mysql_num_rows( ) comes in.

HTH
--
Dave <dave@REMOVEbun dook.com>
(Remove REMOVE for email address)

PHP Date Search Question

PHP Date Search Question

Comment

Comment

Comment

Comment

Comment