move row data from original table into another table

Question

breakzzzz20 0 Newbie Poster

12 Years Ago

Good Day Experts,,can anyone help me,,i have a codes for inserting,selecting and deleting row data from original table into archieve table:

this is the original table

Username password fullname year course breakzzz breakzzzz romel first year prog.

and this is my archieve table:

username password fullnae year course

my plan is to insert the records from original table into archieve table..then this is my codes but it doesn't work

<?php

include('config2.php');

$query = mysql_query("DELETE FROM original_table WHERE id = '$id'");

$result=mysql_query("INSERT INTO archieve (username, password, fullname, course, year) SELECT *  username, password, fullname, course, year FROM original_table WHERE id='$id'")


or die (mysql_error());


while ($row = mysql_fetch_array($result))

{
     echo '<td><center><a href="write_message.php?id=1">' . $row['fullname'] . '</a></center></td>';
                echo '<td><center>' . $row['username'] . '</center></td>';
                 echo '<td><center>' . $row['password'] . '</center></td>';
                 echo '<td><center>' . $row['fullname'] . '</center></td>';
                  echo '<td><center>' . $row['course'] . '</center></td>';
                    echo '<td><center>' . $row['year'] . '</center></td>';
}

?>

hope someone help me out thanks in advance..

php

Edited 12 Years Ago by diafol because: use code tags

5 Contributors
22 Replies
233 Views
2 Days Discussion Span
Latest Post 12 Years Ago Latest Post by breakzzzz20

veedeoo 474 Junior Poster

12 Years Ago

HI,

You need to remove the * from your select. Try using this

   $result=mysql_query("INSERT INTO archieve (username, password, fullname, course, year) SELECT  username, password, fullname, course, year FROM original_table WHERE id='$id'")

Don't get too agressive on your DELETE query.. YOu need to make sure that data has already been posted on your achive table.

Put the delete query somewhere else, not on the very top..

veedeoo 474 Junior Poster

12 Years Ago

The problem in the mysql_fetch_array(), I just realize that when you insert something from another table to a new one, once the query is execueted.. it is done.., and we won't be able to pick up the result..

Before goin on further, on your achive table, is the id set as auto increment??? Make sure that it is set to autoincrement otherwise, the archive table will not have an id value.

To check if the insert to the archive table has been executed. Using the php admin take a look at the archive table.. I am pretty sure , even with the error above.. the script managed to insert..

to fix the error above, we need to create a query specific only to the inserted values on the archive table.. However, if the ID on the archive is not set to auto increment the query below will bring another error. So, please double check..

Here is the new query for the newly posted data on the archive. This will serve as a confirmation on what was posted by the first query.

     $result2 = mysql_query("SELECT * FROM archieve WHERE id='".$id."'")
      or die (mysql_error());          
      while ($row = mysql_fetch_array($result2))

     {
     echo '<td><center><a href="write_message.php?id=1">' . $row['fullname'] . '</a></center></td>';
     echo '<td><center>' . $row['username'] . '</center></td>';
     echo '<td><center>' . $row['password'] . '</center></td>';
     echo '<td><center>' . $row['fullname'] . '</center></td>';
     echo '<td><center>' . $row['course'] . '</center></td>';
     echo '<td><center>' . $row['year'] . '</center></td>';
     }

Before running the updated script above make sure to delete the entry on your archive.. We don't want the script to double post the user ...

Edited 12 Years Ago by veedeoo because: Changed $result to $result2

veedeoo 474 Junior Poster

12 Years Ago

Please observe the $result2 .. I just updated the codes above... I don't want the two results colliding each other...

Edited 12 Years Ago by veedeoo because: more info added

diafol

12 Years Ago

This syntax works for me:

INSERT INTO `table` (`field2`,`field3`,`field4`) SELECT `fieldX`,`fieldY`,`fieldZ` FROM `table2` WHERE id=2

