Query results to text file

melomonk

Hello - My brain has 'mushified' and I'm stuck

I'm trying to two things
#1 - take query results and output to an XML file (working fine)
#2 - take the same results and write them to a text file (not so good)

I can write a single cell to the file - but not the whole "returned set" of data

I was hoping a new set of eyes could point me in the right direction

How do I write all of the data returned from the query, to the text file??

THANK YOU for your time in looking, I do appreciate it :o

Here is the code I'm using
::::::::::::::::::::::::::::::
//for the output
header("Content-type: text/xml");

//to create connection to database
$connection = mysql_connect("localhost","USERNAME","PASSWORD")
or die ("could not connect to database");

//select the database here
$db = mysql_select_db("DATABASE",$connection)
or die ("Couldn't select database.");

$rs = mysql_query("select tour.tourid, tour.mls, tour.userid from tour",$connection)
or die ("invalid query");

//count the no. of columns in the table
$fcount = mysql_num_fields($rs);

//name for the starting XML tag
echo ("<item>");
while($row = mysql_fetch_array( $rs ) )
{
echo ("<coldwell>");
for($i=0; $i< $fcount; $i++)
{
$tag = mysql_field_name( $rs, $i );
echo ("<$tag>". $row[$i]. "</$tag>");
}
echo ("</coldwell>");
}
echo ("</item>");

// query results to be written to file
:glare:$results = mysql_result($rs, 2);:glare:

// open or create file to write to
$fp = fopen("static.txt", "w");
// write query results to the static.txt file
fwrite($fp, $results);
fclose($fp);

paulnaj

Hi,

First, what exactly are you trying to output to the text file? This ...

$results = mysql_result($rs, 2);

... looks like you are trying to get the 'tourid' field form the third row from the result set, i.e. it's equivalent to ...

$results = mysql_result($rs, 2, 0);

Is that right?

Secondly, have you tried outputting a test string to a text file on the server? You might want to ensure that you can do this, getting past file perms etc.

melomonk

Yes - I have tested a string and it is writing to the file - no problems there

What I'm trying to write to the file - is ALL of the results of the query
(ultimately in a delimited format)
But right now - I'm just trying to write all of the results of the query to the text file

Thanks again for your time

paulnaj

Try this ...

<?php
//for the output
header("Content-type: text/xml");

//to create connection to database
$connection = mysql_connect("localhost","USERNAME","PASSWORD") or die ("could not connect to database");

//select the database here
$db = mysql_select_db("DATABASE",$connection) or die ("Couldn't select database.");

$rs = mysql_query("select `tour`.`tourid`, `tour`.`mls`, `tour`.`userid` from tour",$connection) or die ("invalid query");

//count the no. of columns in the table
$fcount = mysql_num_fields($rs);

// open or create file to write to
$fp = fopen("static.txt", "w");

//name for the starting XML tag
echo ("<item>");

// CHANGE TO mysql_fetch_row, rather than mysql_fetch_array
// This allows you to join the '$row' together in one line
while($row = mysql_fetch_row($rs)){
	echo ("<coldwell>");
	for($i = 0; $i < $fcount; $i++){
		$tag = mysql_field_name($rs, $i);
		echo ('<'.$tag.'>'. $row[$i]. '</'.$tag.'>');
	}
	echo ("</coldwell>");
	// write comma-separated query results to the static.txt file
	fwrite($fp, join(',', $row);
}
echo ("</item>");
// close file pointer
fclose($fp);
?>

Key points ...
1. Output to the file on a row-by-row basis
2. Use mysql_fetch_row rather than mysql_fetch_array - mysql_fetch_array gives you indexed results AND named results in the same object, whilst mysql_fetch_row gives only indexed results - means you can string the values together in a join()

Hope that helps.
P.

melomonk

This helps out a great deal
and I understand the change to fetch_row

When I apply the update
I get and error:
Parse error: syntax error, unexpected ';' in /home/prore5/public_html/test/newfile2.php on line 36

I've check over the syntax carefully and cannot see what the error message is reffering to --- When I look on line 36 - syntax is correct, to me

Code WITH your update
::::::::::::::::::::::::::::::::
<?php

//for the output
header("Content-type: text/xml");

//to create connection to database
$connection = mysql_connect("localhost","USER","PASSWORD")
or die ("could not connect to database");

//select the database here
$db = mysql_select_db("DATABASE",$connection)
or die ("Couldn't select database.");

$rs = mysql_query("select tour.tourid, tour.mls, tour.userid from tour",$connection)
or die ("invalid query");

//count the no. of columns in the table
$fcount = mysql_num_fields($rs);

// open or create file to write to
$fp = fopen("static.txt", "w");

//name for the starting XML tag
echo ("<item>");

// CHANGE TO mysql_fetch_row, rather than mysql_fetch_array
// This allows you to join the '$row' together in one line
while($row = mysql_fetch_row($rs)){
echo ("<coldwell>");
for($i = 0; $i < $fcount; $i++){
$tag = mysql_field_name($rs, $i);
echo ('<'.$tag.'>'. $row[$i]. '</'.$tag.'>');
}
echo ("</coldwell>");
// write comma-separated query results to the static.txt file
fwrite($fp, join(',', $row);
}
echo ("</item>");
// close file pointer
fclose($fp);
?>

paulnaj

Hi again,

Looks like I missed a closing parens here ...

fwrite($fp, join(',', $row);

... should be ...

fwrite($fp, join(',', $row));

Paul.

PS. use the text editor PHP tags to colour up your code on the forum - helps to see what's going on.

melomonk

Senior Member paulnaj

Thank You so much for taking the time to help me with this

I've learned a couple of things for sure

Thank you again for your help

juma929

Hello,

You can also accomplish the same task with mysql_fetch_array() if you wanted to stick with that function by using mysql_fetch_array($results, MYSQL_NUM).

I know mysql_fetch_row() can be a tad quicker but for other people reading this thread they might want the flexibility of being able to decide which you can do in the fetch array version.

MYSQL_BOTH (Default if not supplied)
MYSQL_NUM
MYSQL_ASSOC

🙂

Thanks,
Justin