Setting up a database using MySQL at Truman

Updated for Spring 2017

• You were sent an automated email telling you about your MySQL database on mysql.truman.edu, which should have a name something like abc1234CS315. The message also mentioned your MySQL password.
• You can log into this account graphically by going to phpMyAdmin, logging in using your Truman id (abc1234) and typing the password you received in the email message. From here you can choose your abc1234CS315 database, create tables and (eventually) look at and modify data.
• To access your MySQL through PHP, you need to analogize from the examples on the class webpage. My examples run on my webserver, so they are a bit different from what you need to do.

• I'm going to show you three ways to connect PHP and MySQL, from really old and unacceptable for this class, to slightly less old but still unacceptable, to the actual approach we are going to use. But you will still see the old approaches in various places, so you should know they exist.

  1. The oldest style is shown in the bookreportpub.phps example, toward the bottom of the file establishes a MySQL connection and selects a database. Your analogous commands would look like:

        $db = mysql_connect("mysql.truman.edu", "abc1234", "password");
     
        $er = mysql_select_db("abc1234CS315");
     

     Now in the code that actually performs a query (higher up in the file) you use the name of the table that you created using phpMyAdmin. For example, if your table name is hw5, your select statement might look something like:

        $sql = "select lastname, firstname, bannerID from abc1234CS315.hw5";
        $result = mysql_query($sql);
     
         while ($r = mysql_fetch_array($result)) {
     
     

  2. The slightly less bad, but still not acceptable, procedural way to connect to MySQL is shown in bookreportpub3.phps. Everything is the same as the previous example, except mysqli versions of the functions are used and the $db connection object is passed to later function calls.

        $db = mysqli_connect("mysql.truman.edu", "abc1234", "password");
     
        $er = mysqli_select_db($db, "abc1234CS315");
     
        $sql = "select lastname, firstname, bannerID from abc1234CS315.hw5";
        $result = mysqli_query($db,$sql);
     
         while ($r = mysqli_fetch_array($result)) {
     
     

     Note that the $db object needs to be passed as a parameter to the doAll function.
  3. Our textbook's preferred OOP and less MySQL-specific way to do this is shown in bookreportpub2.phps. Now an object is constructed and passed to the later sections.

        $db = new PDO("mysql:dbname=bookstuff;host=vh216602.truman.edu", 
                       "bookuserSel", "AbQm");
     
        $result = $db->query($sql) or die ("Mysql query failed");
     
        foreach ($result as $r) {
     
     

     Again $db needs to be passed as a parameter and the result is just an array of associative arrays that can be stepped through.