Databases
StackOverflow2013
Postsdbo
POCreate dropdown linked to phpmyadmin database
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCreate dropdown linked to phpmyadmin database
    primarykey
    data
    text
    <p>The code below currently adds a new book to a table in a phpMyadmin database called "books" there is a field in that table for the "ModuleCode" which is a foreign key in a table called Modules.</p> <p>I would like it so that the user can select a module code from a drop down menu so that they can only select an existing module to add the book to.</p> <pre><code>&lt;?php require_once('./includes/session_timeout_db.inc.php'); require_once('./includes/logout_db.inc.php'); ?&gt; &lt;?php $hidden = 1; //set viewing level include_once "includes/database.inc.php"; //check if able to view page require "includes/database.inc.php"; ?&gt; &lt;html&gt; &lt;head&gt; &lt;meta http-equiv="Content-Type" content="text/html; charset=UTF-8"&gt; &lt;title&gt;Book&lt;/title&gt; &lt;/head&gt; &lt;body&gt; &lt;h3&gt; Add/Edit Book &lt;/h3&gt; &lt;form action="&lt;?php echo $_SERVER['PHP_SELF'] ?&gt;" method="post"&gt; Book Code: &lt;input type="text" name="fieldOne" /&gt; Book Title: &lt;input type="text" name="fieldTwo" /&gt; Author: &lt;input type="text" name="fieldThree" /&gt; Second Author: &lt;input type="text" name="fieldFour" /&gt; Publisher: &lt;input type="text" name="fieldFive" /&gt; Publish Year: &lt;input type="year" name="fieldSix" /&gt; Module Code: &lt;input type="text" name="fieldEight" /&gt; &lt;input type="submit" name="submit" value="Add/Edit Book" /&gt; &lt;!-- php script to enter values into the books table --&gt; &lt;?php $dsn = "mysql:host=$host;dbname=" . $mysqldatabase; // try to establish connection to db try { $conn = new PDO($dsn, $mysqlusername, $mysqlpassword); $conn-&gt;setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch (PDOException $e) { echo "Connection Failed: " . $e-&gt;getMessage(); } try { //if the fields are set, get data from them if ((isset($_POST["fieldOne"])) &amp;&amp; (isset($_POST["fieldTwo"]))&amp;&amp; (isset($_POST["fieldThree"])) &amp;&amp; (isset($_POST["fieldFour"])) &amp;&amp; (isset($_POST["fieldFive"])) &amp;&amp; (isset($_POST["fieldSix"])) &amp;&amp; (isset($_POST["fieldEight"])) ) { $plainTextOne = strtoupper(trim($_POST["fieldOne"])); $plainTextTwo = ucfirst(strtolower(trim($_POST["fieldTwo"]))); $plainTextThree = ucfirst(strtolower(trim($_POST["fieldThree"]))); $plainTextFour = ucfirst(strtolower(trim($_POST["fieldFour"]))); $plainTextFive = ucfirst(strtolower(trim($_POST["fieldFive"]))); $plainTextSix = ucfirst(strtolower(trim($_POST["fieldSix"]))); $plainTextEight = ucfirst(strtolower(trim($_POST["fieldEight"]))); $disallow = array('"', '-', "'"); $one = str_replace($disallow, '', $plainTextOne); $two = str_replace($disallow, '', $plainTextTwo); $three = str_replace($disallow, '', $plainTextThree); $four = str_replace($disallow, '', $plainTextFour); $five = str_replace($disallow, '', $plainTextFive); $six = str_replace($disallow, '', $plainTextSix); $eight = str_replace($disallow, '', $plainTextEight); } else { $one = ""; $two = ""; $three = ""; $four = ""; $five = ""; $six = ""; $eight = ""; } //if the fields are not all complete, ask user to complete them //NOTE that user is not required to enter a second Author (Author2 can be NULL) if ($one === "" || $two === "" || $three === "" || $five === "" || $six === "") { echo "&lt;br&gt;&lt;br&gt;Please complete all the specified fields to log a new Book or to modify an existing Book's details.&lt;/br&gt;&lt;/br&gt;"; } else { //begin checks on user input $proceed = true; $overwrite = false; //check to see if there already is a book using this book code $sql = "SELECT * FROM books WHERE book_id = \"$one\""; $check = $conn-&gt;query($sql); //if a book with the associated code already exists, overwite with new data if ($check-&gt;rowCount() &gt; 0) { $overwrite = true; } //check length of module title if (strlen($two) &gt; 50) { $proceed = false; echo "&lt;br&gt;&lt;p style = \"color:blue;\"&gt; Book Title is limited to 50 characters. &lt;/p&gt;&lt;/br&gt;"; } // if the data is valid and a book is using the given code, update the row in the book table using the user input data. if ($overwrite == true &amp;&amp; $proceed == true) { $sql = "UPDATE books SET book_title = \"$two\", author1 = \"$three\", author2 = \"$four\", publisher = \"$five\", pub_year = \"$six\", mod_id = \"$eight\" WHERE book_id = \"$one\" "; $conn-&gt;query($sql); echo "&lt;br&gt;&lt;p style = \"color:green;\"&gt;Book with Book Code $one has been editied. Please refer to the table to confirm the changes.&lt;/p&gt; &lt;/br&gt;&lt;/br&gt;"; } else if ($overwrite == false &amp;&amp; $proceed == true) { // if the data is valid, generate the row in the module table using the user input data. $sql = "INSERT INTO books (`book_id`, `book_title`, `author1`, `author2`, `publisher`, `pub_year`, `keywords`, `mod_id`) VALUES(\"$one\", \"$two\", \"$three\", \"$four\", \"$five\", \"$six\" , \"$eight\")"; $conn-&gt;query($sql); echo "&lt;br&gt;&lt;p style = \"color:green;\"&gt;Book with Book Code $one has been added.&lt;/p&gt;&lt;/br&gt;&lt;/br&gt;"; //clear the variables $one = ""; $two = ""; $three = ""; $four = ""; $five = ""; $six = ""; $eight = ""; } } //end try } catch (PDOException $e) { echo "Query failed: " . $e-&gt;getMessage(); } //close the connection $conn = null; ?&gt; &lt;h3&gt; Delete Book &lt;/h3&gt; &lt;form action="&lt;?php echo $_SERVER['PHP_SELF'] ?&gt;" method="post"&gt; Course Code: &lt;input type="text" name="deleteField" /&gt; &lt;input type="submit" name="submitDelete" value="Delete Book" /&gt; &lt;?php //delete a book $dsn = "mysql:host=$host;dbname=" . $mysqldatabase; // try to establish connection to db try { $conn = new PDO($dsn, $mysqlusername, $mysqlpassword); $conn-&gt;setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch (PDOException $e) { echo "Connection Failed: " . $e-&gt;getMessage(); } try { if (isset($_POST["deleteField"])) { $plainTextDelete = strtoupper(trim($_POST["deleteField"])); $disallow = array('"', '-', "'", '&lt;', '&gt;'); $delete = str_replace($disallow, '', $plainTextDelete); } else { $delete = ""; } if ($delete == "") { echo "&lt;br&gt;Please enter the Book Code of the Book you want to delete.&lt;/br&gt;&lt;/br&gt;"; } else { //first check the code exists $sql = "SELECT * FROM books WHERE book_id = \"$delete\" "; $check = $conn-&gt;query($sql); //if the book with the associated code exists in the table, delete. if ($check-&gt;rowCount() &gt; 0) { $sql = "DELETE FROM books WHERE book_id = \"$delete\" "; $conn-&gt;query($sql); echo "&lt;br&gt;&lt;p style = \"color:green;\"&gt;Book with Book Code $delete has been deleted.&lt;/p&gt;&lt;/br&gt;&lt;/br&gt;"; } else { echo "&lt;br&gt;&lt;p style = \"color:blue;\"&gt; Could not find a Book with Book Code $delete &lt;/p&gt;&lt;/br&gt;"; } } //end try } catch (PDOException $e) { echo "Query failed: " . $e-&gt;getMessage(); } //close the connection $conn = null; ?&gt; &lt;!-- script to display all the books in the book table --&gt; &lt;?php $dsn = "mysql:host=$host;dbname=" . $mysqldatabase; // try to establish connection to db try { $conn = new PDO($dsn, $mysqlusername, $mysqlpassword); $conn-&gt;setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch (PDOException $e) { echo "Connection Failed: " . $e-&gt;getMessage(); } $sql = "SELECT * FROM books"; $results = $conn-&gt;query($sql); //if no books have been found, tell the user if ($results-&gt;rowcount() == 0) { echo "&lt;br&gt;", "No Books Found"; //else create table with information from the books table } else { print "&lt;table width=65% style=\"text-align:center;\"&gt;\n"; echo "&lt;th&gt;Book Code&lt;/th&gt;&lt;th&gt;Book Title&lt;/th&gt;&lt;th&gt;Author 1&lt;/th&gt;" . "&lt;th&gt;Author 2&lt;/th&gt;&lt;th&gt;Publisher&lt;/th&gt;&lt;th&gt;Year Published&lt;/th&gt;" . "&lt;th&gt;Module Code&lt;/th&gt;"; foreach ($results as $row) { echo "&lt;tr&gt;"; echo "&lt;td&gt;" . htmlentities($row["book_id"]) . "&lt;/td&gt;"; echo "&lt;td&gt;" . htmlentities($row["book_title"]) . "&lt;/td&gt;"; echo "&lt;td&gt;" . htmlentities($row["author1"]) . "&lt;/td&gt;"; echo "&lt;td&gt;" . htmlentities($row["author2"]) . "&lt;/td&gt;"; echo "&lt;td&gt;" . htmlentities($row["publisher"]) . "&lt;/td&gt;"; echo "&lt;td&gt;" . htmlentities($row["pub_year"]) . "&lt;/td&gt;"; echo "&lt;td&gt;" . htmlentities($row["mod_id"]) . "&lt;/td&gt;"; echo "&lt;td&gt;"; echo "&lt;/tr&gt;\n"; //end foreach loop } print "&lt;/table&gt;\n"; //end else } //close the connection $conn = null; ?&gt; &lt;!-- Links back to Admin Area and Home and logout--&gt; &lt;p&gt;&lt;a href="index.php"&gt;Click here to return to Reading List&lt;/a&gt; &lt;/p&gt; &lt;p&gt;&lt;a href="adminArea_db.php"&gt;Click here to return to Admin Area&lt;/a&gt; &lt;/p&gt; &lt;?php logoutButton();?&gt; &lt;/body&gt; &lt;/html&gt; &lt;?php $hidden = 1; //set viewing level include_once "includes/database.inc.php"; //check if able to view page require "includes/database.inc.php"; ?&gt; &lt;html&gt; &lt;head&gt; &lt;meta http-equiv="Content-Type" content="text/html; charset=UTF-8"&gt; &lt;title&gt;Book&lt;/title&gt; &lt;/head&gt; &lt;body&gt; &lt;?php $mysqli = new mysqli($host, $mysqlusername, $mysqlpassword, $mysqldatabase); // try to establish connection to db if ($mysqli-&gt;connect_error) { die ('Connect Error (' . $mysqli-&gt;connect_errno . ')' . $mysqli-&gt;connect_error); } ?&gt; &lt;form&gt; &lt;select&gt; &lt;option value=""&gt;--- Select ---&lt;/option&gt; &lt;?php $list = $mysqli("SELECT * FROM 'books'"); while($row_list= mysql_fetch_assoc($list)){ ?&gt; &lt;option value="&lt;? echo $row_list['mod_id']; ?&gt;"&gt; &lt;?echo $row_list['mod_id'];?&gt; &lt;/option&gt; &lt;? } ?&gt; &lt;/select&gt; &lt;/form&gt; &lt;/body&gt; &lt;/html&gt; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser3010383
    1. USuser3010383
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COyou just need to get your list of existing module from a DB query. Then echo the results in a <select> html section.
      singulars
      1. POCreate dropdown linked to phpmyadmin database
      1. USjdcookie
    2. COThere is nothing named PHPMyAdmin Database!
      singulars
      1. POCreate dropdown linked to phpmyadmin database
      1. USSaidbakR
    3. COCookie_J5 - What would a <select> html section look like?
      singulars
      1. POCreate dropdown linked to phpmyadmin database
      1. USuser3010383
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload