|
Tutorial 11
mSQL Functions
Functions demonstrated
- <?msql_connect($hostname)>
<?$result = msql($database,$query>
<?msql_result($result,$i,$field)>
<?msql_freeresult($result)>
<?msql_numrows($result)>
<?msql_numfields($result)>
<?msql_fieldname($result,$i)>
<?msql_fieldtype($result,$i)>
<?msql_fieldlen($result,$i)>
<?msql_fieldflags($result,$i)>
mSQL is a lightweight SQL database engine for Unix systems. It is extremely easy to
install and configure and it does a great job of handling simple SQL queries. It is
perfect for small-scale database needs such as those encountered by most web site
designers these days. mSQL was written in Australia by David J. Hughes and is available
from ftp.bond.edu.au.
msql_connect() specifies the host name or IP on which the mSQL database engine
resides. This is equivalent to the msqlConnect() function in the msql C API. The one
difference between this function and the C API equivalent is that if the function isn't
called, a connection to the local host is made by default on the first call to the
msql()
function. And, there is no need for an msql_close function since only one connection may
be active at any one time. If a second call to msql_connect() is made in a file, then the
connection to the first host is automatically closed. To explicitly connect to the msql
daemon on the local host, use: <?msql_connect("localhost")>
msql() sends an mSQL query. Arguments are the database name and the query
string. ie. <?msql("MyDatabase"quot;select * from table")>.
The return value from this function is a result identifier to be used to access the
results from the following functions.
msql_result() displays a field from a returned record. Arguments are the result
identifier returned by the msql() function, an integer which is the index of the record to
be viewed and a field name. This is perhaps best illustrated with a complete example:
<?
msql_connect("localhost");
$name = "bob";
$result = msql($database,"select * from table where firstname='$name'");
$num = msql_numrows($result);
echo "$num records found!<p>";
$i=0;
while($i<$num); echo msql_result($result,$i,"fullname"); echo "<br>"; echo msql_result($result,$i,"address"); echo "<br>"; $i++; endwhile; >
The above script connects to the mSQL engine on the local machine, sets the name
variable to bob and sends a query which asks for all the fields from a
table where the firstname field is set to bob. It then displays the
number of records it found after which it loops through each of the found
records and displays the fullname and address fields for each record.
As you can see, it would be trivial to add HTML markup tags around the printed
fields to format the results in a table or in whatever manner is desired.
msql_freeresult() only needs to be called if you are worried about using too
much memory while your script is running. All result memory will automatically be freed
when the script is finished. But, if you are sure you are not going to need the result
data anymore in a script, you may call msql_freeresult with the result identifier as an
argument and the associated result memory will be freed.
msql_numrows() simply returns the number of rows in a result. The argument is
the result identifier returned by the msql() function.
msql_numfields() returns the number of fields in a result. The argument is the
result identifier returned by the msql() function.
msql_fieldname() returns the name of the specified field. Arguments to the
function is the result identifier and the field index. ie. msql_fieldname($result,2);
will return the name of the second field in the result associated with the $result
identifier.
msql_fieldtype() is similar to the msql_fieldname() function. The arguments are
identical, but the field type is returned. This will be one of "int",
"char" or "real".
msql_fieldlen() again takes the same arguments as msql_fieldname() but returns
the length of the field.
msql_fieldflags() takes the same arguments as msql_fieldname() and returns the
field flags. Currently this is either, "not null", "primary key", a
combination of the two or "" (an empty string).
|
