Connecting to a DB inside a code section?

[gav]

Hi

Can anyone tell me how to connect to a mysql database running on the same host as the WB database is but as a different user, and display some results to a query from it?

(inside a code section of course

Thanks,
Gav

[Stefan]

This is a php question rather than a WB question. Check out php.net or do a google search. You can write any pure php code inside a WB code page. As far as I know, you cannot utilize WB's database functions as you need different settings for a different database.

[gav]

Thanks for the reply Stefan,

I'm really trying to get confirmation from someone that I have found a bug in WB. If anyone could try this and let me know the result I would be grateful.

*Create a new page

*Insert a code section with something like

Code:

$conn = mysql_connect('host', 'user', 'password')
   or die('Could not connect: ' . mysql_error());

mysql_select_db('database') or die('Could not select database');
// This is where we do stuff, but it's not needed to trigger the bug
mysql_close($conn);

*Visit the page to make sure it works.

*Now add a WYSIWYG section after this code section.

*Visit the page again and be suprised to see...

Code:

Warning: mysql_query(): Access denied for user: 'user@host' (Using password: NO) in /Users/gav/Sites/wb/framework/class.database.php on line 135

Warning: mysql_query(): A link to the server could not be established in /Users/gav/Sites/wb/framework/class.database.php on line 135

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /Users/gav/Sites/wb/framework/class.database.php on line 146

I can't figure out why creating a new connection would destroy the WB mysql connection.

This is pretty much a show stopper for me, so any help at all would be great.

Thanks,
Gav.

[bowenmark]

connect to a mysql database running on the same host as the WB database is but as a different user, and display some results to a query from it?

If I'm reading your post right, this should be a default setting from your hosting provider to keep those on virtual hosts from accessing each others' databases and not a bug?

cheers,

      Mark

[gav]

If I'm reading your post right, this should be a default setting from your hosting provider to keep those on virtual hosts from accessing each others' databases and not a bug?

The behaviour is the same on an ISP's machine, and my own machines (tried it on two, just in case). So I wouldn't think this is the case as I have no virtual hosts defined anywhere. They are all just connecting to localhost.

Create a new database on the same mysql server that website baker stores its own database. Try connect to this new database and grab some data from a table there. It works fine. Then put a wysiwyg section on the page after this code section and website baker blows up.

Do you see different behaviour?

Thanks,
Gav

[fienieg]

I know that the same page, may not use 2 users to get content from the same DB..

[gav]

I know that the same page, may not use 2 users to get content from the same DB..

I didn't know this. Is there a reason why WB places this restriction on code sections?

Is there any work around without a lot of hacking WB's code?

Most of the interesting pages on the site I'm making has tables of data. The user I make for reading this data from the www I give read only permissions in mysql.

To put these pages outside of WB would mean %90 of the site is outside WB

Hopefully,
Gav

[gav]

Hacked on class.database.php for an hour, and found the problems, no help needed now

Thanks,
Gav

[opus345]

Hacked on class.database.php for an hour, and found the problems, no help needed now

Thanks,
Gav

Would you like to share?

Opus

[RHase]

I think that is no "problem", no bug and it should not be "solved" within the class.database.php.

If you do not define or use a database handler, PHP (mysql_query, mysql_fetch...) will use the first database connection.
Additional, with mysql_select_db, you re-define the first database connection!!

So if you want to use several database connections, you should work with db-handler (additional, it is a better style):
// $database == default dbh of wb
$wb_sql_results = $database->query(....
// my other db connection
$my_sql_results = mysql_db_query("...", "...", $my_database_handler);

Or, if you want to make your code difficult (crude), you can
// $database is the first dbh (wb connection)
mysql_select_db(... // re-define the first DB connection to your dbh, now $database will be the second db-connection
mysql_query("SELECT ...); // this use the first database connection
mysql_select_db(... // re-define the first DB connection to ...

[gav]

Just to complete this thread

RHase wrote
    "think that is no "problem", no bug and it should not be "solved" within the class.database.php."

You can "think" whatever you like ofcourse, but the code at the time was awful. It gave out an OO interface but didn't act like one. It was purely procedural php wrapped inside a class definition. You could instantiate one, and one only. So, it was broken. It didn't save a database handle inside the 'php object'. It was broken. I thought it suffered a bit of the phpbb syndrome (looks nice, so people think the code is nice).

It was worse though, the initialize method didn't just initialize the object, it made the connection to the (mysql) database and *loads* of other things.

It was just awful code.

It was never trying to be a "framework", so should have stayed out of the way there too.

PHP isn't perfect, but that was awful.

I think the coders were just refactoring the code to be ready for PHP5? So they *could* be more OO.

I solved it by rewriting that class so the objects stored the db handle. Simple. It worked, and still works wonderfully for them. But that change propagated up the code, and it worked for the site I was doing, but the changes I had to make to use postgres meant I rewrote the database stuff altogether. 

I decided I may as well write a cms for the niche market I was targetting myself. It works well (and I didn't choose php).

Website baker was the closest thing to what I wanted. For a general purpose CMS, I know of nothing better. KISS.

So the "problem" *was* a bug. It was broken and was "solved" in "class.database.php".

Gav
(a year later)