Web Hosting Talk : Web Hosting Main Forums : Programming Discussion : Selecting Two Databases At Same Time In Loop

[kgiger]

What I am trying to do is grab a users city and state from one database and then grab the zip code from another database and then update the first databases record so that zip code is in that db also.

here is what I got but I can figure out how to make the second command to connect to second db.

PHP Code:

 $result = mysql_query("SELECT * FROM profiles") 
 or die(mysql_error());  


 while($row = mysql_fetch_array( $result )) {


    $xid = $row['id'];
    $xcity = $row['city'];
    $xstate = $row['state'];
 

} 


[webexperts]

You can select two databases in a loop ,
but u have to use link identifier, that will be returned when connecting to a database ....

[Christian Little]

Here's what you need to do. This code won't work obviously, but it's the rough idea of how to make it work.

Basically when you have multiple DB connections established you just pass in an optional mysql link resource to the mysql_query command, which tells php which database connection to run that specific query on:

PHP Code:

// connect to the 1st database that you are trying to update
$linkCities = mysql_connect(blah blah blah);
mysql_select_db("db_name", $linkCities) || die ("Could not select database: " . mysql_error());

// connect to the 2nd database that has the zipcodes
$linkZips = mysql_connect(blah blah blah);
mysql_select_db("db_name", $linkZips) || die ("Could not select database: " . mysql_error());

$city = "Seattle";
$state = "WA";


// Pull the Zip code from the 2nd database
$query = "SELECT * FROM table_name WHERE state=\"$state\" and city=\"$city\"";
$result = mysql_query($query, $linkZips);
while($row = mysql_fetch_assoc($result)) {

  // Update the 1st database with the zipcode
  $update_query = "UPDATE table_name SET zip = \"" . $row["zip"] . \"" WHERE city = \"$city\" and state = \"$state\"";
  $update_result = mysql_query($update_query, $linkCities);
}

mysql_close($linkCities);
mysql_close($linkZips); 


You can see more examples and the details of the mysql_query function here: http://php.net/manual/en/function.mysql-query.php

[kgiger]

Ok thanks that worked great!!

[InspiRunner]

Just for fun - MySQL allows you to execute queries that pull data from two databases at once. This would make sense if tables can be joined.

PHP Code:

  $mysqlusr = "root";
  $mysqlpass = "";
  $dbname = "database1";
  $idlink = mysql_connect('localhost', $mysqlusr, $mysqlpass)
  or die("Could not connect: " . mysql_error());
  mysql_select_db($dbname);

  $qry = "select * from database1.table1 a inner join database2.table2 b on a.id=b.id";
  $res = mysql_query($qry);

  while($rs = mysql_fetch_array($res))
  {
    // run update query here
  } 


[synrg]

Originally Posted by InspiRunner

Just for fun - MySQL allows you to execute queries that pull data from two databases at once. This would make sense if tables can be joined.

PHP Code:

  $mysqlusr = "root";
  $mysqlpass = "";
  $dbname = "database1";
  $idlink = mysql_connect('localhost', $mysqlusr, $mysqlpass)
  or die("Could not connect: " . mysql_error());
  mysql_select_db($dbname);

  $qry = "select * from database1.table1 a inner join database2.table2 b on a.id=b.id";
  $res = mysql_query($qry);

  while($rs = mysql_fetch_array($res))
  {
    // run update query here
  } 


That would have been my solution also.