[RESOLVED] Export Specific MySQL Fields To CSV With Pre-filled Fields

[fnp]

So I have the general idea here. I just need to get the script to write in a specific order, whereas some fields will be pre-filled all the time with the same information. I have 11 tab separated fields, with only 4 of those that will be dynamic from the script, the other 7 are constant.

Here is what I have so far.

<?php

$connection = new mysqli($h['DOST'],$u['DOST'],$p['DOST'],$d['DOST']);
$connection->select_db($d['DOST']);
$sql = "SELECT SubmissionID, ASIN, MinBid, SubxText ";
$sql .="FROM tblSubmissions ";
$sql .="WHERE Ready='YES'";
$result = $connection->query($sql) or die (mysql_error());
$row = mysqli_fetch_array($result);

$SubmissionID=$row[0];
$ASIN=$row[1];
$MinBid=$row[2];
$SubxText=$row[3];

	$num_fields = mysql_num_fields($result);

	for($i = 0; $i < $num_fields; $i++ ){
    	$header .= mysql_field_name($result,$i)."\\t";
}
while($row = mysql_fetch_row($result)){


?>

I am a little confused as to what to do with the while statement.

Any help is appreciated.
Thanks FNP

[bradgrafelman]

fnp;10987703 wrote:

I am a little confused as to what to do with the while statement.

Do whatever you want with each row of data from the DB, of course.

If you're writing some data to a CSV-like file, then simply call [man]fputcsv/man in the loop to write whatever data you'd like.

[fnp]

So Basically something like this should work.

<?php

$connection = new mysqli($h['DOST'],$u['DOST'],$p['DOST'],$d['DOST']);
$connection->select_db($d['DOST']);
$sql = "SELECT SubmissionID, ASIN, MinBid, SubxText, ItemCondition ";
$sql .="FROM tblSubmissions ";
$sql .="WHERE Ready='YES'";
$result = $connection->query($sql) or die (mysql_error());
$row = mysqli_fetch_array($result);

$SubmissionID=$row[0];
$ASIN=$row[1];
$MinBid=$row[2];
$SubxText=$row[3];
$ItemCondition=$row[4];

	$num_fields = mysql_num_fields($result);

	for($i = 0; $i < $num_fields; $i++ ){
    	$header .= mysql_field_name($result,$i)."\\t";
}
while($row = mysql_fetch_row($result)){
    	$list = array (array($SubmissionID, $ASIN, '1', $MinBid, $ItemCondition, '1', 'a', '1', '', $SubxText, '' ));
	$fp = fopen('/uploads/AMZListings.csv', 'w');

	foreach ($list as $fields) {
			fputcsv($fp, $fields);
	}

	fclose($fp);

?>

Thanks

[bradgrafelman]

Few problems I see:

1. In the absence of any 'ORDER BY' clause, results should be considered unordered (that is, you shouldn't assume that the server will return the result set in the same order every time, or even in the order that the rows were INSERT'ed). This code:

       $sql = "SELECT SubmissionID, ASIN, MinBid, SubxText, ItemCondition "; 
       $sql .="FROM tblSubmissions "; 
       $sql .="WHERE Ready='YES'"; 
       $result = $connection->query($sql) or die (mysql_error()); 
       $row = mysqli_fetch_array($result); 
   
   $SubmissionID=$row[0]; 
   $ASIN=$row[1]; 
   $MinBid=$row[2]; 
   $SubxText=$row[3]; 
   $ItemCondition=$row[4]; 

   seems to suggest, however, that you are expecting the first row to always be a certain row. Is this true?

2.         $num_fields = mysql_num_fields($result); 
   
       for($i = 0; $i < $num_fields; $i++ ){ 
           $header .= mysql_field_name($result,$i)."\\t"; 
      } 

   Why are you writing a literal backslash and then the letter 't' rather than a tab character?

   Also, why are you even retrieving the column names here? Didn't you already specify which columns you want data from in the SELECT query? Since the column names are already known, this section is a bit redundant.

3. $list = array (array($SubmissionID, $ASIN, '1', $MinBid, $ItemCondition, '1', 'a', '1', '', $SubxText, '' )); 

   Why are you creating an array of arrays? (Hint: The answer is that you shouldn't be.)

4. Why are you calling [man]fopen/man (and, subsequently, fclose()) inside of the while loop? That means you're going to be constantly opening/closing a resource pointer to the same file.

5. This:

           foreach ($list as $fields) { 
                   fputcsv($fp, $fields); 
           } 
   

   has numerous problems.

   Get rid of the foreach() loop entirely, and read the manual page for [man]fputcsv/man to learn what parameters it expects.

[johanafm]

Another idea would be to use SELECT ... INTO OUTFILE

SELECT SubmissionID, ASIN, '1', MinBid, ItemCondition, '1', 'a', '1', SubxText, ''
FROM ... WHERE ... ORDER BY ...
INTO OUTFILE stuff.csv

It will of course write to the DB server's disk, unless you have a disk somewhere else which is mapped on the servers file system.
You could also do this from another machine if you have mysql client installed

mysql -e "SELECT ... [do not specify INTO OUTFILE, you use redirection]" > outfile.csv"

[fnp]

Thanks for the advice.

I have got what seems to be working, the only problem now is that I can not open the file with an office or text editor program. I am still looking into the headers. Here is what I have so far.

<?php
	if(isset($_POST['submit'])){
		$connection = new mysqli($h['DOST'],$u['DOST'],$p['DOST'],$d['DOST']);
		$connection->select_db($d['DOST']);
		$sql = "SELECT SubmissionID, ASIN, MinBid, SubxText, ItemCondition ";
		$sql .="FROM tblSubmissions ";
		$sql .="WHERE Ready='YES'";
		$result = $connection->query($sql) or die (mysql_error());
		$row=$result->fetch_row();

	$SubmissionID=$row[0];
	$ASIN=$row[1];
	$MinBid=$row[2];
	$SubxText=$row[3];
	$ItemCondition=$row[4];

		//$num_fields = mysql_num_fields($result);

		//for($i = 0; $i < $num_fields; $i++ ){
	//}
	if($result->num_rows>0){
		$fp = fopen('uploads/AMZListings.csv', 'w');
		while($row=$result->fetch_assoc()){
    			$list = array($SubmissionID, $ASIN, '1', $MinBid, $ItemCondition, '1', 'a', '1', '', $SubxText, '' );
			fputcsv($fp, $list, '/t');
		}
		fclose($fp);		
		echo "Export done";
	}
	else{
		echo "Nothing to Export";
	}
}
else{
 		print "<form action='thisfile.php' method='post'>";
 		print "Just click Submit<br>";
	print "<input type='submit' name='submit' value='submit'></form>";
}

?>

Thanks
Any help is appreciated
FNP

[bradgrafelman]

One problem I see is this:

fputcsv($fp, $list, '/t'); 

Here, you're using a forward slash followed by the letter 't' as a field separator.

See the manual page [man]string[/man] to learn how to represent a tab character in a PHP string. Note that you'll want to look at the 'Double quoted' string section (since the backslash escape sequences aren't interpolated in strings delimited with single quotes).

[fnp]

Alright now I can't seem to get it to enter the while loop. The one time I did it ended up with a continues loop that ran for about 20 seconds and inserted over 720,000 entries into the table. oops. I don't understand I have used almost the same statement elsewhere and it works fine.

<?php
	if(isset($_POST['submit'])){
		$EmployeeID=$_SESSION['eid'];
		$connection = new mysqli($h['DOST'],$u['DOST'],$p['DOST'],$d['DOST']);
		$connection->select_db($d['DOST']);
		$sql = "SELECT SubmissionID, ASIN, MinBid, SubxText, ItemCondition, SubxTitle ";
		$sql .="FROM tblSubmissions ";
		$sql .="WHERE Ready='YES'";
		$result = $connection->query($sql) or die (mysql_error());
		$row=$result->fetch_row();

	$SubmissionID=$row[0];
	$ASIN=$row[1];
	$MinBid=$row[2];
	$SubxText=$row[3];
	$ItemCondition=$row[4];
	$SubxTitle=$row[5];

	if($result->num_rows>0){
		$fp = fopen('uploads/AMZListings.csv', 'w');
		while($rowSub=$result->fetch_assoc()){
    			$list = array($SubmissionID, $ASIN, '1', $MinBid, $ItemCondition, '1', 'a', '1', '', $SubxText, '' );
			fputcsv($fp, $list, "\t");
			$Aucsql = "INSERT INTO tblListings";
			$Aucsql .= "(SubmissionID, AuctionSiteID, AuctionTitle, MinBidPrice, DateStarted, AuctionNumber, LastModifiedBy, LastModifiedTS, Quantity) ";
			$Aucsql .= "VALUES('$SubmissionID', 'AMZ', '$SubxTitle', '$MinBid', NOW(), '$SubmissionID', '$EmployeeID', NOW(), '1')";
			$connection->query($Aucsql);
			$SubUpdate = "UPDATE tblSubmissions set Ready=' ' WHERE SubmissionID='$SubmissionID'";
			$connection->query($SubUpdate);
		}
		fclose($fp);		
		echo "Export done";
	}
	else{
		echo "Nothing to Export";
	}
	$connection->close();
}
else{
 		print "<form action='thispage.php' method='post'>";
 		print "Just click Submit<br>";
	print "<input type='submit' name='submit' value='submit'></form>";
}

?>

This is slightly different now, I had to add an update a couple tables so it will automatically remove the entry for the next time this script is ran.

Thanks
FNP

[fnp]

Ok now the for (while) loop works, however now I have to fix the format of the output csv file. All the information is in there it just in not tab delimiter.

<?php
	if(isset($_POST['submit'])){
		$EmployeeID=$_SESSION['eid'];
		$connection = new mysqli($h['DOST'],$u['DOST'],$p['DOST'],$d['DOST']);
		$connection->select_db($d['DOST']);
		$sql = "SELECT SubmissionID, ASIN, MinBid, SubxText, ItemCondition, SubxTitle ";
		$sql .="FROM tblSubmissions ";
		$sql .="WHERE Ready='YES'";
		$result = $connection->query($sql) or die (mysql_error());
		$row=$result->fetch_row();

	$SubmissionID=$row[0];
	$ASIN=$row[1];
	$MinBid=$row[2];
	$SubxText=$row[3];
	$ItemCondition=$row[4];
	$SubxTitle=$row[5];

	if($result->num_rows>0){
		$fp = fopen('uploads/AMZListings.csv', 'w');
		for($i = 0; $i < $result->num_rows; $i++){
    			$list = array($SubmissionID, $ASIN, '1', $MinBid, $ItemCondition, '1', 'a', '1', '', $SubxText, '' );
			fputcsv($fp, $list, "\t");
			$Aucsql = "INSERT INTO tblListings";
			$Aucsql .= "(SubmissionID, AuctionSiteID, AuctionTitle, MinBidPrice, DateStarted, AuctionNumber, LastModifiedBy, LastModifiedTS, Quantity) ";
			$Aucsql .= "VALUES('$SubmissionID', 'AMZ', '$SubxTitle', '$MinBid', NOW(), '$SubmissionID', '$EmployeeID', NOW(), '1')";
			$connection->query($Aucsql);
			$SubUpdate = "UPDATE tblSubmissions set Ready=' ' WHERE SubmissionID='$SubmissionID'";
			$connection->query($SubUpdate);
		}
		fclose($fp);		
		echo "Export done";
	}
	else{
		echo "Nothing to Export";
	}
	$connection->close();
}
else{
 		print "<form action='csvamzdownload.php' method='post'>";
 		print "Just click Submit<br>";
	print "<input type='submit' name='submit' value='submit'></form>";
}

?>

Any ideas are helpful.
Thanks
FNP

[bradgrafelman]

If it isn't delimited by tabs, then what is it delimited by?

[fnp]

I fought this thing until late last night and the script has now changed to this:

<?php
	if(isset($_POST['submit'])){
		$EmployeeID=$_SESSION['eid'];
		$connection = new mysqli($h['DOST'],$u['DOST'],$p['DOST'],$d['DOST']);
		$connection->select_db($d['DOST']);
		$fp = fopen('uploads/AMZListings.csv', 'wt');
		$filename = "AMZListings.csv";
		$sql = "SELECT SubmissionID, ASIN, MinBid, SubxText, ItemCondition, SubxTitle ";
		$sql .="FROM tblSubmissions ";
		$sql .="WHERE Ready='YES'";
		$result = $connection->query($sql) or die (mysql_error());
		$SUB_Count=$result->num_rows;
		if($SUB_Count>0){
			$i=0;
			while($row=$result->fetch_row()){
				$SubmissionID[$i]=$row[0];
				$ASIN[$i]=$row[1];
				$MinBid[$i]=$row[2];
				$SubxText[$i]=$row[3];
				$ItemCondition[$i]=$row[4];
				$SubxTitle[$i]=$row[5];
				$list[$i] = array($SubmissionID[$i],$ASIN[$i],'1',$MinBid[$i],$ItemCondition[$i],'1','a','1','',$SubxText[$i],'');

			$i++;
		}
		$result->free();
		foreach($list as $newlist){
			fputcsv($fp, $newlist, "\t");
		}

		for($i=0; $i<=$SUB_Count-1; $i++){
		//if($result->num_rows>0){


			//$Aucsql = "INSERT INTO tblListings";
			//$Aucsql .= "(SubmissionID, AuctionSiteID, AuctionTitle, MinBidPrice, DateStarted, DateEnded, AuctionNumber, LastModifiedBy, LastModifiedTS, Quantity) ";
			//$Aucsql .= "VALUES('$SubmissionID[$i]', 'AMZ', '$SubxTitle[$i]', '$MinBid[$i]', NOW(), '0000-00-00', '$SubmissionID[$i]', '$EmployeeID', NOW(), '1')";
			//$connection->query($Aucsql);
			//$SubUpdate = "UPDATE tblSubmissions set Ready=' ' WHERE SubmissionID='$SubmissionID[$i]'";
			//$connection->query($SubUpdate);
		}
		//}

		echo "Export done";
	}
	else{
		echo "Nothing to Export";
	}

	fclose($fp);
	$connection->close();
}
else{
 		print "<form action='csvamzdownload.php' method='post'>";
 		print "Just click Submit<br>";
	print "<input type='submit' name='submit' value='submit'></form>";
}

?>

I set this up for testing purposes, hence the reason why the lower half is commented out.

What this gives me is a file that has all the information that is needed, I can open the file with a text editor or a browser and it outputs what looks to be a tab delimited. However when I open it with an calc/excel program I get the first 5 results in the first column and the last 2 are two rows down, there is also only 1 row from the query that got returned.

When I change the script to look like this:

while($row=$result->fetch_row()){
				$SubmissionID[$i]=$row[0];
				$ASIN[$i]=$row[1];
				$MinBid[$i]=$row[2];
				$SubxText[$i]=$row[3];
				$ItemCondition[$i]=$row[4];
				$SubxTitle[$i]=$row[5];
				$list = array();
				$list[$i] = array($SubmissionID[$i],$ASIN[$i],'1',$MinBid[$i],$ItemCondition[$i],'1','a','1','',$SubxText[$i],'');

			$i++;
		}
		$result->free();
		foreach($list as $newlist){
				fputcsv($fp, $newlist, "\t");
			}

I get one row of the query returned however it is right and I can open it with a calc/excel program.

There is something that I am missing I just can't seem to see it.
Any help is appreciated.
Thanks
FNP

[fnp]

I finally figured out what my problem was. The above code works fine. I have HTML tags (<p>) in the '$SubxText' variable which caused the file not to open properly in a calc/excel program.

Man I feel dumb, but you win some and you lose some.

Anyway here is the finished code. End lesson is not to use HTML tags in fputcsv.

<?php
	if(isset($_POST['submit'])){
		$EmployeeID=$_SESSION['eid'];
		$connection = new mysqli($h['DOST'],$u['DOST'],$p['DOST'],$d['DOST']);
		$connection->select_db($d['DOST']);
		$fp = fopen('uploads/AMZListings.csv', 'wt');
		$filename = "AMZListings.csv";
		$sql = "SELECT SubmissionID, ASIN, MinBid, SubxText, ItemCondition, SubxTitle ";
		$sql .="FROM tblSubmissions ";
		$sql .="WHERE Ready='YES'";
		$result = $connection->query($sql) or die (mysql_error());
		$SUB_Count=$result->num_rows;
		if($SUB_Count>0){
			$i=0;
			while($row=$result->fetch_row()){
				$SubmissionID[$i]=$row[0];
				$ASIN[$i]=$row[1];
				$MinBid[$i]=$row[2];
				$SubxText[$i]=$row[3];
				$ItemCondition[$i]=$row[4];
				$SubxTitle[$i]=$row[5];
				$list[$i] = array($SubmissionID[$i],$ASIN[$i],'1',$MinBid[$i],$ItemCondition[$i],'1','a','1','',$SubxText[$i],'');
				$i++;
			}
			$result->free();

		foreach($list as $value){
			fputcsv($fp, $value, "\t");
		}

		for($i=0; $i<=$SUB_Count-1; $i++){
			//echo $list[$i];
			//fwrite($fp, $SubmissionID[$i].'\t'.$ASIN[$i].'\t''1''\t'.$MinBid[$i].'\t.$ItemCondition[$i]."\t"'1'"\t"'a'"\t"'1'"\t"''"\t".$SubxText[$i]."\t"''."\n");
			//$Aucsql = "INSERT INTO tblListings";
			//$Aucsql .= "(SubmissionID, AuctionSiteID, AuctionTitle, MinBidPrice, DateStarted, DateEnded, AuctionNumber, LastModifiedBy, LastModifiedTS, Quantity) ";
			//$Aucsql .= "VALUES('$SubmissionID[$i]', 'AMZ', '$SubxTitle[$i]', '$MinBid[$i]', NOW(), '0000-00-00', '$SubmissionID[$i]', '$EmployeeID', NOW(), '1')";
			//$connection->query($Aucsql);
			//$SubUpdate = "UPDATE tblSubmissions set Ready=' ' WHERE SubmissionID='$SubmissionID[$i]'";
			//$connection->query($SubUpdate);
		}

		echo "Export done";
	}
	else{
		echo "Nothing to Export";
	}

	fclose($fp);
	$connection->close();
}
else{
 		print "<form action='filename.php' method='post'>";
 		print "Just click Submit<br>";
	print "<input type='submit' name='submit' value='submit'></form>";
}

?>