Solved

delete certain fields of information of a selected table php mysql

Posted on 2010-09-15
7
380 Views
Last Modified: 2013-12-12
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

0
Comment
Question by:thomo03
  • 3
  • 3
7 Comments
 
LVL 11

Expert Comment

by:Amar Bardoliwala
ID: 33682227
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
 

Author Comment

by:thomo03
ID: 33682586
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
 
LVL 11

Expert Comment

by:Amar Bardoliwala
ID: 33682655
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:thomo03
ID: 33683445
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 33690524
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
 
LVL 11

Accepted Solution

by:
Amar Bardoliwala earned 500 total points
ID: 33691231
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
 

Author Comment

by:thomo03
ID: 33699700
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

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now