#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    34
    Rep Power
    3

    Question Limiting Rows Shown with Where Query


    Hi, already posted a similar message to this in the PHP forum, and got told that this was more a HTML error.

    Had a website built for us earlier this year.
    Cutting a long story short, we have news pages with all the news articles down the side - obviously we have lots, so we want to limit them until the beginning of the year, and then for an option "See More" to take us to a different page which then lists every news article on the page after that date (darlingtontowntwinning.co.uk/news_&_events)

    I've been told I just need to put a WHERE query statement in somewhere to limit the date value - but i don't know where, or how - and if it is less than $fromDate, then for A single "See More News" link to display and go to APAGE.html (for example)
    If someone could point this out to me I'd be greatful.

    This is the code we have at the moment:

    PHP Code:
    <div id="right" class="news">
    <h3>Archive</h3>
    <? $news=$session->getNews("","","",1);?>
    <? 
    while($article=mysql_fetch_array($news)){?>
    <? 
    $date 
    $article['thedate'];
    $year date('Y'$date);
    $month date('F'$date);
    $fromDate date('>=130690440',$date);
    ?>
    <h4><?=$month." - ".$year;?></h4>
    <nav class="small">
    <? $innernews=$session->getNews("",$month,$year);?>
    <? 
    while($innerarticle=mysql_fetch_array($innernews)){?>
    <a href="/news/<?=$innerarticle['ftitle']?><? if($title==$innerarticle['ftitle']){?> class="active"<? }?>><?=$innerarticle['title']?></a>
    <? }?>
    </nav>
    <? }?>
    </div>
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Location
    The Pleiades
    Posts
    300
    Rep Power
    9
    Where is the actual query statement which retrieves the data from the database?

    It's something along the lines of:

    SELECT * FROM table where colname=value LIMIT 0,30

    (this retrieves only the first 30 rows which are matched by the query).

    Regards,

    NM.
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi maharrington,

    it might be a good idea to read more carefully. When you posted this in the PHP forum, gw1500se told you that this was more of a MySQL question. Nobody said anything about HTML. He also told you that this is the "wrong" code, because it's not where the actual query is.

    We need the definition of $session->getNews() and anything that leads to the actual query. Until we have that, there's nothing we could do.
  6. #4
  7. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,316
    Rep Power
    7171
    Out of curiosity, could you link to your thread in the PHP board. I'm surprised someone told you this was an HTML question.

    Anyway, the functionality you're going to need to modify is going to be tied to this part:
    $session->getNews("","","",1);

    That executes code that is somewhere else in your code, so you'll need to dig that up and show it to use. It should be in a block of code labeled "function getNews". With some minor modifications to that it should be possible to do this without too much work.

    Edit: In reference to Jacques1's post; this is actually a MySQL question, but don't post this in the MySQL board. The SQL query is burried deep in your PHP code and they won't appreciate having to drag it out. It's also not a very complicated query.
    Last edited by E-Oreo; December 4th, 2012 at 07:38 PM.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  8. #5
  9. CSS & JS/DOM Adept
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jul 2004
    Location
    USA (verifiably)
    Posts
    20,131
    Rep Power
    4304
    Hi, already posted a similar message to this in the PHP forum, and got told that this was more a HTML error.
    Huh? HTML wasn't even mentioned. You were told it was a MySQL question.
    Spreading knowledge, one newbie at a time.

    Check out my blog. | Learn CSS. | PHP includes | X/HTML Validator | CSS validator | Common CSS Mistakes | Common JS Mistakes

    Remember people spend most of their time on other people's sites (so don't violate web design conventions).
  10. #6
  11. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,316
    Rep Power
    7171
    Maybe move this to the PHP board and I'll close that other thread. Anyway, the next step in fixing the problem is the one that Jacques1's and I mentioned.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    34
    Rep Power
    3
    Sorry guys (my bad!)!

    The function is (I believe):
    PHP Code:
    function getNews($title,$month,$year,$group){
           if(
    $title){
               
    $q=$this->query("SELECT * FROM ".TBL_NEWS." WHERE ftitle = '$title'" );
               return 
    mysql_fetch_array($q);
           }else if(
    $year && $month){
             
    $q=mysql_query("SELECT * FROM ".TBL_NEWS." WHERE (FROM_UNIXTIME(thedate, '%Y') = '$year') AND (FROM_UNIXTIME(thedate, '%M') = '$month') ORDER BY thedate DESC");
             return 
    $q;
             }else if(
    $group){
                 
    $q=$this->query("SELECT * FROM ".TBL_NEWS." GROUP BY (FROM_UNIXTIME(thedate, '%Y')),(FROM_UNIXTIME(thedate, '%M')) ORDER BY thedate DESC" );
               return 
    $q;
             }else{
               
    $q=$this->query("SELECT * FROM ".TBL_NEWS." ORDER BY thedate DESC" );
               return 
    $q;
           }
           
       } 
    I'll move the post to the PHP forum as suggested.
    It's not letting me move the thread.
  14. #8
  15. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    What a mess.

    Whoever programmed this has obviously no idea about how to store and process dates in SQL, doesn't know the purpose of "GROUP BY" and couldn't decide which database library to use.

    Anyway, you'll have to add a query that will select the news up to a certain year:
    Code:
    SELECT
    	*
    FROM
    	[your table]
    WHERE
    	EXTRACT(YEAR FROM FROM_UNIXTIME(thedate)) < [your year]
    You'll also have to change the method parameters. You can either add yet another parameter (not very pretty) or use an associative array to specify the search parameters or split the method into specialized methods like getNewsWithTitle(), getNewsUntilYear().
    Last edited by Jacques1; December 5th, 2012 at 08:17 AM.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    34
    Rep Power
    3
    Thanks for your help Jacques1 - however, I'm new to this and haven't really got a clue what I'm doing.

    I know this is the code that i need to insert (i believe):
    Code:
    SELECT * FROM ".TBL_NEWS." WHERE EXTRACT(YEAR FROM FROM_UNIXTIME(thedate, '%Y'))>=130690440
    But i don't get where, or how this will allow me to insert a link underneath saying "See More"?
    Last edited by maharrington; December 7th, 2012 at 07:13 AM. Reason: Removed un-needed ) from the code.
  18. #10
  19. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by maharrington
    however, I'm new to this and haven't really got a clue what I'm doing.
    I'm sorry to say, but then somebody else should do the job.

    When you don't know PHP and MySQL at all and cannot even apply a simple query template, how is this supposed to work? I mean, even if somebody wrote all the code for you, how would you know it doesn't crash the website under certain conditions or create security holes?

    If you don't know anybody with web programming experience, your organisation should grab a few dollars (um, pounds) and hire a programmer.
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    34
    Rep Power
    3

    Angry


    Excuse Me!
    I believe that if you had read my original post you would have seen that the website was developed by somebody else - therefore, assuming that I had no knowledge of websites, how would I know what I'm doing.

    In addition to this, if you had read the first message on the other category you would actually notice that I said I was willing to learn and that I'm new to this....but to learn I need more direction than what you have provided me.

    I am an individual, working on a voluntary basis for a charitable organisation...they have no more money to spend on programmers to do what could be done by myself IF i was given a reasonable direction on what to do.

    It is responses like this that give forums a bad name for themselves....I've come on here for assistance, which, you have somewhat provided me with (for which I'm grateful), but when I ask for further assistance you throw this back in my face!

    If you would like to help out, then please do so...but DO NOT speak to me as if I'm supposed to know everything there is to know about programming (I'll quite happily go elsewhere....where in the past I've had pleasant experiences and people have actually been willing to help out a little more!)

    And yes....it's pounds!
  22. #12
  23. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    What I find quite annoying is that you (like many people) don't seem to view programming as actual work. You have no idea what to do and now expect others to do your job -- for free, of course.

    I mean, when your car breaks, you'll hardly turn to some car forum to explain you exactly how to repair the engine. You pay a mechanic to do it. But when it comes to programming, suddenly everybody thinks he could do anything with a little free help from the Internet.

    There's a difference between helping someone and doing his job. We can help you with the query or with concrete questions. But I don't think anybody here is willing to walk you through the whole process of creating that link when your part is limited to copying and pasting his code. That's the point where we ask people to hire a programmer.

    But if you know some other forum where the users gratefully write the code for free, great, let them do it.

    All I can do is explain the query again, because you haven't fully understood it:

    The genius who designed the database has stored the dates as Unix microseconds (either because he tried to outsmart MySQL or didn't know the date datatypes). So unless you want to fumble with microseconds, you'll have to use FROM_UNIXTIME() to convert the microseconds into actual dates. That's what
    Code:
    FROM_UNIXTIME(thedate)
    does. You now have an actual date like '3/11/2012'.

    To extract the year from the date, you do not use this '%Y' stuff. That's for outputting pretty date strings. The correct way is
    Code:
    EXTRACT(YEAR FROM FROM_UNIXTIME(thedate))
    Now you have an actual year like 2012 or 1998, that microseconds stuff is gone. So to get the articles starting with 2012, you'd say
    Code:
    EXTRACT(YEAR FROM FROM_UNIXTIME(thedate)) >= 2012
    It might be a good idea to actually try this out on your local computer before putting it online.

IMN logo majestic logo threadwatch logo seochat tools logo