Script fails when different database is used for lookup inside a while loop

[MockY]

Throughout the application I'm building, I include a db.php file which has the information about the local database so that I can use it wherever I do a database call. However, I am now using a second database located on a remote server in order to fetch additional information not stored on the local database, inside a while loop. This fail miserably and I have tested many things and need a bit of guidance.

 

Code simplified:

function grab_expiration_date ($number) {

    $remote_link = mysql_connect('remote_host', 'user', 'pass');
    $remote_connect = mysql_select_db('remote_db', $remote_link);
    
    $query = "SELECT ExpirationDate FROM remote_table WHERE Number='$number'";
    $result = mysql_query($query, $remote_link);
    $num_rows = mysql_num_rows($result);
    
    if ($num_rows == 1) {
        
        $r = mysql_fetch_assoc($result);
        $expire_date = $r['ExpirationDate'];
        
        mysql_close($remote_link);
        
        return $expire_date;
    } else {
        
        return false;
    }

}


$query = "SELECT Number FROM local_table;
$result = mysql_query($query, $local_link);

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

      $number = $r['Number'];
      $expire_date = fetch_expiration_date($number);

      if ($expire_date) {

          $query2 = UPDATE local_table SET ExpirationDate='$expire_date' WHERE Number='$number'";
          $result2 = mysql_query($query2, $link);

          if (!$result2) {
               echo 'Could not update the Expiration Number because: ' . mysql_error();
               exit();
          }

      } else {
          echo 'Nothing found, or dublicates';
      }
}

 

The attempt of update the local table fails, and mysql_error() does not even return anything, stating what's wrong. In the past, I fetched this information from a website using CURL, but since I now have access to the database, hitting the database is by far faster. Any help would be much appreciated.

[mikesta707]

I dont see anywhere in which you create $local_link. Perhaps you are fogetting the mysql_connect/select_db lines for the local table.

[MockY]

I have it in the db.php file which I include first on all pages throughout the application. It looks the same as the connection to the remote site

$host = "localhost";
$username = "user";
$password = "password";
$database = "local_db";

$local_link = mysql_connect($host, $username, $password);
if (!$local_link) {
    die('Could not connect: ' . mysql_error());
}

$connect = mysql_select_db($database, $local_link);

 

Without it working correctly, the very first query would fail:

$query = "SELECT Number FROM local_table;

 

The issue comes after that the remote site has been called and result is returned, when it's time to go back to the local database for the UPDATE query.

[mikesta707]

Ahh I see. The problem is that you are inside a function, and even though you include the db.php script, the $local_link variable is not in the scope of the function. I suggest simply passing the local_link into the function, but you could also use the global keyword (though I highly advise against it.)

 

EDIT: sorry the above is incorrect, I must have misread your code. As PF said, you are referencing $link. Perhaps it should be $local_link (or vice versa)

[PFMaBiSmAd]

Your code also contains a reference to a $link variable. Does that exist?

 

You should be developing and debugging your code with error_reporting set to E_ALL (or to a -1) and display_errors set to ON so that php will help you by reporting and displaying all the errors it detects. Add the following two lines of code immediately after your first opening <?php tag on the page -

ini_set("display_errors", "1");
error_reporting(-1);

 

 

[MockY]

Your code also contains a reference to a $link variable. Does that exist?

 

I rewrote the code to simplify things so it would be easier to see. $link was overlooked and should be $local_link since the UPDATE query is attempting to update the local database.

When enabling error reporting, the following error occurs:

Warning: mysql_query(): 3 is not a valid MySQL-Link resource

But it sure is since I use it in the initial query. Once the remote query is done and closed, the reference dissapears even though it was never closed.

 

In short:

I SELECT a range of records from a local database.

I loop through the results, and for each record I connect to a remote database to fetch one piece of information based on the number I received from the first local query.

Once  that piece of information is fetched, I UPDATE the local database with this fetched piece of information

 

The first local query works just fine. So does the query that fetches information from the remote database. The issue occurs when I try to update the local database right after fetching the info from the remote database, even though I specifically close that connection once I retrieve the info.

[PFMaBiSmAd]

If you post the actual code that fails and corresponds to the error message, someone can help with the problem.

[MockY]

Apparently I had to add

$local_link = mysql_connect('localhost', 'username', 'password');
$connect = mysql_select_db($local_database, $local_link)

right before the UPDATE query, which I seem odd since $local_link is defined in the db.php file.

 

So, if I connect to another database, I must then re-establish the connection to the local database again?

I was under the impression that once I close the remote connection, the local connection would take over again. It seems like you can only be connected to one database at a time, and that you have to reconnect to it each time another connection was made, regardless whether you established that connection earlier.

 

In other words, $local_link will no longer be defined as soon as I define $remote_link. Hence, I can't use $local_link once I close remote_links and must therefore define it again.

[PFMaBiSmAd]

So, if I connect to another database, I must then re-establish the connection to the local database again?

 

No, connections stay open unless your code does something to the connection or something to the variable holding the link resource.

 

Also, in your grab_expiration_date() function, by making a new connection each time the function is called and closing that connection, you are wasting a lot of time reestablishing the remote connection each time the function is called. To get around this you can either -

 

A) Make the remote connection in your main code and pass it into the grab_expiration_date() function as a call time parameter or

 

B) Use a static variable inside the function to hold the connection link and add logic to reuse that connection instead of calling mysql_connect() each time the function code runs. You would also need to remove the mysql_close() statement inside the grab_expiration_date() function.