*   finally - PHP and mySQL! (a little anyway)

*   a few caveats: 

    *   our mysql accounts are on sorrel
        rather than redwood (and so are an older version of mysql);

    *   the way this mysql is currently set up, we have to use
        PHP on sorrel to do so, which is also an older version;

    *   note that there are MULTIPLE ways to connect to a mysql
        database from PHP! This is the *simplest* way...

        (there are also object-oriented ways, more "modern"
        ways, and ways using packages such as PEAR which would
        be the same for a variety of databases)

        *   the method used here is also used in W3Schools PHP tutorial,
            in its mysql subsection:

            http://www.w3schools.com/php/php_mysql_intro.asp

*   SO: first of all: how do you reach your mysql account on sorrel?

    *   username: your campus username
    *   password: username reversed + 2 

*   in mysql, you need to connect to mysql, and then select the
    database within mysql that you wish to use;

    *   you have been given authorization (by the mysql administrators)
        to select the database whose name is the same as your username;

    *   SO: here are the 1st 3 important commands for the simple approach
        of connecting from php to mysql:

        1. connect to mysql:

           $con = mysql_connect("localhost", $username, $password);

        2. and select the database within mysql you want to use:
           (since its the same as the username, we can take advantage
           of that here...)

           mysql_select_db($username)

        3. and be sure to CLOSE your connection when you are done!!

           mysql_close($con)

*    a useful PHP construct to mention:

     action or die("message to show if dies");

     @mysql_select_db($username) or die("Unable to select database"); 

     (@ above means, don't just stop and give error if an error occurs...)

     *   SO: if I cannot select the desired database, stop this script
         and output "Unable to select database"

*   try_mysql0.php

    *   (note: mysql_error() can be used at times to obtain a message
        if an error has occurred...)

*   TRY that (
http://www.humboldt.edu/~st10/f06cis180php/180php_lect08/try_mysql0.php
    )
    and if you see:

try_mysql0.php

HEY YOU!! ABOUT TO TRY TO CONNECT...
ABOUT TO SEE IF CONNECTED...ABOUT TO SELECT DATABASE...
ABOUT TO CLOSE CONNECTION...

    ... THEN all is well!

*   NOW: you need a table.

    so, let's create one:  pets 

    column: pet_id   -- to be the primary key, to uniquely
                        identify a pet.

    *   int is one kind of integer in mysql
    *   if I want a column to be used as primary key, I need
        to declare it as "not null", also (cannot be empty)

    column: pet_name, the name of the pet, say up to 15 characters

    *   mysql does have a varchar type --- varchar(15), that would
        be up to 15 characters

    column: pet_birth - date of birth

    *   mysql does have a date type --- you can express a date literal
        as 'YYYY-MM-DD'

    column: num_siblings - an int

    SO: a SQL create statement that ought to work in mysql could be:

    create table pets
    (pet_id         int not null,
     pet_name       varchar(15),
     pet_birth      date,
     num_siblings   int,
     primary key    (pet_id));

    *   how can I run a SQL command that doesn't happen to return
        results I need to look at?

        mysql_query( $desired_command, $con );

        (can use "or die" option with this --- it'll "return" a
        false value if it fails, if you'd like to check and complain
        in that case...)

    *   try_mysql1.php: to create this table
     
*   (note: you can't create two tables with the same name in the
    same database --- so if you run try_mysql1.php twice, and it
    works the first time and complains the second --- that's good!
    Hopefully it means you have a pets table!!

*   Let's insert some pet info...

    insert into pets
    values
    (1, 'Fluffy', '2005-11-16', 8);

    ...but can we do it via a form?

    try_mysql2.php

*   And: how can we query this data?
    Here is one of several possible approaches:

    still use mysql_query --- BUT now you make sure to GRAB the
    large structure that is returned!

    $results = mysql_query( $select_stmt, $con );

    THEN....

    mysql_numrows($results) would return HOW MANY ROWS are in the result

    $row = mysql_fetch_array($results)

    ... which, when called repeatedly, sets $row to each row within
        $results, BUT returns that row as an array whose keys
        are the column names... (OR whatever was in the 1st
        line of the select statement ... for select *, it would
        the column names.)

    while ($row = mysql_fetch_array($results))
    {
        ... $row['desired_column'] ...
    }

    *   let's try this: try_mysql3.php

*   (and you can see how setting up a series of pages with sessions
    would make this more convenient for the user than typing their
    username and password for every single action... 8-) )