Drop down menu with php

[dr. bob]

Hello,

I'm pulling classes from a database and displaying them in dropdown boxes for the users to choose from. I need to be able to take the courseIDs(in my database) from the courses they pick and store them into a table called account_courses. I also wanted the drop-down boxes to start out blank so if they didn't want to add a class in that box they didn't have to. I'm not sure how to implement the variables so I can take in the courseID with accountID. Here is my code:

<?php
/**
*/

class addCoursePage extends webPage {
	protected function content() {
	$accountID = ( isset( $_GET['accountID'] ) ? $_GET['accountID'] : 0 );
	$query = "SELECT courseName FROM courses";
	$result  = query($query);
	if($result->num_rows > 0) {
		$temp = $result->fetch_assoc();
	}
		else {
			echo "we have no data";
			exit;
		}

?>
<h1>Select a Course to Add</h1>
	<div id="messageBox"></div>
	<form id="addCoursePageForm" method="POST" action="?accountID=<?php echo $accountID;?>">
		<table>
			<tr>
				<td>Course 1</td>
				<td>	
	<select>
	<?php
		$query = "SELECT * FROM courses";
		$result = query($query);
		for($i=0; $i < $result->num_rows; $i++) {
			$temp2 = $result->fetch_assoc();
			echo "<option value=\"$temp2[courseID]\">$temp2[courseName]</option>";
		}
	?>
	</select></td>
			</tr>
			<tr>
				<td>Course 2</td>
				<td>	
	<select>
	<?php
		$query = "SELECT * FROM courses";
		$result = query($query);
		for($i=0; $i < $result->num_rows; $i++) {
			$temp2 = $result->fetch_assoc();
			echo "<option value=\"$temp2[courseID]\">$temp2[courseName]</option>";
		}
	?>
	</select></td>
			</tr>
			<tr>
				<td>Course 3</td>
				<td>	
	<select>
	<?php
		$query = "SELECT * FROM courses";
		$result = query($query);
		for($i=0; $i < $result->num_rows; $i++) {
			$temp2 = $result->fetch_assoc();
			echo "<option value=\"$temp2[courseID]\">$temp2[courseName]</option>";
		}
	?>
	</select></td>
			</tr>
			<tr>
				<td>Course 4</td>
				<td>	
	<select>
	<?php
		$query = "SELECT * FROM courses";
		$result = query($query);
		for($i=0; $i < $result->num_rows; $i++) {
			$temp2 = $result->fetch_assoc();
			echo "<option value=\"$temp2[courseID]\">$temp2[courseName]</option>";
		}
	?>
	</select></td>
			</tr>
			<tr>
				<td>Course 5</td>
				<td>	
	<select>
	<?php
		$query = "SELECT * FROM courses";
		$result = query($query);
		for($i=0; $i < $result->num_rows; $i++) {
			$temp2 = $result->fetch_assoc();
			echo "<option value=\"$temp2[courseID]\">$temp2[courseName]</option>";
		}
	?>
	</select></td>
			</tr>
			<tr>
				<td>Course 6</td>
				<td>	
	<select>
	<?php
		$query = "SELECT * FROM courses";
		$result = query($query);
		for($i=0; $i < $result->num_rows; $i++) {
			$temp2 = $result->fetch_assoc();
			echo "<option value=\"$temp2[courseID]\">$temp2[courseName]</option>";
		}
	?>
	</select></td>
			</tr>
			<tr>
				<td>Course 7</td>
				<td>	
	<select>
	<?php
		$query = "SELECT * FROM courses";
		$result = query($query);
		for($i=0; $i < $result->num_rows; $i++) {
			$temp2 = $result->fetch_assoc();
			echo "<option value=\"$temp2[courseID]\">$temp2[courseName]</option>";
		}
	?>
	</select>
	</td>
			</tr>
				<td>&nbsp;</td>
				<td><input type="submit" value="Add Courses!" /></td>
			</tr>
		</table>
	</form>
<?php
	}
}
?>

[Dan Grossman]

Give the <select> tags names so that their values appear in $_POST after the form is submitted.

Make the first <option> in the <select> blank if you don't want to require a course to be chosen.

<option value=""></option>

And make sure you check if $_POST['course1'] or whatever you call the select element is empty or not before inserting it into the database.

You will need to make the account ID a hidden input in the form, since you can't mix POST and GET like you're trying to do with that form action.

<input type="hidden" name="accountID" value="<?php echo $accountID; ?>">

[dr. bob]

Thank you so much. That really helped. I'm still having some problems though. I have 3 tables: accountInformation, courses, and account_courses. I made the table account_courses to clear up a many to many. I can't seem to figure out how to write to it though.


accountInformation Table

CREATE TABLE accounts(
	accountID int primary key not null auto_increment,
	username varchar(25),
	password varchar(20),
	email varchar(30),
	firstname varchar(20),
	lastname varchar(20),
	lastLogin timestamp DEFAULT 0);

courses Table

CREATE TABLE courses(
	courseID int primary key not null auto_increment,
	courseName varchar(25),
	courseDescription text,
	courseDepartment varchar(4),
    courseStartTime time,
	courseEndTime time,
	courseSemester varchar(20),
	courseBuilding varchar(20),
	courseRoomNumber int
);

account_courses table

CREATE TABLE account_courses(
	accountID INT NOT NULL, 
	courseID INT NOT NULL,
	foreign key(accountID) references accountInformation(accountID),
	foreign key(courseID) references courses(courseID), 
	primary key(accountID,courseID)
);

This is my php file where I am querying to get account and courses to write into the table account_courses. I'm trying to link them together so I can take care of a many to many.

<?php
/**
*/

	require_once 
	$accountID = ( isset( $_GET['accountID'] ) ? $_GET['accountID'] : 0 );
	
	/* short variable */
	//$accountID  		= $_POST['accountID'];
	$courseID           = $_POST['course1'];

	$query = "INSERT INTO account_courses VALUES('courseID')";
	$result = query($query);
	

	/*Return to Scheduler*/
	echo '<a href="/1.1/addCoursePage">Add Another Course</a>.';	
	
	/* error checking */

[Dan Grossman]

$query = "INSERT INTO account_corses (accountID, courseID) VALUES ($accountID, $courseID)"; 

[dr. bob]

That's great thanks for all your help!


I want to display the list of courseName(s) on the account holders profile. I made a select statement:


SELECT courseName from courses WHERE courseID IN (SELECT courseID from account_courses WHERE accountID=$accountID);


I tried to put it under My Schedule in my profile page but keep getting errors. Do I need to display this as an array also?


Profile Page

<?php
/*
*/

