Hi,
Some one pls help me to read datas from mysql table and update only single field in my table. I have mysql table and already inserted datas. But now i want to update only date field , since i need to place the all other data as it is.
Some one please help how to do this..
Thank you in advance.
My code is some complicated, it is done in codeigniter. I mentioned the code below, but pls give me a complete simple code in php as seperate file.
and this code has validation of phonenumber, account number etc. I just need to update only the date field curresponding customer account number.
thank you ...
$datafile = $this->upload->data();
// echo 'extension '. substr(strrchr($datafile['file_name'],'.'),1);die;
$pathToFile = './uploads/fileupload/'.$datafile['file_name'];
$this->load->library('spreadsheet_excel_reader');
//$this->spreadsheet_excel_reader->setOutputEncoding('CP1251');
$this->spreadsheet_excel_reader->setOutputEncoding('ISO-8859-1//TRANSLIT//IGNORE'); // Set output Encoding.
$error = $this->spreadsheet_excel_reader->read($pathToFile); // relative path to .xls that was uploaded earlier
if($error == 'error'){
$this->session->set_flashdata('error', 'The data file cannot be uploaded');
redirect('members');
}
$rows = $this->spreadsheet_excel_reader->sheets[0]['cells'];
$row_count = count($this->spreadsheet_excel_reader->sheets[0]['cells']);
$valuearray = array();
foreach ($this->spreadsheet_excel_reader->sheets[0]['cells'][1] as $key=>$value){
$valuearray[] = ($key);
}
//echo '<pre>';print_r($this->spreadsheet_excel_reader->sheets[0]['cells']);die;
$table = 'ad_members';$CustomerAccColumn = '';$phoneIndex = '';$AccColumn = '';
if($fileformat == 'new'){
$phone_arr = array();$account_arr = array();$cust_acnum = array();$acnum = array();$res = array();
$sql = "INSERT INTO ".$table." (";
for($index = 1;$index <= $this->spreadsheet_excel_reader->sheets[0]['numCols']; $index++){
if(isset($this->spreadsheet_excel_reader->sheets[0]['cells'][1][$index])){
switch(strtoupper($this->spreadsheet_excel_reader->sheets[0]['cells'][1][$index])){
case 'ID':
$column = 'id';
break;
case 'SALE DATE':
$column = 'SaleDate';
break;
case 'DATE OF PAYMENT':
$column = 'SaleDate';
break;
case 'CENTRE':
$column = 'Center';
break;
case 'ACCOUNT':
$column = 'CustomerAccountNumber';
$CustomerAccColumn = $index;
break;
case 'CUSTOMER A/C':
$column = 'CustomerAccountNumber';
$CustomerAccColumn = $index;
break;
case 'ACCOUNT NUMBER':
$column = 'CustomerAccountNumber';
$CustomerAccColumn = $index;
break;
case 'PHONE NUMBER':
$column = 'Phone';
$phoneIndex = $index;
break;
case 'CONTACT DETAILS':
$column = 'Phone';
$phoneIndex = $index;
break;
case 'TITLE':
$column = 'Title';
break;
case 'NAME':
$column = 'CustomerName';
break;
case 'NAME OF CUSTOMER':
$column = 'CustomerName';
break;
case 'FIRST NAME':
$column = 'firstName';
break;
case 'NAME OF CUSTOMER':
$column = 'CustomerName';
break;
case 'LAST NAME':
$column = 'lastName';
break;
case 'ADDRESS ':
$column = 'Address';
break;
case 'ADDRESS':
$column = 'Address';
break;
case 'ADDRESS LINE 1':
$column = 'AddressLine1';
break;
case 'ADDRESS LINE 2':
$column = 'AddressLine2';
break;
case 'ADDRESS LINE 3':
$column = 'AddressLine3';
break;
case 'ADDRESS TOWN':
$column = 'AddressTown';
break;
case 'ADDRESS COUNTY':
$column = 'AddressCountry';
break;
case 'ADDRESS COUNTRY':
$column = 'AddressCountry';
break;
case 'POSTCODE':
$column = 'postCode';
break;
case 'BOX TYPE':
$column = 'BoxType';
break;
case 'TYPE OF BOX':
$column = 'BoxType';
break;
case 'MULTIROOM BOX':
$column = 'MultiroomBox';
break;
case 'MULTIROOM BOXES':
$column = 'MultiroomBox';
break;
case 'SCHEDULE REFERENCE':
$column = 'ScheduleReference';
break;
case 'AMOUNT PAID':
$column = 'AmountPaid';
break;
case 'AMOUNT':
$column = 'AmountPaid';
break;
case 'DIRECT DEBIT':
$column = 'DirectDebit';
break;
case 'SORT CODE':
$column = 'SortCode';
break;
case 'ACCOUNT NO':
$column = 'AccountNo';
$AccColumn = $index;
break;
case 'PASSWORD':
$column = 'Password';
break;
case 'ACCOUNT NAME':
$column = 'AccountName';
break;
case 'FEED BACK':
$column = 'Feedback';
break;
case 'COMMENT ':
$column = 'comments';
break;
case 'COMMENT':
$column = 'comments';
break;
case 'COMMENTS DATE':
$column = 'comments';
break;
case 'STATUS':
$column = 'status';
break;
case 'ACTIVE OR CANCELLED':
$column = 'status';
break;
case 'ACTIVE':
$column = 'status';
break;
case 'CALL CENTER':
$column = 'CallCentre';
break;
case 'CENTER':
$column = 'Center';
break;
case 'CALL CENTRE':
$column = 'CallCentre';
break;
case 'CALL CENTER ':
$column = 'CallCentre';
break;
case 'QUALITY CHECK':
$column = 'Quality';
break;
case 'REFERENCE NUMBER':
$column = 'ReferenceNumber';
break;
case 'ENGINE':
$column = 'Engine';
break;
case 'CLAIME':
$column = 'Claime';
break;
case 'PAID':
$column = 'Paid';
break;
/*default :
$column = $this->spreadsheet_excel_reader->sheets[0]['cells'][1][$index];
break;*/
default :
$colmn_arr = array("id","firstName","lastName","CustomerName","CustomerAccountNumber","email","phone","postCode","comments","author","status","created_date","SaleDate","PaymentDate","Center","Title","AddressLine1","AddressLine2","AddressLine3","AddressTown","AddressCity","AddressCountry","Address","Password","BoxType","MultiroomBox","ScheduleReference","AmountPaid","DirectDebit","SortCode","AccountNo","AccountName","CallCentre","Quality","ReferenceNumber","Feedback","Claime","Engine","Paid");
if(!in_array($this->spreadsheet_excel_reader->sheets[0]['cells'][1][$index],$colmn_arr)){
$this->session->set_flashdata('error', 'The table contains an unknown column named "'.$this->spreadsheet_excel_reader->sheets[0]['cells'][1][$index].'". Please remove that field from excel sheet and try again.');
redirect('members/uploadfile');return;
}
break;
}
//$sql.= ($column) . ",";
$sql.= strtolower($column) . ",";
}
}
$sql = rtrim($sql, ", ").", created_date ) VALUES ( ";
for ($i = 2; $i <= $this->spreadsheet_excel_reader->sheets[0]['numRows']; $i++) {
$valuesSQL = '';$newDateValue = '';$statValue = '';$present = '';
for($temp = 0; $temp < sizeof($valuearray); $temp++){
//Prevents the phone number duplication
$phoneNumber = "";$PhnSql = "";$PhnQuery = "";$phoneNumberExist = "";
if($temp == ($phoneIndex - 1)){
$phoneNumber = (!empty($this->spreadsheet_excel_reader->sheets[0]['cells'][$i][$valuearray[$phoneIndex - 1]])) ? $this->spreadsheet_excel_reader->sheets[0]['cells'][$i][$valuearray[$phoneIndex - 1]] : '';
//break 2;
}
//Prevents the account number duplication
$accountNumber = "";$AcSql = "";$AcQuery = "";$accountNumberExist = "";
if($temp == ($CustomerAccColumn - 1)){
$accountNumber = (!empty($this->spreadsheet_excel_reader->sheets[0]['cells'][$i][$valuearray[$CustomerAccColumn - 1]])) ? $this->spreadsheet_excel_reader->sheets[0]['cells'][$i][$valuearray[$CustomerAccColumn - 1]] : '';
//break 2;
}
//Prevents the account number duplication
$acctNumber = "";
if($temp == ($AccColumn - 1)){
$acctNumber = (!empty($this->spreadsheet_excel_reader->sheets[0]['cells'][$i][$valuearray[$CustomerAccColumn - 1]])) ? $this->spreadsheet_excel_reader->sheets[0]['cells'][$i][$valuearray[$CustomerAccColumn - 1]] : '';
//break 2;
}
if($phoneNumber || $accountNumber || $acctNumber){
$Sql = "SELECT * FROM ad_members WHERE CustomerAccountNumber LIKE '".$accountNumber."' OR AccountNo LIKE '".$acctNumber."' OR Phone LIKE '".$phoneNumber."'";
$Query = $this->db->query($Sql);
$this->load->database();
$this->db->reconnect();
//echo $this->db->last_query();die;
$Result = $Query->row();
if($Result){
//echo $Result->phone;die;
if($Result->CustomerAccountNumber || $Result->AccountNo || $Result->phone){
$present = $i;
$res[$Result->CustomerAccountNumber][] = array('customer_account_number' => $Result->CustomerAccountNumber, 'AccountNo' => $Result->AccountNo, 'phone_number' => $Result->phone);
}
}
}
I just need to update only the date field curresponding customer account number.
Where do you get the customer account number from?
I have already inserted excle sheet datas in database, now i only need to update the date field in that same excel sheet. So what i am looking is to read same excel and update only date field to the curresponding customer accountnumber or same field.
thanks.
anyway to update one single row?
Hang on. You said in your previous post that you have data in mysql table which you want to update, or have I missunderstood the question.
So do you want to update a row in an excel sheet? I do not hink you could do that. You have to read in the whole file, find the row to change and write the whole file back, as far as I know. But I could be werong so maybe anyone else knows?
Yes, I have already exprted datas in to mysql table. But the one of the date field formate has been changed when export excel sheet. So i need to update only date field in mysql table by reading the same excel sheet. But the other datas except that date field shoudnot be changed.
Please help me.
Thanks in advance.
OK, how is the date written in Excel and how you want to have it written in mysql? Post an example if possible.
in excel written as dd/mm/yy. didnt change the format like mm-dd-yy. That may be chnanged the date.
Now the date has been change to 1970-01-01 , not getting actual date.
If dates in Excel are formatted as dd/mm/yy (allways) you can use the following function on each date during the import or write to database:
function reformatDate($theDate) {
// get the parts of the date
$day = substr($theDate, 0, 2);
$month = substr($theDate, 3, 2);
$year = substr($theDate, 6, 2);
// return the parts in different order and separated by dash
return "$month-$day-$year";
}
[EDIT] or even better:
function reformatDate($theDate) {
// explode date string into array
$dateArr = explode('/', $theDate);
// return string with reordered elements
return "{$dateArr[1]}-{$dateArr[0]}-{$dateArr[2]}";
}
Thank you....
but pls tell me how to read one particular field (here "sale_date') from excel sheet when importing?
Pass the above function the appropriate element of the array, containing the excel data. Maybe something like this:
$correctDate = reformatDate($this->spreadsheet_excel_reader->sheets[0]['cells'][1]['sale_date']);
I am just guessing this since I do not know the structure of the excel reader class.
I 'l try.
Thank you so much for your great support.
Hi, I have one another doubt.
When I imprt excel sheet, there is a column for amount. In excel the amount is in £, but when inserted in database , there shows $ symbol. How is it changed automatically?
Is there an attribute in the excel reader class that keeps the currency setting?
I think no.. the code is same mentioned above.
In exel sheet i used £12. But in database it shows only 12 or some times it is $12. pls tell me how to solve this?
thanks in advance.
I think in the database you should have only the number without any sign (provided that all the values are in same currency). You should get rid of the pound symbol i.e. using the mb_substr function, something like:
$trimmedAmount = mb_substr($amount, 1);
But I am not sure if this will work correctly since it depends on how the amount is read, what the encoding is and what the database column type for the amount is. Let me know the result.
Currenltly my database are values are like this.
amountpaid
12.98
$10.50
$12.10
£11.50
14.30
£9.58
.
.
.
.
and i need to replace these values like,
amountpaid
£12.98
£10.50
£12.10
£11.50
£14.30
£9.58
.
.
.
etc.
pls tell me a solution...
Thanks..
waiting for your answer , pls help me.
I wrote code like this, but it add 2 £ ('££120'). code is,
$amount= mysql_real_escape_string("£59.99");
echo $first= $amount[1];
if( ($first=="$")||($first=="£") ) {
$amount =substr($amount,1);
$amount="£".$amount;
}
Have you tried the mb_substr function (see above post)?
It seems that the SQL statement the $sql variable does not get constructed properly. In the lines below you can see how it gets built up up to the VALUES part. But where are the actual values added to it then? Is there any more code you did not post?
Line 26: $sql = "INSERT INTO ".$table." (";
...
Line 205: $sql.= strtolower($column) . ",";
...
Line 208: $sql = rtrim($sql, ", ").", created_date ) VALUES ( ";
...
It is in the code that follows you should use the mb_substr function.
You can change the existing values in the database with a query like this:
UPDATE `test` SET amount =
IF(
SUBSTRING(`amount`, 1, 1) = '$',
CAST(SUBSTRING(`amount`, 2) AS DECIMAL(10,2)),
CAST(`amount` AS DECIMAL(10,2))
)
I am assuming that you have the amount column of type DECIMAL which I think is appropriate for money. You might have to create a temporary column if transformation can not be done in the same column.
Your code has a few errors:
$amount= mysql_real_escape_string("£59.99");
echo $first= $amount[1];
if( ($first=="$")||($first=="£") ) {
$amount =substr($amount,1);
$amount="£".$amount;
}
$first= $amount[0];So in my view the code should be:
$amount= '$59.99';
$first= $amount[0];
// ASCII 36 = dollar sign, ASCII 163 = pound sign
if( ($first == chr(36)) || ($first== chr(163)) ) {
$amount = substr($amount,1);
}
// store this value in database
$safe_amount= mysql_real_escape_string($amount);
Thank you very much.
You are welcome. If the problem is solved please mark the thread as solved. Happy coding.
This helped me a lot and I got great support from here. Currenlty I didnt update database via excel sheet, just updated the currency showing area only. Currenlty solved temporarily (working on another project now).
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.