You don';t need ' ' around the variable if it is an integer.
Also check your datatypes. Do they match?
Check your spelling - are all the fields and tables spelled correctly? Is it 'archive' or 'archieve', for example?

Edited 12 Years Ago by diafol

Biiim 182 Junior Poster

12 Years Ago

this is a backtick ` it has a different meaning

SELECT `username`, `password`, `fullname`, `course`, `year`, `status` From `account_student` WHERE `id` = '6'

they go around database,table and column names only to specifically say it is one, using ' will tell it they are strings

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

breakzzzz20 0 Newbie Poster · Answer 1 · 2012-04-21T03:33:16+00:00

breakzzzz20 0 Newbie Poster

12 Years Ago

up.:}

breakzzzz20 0 Newbie Poster · Answer 2 · 2012-04-21T04:00:44+00:00

<?php

include('config2.php');

$result= mysql_query("INSERT INTO archieve (username, password, fullname, course, year) SELECT  username, password, fullname, course, year FROM account_student WHERE id='$id'")
or die (mysql_error());     <-----line 9


while ($row = mysql_fetch_array($result))

{
     echo '<td><center><a href="write_message.php?id=1">' . $row['fullname'] . '</a></center></td>';
                echo '<td><center>' . $row['username'] . '</center></td>';
                 echo '<td><center>' . $row['password'] . '</center></td>';
                 echo '<td><center>' . $row['fullname'] . '</center></td>';
                  echo '<td><center>' . $row['course'] . '</center></td>';
                    echo '<td><center>' . $row['year'] . '</center></td>';
}

?>

`

this is now my new code but still have a error show this is the error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\AppServ\www\EOG\MY_ADMIN\archieve.php on line 9

veedeoo 474 Junior Poster Featured Poster · Answer 3 · 2012-04-21T05:27:43+00:00

Correction,. not auto increment.. and the first query should be corrected once again to add the id. The same id as in the old table and the archive. Without out this we won't be able to request any result

$result= mysql_query("INSERT INTO archieve (id,username, password, fullname, course, year) SELECT id, username, password, fullname, course, year FROM account_student WHERE id='".$id."'")

The query above should post the item in the archive table with the same id value as in $id..

Sorry about that... I must be pretty tired already or just having a weekend fever.. I will look at this again tomorrow... I out of here...

breakzzzz20 0 Newbie Poster · Answer 4 · 2012-04-21T07:58:47+00:00

yeah sir veedeoo,..done to tried that^^ but still nothing happen,can't insert into another table...wew..

breakzzzz20 0 Newbie Poster · Answer 5 · 2012-04-21T23:11:42+00:00

breakzzzz20 0 Newbie Poster

12 Years Ago

help,,

breakzzzz20 0 Newbie Poster · Answer 6 · 2012-04-22T11:57:02+00:00

breakzzzz20 0 Newbie Poster

12 Years Ago

:}

diafol · Answer 7 · 2012-04-22T14:17:28+00:00

Don't you need VALUES in the syntax?

Also you have a number of un-backticked reserved words: PASSWORD and YEAR
Ensure these are backticked.

breakzzzz20 0 Newbie Poster · Answer 8 · 2012-04-22T21:48:29+00:00

i already tried that,,:{,,do you have a sample sir ardav??

breakzzzz20 0 Newbie Poster · Answer 9 · 2012-04-23T09:50:19+00:00

wew,still have error,,

this is my account_student table:

    <?php
/* 
        VIEW.PHP
        Displays all data from 'admin_account' table
*/

        // connect to the database
        include('config2.php');

        // get results from database
        $result = mysql_query("SELECT * FROM account_student") 
                or die(mysql_error());  

        // display data in table


        echo "<table border='1' cellpadding='10' width='727'>";

        echo "<tr>  <th><center>Full Name</center></th> <th><center>Year Enrolled</center></th> <th><center>Course</center></th><th>Status</th><th>Username</th> <th><center>Add Grades</center></th>
        <th><center>View Grades</center></th>  <th> Delete</th></tr>";


        // loop through results of database query, displaying them in the table

        while($row = mysql_fetch_array( $result )) {

                // echo out the contents of each row into a table   

                echo "<tr>";

                echo '<td><center><a href="write_message.php?id=1">' . $row['fullname'] . '</a></center></td>';
                echo '<td><center>' . $row['year'] . '</center></td>';
                 echo '<td><center>' . $row['course'] . '</center></td>';
                 echo '<td><center>' . $row['status'] . '</center></td>';
                  echo '<td><center>' . $row['username'] . '</center></td>';
                echo '<td><center><a class="btn" href="add_grades.php?id=' . $row['username'] . '&course=' .$row['course'] . '"><span class="icon icon-add">&nbsp;</span></a></center></td>';
                 echo '<td><center><a class="btn" href="view_grades.php?id=' . $row['username'] . '&course=' .$row['course'] . '"><span class="icon icon-grades">&nbsp;</span></a></center></td>';

                 echo '<td><center><a class="btn" href="archieve.php?id=' . $row['id'] . '"><span class="icon icon-cancel">&nbsp;</span></a></center></td>';
                echo "</tr>"; 
        } 

        // close table>
        echo "</table>";
?>

after i click the the delete button (span class="icon icon-cancel) all the record of the student,move into table archieve..

and this is my current archieve.php:

<?php

include('config2.php');



$result= mysql_query("INSERT INTO archieve (username, password, fullname, course, year, status) SELECT 'username', 'password', 'fullname', 'course', 'year', 'status' From account_student WHERE id='6'")

or die (mysql_error());



while ($row = mysql_fetch_array($result))

{
     echo '<td><center><a href="write_message.php?id=1">' . $row['fullname'] . '</a></center></td>';
                echo '<td><center>' . $row['username'] . '</center></td>';
                 echo '<td><center>' . $row['password'] . '</center></td>';
                 echo '<td><center>' . $row['fullname'] . '</center></td>';
                  echo '<td><center>' . $row['course'] . '</center></td>';
                    echo '<td><center>' . $row['year'] . '</center></td>';
}

?>

very appreciate,,hope someone help me,,..

breakzzzz20 0 Newbie Poster · Answer 10 · 2012-04-23T10:02:35+00:00

this is the error when i use the ^^

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''archieve' ('username', 'password', 'fullname', 'course', 'year', 'status') SELE' at line 1

Biiim 182 Junior Poster · Answer 11 · 2012-04-23T10:04:07+00:00

<?php
include('config2.php');
$result= mysql_query("INSERT INTO archieve (username, password, fullname, course, year, status) SELECT 'username', 'password', 'fullname', 'course', 'year', 'status' From account_student WHERE id='6'")

are you not pulling the $_GET variable?

<?php
include('config2.php');
if(ctype_digit($_GET['id'])){
    $id = $_GET['id'];
}
$result= mysql_query("INSERT INTO archieve (username, password, fullname, course, year, status) SELECT 'username', 'password', 'fullname', 'course', 'year', 'status' From account_student WHERE id='$id'")

breakzzzz20 0 Newbie Poster · Answer 12 · 2012-04-23T10:19:32+00:00

no,,'m not pulling the get variable,,wew,,i tried ur code but it gives me an error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\AppServ\www\EOG\MY_ADMIN\archieve.php on line 12

this is my new archieve.php:

<?php
include('config2.php');
if(ctype_digit($_GET['id'])){
    $id = $_GET['id'];
}
$result= mysql_query("INSERT INTO archieve (username, password, fullname, course, year, status) SELECT 'username', 'password', 'fullname', 'course', 'year', 'status' From account_student WHERE id='$id'")

or die (mysql_error());



while ($row = mysql_fetch_array($result))

{
     echo '<td><center><a href="write_message.php?id=1">' . $row['fullname'] . '</a></center></td>';
                echo '<td><center>' . $row['username'] . '</center></td>';
                 echo '<td><center>' . $row['password'] . '</center></td>';
                 echo '<td><center>' . $row['fullname'] . '</center></td>';
                  echo '<td><center>' . $row['course'] . '</center></td>';
                    echo '<td><center>' . $row['year'] . '</center></td>';
}

?>

urtrivedi 276 Nearly a Posting Virtuoso · Answer 13 · 2012-04-23T10:25:48+00:00

while ($row = mysql_fetch_array($result))

Is used after select statment to display/process selected reords, but you have inserted record not selecte before while loop. So you need one more select statment before while loop.

breakzzzz20 0 Newbie Poster · Answer 14 · 2012-04-23T10:58:41+00:00

breakzzzz20 0 Newbie Poster

12 Years Ago

can you give me some example please??

diafol · Answer 15 · 2012-04-23T11:22:27+00:00

It looks as though you're using single quotes instead of backticks. 'this' is not the same as `this`. If this is the case, the backtick key is usually the one before '1' on the keyboard. Press this once followed by another key.

Biiim 182 Junior Poster · Answer 16 · 2012-04-23T13:10:47+00:00

<?php
include('config2.php');
if(ctype_digit($_GET['id'])){
    $id = $_GET['id'];
}
$insert_result = mysql_query("INSERT INTO archieve (username, password, fullname, course, year, status) SELECT `username`, `password`, `fullname`, `course`, `year`, `status` From `account_student` WHERE id = '$id'") or die (mysql_error());

$select_result = mysql_query("SELECT `id`,`username`, `password`, `fullname`, `course`, `year`, `status` From `account_student` WHERE id = '$id'") or die (mysql_error());


while ($row = mysql_fetch_array($select_result))
{
     echo '<td><center><a href="write_message.php?id=' . $row['id'] . '">' . $row['fullname'] . '</a></center></td>';
                echo '<td><center>' . $row['username'] . '</center></td>';
                 echo '<td><center>' . $row['password'] . '</center></td>';
                 echo '<td><center>' . $row['fullname'] . '</center></td>';
                  echo '<td><center>' . $row['course'] . '</center></td>';
                    echo '<td><center>' . $row['year'] . '</center></td>';
}
?>

Also for back referencing you might want to store the id too:

$insert_result = mysql_query("INSERT INTO archieve (id, username, password, fullname, course, year, status) SELECT `id`,`username`, `password`, `fullname`, `course`, `year`, `status` From `account_student` WHERE id = '$id'") or die (mysql_error());

breakzzzz20 0 Newbie Poster · Answer 17 · 2012-04-23T14:11:27+00:00

,,soory,,first time to heard the backticked..:}

wew,,,,you all great here sir ardav,vedeoo,and biim,,very,very,appreciated your help,,i have now the complete working codes..

<?php
include('config2.php');
if(ctype_digit($_GET['id'])){
    $id = $_GET['id'];
}
$insert_result = mysql_query("INSERT INTO archieve (username, password, fullname, course, year, status) SELECT `username`, `password`, `fullname`, `course`, `year`, `status` From `account_student` WHERE id = '$id'") or die (mysql_error());

$select_result = mysql_query("SELECT `id`,`username`, `password`, `fullname`, `course`, `year`, `status` From `account_student` WHERE id = '$id'") or die (mysql_error());

$delete_result = mysql_query("DELETE FROM account_student WHERE id = '$id'") or die (mysql_error());
while ($row = mysql_fetch_array($select_result))
{
     echo '<td><center><a href="write_message.php?id=' . $row['id'] . '">' . $row['fullname'] . '</a></center></td>';
                echo '<td><center>' . $row['username'] . '</center></td>';
                 echo '<td><center>' . $row['password'] . '</center></td>';
                 echo '<td><center>' . $row['fullname'] . '</center></td>';
                  echo '<td><center>' . $row['course'] . '</center></td>';
                    echo '<td><center>' . $row['year'] . '</center></td>';
}
?>