Page 1 of 1
Select All From week starting "Date From Search"
Posted: Sat Jun 12, 2010 1:02 pm
by webcrafty
Hi All,
I'm designing a MySQL/PHP admin site for a transport company. I'm new to php so i'm using dreamweaver to help thing along.
I have various search inputs for searching for truck, drivers, trailers on a selected date. I have thest searches working fine.
The problem I have is that I want to search for driver activity on a chosen week. I have the drivers in a dropdown list and a date picker to choose the Monday of the week I wish to search.
At the moment the sql looks like this,
SELECT *
FROM daily_work_sheet
WHERE Driver LIKE 'colname' AND Date = 'colname2' display_by="week"
ORDER BY Date ASC
Colname and colname2 correspond to the dropdown list name and the date field name.
How can I get the results to show the data for the monday picked and the 6 days after.
Can anyone help, Please.
Thanks in advance
Re: Select All From week starting "Date From Search"
Posted: Sat Jun 12, 2010 1:18 pm
by internet-solution
You can try something like this:
In PHP
Code: Select all
$weekEnd = strtotime($colname2."+ 6 days");
$weekEndString = date("Y-m-d",$weekEnd); //date in MySQl format
and SQl statement becomes:
[syntax]
SELECT *
FROM daily_work_sheet
WHERE Driver LIKE 'colname' AND Date between '$colname' and '$weekEndString' display_by="week"
ORDER BY Date ASC[/syntax]
Re: Select All From week starting "Date From Search"
Posted: Sat Jun 12, 2010 2:32 pm
by webcrafty
Thanks a lot. Works like a charm.
just one more thing please,
The Date field in my mysql database is in the format yyyymmdd, when I try to format the date to something like Monday 14th June I always get the date displayed as Monday 1st Jan 1970. I know I got this to work before but cant remember how. Lost those files when I left my last job.
I'm in ireland so having the date formatted ddmmyyy is required for ease of use.
Here is the code that returns yyyymmdd
<?php echo $row_driversearch['Date']; ?>
Here is what returns monday ddmmyyy
<?php echo date('l, d.m.Y',$row_driversearch['Date']); ?>
Maybe it's my database setup, I'm lost !!!
thanks again.
Re: Select All From week starting "Date From Search"
Posted: Sat Jun 12, 2010 4:37 pm
by internet-solution
The date returned from database is not recognised by PHP as date. It treats it as string. So you will have to use strtotome() function first -
Code: Select all
<?php echo date('l, d.m.Y',strtotime($row_driversearch['Date'])); ?>
Re: Select All From week starting "Date From Search"
Posted: Sun Jun 13, 2010 7:00 am
by webcrafty
Thanks again,
Slight problem with the date+6 day issue I had first. I thought it was working but now it seems to be showing all records when I search for 1 particular week.
e.g. I want to see what a driver has done from monday 7th Jun. that is monday through sunday
It gives me results for that week after the week starting the 7th ( only have about 3 weeks records in database)
here is what im using on the result page.
Code: Select all
<?php
$weekEnd = strtotime($colname2."+ 6 days");
$weekEndString = Date("Y-m-d",$weekEnd); //date in MySQl format
//NeXTenesio Special List Recordset
$colname2_driversearch = "-1";
if (isset($_POST['driverdate1'])) {
$colname2_driversearch = $_POST['driverdate1'];
}
$colname_driversearch = "-1";
if (isset($_POST['driverSearch'])) {
$colname_driversearch = $_POST['driverSearch'];
}
mysql_select_db($database_camp, $camp);
$query_driversearch = sprintf("SELECT * FROM daily_work_sheet WHERE Driver LIKE '%s' AND Date between '%s' and '$weekEndString' ORDER BY Date ASC", $colname_driversearch,$colname2_driversearch);
$driversearch = mysql_query($query_driversearch, $camp) or die(mysql_error());
$row_driversearch = mysql_fetch_assoc($driversearch);
$totalRows_driversearch = mysql_num_rows($driversearch);
//End NeXTenesio Special List Recordset
?>
could you have a quick look at this please.
Thanks
Re: Select All From week starting "Date From Search"
Posted: Sun Jun 13, 2010 5:23 pm
by internet-solution
You need to use $colname2_driversearch (not $colname2 which is not initialised!) in the code. Try this
Code: Select all
<?php
//NeXTenesio Special List Recordset
$colname2_driversearch = "-1";
if (isset($_POST['driverdate1'])) {
$colname2_driversearch = $_POST['driverdate1'];
}
$weekEnd = strtotime( $colname2_driversearch."+ 6 days");
$weekEndString = Date("Y-m-d",$weekEnd); //date in MySQl format
$colname_driversearch = "-1";
if (isset($_POST['driverSearch'])) {
$colname_driversearch = $_POST['driverSearch'];
}
mysql_select_db($database_camp, $camp);
$query_driversearch = sprintf("SELECT * FROM daily_work_sheet WHERE Driver LIKE '%s' AND Date between '%s' and '$weekEndString' ORDER BY Date ASC", $colname_driversearch,$colname2_driversearch);
$driversearch = mysql_query($query_driversearch, $camp) or die(mysql_error());
$row_driversearch = mysql_fetch_assoc($driversearch);
$totalRows_driversearch = mysql_num_rows($driversearch);
//End NeXTenesio Special List Recordset
?>
Re: Select All From week starting "Date From Search"
Posted: Mon Jun 14, 2010 12:45 pm
by webcrafty
Works fine now.
Thanks for your time.
Most helpful forum I have been on for some time. I'll be back !