Forum Moderators: coopster
<html>
<head>
<title>AME AAD FILES</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<h2 align="center">First AME Database Testing Using PHP and MySQL</h2
>
<table align="center" border="1">
<tr>
<th>#</th>
<th>BUNO</th>
<th>DATE</th>
<th>Hour-Minute</th>
<th>DOWNLOAD</th>
</tr>
<?php
# Bring in db connection code
include("aad_db.php");
# Define SQL query
$q = "SELECT * FROM aad";
# Send query to MySQL and store results in buffer named $r
if($r=mysql_query($q,$db))
{
# Loop through result set
while ($d=mysql_fetch_assoc($r))
{
# Print out each dynamic HTML row, NOTE: Files must be in the proper folder
printf("<tr>\n <td>%s</td>\n", $d['num']);
printf("<td>%s</td>\n", $d['bn']);
printf("<td>%s</td>\n", $d['dt']);
printf("<td>%s</td>\n", $d['hhmm']);
printf("<td><a href=E:/AME/%s>download</a></td>\n</tr>\n", $d['aad']);
}
}
?>
</table>
</body>
</html>
What you want to use is the WHERE clause [dev.mysql.com]
$q = "SELECT * FROM aad WHERE bn=165655 AND (dt > '2003-10-08' AND dt < '2004-01-15')";
The year usually comes first in the date field.
Hope that makes sense,
Tim
Sincerely,
Roger
Take some time understanding this thread on the basics of extracting data for mysql [webmasterworld.com]. It shows you how to make a form and build a query from it. Let me know if you run into any roadblocks.
Tim
Roger
-Roger
php code is as follows:
<html>
<head>
<title>AAD BUNO SEARCH</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<form method="post" action="<?php print $_SERVER['../PHP_SELF'];?>">
Search for BUNO:
<input type="text" name="s" value="<?php print $_POST['s'];?>"><br>
<input type="submit" name="submit" value="Search">
</form>
<?php
# Create a local variable with form input
$s = $_POST['s'];
#Is there a value in $s?
if ($s)
{
print "<hr>\n";
# Connect to database
include("aad_db.php");
# Create an array of $words from search input string $s
# $words = split(" ", $s);
$words = split("[; ,.]+", $s);
# Define a query
$q = "SELECT * FROM aad WHERE ";
#work with each element of the array #words
foreach ($words as $w)
{
$q .= "(bn LIKE '%$w%')";
}
$r = mysql_query($q, $db) or die("Query Failed:<br>$q<br><br>" . mysql_error());
while ($bn = mysql_fetch_array($r, MYSQL_ASSOC))
{
extract ($bn);
print "<a href=myaadsingle.php?bn=$bn>";
print "$bn - STRIPID: ($aad)";
print "</a><br>\n";
}
}
?>
</body>
</html>
Dates are such a pain ;-)
So probably the easiest thing to do is make some drop down boxes. You could get a lot fancier with some javascript. There's plenty of canned pop up calendars on the net to do this.
But back to your selects. One for the start month like so...
<select name="start_month" size="1">
<option value="1" SELECTED>January</option>
<option value="2">February</option>
<option value="3">March</option>
<option value="4">April</option>
<option value="5">May</option>
<option value="6">June</option>
<option value="7">July</option>
<option value="8">August</option>
<option value="9">September</option>
<option value="10">October</option>
<option value="11">November</option>
<option value="12">December</option>
</select>
Do something similar for the day and one for the year. The same for the end date.
Now your post data with have these variables which you can use in you sql statement.
$start_date = $_POST['start_year'] . "-" . $_POST['start_month'] . "-" . $_POST['start_day'];
same for $end_date
$q = "SELECT * FROM aad WHERE bn=165655 AND (dt > $start_date AND dt < $end_date)";
Well that's one way to do it.
And before you made the drop-down list suggestion, I had this code working: Pls. excuse the way it was written; I'm still very new at this...
<snip>code dump</snip>
<form method="post" action="<?php print $_SERVER['../PHP_SELF'];?>">
<b>Enter Bureau Number:</b><input type="text" name="s1" value="<?php print $_POST['s1'];?>" size=10><br>
<b>Enter Date Range:</b><br>(yyyy-mm-dd)
from: <input type="text" name="s2" value="<?php print $_POST['s2'];?>" size=10>
to: <input type="text" name="s3" value="<?php print $_POST['s3'];?>" size=10>
<input name="Submit" type=Submit value="Search"></td></form>
<?php
$s1 = $_POST['s1'];
$s2 = $_POST['s2'];
$s3 = $_POST['s3'];
if (!$s1)
{
print "Please Enter a Bureau Number...";
}
elseif (strlen($s1)!== 6)
{
print "Sorry! $s1 is not a valid BUNO.";
}
else
{
if ($s1)
{
if ($s2)
{
if ($s3)
{
print "<hr>\n";
print "<b>Search Results</b> (click on ADD file to download)";
print "<br>\n";
print "<br>\n";
include("aad_db.php");
$word1 = split("[; ,.]+", $s1);
$word2 = split("[; ,.]+", $s2);
$word3 = split("[; ,.]+", $s3);
$q = "SELECT * FROM aad WHERE ";
foreach ($word1 as $w1)
{
foreach ($word2 as $w2)
{
foreach ($word3 as $w3)
{
$q .= "(bn LIKE '%$w1%') and (dt > '%$w2%' AND dt < '%$w3%' )";
}
}
}
if($r=mysql_query($q,$db))
{
while ($bn = mysql_fetch_array($r, MYSQL_ASSOC))
{
extract ($bn);
print "$bn - STRIPID: ";
print " <a href=I:/AME/$bn/2001/$aad>$aad</a>\n";
print "</a><br>\n";
}
}
}
}
}
}
?>
</body>
</html>
And it works! Well somewhat... I still have to create a "count" script where the results would tell the user how many files (if any) were found; and if none, to say, "No files found" (I need help on that as well...)
Anyway, when I created the dropdown list for the dates, it didn't produce the results as the code above... Could you pls. help in determining what's wrong and maybe refining the code as well (very crude)... sorry.
Here it is:
<snip>code dump</snip>
<?php
# Create a local variable with form input
$s1 = $_POST['s1'];
$s2 = $_POST['start_year'] . "-" . $_POST['start_month'] . "-" . $_POST['start_day'];
$s3 = $_POST['end_year'] . "-" . $_POST['end_month'] . "-" . $_POST['end_day'];
if (!$s1)
{
print "Please Enter a Bureau Number...";
}
elseif (strlen($s1)!== 6)
{
print "Sorry! $s1 is not a valid BUNO.";
}
else
{
if ($s1)
{
if ($s2){
if ($s3){
print "<hr>\n";
print "<b>Search Results</b> (click on ADD file to download)";
print "<br>\n";
print "<br>\n";
include("aad_db.php");
$word1 = split("[; ,.]+", $s1);
$word2 = split("[; ,.]+", $s2);
$word3 = split("[; ,.]+", $s3);
$q = "SELECT * FROM aad WHERE ";
foreach ($word1 as $w1)
{
foreach ($word2 as $w2){
foreach ($word3 as $w3){
#$q .= "(bn LIKE '%$w1%') and (dt > $s2 AND dt < $s3 )";
$q .= "(bn LIKE '%$w1%') and (dt > '%$w2%' AND dt < '%$w3%' )";
}
}
}
print "$s1, $s2, $s3<br>\n";
print "$w1, $w2, $w3";
if($r=mysql_query($q,$db))
{
while ($bn = mysql_fetch_array($r, MYSQL_ASSOC))
{
extract ($bn);
print "$bn - STRIPID: ";
print " <a href=I:/AME/$bn/2001/$aad>$aad</a>\n";
print "</a><br>\n";
}
}
}
}
}
}
?>
<form method="post" action="<?php print $_SERVER['../PHP_SELF'];?>">
<b>Enter Bureau Number:</b><input type="text" name="s1" value="<?php print $_POST['s1'];?>" size=10><br>
<b>Enter Date Range:</b>
<br>FROM <input type="hidden" name="s2" value="<?php print $_POST['s2'];?>" size=10>
<select name="start_month" size="1">
<option value="01" selected>January</option>
<option value="02">February</option>
<snip>code dump</snip></select>
<select name="start_day" size="1">
<option value="01" selected>01</option>
<snip>code dump</snip>
</select>
<select name="start_year" size="1">
<option value="1988">1988</option>
<snip>code dump</snip>
<option value="2003">2003</option>
<option value="2004" selected>2004</option>
</select>
<br>TO: <input type="hidden" name="s3" value="<?php print $_POST['s3'];?>" size=10>
<select name="end_month" size="1">
<option value="01">January</option>
<snip>code dump</snip>
<option value="12">December</option></select>
<select name="end_day" size="1">
<option value="01">01</option>
<option value="02">02</option>
<snip>code dump</snip>
</select>
<select name="end_year" size="1">
<option value="1988">1988</option>
<snip>code dump</snip>
<option value="2004" selected>2004</option>
</select>
<br><input type=Submit value="Search"></form>
THANKS IN ADVANCE!
[edited by: ergophobe at 10:50 pm (utc) on Aug. 18, 2004]
[edit reason] Code dump snipped [/edit]
$word1 = split("[; ,.]+", $s1);
$word2 = split("[; ,.]+", $s2);
$word3 = split("[; ,.]+", $s3);$q = "SELECT * FROM aad WHERE ";
foreach ($word1 as $w1)
{
foreach ($word2 as $w2){
foreach ($word3 as $w3){
#$q .= "(bn LIKE '%$w1%') and (dt > $s2 AND dt < $s3 )";
$q .= "(bn LIKE '%$w1%') and (dt > '%$w2%' AND dt < '%$w3%' )";
}
}
}
I'd just get rid of all the word variables and the foreach loops. Let's take it back to the line you commented out and put some single quotes in around $s2 and $s3.
$q .= "(bn LIKE '%$w1%') and (dt > '$s2' AND dt < '$s3')";
I'm assuming here your dt field is of type 'date'.
Another debugging trick is to print your sql statement and make sure it looks like what you want.
$q .= "(bn LIKE '%$w1%') and (dt > '$s2' AND dt < '$s3')";
but it didn't return any results...
and yes, dt is a date field...
any further assistance is much appreciated. Sorry for the mess.
SELECT * FROM aad WHERE (bn LIKE '%162888%') and (dt > '2004-01-01' AND dt < '2004-03-01' )
which is what I entered in the list box...
still it won't execute these lines that worked in the previous code:
if($r=mysql_query($q,$db))
{
while ($bn = mysql_fetch_array($r, MYSQL_ASSOC))
{
extract ($bn);
print "$bn - STRIPID: ";
print " <a href=I:/AME/$bn/2001/$aad>$aad</a>\n";
print "</a><br>\n";
}
}
Thanks again!
$q = "SELECT * FROM aad WHERE dt > '2004-01-01'";
$r=mysql_query($q);
while ($bn = mysql_fetch_array($r))
{
print_r($bn);
}
