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

    daboss Guest

    Messages:
    2,249
    Likes Received:
    151
    Best Answers:
    0
    Trophy Points:
    0
    #41
    try this out:
    SEMrush
    $mysql_access = mysql_connect("localhost", "user", "pass") or die("Cannot connect to DB!");
    mysql_select_db( "DBname") or die("Cannot select DB!");
    
    $sql = "SELECT asin, name FROM freebs_items ORDER BY name DESC";
    $result = mysql_query($sql);
    
    $num = mysql_affected_rows();
    print "number of records found = " . $num;
    
    $i=0;
    while ($i < $num) {
       $book_name = mysql_result($result,$i,"name");
       $book_asin = mysql_result($result,$i,"asin");
       echo ("<a href=\"$book_asin\">$book_name</a><br />");
       $i++;
    }
    Code (markup):
     
    daboss, Oct 17, 2005 IP
    SEMrush
  2. SEbasic

    SEbasic Peon

    Messages:
    6,318
    Likes Received:
    318
    Best Answers:
    0
    Trophy Points:
    0
    #42
    OK cool - that worked, but now the other elements on the page are trying to pull content from that DB too...

    I tried adding this to the end of the query, but it didn't seem to do the job...
    mysql_close($link);

    I *think* I'm on the right lines here...
     
    SEbasic, Oct 17, 2005 IP
  3. daboss

    daboss Guest

    Messages:
    2,249
    Likes Received:
    151
    Best Answers:
    0
    Trophy Points:
    0
    #43
    what do you mean by this?

    in any event, in front of database code, you need to specify this again:
    mysql_select_db("DBname") or die("Cannot select DB!");

    replacing "DBname" with whatever database you want the code to use...
     
    daboss, Oct 17, 2005 IP
  4. SEbasic

    SEbasic Peon

    Messages:
    6,318
    Likes Received:
    318
    Best Answers:
    0
    Trophy Points:
    0
    #44
    Tried that - does the same as mentioned above...

    Including the ones for the side bar include statements?
    Yep - done that...

    It's just the lnclued in other parts of the template are now not working...

    this will be so much easier...

    http://www.oweb.co.uk/site-map/
     
    SEbasic, Oct 17, 2005 IP
  5. daboss

    daboss Guest

    Messages:
    2,249
    Likes Received:
    151
    Best Answers:
    0
    Trophy Points:
    0
    #45
    hmmm...

    The error says:
    [Table 'stretch_owebbs.wp_posts' doesn't exist]
    SELECT ID, post_title FROM wp_posts WHERE post_status = 'publish' AND post_password ='' ORDER BY post_date DESC LIMIT 0, 5

    is there a table named 'wp_posts' in database 'stretch_owebbs'?
     
    daboss, Oct 17, 2005 IP
  6. SEbasic

    SEbasic Peon

    Messages:
    6,318
    Likes Received:
    318
    Best Answers:
    0
    Trophy Points:
    0
    #46
    Nope - but the table it *should* be using isn't in that DB - it's in the usual site one - stretch_wrdp1

    So I tried closing the connestion to owebbs from our previous query by using mysql_close($mysql_access);

    But that only casused an error to be repeated over and over between each item...

    I added the "close connection" line underneath
    $i++;

    Is that right?
     
    SEbasic, Oct 17, 2005 IP
  7. daboss

    daboss Guest

    Messages:
    2,249
    Likes Received:
    151
    Best Answers:
    0
    Trophy Points:
    0
    #47
    ah, i get it... your wordpress script is trying to use the database you specified in:

    $mysql_access = mysql_connect("localhost", "user", "pass") or die("Cannot connect to DB!");
    mysql_select_db( "DBname") or die("Cannot select DB!");

    what you can do is:
    $mysql_access = mysql_connect("localhost", "user", "pass") or die("Cannot connect to DB!");
    mysql_select_db( "DBname", $mysql_access) or die("Cannot select DB!");

    make sure the $mysql_access is a unique variable name not used in wordpress...
     
    daboss, Oct 17, 2005 IP
  8. daboss

    daboss Guest

    Messages:
    2,249
    Likes Received:
    151
    Best Answers:
    0
    Trophy Points:
    0
    #48
    yes, closing the connection would work too :)

    you need to add it after the while loop:

    while ($i < $num) {
    $book_name = mysql_result($result,$i,"name");
    $book_asin = mysql_result($result,$i,"asin");
    echo ("<a href=\"$book_asin\">$book_name</a><br />");
    $i++;
    }
    mysql_close($mysql_access);

    not after the $1++;
     
    daboss, Oct 17, 2005 IP
  9. SEbasic

    SEbasic Peon

    Messages:
    6,318
    Likes Received:
    318
    Best Answers:
    0
    Trophy Points:
    0
    #49
    Oh ok cool - so what does that do?

    Simply adding "$mysql_access" doesn't work...

    You said I need to make it a unique variable - changing it to $mysql_bsaccess gives me

    Warning: mysql_select_db(): supplied argument is not a valid MySQL-Link resource in /home/stretch/public_html/oweb/wp-content/plugins/phpinposts.php(40) : eval()'d code on line 3
    Cannot select DB!

    I've got this all wrong - clearly...
     
    SEbasic, Oct 17, 2005 IP
  10. SEbasic

    SEbasic Peon

    Messages:
    6,318
    Likes Received:
    318
    Best Answers:
    0
    Trophy Points:
    0
    #50
    Ignore that last post...

    I now have this...

    <phpcode>
    $mysql_bsaccess = mysql_connect("localhost", "USER", "PASS") or die("Cannot connect to DB!");
    mysql_select_db( "stretch_owebbs", $mysql_bsaccess) or die("Cannot select DB!");
    $sql = "SELECT asin, name FROM freebs_items ORDER BY name DESC";
    $result = mysql_query($sql);

    $num = mysql_affected_rows();
    print "number of records found = " . $num;

    $i=0;
    while ($i < $num) {
    $book_name = mysql_result($result,$i,"name");
    $book_asin = mysql_result($result,$i,"asin");
    echo ("<li><a href=\"$book_asin\">$book_name</a>");
    $i++;
    }</phpcode>

    But it's not doing the trick...

    Closing it didn't work either... - Scratch that, just saw your last post...

    Now I get this where the sidebar content should be appearing -

    Warning: Invalid argument supplied for foreach() in /home/stretch/public_html/oweb/my-hacks.php on line 9

    I got this far using this...
    $mysql_access = mysql_connect("localhost", "USER", "PASS") or die("Cannot connect to DB!");
    mysql_select_db( "stretch_owebbs") or die("Cannot select DB!");
    
    $sql = "SELECT asin, name FROM freebs_items ORDER BY name DESC";
    $result = mysql_query($sql);
    
    $num = mysql_affected_rows();
    print "number of records found = " . $num;
    
    $i=0;
    while ($i < $num) {
       $book_name = mysql_result($result,$i,"name");
       $book_asin = mysql_result($result,$i,"asin");
       echo ("<li><a href=\"$book_asin\">$book_name</a></li>");
       $i++;
    }
    mysql_close($mysql_access);
    PHP:
     
    SEbasic, Oct 17, 2005 IP
  11. daboss

    daboss Guest

    Messages:
    2,249
    Likes Received:
    151
    Best Answers:
    0
    Trophy Points:
    0
    #51
    did you try closing the connection as suggested in post #48?

    otherwise:

    if you want to use $mysql_bsaccess, make sure you've got it in both the mysql_connect and the mysql_select_db.

    see the following:

    $mysql_bsaccess = mysql_connect("localhost", "user", "pass") or die("Cannot connect to DB!");
    mysql_select_db( "DBname", $mysql_bsaccess) or die("Cannot select DB!");
     
    daboss, Oct 17, 2005 IP
  12. SEbasic

    SEbasic Peon

    Messages:
    6,318
    Likes Received:
    318
    Best Answers:
    0
    Trophy Points:
    0
    #52
    Yep - tried that, still getting the
    "Warning: Invalid argument supplied for foreach() in /home/stretch/public_html/oweb/my-hacks.php on line 9"

    Displayed now...

    Sorry about this hassle - I get confused with all this pretty easily...
     
    SEbasic, Oct 17, 2005 IP
  13. daboss

    daboss Guest

    Messages:
    2,249
    Likes Received:
    151
    Best Answers:
    0
    Trophy Points:
    0
    #53
    well, at least the menus are appearing now... :) making some headway... you mind posting line 9 of /home/stretch/public_html/oweb/my-hacks.php?
     
    daboss, Oct 17, 2005 IP
    SEbasic likes this.
  14. SEbasic

    SEbasic Peon

    Messages:
    6,318
    Likes Received:
    318
    Best Answers:
    0
    Trophy Points:
    0
    #54
    Of course... :)

    Only I'm not sure that's the issue - there are 2 things that need to be displayed on the left, but the first is the only one actually outputting an error...

    myhacks is...
    <?php
    function get_recent_posts($no_posts = 5, $before = '<li>', $after = '</li>', $show_pass_post = false, $skip_posts = 0) {
        global $wpdb, $tableposts;
        $request = "SELECT ID, post_title FROM $tableposts WHERE post_status = 'publish' ";
            if(!$show_pass_post) { $request .= "AND post_password ='' "; }
        $request .= "ORDER BY post_date DESC LIMIT $skip_posts, $no_posts";
        $posts = $wpdb->get_results($request);
        $output = '';
        foreach ($posts as $post) {
            $post_title = stripslashes($post->post_title);
            $permalink = get_permalink($post->ID);
            $output .= $before . '<a href="' . $permalink . '" rel="bookmark" title="Permanent Link: ' . $post_title . '">' . $post_title . '</a>' . $after;
        }
        echo $output;
    }
    ?>
    
    PHP:
     
    SEbasic, Oct 17, 2005 IP
  15. daboss

    daboss Guest

    Messages:
    2,249
    Likes Received:
    151
    Best Answers:
    0
    Trophy Points:
    0
    #55
    sebasic, i'm fairly certain that you're hitting this problem because you have multiple databases on the same server and although you call mysql_connect with a new handle, mysql_connect actually returns an existing handle if the parameters you specify for mysql_connect are the same.

    in your case, you're calling:
    $mysql_access = mysql_connect("localhost", "USER", "PASS") or die("Cannot connect to DB!");
    mysql_select_db( "stretch_owebbs", $mysql_access) or die("Cannot select DB!");

    are the parameters in blue the same as those used by your other database? if so, you can try:

    $mysql_access = mysql_connect("127.0.0.1", "USER", "PASS") or die("Cannot connect to DB!");
    mysql_select_db( "stretch_owebbs", $mysql_access) or die("Cannot select DB!");
     
    daboss, Oct 17, 2005 IP
  16. SEbasic

    SEbasic Peon

    Messages:
    6,318
    Likes Received:
    318
    Best Answers:
    0
    Trophy Points:
    0
    #56
    Yeah they prolly are the same...

    They were...

    And now - I get this again where the stretch_owebbs is supposed to output...

    Parse error: parse error, unexpected T_STRING in /home/stretch/public_html/oweb/wp-content/plugins/phpinposts.php(40) : eval()'d code on line 2

    That's after I changed 'localhost' to '127.0.0.1'
     
    SEbasic, Oct 17, 2005 IP
  17. daboss

    daboss Guest

    Messages:
    2,249
    Likes Received:
    151
    Best Answers:
    0
    Trophy Points:
    0
    #57
    is it working now?
     
    daboss, Oct 17, 2005 IP
  18. daboss

    daboss Guest

    Messages:
    2,249
    Likes Received:
    151
    Best Answers:
    0
    Trophy Points:
    0
    #58
    ah... good... at least it's not a database problem anymore... :)

    care to post:
    /home/stretch/public_html/oweb/wp-content/plugins/phpinposts.php(40) : eval()'d code on line 2
     
    daboss, Oct 17, 2005 IP
  19. SEbasic

    SEbasic Peon

    Messages:
    6,318
    Likes Received:
    318
    Best Answers:
    0
    Trophy Points:
    0
    #59
    This is the whole of that particular plugin...
    <?php
    /*
    Plugin Name: Run PHP
    Version: 0.2.2
    Plugin URI: http://mark.scottishclimbs.com/archives/2004/07/02/running-php-in-wordpress-posts/
    Description: Allows PHP to run in posts by using custom HTML style tags
    Author: Mark Somerville
    Author URI: http://mark.scottishclimbs.com/
    */
    
    /*
    run_php
    This function runs and outputs PHP code that exists within <phpcode></phpcode> tags.
    */
    
    function run_php($data) {
        $tag = "phpcode";
        $taglength = strlen($tag);
    
        //html_entity_decode doesn't work! These lines change ' and " instead. Maybe other entities needed?
        $data = str_replace(array("‘", "’"), "'",$data);
        $data = str_replace(array("”", "“"), '"', $data);
        $data = str_replace("&Prime;", '"', $data);
        $data = str_replace("&prime;", "'", $data);
    
        while($phpstart = strpos($data, "<".$tag.">")) {
            $phpend = strpos($data, "</".$tag.">");
            $phptrueend = $phpend + $taglenth + 3 + $taglength;
            $before = substr($data, 0, $phpstart);
            $after = substr($data, $phptrueend);
            $phpcodelength = $phpend - ($phpstart+2+$taglength);
            $phpcode = substr($data, $phpstart+2+$taglength, $phpcodelength);
    
            //remove the <br /> and <p></p> tags that WP adds to the code
            $phpcode = str_replace('<br />', ' ', $phpcode);
            $phpcode = str_replace('<p>', '', $phpcode);
            $phpcode = str_replace('</p>', '', $phpcode);
    
            ob_start();
            eval($phpcode);
            $data = $before.ob_get_clean().$after;
        }
        return $data;
    }
    
    add_filter('the_content', 'run_php');
    ?>
    PHP:
    It lets you run php queries within a wordpress post by using the tag <phpcode></phpcode> instead of <?php ?>
     
    SEbasic, Oct 17, 2005 IP
  20. johnt

    johnt Peon

    Messages:
    178
    Likes Received:
    21
    Best Answers:
    0
    Trophy Points:
    0
    #60
    could you put
    echo "<pre>$phpcode</pre>";
    PHP:
    before the eval line? That will tell you what code is being executed and should help you determine the problem
     
    johnt, Oct 17, 2005 IP