Re: [PHP] creating an xls file from mysql data

Chris wrote:
> Richard Kurth wrote:
>> This script will create an xls file from the data that is sent to it
>> When I run this it only gets one recored and it is supposet to get all
>> the records that are past by the  $_POST[selectedcontactlist]
>> I think I have a } in the wrong place but I can not figure it out
>> anybody have a suggestion
>>
>> $_POST[selectedcontactlist]="3,45,65,23,12,4,56"; //this is a sample of
>> what is past
>>
>> $ExplodeIt = explode(",",rtrim($_POST[selectedcontactlist],","));
>> $Count = count($ExplodeIt);
>> for ($i=0; $i < $Count; $i++) {
>> $sql = "SELECT * FROM contacts WHERE id = '$ExplodeIt[$i]'";
> 
> Instead of doing that, do this:
> 
> /**
> * This section makes sure the id's you are going to use in your query
> are actually integer id's.
> * If they aren't, you'll get an sql error.
> *
> */
> $ids = array();
> foreach ($_POST['selectedcontactlist'] as $id) {
>   if (!is_int($id)) {
>     continue;
>   }
>   $ids[] = $id;
> }
> 
> // all posted values are duds? show an error.
> if (empty($ids)) {
>   echo "No id's are numeric, try again";
>   exit;
> }
> 
> $sql = "select * from contacts where id in (" . implode(',', $ids) . ")";
> 
> 
> That'll get everything for all of those id's and then you can loop over
> it all once:
> 
> // print out the header for the csv file here.
> 
> // then loop over the results:
> while ($row = mysql_fetch_assoc($sql_result)) {
>   // put it into file here.
> }
> 
> // close the file
> // print it out.
> 

Or you can do it straight from MySQL, which is a lot faster:

SELECT [fields] INTO OUTFILE '/path/to/file.csv' FIELDS TERMINATED BY 
',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM [tables] 
WHERE [conditions]

If you want a header row, then you can use a UNION statement.

-- 
Ray Hauge
www.primateapplications.com