php and mysql help, working with multiple database

Question

I need to work on multiple database, and below is my current code. Following code seems to be very very slow and having issues with multiple connection while I view the mysql connection list.

So can anyone let me what's wrong with the code and help me to correct the issues and faster performance.


$dbHost="localhost";
$dbUser="user";
$dbPass="pass";
$db_1="database1";
$db_2="database2";

$connect=mysql_connect($dbHost, $dbUser, $dbPass);
if (!$connect)
die("Error Connecting to MYSQL");
mysql_select_db($db_1, $connect)
or die("Error Connecting to Database 1");

$remainingDataCount="25000";

$getData=mysql_query("select * from TABLE where FIELD1='data' && FIELD2>'date' && FIELD3='NO'");
$loop=0;
while ($eachData=mysql_fetch_array($getData)) {
    $loop++;
    $UNIQUE_ID=$eachData['id'];
    $UNIQUE_DATA1=$eachData['FIELD4'];
    $DATA2=$eachData['FIELD5'];

    $responseMessage="some text here";
    $currentTimeStamp=date('Y-m-d H-i-s');

    if ($loop>$remainingDataCount) {
        break;
    } else {

        $insertQuery=mysql_query("insert into ".$db_2.".TABLE values('', '$UNIQUE_DATA1', '$DATA2', '$responseMessage', '$currentTimeStamp')");

        if ($insertQuery==true) {

            $updateQuery=mysql_query("update ".$db_1.".TABLE set FIELD1='YES', FIELD2='$responseMessage', FIELD3='$currentTimeStamp' where FIELD4='$UNIQUE_DATA1'");
            if ($updateQuery==false) {
                mysql_query("delete from ".$db_2.".TABLE where FIELD1='$UNIQUE_DATA1'");
            }

        }
    }

}

Joonas Trussmann · Accepted Answer · 2010-07-02 13:46:51Z

2

Open up two connections with mysql_connect(), select the correct database on both and then use the optional link identifier parameter with mysql_query() to choose which database to execute your queries on.

mysql_query ( string $query [, resource $link_identifier ] )

Edit: Also, you might want to have a look at Mysqli (ee.php.net/manual/en/book.mysqli.php) so you can use prepared statements which would probably speed up the looped insert dramatically.

edited Jul 2, 2010 at 13:46

answered Jul 1, 2010 at 13:03

Joonas Trussmann

1,06413 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Naktibalda · Accepted Answer · 2010-07-01 14:50:55Z

1

SQL statements in a loop is a performance issue number one. I think that you could use INSERT SELECT instead of loop

answered Jul 1, 2010 at 14:50

Naktibalda

14.2k5 gold badges37 silver badges53 bronze badges

Collectives™ on Stack Overflow

php and mysql help, working with multiple database

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related