Update MySQL database PHP

Updating existing data in a MySQL database is easily done by using this VBA Macro code. Many are using the methodology when working with websites developed in PHP and MySQL.

Explanation

This VBA Macro code is optimized for updating an exsisting MySQL database. You need a connector, ODBC for latest version mysql.com download the excel file ate the bottom of this page or copy and paste the code directly. In the file there are some data that needs to be added according to your set up of the MySQL database and connection. Fill in the data and add the required fileds that you are going to update. Push the buttom and you will be updating existing data in your MySQL database. MySQL is one of the most effective databases and the best thing is that it is used free of charge.

You have to enable the Microsoft ActiveX Data Objects X.X Library in order to execute the VBA Macro code.

Code

Sub UpdateMySQLDatabasePHP()

' For detailed description visit http://www.vbaexcel.eu/

Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim SQLStr As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

User_ID = Range("h1").Value                      'i d user or username
Password = Range("e3").Value                    ' Password
Server_Name = Range("e4").Value             ' IP number or servername
Database_Name = Range("e1").Value         ' Name of database
Tabellen = Range("e2").Value                     ' Name of table to write to

rad = 0
While Range("a6").Offset(rad, 0).Value <> tom
    TextStrang = tom
    kolumn = 0
    While Range("A5").Offset(0, kolumn).Value <> tom
        If kolumn = 0 Then TextStrang = TextStrang & Cells(5, 1) & " = '" & Cells(6 + rad, 1)
        If kolumn <> 0 Then TextStrang = TextStrang & "', " & Cells(5, 1 + kolumn) & " = '" & Cells(6 + rad, 1 + kolumn)
        kolumn = kolumn + 1
    Wend
    TextStrang = TextStrang & "'"
    SQLStr = "UPDATE " & Tabellen & " SET " & TextStrang & "WHERE " & Cells(5, 1) & " = '" & Cells(6 + rad, 1) & "'"
    Set Cn = New ADODB.Connection
    Cn.Open "Driver={MySQL ODBC 3.51 Driver};Server=" & Server_Name & ";Database=" & Database_Name & _
    ";Uid=" & User_ID & ";Pwd=" & Password & ";"
    Cn.Execute SQLStr
    rad = rad + 1
Wend
Set rs = Nothing
Cn.Close
Set Cn = Nothing

End Sub

Download excel file! Update-MySQL-Database-PHP.xls