Date ranges

Christianharris · September 17, 2008

Hope someone can help me please. I'm not a developer, but am trying to learn!

I have a search feature on my site which lists news stories by popularity. However, I'm trying to change the code so that instead of listing the most popular stories of all time, it lists the most popular stories of the day (i.e. today). Any advice on which parts of the code to change would be amazing!

Here's the code that's live on my site:

}

$order = "content.id DESC";

$pop = "";

if ($sort == "pop")

{

# Override all else?!

$order = "views DESC";

//$limit = " LIMIT 0,15";

$startfrom = strtotime("last sunday");

$startrange = date("Y-m-d", $startfrom) . " 23:59:59";

//$pop = "AND content.ptime > $startrange";

$pop = "AND lastvisitdate > '$startrange'";

$order = "sincemondayvisits DESC, lastvisitdate desc";

}

F1Fan · September 17, 2008

Can yo list more of your code? The rest of your query maybe? It seems like you should easily be able to query only a specific date.

genericnumber1 · September 17, 2008

What is the structure of your table/tables?

Christianharris · September 17, 2008

Does this help?

# NEW, FORCE DISPLAY OF PAGE NAV LINKS!

$limit = "";

#$limit=" LIMIT 15";

}

$order = "content.id DESC";

$pop = "";

if ($sort == "pop")

{

# Override all else?!

$order = "views DESC";

//$limit = " LIMIT 0,15";

$startfrom = strtotime("now");

$startrange = date("Y-m-d", $startfrom) . " 00:00:01";

//$pop = "AND content.ptime > $startrange";

$pop = "AND lastvisitdate > '$startrange'";

$order = "now DESC, lastvisitdate desc";

}

$time = time();

if( $limit == "" ) {

$limit = " LIMIT 0,15";

if( $s_no != "" ) {

$limit = " LIMIT $s_no," . ($s_no + 15);

}

// inner join prod_type on content.prod_type = prod_type.id

// ,prod_type.name as prod_type

$query = "SELECT content.id,content.body,content.ptime,content.prod_name,content.prod_type as pt,

FROM_UNIXTIME(content.ptime, '%d.%m.%y') as datestamp,

FROM_UNIXTIME(content.ptime, '%d %M %y') as datestampformatted, lastvisitdate, sincemondayvisits

FROM content

WHERE content.active = 1

AND prod_type IN (SELECT id FROM prod_type)

AND content.ptime < $time

genericnumber1 · September 17, 2008

It's difficult because the code is very messy, but will you give us some more code lower down below where you've just showed us?.

Christianharris · September 17, 2008

Sure. Thanks for helping. This is the rest of the entire page:

<?php
# NEW, FORCE DISPLAY OF PAGE NAV LINKS!
  $limit = "";
  #$limit=" LIMIT 15";



}

$order = "content.id DESC";
$pop = "";
if ($sort == "pop")
{
  # Override all else?!
  $order = "views DESC";
  //$limit = " LIMIT 0,15";

$startfrom = strtotime("now");
$startrange = date("Y-m-d", $startfrom) . " 00:00:01";
//$pop = "AND content.ptime > $startrange";
$pop = "AND lastvisitdate > '$startrange'";
$order = "now DESC, lastvisitdate desc";
}


$time = time();

if( $limit == "" ) {
  $limit = " LIMIT 0,15";
  
  if( $s_no != "" ) {
  $limit = " LIMIT $s_no," . ($s_no + 15);
  }
}

// inner join prod_type on content.prod_type = prod_type.id
// ,prod_type.name as prod_type

$query = "SELECT content.id,content.body,content.ptime,content.prod_name,content.prod_type as pt,

FROM_UNIXTIME(content.ptime, '%d.%m.%y') as datestamp,
FROM_UNIXTIME(content.ptime, '%d %M %y') as datestampformatted, lastvisitdate, sincemondayvisits

FROM content 



WHERE content.active = 1

AND prod_type IN (SELECT id FROM prod_type)

AND content.ptime < $time



$get_ed



$get_tia



$qpt



$qdate







$qprod

$pop





ORDER BY $order $limit";


$result = mysql_query ($query) or die ("Content Query failed");
//echo $query;
//echo "<!--" . $query . "-->";


$queryRows = "SELECT count(id) as rowcounter from content
WHERE content.active = 1
AND content.ptime < $time
AND prod_type IN (SELECT id FROM prod_type)
$get_ed
$get_tia
$qpt
$qdate
$qcomp
$qprod
$pop";

//echo $queryRows;
$resultRows = mysql_query ($queryRows) or die ("Content Query failed");
$row = mysql_fetch_assoc ($resultRows);
$num_rows = $row['rowcounter'];
//echo $num_rows;

