Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

delete certain fields of information of a selected table php mysql

Posted on 2010-09-15
7
Medium Priority
?
393 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 111

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 2000 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 Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

564 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