Solved

delete certain fields of information of a selected table php mysql

Posted on 2010-09-15
7
384 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

 

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 110

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
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…

729 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