if statement

[confused_aswell]

Hi

 

If my database holds a telephone number that is all numbers like this 01234567890 or 07768743453 the excel file what will display that number will strip the 0 off the front of the number. So my question is can you write a if statement so that if the number in the database is all one long line of numbers, you can add a space after the first 5 numbers to stop the excel file stripping the 0 from the beginning?

 

I think an if statement would do this, but if you know better then please let me know.

 

Thanks,

 

Phil

 

Here is the script that it has to work with -

 

<?php
$host = 'localhost';
$user = 'username';
$pass = 'password';
$db = 'database_name';
$table = 'zen_address_book,zen_customers';
$file = 'export';


$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
mysql_select_db($db) or die("Can not connect.");

$replace = array(
'address_book_id' => '', 
'entry_company' => 'Customer Name',
   'entry_firstname' => 'First Name', 
   'entry_lastname' => 'Last Name',
   'entry_street_address' => 'Billing Address1',
   'entry_suburb' => 'Billing Address2',
   'entry_city' => 'Billing Address3',
   'entry_state' => 'Billing Address4',
   'entry_postcode' => 'Billing Address5',
   'customers_email_address' => 'Email',
   'customers_telephone' => 'Phone'
); 

$values = mysql_query("SELECT
a.entry_company, a.entry_firstname, a.entry_lastname, a.entry_street_address,
a.entry_suburb, a.entry_city, a.entry_postcode, c.customers_email_address, c.customers_telephone
FROM
zen_address_book as a
LEFT JOIN
zen_customers as c
ON
c.customers_id = a.customers_id 


");

$i=0;
while ($rowr = mysql_fetch_assoc($values)) {
if($i==0) {
foreach(array_keys($rowr) as $title)
$csv_output .= '"'.str_replace(array_keys($replace), $replace, $title).'",';
$csv_output .= "\n";	
}

foreach ($rowr as $key => $value) {
$csv_output .= '"'.$value.'",'; 

}
$csv_output .= "\n";
$i++;
}

$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");
print $csv_output;
exit;

?>

[jonsjava]

<?php
function split_num($num){
$num = ereg_replace("[^0-9]",'',$num);
$s1 = substr($num,0,5);
$s2 = substr($num,5,11);
$num = "$s1 $s2";
return($num);
}
print split_num("0123456789");
?>  

[DarkWater]

@jonsjava: Don't use short tags and don't use ereg()...

[jonsjava]

short tag was a typo. ereg was because I ripped the script from someone else, and modded it to suit his needs. It worked. I'm not a regular expression writer.

[jonsjava]

here:

<?php
function split_num($num){
$num = preg_replace("[^0-9]",'',$num);
$s1 = substr($num,0,5);
$s2 = substr($num,5,11);
$num = "$s1 $s2";
return($num);
}
print split_num("0123456789");
?>

[confused_aswell]

Hi

 

It seems to be throwing up errors, is there a place in the script that I should be putting this or does it go anywhere?

 

Thanks,

 

Phil

[jonsjava]

I think this will work (no way to tell):

<?php
$host = 'localhost';
$user = 'username';
$pass = 'password';
$db = 'database_name';
$table = 'zen_address_book,zen_customers';
$file = 'export';


$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
mysql_select_db($db) or die("Can not connect.");

$replace = array(
'address_book_id' => '',
'entry_company' => 'Customer Name',
'entry_firstname' => 'First Name',
'entry_lastname' => 'Last Name',
'entry_street_address' => 'Billing Address1',
'entry_suburb' => 'Billing Address2',
'entry_city' => 'Billing Address3',
'entry_state' => 'Billing Address4',
'entry_postcode' => 'Billing Address5',
'customers_email_address' => 'Email',
'customers_telephone' => 'Phone'
);
function split_num($num){
$num = preg_replace("[^0-9]",'',$num);
$s1 = substr($num,0,5);
$s2 = substr($num,5,11);
$num = "$s1 $s2";
return($num);
}
$values = mysql_query("SELECT
a.entry_company, a.entry_firstname, a.entry_lastname, a.entry_street_address,
a.entry_suburb, a.entry_city, a.entry_postcode, c.customers_email_address, c.customers_telephone
FROM
zen_address_book as a
LEFT JOIN
zen_customers as c
ON
c.customers_id = a.customers_id 


");

$i=0;
while ($rowr = mysql_fetch_assoc($values)) {
if($i==0) {
	$rowr['customers_telephone'] = split_num($rowr['customers_telephone']);
	foreach(array_keys($rowr) as $title)
	$csv_output .= '"'.str_replace(array_keys($replace), $replace, $title).'",';
	$csv_output .= "\n";
}

foreach ($rowr as $key => $value) {
	$csv_output .= '"'.$value.'",';

}
$csv_output .= "\n";
$i++;
}

$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");
print $csv_output;
exit;

?>

[DarkWater]

Can you show us where you actually put it, and maybe we can tell you why it errored?

[confused_aswell]

Hi

 

I just copied from above where the function line started and I got this error -

 

Fatal error: Cannot redeclare split_num() (previously declared in /home/john/public_html

 

Thanks,

 

Phil

[DarkWater]

You put the function code in twice.

[confused_aswell]

Hi

 

Yes I did sorry! But after correcting it, it has made no difference to the results.

 

Thanks,

 

Phil

 

 

[jonsjava]

first, please, oh please remove that link! you are giving the world a free list of names, numbers, addresses, and phone numbers of some people in that file.

 

Next, it appears to have worked on some, but not all. hmm...unfortuately, it's time for me to go for the day (I'll try to get on later tonight), so someone else is going to have to look at this.

[confused_aswell]

Ok

 

I am in the UK so it's getting late too. I have removed the file from the server so the link will not work anymore.

 

Thanks for trying.

 

The Numbers that are correct are the numbers that were entered with a space, so it is the numbers that are entered without a space that are causing the trouble.

 

Speak tomorrow evening UK time.

 

Phil