SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Oct 2013
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Retrieve mysql data from drop-down list

    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
    Code:
    <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
    Code:
     // 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>

  2. #2
    SitePoint Addict bronze trophy Hall of Famer's Avatar
    Join Date
    Apr 2013
    Location
    Ithaca
    Posts
    370
    Mentioned
    6 Post(s)
    Tagged
    2 Thread(s)
    Well its always a pain to work with HTML dropdown list whose options come with MySQL, use my GUI API to make things much much easier:
    http://www.phpclasses.org/package/78...matically.html

  3. #3
    SitePoint Member
    Join Date
    Oct 2013
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would use your API, but this is an assignment for class and I don't think it would be accepted. I will try your API though and see if it help me understand what I'm doing wrong.

    Thank you!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •