Using two databases

bprof

Hi,

I'm trying to connect to two databases(users, info) in one script to use the result from a query on one database, as an input for another query on the second database...Is this possible?

Here is the script that I have included in the actual script:

<?php

  $dbHostname = "localhost";
  $dbUsername = "test";
  $dbPassword = "test123";
  $dbName     = "users";      

  $dblink = MYSQL_CONNECT($dbHostname, $dbUsername, $dbPassword) OR DIE("Error !! Unable to connect to database");       

  // Select database or print error message if unsuccessful */
  mysql_select_db($dbName) or die( "Unable to select database ".$dbName); 


  $dbhostname2= "192.168.1.1"; //different machine
  $dbusername2 = "test2";
  $dbpassword2 = "test123";
  $dbname2 = "info";      

  $dblinkbugs = MYSQL_CONNECT($dbhostname2, $dbusername2, $dbpassword2) OR DIE("Error !! Unable to connect to database");       

  mysql_select_db($dbname2) or die( "Unable to select database ".$dbname2); 

?>

And here is the running script

<?php

  include_once("include/db_connection.php");

  $query = "SELECT * FROM userdata where contact1email LIKE '%yahoo%' order by username";
  $result=mysql_query($query) or die('Query failed: ' . mysql_error());

  $num=mysql_numrows($result);

  $i=0;

  echo $num;

  while ($i<$num) {

  $username=mysql_result($result,$i,"username");
  $firstname=mysql_result($result,$i,"fname");
  $lastname=mysql_result($result,$i,"lname");
  $contactn=mysql_result($result,$i,"contact1name");
  $userid=mysql_result($result,$i,"uid");

  $query2 = "select book from booksinfo where uid='$userid'";
  $result2= mysql_query($query) or die('Query failed: ' . mysql_error());

  $bnum=mysql_numrows($result2);

  $bookname=mysql_result($result2,$bnum,"book");      

  $i++;
   }

?>

When I ran the script I got this error message?

Query failed: Table 'info.userdata' doesn't exist

I know that it is using the last added database(which in this case info), and because info does not have userdata I got this error.

But is there are away that I can make it work?

Thank you very much for your help.

etully

Notice in your line where you perform the query, you are saying:

$result=mysql_query($query)

When you perform that, you aren't giving the command enough information to tell it which database it should be using. Hypothetically, you could have a table called "products" in both databases so when you say, select * from products, the query function wouldn't know which database it should be using.

According to this page, there is an optional argument you can pass in telling it which resource (database) you want to connect to.
http://us3.php.net/manual/en/function.mysql-query.php

Your resource handles are called $dblink and $dblinkbugs so you should be specifying them when you perform the query.

bprof

Thanks a lot for your help.

I tried using the handles, and I was able to get the first query results, and also the number of rows from the second query.

But with this warning:

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 1 on MySQL result index 6 in C:\www\dbuser\index.php on line 45

And this is line 45

$bookname=mysql_result($result2,$bnum,"book");

 

  $query = "SELECT * FROM userdata where contact1email LIKE '%yahoo%' order by username"; 
  $result=mysql_query($query, $dblink) or die('Query failed: ' . mysql_error());

  $num=mysql_numrows($result);

 $query2 = "select book from booksinfo where uid='$userid'"; 
  $result2= mysql_query($query2, $dblinkbugs) or die('Query failed: ' . mysql_error());

  $bnum=mysql_numrows($result2);

  echo $bnum;

  $sv=mysql_result($result2,$bnum,"book");

Could you help me in that please?

Thank you,

bpat1434

That is true. Also to point out that the second database won't ever connect because those variables ($dbcamel, $dbubugs, $dbpbugs) don't exist. Could be a typo, or it is an error.

bprof

Yes it was miss typo, I've just corrected it, but I still have the same error.

Thanks for pointing to the error.

Weedpacket

$bnum=mysql_numrows($result2);

  echo $bnum;

  $sv=mysql_result($result2,$bnum,"bug_severity");

Result set rows, like arrays, are indexed starting from 0; so if there are $bnum rows in the set, the last one will be number $bnum-1.

bprof

The second query should always give 1 result, $bnum should always be 1. So now it is set to -1, is that what you are saying? Sorry I didn't quite understand.

bpat1434

Well, there's an issue with your mysql_result() call. There is no column selected named "bug_severity"

bprof

bpat1434 wrote:
Well, there's an issue with your mysql_result() call. There is no column selected named "bug_severity"

You are right, I fixed it, and I've updated my post, to include the correct name(book).

But still I have the same problem.

bpat1434

Well Weedpacket told you what was wrong. The result doesn't start with row 1. If you're returning only one row, it starts with row 0. So $bnum-1 is what you want to use, not $bnum.

bprof

I tried

$bnum=$bnum-1;

