SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 27

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    May 2004
    Location
    nyc
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to put query to work

    Hi, I'm using the following code to display my entire table on my front page:

    $query[550] = mysql_query("SELECT * FROM journal_blog ORDER BY id DESC");
    print mysql_error();
    while($result = mysql_fetch_array($query[550])) {
    }

    I hope this makes sense....how would I control the LIMIT value so that a certain amount of entries displays on the page followed by a link that when clicked would display that same amount of entries that were added to the table previously. I want my code to generate this result by calculting the amount of entries currently present in this particular table.

    Basically I just want my front page to display 10 entries and a link which wouldd link to a paage that would display thee 10 entries posted prior to these entries. However, If I'd do this maanualy, I'd have to update it manulay evereytime the table grows by additional 10 tables by having to create new links and new pages, so basically, I need a script that would do this for me.

  2. #2
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi

    you could add LIMIT 10 at the end of your query, but that would do the trick only if you wanted to display the first 10 results, period.

    I think that this article has all the answers you need

    http://www.sitepoint.com/article/php-paging-result-sets

  3. #3
    SitePoint Member
    Join Date
    May 2004
    Location
    nyc
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    HI,
    I've tried that tutorial and I wasn't able to make it work with my current query. I already have an include on my php page which contains database connection scripts. This is how my current query looks:

    <?php
    $query[550] = mysql_query("SELECT * FROM journal_blog ORDER BY id DESC");
    print mysql_error();
    while($result = mysql_fetch_array($query[550])) {
    $temp_id = $result['id'];
    $temp_date = convdate($result['timestamp']);
    $temp_user = $result['user'];
    $temp_title = $result['title'];
    $temp_views = $result['views'];
    $temp_content = stripslashes($result['content']);
    $query[758] = mysql_query("SELECT count(*) AS total FROM journal_comment WHERE linkid=$temp_id");
    $comments = mysql_fetch_array($query[758]);
    if($comments['total'] < 1) {
    $comments['total'] = 0;
    }
    print("<table width=\"92%\"><tr><td class=\"NewThreadTop\"><span class=\"style1\"> " . $temp_title . "</span>&nbsp;&nbsp;" .$temp_date. "</td></tr><tr><td class=\"NewThreadMiddle\">" . $temp_content . "</td></tr><td class=\"NewThreadBottom\"><a href=\"viewblog.php?id=" .$temp_id. "\">view comments</a><span class=\"style2\"> (" . $comments['total'] . ")</span>&nbsp;|&nbsp;<a href=\"addcomment.php?id=" .$temp_id. "\">add comment</a></td></tr><tr><td>&nbsp;</td></tr><tr><td>&nbsp;</td></tr></table>");
    } //End While
    ?>


    Currently it displays all rows but I'd like to have the ability of splitting it up into 10 rows a page.

  4. #4
    SitePoint Member
    Join Date
    May 2004
    Location
    nyc
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can anyone help please?

  5. #5
    SitePoint Addict The Mog's Avatar
    Join Date
    Dec 2002
    Location
    Manchester UK
    Posts
    310
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try

    [PHP]
    $query[550] = mysql_query("SELECT * FROM journal_blog ORDER BY id DESC LIMIT 0,10");
    [PHP]

  6. #6
    SitePoint Member
    Join Date
    May 2004
    Location
    nyc
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by The Mog
    try

    [PHP]
    $query[550] = mysql_query("SELECT * FROM journal_blog ORDER BY id DESC LIMIT 0,10");
    [PHP]
    Well yes that would work, but then I'd need to create additional pages with the following query modified ie ... 10,20 .... 20,30 etc and I'd need to keep on updating this page everytime the table grew by 10 rows. instead I'm looking for a way to have the query be able to to display 10 rows and then output links such as 'next' and 'back' based on the size of the table.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    step 1 is to decide which fields you actually want to display, and then SELECT only those fields, because you're going to list them in the GROUP BY of the following query

    step 2 is to combine your query-inside-a-loop (which is an absolute performance killer) with the first query like this --
    Code:
    select B.id
         , B.dateadded
         , B.title
         , count(C.blog_id) as comments
      from journal_blog  B
    inner
      join journal_comment  C  
        on B.id = C.blog_id
    group
        by B.id
         , B.dateadded
         , B.title
    order
        by id desc
     limit 0,10
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    Now with customized title Jump's Avatar
    Join Date
    Sep 2002
    Location
    The Restaurant at The End of The Universe
    Posts
    1,423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You need to do a search on "pagination" then.

  9. #9
    SitePoint Member
    Join Date
    May 2004
    Location
    nyc
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Jump
    You need to do a search on "pagination" then.
    I've tried over 20 methods from various tutorials I found and I can't get any of them to work with my query. If I don't find a solution soon, I'm going to have to rewrite all of my queries.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    what does your query look like now?

    as for next/prev links, see this thread --
    http://www.sitepoint.com/forums/showthread.php?p=969764
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Member
    Join Date
    May 2004
    Location
    nyc
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's my current query which has no limit for now(functions such as convdate, stripslashes etc are gotten from header.php file which is an include at top of the paage):

    <?php
    $query[550] = mysql_query("SELECT * FROM journal_blog ORDER BY id DESC LIMIT 5");
    while($result = mysql_fetch_array($query[550])) {
    $temp_id = $result['id'];
    $temp_date = convdate($result['timestamp']);
    $temp_user = $result['user'];
    $temp_title = $result['title'];
    $temp_views = $result['views'];
    $temp_content = stripslashes($result['content']);
    $query[758] = mysql_query("SELECT count(*) AS total FROM journal_comment WHERE linkid=$temp_id");
    $comments = mysql_fetch_array($query[758]);
    if($comments['total'] < 1) {
    $comments['total'] = 0;
    }
    print("<table width=\"92%\"><tr><td class=\"NewThreadTop\"><span class=\"style1\"> " . $temp_title . "</span>&nbsp;&nbsp;" .$temp_date. "</td></tr><tr><td class=\"NewThreadMiddle\">" . $temp_content . "</td></tr><td class=\"NewThreadBottom\"><a href=\"viewblog.php?id=" .$temp_id. "\">view comments</a><span class=\"style2\"> (" . $comments['total'] . ")</span>&nbsp;|&nbsp;<a href=\"addcomment.php?id=" .$temp_id. "\">add comment</a></td></tr><tr><td>&nbsp;</td></tr><tr><td>&nbsp;</td></tr></table>");

    ?>

    I can use the following query to get the amount of rows:
    <?php
    $query = "SELECT COUNT(*) AS total FROM journal_blog";
    $result = mysql_query($query);
    $total = mysql_result($result,0,"total");
    print ".$total.";

  12. #12
    SitePoint Member
    Join Date
    May 2004
    Location
    nyc
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's my current query which has no limit for now(functions such as convdate, stripslashes etc are gotten from header.php file which is an include at top of the paage):

    <?php
    $query[550] = mysql_query("SELECT * FROM journal_blog ORDER BY id DESC");
    while($result = mysql_fetch_array($query[550])) {
    $temp_id = $result['id'];
    $temp_date = convdate($result['timestamp']);
    $temp_user = $result['user'];
    $temp_title = $result['title'];
    $temp_views = $result['views'];
    $temp_content = stripslashes($result['content']);
    $query[758] = mysql_query("SELECT count(*) AS total FROM journal_comment WHERE linkid=$temp_id");
    $comments = mysql_fetch_array($query[758]);
    if($comments['total'] < 1) {
    $comments['total'] = 0;
    }
    print("<table width=\"92%\"><tr><td class=\"NewThreadTop\"><span class=\"style1\"> " . $temp_title . "</span>&nbsp;&nbsp;" .$temp_date. "</td></tr><tr><td class=\"NewThreadMiddle\">" . $temp_content . "</td></tr><td class=\"NewThreadBottom\"><a href=\"viewblog.php?id=" .$temp_id. "\">view comments</a><span class=\"style2\"> (" . $comments['total'] . ")</span>&nbsp;|&nbsp;<a href=\"addcomment.php?id=" .$temp_id. "\">add comment</a></td></tr><tr><td>&nbsp;</td></tr><tr><td>&nbsp;</td></tr></table>");

    ?>

    I can use the following query to get the amount of rows:
    <?php
    $query = "SELECT COUNT(*) AS total FROM journal_blog";
    $result = mysql_query($query);
    $total = mysql_result($result,0,"total");
    print ".$total.";

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    yes indeed, that looks like a query in a loop

    you want the number of comments per blog entry, and as things stand, in order to get that in one query, you'd have to include the 'content' field in a GROUP BY, and that won't fly

    what exactly are you trying to display, and what is the question you're facing?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Member
    Join Date
    May 2004
    Location
    nyc
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    yes indeed, that looks like a query in a loop

    you want the number of comments per blog entry, and as things stand, in order to get that in one query, you'd have to include the 'content' field in a GROUP BY, and that won't fly

    what exactly are you trying to display, and what is the question you're facing?
    The result of the query prints out links, titles, dates and number of comments next to each corresponding row allowing the user to view/add comments for that particular entry.

    As the query is set up now for the journal_blog table, it prints out all of my current rows in table journal_blog, instead I'd like to set-up a limit as well as some type of an if-else statement which would only display 10 rows per page and print out a link at bottom to view the previous 10 rows. In the else statement I'd like to display some type of text such as "there are no more entries", if the current query has reached the last row.

    I'm very new to php and mysql, prior to this I only have a html/photoshop/flash baackground. I know that I can display the 10 newest rows by ussing (select 0,10) and then use 10, 20 on the next page. However, that would be extremely annoying because as the table grew in the amount of rows containeed I'd haave to keep on creating new pages for these results, instead I'm looking to modify my current query so that it may do the work on its own based on the amount rows currently present in the table.

    Here's how the journal currently looks:
    http://jessica.livemotionstudio.com
    (as of right now, I'm not running select in my query so that all rows print out on the page)
    Last edited by livemotion; May 18, 2004 at 11:15.

  15. #15
    SitePoint Member
    Join Date
    May 2004
    Location
    nyc
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hey r937 can you help me out?

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    what i meant by "what exactly are you trying to display" is which fields

    you've answered this nicely: links, titles, dates and number of comments

    and do not worry about "creating new pages" for each new set of 10 rows

    you will need only one page, and it handles all situations

    to call this page, what you could do is to pass it something like "start with row M, go for N rows"

    then this page does all the work, whether you pass in "1,10" or "931,10"

    this single page therefore must not only display the right rows based on M,N (you will need to subtract 1 from M, to use as the offset in the LIMIT clause), but it must also generate the appropriate "next" and "prev" links

    but the real problem i was referring to, which you have resolved, is that for this particular case, your query does not actually need the blog "content" field itself (typically stored in a TEXT or BLOB field) and hence the GROUP BY will actually work

    Code:
    select B.link
         , B.title
         , B.pubdate
         , count(C.blog_id) as comments 
      from journal_blog B
    left outer
      join journal_comment C
        on B.id = C.blog_id       
    group
        by B.link
         , B.title
         , B.pubdate
    order 
        by B.pubdate desc
     limit 930,10
    the ORDER BY is crucial to this working correctly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Member
    Join Date
    May 2004
    Location
    nyc
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi
    I formated my query:

    $query[550] = mysql_query("SELECT B.id, B.title, B.timestamp, B.content, count( C.linkid ) AS comments'
    ********. ' FROM journal_blog B'
    ********. ' LEFT OUTER '
    ********. ' JOIN journal_comment C ON B.id = C.linkid'
    ********. ' GROUP '
    ********. ' BY B.id, B.title, B.timestamp, B.content'
    ********. ' ORDER '
    ********. ' BY B.id DESC '
    ********. ' LIMIT 930 , 10'");
    while($result = mysql_fetch_array($query[550])) {
    $temp_id = $result['id'];
    $temp_date = convdate($result['timestamp']);
    $temp_user = $result['user'];
    $temp_title = $result['title'];
    $temp_views = $result['views'];
    $temp_content = stripslashes($result['content']);
    if($comments['total'] < 1) {
    $comments['total'] = 0;
    }
    print("<table width=\"92%\"><tr><td class=\"NewThreadTop\"><span class=\"style1\"> " . $temp_title . "</span>&nbsp;&nbsp;" .$temp_date. "</td></tr><tr><td class=\"NewThreadMiddle\">" . $temp_content . "</td></tr><td class=\"NewThreadBottom\"><a href=\"viewblog.php?id=" .$temp_id. "\">view comments</a><span class=\"style2\"> (" . $comments['total'] . ")</span>&nbsp;|&nbsp;<a href=\"addcomment.php?id=" .$temp_id. "\">add comment</a></td></tr><tr><td>&nbsp;</td></tr><tr><td>&nbsp;</td></tr></table>");
    } //End While

    and got the folloing error:
    Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

    The table journal_blog is order by id, which also auto-increments itself as a new thread is added, so I need to order by the column id, additionally, I'm not sure what starting at row 930 would do sincee there is no row 930 yet.

  18. #18
    SitePoint Member
    Join Date
    May 2004
    Location
    nyc
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    this is how my journal blog table is set-up:

    CREATE TABLE `journal_blog` (
    `id` int(10) NOT NULL auto_increment,
    `timestamp` varchar(20) NOT NULL default '',
    `title` varchar(255) NOT NULL default '',
    `user` varchar(100) NOT NULL default '',
    `content` text NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `id` (`id`)
    ) TYPE=MyISAM AUTO_INCREMENT=34 ;

    This is how my journal_comment table is setup
    CREATE TABLE `journal_comment` (
    `id` int(10) NOT NULL auto_increment,
    `linkid` int(10) NOT NULL default '0',
    `timestamp` varchar(20) NOT NULL default '',
    `title` varchar(255) NOT NULL default '',
    `content` text NOT NULL,
    `user` varchar(100) NOT NULL default '',
    `email` varchar(100) NOT NULL default '',
    `ip` varchar(30) NOT NULL default '',
    PRIMARY KEY (`id`),
    UNIQUE KEY `id` (`id`)
    ) TYPE=MyISAM COMMENT='comments for threads' AUTO_INCREMENT=53 ;

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    you do not need to sort by id, you said you wanted the newest, so just ORDER BY B.timestamp DESC

    the 930 was the example starting number (see my earlier post)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Member
    Join Date
    May 2004
    Location
    nyc
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't see how the query you wrote for me helps. The current query that I have works, the only problem that I'm having is creating if and else statements for my current query that would allow me to pass a variable in limit so that I may display a specific amounts of rows per page.

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    well, you'd use a php variable for the LIMIT, and substitute that into the query where i've got the 930
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    SitePoint Member
    Join Date
    May 2004
    Location
    nyc
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Group by's are still confusing to me. I've modified my queries and I'm able to display the amount rows in my table and the amount of rows currently displayed.

    <?php
    if(!isset($startonrow)) $startonrow = 0;
    if(!isset($displaayrows)) $displayrows = 10;
    ?>
    <?php
    $query = mysql_query("SELECT * FROM journal_blog ORDER BY id DESC LIMIT " . $startonrow. ", " . $displayrows ."");
    while($result = mysql_fetch_array($query)) {
    $temp_id = $result['id'];
    $temp_date = convdate($result['timestamp']);
    $temp_user = $result['user'];
    $temp_title = $result['title'];
    $temp_views = $result['views'];
    $temp_content = stripslashes($result['content']);
    $query2 = mysql_query("SELECT count(*) AS total FROM journal_comment WHERE linkid=$temp_id");
    $comments = mysql_fetch_array($query2);
    if($comments['total'] < 1) {
    $comments['total'] = 0;
    }
    print("<table width=\"92%\"><tr><td class=\"NewThreadTop\"><span class=\"style1\"> " . $temp_title . "</span>&nbsp;&nbsp;" .$temp_date. "</td></tr><tr><td class=\"NewThreadMiddle\">" . $temp_content . "</td></tr><td class=\"NewThreadBottom\"><a href=\"viewblog.php?id=" .$temp_id. "\">view comments</a><span class=\"style2\"> (" . $comments['total'] . ")</span>&nbsp;|&nbsp;<a href=\"addcomment.php?id=" .$temp_id. "\">add comment</a></td></tr><tr><td>&nbsp;</td></tr><tr><td>&nbsp;</td></tr></table>");
    } //End While
    ?>
    <?php
    $query = "SELECT COUNT(*) AS total FROM journal_blog";
    $result = mysql_query($query);
    $total = mysql_result($result,0,"total");
    print("<table width=\"92%\"><tr><td>________________________________________________<br> <a href=\"#top\">Return to top</a> | there are ".$total." threads | there are ".$displayrows." displayed on page</td></tr></table>");
    ?>

    startonrow variable is 0 on the current page
    total variable is the number of rows on the table
    All I Need now is to proper way to create if and else statements that would pass the value of variable $startonrow, increase it by 10 (as long as its value is = or less then the variable $total) and print out a link passing the new value of variable startonrow

    I realize the code is a bit 'dirty', but I understand it and it works.

  23. #23
    SitePoint Member
    Join Date
    May 2004
    Location
    nyc
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Lol well, I managed to figure it out on my own AND IT WORKS!

    here are the if and else statements to display previous and next threads based on the queries I listed in my previous comment.

    <?php
    $query = "SELECT COUNT(*) AS total FROM journal_blog";
    $result = mysql_query($query);
    $total = mysql_result($result,0,"total");
    print("<table width=\"92%\"><tr><td>________________________________________________<br> <a href=\"#top\">Return to top</a>");
    ?>
    <?php
    if ($total > ($startonrow + 5)) {
    print(" | <a href=\"index_display_threads2.php?startonrow=".($startonrow +5)."\">View Previous Threads</a>");
    }
    ?>
    <?php
    if ($startonrow > 0) {
    print(" | <a href=\"index_display_threads2.php?startonrow=".($startonrow -5)."\">View Newest Threads</a>");
    }
    ?>
    </td>
    </tr>
    </table>

    Modify value 5 to the amount of threads you wish to display per page. Maybe I'll amke a tuitorial for thsi on my siste since I've noticed that a lot of the totoriaals for paagination simply don't work.

  24. #24
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    tutorial would be a good idea, but please, don't use a query inside a loop in your example -- it's not only a performance killer, but a credibility killer too

    go back and have a look at my GROUP BY query in post #6

    run it through phpmyadmin until you're comfortable with it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  25. #25
    SitePoint Member
    Join Date
    May 2004
    Location
    nyc
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    tutorial would be a good idea, but please, don't use a query inside a loop in your example -- it's not only a performance killer, but a credibility killer too

    go back and have a look at my GROUP BY query in post #6

    run it through phpmyadmin until you're comfortable with it
    I'm a little confused by the GROUP BY query, do i need to select all colums within it? I'm already using $result to define each one.

    and would this be the correct way to set up the those queries:
    SELECT B.id, B.timestamp, B.user, B.title, B.views, B.content, count( C.linkid ) AS comments
    FROM journal_blog B
    LEFT OUTER
    JOIN journal_comment C ON B.id = C.linkid
    GROUP BY B.id, B.timestamp, B.user, B.title, B.views, B.content
    ORDER
    BY B.id DESC
    LIMIT 0 , 10

    0 and 10 would be replaces with the variables pf course.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •