Solved

What's wrong with this query?

Posted on 2014-10-29
10
80 Views
Last Modified: 2014-10-30
This isn't my code. I say that because there's things I would change right out of the box, but still, it's sound with the exception of the things I've tried to add and I'm blowing it somewhere.

So, here's what I've got:

     
<?php


	if(!empty($_GET['query1']))  
	{
		$query1 = $_GET['query1'];
	//echo $query1;
	}
	
	 $michelle = mysql_query("SELECT PARTCODE, i2.CAPITAL_DESC, i1.STATUS_DESC, sum(QTY) AS QTY, CATS_FLAG, END_OF_LIFE FROM inventory.installbase AS i1 INNER JOIN inventory.installbase2 as i2 ON i1.STATUS_DESC = i2.STATUS_DESC WHERE PARTCODE = '$query1' GROUP BY PARTCODE, STATUS_DESC ORDER BY CAPITAL_DESC, STATUS_DESC;") or die ("select error".mysql_error()); 
	//$bruce="SELECT PARTCODE, i2.CAPITAL_DESC, i1.STATUS_DESC, sum(QTY) AS QTY, CATS_FLAG, END_OF_LIFE FROM inventory.installbase AS i1 INNER JOIN inventory.installbase2 as i2 ON i1.STATUS_DESC = i2.STATUS_DESC WHERE PARTCODE = '$query1' GROUP BY PARTCODE, STATUS_DESC ORDER BY CAPITAL_DESC, STATUS_DESC";
	//echo $bruce;
	
	$michelle_count = mysql_num_rows($michelle); 
	//echo $michelle_count; I'VE GOT 13 ROWS OF DATA, SO I KNOW I'M GOOD HERE
	if($michelle_count>0)
	{
	$heading=array('PARTCODE', 'CAPITAL_DESC', 'STATUS_DESC', 'QTY', 'CATS_FLAG', 'END_OF_LIFE';);
		foreach ($heading as $val)
		{
			$output .=''.$val.',';
		}
		$output .="\n";
	
		while($michelle_row=mysql_fetch_array($michelle))
		{
			for($i=0; $i<6; $i++)
			{
				$output .=''.str_replace(array(",", "\n", "\r","\r\n", "\n\r"), "&#44", $michelle_row["$i"]).',';
			}
		$output .="\n";
		}
	}	

$filename="embedded.csv";
header('Content-type:application/csv');
header('Content-Disposition: attachment; filename='.$filename);
echo $output;

Open in new window


Bottom line: No errors, but no csv file. What am I missing?

Note: Code moved into Code snippet.
0
Comment
Question by:brucegust
  • 5
  • 4
10 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40411863
What is the data in $_GET['query1'] ?  What if that is empty?

Maybe the query found no results?

Please add error_reporting(E_ALL) to the top of the script so you can be sure you're not relying on an undefined variable.

You might want to print out the fully resolved query string.  

You might want to learn about this function before the database is attacked.
http://php.net/manual/en/function.mysql-real-escape-string.php

This appears to be wrong in that it will leave a trailing comma on the end of the data set.
		foreach ($heading as $val)
		{
			$output .=''.$val.',';
		}
		$output .="\n";

Open in new window


It might be good to use the built-in fputcsv() function instead of trying to roll-your-own CSV.  This will give you the advantage that you will have a data set you can look at, and you won't have to guess what happened - it will be there in the data!
http://php.net/manual/en/function.fputcsv.php
0
 

Author Comment

by:brucegust
ID: 40411889
Hey, Ray!

I've got data. Prior to posting my question, I printed the query as well as $query1. $query1 is showing up correctly, the select statement is sound and I was able to print the number of rows, which is 13.

So I've got data and while the fputcsv thing is a great suggestion, I was hoping to use what I had only because I know that it works and I've got the column headings set up etc.

My concern is the fact that I'm using msyql as opposed to mysqli. When I first looked at this code I recognized it as older syntax and the fact that I was plugging in my "create csv" stuff made me wonder if I wasn't mixing apples and oranges.

In any event, the problem seems to be between line 18 and 33. For example, is:

while($michelle_row=mysql_fetch_array($michelle))

...is that sound? I was using mysqli_fetch_array, now I'm tweaking it to be mysql_fetch_array. Is that a potential problem.

What do you think?
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40411905
Also, this might cause a PHP parse error:
	$heading=array('PARTCODE', 'CAPITAL_DESC', 'STATUS_DESC', 'QTY', 'CATS_FLAG', 'END_OF_LIFE';);

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40411908
I think there may be some more useful ways to look at this, and I'll try to give you a code snippet in a moment.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:brucegust
ID: 40411920
Awesome! I don't want to head out tonite till I get it right.
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 40411956
Obviously this is untested but it seems a more certain way of getting the data right or finding out what's wrong.
<?php // demo/temp_brucegust.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);

/**
 * ASSUME THAT WE ARE CONNECTED TO THE DATABASE SERVER
 * ASSUME THAT WE HAVE SELECTED THE DATABASE
 */


// ESCAPE THE REQUEST VARIABLE
$query1 =(!empty($_GET['query1'])) ? mysql_real_escape_string($_GET['query1']) : NULL;

// IF THE REQUEST VARIABLE IS MISSING, THAT IS AN ERROR
if (!$query1) trigger_error("MISSING URL PARAMETER query1=", E_USER_ERROR);

// CONSTRUCT THE QUERY IN ITS OWN VARIABLE SO IT CAN BE VISUALIZED
$sql = 
"
SELECT
  PARTCODE
, i2.CAPITAL_DESC
, i1.STATUS_DESC
, sum(QTY) AS QTY
, CATS_FLAG
, END_OF_LIFE
FROM
  inventory.installbase AS i1
INNER JOIN 
  inventory.installbase2 AS i2 ON i1.STATUS_DESC = i2.STATUS_DESC
WHERE PARTCODE = '$query1'
GROUP BY
  PARTCODE
, STATUS_DESC
ORDER BY
  CAPITAL_DESC
, STATUS_DESC
"
;

// RUN THE QUERY, TEST FOR SUCCESS AND VISUALIZE ANY ERRORS
$res = mysql_query($sql);
if (!$res)
{
    echo PHP_EOL . $sql;
    echo PHP_EOL . mysql_error();
    trigger_error("QUERY FAILURE", E_USER_ERROR);
}

// OMIT THIS PART AFTER THE SCRIPT IS DEBUGGED
$num = mysql_num_rows($res);
echo PHP_EOL . "FOUND $num ROWS";

// THE LOCATION OF THE TEMPORARY CSV FILE
$url = 'temp.csv';

// OPEN THE TEMPORARY FILE AND TEST FOR SUCCESS OR FAIL ON ERROR
$fpw = fopen($url, 'wb');
if (!$fpw) trigger_error("UNABLE TO OPEN $url FOR WRITE", E_USER_ERROR);

// WRITE AN ARRAY OF HEADERS INTO THE CSV FILE
$heading = array('PARTCODE', 'CAPITAL_DESC', 'STATUS_DESC', 'QTY', 'CATS_FLAG', 'END_OF_LIFE');
if (!fputcsv($fpw, $heading)) trigger_error("UNABLE TO WRITE TO $url", E_USER_ERROR);

// FETCH THE ROWS AND WRITE THE ARRAYS OF DATA INTO THE CSV FILE
while($row = mysql_fetch_assoc($res))
{
    if (!fputcsv($fpw, $row)) trigger_error("UNABLE TO WRITE TO $url", E_USER_ERROR);
}
fclose($fpw);


/**
 * AT THIS POINT STOP WORK AND INSPECT THE temp.csv FILE
 * THEN WHEN YOU'VE GOT THE CSV YOU WANT, GO BACK INTO THIS SCRIPT
 * AND REMOVE THE ECHO STATEMENTS ABOVE, THEN ADD THESE STATEMENTS BACK
$filename="embedded.csv";
header('Content-type:application/csv');
header('Content-Disposition: attachment; filename='.$filename);
readfile($url);
unlink($url);
 */

Open in new window

0
 

Author Comment

by:brucegust
ID: 40411998
Ray!

Here's what I got:

FOUND 13 ROWS Warning: fopen(temp.csv): failed to open stream: Permission denied in /var/www/html/embedded/embedded_csv.php on line 82 Fatal error: UNABLE TO OPEN temp.csv FOR WRITE in /var/www/html/embedded/embedded_csv.php on line 83

Let me ask you this: When I use fputcsv, is it assumed that I have a file named "temp.csv" sitting on the server? Is that why I'm getting this error?
0
 

Author Comment

by:brucegust
ID: 40412003
I'm thinking we're poised on the threshold of great things because there were no errors up to that point.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40412072
With fputcsv(), there is a presumption that temp.csv exists.  It would have been created by fopen().  See what the fopen() man page says about non-existent files.  So at this point all you need to do is make the directory writable or find a writable directory and create the temporary file there.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
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…
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.

760 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

21 Experts available now in Live!

Get 1:1 Help Now