Solved

What's wrong with this query?

Posted on 2014-10-29
10
82 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
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.

 

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

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

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.  …
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 dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

896 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

11 Experts available now in Live!

Get 1:1 Help Now