delete certain fields of information of a selected table php mysql

Hi there,

I have the following code (Thanks to a user on EE previously).

The code displays information from a table. The "move_records" allows for the record to be selected using a checkbox, and then moved to another table.

Once this data has been moved, affectively archived, I wanted to be able to delete some sensitive data, such as 'nature' but wanted to keep some data for stats, such as date.

Is there any way in doing this?

Thanks in advance

I HAVER TRIED TO MOVE THIS QUESTION INTO MYSQL SERVER, SO I APOLOGIS TO amar_bardoliwala: FOR HIS HELP. THIS IS WHAT HE WROTE

amar_bardoliwala:Hello thomo03,

While moving your data you can ignore or remove fields for sensitive data in your insert query, so in that way your data will not be stored in archive table.

Thanks.

I WORTE BACK

I see what your saying, and it seems like the obvious way to go about it. The problem is I have a file which selects the source table and the destination table, but I don't know how to just select fields...I'm new to this (If you can't tell!)

This is the 'move code'

$queryInsert= "INSERT INTO $dest_table (SELECT * FROM $src_table WHERE id IN ($ids))";
$queryDelete= "DELETE FROM $src_table WHERE id IN ($ids)";
if(!mysql_query($queryInsert))    
    show_message('Referrals could not be moved',2);
if(!mysql_query($queryDelete))
    show_message('Referrals could not be moved',2);
header("Location: ".$_SERVER['HTTP_REFERER']);

How would I go about inserting only some fields and deleting others (Am I being clear?!)

Thanks
<form action="" method="post">
      <table width="99%" class="grid">
        <thead>
          <tr>
            <th width="58">Date</th>
            <th width="266"><div align="center">name</div></th>
            <th width="78"><div align="center">View</div></th>
            <th width="130"><div align="center">Modify</div></th>
            <th width="74"><div align="center">Move</div>
                <div align="center"></div></th>
          </tr>
        </thead>
        <tbody>
          <?php  
    if(!mysql_num_rows($rs))
        show_message('There are no records ',2);
    while($result = mysql_fetch_array($rs)) 
    {
        $id     			=$result['id'];
        $date   			=$result['date'];
        $nature   	=$result['nature'];
    	echo "<tr>"; 
        echo "<td>$date</td>";
        echo "<td>$nature</td>";
        echo "<td><a href=\"view.php?id=$id\"> view</a></td>";
	    echo "<td><a href=\"modify.php?id=$id\"> modify</a></td>";
	    echo "<td><input name=\"id[]\" type=\"checkbox\" value=\"$id\"/></td>";
	    echo "</tr>";
     }
 ?>
        </tbody>
      </table>
      <input type="submit" value="move selected" onClick="move_records();"/>
    </form>

Open in new window

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

Amar BardoliwalaCommented:
Hello thomo03,

Following is sample example of your problem. Hopefully you will understand how to do it.

INSERT INTO tbl_temp2 (fld_id)
  SELECT tbl_temp1.fld_order_id
  FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;


Thanks.
0
thomo03Author Commented:
Hi Amar,

My brain hurts! (it really does!)

I've decided I will have a seperate table, I am going to call 'stats' I am able to move all the archeived data into 'stats'

$queryInsert= "INSERT INTO $stats_table (SELECT * FROM $archeived_table WHERE id IN ($ids))";

But instead of picking out all the fields how do I juat slect say 'date' field of 'name field'

I'm sure its this line of code I've selected?!

Sorry for having to do all the leg work.

Cheers
0
Amar BardoliwalaCommented:
Hello thomo03,

suppose you have stats table with fields (id, date) and archieved table with fields (id, date, nature) than you query should be like given below.

$queryInsert= "INSERT INTO $stats_table(id, date) (SELECT (id, date) FROM $archeived_table WHERE id IN ($ids))";

Hope this will help.

Let me know if you have any problems.


Thanks.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

thomo03Author Commented:
Hi Amar

This works
 
<?php
if(!isset($_POST['id']))
    header("Location: ".$_SERVER['HTTP_REFERER']);
require_once('config.inc.php');
require_once('common.inc.php');
require_once('connection.inc.php');
$ids        = implode(',',$_POST['id']);
$queryInsert= "INSERT INTO $dest_table (SELECT * FROM $src_table WHERE id IN ($ids))";
$queryDelete= "DELETE FROM $src_table WHERE id IN ($ids)";
if(!mysql_query($queryInsert))    
    show_message('Referrals could not be inserted,2);
if(!mysql_query($queryDelete))
    show_message('Referrals could not be deleted,2);
header("Location: ".$_SERVER['HTTP_REFERER']);  
 But this doesn't
<?php
if(!isset($_POST['id']))
    header("Location: ".$_SERVER['HTTP_REFERER']);
require_once('config.inc.php');
require_once('common.inc.php');
require_once('connection.inc.php');
$ids        = implode(',',$_POST['id']);
$queryInsert= "INSERT INTO $dest_table (id, date) (SELECT (id, date) FROM $src_table WHERE id IN ($ids))";
$queryDelete= "DELETE FROM $src_table WHERE id IN ($ids)";
if(!mysql_query($queryInsert))    
    show_message('Referrals could not be inserted',2);
if(!mysql_query($queryDelete))
    show_message('Referrals could not be deleted',2);
header("Location: ".$_SERVER['HTTP_REFERER']);  
?>
 
 I get a message from the code saying ''Referrals could not be inserted'
 
thanks
0
Ray PaseurCommented:
Let's look at these two lines of code...

$ids        = implode(',',$_POST['id']);
$queryDelete= "DELETE FROM $src_table WHERE id IN ($ids)";

Now think about what will have happened to your data when a hacker sends your form this attack string...

$_POST["id"] = serialize(range(1,2147483647));

While that is admittedly a little extreme, it makes an important point.  If you do not filter the external input it is only matter of time before catastrophe is not left to chance.  A more reasonable design pattern might be to separate the INSERT and DELETE programming.  Your brain will not hurt as much.  Just a thought, ~Ray
0
Amar BardoliwalaCommented:
Hello thomo03,

you can print real mysql error using following

if(!mysql_query($queryInsert))
 die('Invalid query: ' . mysql_error());

Please give the real error. it will help us understanding the real problem.

Also I just gave you an example. what you need to do is to confirm that which fields are necessary(fields that can not be null) in $dest_table. because you need to insert all necessary fields in your table. After checking that you need to include all necessary fields in that insert query and not only (id,date) fields taken currently..

also you can try to insert one record directly like "insert into test(id,date) values(1, '2010-09-16');" in $dest table from your mysql console or using phpmyadmin.

It is just to test to see if that works. Also you need to make sure that field types in your table and your insert statements are matching.

Thanks.

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
thomo03Author Commented:
Thank you Ray.

I will be looking into it, first I really want to sort out this transfer. But thatn you for your advice.

Hi Amar,

I have taken a screen dump of my table structure for 'stats', it is the same as the 'arc' as I've use SQL export.

Is the issue to do with the id and date table?

The following ...

"insert into test(id,date) values(1, '2010-09-16');" in $dest

using phpmyadmin and it worked fine.


TheI code is

nvalid query: Operand should contain 1 column(s)

Sorry I'm not very good, I really appricate your time and efforts
table-structure.gif
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.