Solved

Why does this delete statement laugh at me?

Posted on 2014-10-24
6
67 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
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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 108

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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.  …
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
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.

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now