Solved

Why does this delete statement laugh at me?

Posted on 2014-10-24
6
71 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 AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
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 109

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

ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

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

Suggested Solutions

This article discusses four methods for overlaying images in a container on a web page
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

773 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