And I got the results that I'm looking for.

Thanks a lot for it.

I'm still getting this warning:

Warning: mysql_result() [function.mysql-result]: Unable to jump to row -1 on MySQL result index

Weedpacket

The first row of the result set is row number 0. If $bnum is the number of rows in the result set (as returned by mysql_numrows()), then the last row in the result set is $bnum-1.

So if there are 50 rows in the result set, then $bnum==50, and the last row is row number $bnum-1, that is, 50-1, or 49.

And if there is 1 row in the result set, then $bnum==1, and the last row is row number 1-1, or 0.

Exactly like arrays.

bprof

Thanks a lot Weedpacket, for the explanation.

Is there a way, that I can use, to remove the warning that comes with the results?

I have the results that I'm looking for, but with this warning

Warning: mysql_result() [function.mysql-result]: Unable to jump to row -1 on MySQL result index

Sometimes tens of even few hundreds in each results page.

etully

The reason that you are getting "unable to jump to row -1 on MySQL" is that you are trying to read row -1 which doesn't make sense. It never exists.

Here are two correct ways to read all the rows that get returned from a MySQL query:

$query = "select blah blah blah";
$result = MYSQL_QUERY($query); 
while (list($name,$address,$city,$state,$zip) = @mysql_fetch_row($result)) {
print "$name, $address, $city, $state, $zip";
}

$query = "select blah blah blah";
$result = MYSQL_QUERY($query); 
$number=mysql_numrows($result);
$i=0;
while ($i < $number) {
$name=mysql_result($result,$i,"name");
$address=mysql_result($result,$i,"address");
$city=mysql_result($result,$i,"city");
$state=mysql_result($result,$i,"state");
$zip=mysql_result($result,$i,"zip");
print "$name, $address, $city, $satte, $zip";
$i++;
}

bpat1434

And a third way:

 $query = "select blah blah blah";
$result = MYSQL_QUERY($query);
$number=mysql_numrows($result);

for($i=0; $i<$number; $i++) {
  $ary = mysql_fetch_row($result);
  echo implode(' ', $ary);
}

And a fourth:

 $query = "select blah blah blah";
$result = MYSQL_QUERY($query);

while($row = mysql_fetch_array($result)) {
  echo implode(' ', $row);
}

The point is that the itteration needs to start at 0 (no less than that) and continue until it is equal to 1 less than $number ($number-1, or $number-- or --$number) OR continue as long as $i is less than but not equal to $number ($i<$number).

bprof

Thanks a lot everyone I highly appreciate your help.

This is the code that I'm using which gave me the results that I need, besides the warning messages:

<?php include_once("include/db_connection.php");?>

<table border="1" cellspacing="0" bordercolordark="#008000">
 <tr bgcolor="#CCFFCC">
    <td width="10%" align=center><b>v1</b></td>
    <td width="10%" align=center><b>v2</b></td>
    <td width="10%" align=center><b>v3</b></td>
    <td width="5%" align=center><b>v4</b></td>
    <td width="10%" align=center><b>v5</b></td>
    <td width="10%" align=center><b>v6</b></td>
    <td width="10%" align=center><b>v7</b></td>
  </tr>

<?php



  $query = "SELECT blah blah blah";

  $result=mysql_query($query.";", $dblink) or die('Query failed: ' . mysql_error());

  $num=mysql_numrows($result);

  $i=0;
  $bnum=0;
  echo $num."<br>";

  while ($i<$num) {

  $v1=mysql_result($result,$i,"dv1");
  $v2=mysql_result($result,$i,"dv2");
  $v3=mysql_result($result,$i,"dv3");
  $v4=mysql_result($result,$i,"dv4");
  $v5=mysql_result($result,$i,"dv5");
  $v6=mysql_result($result,$i,"dv6");

  $query2 = "select blah blah blah='$v6'";
  $result2= mysql_query($query2, $dblinkbugs) or die('Query failed: ' . mysql_error());

  $bnum=mysql_numrows($result2);

  $bnum=$bnum-1;

  $v7=mysql_result($result2,$bnum,"dv#");

  echo "<TR>\n";
  echo "<TD WIDTH=\"20%\" ALIGN=\"CENTER\">$v1</TD>\n";
  echo "<TD WIDTH=\"20%\" ALIGN=\"CENTER\">$v2</TD>\n";
  echo "<TD WIDTH=\"20%\" ALIGN=\"CENTER\">$v3</TD>\n";
  echo "<TD WIDTH=\"20%\" ALIGN=\"CENTER\">$v4</TD>\n";
  echo "<TD WIDTH=\"20%\" ALIGN=\"CENTER\">$v5</TD>\n";
  echo "<TD WIDTH=\"20%\" ALIGN=\"CENTER\">$v6</TD>\n";
  echo "<TD WIDTH=\"20%\" ALIGN=\"CENTER\">$v7</TD>\n";
  echo "</tr>"; 


  $i++;
   }