class profilePage extends webPage {
	protected function content() {
		$accountID = ( isset( $_GET['accountID'] ) ? $_GET['accountID'] : 0 );
		$query = "SELECT * FROM student WHERE accountID=$accountID";
		$result  = query($query);
		if($result->num_rows > 0) {
			$userInfo = $result->fetch_assoc();
		}
		else {
			echo "we have no data";
			exit;
		}
?>
	<div id="Welcome">	
		<h1>Welcome to <?php echo $userInfo['firstName'];?> <?php echo $userInfo['lastName']; ?>'s Profile!</h1>
		<!--<img src="" alt="Profile Picture" id="profilePic" />_-->
		<h3><?php echo $userInfo['firstName']?> <?php echo $userInfo['lastName']?> <?php echo $userInfo['status'];?></h3>
		<a href="Update_Status?accountID=<?php echo $accountID?>"> Update Status</a>
	</div>
	
	<div id="About">	
		<h2>About Me</h2>
		<p><?php echo $userInfo['about']; ?></p>
	<div>

	<a href=messageid="msgMe"><font color="black">Message Me!</font></a>	
		
	<div id="Schedule">
		<h2>My Schedule</h2>
		<div>
			<a href="addCoursePage?accountID=<?php echo $accountID; ?>">Add a Course</a>
		</div>	
		<p><ul>
		<li>class 1</li>
		<li>class 2</li>
		<li>class 3</li>
		<li>class 4</li>
		<li>class 5</li>
		</ul></p>
	<div>
	
	<div id="Intrests">
		<h2>Intrestes</h2>
		<p><ul>
		<li>Music: <?php echo $userInfo['music'];?></li>
		<li>Movies: <?php echo $userInfo['movies'];?></li>
		<li>Hobbies: <?php echo $userInfo['hobbies'];?></li>
		</ul></p>
	</div>
	
	<div id="Details">
		<h2>Details</h2>
		<p><ul>
		<li>Birthday: <?php echo $userInfo['birthday'];?></li>
		<li>Relationship Status: <?php echo $userInfo['relateStat'];?></li>
		<li>Home town:  <?php echo $userInfo['homeTown'];?></li>
		<li>Greek: <?php echo $userInfo['greek'];?></li>
		<li>Organizations: <?php echo $userInfo['otherOrgs'];?></li>
		</ul></p>
	</div>
	
	<div id="Contact">
		<h2>Contact Info</h2>
		<p><ul>
		<li>E-mail: <?php echo $userInfo['email'];?></li>
		<li>Instant Messenger: <?php echo $userInfo['instantMsngr'];?></li>
		<li>Phone: <?php echo $userInfo['landLine'];?></li>
		<li>Cell Phone: <?php echo $userInfo['cell'];?></li>
		<li>Address: <?php echo $userInfo['address'];?></li>
		<li>School Residence: <?php echo $userInfo['schoolRes'];?></li>
		<li>Campus Box: <?php echo $userInfo['campusBox'];?></li>
		</ul></p>
	</div>
	
		<div>
			<a href="editProfile?accountID=<?php echo $accountID; ?>">Edit Profile</a>
		</div>
		
		<div>
			<a href="<?php echo EVENT?>">Create Event</a>
		</div>
		
		<div>
			<a href="<?php echo DISPLAYEVENT?>">Display Events</a>
		</div>

	
<?php
	}
}
?>

courses SQL script

CREATE TABLE courses(
	courseID int primary key not null auto_increment,
	courseName varchar(25),
	courseDescription text,
	courseDepartment varchar(4),
    courseStartTime time,
	courseEndTime time,
	courseSemester varchar(20),
	courseBuilding varchar(20),
	courseRoomNumber int
);

[Dan Grossman]

SELECT courseName from courses WHERE courseID IN (SELECT courseID from account_courses WHERE accountID=$accountID);


I tried to put it under My Schedule in my profile page but keep getting errors. Do I need to display this as an array also?

What's the error? What code are you using to display the results of that query?

[dr. bob]

I get a parse error on line 38.

<div id="Schedule">
		<h2>My Schedule</h2>
		<div>
			<a href="addCoursePage?accountID=<?php echo $accountID; ?>">Add a Course</a>
		</div>	
		<?php
		$query = SELECT courseName from courses WHERE courseID IN (SELECT courseID from account_courses WHERE accountID=$accountID);
		$result  = query($query);
		if($result->num_rows > 0) {
			$temp = $result->fetch_assoc();
			}
			?>
		<p><ul>
		<li><?php echo $temp['course1'] ?></li>
		<li>class 2</li>
		<li>class 3</li>
		<li>class 4</li>
		<li>class 5</li>
		</ul></p>
	<div>

[Dan Grossman]

You forgot to put quotes around your query. It's not PHP code

Also, you will need to loop over the results, and the column name is going to be "courseName", not "course1".

[dr. bob]

Yeah your right I forgot my quotes. I fixed it and took out my php tags but now its just showing my code on the page


My Schedule

Add a Course
$query = "SELECT courseName from courses WHERE courseID IN (SELECT courseID from account_courses WHERE accountID=$accountID)"; $result = query($query); if($result->num_rows > 0) { $temp = $result->fetch_assoc(); } else { echo "we have no data"; exit; }


