php multiple databases issue

问题:

I've set two database conections as below

$con1 = mysql_connect("localhost", "root", "pwd") or die (mysql_error());

$con2 = mysql_connect("localhost", "wordpress", "pwd", true) or die(mysql_error());

mysql_select_db("lab_ancp", $con1) or die(mysql_error());   
mysql_select_db("wordpress",$con2) or die(mysql_error());

and it works fine

so then i do some queries on a page like this:

$sql="select unome from associado where uid=$uid";
    $result=mysql_query($sql,$con1) or die(mysql_error());

and it works fine, after that i do a second query like this:

$sql="select ID, post_content, post_title, post_excerpt, meta_value
                    from wp_posts join (
                        select post_id, meta_value 
                        from wp_postmeta 
                            join (
                                select post_id from wp_postmeta
                                where meta_key='destaque' and meta_value='s'
                            )as t1 using(post_id)
                        where meta_key='pft_widescreen'
                    ) as t2 on (wp_posts.ID=t2.post_id)
                ORDER BY RAND() LIMIT 1";
            //echo $sql . "<br />";
            $row=mysql_fetch_assoc(mysql_query($sql,$con2)) or die(mysql_error());

and again everything is just fine, but then....

$sql="select * from eventos where edatade>='$hoje' or edataate>='$hoje'";
            $result=mysql_query($sql, $con1) or die (mysql_error());

gives this error:

**

SELECT command denied to user 'wordpress'@'localhost' for table 'eventos'

**

回答1:

From the error it seems you should verify permissions for the wordpress user on the eventos table. Your code seems to be correct.

If you want to verify this, maybe try a "SELECT * from eventos" using the second connection. Do this as the first query in the script.

回答2:

Well

Its solved.

Don't askme the reason but i've tried to change the order in the first two roww, i.e put $con2 before $con1 and the queries now simply work fine.

I suspect that the ..."true" parameter has something to do with that.

Thx guys.

回答3:

http://se2.php.net/manual/en/function.mysql-select-db.php#39095 http://se2.php.net/manual/en/function.mysql-select-db.php#93487

Seems to be a problem with the mysql_select_db, the second link is one solution.

I would recommend using phps mysqli (MySQL Improved Extension) instead of old mysql stuff (don't know if it solves your problem, but it solves other problems you might walk in to).

回答4:

I suspect that the reason is that the connection is still in memory so when you try to execute the second query, this use the last connection. I have the similar problem with Joomla two databases use issue. I am looking for the fix for this problem, I think that is a missing rule in our code.

---

HEY!!! I get it, the problem is that when you create the second connection, for default PHP return the same reference for the last one. So if you need a new connection you should prepare a mysql_connect with $new_link to true, like this

$myconn = @mysql_connect($this->db2_host,$this->db2_user,$this->db2_pass, true);

The last parameter means that you need a new reference and not the last one. You can find more information:

HERE and HERE

Hope it helps.