Jump to content

Archived

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

zenin

Date sorting

Recommended Posts

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!

Share this post


Link to post
Share on other sites
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 :)

Share this post


Link to post
Share on other sites
Im not quite sure what i have to replace enddate with. Please give me a little more concrete examples :)

Share this post


Link to post
Share on other sites
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..

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites

[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 >=

Share this post


Link to post
Share on other sites
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...

Share this post


Link to post
Share on other sites
[!--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]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
What is rest of your code where you output the results of the query?

Share this post


Link to post
Share on other sites
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]

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.