Date sorting

[zenin]

On my website i have over 300 hundred links sorted under the date they have been posted on the site. The problem is that the list has gotten quite long. It takes like 4 seconds to load or something. So now I want to limit the list of links to the last 3 or 4 days. And I dont have a clue on how to do that.

I figuered that I cant just use the MYSQP LIMIT function becouse that only effects the links itself. I want a nice cut so it still displays all links on the last day on the index page. Please tell me if you dont understand the problem and Ill try to explain a little better.

Thx in advance!

[shocker-z]

You can use

AND DATE_SUB(CURDATE(),INTERVAL 4 DAY) <= enddate;");

in your query and that will retuan all in last 4 days.. just replace enddate with the field your wanting to limit to past 4 days :)

[zenin]

Im not quite sure what i have to replace enddate with. Please give me a little more concrete examples :)

[shocker-z]

enddate is simply a field i had used in one of my projects.. what ever field has the date in in your table change enddate to that.. so if you table has date as a field/column then change enddate to date..

[zenin]

Sorry wont work... my query looks like this:

$query = "SELECT * FROM spunk_links ORDER BY date DESC AND DATE_SUB(CURDATE(),INTERVAL 4 DAY) <= date";

That query will display nothing but a blank page. Not even an error.
Is there something im forgetting?

[zenin]

Can anyone help please. Havent figured it out yet... :(

[swatisonee]

[code]$query = "SELECT * FROM spunk_links  WHERE   `Date` >= (whatever you want)   AND `Date` <= CURDATE   ORDER BY `Date` desc";

$result = mysql_query($query) or die (mysql_error());[/code]

and then see what errors crop up.

You can also use BETWEEN if you dont want to use <= and >=

[zenin]

If using you code everything lists up as usuall...no errors. That still didnt solve my problem... please if you dont understand my problem say so and ill explain it more thoroughly...

[zenin]

bumpidy bump ;)

[Barand]

[!--quoteo(post=357041:date=Mar 21 2006, 06:07 PM:name=zenin)--][div class=\'quotetop\']QUOTE(zenin @ Mar 21 2006, 06:07 PM) [snapback]357041[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Sorry wont work... my query looks like this:

$query = "SELECT * FROM spunk_links ORDER BY date DESC AND DATE_SUB(CURDATE(),INTERVAL 4 DAY) <= date";

That query will display nothing but a blank page. Not even an error.
Is there something im forgetting?
[/quote]

Syntax is wrong and you haven't executed the query.

[code]$query = "SELECT * FROM spunk_links  
               WHERE DATE_SUB(CURDATE(),INTERVAL 4 DAY) <= `date`
               ORDER BY `date` DESC";
$result = mysql_query($query) or die (mysql_error());[/code]

[zenin]

Sorry but it still displays a blank page... current code is still

[code]
$query = "SELECT * FROM spunk_links  
               WHERE DATE_SUB(CURDATE(),INTERVAL 4 DAY) <= 'datum'
               ORDER BY 'datum' DESC";

$result = mysql_query($query) or die (mysql_error());
[/code]

And no errors whatsoever. I thought that perhaps my database fields have something to do with it... I changed the date field name to "datum" so it doesnt conflict with an existing function and I set the datum column type to date. Any thoughts about that? And btw that for all your help so far :D

[Barand]

What is rest of your code where you output the results of the query?

[zenin]

Sorry I have to correct myself. The current code:

[code]

$query = "SELECT * FROM spunk_links  
               WHERE DATE_SUB(CURDATE(),INTERVAL 4 DAY) <= 'datum'
               ORDER BY 'datum' DESC";


$result = mysql_query($query) or die (mysql_error());
[/code]

Will row all links I have in my database. But if I remove the ' sign around datum it will display a totally blank page.

Rest of my code looks like this:

[code]

$prev = '';

$color1 = "white";
$color2 = "#ecf5f8";
$row_count = 0;

while($r = mysql_fetch_array($result))
{  

    $id=$r["id"];
    $title=$r["title"];
    $link=$r["link"];
    $info=$r["info"];
    $date=$r["datum"];
    $public=$r["public"];
    $category=$r["category"];
    $click=$r["click"];
    $type=$r["type"];
    $uploader=$r["uploader"];


$row_color = ($row_count % 2) ? $color1 : $color2;

if ($public == 1) {

if ($orderby == '') {
    if ($last_date != $date)
    {
        ?><br><br><center><b><? if ($date == date("Y-m-d")) { echo "Dagens länkar"; } else { echo "$date"; } ?></b></center><br><?
    }
}
    
?>

<div id='link'>

<table width='700px' cellspacing='0px' cellpadding='2px' style='font-size: 10px;'>
<tr>
<td width='250px' bgcolor='<? echo "$row_color"; ?>' nowrap>

<? if ($category != Erbjudande) {

echo "
<a href='postlink/count.php?id=$id' target='_blank'
onmouseover=\"return overlib('$info');\" onmouseout=\"return nd();\">
$title</a>
";
} else {

echo "
<a href='$link' target='_blank'
onmouseover=\"return overlib('$info');\" onmouseout=\"return nd();\">
$title</a>
";
}

echo "
</td>
<td width='100px' align='left' bgcolor='$row_color' nowrap>
<a href='http://spunkie.se/index.php?orderby=$category'>$category</a>
</td>
<td width='100px' align='left' bgcolor='$row_color' nowrap>
<a href='http://spunkie.se/index.php?orderby=$type'>$type</a>
</td>
<td width='100px' align='left' bgcolor='$row_color' nowrap>
";

if ($category != Erbjudande) {

echo "
<a href='http://spunkie.se/index.php?orderby=clicks'>$click klick</a>
"; } else {
echo "
<a href='http://spunkie.se/index.php?orderby=Erbjudande'>Erbjudande</a>
";
}
echo "
</td>
<td width='75px' align='left' bgcolor='$row_color' nowrap>
";

$sql = mysql_query("SELECT * FROM spunk_comments WHERE reciever='$title'")or die(mysql_error());

$sql_num_rows = mysql_num_rows($sql);

echo "

<a href=''
onClick=\"return

!window.open('http://spunkie.se/comments/viewcomment.php?title=$title','comment',
'width=250,height=600,screenX=25,screenY=25,resizable=yes,scrollbars=yes')\"
>
$sql_num_rows inlägg
</a>
</td>
<td width='100px' align='right' bgcolor='$row_color' nowrap>
Av $uploader
</td>
</tr>

</table>
</div>

";

$row_count++;
$last_date = $date;
}
}
mysql_close($db);

?>
[/code]