Solved

Why does this delete statement laugh at me?

Posted on 2014-10-24
6
69 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
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 look for a specific file type in a local or remote server directory using PHP.

863 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

26 Experts available now in Live!

Get 1:1 Help Now