Connect to Different Database Depending on MySQL Syntax

[casbboy]

On a typical page I will have a number of queries that all refer to the same mysql resource...

Both an Update and a Select at least. I'm going to use two remote MySQL servers now, one for everything other than Select (a master DB to write to), and the other for Select queries only.

Here is my typicaly mysql_connect script resource referred to by all my MySQL queries:

$dbh=mysql_connect ("server1_address", "user", "password") or 
die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("db1"); 

Instead of changing the queries to a new connection resource other than $dbh, is there some way I can have my connection script recognize the mysql syntax being called, and pick the proper mysql server based on that? So, if the query is an UPDATE, I'd want it to connect to server2_address/db2 instead of shown above.

At all possible? I have about 100 static pages that run my site, and each has up to 10 mysql queries. I'm trying to avoid having to change every query that is not using SELECT syntax to use secondary db connection resource.

Any feedback appreciated.

Cheers
Ryan

[JbmediaForce]

Hi,
Hope the following code help you.

$dbh = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL");

Thnx

[casbboy]

Thanks. I understand that. But can the resource recognize the syntax and know which variables to throw in there depending on whether query is either a Select or Update?

Cheers
Ryan

[Cups]

Is that db connection script -- or any of the ~1000 queries you seemingly have -- kept in include files, or are they all hand-written into each 'page'?

[casbboy]

Yeah, they are in the page itself, with the connection script as the only include (sample code was shown in first post).

And then on the page I have

$selectt = mysql_query("SELECT...", $dbh);

$updatet = mysql_query("UPDATE table....",$dbh); 

Am I F'd or what?

Cheers
Ryan

[Cups]

Looks to me like you'd need to do a multi-file search and replace.

When line contains SELECT and $dbh replace $dbh with $dbh1 or similar.

Tools exist to do this kind of work on all OSs.

Then add $dbh1 to your include file.

[casbboy]

Yeah, that's sort of the conclusion I came up with also. Though I want do an automatic replace, since each query is somewhat different.

I'll just have to find, edit manually, and find again. I totally forgot about the ability to do multi-file search through Dreamweaver, so that should help things greatly.

Cheers
Ryan

[Cups]

Yours is a good example of why people go to the trouble of removing the absolute dependency on sql statements as far as possible from their scripts, much like people tend to try and use Templates to separate some of the hard core logic from display.

This is why you may find code similar to this elsewhere:

<?php
include 'db.php';

$what = array('name','age'); // 
$from = "PETS"; 
$where = "dob > '2005-12-31';  // string, but could be an array

$pets_starting_p = $db->select($what, $from, $where);

This is totally imaginary, but you can imagine you could tinker with $db and in that central place, set which database to use, which server and so on.

This basic premise can be taken to far greater degrees, take a look at ORM for example.

Anyhow, that huge rewrite may not be an option for you, but I mention it because you might bear it in mind for your next project. Think about centralising as much code as you can in an effort to apply the principles of DRY (Don't Repeat Yourself). If you do go that way I'd urge you to look at using PDP (or mysqli) and their prepared statements.

Many years back I used a multiple search/replace win32 tool whose name eludes me, but I thought it was called "ultimate search and replace", *nix tools abound for this kind of work. A lot may depend on whether you can say with absolute certainty that all of your SELECT statements are actually on one single line