CSCI 2006 - Spring 2024 - Lab #8

Lab #8 - DBMS MODIFICATION

Purpose: Interact with a DBMS II

We will now be implementing a modification and insertion page for our languages website
The host for accessing the database is localhost (because your script and the database are located on the same server)
The username for accessing the databases used in this course is: csci2006
The password for accessing the databases used in this course is: 9TfP4lxsxH1szKuA
The database name is: csci2006_languages
Copy your (or the key) Lab #7 solution as a base for Lab #8
Add an edit link to the bottom of each page, such that the link will bring the user to a page with a form to enable editing the information related to a programming language
Add a link in the footer to enable the user to go to the same form page, but this time to create a new language page, rather than editing a current one
Modify your Language class to add a function "getEditor" which will create HTML form fields for editing the langauge. Please utilize the HTML/CSS/JavaScript from the example site to produce this form
Ensure that your form is pre-filled with values if editing an existing language, and not pre-filled if creating a new language
Capture the submitted input and prepare it for executing database queries to modfiy the database
If you need help understanding how any of these queries work, please ask the instructor
The SQL you will need to make modifications to the database are as follows:

Add a new language:

INSERT INTO `language`
  (`language_slug`,`language_name`,`language_example`,`language_highlighter`)
  VALUES (?, ?, ?, ?)

Edit a language:

UPDATE `language`
  SET `language_slug`=?,
      `language_name`=?,
      `language_example`=?,
      `language_highlighter`=?
  WHERE `lang_id`=?

Add a new description paragraph:

INSERT INTO `description`
  (`desc_lang`,`desc_ordinal`,`desc_paragraph`)
  VALUES (?, ?, ?)

Edit a description paragraph:

UPDATE `description`
  SET `desc_paragraph`=?
  WHERE `desc_lang`=?
    AND `desc_ordinal`=?

Remove a description paragraph:

DELETE FROM `description`
  WHERE `desc_lang`=?
    AND `desc_ordinal`=?

When working with your descriptions, you may find that you need this query as well

SELECT `desc_ordinal`, `desc_paragraph`
  FROM `description`
  WHERE `desc_lang`=?
  ORDER BY `desc_ordinal`

Test your editing form to ensure that it is properly executing the different SQL code based on the form you submit. Note that the database will periodically reset itself to the original dataset (a database would not normally do this, but since we are all sharing the same dataset, it will be reseting to provide a more consistent starting point for all students)
Upload your PHP file(s) (if you have not been doing that to test your code as you go), to the remote server. Place the file in "public_html/csci2006/lab08/
In a web-browser, go to the URL below
Username:
Link will populate after you enter your username