PHP mysql - extracting specific data depending on user selection

General PHP Help
#1

I have been trying to create a way for a user to select data from a MySQL database depending on the date range they select from drop down lists. The user has to select the start day, month and year and the end day, month and year. The data between these dates is then displayed within a html table. This also means there is 6 drop down menus in total. The code so fare is as follows -

table.altrowstable { font-family: verdana,arial,sans-serif; font-size:11px; color:#333333; border-width: 1px; border-color: #a9c6c9; border-collapse: collapse; text-align: center; } table.altrowstable th { border-width: 1px; padding: 8px; border-style: solid; border-color: #a9c6c9; } table.altrowstable td { border-width: 1px; padding: 8px; border-style: solid; border-color: #a9c6c9; } .oddrowcolor{ background-color:#d4e3e5; } .evenrowcolor{ background-color:#c3dde0; }
<?php $username="root"; $password=""; $database="btg"; $date1 =""; $date2 =""; if (isset($_POST['Submit'])) { $date1 = date('Y-m-d', strtotime(mktime(0,0,0,$_POST['startday'],$_POST['startmonth'],$_POST['startyear']))); $date2 = date('Y-m-d', strtotime(mktime(0,0,0,$_POST['endday'],$_POST['endmonth'],$_POST['endyear']))); } mysql_connect("localhost",$username,$password); @mysql_select_db($database) or die( "Unable to select database"); $result = mysql_query("SELECT * FROM users WHERE corrispondance BETWEEN '$date1' AND '$date2'"); $num=mysql_numrows($result); ?> <?php $i=0; while ($i < $num) { $f1=mysql_result($result,$i,"title"); $f2=mysql_result($result,$i,"firstname"); $f3=mysql_result($result,$i,"surname"); $f4=mysql_result($result,$i,"address"); $f5=mysql_result($result,$i,"addresstwo"); $f6=mysql_result($result,$i,"city"); $f7=mysql_result($result,$i,"country"); $f8=mysql_result($result,$i,"postcode"); $f9=mysql_result($result,$i,"email"); $f10=mysql_result($result,$i,"tel"); $f11=mysql_result($result,$i,"healthcarepro"); $f12=mysql_result($result,$i,"corrispondance"); ?> <?php $i++; } ?>
title firstname surname address email tel healthcarepro corrispondance
<?php echo $f1; ?> <?php echo $f2; ?> <?php echo $f3; ?> <?php echo $f4; ?>
<?php echo $f5; ?>
<?php echo $f6; ?>
<?php echo $f7; ?>
<?php echo $f8; ?>		 <?php echo $f9; ?> <?php echo $f10; ?> <?php echo $f11; ?> <?php echo $f12; ?>

Select date
Start Date: Select 24 25 26 27 End Date: Select 05 06 07 08 Mounth: Select 2009 2010 2011 2012 End Date: Select 24 25 26 27 Mounth: Select 05 06 07 08 Mounth: Select 2009 2010 2011 2012
 
 

any help would be awsome as i cant seem to get this to work

Cheers

#2

I only see an error :

$num=mysql_numrows($result);

must be
$num=mysql_num_rows($result);

#3

thanks for the reply - tried changing the error unfortunatly still cant seem to get it to work
Regards
J

#4

I have made some alterations to the code but still dosnt seem to work

table.altrowstable { font-family: verdana,arial,sans-serif; font-size:11px; color:#333333; border-width: 1px; border-color: #a9c6c9; border-collapse: collapse; text-align: center; } table.altrowstable th { border-width: 1px; padding: 8px; border-style: solid; border-color: #a9c6c9; } table.altrowstable td { border-width: 1px; padding: 8px; border-style: solid; border-color: #a9c6c9; } .oddrowcolor{ background-color:#d4e3e5; } .evenrowcolor{ background-color:#c3dde0; }
<?php $con = mysql_connect("localhost", "root", ""); if (!$con) { die('Could not connect: ' . mysql_error()); } $db_selected = mysql_select_db("btg", $con); $date1 = ""; $date2 = ""; if (isset($_POST['Submit'])) { $date1 = date('Y-m-d', strtotime(mktime(0,0,0,$_POST['startday'],$_POST['startmonth'],$_POST['startyear']))); $date2 = date('Y-m-d', strtotime(mktime(0,0,0,$_POST['endday'],$_POST['endmonth'],$_POST['endyear']))); } $sql = ("SELECT * FROM users WHERE corrispondance BETWEEN '$date1' AND '$date2'"); $result = mysql_query($sql,$con); $num=mysql_num_rows($result); ?> <?php $i=0; while ($i < $num) { $f1=mysql_result($result,$i,"title"); $f2=mysql_result($result,$i,"firstname"); $f3=mysql_result($result,$i,"surname"); $f4=mysql_result($result,$i,"address"); $f5=mysql_result($result,$i,"addresstwo"); $f6=mysql_result($result,$i,"city"); $f7=mysql_result($result,$i,"country"); $f8=mysql_result($result,$i,"postcode"); $f9=mysql_result($result,$i,"email"); $f10=mysql_result($result,$i,"tel"); $f11=mysql_result($result,$i,"healthcarepro"); $f12=mysql_result($result,$i,"corrispondance"); ?> <?php $i++; } ?>
title firstname surname address email tel healthcarepro corrispondance
<?php echo $f1; ?> <?php echo $f2; ?> <?php echo $f3; ?> <?php echo $f4; ?>
<?php echo $f5; ?>
<?php echo $f6; ?>
<?php echo $f7; ?>
<?php echo $f8; ?>		 <?php echo $f9; ?> <?php echo $f10; ?> <?php echo $f11; ?> <?php echo $f12; ?>

Select date
Start Date: Select 24 25 26 27 End Date: Select 05 06 07 08 Mounth: Select 2009 2010 2011 2012 End Date: Select 24 25 26 27 Mounth: Select 05 06 07 08 Mounth: Select 2009 2010 2011 2012
 
 

Reagrds
J

#5

Fixed it

if (isset($_POST[‘Submit’]))
{
$date1 = date(‘Y-m-d’, mktime(0,0,0,$_POST[‘startmonth’],$_POST[‘startday’],$_POST[‘startyear’]));
$date2 = date(‘Y-m-d’, mktime(0,0,0,$_POST[‘endmonth’],$_POST[‘endday’],$_POST[‘endyear’]));
}

Cheers

Sponsor our Newsletter | Privacy Policy | Terms of Service