move row data from original table into another table

[breakzzzz20]

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..

[breakzzzz20]

up.:}

[veedeoo]

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..

[breakzzzz20]

<?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]

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 ...

[veedeoo]

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

[veedeoo]

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]

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

[breakzzzz20]

help,,

[breakzzzz20]

:}

[diafol]

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]

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

[diafol]

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?

[breakzzzz20]

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,,..

[Biiim]

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

[breakzzzz20]

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]

<?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]

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]

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]

can you give me some example please??

[diafol]

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]

<?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]

,,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>';
}
?>