PHP - Misbehaving Sql-driven Table...whyyyyy!?

I currently have an HTML form where the options for a certain drop-down menu are hard-coded. Instead, I want to use PHP to...

1. Look up the values in a column (cities) in a MySQL table (locations)
2. Make those values the only options in the dropdown menu.

Any ideas how I would do this? This is what I have so far.

<label for="city">What is your destination city?</label>
<select class="form-control" id="city" name="city">

<?php
//connect to the database
$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

//grab the city names from the MySQL table 
$query = "SELECT cities FROM locations";

$res = mysqli_query($dbc, $query);

while ($data = mysqli_fetch_assoc($res)) {
echo '<option value="'.$data['cities'].'">'.$data['cities'].'</option>';
}

//close the db connection
mysqli_close($dbc);
?>

</select> 

I am trying to write out a calendar style html table with data in each cell, and with a column on the left that shows the times.

Basically it should look like this when written out to the webpage:

Time     Mon    Tues     Wed     Thur     Fri     Sat
8:00    data    data2
9:30               data3              data4   
10:00                         data5            data6
11:00  data7              data8

The times on the left will vary each month, and the 'data' (these are actually fitness classes held each month) will appear on various days of the week.

There is no need to toggle from one month to the next and such, it's just a listing of the current month to display the classes held on which days and at which times.
Very easy to do statically in html, but a bit more challenging dynamically.

Using MySQL, the table that holds the data looks like this:

Code: [Select]
schedule_id     class_time     class_title     day_order       
1               08:00           data                1       
2               08:00           data2               3       
3               09:30           data3               2       
4               09:30           data4               4       
5               10:00           data5               3       
6               10:00           data6               5       
7               11:00           data7               1       
8               11:00           data8               3

The day_order field corresponds to the weekday - Mon is 1, Tues is 2, etc.

My query looks like this:

Code: [Select]
SELECT schedule_id, class_time, class_title, day_orderFROM `class_schedule` order by class_time, day_order
Here is the html/ php code to write it out:

Code: [Select]
<table border="0" cellpadding="2" cellspacing="0" width="625">  
<tr>  
<td class="calheader">Time</td>
<td class="calheader">Mon</td>  
<td class="calheader">Tue</td>  
<td class="calheader">Wed</td>  
<td class="calheader">Thu</td>  
<td class="calheader">Fri</td>  
<td class="calheader">Sat</td>  
<td class="calheader">Sun</td>  
</tr>     

<? $i=0;  
$first_time = "";
 
while($i<$num_rows){
 
$sid = mysql_result($sql,$i,"schedule_id");  
$ctime = mysql_result($sql,$i,"class_time");  
$cdate = mysql_result($sql,$i,"CURDATE()");  
$strtime = date("h:i:s",$cdate.$ctime);  
$ctitle = stripslashes(mysql_result($sql,$i,"class_title"));
$order = mysql_result($sql,$i,"day_order");
   
if ($ctime != $first_time){

  if (isset($first_time)){
  echo "";
 }

 if ($first_time == ""){
 
echo "<tr>\n";    
echo "<td class=\"timecell\">". $ctime ."</td>\n";
 }
     
$first_time = mysql_result($sql,$i,"class_time");    
}
     
$j = 1;
 while ($j<8 && $first_time != ""){        
 if ($order == $j){  
 $thetitle = $ctitle;    
} else {    
$thetitle = "&nbsp;";  
}
 
echo "<td class=\"calcell\">". $thetitle ."</td>\n";

 $j++;
 
}

unset($first_time);
if (!isset($first_time))
{  
echo "</tr>\n";
}     
$i++;  
 }  

unset($ctitle);  
unset($thetitle);  
unset($weekday);  
unset($sid);
unset($clink);
unset($order);
mysql_close();
The above code sort of works, but the trouble I am having is that it is creating a new table row for each class, even if they occur at the same time slot. It should be putting them on the same row, to the right of the time slot across the days of the week.

If anyone could assist, that would be wonderful - I am really stuck with this...
Thanks

Hello,

I am making a small CMS and I having some trouble with making a dynamic menu. I am wondering for a few days how I should make this menu system. I have the follow MySQL table:
 
Code: [Select]
CREATE TABLE `pages` (
  `id` int(4) unsigned NOT NULL AUTO_INCREMENT,
  `time` varchar(10) DEFAULT NULL,
  `lastby` varchar(2) DEFAULT NULL,
  `order_id` varchar(2) DEFAULT NULL,
  `text` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
Now I would like to have a menu system as following that I make menu's in a menu table with a name and a menu ID and that I could place those pages as parent or as subparent.

How could I manage to turn my theory into reality?

This topic has been moved to Application Design.

http://www.phpfreaks.com/forums/index.php?topic=351877.0

Hello all;
I have a client that has a members area.  He asked me to password protect it, which I did simply by assigning one static password.  Now he wants a full username/login system where the member can set their own password, which I have never done before.  I assume I'd just set up a Table with three fields, (one for name, one for password, one for the type of access they have) then check against it for access, but experience has taught me that whenever something seems simple, it's actually very complex. 

Do any of you know of any good premade templates for this kind of thing?  Ideally it'd be session-based (obviously).  I found one system he http://frozenade.wordpress.com/2007/11/24/how-to-create-login-page-in-php-and-mysql-with-session/  but it's several years old, and the misspellings in the comments tend to scare me away a bit. 

Thanks for any help you might be able to provide.