Jump to content


Photo

Date sorting


  • Please log in to reply
12 replies to this topic

#1 zenin

zenin
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 15 March 2006 - 05:37 PM

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!

#2 shocker-z

shocker-z
  • Members
  • PipPipPip
  • Advanced Member
  • 864 posts
  • LocationNottingham

Posted 15 March 2006 - 05:40 PM

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 :)
www: www.ukchat.ws | irc: irc.ukchat.ws chan: #blufudge

#3 zenin

zenin
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 21 March 2006 - 03:46 PM

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

#4 shocker-z

shocker-z
  • Members
  • PipPipPip
  • Advanced Member
  • 864 posts
  • LocationNottingham

Posted 21 March 2006 - 05:12 PM

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..
www: www.ukchat.ws | irc: irc.ukchat.ws chan: #blufudge

#5 zenin

zenin
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 21 March 2006 - 06:07 PM

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?


#6 zenin

zenin
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 23 March 2006 - 06:37 PM

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

#7 swatisonee

swatisonee
  • Members
  • PipPipPip
  • Advanced Member
  • 253 posts

Posted 23 March 2006 - 06:48 PM


$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());

and then see what errors crop up.

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

#8 zenin

zenin
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 23 March 2006 - 07:21 PM

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

#9 zenin

zenin
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 24 March 2006 - 06:17 PM

bumpidy bump ;)

#10 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 24 March 2006 - 11:52 PM

[!--quoteo(post=357041:date=Mar 21 2006, 06:07 PM:name=zenin)--][div class=\'quotetop\']QUOTE(zenin @ Mar 21 2006, 06:07 PM) View Post[/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.

$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());

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#11 zenin

zenin
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 25 March 2006 - 02:57 PM

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

$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());

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

#12 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 25 March 2006 - 03:09 PM

What is rest of your code where you output the results of the query?
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#13 zenin

zenin
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 26 March 2006 - 03:40 PM

Sorry I have to correct myself. The current 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());

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:


$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);

?> 





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users