I get rows of SMS data into a MySQL table and need to extract the body of the SMS into another table so my web site can parse the data.
The SMS table is called Incoming and is made up of 3 columns: ts, tel, body.
The body column is made up of a message string and is formatted as VARCHAR(255)
The body message strings contains a CSV string of data blocks and looks like this:
1242,1309161346,60,1,0,188,32,42,30,0,0,0,0,0,0,0,0,0,0,0,0,0,406,1012,322,381,738,357,206
The first block (3 or 4 digits) is always the unit identifier
The second block is always the log start date in ddmmyyhhmm format (24 hour clock used)
The third block is always the interval in minutes, i.e. 60 indicates that a data reading is every hour starting from the log start date
The fourth block is always the number of channels being used and will only be 1 digit.
The next blocks after the fourth could vary in length and the number of blocks could vary as well. These are the channel readings.
So in the example given:
unit_id = 1242
start time = 13 Sep 2016 13:46
report interval = 60 minutes
number of channels = 1
I need to move the data into another table called staging that has the following columns: id, date, flag, chan1, chan2, chan3, chan4, chan5, chan6, chan7, chan8, chan9, chan10
The flag column is defaulted to 0 and is used by the web site to mark which rows have been read into the web site.
There will never be more than 10 channels.
The date will need to be calculated from the start time plus the interval depending on the position of the data block in the message string.
The date will need to be in the default MySQL format yyyy-mm-dd
So in the example given the data should break out like this:
id date flag chan1 chan2 chan3 chan4 chan5 chan6 chan7 chan7 chan8 chan9 chan10
1242 2016-09-13 13:46 0 0
1242 2016-09-13 14:46 0 188
1242 2016-09-13 15:46 0 32
1242 2016-09-13 16:46 0 42
1242 2016-09-13 17:46 0 30
1242 2016-09-13 18:46 0 0
1242 2016-09-13 19:46 0 0
1242 2016-09-13 20:46 0 0
1242 2016-09-13 21:46 0 0
1242 2016-09-13 22:46 0 0
1242 2016-09-13 23:46 0 0
1242 2016-09-14 00:46 0 0
1242 2016-09-14 01:46 0 0
1242 2016-09-14 02:46 0 0
1242 2016-09-14 03:46 0 0
1242 2016-09-14 04:46 0 0
1242 2016-09-14 05:46 0 0
1242 2016-09-14 06:46 0 0
1242 2016-09-14 07:46 0 406
1242 2016-09-14 08:46 0 1012
1242 2016-09-14 09:46 0 322
1242 2016-09-14 10:46 0 381
1242 2016-09-14 11:46 0 738
1242 2016-09-14 12:46 0 357
1242 2016-09-14 13:46 0 206
I would like this to run as a trigger after insert on our incoming table so everything is automatic in the background.
Thank you
Hello, I can develop your query, and through triggers, generate what you want, I would send the code of the query that would create the trigger, it would be better to work on your base, but it is not restrictive, if you have the least knowledge you can do it yourself .
Habilidades y experiencia relevante
more 15 years work with database.
Htos propuestos
£333 GBP - Query
if you have the least knowledge you can do it yourself .
£250 GBP 10 napon belül
5,0 (17 értékelés)
5,8
5,8
47 szabadúszó adott átlagosan £435 GBP összegű árajánlatot erre a munkára
Surely, We are ready to help you with Database conversion - SMS String to columns.
Can we discuss little more? We are ready to start the work now!
Relevant Skills and Experience
Please review our profile and past work here:
https://www.freelancer.com/u/OutsourceMan
Proposed Milestones
£315 GBP - We do not take any upfront payments. Pay when satisfied
Please select us right away, so that we can get started without wasting a day!
Thanks
Vaishali
On time and On budget I can do all what you want ....
I did 320 projects with a rate of 5/5 ....
please check https://www.freelancer.com/u/tarekahmed.html
Relevant Skills and Experience
I have a Professional team for programming PHP Laravel framework,chat systems,webcam chat, WordPress, Jquery,Nodejs,Anjular js,Mobile Applications and Flash
Proposed Milestones
£555 GBP - Final
Get rows of SMS data into a MySQL table and need to extract the body of the SMS into another table so my web site can parse the data.
Relevant Skills and Experience
I am good in MySQL
Proposed Milestones
£500 GBP - Cost
Hi,
Will provide you a java/C# based application to read the mysql table and parse the body data and fill into the new table.
Please provide me a sample table will demonstrate you.
Relevant Skills and Experience
The example i have does not relevant to you but yes can do demonstration then you can accept my bid.
Proposed Milestones
£200 GBP - After Demo for 10 rows
£244 GBP - After your satisfaction
Please share me sample table data.
Hello sir,
I can do it.
BEST THAT I TRY ALWAYS PLEASE HAVE A LOOK
https://www.freelancer.in/u/sismaster.html
Looking for your response.
Thanks,
Poonam
Relevant Skills and Experience
HTML, CSS, Bootstrap, Wordpress, Woocommerce and PHP Development.
Exp : +6 years
Proposed Milestones
£250 GBP - It will be nice if we have one time discuss to me.
good day!
i have went through your description, and i believe we can assist you creating this trigger under MySQL for mapping the data into the new table.
Relevant Skills and Experience
we have 13+ years of experience in web solutions, particularly in LAMP environment and in dealing with MySQL and DB Management. We also have excellent English communication
Proposed Milestones
£300 GBP - deposit
£300 GBP - completion
Greetings sir, i am an expert freelancer for this job and your 100% satisfaction is assured if you allow me to serve. I can do this task as per your requirement
Relevant Skills and Experience
I have the 5 years of experience with excellent knowledge. please give me a chance to work on your project
Proposed Milestones
£450 GBP - payment after work completion
Hi greetings of the day I am a Experienced PHP Developer based out in Chennai,India. My experience revolves around development of web and mobile applications,Ecommerce websites.
Relevant Skills and Experience
I believe that I am well qualified to undertake the task at hand and deliver a good solution. I would like to discuss this project and your exact requirements.
Proposed Milestones
£250 GBP - Database conversion - SMS String to columns
Hi there,
I have read the details, its an easy job for me.
Message me, I would like to discuss over chat.
Relevant Skills and Experience
I have strong knowledge in plsql, procedure, trigger, dynamic sql, pivot tables, normalization and much more
Have a look at my profile for similar projects I completed.
Proposed Milestones
£600 GBP - Project Milestone
Hi
We are a team of 150+ highly skilled developers having huge experience in website and mobility segment (android, ios) and already executed 300+ websites, 300+ android, 250+ iphone app...
Relevant Skills and Experience
MySQL,
Proposed Milestones
£555 GBP - Full project
Hi,
Thank you for your job posting.
I can help you with my skill and experience.
Relevant Skills and Experience
I will do this job with regular expressiong and cron.
As a linux adminstrator, I did similar works before, several times.
It is a simple work for one who knows RegEXP well.
Thank you.
Proposed Milestones
£388.89 GBP - Whole project
What is your OS?
In linux, is is better setup converting script on linux cron daemon.
Hi, I (Myself a senior PHP Developer) have a team having experience of more than 10 years.
Document for this project :-
- Technical Project Proposal
- Flow chart for this Project
- Execution plan
Relevant Skills and Experience
We also use to work on other CMS based on PHP like -
Wordpress,
Drupal,
Joomla
*Shopping cart/ eCommerce CMS -
1)WoocommerceD
2)Opencart
3)Shopify
4)Laravel
5)CodeIgnite
6)Symfony
Proposed Milestones
£515 GBP - This is a park price and will be discuss on further.
I have been in this field for more than 7 years. I have experience in php,perl,Html,postgress ,Web Services, Rest Api, Zend ,YIi,Mysql.i provide support Chester cap llc more than 5 years.
Relevant Skills and Experience
so i am right candidate for your project because i have experience in Perl,php,Api projects.
Proposed Milestones
£444 GBP - Database conversion - SMS String to columns
Hello Sir,
hope you are fine,
I am interested in your work "SMS String to columns".
I will create store procedure for given task ?
Relevant Skills and Experience
I have 5 year experience in Web Development Service. I am able to this job quickly.
Proposed Milestones
£400 GBP - Task 1
can i create php script for this task ?
Hi!
I have seen your requirement carefully.
I can do it.
Relevant Skills and Experience
MYSQL, Database.
Please contact me.
Proposed Milestones
£555 GBP - complete
Thank you for your reply.
I would like to offer myself as a probable candidate for this assignment as I am confident of delivering a quality output. I have excellent expertise in this area. I have total 7 years or experience in java and MySQL.
java,MySQL,trigger,function,procedure
hi, i can help you with your project. send me more information about via freelancer.com chat. regards
Habilidades y experiencia relevante
linux php mysql mariadb sql server
Htos propuestos
£25 GBP - freelancer.com fee
£225 GBP - final milestone
Yes, I can surely help you out with your project. I have few questions regarding the project and would like to discuss them with you as per your convenience.
Relevant Skills and Experience
PHP/OOP, CodeIgniter,CakePHP, Laravel, Ecommerce, WordPress,MySQL,
Proposed Milestones
£466 GBP - TBD
Hello there, I have reviewed your job description as well and very confident to handle this project perfectly.
Relevant Skills and Experience
php, html5
Proposed Milestones
£250 GBP - 250
Once please chat with me
We are happy to bid on this project.
We provides dedicate developers and Development and design services as well.
*****
We are having a great team of Mobile developers with 7-8 years of experience
Relevant Skills and Experience
MySQL,
Proposed Milestones
£555 GBP - Complete project