Notice: Undefined variable: temp in C:\wamp\www\1.1\lib\profilePagephpinc on line 48
* class 2
* class 3
* class 4
* class 5

	<div id="Schedule">
		<h2>My Schedule</h2>
		<div>
			<a href="addCoursePage?accountID=<?php echo $accountID; ?>">Add a Course</a>
		</div>	
		$query = "SELECT courseName from courses WHERE courseID IN (SELECT courseID from account_courses WHERE accountID=$accountID)";
		$result  = query($query);
		if($result->num_rows > 0) {
			$temp = $result->fetch_assoc();
			}
				else {
			echo "we have no data";
			exit;
		}
			
		<p><ul>
		<li><?php echo $temp['course1'] ?></li>
		<li>class 2</li>
		<li>class 3</li>
		<li>class 4</li>
		<li>class 5</li>
		</ul></p>
	<div>

[Dan Grossman]

You need those PHP tags.. it's the SQL query that isn't PHP code, it needed to be in quotes because it's a string.

[dr. bob]

Ok I see. I'm new to using php and mysql. I would really like to have the course name be a link that takes you to that specific course page but i'm not sure how to associate the name of the course with a link. Also what did you mean about looping though earlier?

[Dan Grossman]

You will have one row in this table per course the user is registered to. You need a loop to retrieve all of the rows you selected. Your current code only retrieves the first row, which will only give you one course they're registered for.

while ($row = mysql_fetch_array($result)) {
  echo "Course you are registered for: " . $row['courseName'] . "<br />";
} 

You should select the courseID in addition to the name in your query so that you can use it in your link.

[dr. bob]

Ok so I should place this right under result.


i get this error.


Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\wamp\www\1.1\lib\profilePagephpinc on line 40

<div id="Schedule">
		<h2>My Schedule</h2>
		<div>
			<a href="addCoursePagephp?accountID=<?php echo $accountID; ?>">Add a Course</a>
		</div>	
		<?php
		$query = "SELECT courseName from courses WHERE courseID IN (SELECT courseID from account_courses WHERE accountID=$accountID)";
		$result  = query($query);
		while ($row = mysql_fetch_array($result)) {
  echo "Course you are registered for: " . $row['courseName'] . "<br />";
}
		if($result->num_rows > 0) {
			$temp = $result->fetch_assoc();
			}
				else {
			echo "we have no data";
			exit;
		}
		?>
		<?php 
		
?>
		<p><ul>
		<li><?php echo $temp['courseName'] ?></li>
		<li>class 2</li>
		<li>class 3</li>
		<li>class 4</li>
		<li>class 5</li>
		</ul></p>
	<div>

[Dan Grossman]

Is query() some function you wrote?

Is $accountID defined somewhere before the code you're showing?

That error indicates the SQL query did not return a result set.

[dr. bob]

It was working until I added the loop part. Here's the rest of my code(above). accountID is defined.

<?php
/*
*/

