Jump to content

Can anyone help with this database query?


roytheboy

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.
Link to comment
https://forums.phpfreaks.com/topic/8177-can-anyone-help-with-this-database-query/
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...



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

Archived

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

×
×
  • Create New...

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.