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
Select All From week starting "Date From Search"
Moderator: General Moderators
-
internet-solution
- Forum Contributor
- Posts: 220
- Joined: Thu May 27, 2010 6:27 am
- Location: UK
Re: Select All From week starting "Date From Search"
You can try something like this:
In PHP
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]
In PHP
Code: Select all
$weekEnd = strtotime($colname2."+ 6 days");
$weekEndString = date("Y-m-d",$weekEnd); //date in MySQl format
[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"
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.
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.
-
internet-solution
- Forum Contributor
- Posts: 220
- Joined: Thu May 27, 2010 6:27 am
- Location: UK
Re: Select All From week starting "Date From Search"
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"
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.
could you have a quick look at this please.
Thanks
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
-
internet-solution
- Forum Contributor
- Posts: 220
- Joined: Thu May 27, 2010 6:27 am
- Location: UK
Re: Select All From week starting "Date From Search"
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"
Works fine now.
Thanks for your time.
Most helpful forum I have been on for some time. I'll be back !
Thanks for your time.
Most helpful forum I have been on for some time. I'll be back !