PHP & MYsql insert date and show result in last week

PHP
1

Hi, I need to save the date i post something in to a mysql database and then retrieve all results in 3 diffrent table within the last 2 weeks. The 3 tables will be link through forgien keys. ANy suggestions i cant seem to find how to select results within the last 2 weeks ( a set date for example 23/4/2011- 10/5/2011 easy but the make it within the last two weks i cant find)
So two problems really:
Trying to save todays date in to mysql.
Retrieving the data from 3 tables to show result in php but collecting all results from three table and showing all in order.
Any suggested please

Thanks You

2

You’ll use a JOIN on the foreign key to select from 3 tables. This produces a single ‘result set’ that has data from the three tables.
Looks a bit like this:


SELECT t1.id, t1.date, t2.name, t3.other
FROM `table1` AS t1
   INNER JOIN `table2` AS t2 ON t2.parent_id = t1.id
   INNER JOIN `table3` AS t3 ON t3.parent_id = t1.id
WHERE t1.date >= '2011-02-18'

You CAN do the 2 week date calculation directly in the SQL query with the DATE_SUB function, but the query won’t use MySQLs query cache (maybe irrelevent).

The other option is to do the subtraction in PHP with [fphp]strtotime[/fphp](‘-2 weeks’);

Your date column should be DATE or DATETIME depending on the detail you need.

3

Thank you cranial-bore for the quick reply i have looked over those links and could not identify what bit i needed. Could you possible point me toward the bit i need ? maybe a sentence and i will find it on the page. Thank you again

4

To do the date calculation directly in the query, it’ll look a bit like this:


SELECT my, fields 
FROM my_table 
WHERE date_field >= DATE_SUB(NOW(), INTERVAL 2 WEEK)

or using PHP:


$past = date('Y-m-d H:i:s', strtotime('-2 weeks');
$query = "SELECT my, fields 
FROM my_table 
WHERE date_field >= '$past'
5

Hey,
My code isnt retreiving the results
Here is my php code

$past = date('Y-m-d H:i:s', strtotime('-2 weeks'));
$result = mysql_query(
"SELECT QuoteSummery.QuoteID,
QuoteSummery.QuoteStatus,
QuoteSummery.LastEdited,
ClientDetails.FirstName,
ClientDetails.LastName,
ClientDetails.LastEdited,
QuoteSummery.Name,
QuoteSummery.Country,
Staff.Initial,
Staff.LastEdited
FROM QuoteSummery, ClientDetails, Staff
WHERE QuoteSummery.ClientDetails_ClientID = ClientDetails.ClientID AND QuoteSummery.Staff_StaffID = Staff.StaffID OR ClientDetails.LastEdited >= '$past' OR QuoteSummery.LastEdited >= '$past'"
);
if (!$result) {
    die("Query to show fields from table failed");
}

It worked before with

$result = mysql_query(
"SELECT QuoteSummery.QuoteID,
QuoteSummery.QuoteStatus,
ClientDetails.FirstName,
ClientDetails.LastName,
QuoteSummery.Name,
QuoteSummery.Country,
Staff.Initial
FROM QuoteSummery, ClientDetails, Staff
WHERE QuoteSummery.ClientDetails_ClientID = ClientDetails.ClientID AND QuoteSummery.Staff_StaffID = Staff.StaffID"
);
if (!$result) {
    die("Query to show fields from table failed");
}

the time stamp in the database is 2011-03-04 10:21:51

6

Your new query has AND and OR in the WHERE clause which might be negating the join.
Those old style joins aren’t the done coding practice, try the INNER JOIN syntax I used. Then the WHERE stays clear for the date condition.

7

Still not finding any result

$past = date('Y-m-d H:i:s', strtotime('-2 weeks'));
$result = mysql_query(
"SELECT 
QuoteSummery.QuoteID,
QuoteSummery.QuoteStatus,
QuoteSummery.LastEdited,
ClientDetails.LastEdited,
ClientDetails.FirstName,
ClientDetails.LastName,
QuoteSummery.Name,
QuoteSummery.Country,
Staff.Initial
FROM 
`QuoteSummery` AS qs
INNER JOIN `ClientDetails` AS cd ON cd.ClientID = qs.ClientDetails_ClientID   
INNER JOIN `Staff` AS sf ON sf.StaffID = qs.ClientDetails_ClientID
WHERE qs.LastEdited >= '$past' OR cd.LastEdited >= '$past'"
);

Can you see anything wrong with that query ?

I’m i right in understanding that using “AS” is just making the shorthand way of righting the table name ?

8

Looks okay. Can you post the structure of the three tables.
Use


SHOW CREATE TABLE `QuoteSummery`
SHOW CREATE TABLE `ClientDetails`
SHOW CREATE TABLE `Staff`

Yes, AS creates a table alias so you can refer to it by a shorter name. You can also use that alieas in the SELECT part of the query, to choose your fields.

Btw, Summery is spelt Summary, but obviously you have to match your table name.

9

you want a staff member who has the same id number as a client?

weird – maybe that’s why there aren’t any results