Jump to content


Photo

Can anyone help with this database query?


  • Please log in to reply
2 replies to this topic

#1 roytheboy

roytheboy
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 23 April 2006 - 01:42 PM

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 not 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.

#2 litebearer

litebearer
  • Members
  • PipPipPip
  • Advanced Member
  • 2,357 posts
  • Locationwhite lake michigan

Posted 24 April 2006 - 02:49 AM

Well as to the timestamp issue...

this will create a timestamp equal to 3 months prior to the current date.
$old_date_ts = strtotime ("-3 month");
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...




all the brothers were valiant!

[br][br]The truely intelligent people are not those who create the dots; rather they are they ones with the ability to connect the dots into a coherent picture

#3 roytheboy

roytheboy
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 24 April 2006 - 02:43 PM

Thanks a lot litebearer I will try that....
does that mean I would not 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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users