|
Some web users have been looking
for a database. We do not currently allow users
to create their own. If you need a web database,
and you understand SQL (the language), you can request
a MySQL database from http://cis.brown.edu/db Please
understand that a database is no trivial application,
and requires a much deeper understanding than simply
being able to create web pages. Here is the MySQL
documentation site.
Once you have your MySQL database,
you can use the phpMyAdmin interface at www.brown.edu/phpMyAdmin. Only
people with a Brown NetID will be able to use this. You
can use SQL to create your tables in the database,
but you may find using phpMyAdmin is much easier.
If you are connecting to the database
server from the Brown web server (webpub), the database
server is "webdb.brown.edu". When you request
your database, you will receive a confirmation email
with your username, your password and your database
name.
MySQL and PHP Applications like phpBB do not
require you to make your own connections to the database. They
usually have form fields where you can fill in the
pertinent information. However if you want to
write your own PHP scripts that connect to your database,
you will need to know a little programming. The
best place to start is at www.php.net There
is a great deal of documentation and other information
there. Here are some basic tips to PHP.
- PHP *is* installed on the webserver. You
do not need to do anything to "turn it on".
- To see our PHP configuration,
view www.brown.edu/cis/about/phpinfo.php
- Make sure your files have a
.php extension on them. The server will not
know you have php code in the file if you don't put
a .php extension on the file
- You can't put a server-side
include in a PHP file. The alternative is to
either use the php require() function, or to make
your file a .html file and do an SSI of your php
code.
- Make sure all your php code
is inside <? and ?> tags and all your non-php code
is outside the tags
Using PHP to Connect to MySQL
Once you've learned a little PHP,
you can write your own connection script to your database. Here
are the basics:
It is best to keep your values,
like your username, password and database name
separated in the file, like this:
$server = "webdb.brown.edu";
$username = "brownbear";
$password = "cisiscool";
$db = "bear";
Then use the variables in your
connection function:
$connection = mysql_connect(
$server, $username, $password) or die ("Problem connecting to DB");
Once you have a successful connection,
you indicate which database you want to use:
mysql_select_db($db, $connection);
Now any time you need to communicate
with the database through your PHP script, simply talk
to the $connection variable.
MySQL Commands
Insert Now that you have a connection
to your database, you'll want to talk to it. Since
this is probably your first connection, the database
and your tables are empty. So lets put some information
in the tables. When you created your tables using
phpMyAdmin (what
is phpMyAdmin?) you created fields to define
the type of type of information that you will be using. The
first SQL command we will use will be to get data into
the database. This command is INSERT. These
are not case-sensitive, I did all caps for emphasis. Here
is an example of an insert statement where I put a record
into my courses table:
$insert = "Insert into courses
values('Intro to Biology', 'Van Der Hoek', '9:00 am',
'Sayles Hall'";
mysql_query($insert, $connection);
Notice in mysql_query, the connection
is also included. That's how the function will
know which database it needs to connect to.
Select
Now that you have some information
in your database, you need to be able to get it out
to display it, or print it to the page. By the
way, you can print to the screen using the print() function. Once
you get the hang of talking to the database with one
command, all the rest are very similar. Here is
a SELECT statement:
$select = "Select * from courses
where time = '9:00 am'"; (* is a wildcard indicating
"everything")
$result = mysql_query($select,
$connection);
The big difference is the $result
that you get back. This result is a blob of data
that you need to use PHP functions to extract. A
couple of examples of functions to read your data from
a result are:
mysql_fetch_object($result);
mysql_fetch_row($result);
mysql_fetch_array($result);
mysql_fetch_field($result);
Once you get an understanding for
how those functions work, you'll have your data, ready
to either manipulate or simply print somewhere.
Update
Next is the SQL update statement. You
want to change the values of some data in your table. It
is very similar to the insert statement, but now you
need to specify a "where" to tell the database which
exact row you want to change.
$update = "Update courses
set time='10:30' where building='Sayles Hall';
mysql_query($update,
$connection);
Delete The last of the major four SQL
statements to worry about is delete. Delete will
remove a whole row, or multiple rows (or all the rows!)
from your table. It is very similar to insert
and update.
$delete = "Delete from courses
where professor='Van Der Hoek'";
mysql_query($delete, $connection);
One last bit of information is
mysql_query for update, insert and delete functions
will each send back a true
or false value for whether your request was successful. If
you get back a zero (0), that means everything went
fine, and if you get back anything else, there was
a problem that you need to look into.
Once you are done talking to the
database in your script, it is a good programming practice
to call the php close() function on your connection. This
will clean up your resources, so the server is not
unnecessarily bogged down. That's about it. If you understand
how a database works, how to create your tables and
fields, you should be able to communicate back and
forth with your database and output information to
the user's screen. Remember, PHP is a language
that needs to be parsed by the server, so don't try
to run your PHP files in your local browser like you
can for html files. PHP files need to be uploaded
to the server before they will work.
If you have other questions, just
contact webpublishing<at>brown.edu and we'll do our
best to get you going again.
|