?>

</table>;

If I used anything other than this:

$bnum=$bnum-1;

I will get column 7 empty, which is not what I want.

The code as I said is working, but before it starts to print the results in the table, it gives me a huge list of warnings.

I tried different ways to avoid read row -1. The only one that worked the way I want, but with warnings is this:

$bnum=$bnum-1;

If I ran the script without the second query it will work perfect, without warnings. I get the warnings only when I try to get the data for column seven which comes from the second query.

Thanks for all your help.

Weedpacket

  $v7=mysql_result($result2,$bnum,"dv#");

Rows are numbered starting from 0. If there is only one row, it's row number 0, so why not just ask for row number 0?

  $v7=mysql_result($result2,0,"dv#");

Incidentally, running a query inside a loop driven by another query is often an inefficient way of doing something that could be done by the query itself. Using the original code as an example:

Select
	u.username, u.fname, u.lname, u.contact1name, u.uid, b.book
from
	userdata u, booksinfo b
where
	u.contact1email like '%yahoo%'
	and
	b.uid=u.uid
order by
	u.username;

bprof

Thanks a lot Weedpacket, for all your great help.

I tried using 0 instead of $bnum, but I still keep getting the same warning.

Unable to jump to row 0 on MySQL result index.

But this time with row 0 instead of -1, 0, or sometimes 1.

I tried many ways to get ride of this warning but they didn't work.

I'm sorry, I wasn't clear; I ran both queries this way, because one query comes from one database, and the other query comes from different database.

etully

Show us your code.

Are you using one of the four techniques bpat1434 or I gave you for reading data from a SQL query?

bprof

Thank you etully. Here is the code that I'm using now:

<?php include_once("include/db_connection.php");?>

<table border="1" cellspacing="0" bordercolordark="#008000">
<tr bgcolor="#CCFFCC">
    <td width="10%" align=center><b>Username</b></td>
    <td width="10%" align=center><b>First Name</b></td>
    <td width="10%" align=center><b>Last Name</b></td>
    <td width="5%" align=center><b>Contact</b></td>
    <td width="10%" align=center><b>User ID</b></td>
    <td width="10%" align=center><b>Book Name</b></td>
  </tr>

<?php



 $query = "SELECT * FROM userdata where contact1email LIKE '%yahoo%' order by username"; 

  $result=mysql_query($query.";", $dblink) or die('Query failed: ' . mysql_error());

  $num=mysql_numrows($result);

  $i=0;
  $bnum=0;
  echo $num."<br>";

  while ($i<$num) {

  $username=mysql_result($result,$i,"username");
  $firstname=mysql_result($result,$i,"fname");
  $lastname=mysql_result($result,$i,"lname");
  $contactn=mysql_result($result,$i,"contact1name");
  $userid=mysql_result($result,$i,"uid"); 

  $query2 = "select book from booksinfo where uid='$userid'"; 
  $result2= mysql_query($query2, $dblinkbugs) or die('Query failed: ' . mysql_error());

  $bnum=mysql_numrows($result2);

  $bookname=mysql_result($result2,0,"book"); 

  echo "<TR>\n";
  echo "<TD WIDTH=\"20%\" ALIGN=\"CENTER\">$username</TD>\n";
  echo "<TD WIDTH=\"20%\" ALIGN=\"CENTER\">$firstname</TD>\n";
  echo "<TD WIDTH=\"20%\" ALIGN=\"CENTER\">$lastname</TD>\n";
  echo "<TD WIDTH=\"20%\" ALIGN=\"CENTER\">$contactn</TD>\n";
  echo "<TD WIDTH=\"20%\" ALIGN=\"CENTER\">$userid</TD>\n";
  echo "<TD WIDTH=\"20%\" ALIGN=\"CENTER\">$bookname</TD>\n";
  echo "</tr>";


  $i++;
   }

?>

</table>

And here is the included script(db_connection.php)

<?php

  $dbHostname = "localhost";
  $dbUsername = "test";
  $dbPassword = "test123";
  $dbName     = "users";      

  $dblink = MYSQL_CONNECT($dbHostname, $dbUsername, $dbPassword) OR DIE("Error !! Unable to connect to database");       

  // Select database or print error message if unsuccessful */
  mysql_select_db($dbName) or die( "Unable to select database ".$dbName);


  $dbhostname2= "192.168.1.1"; //different machine
  $dbusername2 = "test2";
  $dbpassword2 = "test123";
  $dbname2 = "info";      

  $dblinkbugs = MYSQL_CONNECT($dbhostname2, $dbusername2, $dbpassword2) OR DIE("Error !! Unable to connect to database");       

  mysql_select_db($dbname2) or die( "Unable to select database ".$dbname2);

?>