Using MyODBC To Access Your MySQL Database Via ASP - Querying our MySQL database

(Page 5 of 6 )

As mentioned earlier, MySQL conforms to standard (ANSI) structured query language. As with SQL Server 2000 and Oracle, MySQL also has its own set of custom SQL functions that are available for us to use. We will talk more about these soon.

Using MySQL, we can add, update and delete records, just like we can for any other database management system that supports an OLEDB driver. Firstly, let's take a look at how we would add a record to our contacts table using the ADO recordset object:

<%

on error resume next

dim adoConn

dim adoRS

dim strQuery

set adoConn = Server.CreateObject("ADODB.Connection")

adoConn.Open "DSN=mysql_dsn"

strQuery = "INSERT INTO contacts VALUES(0, 'Jill', 'Smith', '20 Santamonica Parade', 'Beverly Hills', '555 0303')"

adoConn.Execute strQuery

%>

To update a record, we simply modify our query:

strQuery = "UPDATE contacts SET firstName = 'Jack' WHERE firstName = 'Jill'"

Lastly, to delete a record, we can use the ANSI SQL command, delete:

strQuery = "DELETE FROM contacts WHERE firstName = 'Jack'"

The LIMIT keyword

MySQL also has some implementation specific keywords that can save us an extraordinary amount of time. The first of these is "LIMIT". Using the limit keyword within a select statement, we can tell MySQL how many rows of data to return, and which row index to start fetching these rows from. For anyone working with Access/MS SQL looking to start using MySQL, setting up recordset paging will be extremely easier!

The limit keyword should be appended to the end of a MySQL query after the where and group by keywords. It accepts two arguments, with only the first required:

LIMIT [offset,] rows

If we use the limit keyword with just one argument, then MySQL will start the results offset at zero and fetch however many rows are specified as that parameter. On the other hand, if we specify two arguments, then MySQL will use the first argument as the row offset, and the second as the number of rows to return.

So, if we wanted to start to retrieve rows 6 to 15 in our contacts table, we would use the following query:

SELECT * FROM contacts

WHERE 1

LIMIT 5,10

The SHOW TABLES keyword

MySQL gives us easy access to the complete list of tables in any database that we have access to. Using the show tables keyword, we can retrieve a list of table names in the current database.

To use the show tables command, just pass it as the query for the open method of a recordset object:

adoRS.Open "SHOW TABLES"

This would return a complete list of all tables names in the current database. Each table name would occupy a new row in the results set.

The DESCRIBE keyword

PhpMyAdmin is a PHP script that allows you to modify your MySQL databases over the web. If you’ve ever worked with phpMyAdmin, then you’ll be familiar with its interface: It displays the details of each table in a database including field names, lengths and extra options.

Using the describe table keyword, we can accomplish this for our contacts table with very little effort:

adoRS.Open "DESCRIBE contacts"

while not adoRS.EOF

for counter = 0 to adoRS.Fields.Count - 1

response.write adoRS.Fields(counter).value & " "

next

response.write "<br>"

adoRS.MoveNext

wend

The output of this ASP code would look like this:

contactId int(11) PRI auto_increment

firstName varchar(50) YES

lastName varchar(50) YES

address1 varchar(100) YES

address2 varchar(100) YES

phone varchar(20) YES

In the example above, I have only passed one argument with the describe keyword. If we wanted to, we could pass two. The definition of the describe command looks like this:

DESCRIBE | DESC} tbl_name {col_name | wild}

The second (and optional) argument can either be a specific column name (such as "firstName") or a string containing wild card characters such as "%" and "_". Using the second argument is handy if you need to retrieve the field details from a table based on a specific set of specific search criteria.

Next: Conclusion >>
 

More ASP Articles
More By Annette Tennison

Comments On: Using MyODBC To Access Your MySQL Database Via ASP

>>> Be the FIRST to comment on this article!