?
Solved

Why does this delete statement laugh at me?

Posted on 2014-10-24
6
Medium Priority
?
76 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
WordPress Tutorial 4: Recommended Plugins

Now that you have WordPress installed, understand the interface, and know how to install new parts, let’s take a look at our recommended plugins.

 
LVL 58

Assisted Solution

by:Gary
Gary earned 1000 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 111

Accepted Solution

by:
Ray Paseur earned 1000 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

Tutorial: Introduction to Managing a Linux Server

In this tutorial on systemd, we will explore:
-OS/Distro Adoption
-chkconfig and Other Legacy Commands
-Summary and Key Commands

Question has a verified solution.

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

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
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.
Suggested Courses

765 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