Hello

I am learning PHP. I need help with the following code:

I have a phpmyadmin database with following fields: id, coursename, coursecode, coursedescription, instructor , courseyear , coursesemester,building, room.

I want to create a php page including a basic drop-down menu to sort the phpmyadmin table.

So far, I have created the drop-down menu form.
I have written the php code that displays all records within the database.

However I couldn`t tie the drop-down menu to the actual table records. Meaning that, when I pick a category from the drop-down menu(lets say I want to list courses in fall2008 or I want to show courses that John Doe teaches) it doesnt work. It always shows all records in the database.

I will really appreciate if you can help me out.

Best,

database connection part

<?php

session_start();
include_once("x.php");
$db = new Database("localhost", "xxx", "yyy", "zzz")or trigger_error(mysql_error(),E_USER_ERROR); ?>

form part:

    <div class="pulldown">
    <form method="GET" action="<?=$self?>" name="courseselect" >
    Courses by: 
    <select id="q" name="q">
        <optgroup label="Semesters">
            <option value="fall2008" >Fall</option>
            <option value="spring2008" >Spring</option>
            <option value="summer2008" >Summer</option>
        </optgroup>
        <optgroup label="Instructors">
            <option value="john">John Doe</option>
            <option value="mary">Mary White</option>
            <option value="tom">Tom Sawyer</option>
        </optgroup>
    </select>
    <input type="submit" value="show »" />
    </form>
    </div>

php part:

<?

$result = mysql_query("SELECT DISTINCT id, coursename, coursecode, coursedescription, instructor, courseyear, coursesemester, building, room FROM coursesnew WHERE crse IN ( '2100', '2102', '2104') ORDER BY crse ASC;");

while( $c = mysql_fetch_object($result)) {
	printf( '<div class="info">
              <br><h2>%s%s - %s</h2><br>
	               %s <br />
	</div>',
	        $c->coursename, $c->coursecode, $c->instructor, $c->room);
}

?>

    if (isset($_POST['q'])) {
	if ($_POST['q'] == '2008') {
		$criteria = "WHERE courseyear = 2008";
	}
	if ($_POST['q'] == 'joe') {
		$criteria = "WHERE instructor = 'joe'";
	}
	//...etc
} else {
	$criteria = null;
}

$query = "SELECT * FROM coursesnew {$criteria} ORDER BY crse ASC";
$result = mysql_query($query);

    if the search form is submitted it will narrow down the search, hope that helps.

      Thanks for your reply coldwerturkey.

      I guess I understand your logic but unfortunately it doesnt parse anything out. I dont know what am I doing wrong :queasy:

        postmanager wrote:

        I guess I understand your logic but unfortunately it doesnt parse anything out. I dont know what am I doing wrong

        After having incorporated coldwerturkey's suggestions, what have you done? That is, what is your current code?

        Please post your properly formatted code in [php][/php] bbcode tags.

          Here is the last version of the code with coldwerturkey`s updates:

          <?php 

session_start();
include_once("x.php");
$db = new Database("localhost", "xxx", "yyy", "zzz")or trigger_error(mysql_error(),E_USER_ERROR); ?>


<div class="pulldown">
<form method="GET" action="<?=$self?>" name="courseselect" >
Courses by: 
<select id="q" name="q">
<optgroup label="Semesters">
<option value="fall2008" >Fall</option>
<option value="spring2008" >Spring</option>
<option value="summer2008" >Summer</option>
</optgroup>
<optgroup label="Instructors">
<option value="john">John Doe</option>
<option value="mary">Mary White</option>
<option value="tom">Tom Sawyer</option>
</optgroup>
</select>
<input type="submit" value="show »" />
</form>
</div>


<?php

$query = "SELECT * FROM coursesnew {$criteria} ORDER BY coursename ASC";
$result = mysql_query($query); 


while( $q = mysql_fetch_object($result)) {
printf( '<div class="info">
<br>%s<br>
%s <br>
%s<br>
%s <br>
</div>',
$q->coursename, $q->coursecode, $q->instructor, $q->room);
}

if (isset($_POST['q'])) {
    if ($_POST['q'] == '2008') {
        $criteria = "WHERE courseyear = 2008";
    }
    if ($_POST['q'] == 'joe') {
        $criteria = "WHERE instructor = 'joe'";
    }

} else {

$criteria = null;

}
?>

            One reason could be that your form submission method is get, but you are using $POST. Use $GET instead.

              🙁 I have changed all $POST with $GET. Didnt work either...

                Note that since you are building the query, clearly, it has to come before you execute the query.

                  Sorry, I am really novice in PHP.
                  What do you mean by "it has to come before you execute the query."
                  Would you mind changing it on the code?

                    Do you understand that you are building the query here:

                    $query = "SELECT * FROM coursesnew {$criteria} ORDER BY coursename ASC";

                    and then executing it on the next line:

                    $result = mysql_query($query);

                    Now, you want to add more stuff to build the query. This must come before you execute the query, since PHP is an imperative programming language: a PHP script just does what you tell it to do, in the order you tell it.

                      Write a Reply...