Page 1 of 1

Question on Accessing Multiple MySQL Databases on 1 page

Posted: Thu Nov 04, 2010 1:37 pm
by yy4u2rel82
Hi guys.. probably easy for the more advanced guys here, but my site utilizes 3 different mysql databases. I'm pulling the top 10 results to the the front page from 1 database. What I want to do is pull the top 10 results from each database, based on the day of the week.

I have 2 questions:

1) I currently access my front page database (for top 10 results) with this code:

Code: Select all

<?php 
$dbname="mydb1";
$host="localhost";
$user="myusername";
$pass="mypw";

$link = mysql_connect($hostname, $user, $pass);
mysql_select_db($dbname, $link);
?>
If I add 3 more databases to this config.php file, is the best way to create a completely new section for each one.. such as this.. (notice mydb1, mydb2 and mydb3):

Code: Select all

<?php 
$dbname="mydb1";
$host="localhost";
$user="myusername";
$pass="mypw";

$link = mysql_connect($hostname, $user, $pass);
mysql_select_db($dbname, $link);
?>

<?php 
$dbname="mydb2";
$host="localhost";
$user="myusername";
$pass="mypw";

$link = mysql_connect($hostname, $user, $pass);
mysql_select_db($dbname, $link);
?>

<?php 
$dbname="mydb3";
$host="localhost";
$user="myusername";
$pass="mypw";

$link = mysql_connect($hostname, $user, $pass);
mysql_select_db($dbname, $link);
?>
or.. is the link just needed once.. such as:

Code: Select all

<?php 
$dbname="mydb1";
$host="localhost";
$user="myusername";
$pass="mypw";

<?php 
$dbname="mydb2";
$host="localhost";
$user="myusername";
$pass="mypw";

<?php 
$dbname="mydb3";
$host="localhost";
$user="myusername";
$pass="mypw";

$link = mysql_connect($hostname, $user, $pass);
mysql_select_db($dbname, $link);
?>
....or does it even matter?

Question 2:
Could someone one whip up a quick php script that says...
If Monday, use mydb1;
If Tuesday, use mydb2;
If Wednesday, use mydb3;
// Obviously, I could add the rest of the days if I could get this.

This is how I currently pull the top 10 to the front page:

Code: Select all

<?php
$sql_statement = 'SELECT title, description FROM mytable ORDER BY date DESC LIMIT 0 , 10';
$result = mysql_query($sql_statement, $link);

while ($curr_row = mysql_fetch_assoc($result)) {

$title = $curr_row['title'];
$title = str_replace("-", " ", $title);
$title = strtoupper($title);

$description = $curr_row['description'];
$description = substr($description,0,300);
    
echo '<font size="2" face="sans-serif"><b><h5>' . $title . '</h5></b>' . '&nbsp;&nbsp;' . $description . '...</font>';
}
mysql_free_result($result);	
?>
Please let me know if you have any suggestions, help, tips, great advice or helpful information. :) You're help is greatly appreciated! Thanks!
Jeff

Re: Question on Accessing Multiple MySQL Databases on 1 page

Posted: Thu Nov 04, 2010 3:43 pm
by pickle
I'd suggest making one connection (one $link) then, when necessary, call mysql_select_db() to change to the appropriate database. I assume each database is identically structured, so you only need to change the database you're connected to, not any of the queries.

As for your second question, you could write a quick switch() statement based on date('N'), or date('w') & choose your database accordingly.

Re: Question on Accessing Multiple MySQL Databases on 1 page

Posted: Thu Nov 04, 2010 3:57 pm
by yy4u2rel82
Thanks, pickle! Just what I needed. You assumed right with the structure question. The query will be the same each time (good question).

And the switch() function looks to be just what I need. I can probably make it work from here... just don't write enough php to be familiar with the functions.
Thanks a bunch! Have a great day!

Jeff