0

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

Edited by diafol: use code tags

5
Contributors
22
Replies
24
Views
5 Years
Discussion Span
Last Post by breakzzzz20
Featured Replies
  • 1

    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 … Read More

  • 1

    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 … Read More

  • 2
    diafol 3,720   5 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' … Read More

  • 1

    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 Read More

1

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

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

Edited by diafol: use code tags instead of inline tags for code block

1

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 by veedeoo: Changed $result to $result2

0

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

Edited by veedeoo: more info added

0

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

Edited by veedeoo: more info added

0

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

0

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.

2

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 by diafol

0

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

1

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

0

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
0

<?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'")
0

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

?>
0

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.

0

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.

0
<?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());

Edited by Biiim

0

,,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>';
}
?>
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.