MySQLi / PHP - Pulling data from one database. Inserting into another database

Question

Trying to pull data out of a basic phpmyadmin database.

The code below pulls the data correctly (Commented out section verify). I can write it to the screen and display it. (Not needed just testing) Trying to insert it into another database however and it fails.

I've discovered that the while loops for inserting do not run. Although I can not find out why.

It's a basic localhost database (Testing right now) So the connect data is just temporary.

Any assistance is greatly appreciated Thanks.

<?php

/*
  Connect to database
 */
$webhost = 'localhost';
$webusername = 'root';
$webpassword = '';
$webdbname = 'transfertest';
$webcon = mysqli_connect($webhost, $webusername, $webpassword, $webdbname);
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
/*
 * 
 */
$questions = mysqli_query($webcon, "SELECT * FROM questions");
$scenarios = mysqli_query($webcon, "SELECT * FROM scenarios");
$results = mysqli_query($webcon, "SELECT * FROM results");
$employees = mysqli_query($webcon, "SELECT * FROM employees");
/*
 * These while loops display the content being pulled from the database correctly.
while ($row = mysqli_fetch_array($questions)) {
    echo $row['questionID'] . " : " . $row['question'] . " : " . $row['answers'];
    echo "</br>";
}
while ($row = mysqli_fetch_array($scenarios)) {
    echo $row['scenarioID'] . " : " . $row['scenarioTitle'] . " : " . $row['scenarioInformation'];
    echo "</br>";
}
while ($row = mysqli_fetch_array($results)) {
    echo $row['employeeID'] . " : " . $row['scenarioID'] . " : " . $row['questionID'] . " : " . $row['answers'] . " : " . $row['correct'];
    echo "</br>";
}
while ($row = mysqli_fetch_array($employees)) {
    echo $row['employeeID'] . " : " . $row['firstName'] . " : " . $row['lastName'] . " : " . $row['email'] . " : " . $row['password'];
    echo "</br>";
}
 */
/* //////////////////////////////////////////////////////////////////////////
  Connect to database
 */
$mobhost = 'localhost';
$mobusername = 'root';
$mobpassword = '';
$mobdbname = 'exampletransfer';
$mobcon = mysqli_connect($mobhost, $mobusername, $mobpassword, $mobdbname);
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
/*
 * 
 */
while ($row = mysqli_fetch_array($questions)) {
    mysqli_query($mobcon, "INSERT INTO questions (questionID, question, answers) VALUES (" . $row['questionID'] . ", " . $row['question'] . ", " . $row['answers'] . ")");
}
while ($row = mysqli_fetch_array($scenarios)) {
    mysqli_query($mobcon, "INSERT INTO scenarios (scenarioID, scenarioTitle, scenarioInformation) VALUES (" . $row['scenariosID'] . ", " . $row['scenarioTitle'] . ", " . $row['scenarioInformation'] . ")");
}
while ($row = mysqli_fetch_array($results)) {
    mysqli_query($mobcon, "INSERT INTO results (employeeID, scenarioID, questionID, answers, correct) VALUES (" . $row['employeesID'] . ", " . $row['scenariosID'] . ", " . $row['questionID'] . ", " . $row['answers'] . ", " . $row['correct'] . ")");
}
while ($row = mysqli_fetch_array($employees)) {
    mysqli_query($mobcon, "INSERT INTO employees (employeeID, firstName, lastName, email, password) VALUES (" . $row['employeesID'] . ", " . $row['firstName'] . ", " . $row['lastName'] . ", " . $row['email'] . ", " . $row['password'] . ")");
}
/*
  Close Connections
 */
mysqli_close($webcon);
mysqli_close($mobcon);
/*
 * Error code:
Notice: Undefined index: scenariosID on line 75

Notice: Undefined index: employeesID on line 78

Notice: Undefined index: scenariosID on line 78

Notice: Undefined index: employeesID on line 81
 */
?>

You've closed the connection to the first database before you fetch the data from it. Just close it later, after the copying. — KIKO Software
– KIKO Software, Commented Sep 10, 2014 at 8:41
i think the mysqli_fetch_array($questions) cannot be done since you've closed the connection for that db. — may saghira
– may saghira, Commented Sep 10, 2014 at 8:42
But I've got 2 mysqli_connects, wont it conflict if I leave it open longer? I'm closing $webcon after I've stored the data in a variable. Isn't that fine? — Hamedar
– Hamedar, Commented Sep 10, 2014 at 8:43
Just give them different names. You can open more than one database. You use the database resource variable in mysqli_query() so the query knows to which database it refers. — KIKO Software
– KIKO Software, Commented Sep 10, 2014 at 8:43
I changed the code to reflect closing the second database at the end, however it has thrown the same errors. — Hamedar
– Hamedar, Commented Sep 10, 2014 at 8:47

PatrickB · Accepted Answer · 2014-09-10 08:45:04Z

4

The problem is that you close your $webcon connection and then you try to read from it ^^

You try to do this... Thats not possible ;)

Prepare query mysqli_query($webcon, "SELECT * FROM questions");
Close connection <<< after that i cant read data
Read data

Try this please.

<?php

/**
 * Connect to database
 */
$webhost        = 'localhost';
$webusername    = 'root';
$webpassword    = '';
$webdbname      = 'transfertest';
$webcon         = mysqli_connect($webhost, $webusername, $webpassword, $webdbname);
if (mysqli_connect_errno())
{
    echo 'Failed to connect to MySQL: ' . mysqli_connect_error();
}

/**
 * Queries for reading
 */
