Select All From week starting "Date From Search"

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
webcrafty
Forum Newbie
Posts: 4
Joined: Sat Jun 12, 2010 12:41 pm

Select All From week starting "Date From Search"

Post 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
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"

Post 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]
webcrafty
Forum Newbie
Posts: 4
Joined: Sat Jun 12, 2010 12:41 pm

Re: Select All From week starting "Date From Search"

Post 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.
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"

Post 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'])); ?>
webcrafty
Forum Newbie
Posts: 4
Joined: Sat Jun 12, 2010 12:41 pm

Re: Select All From week starting "Date From Search"

Post 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
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"

Post 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
?>
 
webcrafty
Forum Newbie
Posts: 4
Joined: Sat Jun 12, 2010 12:41 pm

Re: Select All From week starting "Date From Search"

Post by webcrafty »

Works fine now.

Thanks for your time.
Most helpful forum I have been on for some time. I'll be back !
Post Reply