SQL on my website

NZ_Kiwis

Hi,

I have a website which is run on PHP with a MySQL database. Im wanting to make a desktop admin application for this using VB. It will need to retrieve insert and edit data from the MySQL database.

Firstly does anyone know if can this be done???
If so how easy is it... how can I learn more about how it would work??

Thanks heaps

drew010

MySQL has an ODBC driver you can download at
http://www.mysql.com/products/connector/odbc/

this should enable you to connect to the database through your vb program much like using the databound control and the access odb driver.

I'm guessing you want a friendly admin frontend for your specific program and the administrator program from them is too broad.
http://www.mysql.com/products/administrator/

Edit:
here is some good examples of the mysql driver with ADO
http://dev.mysql.com/doc/mysql/en/ado--rs-addnew.html

NZ_Kiwis

okay downloaded... but what is it?? how do I add something to my form to connect to the database??

drew010

Its a dll which provides ADO access to a mysql database on the local machine.
You would need to be a bit familiar with ADO data objects since it relies on that technology. If you have ever used ADO to use an access database file its the same.

A basic example taken from http://dev.mysql.com/doc/mysql/en/ado--rs-addnew.html

  Dim conn As ADODB.Connection

  'connect to MySQL server using MySQL ODBC 3.51 Driver
  Set conn = New ADODB.Connection
  conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};"_
                        & "SERVER=localhost;"_
                        & " DATABASE=test;"_
                        & "UID=venu;PWD=venu; OPTION=3"

  conn.Open

  'create table
  conn.Execute "DROP TABLE IF EXISTS my_ado"

thats basically how ado connections go.

im sure you can google for vb6 ado examples and see how to do it.

NZ_Kiwis

Originally posted by drew010
Its a dll which provides ADO access to a mysql database on the local machine.
You would need to be a bit familiar with ADO data objects since it relies on that technology. If you have ever used ADO to use an access database file its the same.

A basic example taken from http://dev.mysql.com/doc/mysql/en/ado--rs-addnew.html
  Dim conn As ADODB.Connection

  'connect to MySQL server using MySQL ODBC 3.51 Driver
  Set conn = New ADODB.Connection
  conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};"_
                        & "SERVER=localhost;"_
                        & " DATABASE=test;"_
                        & "UID=venu;PWD=venu; OPTION=3"

  conn.Open

  'create table
  conn.Execute "DROP TABLE IF EXISTS my_ado"
thats basically how ado connections go.

im sure you can google for vb6 ado examples and see how to do it. [/B]

Oh okay... so I few quick questions.... I'll have to make a "ActiveX DLL Document" not a "Standard EXE" one??

Can this be done when the database is not on my PC... it's loaded on a internet site? ie: another server

otherwise I've done the basics of ADO so could read more into it

drew010

you still make a standard exe, but you have to include a control "Microsoft ADO Data Control 6.0" MSADODC.OCX

The mysql db driver is setup to work with remote databases as well.

the one problem you will run into is when a remote computer tries to connect to mysql with the username someuser, its actually coming to the server as someuser@xx.xx.xx.xx where the xx.xx.xx.xx is their ip address. so the sql server will have to be setup to allow them access. this will be a problem if the ip changes a lot or you dont know the ip of the person to connect.

NZ_Kiwis

Originally posted by drew010
you still make a standard exe, but you have to include a control "Microsoft ADO Data Control 6.0" MSADODC.OCX

The mysql db driver is setup to work with remote databases as well.

the one problem you will run into is when a remote computer tries to connect to mysql with the username someuser, its actually coming to the server as someuser@xx.xx.xx.xx where the xx.xx.xx.xx is their ip address. so the sql server will have to be setup to allow them access. this will be a problem if the ip changes a lot or you dont know the ip of the person to connect.

So if I could not get the database to allow my ip t go to it... this is a waste of time??? any other options???

drew010

yeah if you have no way to grant permissions on the table then this dll is not for you. the only other thing I could think of would be to provide some sort of way to pass commands to some script which then processes it with the sql server and returns the output or any relevant data via some xml or something.

For example passing "SHOW TABLES" to the script would produce

<?xml version="1.0"?>
  <result>
    <row>
      <column name="Tables_in_sometable">table1</column>
    </row>

<row>
  <column name="Tables_in_sometable">table2</column>
</row>
  </result>

of course the xml is just a thought and you can do it any way, but parsing it is relatively easy with the right libraries.

NZ_Kiwis

Originally posted by drew010
yeah if you have no way to grant permissions on the table then this dll is not for you. the only other thing I could think of would be to provide some sort of way to pass commands to some script which then processes it with the sql server and returns the output or any relevant data via some xml or something.

For example passing "SHOW TABLES" to the script would produce
<?xml version="1.0"?>
  <result>
    <row>
      <column name="Tables_in_sometable">table1</column>
    </row>

<row>
  <column name="Tables_in_sometable">table2</column>
</row>
  </result>
of course the xml is just a thought and you can do it any way, but parsing it is relatively easy with the right libraries. [/B]

Yeah a mate said to maybe use XML... he also said it's ver advanced... I never used it and hardly knows what it does... would it be hard to learn???? what does it do and would it work??

drew010

the stuff i posted above is xml. its really not hard to learn. basically you make it up yourself. there are a few standards and rules that should be followed in order to make parsing consistent. but basically you pick the names of the tags and what goes inside them and what their children are (in this case column is a child or row which is a child of result).
the xml parser then reads all those tags and puts them in some sort of container. in vb.net it may be xmlres.Result.Row(0).Column(0)

NZ_Kiwis

Originally posted by drew010
the stuff i posted above is xml. its really not hard to learn. basically you make it up yourself. there are a few standards and rules that should be followed in order to make parsing consistent. but basically you pick the names of the tags and what goes inside them and what their children are (in this case column is a child or row which is a child of result).
the xml parser then reads all those tags and puts them in some sort of container. in vb.net it may be xmlres.Result.Row(0).Column(0)

hmm okay... seems to look like another form of normal HTML!!!

Well so I write a XML code upload that to the website then if I hada VB form which a person clicked save it would then send the data to a XML form on the website which would in turn save the info to the database.... is that basicly it??

drew010

the way i was thinking would be more like the client issues a regular query to a script to process it and return the output and the xml data is returned to the vb app for processing so you can output the appropriate results.
but whatever works for you is what counts

Weedpacket

Y'know, this isn't really a PHP problem, is it? It's a question of how to write a VB application that uses a MySQL database.

(I wouldn't say XML is "advanced" either - it's not as complex as HTML, and it's hardly new).

NZ_Kiwis

Originally posted by drew010
the way i was thinking would be more like the client issues a regular query to a script to process it and return the output and the xml data is returned to the vb app for processing so you can output the appropriate results.
but whatever works for you is what counts

How would I connect my XML script to the MySQL database??

drew010

you just use php like normal to connect to the database, but make it output the data in xml format thats all.

NZ_Kiwis

Originally posted by drew010
you just use php like normal to connect to the database, but make it output the data in xml format thats all.

Oh that easy then hey.... well do you know the VB side of things?? How I call it when I click save on my application?

drew010

you should have some sort of processing script on the server so the vb app can post data like a webform and the script handles the input. sort of like making your own little protocol. just make sure its secure to the point where people cant start accessing your db for bad reasons.