class profilePage extends webPage {
	protected function content() {
		$accountID = ( isset( $_GET['accountID'] ) ? $_GET['accountID'] : 0 );
		$query = "SELECT * FROM student WHERE accountID=$accountID";
		$result  = query($query);
		if($result->num_rows > 0) {
			$userInfo = $result->fetch_assoc();
		}
		else {
			echo "we have no data";
			exit;
		}
?>
	<div id="Welcome">	
		<h1>Welcome to <?php echo $userInfo['firstName'];?> <?php echo $userInfo['lastName']; ?>'s Profile!</h1>
		<!--<img src="" alt="Profile Picture" id="profilePic" />_-->
		<h3><?php echo $userInfo['firstName']?> <?php echo $userInfo['lastName']?> <?php echo $userInfo['status'];?></h3>
		<a href="Update_Statusphp?accountID=<?php echo $accountID?>"> Update Status</a>
	</div>
	
	<div id="About">	
		<h2>About Me</h2>
		<p><?php echo $userInfo['about']; ?></p>
	<div>

	<a href=messagehtml id="msgMe"><font color="black">Message Me!</font></a>	
		
	<div id="Schedule">
		<h2>My Schedule</h2>
		<div>
			<a href="addCoursePagephp?accountID=<?php echo $accountID; ?>">Add a Course</a>
		</div>	
		<?php
		$query = "SELECT courseName from courses WHERE courseID IN (SELECT courseID from account_courses WHERE accountID=$accountID)";
		$result  = query($query);
		while ($row = mysql_fetch_array($result)) {
  echo "Course you are registered for: " . $row['courseName'] . "<br />";
}
		if($result->num_rows > 0) {
			$temp = $result->fetch_assoc();
			}
				else {
			echo "we have no data";
			exit;
		}
		?>
		<?php 
		
?>
		<p><ul>
		<li><?php echo $temp['courseName'] ?></li>
		<li>class 2</li>
		<li>class 3</li>
		<li>class 4</li>
		<li>class 5</li>
		</ul></p>
	<div>

[Dan Grossman]

Since I'm still working in the blind, I didn't know your query() function returned an object. And since I don't know that object's interface, I can't write code against it.

Perhaps someone else will step in to help you further, back to my own work now.

[dr. bob]

Alright. Thank you so much for getting me this far. I'm really excited about it.

[dr. bob]

query()

name: query
description: connects to a given database stored in the DBADDRESS constant, executes a given query,
closes the database, and returns the result of the query as an array
parameters: 1. $query -- a valid SQL query
return: $result -- query results in an array

function query($query) {
	@ $db = new mysqli(DBADDRESS, DBUSER, DBPASSWORD, DBNAME);
	
	try {
		if(mysqli_connect_errno()) {
			throw new systemUnavailableException();
		}
	}
	catch (systemUnavailableException $e) {
		echo $e; 
		exit;
	}
	$result = $db->query($query);
	$db->close();
	return $result;
}

If that helps. That is what the query function does.

[dr. bob]

So basically I need to loop through my array so i can retrieve the next rows. The loop I put in gives me this error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\wamp\www\1.1\lib\profilePagephpinc on line 51

Can anyone help me loop through my array.

	<div id="Schedule">
		<h2>My Schedule</h2>
		<div>
			<a href="addCoursePagephp?accountID=<?php echo $accountID; ?>">Add a Course</a>
		</div>	
		<?php
		$query = "SELECT courseName from courses WHERE courseID IN (SELECT courseID from account_courses WHERE accountID=$accountID)";
		$result = query($query);

		if($result->num_rows > 0) {
			$temp = $result->fetch_assoc();
			}
				else {
			echo "we have no data";
			exit;
		}
		?>

		<?php 
		while ($row = mysql_fetch_array($result)) {
  echo "Course you are registered for: " . $row['courseName'] . "<br />";
}
		
?>
		<p><ul>
		<li><?php echo $temp['courseName'] ?></li>
		<li><?php echo $temp['courseName'] ?></li>
		<li>class 3</li>
		<li>class 4</li>
		<li>class 5</li>
		</ul></p>
	<div>

Here is my _addCoursePage.php

<?php
/**
*/

	require_once './lib/libsphpinc';
	$accountID = ( isset( $_GET['accountID'] ) ? $_GET['accountID'] : 0 );
	
	/* short variable */
	$accountID  		= $_POST['accountID'];
	$courseID           = $_POST['course1'];
	$couresID2			= $_POST['course2'];

	$query = "INSERT INTO account_courses (accountID, courseID) VALUES ($accountID, $courseID)"; 

	$result = query($query);
	

	/*Return to Scheduler*/
	echo '<a href="/1.1/addCoursePagephp">Add Another Course</a>.';	
	
	/* error checking */

I want to be able to click on the courses that were assigned to the account so it takes me to a course page.

[oddz]

        <?php 
        while ($row = mysqli_fetch_array($result)) {
  echo "Course you are registered for: " . $row['courseName'] . "<br />";
}
        
?>

mysqli_fetch_array()

[dr. bob]

Ok.. There are only a couple of things that I need help with. When I display the courses from the database, the first course I add to my course database never shows up. The rest of the entries do after the 1st one. How can I correct this.

Another question I had was how I could make all of my course pages into links with the courseID. So the user could just click their class and be taken to the page. I wasn't sure how to to echo "<li>" . $row['courseName'] . "<br /></li>"; change it so I could see links to every class.

<div id="Schedule">
		<h2>My Schedule</h2>
		<div>
			<p><a href="addCoursePagephp?accountID=<?php echo $accountID; ?>">Add a Course</a></p>
			<p><a href="createCoursePagephp?accountID=<?php echo $accountID;?>">Create a New Course Here</a></p>
		</div>	
		<div>
		<?php
		$query = "SELECT courseName from courses WHERE courseID IN (SELECT courseID from account_courses WHERE accountID=$accountID)";
		$result = query($query);

		if($result->num_rows > 0) {
			$temp = $result->fetch_assoc();
			}

		?>
        <p><ul>
		<?php 
					/* Loop to get all rows */
			while ($row = mysqli_fetch_array($result)) {
				echo "<li>" . $row['courseName'] . "<br /></li>";
			
			}
		
		?>
		</ul></p>

	</div>

[Dan Grossman]

Remove these lines, which are pulling out the first row and doing nothing with it, which is why your first course disappears:

    if($result->num_rows > 0) {
    $temp = $result->fetch_assoc();
} 

And add the courseID to what you select in your SQL query, so that you can use it in the links:

$query = "SELECT courseID, courseName from courses WHERE courseID IN (SELECT courseID from account_courses WHERE accountID=$accountID)"; 

Now here's your new echo line to print a link:

echo "<li><a href='course.php?id=" . $row['courseID'] . "'>" . $row['courseName'] . "</a></li>"; 

[dr. bob]

Ok that worked but when I try to click add a course or create course page it just says: we have no data. When I take the else out it say undefined variable. The accountIDs are still being put in the url so I have no idea why its happening.

profile page

	<div id="Schedule">
		<h2>My Schedule</h2>
		<div>
			<p><a href="addCoursePage.php?accountID=<?php echo $accountID; ?>">Add a Course</a></p>
			<p><a href="createCoursePage.php?accountID=<?php echo $accountID;?>">Create a New Course Here</a></p>
		</div>	
		<div>
		<?php
		$query = "SELECT courseID,courseName from courses WHERE courseID IN (SELECT courseID from account_courses WHERE accountID=$accountID)";
		$result = query($query);
		?>
    <p><ul>
		<?php 
					/* Loop to get all rows */
			while ($row = mysqli_fetch_array($result)) {
				echo "<li><a href='course.php?courseid=" . $row['courseID'] . "'>" . $row['courseName'] . "</a></li>"; 	
			}
		?>
	</ul></p>

	</div>

addCoursePage

<?php
/**
*/

class createCoursePage extends webPage {
	protected function content() {
	$accountID = ( isset( $_GET['accountID'] ) ? $_GET['accountID'] : 0 );
	$query = "SELECT courseName FROM courses";
	$result  = query($query);
	if($result->num_rows > 0) {
		$temp = $result->fetch_assoc();
	}
		else {
			echo "we have no data";
			exit;
		}

?>
	<h1>Create a Course</h1>
	<div id="messageBox"></div>
	<form id="createCoursePageForm" method="POST" action="_createCoursePage.php?accountID=<?php echo $accountID;?>">
		<table>
			<tr>
				<td>Course Name</td>
				<td><input type="text" name="courseName" /></td>
			</tr>
			<tr>
				<td>Description</td>
				<td><input type="text" name="courseDescription" /></td>
			</tr>
			<tr>
				<td>Course Department (ex. CSCI)</td>
				<td><input type="number" name="courseDepartment" /></td>
			</tr>
			<tr>
				<td>Start Time</td>
				<td><input type="date" name="startTime" /></td>
			</tr>
			<tr>
				<td>End Time</td>
				<td><input type="date" name="endTime" /></td>
			</tr>
			<tr>
				<td>Semester</td>
				<td><input type="text" name="courseSemester" /></td>
			</tr>
			<tr>
				<td>Building</td>
				<td><input type="text" name="building" /></td>
			</tr>
			<tr>
				<td>Room Number</td>
				<td><input type="number" name="roomNumber" /></td>
			<tr>
			<tr>
				<td>&nbsp;</td>
				<td><input type="submit" value="Create Course Page!" /></td>
			</tr>
		</table>
	</form>
<?php
	}
}
?>

[dr. bob]

I spent the whole weekend trying to figure this out. Every time I click add a course or create a course the page just says: we have no data. It was working fine earlier.