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.
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.);
}
}
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] creating an xls file from mysql data
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,
First question is why do you need to pass it through like that?
it is not coming across as an array so the foreach is throwing an error
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']);
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.
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/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] creating an xls file from mysql data
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;
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] creating an xls file from mysql data
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.
--
Postgresql php tutorials
http://www.designmagick.com/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] creating an xls file from mysql data
Or you can do it straight from MySQL, which is a lot faster: With the caveat that you need extra mysql permissions to be able to do that. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] creating an xls file from mysql data
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;
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] creating an xls file from mysql data
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 [EMAIL PROTECTED]:
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;
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
