ITEC 325 2014fall ibarland

home—lects—hws
D2L—breeze (snow day)

lect27-myphpadmin
MyPhpAdmin

We'll briefly discuss using the GUI MyPhpAdmin interface to a mysql database from php.radford.edu. A later lecture will cover the common php functions like mysqli_query and mysqli_connect, to work with a database from within your program.

• Setting up a MySQL database on your RU account:
  • Go to php.radford.edu > MySQL hosting page.
  • Follow the instructions for 'Request/create a MySQL database'.
  • Once you have set up your MySQL account, you can write php programs that save/retrieve info from your database.
  • If you want to interact with your database via MySQL Admin Tool, you can follow links to php.radford.edu > MySQL hosting page > RU Mysql Admin Tool.
  (Note that if you want to connect to the dept Oracle server you used in itec340, instead of a MySQL database, here is an example php program which does so.)
• Constraints in mysql

  Warning: In order to get a Foreign Key constraint, you must create both tables involved with the Storage Engine option as "InnoDB". (Sadly, on php.radford.edu, this isn't installed, and you have to settle for the MyISAM engine which doesn't allow Foreign Key constraints.) Then, after the table is created, you can add the FK constraint one of two ways:

  • Via a raw SQL command: In yourTable > SQL, type in the exact SQL command, such as:

    -- To create a FK alter table Goo add constraint Goo_ij_FK Foreign key Goo(i,j) references Foo(m,n) -- To delete: alter table Goo drop foreign key Goo_ij_FK -- it seems odd to me, to say 'drop foreign key ...' rather than 'drop constraint ...'.

    If the table wasn't created with the InnoDB engine, then it silently discards any FK constraints you specify (!!).
  • Via the GUI: In yourTable > Structure > Relation View, in the column to the right of internal, you can set the foreign key. (This column won't appear for tables not created with the InnoDB engine.) You can't use the GUI approach if your key involves a composite key.

  Warning: MySQL ignores check constraints — it lets you specify them, but ignores them w/o letting you know(!).

  Rumor: I saw one reasonable-sounding post that claimed that you also have to build an index on both the source and destination key-columns.

  tip: If googling problems, stackoverflow tends to have better-curated answers than most sites. (e.g. this one)

home—lects—hws
D2L—breeze (snow day)

©2014, Ian Barland, Radford University Last modified 2014.Oct.24 (Fri)

Please mail any suggestions (incl. typos, broken links) to ibarlandradford.edu