What's wrong with this query?

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.
brucegustPHP DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
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
brucegustPHP DeveloperAuthor Commented:
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
Ray PaseurCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Ray PaseurCommented:
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
brucegustPHP DeveloperAuthor Commented:
Awesome! I don't want to head out tonite till I get it right.
0
Ray PaseurCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brucegustPHP DeveloperAuthor Commented:
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
brucegustPHP DeveloperAuthor Commented:
I'm thinking we're poised on the threshold of great things because there were no errors up to that point.
0
Ray PaseurCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.