Jump to content

Archived

This topic is now archived and is closed to further replies.

roytheboy

Can anyone help with this database query?

Recommended Posts

Hello gang,
I am quite new to php & MySQL and have been given the task of amending a website which has a database that is scripted to show the latest 20 entries on a web page for the case studies section. This needs to be changed to show latest entries for the last 3 months instead without deleting anything from the database. Here is the script as it stands:

while ($release = mysql_fetch_array($case))
{
if ($count > 20) break; //don't show more than 20 docs
$logo = "./assets/pics/agents/".$release['agent_id'].".jpg";
$agent = $db->getAgent($release['agent_id']);
$res = $db->getImagesByDocument($release['id']);
$has_images = false;
while ($a = mysql_fetch_assoc($res)) $has_images = true;
$aFeatures = array();
$doctype = substr($release['filename'],-3,3);

for ($i=0;$i<=20;$i++)
{



Also, a function seems to have been used and required once from a file dbobj.php to call up from the database:

function getCaseStudies()
{
$this->query = "SELECT * FROM document WHERE type_id='case' ORDER BY date_created DESC;";
$rs = mysql_query($this->query);
return ($rs);
}


I have tried using the time stamp function but I couldn't figure out how [b]not[/b] to pinpoint specific months instead of just the 3 month period.

Here is the URL: [a href=\"http://affordablehomeowndership.myzen.co.uk/v1.8/\" target=\"_blank\"]http://affordablehomeowndership.myzen.co.uk/v1.8/[/a]

Any help will be greatly appreciated. Kind regards, RTB.

Share this post


Link to post
Share on other sites
Well as to the timestamp issue...

this will create a timestamp equal to 3 months prior to the current date.
[code]
$old_date_ts = strtotime ("-3 month");[/code]you can use this in your sql query to select all records where the case date (timestamp) is greater than the $old_date_timestamp.

Obviously you will also need to edit your code to remove any reference to the 20 case limit.

Hope it helps.

Lite...



Share this post


Link to post
Share on other sites
Thanks a lot litebearer I will try that....
does that mean I would [b]not[/b] have to also alter the SQL SELECT query in the dbobj.php file I mentioned previously?


function getCaseStudies()
{
$this->query = "SELECT * FROM document WHERE type_id='case' ORDER BY date_created DESC;";
$rs = mysql_query($this->query);
return ($rs);
}

regards, roytheboy

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.