I have email notifications on my site but I want to add SMS notifications also. I am lost how to do this. Is it purely coding or do i need to engage in partnerships with phone providers world over or buy a service or how does it work. And for the coding are there frameworks i can use or have to do it from ground up? My social network is in php and mysql. Do i need anything from the database for this, like to store the messages or is that all on the ISP's end?
You need either a service for that, or to set up your own infrastructure (GSM modem, etc.). Best go with an SMS Aggregator, such as Clickatel. The technical term for this kind of service is "SMS Gateway".
Whichever gateway you choose, you need to find out what their API is and use it to send SMS.