SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,761
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Connect to Different Database Depending on MySQL Syntax

    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:

    PHP Code:
    $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
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.

  2. #2
    SitePoint Member
    Join Date
    Oct 2011
    Location
    California
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,
    Hope the following code help you.

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

    Thnx

  3. #3
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,761
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.

  4. #4
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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'?

  5. #5
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,761
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    PHP Code:
    $selectt mysql_query("SELECT..."$dbh);

    $updatet mysql_query("UPDATE table....",$dbh); 
    Am I F'd or what?

    Cheers
    Ryan
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.

  6. #6
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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.

  7. #7
    SitePoint Wizard
    Join Date
    Oct 2004
    Location
    Newport Beach
    Posts
    1,761
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Hey Cups

    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
    Upcoming Movies - Movie News. Updated Daily.
    Movie Trailers - Awesome trailer site. Nuff said.

  8. #8
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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 Code:
    <?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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •