Solved

Why does this delete statement laugh at me?

Posted on 2014-10-24
6
73 Views
Last Modified: 2014-10-29
I'm grabbing from a table everything I need to create a CSV file. It's lovely, the way that it works:

$filename="twitter.csv";
header('Content-type:application/csv');
header('Content-Disposition: attachment; filename='.$filename);
echo $output;
$dan="delete from twitter_csv where session_id='$session_id'";
$dan_query=mysqli_query($cxn, $dan);

My problem is what I want to happen after I "echo $output." I need to delete everything in the table that coincides with that session_id. It's not happening. This morning my table is upwards of 2,000,000 rows which means I've got a monster that is growing in in power and size and I've got to keep it manageable.

What's wrong with my logic? Why isn't my table cleared of all data after it's created the CSV file and what can I do to correct my error?
0
Comment
Question by:brucegust
[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
6 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40402162
maybe it is long shot bu try like this :

$dan="delete from twitter_csv where trim(session_id)=trim('$session_id')";

Open in new window

0
 

Author Comment

by:brucegust
ID: 40402197
So, Samo, you're thinking that my logic is sound, it's just the delete statement is coming up short because my search criteria needs to tweaked? I'm testing it now, but I wanted to confirm that where I've got the delete statement situated resonates as being correct.
0
 
LVL 19

Expert Comment

by:alextoft
ID: 40402311
Maybe...

if (!$dan_query) {
  print "Fail-o-rama: ".mysqli_error($cxn);
}

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 58

Assisted Solution

by:Gary
Gary earned 250 total points
ID: 40403395
Better to have a try/error in the execution of the sql so you can capture any errors tho judging from your php that is not possible
Obvious question is are you sure $session_id actually equals anything since there is nothing in your opening question where it is being set
0
 
LVL 110

Accepted Solution

by:
Ray Paseur earned 250 total points
ID: 40404047
You need to be able to detect errors in your queries, as well as to visualize these errors (We've tried to give you design patterns that will accomplish this in other answers, so I'll try to explain this a little more here).

MySQL is not a black box.  It can and will fail for reasons that are outside your control, and if you don't deliberately write your scripts to test for success or failure, you can be fairly certain that eventually disaster is not left to chance.  

1. To test for success or failure, test the return values from the function calls, for example, as documented here.  Read this man page carefully, for understanding, paying particular attention to "Return Values."
http://php.net/manual/en/mysqli.query.php

2. To visualize any error conditions, use the built-in PHP function trigger_error().  This will write an error_log() entry.  After the execution of a script you can look for the error_log file.  If you find one, examine its contents to see what happened.
http://php.net/manual/en/function.trigger-error.php

You might try writing the query a little more like this.  Then look at the error_log and maybe you can find the problem.
// ALWAYS RAISE THE ERROR REPORTING LEVEL TO THE HIGHEST AVAILABLE
error_reporting(E_ALL);

// CONSTRUCT A QUERY STRING IN ITS OWN VARIABLE
$qry = "DELETE FROM twitter_csv WHERE session_id='$session_id'";

// LOG THE QUERY STRING SO YOU CAN SEE WHAT IT CONTAINS
error_log("TRY: $qry");

// RUN THE QUERY AND TEST FOR SUCCESS OR FAILURE
$res = mysqli_query($cxn, $qry);
if (!$res) trigger_error("FAIL: $qry BECAUSE: " . mysqli_error($cxn), E_USER_ERROR);

// DETERMINE IF ANY ROWS WERE AFFECTED
$num = mysqli_affected_rows($cxn);
error_log("AFFECTED ROWS: $num");

Open in new window

0
 

Author Comment

by:brucegust
ID: 40411892
I got it to work!

I simply put the delete statement before the forced download dynamic and it worked great!

Thanks for your input!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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…

763 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