Solved

delete certain fields of information of a selected table php mysql

Posted on 2010-09-15
7
382 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 109

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
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 dynamically set the form action using jQuery.

808 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