$questions = mysqli_query($webcon, 'SELECT * FROM `questions`');
$scenarios = mysqli_query($webcon, 'SELECT * FROM `scenarios`');
$results = mysqli_query($webcon, 'SELECT * FROM `results`');
$employees = mysqli_query($webcon, 'SELECT * FROM `employees`');

/**
 * Connect to database
 */
$mobhost        = 'localhost';
$mobusername    = 'root';
$mobpassword    = '';
$mobdbname      = 'exampletransfer';
$mobcon         = mysqli_connect($mobhost, $mobusername, $mobpassword, $mobdbname);
if (mysqli_connect_errno())
{
    echo 'Failed to connect to MySQL: ' . mysqli_connect_error();
}

/**
 * Insert data from old database
 */

// questions
while ($row = mysqli_fetch_array($questions))
{
    // escape your strings
    foreach($row as $key => $val)
    {
        $row[$key] = mysqli_real_escape_string($mobcon, $row[$key]);
    }
    mysqli_query($mobcon, "INSERT INTO `questions` (`questionID`, `question`, `answers`) VALUES ('" . $row['questionID'] . "', '" . $row['question'] . "', '" . $row['answers'] . "');");
}

// scenarios
while ($row = mysqli_fetch_array($scenarios))
{
    // escape your strings
    foreach($row as $key => $val)
    {
        $row[$key] = mysqli_real_escape_string($mobcon, $row[$key]);
    }
    mysqli_query($mobcon, "INSERT INTO `scenarios` (`scenarioID`, `scenarioTitle`, `scenarioInformation`) VALUES ('" . $row['scenariosID'] . "', '" . $row['scenarioTitle'] . "', '" . $row['scenarioInformation'] . "');");
}

// results
while ($row = mysqli_fetch_array($results))
{
    // escape your strings
    foreach($row as $key => $val)
    {
        $row[$key] = mysqli_real_escape_string($mobcon, $row[$key]);
    }
    mysqli_query($mobcon, "INSERT INTO `results` (`employeeID`, `scenarioID`, `questionID`, `answers`, `correct`) VALUES ('" . $row['employeesID'] . "', '" . $row['scenariosID'] . "', '" . $row['questionID'] . "', '" . $row['answers'] . "', '" . $row['correct'] . "');");
}

// employees
while ($row = mysqli_fetch_array($employees))
{
    // escape your strings
    foreach($row as $key => $val)
    {
        $row[$key] = mysqli_real_escape_string($mobcon, $row[$key]);
    }
    mysqli_query($mobcon, "INSERT INTO `employees` (`employeeID`, `firstName`, `lastName`, `email`, `password`) VALUES ('" . $row['employeesID'] . "', '" . $row['firstName'] . "', '" . $row['lastName'] . "', '" . $row['email'] . "', '" . $row['password'] . "');");
}

/*
  Close Connections
 */
mysqli_close($mobcon);
mysqli_close($webcon);

answered Sep 10, 2014 at 8:45

PatrickB

3,2757 gold badges36 silver badges55 bronze badges

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

4 Comments

Hamedar Over a year ago

Tried your code. Get the same errors. Notice: Undefined index: scenariosID on line 60 Notice: Undefined index: employeesID on line 71 Notice: Undefined index: scenariosID on line 71 Notice: Undefined index: employeesID on line 82

Hamedar Over a year ago

Its working... Although it's still throwing those errors I've posted above. Strange. I'll try to iron out those errors. Thank you PatrickB

PatrickB Over a year ago

Send me sql example dump (1 data entry is enough + structure) and i'll fix ^^

Hamedar Over a year ago

Managed to piece it together. The issue was that I was trying to insert into the wrong database column. So it threw an error. Instead of $row['employeesID'] it should of been $row['employeeID']. Works wonders and is perfect for what I needed PatrickB. Thank you greatly.

James Lalor · Accepted Answer · 2014-09-10 09:40:12Z

Pending it's on the same server and using the same username and password:

// Create a new MySQL database connection
if (!$con = mysql_connect('localhost', $username, $password)) {
    die('An error occurred while connecting to the MySQL server!<br/>' . mysql_error());
}

if (!mysql_select_db($database)) {
    die('An error occurred while connecting to the database!<br/>' . mysql_error());
}

// Create an array of MySQL queries to run
$sql = array(
    'DROP TABLE IF EXISTS `exampletransfer.questions`;',
    'CREATE TABLE `exampletransfer.questions` SELECT * FROM `transfertest.questions`'
);

// Run the MySQL queries
if (sizeof($sql) > 0) {
    foreach ($sql as $query) {
        if (!mysql_query($query)) {
            die('A MySQL error has occurred!<br/>' . mysql_error());
        }
    }
}

If using MySQLi instead of MySQL:

// Create a new MySQL database connection
if (!$con = new mysqli('localhost', $username, $password, $database)) {
    die('An error occurred while connecting to the MySQL server!<br/>' . $con->connect_error);
}

// Create an array of MySQL queries to run
$sql = array(
    'DROP TABLE IF EXISTS `exampletransfer.questions`;',
    'CREATE TABLE `exampletransfer.questions` SELECT * FROM `transfertest.questions`'
);

// Run the MySQL queries
if (sizeof($sql) > 0) {
    foreach ($sql as $query) {
        if (!$con->query($query)) {
            die('A MySQL error has occurred!<br/>' . $con->error);
        }
    }
}

$con->close();

Collectives™ on Stack Overflow

MySQLi / PHP - Pulling data from one database. Inserting into another database

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related