DELETE FROM query via CSV contents MySQL

Question

I have a CSV file uploaded to the same directory as the file containing the email addresses of people I want to delete from a table (all at once) in my database. The code I'm using echoes correctly but doesn't delete anything from the table. The CSV file is uploaded to the same directory as the file containing the code below.

<?php
    // Make a MySQL Connection
    mysql_connect("x", "x", "x") or die(mysql_error());
    mysql_select_db("volunteers2012") or die(mysql_error());
    mysql_query($sql);
    /********************************/

    $addresses = file('deleteEmails.csv');
    foreach($addresses as $address) {
        $safe_address = mysql_real_escape_string($address);
        $sql = "DELETE FROM vols2012 WHERE `email` = '$safe_address'";
        $result = mysql_query($sql) or die(mysql_error());
    }
    echo "DELETION A GREAT SUCCESS!";
    ?>

Round 2- Trying with a .txt file

<?php
    // Make a MySQL Connection
    mysql_connect("x", "x", "x") or die(mysql_error());
    mysql_select_db("volunteers2012") or die(mysql_error());
    mysql_query($sql);
    /********************************/

    $addresses = "LOAD DATA LOCAL INFILE ('deleteEmails.txt')";
foreach($addresses as $address) {
$safe_address = mysql_real_escape_string($address);
$sql = "DELETE FROM vols2012 WHERE (email = '$safe_address')";
$result = mysql_query($sql) or die(mysql_error());
   }
    echo "DELETION A GREAT SUCCESS!";
    ?>

I'm still getting the success echo, but with the error- Invalid argument supplied for foreach().

LOAD DATA INFILE supports CSV, so you don't need the PHP loop. — OMG Ponies
– OMG Ponies, Commented May 12, 2012 at 3:07
It's a CSV file with one column of email addresses...I don't have the email column labeled...trying that and the LOAD DATA now — Commandrea
– Commandrea, Commented May 12, 2012 at 3:21

Commandrea · Accepted Answer · 2012-05-20 18:50:46Z

I wound up not using the upload to delete method and instead created an html page w/ checkboxes to delete by hand. In the end this was the best method as I needed to leave some of the records in one table but not the other.

          <form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
    <?php


       // Delete the customer rows (only if the form has been submitted)

         if(isset($_POST['submit']))
      {
      foreach($_POST['todelete'] as $delete_id) {

             $query="DELETE FROM volConfirm WHERE email='$delete_id'";


                mysql_query($query) or die ('Error in query: ' .mysql_errno() . mysql_error());
            }


        }


        // Display the customer rows with checkboxes for deleting
        $query="SELECT * FROM volConfirm";

        $result = mysql_query($query);
        while ($row = mysql_fetch_array($result)){
        echo '<input type="checkbox" value="' . $row['email'] .'"name="todelete[]" />';
        echo $row['agreeName'];
        echo ' '.$row['position'];
        echo ' '.$row['shift_times'];
        echo ' '.$row['email'];
        echo ' '.$row['confirmed'];
        echo '<br />';
        echo '<br />';
       }
     ?>

        <input type="submit" name="submit" value="Remove" />
      </form>

Julian H. Lam · Accepted Answer · 2012-05-12 03:44:01Z

0

For a CSV list of email addresses, try this query on for size:

PHP

$emails = '[email protected],[email protected],[email protected]';

SQL

$emails = mysql_real_escape_string($emails);
$sql = "DELETE FROM vols2012 WHERE `email` IN ({$emails})";
mysql_query($sql);

answered May 12, 2012 at 3:44

Julian H. Lam

26.3k13 gold badges51 silver badges74 bronze badges

4 Comments

Commandrea Over a year ago

But where do I call the .csv file?

Julian H. Lam Over a year ago

In the above example, I am simply assuming that the CSV file is is read via fopen(); or file_get_contents();. I have just excluded it for brevity.

Commandrea Over a year ago

Ah- you assumed I knew how to do that? I'm flattered :) I do have a download php file that I think contains something similar. I'm not sure how to modify for row deletion. Would the file I'm using to delete need to contain all of the column headers?

Julian H. Lam Over a year ago

Oh, hm. I assumed from the first example that the CSV file was actually in the format as specified in the $emails variable. In the end, there's no "set" way to do this, it all depends on what the source CSV file looks like, and what you want to do with it. Providing the CSV source may help get better answers.

Collectives™ on Stack Overflow

DELETE FROM query via CSV contents MySQL

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related