By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,687 Members | 2,046 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,687 IT Pros & Developers. It's quick & easy.

PHP Date Search Question

P: n/a
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?

This page calls the script datersearch.php

************
searchrdates.php
************

<html>
<head><title>Search Repair Dates</title>
</head>
<body bgcolor=#ffffff>
<center>
<P><CENTER><IMG SRC='images/MCLogo.jpg'
WIDTH='576' HEIGHT='87' NATURALSIZEFLAG='0'
ALIGN='BOTTOM'></CENTER></P>
<font face="Tahoma" size="3"><i><b>202 Harper Ave. NW Lenoir, NC
28645</b></i></font>
<br>
<br>
<br>
<br>
<input type="button" style="font-style:tahoma; font-size:10px;
font-weight:bold; color:white; background-color:#0000FF;
height:20px;"onclick="location.href="displayupdate .php'" value="Update
Repair Entry">
<input type="button" style="font-style:tahoma; font-size:10px;
font-weight:bold; color:white; background-color:#0000FF;
height:20px;"onclick="location.href="displayupdate .php'" value="Display
Repair Entries">
<input type="button" style="font-style:tahoma; font-size:10px;
font-weight:bold; color:white; background-color:#0000FF;
height:20px;"onclick="location.href="searchrdates. php'" value="Search
Repair Entries">
<input type="button" style="font-style:tahoma; font-size:10px;
font-weight:bold; color:white; background-color:#0000FF;
height:20px;"onclick="location.href="index.php'" value="Home">
<br>
<br>
<br>
<font face="Tahoma" size=3><b>Search Repairs By Date</b></font>
<br>
<i>(Example of Date Format: Jun 1, 2005)</i>
<table>
<tr>
<form method="post" action="datersearch.php">
<td>From This Date:</td>
<td><input name="from" type="text" id="from"></td>
<td></td></tr>
<tr><td>Back To This Date:</td>
<td><input name="to" type="text" id="to"></td>
<td></td></tr>
<tr><td>For Repairman:</td>
<td><SELECT NAME="name">
<OPTION VALUE="Lisa Creamer" SELECTED>Lisa Creamer
<OPTION VALUE="Jim Stone">Jim Stone
<OPTION VALUE="Rick Stapleton">Rick Stapleton
<OPTION VALUE="Eddie Little">Eddie Little
<OPTION VALUE="Brandon Miller">Brandon Miller
</td>
<td></td></tr>
<td></td>
<td><input type="submit" style="font-style:tahoma; font-size:10px;
font-weight:bold; color:white; background-color:#0000FF; height:22px;"
value="Search">
</td><td></td></tr>
</table>
<?
include("footer.php";
?>
</form>
</body>
</center>
</html>

***************
datersearch.php
***************

<?php
$search = $_REQUEST['name'];
$from = $_REQUEST['from'];
$to = $_REQUEST['to];
if($search) // perform search only if a string was entered.
{
mysql_connect("192.168.1.8","root","passwordhere") ;
mysql_select_db("repair"); //database name
$srch = "%".$search."%";
$query = "SELECT * FROM repair WHERE repairman LIKE '$srch' AND
daterepaired BETWEEN '$from' AND '$to'";
$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_error());
$addtotal = mysql_db_query("repair", $total) or die("MySQL error
#".mysql_errno().":".mysql_error());
if ($result)
{
echo "<font face='Tahoma' size=2><P><CENTER><IMG SRC='images/MCLogo.jpg
WIDTH='576' HEIGHT='87' NATURALSIZEFLAG='0'
ALIGN='BOTTOM'></CENTER></P></font>
<br>
<br>
echo "<table border=0 cellpadding=3 cellspacing=5>
<tr bgcolor=#104E8B>
<td width=10><font size=2 color=#FFFF00
face=tahomo><b>ID</b></font></td>
<td width=10><font size=2 color=#FFFF00 face=tahomo><b>DATE
REPAIRED</b></font></td>
<td width=12><font size=2 color=#FFFF00 face=tahomo><b>TICKET
NUMBER</b></font></td>
<td width=10><font size=2 color=#FFFF00
face=tahomo><b>REPAIRMAN</b></font></td>
<td width=10><font size=2 color=#FFFF00 face=tahomo><b>DATE
RECEIVED</b></font></td>
<td width=15><font size=2 color=#FFFF00
face=tahomo><b>LOCATION</b></font></td>
<td width=10><font size=2 color=#FFFF00
face=tahomo><b>PRICE</b></font></td>
<td width=10><font size=2 color=#FFFF00
face=tahomo><b>M&R</b></font></td>
</tr>;

while ($r = mysql_fetch_array($result)) {//Begin while
$id = $r["id"];
$daterepaired = $r["daterepaired"];
$ticketnumber = $r["ticketnumber"];
$repairman = $r["repairman"];
$datereceived = $r["datereceived"];
$location = $r["location"];
$price = $r["price"];
$mr = $r["mr"];
echo "<tr bgcolor-669966>
<td>$id</td>
<td>$daterepaired</td>
<td>$ticketnumber</td>
<td>$repairman</td>
<td>$datereceived</td>
<td>$location</td>
<td>$price</td>
<td>$mr</td>
</tr>";
} // end while
echo "</table>";
}
while ($t = mysql_fetch_array($addtotal)) {// Begin while
$price = $t["price"];
$commission = $price*.06;
echo "<table><tr>
<td><font size=4>Repairs for $repairman for the date range $to to $from
are: <b>$ $price</b></font></td></tr>
<tr><td><font size=4>and commission is: <b>$
$commission</b></font></td></tr></table>";
}
} else{
echo "Search field is empty. <br> Go back and enter something to search
by or click Display to view all repairs.";
}
?>
<?
include("footer.php";
?>

Ok, that's my code. It pulls info by the repairman but pulls all dates
no matter what dates are in the "to" and "from". I'm new at this and am
probably doing something stupid. Thanks in advance for your help.

Greg

Jul 17 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Greg (gr**@basssax.com) decided we needed to hear...
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? <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.
<?php
$search = $_REQUEST['name'];
$from = $_REQUEST['from'];
$to = $_REQUEST['to];
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?
if($search) // perform search only if a string was entered.
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.
{
mysql_connect("192.168.1.8","root","passwordhere") ;
mysql_select_db("repair"); //database name
$srch = "%".$search."%";
$query = "SELECT * FROM repair WHERE repairman LIKE '$srch' AND
daterepaired BETWEEN '$from' AND '$to'";
from and to are not validated to it would be easy for someone to
delete your data via SQL-injection.
$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_error());
You've already selected the repair database above, so you might as
well use mysql_query() instead.
$addtotal = mysql_db_query("repair", $total) or die("MySQL error
#".mysql_errno().":".mysql_error());
if ($result)
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.
{
echo "<font face='Tahoma' size=2><P><CENTER><IMG SRC='images/MCLogo.jpg
WIDTH='576' HEIGHT='87' NATURALSIZEFLAG='0'
ALIGN='BOTTOM'></CENTER></P></font>
<br>
<br>
echo "<table border=0 cellpadding=3 cellspacing=5> <snip some code> <td width=10><font size=2 color=#FFFF00
face=tahomo><b>M&R</b></font></td>
</tr>;
There is a " missing in the line above.

while ($r = mysql_fetch_array($result)) {//Begin while <snip some code> <?
include("footer.php";
There is a ) missing above. Did you copy/paste or retype this code? It
won't run at all in its present form.
?>

<snip>

--
Dave <da**@REMOVEbundook.com>
(Remove REMOVE for email address)
Jul 17 '05 #2

P: n/a
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 <mi****@gmail.com>
Tel: +40745515505/+40723573761

Jul 17 '05 #3

P: n/a
Alex (mi****@gmail.com) decided we needed to hear...
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.
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.
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.


I agree, thats a much better way than expecting users to type in a
date - and expecting them not to make mistakes ;)
--
Dave <da**@REMOVEbundook.com>
(Remove REMOVE for email address)
Jul 17 '05 #4

P: n/a
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

Jul 17 '05 #5

P: n/a
Greg (gr**@basssax.com) decided we needed to hear...
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


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 -
http://ca.php.net/manual/en/function.strtotime.php

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 <da**@REMOVEbundook.com>
(Remove REMOVE for email address)
Jul 17 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.