Access Other MySQL Database from within WordPress WordPress
- SOLVED
I have a second database that I need to access from within WordPress.
Even a simple attempt to access this database fails completely. I know I can access the database with the same code from outside of WordPress so I am assuming that WordPress is stopping my access of this external MySQL Database.
The quick code I put together for testing is as follows (sorry about crappy code - not really my calling or skillset);-
mysql_connect("localhost", "db_user", "db_password") or die(mysql_error());
mysql_select_db("db_name") or die(mysql_error());
$result = mysql_query("SELECT * FROM db_table WHERE region like '%$region%'") or die(mysql_error());
$num_results = mysql_num_rows($result);
if ($num_results == 0) {
echo '<br />';
echo '<p>There were no matches in the database for your search criteria';
echo '<br />';
}
while($row = mysql_fetch_array($result)){
echo '<br />'.$row['result1'];
echo '<br />'.$row['result2'];
echo '<br />'.$row['result3'];
echo '<br />'.$row['result4'];
echo '<hr />';
}
Can anyone advise how I can help me modify the above code so I can access the second MySQL database from within WordPress preferably via modification of one of my page templates.
thanks in advance for any help you can offer.
Answers (3)
Andrzej answers:
Hey,
Are you trying to connect to the localhost or some external server database?
Here's the code that worked for me with both connecting to localhost and remote database.
$db1 = mysql_connect( 'localhost', 'db_username', 'db_password' ) or die( mysql_error() );
$sel1 = mysql_select_db( 'db_name' ) or die( mysql_error() );
$query = "SELECT something FROM some_table";
$res1 = mysql_query( $query, $db1 );
while ( $array = mysql_fetch_array( $res1 ) ){
print_r( $array );
}
I just putted it into my page.php template.
Please see if this works for you?
Kirk O'Connor comments:
That's nearly there but it just displays the entire array and I need to be able to select and print selected rows from the array.
Before I was using this code to access the specific rows in the array I needed to display;-
while($row = mysql_fetch_array($result)){
echo '<br />'.$row['result1'];
echo '<br />'.$row['result2'];
echo '<br />'.$row['result3'];
echo '<br />'.$row['result4'];
echo '<hr />';
}
It no longer works. How would I do this now with your code ?
Sorry for this but I am not a programmer.
Monster Coder answers:
Is the second database in the same server where your wordpress is installed? if not, you must allow this IP (where wordpress is installed) to the mysql server.
suppose, ur wordpress is installed in 192.168.1.1 and your mysql server is installed in 192.168.1.5 then you need to allow 192.168.1.1 in your mysql server. you may write the following statement in mysql console:
> grant all privileges on `db_name`.* TO 'db_user'@'192.168.1.1 ' IDENTIFIED BY 'db_pass';
it is out of wordpress's capabilities to restrict any connection to your mysql server as long as your credentials are correct.
thanks
Monster Coder comments:
Also, if should use connection handle resources to identify the connection that is shown in the code by <strong>Andrzej Zglobica. </strong>It will help avoid confusions and mistakes.
Oleg Butuzov answers:
if your user have same credentiols for assecign db its a simple.
1)
//second db name
define('ONEMOREDB', 'clients_tartak_tartak_ua');
function queryresults() {
global $wpdb;
// no `` in db query for db table name
$result = $wpdb->get_row("SELECT * FROM ".ONEMOREDB.".tartak_news as news LIMIT 1");
var_dump($result);
exit;
}
add_action('wp_head', 'queryresults');
2) who cares about ``
define('ONEMOREDB', 'clients_tartak_tartak_ua');
add_filter('query', 'sql_filter');
function sql_filter($sql){
if (strpos($sql, ONEMOREDB.".") !== false){
if (preg_match_all('/`'.ONEMOREDB.'\.([a-z_0-9]{1,})`/si', $sql, $m)){
foreach($m[0] as $match){
$sql = str_replace($match, trim($match, "\"`"), $sql);
}
}
}
return $sql;
}
function sql_results_test() {
global $wpdb;
$result = $wpdb->get_row("SELECT * FROM `".ONEMOREDB.".tartak_news` as news LIMIT 1");
var_dump($result);
exit;
}
add_action('wp_head', 'sql_results_test');
3) 2 simple ways to amke it also workable with $wpdb->update and $wpdb->insert
open <em><strong>wp-includes/wp-db.php</strong></em>
find <strong>function prepare($query = null)</strong>
now find <strong>return @vsprintf($query, $args);</strong>
replace its by <strong>return apply_filters('query', @vsprintf($query, $args));</strong>
i am assuming you have add filter for sql abouw to this code.
So as you can see its prety usefull and simple. witch same credentials to make simple selects!
cheers.
a sample plugin for you
http://www.mediafire.com/?jtmzqjzjcnt