Hello!
I just started learning PHP and MYSQL a few weeks ago and I’m having a little trouble. I’m trying to display data from my database when a user selects an option from a drop-down list. I was able to display an error if the user made no selection, then I added more code to query the database and I started receiving errors once I made a selection.
I’ve been working on this for a couple of days now and I have ran out of ideas. Any help would be greatly appreciated!
Thank you!
HTML
<form method="get" action="booksdb.php">
<table width="600">
<tr>
<td><strong>Option 1</strong>: Select a field to display current book inventory</td>
<td>
<select name="inventory">
<option value="select">Select</option>
<option value="all">All Fields</option>
<option value="id">IDs Only</option>
<option value="title">Titles Only</option>
<option value="category">Categories Only</option>
<option value="isbn">ISBNs Only</option>
</select>
</td>
</tr>
<tr>
<td><strong>OR</strong></td>
</tr>
<tr>
<td><strong>Option 2</strong>: Select an author to display his/her books</td>
<td>
<select name="authorInfo">
<option value="select">Select</option>
<option value="bob">Bob Dobbs</option>
<option value="stephen">Stephen Queen</option>
<option value="noam">Noam Chomsky</option>
<option value="mark">Mark Twain</option>
<option value="eric">eric Applebaum</option>
<option value="anna">Dave Thomas</option>
<option value="jonah">Al Alfred</option>
<option value="trent">Max Headrom</option>
</select>
</td>
</tr>
<tr>
<td><input type="submit" name="submitForm" value="Submit" /></td>
</tr>
</table>
</form>
PHP
// create global vars
$inventory = "";
$author = "";
$query = "";
$error = "";
// Connect to MySQL Server
$dbConnection = mysqli_connect( $dbHost, $dbUser, $dbPassword, $dbName );
if ( mysqli_connect_errno() ) {// if the connection in previous statement failed:
die( "Could not connect to the database server: " .
mysqli_connect_error() . " " . mysqli_connect_errno() .
"</body></html>" );
//die () : quit or exit the program completely after displaying the error3 and the actual error
}
// get values from selection
if ($_GET['submitForm']) {
$inventory = $_GET['inventory'];
$author = $_GET['authorInfo'];
$error = validate($inventory,$author);
}
// Build a SELECT query
function validate($inventory,$author) {
if (($inventory == 'select') && ($author == 'select')) {
return "You must select an option!";
}
else if (($inventory != 'select') && ($author != 'select')) {
return "You may only select one option!";
}
elseif($_GET['inventory']== 'all' and $_GET['authorInfo']== 'select'){
$query= "SELECT * FROM books";
//Get all books by selected author
}
elseif($_GET['inventory']== 'select' and $_GET['authorInfo']!= 'select'){
$query= "SELECT books.Title, authors.Name
FROM books, authors, books_authors
WHERE books.ID = books_authors.BID
and authors.ID = books_authors.AID
and Name = '". $_GET['authorInfo'] . "'";
//Field info for specific author
}else{
$query = "SELECT books." . $_GET['inventory'] .
" FROM books, authors, books_authors
WHERE books.ID = books_authors.BID
and authors.ID = books_authors.AID
and Name ='" . $_GET['authorInfo'] . "'";
}
}
// Query the database
if ($query != Null) {
if ( !( $result = mysqli_query($dbConnection, $query) ) )
{
print( "<p>Could not execute query!</p>" );
die( mysqli_error() . "</body></html>" );
} // end if
else {
echo "<p>" . $error . "</p>";
}
}
?>
<table>
<caption><?php if ($query != Null) { echo "Results of " . $query; } else { echo "<p>No Query Selected</p>"; } ?> </caption>
<?php
// Fetch each record in the result set by iterating through each record
if ($query != Null) {
while ( $row = mysqli_fetch_row( $result ) )
{
// build table to display results
print( "<tr>" );
foreach ( $row as $value )
print( "<td>$value</td>" );
print( "</tr>" );
}
}
else {
echo "No results returned.";
}
//Release the returned data to free mysql resources
mysqli_free_result($result);
mysqli_free_result($inventory);
mysqli_free_result($author);
//Close the database connection:
mysqli_close( $dbConnection );
?>
</table>