//$num_rows = mysql_num_rows($result);







//Display correct header graphic for search, awards or recent stories



if ((!$s)&&(!$sort)) {

    echo '<TR VALIGN="TOP"><TD HEIGHT="20" COLSPAN="3"><table width=100% cellpadding=0 cellspacing=0><tr><td><SPAN class=panelHeading2>          </SPAN></TD>';


  //echo '<TR VALIGN="TOP"><TD HEIGHT="20" COLSPAN="3"><IMG SRC="images/system/titles/sectitle_latestproducts.gif" WIDTH="146" HEIGHT="17"></TD></TR>';
  //echo '<TR VALIGN="TOP"><TD HEIGHT="20" COLSPAN="3"><SPAN class=panelHeading2>Latest Products</SPAN></TD></TR>';



} else {



  if ($sort == "ed") {



    echo '<TR VALIGN="TOP"><TD HEIGHT="20" COLSPAN="3"><table width=100% cellpadding=0 cellspacing=0><tr><td><SPAN class=panelHeading2>Editors Choice</SPAN></TD>';



  } elseif ($sort == "tia") {


    echo '<TR VALIGN="TOP"><TD HEIGHT="20" COLSPAN="3"><table width=100% cellpadding=0 cellspacing=0><tr><td><SPAN class=panelHeading2>Technical Innovations</SPAN></TD>';


  } elseif ($sort == "pop") {

    echo '<TR VALIGN="TOP"><TD HEIGHT="20" COLSPAN="3"><table width=100% cellpadding=0 cellspacing=0><tr><td><SPAN class=panelHeading2>Most Popular News</SPAN></TD>';

  } else {



    echo '<TR VALIGN="TOP"><TD HEIGHT="20" COLSPAN="3"><table width=100% cellpadding=0 cellspacing=0><tr><td><SPAN class=panelHeading2> </SPAN></TD>';



  }



  $hg=1;



}

# NEW FORCE DISPLAY OF PAGE NAV LINKS!
$s=1; $hg=1;

if ($hg==1) {



  if (($s_no+15) > $num_rows) {



    $r = (($num_rows - $s_no) +$s_no);



  } else {



    $r = $s_no+15;



  }






if ($pt || $prod) {

  if (($s_no+1) == $num_rows) {



    //$disp = '<td><SPAN CLASS="intro">Displaying result '.($s_no+1).' of '.$num_rows.'.</SPAN></td>';



  } else {



    //$disp = '<td><SPAN CLASS="intro">Displaying results '.($s_no+1).' to '.($r).' of '.$num_rows.'.</SPAN></td>';



  }
}






  $linkperpage=15;

  $num_pages = ceil($num_rows/$linkperpage);
  //echo $num_pages;

  $plstart = 1;
  $plend   = $num_pages;

  # What page are we on now?
  $page = intval($s_no / 15) + 1;
  
  # include the new page nav pod code
  include("nofn.php");
  
  # Use it to generate the nav
  $s_nav = NofN( $num_pages,       // Number of pages in total
  				 $num_rows,        // Number of items in total
  				 $page, 		   // Current page
			 15,               // Items per page
			 // URL...
			 'news.php?pt='.$pt.'&sort='.$sort.'&DateRange='.$DateRange.'&MonthFrom='.$MonthFrom.'&MonthTo='.$MonthTo.'&comp='.$comp.'&prod='.$prod.'&cmdSearch=Search',
			 "s_no",            // URL param to hold the start point
			 0,                 // URL param is page num? 1 or 0 (if zero, tis assumed it's item num
			 $s_no              // Param is now?
			); 

  if ($num_rows==0) {

    echo '<tr><td><SPAN CLASS="intro">Sorry, your search generated no results.</SPAN></P></td></tr></table>';

  } else {

if( $disp != "" )
{
	echo $disp;
}

    echo "<TR><td>$s_nav</td></TR></table>";
  }



}







$c=0;



while ($row = mysql_fetch_assoc ($result)) {



  $id[$c] = $row['id'];

/*  # Get the company name associated with this article...
  $sql = "SELECT company.name FROM company WHERE company.id=$row['id']";
  $coresult = mysql_query ($sql);
echo "<!--" . $sql . "-->";
  $coname   = mysql_fetch_assoc($coresult);
  $prod_coname[$c] = $coname['name'];
  mysql_free_result($coresult);
  ######################################################
*/  
	$datestampformatted[$c] = $row['datestampformatted'];

  $prod_name[$c] = $row['prod_name'];



  $prod_type[$c] = $row['prod_type'];



  //$npt[$c] = $row['pt'];



  $body[$c] = $row['body'];



  $ptime[$c] = $row['ptime'];

  $datestamp[$c] = $row['datestamp'];





  #Get only first 160 chars of body



  //$maxTextLenght=160;
  $maxTextLenght=500;



  $aspace=" ";



  if(strlen($body[$c]) > $maxTextLenght ) {



   $body[$c] = substr(trim($body[$c]),0,$maxTextLenght);



   $body[$c] = substr($body[$c],0,strlen($body[$c])-strpos(strrev($body[$c]),$aspace));



   $body[$c] = $body[$c].'...';



  }


  $c++;





}







if($s){



  if ($r < ($s_no+15)) {



    $num_links = $r;



  } else {



    $num_links = ($s_no+15);



  }



} else {



  $num_links=$num_rows;



}







if (!$s_no){



  $s_no=0;



}



//for($e = $s_no; $e < $num_links; $e++){
for($e = 0; $e < $linkperpage && $id[$e] != ""; $e++){


  

$title = "";
if( strtotime(str_replace(".","/", $datestampformatted[$e])) > strtotime('09 May 2006') ) {
  $title = $prod_name[$e];
} else {
  $title = $prod_coname[$e] . " " . $prod_name[$e];
}

//echo '<!--datecheck' . $datestampformatted[$e]. ' ' .strtotime(str_replace(".","/", $datestampformatted[$e])) . ' '. strtotime('09 May 2006') .'-->';

echo '
      <TR VALIGN="TOP">
        <TD COLSPAN="3" HEIGHT="12"></TD>
      </TR>
      <TR VALIGN="TOP">
	<TD>
		<table width="100%" cellspacing="0" cellpadding="0" border="0">
		<tr valign=top>
			<td class="body" colspan="2">
				<table cellpadding=0 cellspacing=0>
				<tr>
					<Td class="body"><b>'.$title.'</b></td>
				</td>
				<tr>
					<td><img src="images/spacer.gif" height=5 width=10></td>
				</td>
				<tr>
					<td class="body">'.$body[$e].'</td>
				</td>
				</table>
			</td>
			<td>
				<img src="images/spacer.gif" height=10 width=15>
			</td>

		</tr>
		<tr valign=top>
			<Td colspan=3><img src="images/spacer.gif" height=10 width=100%></td>
		</tr>
		<tr valign=top>
			<td class="body" valign="bottom">
				<a href="article.php?id='.$id[$e].'" class=l2menuselectedgreen><img src="images/more_new_text.gif" width="45" height="10" border="0" name="imageenlarge"></a>
			</td>
			<td align="right" valign="bottom" class="tiny"><i><font color="#666666">'.$datestamp[$e].'</font></i><br><img src="images/spacer.gif" height=1 width=10></td>
		</tr>
		</table>
	</TD>
	</TR>
      <TR VALIGN="TOP">
        <TD COLSPAN="3" HEIGHT="12"></TD>
  </TR>';
  
  if( ($e + 1) < $linkperpage )
  {
  	echo '<TR VALIGN="TOP"  >
        <TD COLSPAN="3" HEIGHT="2" BACKGROUND="images/dot-black.gif"></TD>
  </TR>';
  }
}







#Chris dosn't like it



#Repeat prev/next search nav



/*



if ($s) {



  if ($num_rows!=0) {



    echo '<TR>



            <TD COLSPAN=3>



              <TABLE BORDER="0" CELLSPACING="0" CELLPADDING="0" WIDTH="100%">



                <TR>



                  <TD WIDTH="245">



                    <P CLASS="body">'.$disp.'</P>



                  </TD>



                  '.$s_nav.'</TR></TABLE></TD></TR>';



  }



}



*/



?>

</td></tr></table>

genericnumber1 · September 17, 2008

keeping with the hard-to-read mindset...

change

   //$pop = "AND content.ptime > $startrange";

to

   $today = "AND content.ptime > $startrange";

and change

$pop





ORDER BY $order $limit";

to

$pop

$today

ORDER BY $order $limit";

I had to guess for some things what your database structure was, but it seemed like the facility was in place, just commented out, so hopefully it will work like that.

Christianharris · September 18, 2008

Thanks for your feedback, but when I make those two changes i get an error message: 'Content Query failed'

:'(

Sign In

Date ranges

Recommended Posts

Christianharris

Link to comment

Share on other sites

F1Fan

Link to comment

Share on other sites

genericnumber1

Link to comment

Share on other sites

Christianharris

Link to comment

Share on other sites

genericnumber1

Link to comment

Share on other sites

Christianharris

Link to comment

Share on other sites

genericnumber1

Link to comment

Share on other sites

Christianharris

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information