PHP Data from 1 MySQL Table to Another

Hi all,

I need a PHP code so I can move records from one table to another.

For example:

MOVE 10 Records FROM Table1 to Table2 Where Qid='1'

So it will Move the number of records I ask it to move from one table to the other.

How can I this in PHP please?
LVL 1
error2013Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dimmergeekCommented:
INSERT INTO Table2 SELECT s.* FROM Table1 s WHERE Qid = '1';

Open in new window


That should be your query.  You just need to execute in PHP with your active db connection.

$result=$con->query($mySQL);

Open in new window

0
Ray PaseurCommented:
Use a LIMIT clause to set the number of records.
0
dimmergeekCommented:
Be VERY CAREFUL using my recommendation. It does work, but it assumes the order of columns in the two tables is identical; it does not match by column name, and does try to coerce values to fit, which can cause unexpected results
0
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

dimmergeekCommented:
INSERT INTO Table2 SELECT s.* FROM Table1 s WHERE Qid = '1' LIMIT 10;

Open in new window

0
Ray PaseurCommented:
I can see from this and your other recent questions here that you're relatively new to PHP and you might benefit from some structured learning.  This article will help you get a foundation and most importantly, will help you avoid learning from all of the terrible and incompetent PHP examples that show up in a Google search.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html

Give yourself a month with that article and the recommended books, and you will be a year ahead of trial-and-error learning, I promise!
0
error2013Author Commented:
Hi dimmergeek,

The only copies the records, I don't want to keep the records what have been moved in Table1.

I also need to tell it how many to move.
0
dimmergeekCommented:
Okay,

Does each record with a matching Qid have a unique record ID column?
If so, what is that field called?
0
Ray PaseurCommented:
And to expand a little bit on what @dimmergeek wisely points out, never use SELECT * but instead SELECT the columns by name.  You will save yourself from a future catastrophe if you name the columns explicitly in all of your queries!  With SELECT * you do not know exactly what you're getting in the results set, and when you're programming a computer it's not good to be the unknowing one.
0
Ray PaseurCommented:
How many to move? LIMIT clause.  We covered that.

Don't want to keep the data in the old table? DELETE query.

When you're doing something like this you want to be very sure that you can rerun the entire process without losing any data.  That requires a little bit of planning and since it will require more than one query, I'd recommend that you speak to a DBA for advice.
0
error2013Author Commented:
Hi dimmergeek,

Yes all records have a unique id field called id but I will be quering in Qid which is not unique as multiple records will have it.
0
Ray PaseurCommented:
If you post your CREATE TABLE statements, we might be able to show you the exact code.
0
error2013Author Commented:
Can I have some php code sample please?
0
Ray PaseurCommented:
Let me try this one again... We are experts but not mind readers!

If you post your CREATE TABLE statements, we might be able to show you the exact code.

0
error2013Author Commented:
Table Example:

Table 1 and 2 have the same fields.

Fields:

id, name, qid;
0
dimmergeekCommented:
<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "db_name");

/* check connection */
if (mysqli_connect_errno()) {
    echo("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query = "SELECT id FROM Table1 WHERE Qid = '1' LIMIT 10";

if ($result = $mysqli->query($query)) {

    /* fetch object array */
    while ($row = $result->fetch_row()) {
        $query = "INSERT INTO Table2 SELECT s.* FROM Table1 s WHERE id=" . $row[1] . ";"
        $mysqli->query($query);
        $query = "DELETE FROM Table1 WHERE id=" . $row[1] . ";"
        $mysqli->query($query);
    }

    /* free result set */
    $result->close();
}

/* close connection */
$mysqli->close();
?>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dimmergeekCommented:
Thanks!
0
error2013Author Commented:
Just one thing please...I'm getting an error here:

 echo("Connect failed: %s\n", mysqli_connect_error());

Error is:

Parse error: syntax error, unexpected ',' in /home4/index2.php on line 6

Any ideas pls?
0
error2013Author Commented:
Update: Fixed that one but now I've getting an error here:

  $mysqli->query($query);

Parse error: syntax error, unexpected T_VARIABLE
0
Ray PaseurCommented:
@error2013: Not everyone who posts solutions here tests them before they are posted, and as you can see, accepting an answer before you have evaluated the efficacy of the answer is not usually a very good idea.

Some examples of how to run queries, including error checking, are available in this article.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html\

And you probably want to read up on this.  It might work to mix procedural and OOP styles of calls to the MySQLi extension, but I wouldn't bet on it.
http://php.net/manual/en/mysqli.connect-errno.php
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.