Accessing another database in MySQL from Android app

Question

I am using XAMPP with PHP and PHPMyAdmin as interface and trying to query information from one database, onlineportal, and inserting into another database, androidchatterdatabase, within one action. Given the code below it is not allowing to query information from $dbOnlinePortal as if I change the query statements from $dbOnlinePortal->query() to $db->query(), it shows results but returns '0' when selecting from onlineportal.types and onlineportal.campaigns.

Can I start another instance of new MySQL in the PHP index file, therefore giving a connection to two databases simultaneously? Or is there a better way to access multiple databases on one server than what I am doing?

$dbHost = "localhost";
$dbUsername = "root";
$dbPassword = "";
$dbName = "androidchatterdatabase";
$dbName2 = "onlineportal";

$db = new MySQL($dbHost,$dbUsername,$dbPassword,$dbName);
$dbOnlinePortal = new MySQL($dbHost,$dbUsername,$dbPassword,$dbName2);


case "clickSpecial":
        if ($userId = authenticateUser($db, $username, $password))
        {
            if (isset($_REQUEST['specialId']))
            {   
                // Get data
                $specialId = $_REQUEST['specialId'];    
                $timePeriod = $_REQUEST['timePeriod'];
                $cityLocationId = $_REQUEST['cityLocationId'];

                // Get the restaruant id, which will then serve to get the 
                // selected restaurant categories
                $sqlRestaurantCategorization = 
                       "SELECT distinct category_id 
                        FROM onlineportal.types
                        WHERE user_id IN (SELECT DISTINCT user_id FROM onlineportal.campaigns WHERE id = '".$specialId."');";

                // Get the categeories of the restraurant       
                if($getRestaurantCategorization = $dbOnlinePortal->query($sqlRestaurantCategorization))
                {   
                    // Insert those into the table
                    while($rowRestaurantCategorization = $dbOnlinePortal-> fetchObject($getRestaurantCategorization))
                    {
                        $sql22 = "INSERT INTO `users_click` (`usersId`, `restaurantCategoryId`, `timePeriod`, `cityLocationId`, `clickedDt`)
                                              VALUES('".$userId."','".$rowRestaurantCategorization->restaurantCategoryId."','".$timePeriod."','".$cityLocationId."',NOW());";                   

                        error_log("$sql22", 3 , "error_log");
                        if ($db->query($sql22)) 
                        {
                                $out = SUCCESSFUL;
                        }               
                        else 
                        {
                                $out = FAILED;
                        }                   

                    }   

                }
                else
                {
                    $out = FAILED;
                }       

            }
            else
            {
                $out = FAILED;
            }   

        }
        else
        {
            $out = FAILED;
        }   

    break;

Answer 1

The syntax to insert into another DB using an insert stmt is this:

USE `old_database`;
INSERT INTO `new_database`.`new_table`(`column1`,`column2`,`column3`)
SELECT `old_table`.`column2`, `old_table`.`column7`, `old_table`.`column5` 
FROM `old_table`

Edit:

Did you try doing it like this:

INSERT INTO `users_click` (`usersId`, `restaurantCategoryId`, `timePeriod`, `cityLocationId`, `clickedDt`)
                   VALUES ('".$userId."','(SELECT distinct category_id 
                                           FROM onlineportal.types
                                           WHERE user_id IN (SELECT DISTINCT user_id FROM onlineportal.campaigns WHERE id = '".$specialId."'))','".$cityLocationId."',NOW());";