Basic Tutorial for MySQL VB

How to use MySQL? First, let's define this term. MySQL is a database found on many web servers. Its features include a user interface that's easy to use, and an ability to connect easily to other web technologies like PHP and ASP.

To begin your MySQL tutorial, get an account with a web host that uses both MySQL and PHP. You can find a list of free hosts here.

Then, read this article for a basic tutorial for MySQL VB, and for other technologies connected with MySQL.

After you've signed on with a MySQL-enabled web host, consult their technical support staff and documentation to get the instructions for creating your first database on their servers. Be sure the login and password information needed to connect to your database is among these instructions.

Once you've created the database, your next step is to make a table of sample data for it. Begin that process by logging onto the MySQL web interface that your host has made available.

Click the name of your database to display the form with the following links near the top: "Structure," "SQL," "Search," "Query" and others. Near the bottom of that page, seek the text "Create new table on database <your database name>". In the text box next to that text, type "MoviesTable," and enter "3" in the text box next to the "Number of fields" text.

The MySQL form for adding new tables

Describe the fields of the Movies table in the next form that appears: type these values for the "Field" column:

"MovieTitle"

"Director"

"YearReleased"

Specify the length of the field in the "Length/Values" column: enter these values, which correspond to the field names you just entered:

80

80

7

In the "Type" field, change just one of the values in the dropdown boxes: make the "Type" value for "YearReleased" read "INT."

Press the "Save" button to make MySQL create the actual table.

Insert a row with the web interface

Insert a row representing the information for one movie, into your new table. Press the "Insert" link, then type in "Close Encounters of the Third Kind," or some other movie title, in the "Value" field of the "MovieTitle" row. Complete the "Director" and "YearReleased" fields in the same way. (You can type "Spielberg" and 1977" for the "Close Encounters" movie.)

Press the "Go" button near the bottom of the form to create the new record. On the page that follows, look for confirmation of the entry in the form of text that reads something like this: "Inserted rows: 1"

Notice the text in the section "SQL query:" That text is the SQL equivalent of the insertion action you just took. If you'd entered that statement at the MySQL command prompt, the row would have been inserted just as effectively.

Write some SQL

Dig deeper in the SQL language itself by creating some queries with it, as opposed to using the form to create queries. Click the "SQL" link, then notice the default statement that appears in the SQL window:

SELECT * FROM `MoviesTable` WHERE 1

If you don't see that statement in the window, paste it in now, then press the "Go" button at the right of the window. MySQL executes your query, and displays the results in the form of a table.

The MySQL SQL window

Only one row was returned, since we've only entered one row in the table thus far. Insert more rows of movie information--but using SQL this time. Click the "SQL" link again to return to the SQL window. Enter the following statement in the window to create two new rows of movie information. Remember to press the "Go" button after pasting the statement in the SQL window.

Insert into MoviesTable (MovieTitle, Director, YearReleased) values ('Das Boot', 'Petersen', '1982'), ('Raiders of the Lost Ark', 'Spielberg', '1981');

The other common SQL functions besides INSERT and SELECT are UPDATE and DELETE. We'll cover those statements in the final section of this article.

When learning how to use MySQL, you're not confined to working with just the MySQL web interface. You can easily do SELECTS, INSERTS and other operations through scripts. This section's MySQL tutorial demonstrates that.

One scripting language you can use to execute MySQL statements is Visual Basic (VB). Use Visual Basic within the Active Server Pages (ASP) framework, which your web host service must have installed before you try this approach.

Setting the connection string

To begin writing VB code that executes MySQL statements, first contact the technical support staff at your web host. Make the following request of them, starting with giving them this code fragment:

Set Connection = Server.CreateObject("ADODB.Connection")

ConnectionString="???"

Connection.Open ConnectionString

Ask them this: "What do I enter in place of the '???' in this code fragment?" Or, simply ask them "What connection string do I use to connect to my MySQL database?"

The string they respond with should look something similar to this:

ConnectionString="DRIVER={MySQL ODBC 3.51 Driver}; SERVER=<YOUR_MYSQL_URI>; PORT=3306; DATABASE=<YOUR_DATABASE_NAME>; USER=<YOUR USER NAME>; PASSWORD=<YOUR PASSWORD>; OPTION=3;"

For more information on connection strings required by ASP scripts, consult ConnectionStrings.com.

A sample database fetch with VB

Once you've gotten the connection string from your web host staff, paste the following code into any plain text document:

<%

Option Explicit

Dim Connection

Dim RS

Dim SQLStmt

Dim SSN, Firstname, Lastname

Dim ConnectionString

Set Connection = Server.CreateObject("ADODB.Connection")

ConnectionString="YOUR_CONNECTION_STRING_GOES_HERE"

Connection.Open ConnectionString

SQLStmt = "SELECT * FROM MoviesTable;"

Set RS = Connection.Execute(SQLStmt)

' loop through the records

Do While Not RS.EOF

Response.Write RS.Fields("MovieTitle") & "<br>"

RS.MoveNext

Loop

Connection.Close

%>

Upload the file to your server, then open it in a web browser. You should see a listing of the movie titles you entered.

Read more about using ASP and VB with MySQL here.

Another scripting language is commonly used with MySQL, more commonly than VB: the PHP language. Here's a link to an article on PHP MySQL Basics. The following program is a PHP script that presents a web form that the user can use to run queries against a MySQL database. Paste the following code into a new, plain text file, and save the file as "AllQueries.php" Upload the script to your server, then download it to your browser.

<?php

displayForm();

$qstring = $_POST["myquery"];

if ($qstring != "" ) {

executeQuery($qstring);

}

///////////////////////////

function displayForm () {

echo '

<html>

<body>

<form action="allQueries.php" method="POST" >

<textarea name="myquery" cols="80">select * from MoviesTable;</textarea>

<br>

<input type="submit" >

</form>

</body>

</html> ';

}

function executeQuery ($qry) {

mysql_connect("YOUR_USERNAME.YOUR_HOSTS_MYSQL_WEB_ADDRESS", "YOUR_USERNAME", "YOUR_PASSWORD") or die(mysql_error());

mysql_select_db("YOUR_DATABASE_NAME") or die(mysql_error());

$rcode = mysql_query( $qry);

while ($movie = mysql_fetch_assoc($rcode)) {

$key = array_keys($movie);

foreach ($key as $columnName) {

echo $columnName.": ".$movie[$columnName].", ";

}

echo "<br>";

}

mysql_free_result($rcode);

}

?>

Type the following INSERT query into the text area, then press the "Submit Query" button:

INSERT INTO `MoviesTable` ( `MovieTitle` , `Director` , `YearReleased` ) VALUES ( 'MyMovie', 'Some Guy', '1999');

Verify the new row was inserted by executing this statement: "select * from MoviesTable;"

Try an Update statement: UPDATE `MoviesTable` SET `MovieTitle` = 'HerMovie',`Director` = 'Some Gal' WHERE ( `MovieTitle` = 'MyMovie' AND `Director` = 'Some Guy' AND `YearReleased` =1999 );

Finally, delete some records with this statement:

Delete from `MoviesTable` where (`YearReleased`=1999);

Get the details on how PHP can work with MySQL here.

References

The MySQL Reference Manual