1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Pulling records from mySQL...

Discussion in 'PHP' started by SEbasic, Sep 23, 2005.

  1. #1
    OK, So I want to list all of the records that I have in a particular column on a wp table.

    Essentially I'm trying to make a site map query that I can just output on a page.

    So, so far I have...
    SEMrush
    $sql = 'SELECT `post_title` FROM `wp_posts` ORDER BY `post_status` ASC LIMIT 0, 1000 ';

    Now with this, I'm am going to be making the assumption that there will only ever be 1000 records to pull (Is there an infinite or something sign I can use instead?)

    So that will pull the page title.

    Then I need to pull the page url...

    $sql = 'SELECT `guid` FROM `wp_posts` ORDER BY `post_status` ASC LIMIT 0, 1000 ';

    I *think* this is all good so far...

    But I guess what I'm saying, is how do I
    a) Get these results to appear on a page
    and
    b) Make sure the Page title correlates to the page URL?

    I am trying *really* hard to learn some of this coding stuff - I just need a push in the right direction (Or maybe a slap to tell me that I'm doing it all wrong)...

    Cheers

    Oliver
     
    SEbasic, Sep 23, 2005 IP
    SEMrush
  2. JoeO

    JoeO Peon

    Messages:
    431
    Likes Received:
    29
    Best Answers:
    0
    Trophy Points:
    0
    #2
    First to do it infinite just remove "LIMIT 0, 1000" all together.

    To make the page title correlate with the url use one select statement:
    $sql = 'SELECT `post_title`, `guid` FROM `wp_posts` ORDER BY `post_status` ASC';

    To make them appear on a page:
    
    $result = mysql_query($sql);
    $num = mysql_num_rows($result);
    $i=0;
    while ($i < $num) {
        $post_title = mysql_result($result,$i,"post_title");
        $guid = mysql_result($result,$i,"guid");
        echo ("<a href=\"$guid\">$post_title</a><br>");
        $i++;
    }
    
    PHP:
     
    JoeO, Sep 23, 2005 IP
    newbie100 likes this.
  3. SEbasic

    SEbasic Peon

    Messages:
    6,318
    Likes Received:
    318
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Ah cool...

    So, sorry - Complete n00b at all this ;)

    
    $result = mysql_query($sql = 'SELECT `post_title`, `guid` FROM `wp_posts` ORDER BY `post_status` ASC';);
    $num = mysql_num_rows($result);
    $i=0;
    while ($i < $num) {
    $post_title = mysql_result($result,$i,"post_title");
    $guid = mysql_result($result,$i,"guid");
    echo ("<a href=\"$guid\">$post_title<br />");
    $i++;
    } 
    
    Code (markup):
    Is that what I need to have then?

    It's throwing up an error...
     
    SEbasic, Sep 23, 2005 IP
  4. JoeO

    JoeO Peon

    Messages:
    431
    Likes Received:
    29
    Best Answers:
    0
    Trophy Points:
    0
    #4
    Try this:

    
    $sql = "SELECT `post_title`, `guid` FROM `wp_posts` ORDER BY `post_status` ASC";
    $result = mysql_query($sql);
    $num = mysql_num_rows($result);
    $i=0;
    while ($i < $num) {
    $post_title = mysql_result($result,$i,"post_title");
    $guid = mysql_result($result,$i,"guid");
    echo ("<a href=\"$guid\">$post_title</a><br />");
    $i++;
    } 
    
    
    PHP:
     
    JoeO, Sep 23, 2005 IP
    SEbasic likes this.
  5. SEbasic

    SEbasic Peon

    Messages:
    6,318
    Likes Received:
    318
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Wow - You are a-w-e-s-o-m-e

    OK, so I don't have to ask in the future, would you mind telling me how this actually works?

    I get the SQL bit :eek:
     
    SEbasic, Sep 23, 2005 IP
  6. JoeO

    JoeO Peon

    Messages:
    431
    Likes Received:
    29
    Best Answers:
    0
    Trophy Points:
    0
    #6
    ok... well slow day at the office anyways :p

    
    //stores the sql statement in $sql
    $sql = "SELECT `post_title`, `guid` FROM `wp_posts` ORDER BY `post_status` ASC";
    //runs the sql query and stores all the results in the $result variable
    $result = mysql_query($sql);
    //counts the numbers of rows from the results of the sql statement
    $num = mysql_num_rows($result);
    //counter used to see when you have gone through all the rows
    $i=0;
    //loop used to go through each row of your result
    while ($i < $num) {
    //these 2 lines select 1 row at a time from the results (since $i goes up once every time it loops)
    $post_title = mysql_result($result,$i,"post_title");
    $guid = mysql_result($result,$i,"guid");
    //prints the results to the users end
    echo ("<a href=\"$guid\">$post_title</a><br />");
    $i++;
    }
    
    PHP:
     
    JoeO, Sep 23, 2005 IP
    dct likes this.
  7. SEbasic

    SEbasic Peon

    Messages:
    6,318
    Likes Received:
    318
    Best Answers:
    0
    Trophy Points:
    0
    #7
    Ah man that's awesome - Thanks very much indeed for that. :)
     
    SEbasic, Sep 23, 2005 IP
  8. JoeO

    JoeO Peon

    Messages:
    431
    Likes Received:
    29
    Best Answers:
    0
    Trophy Points:
    0
    #8
    no problem. Good luck with the rest ;)
     
    JoeO, Sep 23, 2005 IP
  9. SEbasic

    SEbasic Peon

    Messages:
    6,318
    Likes Received:
    318
    Best Answers:
    0
    Trophy Points:
    0
    #9
    I'll need it - this has really helped a lot though...

    I don't feel *quite* as lost ;)

    Cheers
     
    SEbasic, Sep 23, 2005 IP
  10. SEbasic

    SEbasic Peon

    Messages:
    6,318
    Likes Received:
    318
    Best Answers:
    0
    Trophy Points:
    0
    #10
    OK, so I've done a bit of this myself, I'm now just a little stuck at one bit...

    $sql = "SELECT `post_title`, `guid`, `post_date`, `post_status` FROM `wp_posts` ORDER BY `post_status` DESC";
    $result = mysql_query($sql);
    $num = mysql_num_rows($result);
    $i=0;
    while ($i < $num) {
    $post_date = mysql_result($result,$i,"post_date");
    $post_status = mysql_result($result,$i,"post_status");
    $post_title = mysql_result($result,$i,"post_title");
    $guid = mysql_result($result,$i,"guid");
    if ( $post_status == "static" ) {
    echo ("<li><a href=\"$guid\">$post_title</a></li>");
    } elseif($post_status="publish"){
    echo ("<li><a href=\"$guid\">$post_title</a> - $post_date</li>");
    }
    $i++;
    }
    Code (markup):
    Now this currently means that it echo's a list, only of pages in the DB, who's post_status is either "static" or "publish" and orders them in groups together.

    How do I add a subheading to each group?

    So I currently have
    And what I want to have is
    Does that make sense?
     
    SEbasic, Sep 25, 2005 IP
  11. dct

    dct Finder of cool gadgets

    Messages:
    3,134
    Likes Received:
    328
    Best Answers:
    0
    Trophy Points:
    230
    #11
    You could do something like this, which basically prints a header whenever the post_status changes (untested but pretty simple). You also might want to use the PHP BBCode when posting as it's make it really really pretty :)

    $sql = "SELECT `post_title`, `guid`, `post_date`, `post_status` FROM `wp_posts` ORDER BY `post_status` DESC";
    $result = mysql_query($sql);
    $num = mysql_num_rows($result);
    $i=0;
    
    $previousStatus = "";
    
    while ($i < $num) 
    {
        $post_date = mysql_result($result,$i,"post_date");
        $post_status = mysql_result($result,$i,"post_status");
        $post_title = mysql_result($result,$i,"post_title");
        $guid = mysql_result($result,$i,"guid");
    
        if($previousStatus != $post_status)
        {
            // Status has changed so print the header
            echo "<li><b>$post_status header</b></li>";
            $previousStatus = $post_status;
        }
    
    
        if ( $post_status == "static" ) 
        {
            echo ("<li><a href=\"$guid\">$post_title</a></li>");
        }
        elseif($post_status="publish")
        {
            echo ("<li><a href=\"$guid\">$post_title</a> - $post_date</li>");
        }
        $i++;
    }
    PHP:
     
    dct, Sep 25, 2005 IP
  12. SEbasic

    SEbasic Peon

    Messages:
    6,318
    Likes Received:
    318
    Best Answers:
    0
    Trophy Points:
    0
    #12
    Mate, that is f'in awesome!
    Will use the php bbcode in future...

    OK then...
    So for the headers, it is pulling the actual name in the DB out right?
     
    SEbasic, Sep 25, 2005 IP
    dct likes this.
  13. dct

    dct Finder of cool gadgets

    Messages:
    3,134
    Likes Received:
    328
    Best Answers:
    0
    Trophy Points:
    230
    #13
    The code I gave you displays post_status (from the database) and then the word Header (hard coded)
     
    dct, Sep 25, 2005 IP
    JoeO likes this.
  14. SEbasic

    SEbasic Peon

    Messages:
    6,318
    Likes Received:
    318
    Best Answers:
    0
    Trophy Points:
    0
    #14
    Great cheers mate...

    I'm working on getting it to do this with the post category now...

    Hopefylly it will work right. ;)

    Cheers
     
    SEbasic, Sep 25, 2005 IP
  15. SEbasic

    SEbasic Peon

    Messages:
    6,318
    Likes Received:
    318
    Best Answers:
    0
    Trophy Points:
    0
    #15
    So... *cough*...

    How do I give them custom names rather than pulling them directly from the DB?

    *cough*
     
    SEbasic, Sep 26, 2005 IP
  16. dct

    dct Finder of cool gadgets

    Messages:
    3,134
    Likes Received:
    328
    Best Answers:
    0
    Trophy Points:
    230
    #16
    Where are the customs names stored, or do you want to be a lazy git and hardcode them in the code.
     
    dct, Sep 26, 2005 IP
  17. SEbasic

    SEbasic Peon

    Messages:
    6,318
    Likes Received:
    318
    Best Answers:
    0
    Trophy Points:
    0
    #17
    I wan't to be a lazy git

    (Sorry took so long to reply - needed a reeboot)...
     
    SEbasic, Sep 26, 2005 IP
  18. dct

    dct Finder of cool gadgets

    Messages:
    3,134
    Likes Received:
    328
    Best Answers:
    0
    Trophy Points:
    230
    #18
    Do something like
    
        if($previousStatus != $post_status)
        {
            // Status has changed so print the header
            $previousStatus = $post_status;
            if ( $post_status == "static" )
            {
                echo "<li><b>Lazy Bastard Static header</b></li>";
            }
            else if ( $post_status == "publish" )
            {
                echo "<li><b>Horribly hardcode publish header</b></li>";
            }
        }    
    
    PHP:
    Now hang your head in shame hard coder.
     
    dct, Sep 26, 2005 IP
  19. SEbasic

    SEbasic Peon

    Messages:
    6,318
    Likes Received:
    318
    Best Answers:
    0
    Trophy Points:
    0
    #19
    Hehe - yeah, man - I need help... :eek:
    You must spread some Reputation around before giving it to dct again.
     
    SEbasic, Sep 26, 2005 IP
  20. dct

    dct Finder of cool gadgets

    Messages:
    3,134
    Likes Received:
    328
    Best Answers:
    0
    Trophy Points:
    230
    #20
    That you still need help, or an appology for being a hardcoder
     
    dct, Sep 26, 2005 IP