richardkurth

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]'";
$sql_result = query($sql);
$count = mysql_num_fields($sql_result);

for ($i = 0; $i < $count; $i++){
$header .= mysql_field_name($sql_result, $i)."\t";
}

while($row = mysql_fetch_row($sql_result)){
$line = '';
foreach($row as $value){
if(!isset($value) || $value == ""){
$value = "\t";
}else{
# important to escape any quotes to preserve them in the data.
$value = str_replace('"', '""', $value);
# needed to encapsulate data in quotes because some data might be multi
line.
# the good news is that numbers remain numbers in Excel even though quoted.
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
}
# this line is needed because returns embedded in the data have "\r"
# and this looks like a "box character" in Excel
$data = str_replace("\r", "", $data);


# Nice to let someone know that the search came up empty.
# Otherwise only the column name headers will be output to Excel.
if ($data == "") {
$data = "\nno matching records found\n";
}

# This line will stream the file to the user rather than spray it across
the screen
header("Content-type: application/octet-stream");

# replace excelfile.xls with whatever you want the filename to default to
header("Content-Disposition: attachment; filename=excelfile.xls");
header("Pragma: no-cache");
header("Expires: 0");

echo $header."\n".$data;

andrew

Hi,
You could always do this within mysql itself. You also have the wrong output
header and what seems to be some quite inefficient code, anyway take a look
at the following.

SELECT order_id,product_name,qty
FROM orders
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

Thanks

Andrew

2008/5/11 Richard Kurth <richardkurth@centurytel.net>:

hans wolters

Into outfile needs specific user rights. Default it's not available.

Hans
--
IM: hans.wolters@xs4all.nl

richardkurth

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]'";
$sql_result = query($sql);
$count = mysql_num_fields($sql_result);

for ($i = 0; $i < $count; $i++){
$header .= mysql_field_name($sql_result, $i)."\t";
}

while($row = mysql_fetch_row($sql_result)){
$line = '';
foreach($row as $value){
if(!isset($value) || $value == ""){
$value = "\t";
}else{
# important to escape any quotes to preserve them in the data.
$value = str_replace('"', '""', $value);
# needed to encapsulate data in quotes because some data might be multi
line.
# the good news is that numbers remain numbers in Excel even though quoted.
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
}
# this line is needed because returns embedded in the data have "\r"
# and this looks like a "box character" in Excel
$data = str_replace("\r", "", $data);


# Nice to let someone know that the search came up empty.
# Otherwise only the column name headers will be output to Excel.
if ($data == "") {
$data = "\nno matching records found\n";
}

# This line will stream the file to the user rather than spray it across
the screen
header("Content-type: application/octet-stream");

# replace excelfile.xls with whatever you want the filename to default to
header("Content-Disposition: attachment; filename=excelfile.xls");
header("Pragma: no-cache");
header("Expires: 0");

echo $header."\n".$data;

dmagick

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.

--
Postgresql & php tutorials
http://www.designmagick.com/

ray.hauge.lists

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
http://www.primateapplications.com

dmagick

With the caveat that you need extra mysql permissions to be able to do that.

--
Postgresql & php tutorials
http://www.designmagick.com/

richardkurth

This is what the $_POST['selectedcontactlist'] looks like

121,17,97,123,243,52,138,114,172,170,64,49,60,256, 176,244,201,42,95,4,

it is not coming across as an array so the foreach is throwing an error

how can I make this an array in the proper format.

This number are selected in a checkbox and passed with a javascript to the script should I be converting them to an array in the javascript.
this is the javascript that is passing the numbers.

function exportContacts(theform)
{
//theform.action = "cmexport.php";

theform.action="cmexport.php";
if (getSelectedContacts(theform) != "")
{
theform.submit();
}
else
{
alert("Please select contacts to export by checking the boxes to the left of the contact's name.");
}
}

dmagick

First question is why do you need to pass it through like that?

I assume it always has a ',' in it if you only choose one box.

if (strpos($_POST['selectedcontactlist'], ',') === false) {
// no boxes were selected - or at least there is no comma.
die();
}

// turn it into an array
$selected_contact_lists = explode(',', $_POST['selectedcontactlist']);

No need to do that either, just make the form variable an array:

<input type="checkbox" name="selectedcontactlist[]" value="X">

The [] turns it into an array which php can then process automatically
as an array.


You can check that some checkboxes are ticked using an idea similar to this:

http://homepage.ntlworld.com/kayseycarvey/jss3p8.html

Though I'd just either set a flag or counter instead of a message when
you find one that is checked.

If you're just checking that any are checked, as soon as you find one,
return true out of the function.

--
Postgresql & php tutorials
http://www.designmagick.com/