Help with table / select box.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
JustPlainJef
Forum Commoner
Posts: 42
Joined: Tue Jan 04, 2011 5:04 am
Location: McHenry, IL

Help with table / select box.

Post by JustPlainJef »

Hello everyone. I'm working on a website for a youth softball organization, and starting the conversion from straight HTML to PHP / MySQL. I'm trying to make it possible to reschedule rainout games, but I want to do it with select boxes for ease of use. I have most of the code together, but I can't get the select box to populate on subsequent lines...

This is my first code posting, so let's hope I do a decent job...

Right now, the table headings are as follows, and work fine:
Date | Time | Field | Division | Away Color | Away Manager | Home Color | Home Manager.

If the games are rained out, the only thing that will need an update will be the first three columns, as they same teams will still be playing each other. So here's my code.

Code: Select all

<?php
	$result = mysqli_query($cxn,$schedulequery) #pulls the schedule
	     or die ("Couldn't execute query.");	
	$fieldresult = mysqli_query($cxn,$fieldquery) #pulls the list of fields
	     or die ("Couldn't execute field query.");
	echo "<H1>2011 Pigtail Selectable Schedule</H1>\n"; 
	echo "<table border='1' cellpadding='5'>\n";
	while($row = mysqli_fetch_assoc($result))
	      {
		extract ($row);
		$f_Date = date("F j", strtotime($Date));  #formats the date from YYYY-MM-DD to April 5
			echo "<tr>\n
			<td>$f_date</td>\n
			<td>$Time</td>\n
				<td><select>";  #creates select box
			      	##reset ($fieldresult);  This didn't work, I tried it in a few locations
				      while($row2 = mysqli_fetch_assoc($fieldresult))  #pulls fields from the list of different fields
		                       		{
					extract ($row2);
	     				echo "<option value=\"$Fieldsel\" ";
						If ($Field == $Fieldsel)    #sets select default to whatever the current field is
								   #$Field is in Schedule, $Fieldsel is the field select table.
						{
						echo " selected = 'selected'";
						} 
						echo ">$Fieldsel</option>\n";
			 		}
				##tried the reset here too
				echo "</select></td>\n  #end select box
			<td>$Division</td>\n
			<td>$AwayColor</td>\n
			<td>$AwayManager</td>\n
			<td>$HomeColor</td>\n
			<td>$HomeManager</td>\n
			</tr>\n";
			}
			##Tried reset here also
  echo "</table><BR /><BR />\n";
?>
When I run it, all lines get select boxes, but only the first line's select box has any data in it. Why aren't the rest of the select boxes populating?

It does default to the proper (current) field. This isn't critical, but it's a nice double-check to see the dates / times / fields before they are changed. And that part of the code works, so I don't think I need to pull it out.

I want to use the query to pull the fields as there's a few fields we use rarely, but I still want them to be available for use. Also, if we need to add another field that's not in the list, I can create a page to add a field, and then it's available in the list.

I haven't figured out how I want to do the date and time yet, but I'll get to those in the future. Having a touch of problem displaying AM / PM time, but having it sort properly. There's plenty of examples out there, so I'll get that taken care of soon.


Thanks for any and all time you can spare!


Jef

EDIT: Forgot the PHP tags. :oops:
User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: Help with table / select box.

Post by Jade »

Where are you assigning the value of $Field? If $Field never has a value then it'll never match the $Fieldsel.
JustPlainJef
Forum Commoner
Posts: 42
Joined: Tue Jan 04, 2011 5:04 am
Location: McHenry, IL

Re: Help with table / select box.

Post by JustPlainJef »

$Fieldsel is a list of the 10 fields we have available to schedule ballgames.
The value of $Field comes from the Schedules table. I'll give an example...

Date | Time | BallField | Division | Away Color | Away Manager | Home Color | Home Manager
April 30 | 10:00 | Petersen Park 3 | Junior | Red | Robinson | Blue | Baylor
May 1 | 12:00 | Fox Ridge 2 | Instructional | Green | Gregory | White | Wilson

These will initially be imported, as we'll have maybe a total of 40 teams with 16 games each.

When this game gets marked as "postponed," It will go to another list. Now, in the third field (BallField) will be a select box, and we have 10 ballfields to choose from. It will default to Petersen Park 3 (as that's what it's currently set to), but can be changed from there. That's why I changed my second $Field to $Fieldsel (for Field selection list, basically).
Originally, I would have had "If $Field == $Field."

In the above example, the May 1st game will have a select box for the Ballfield, but it will be empty.

So if I get 3 games postponed, the first will have the list of all of the ballfields, the rest will have an empty select box.

(I'm also trying to change literal "field" to "ballfield" to hopefully prevent a little confusion).
User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: Help with table / select box.

Post by Jade »

Have you checked to make sure $Field has the same values as $Fieldsel? I would print both the value of $Field and $Fieldsel in the middle of the <option>.
Neilos
Forum Contributor
Posts: 179
Joined: Fri Nov 19, 2010 2:07 am

Re: Help with table / select box.

Post by Neilos »

I think that Jades point was that in your code you don't initialise the variable $Field before you use it in the if statement. Do you initialise this variable in another part of your code?
JustPlainJef
Forum Commoner
Posts: 42
Joined: Tue Jan 04, 2011 5:04 am
Location: McHenry, IL

Re: Help with table / select box.

Post by JustPlainJef »

Jade wrote:Have you checked to make sure $Field has the same values as $Fieldsel? I would print both the value of $Field and $Fieldsel in the middle of the <option>.
Yes, they are both the same value.

That part of the code is working. My web page is actually very messed up at the moment because I'm just learning how to do what I want to do, and I'm doing it in stages, and leaving all of the stuff on the page to go back and reference. Unfortunately, my current production environment doesn't include PHP / MySQL support (I don't think, we are migrating to a new host in the near future).

I made a table to display the full schedule (from the Schedule table)
Date | Time | BallField | Division | Away Color | Away Manager | Home Color | Home Manager
April 30 | 10:00 | Petersen Park 3 | Junior | Red | Robinson | Blue | Baylor
May 1 | 12:00 | Fox Ridge 2 | Instructional | Green | Gregory | White | Wilson

I've made a combo box from the 10 values of the Fields (ballfields) table, which only has the 10 different fields listed.

I've made another table (copy and paste from above) where the first three fields are text boxes, but populated with the current value of the record. This will allow the user to type in a new value.

I made a third table (again, copy / paste) where I'm inserting a select box into the table. It works exactly as I want it to, but only for the first record. The second record in the table has a select box, but it's empty.
Neilos wrote:I think that Jades point was that in your code you don't initialise the variable $Field before you use it in the if statement. Do you initialise this variable in another part of your code?
The variable $Field is pulled from an SQL table. That section of code is working fine. All that does is set the default from the select box of 10 ball fields to the current value of the current record. The problem happens when I go to record #2. One of the loops isn't working properly, as the select boxes in records two through the end are empy.

Let me try again in English...
I have a schedule of hundreds of games. Today, it rains, and we have to cancel all the games. So the 4 games for today get marked as "postpones."
That flag is then queried, and I have a list of just 4 games instead of hundreds. It looks exactly like the normal read-only schedule, except the first three fields (Date, Time, Ballfield) can be edited so the teams can play again in the future.

I'm heading home for the day, I'll be back tomorrow to drop some better examples and (screen shots) if no one else is able to read my mind between now and then, as I seem to be missing something...

:banghead:


P.S. Thanks for trying, guys. I really hope this is going to be my last hurdle for a bit!
JustPlainJef
Forum Commoner
Posts: 42
Joined: Tue Jan 04, 2011 5:04 am
Location: McHenry, IL

Re: Help with table / select box.

Post by JustPlainJef »

OK, I'm back. Did you miss me???

Let's try this again..... I've got an SQL database that I'll populate with the league's schedule. It's called schedule, and looks like this when it's done.

Code: Select all

<?php
	 #Table with schedule
	 $schedulequery = "SELECT * FROM schedule ORDER BY date,time";  
	 $result = mysqli_query($cxn,$schedulequery)
	      or die ("Couldn't execute query.");
	 echo "<H1>2011 Pigtail Schedule</H1>\n";
	 echo "<table border='1' cellpadding='5'>\n";
	 while($row = mysqli_fetch_assoc($result))
		{
		extract ($row);
		$f_Date = date("F j", strtotime($Date));
		echo "<tr>\n
		<td>$f_Date</td>\n
		<td>$Time</td>\n
		<td>$Field</td>\n
		<td>$Division</td>\n
		<td>$AwayColor</td>\n
		<td>$AwayManager</td>\n
		<td>$HomeColor</td>\n
		<td>$HomeManager</td>\n
		</tr>\n";
		}
  echo "</table><BR /><BR />\n";
?>
That makes it look like this.
Image

I've also got a table that just lists all of the fields we have available to us. I've figured out how to make a drop down list. It looks like this.

Code: Select all

<?php
	##Drop down with field names
	$fieldquery = "SELECT * FROM fields ORDER BY Fieldsel";  
	$fieldresult = mysqli_query($cxn,$fieldquery)
		or die ("Couldn't execute field query.");
	echo "<select>";
	while($row = mysqli_fetch_assoc($fieldresult))
		{
		extract ($row);
		echo "<option value=\"$Fieldsel\">$Fieldsel</option>\n";
		}
	echo "</select>
	<BR /><BR />\n";
?>
Image

When I put them together, here's what I get.....

Code: Select all

<?php
	##Rain out list as select boxes.
	$result = mysqli_query($cxn,$schedulequery)  #pulls the schedule
		or die ("Couldn't execute query.");	
	$fieldresult = mysqli_query($cxn,$fieldquery)  #pulls the list of ballfields
		or die ("Couldn't execute field query.");
	echo "<H1>2011 Pigtail Selectable Schedule</H1>\n"; 
	echo "<table border='1' cellpadding='5'>\n";  #starts the table
	while($row = mysqli_fetch_assoc($result))  
		{
		extract ($row);
		$f_Date = date("F j", strtotime($Date));  #formats the date
		echo "<tr>\n
		<td>$f_Date</td>\n
		<td>$Time</td>\n
			<td><select>";  #starts the select box
				reset ($fieldresult);   #I think this is what I need, but I tried it in a few places, and it didn't work
				while($row2 = mysqli_fetch_assoc($fieldresult))  #select box based off of the ballfield listing
					{
					extract ($row2);
	     				echo "<option value=\"$Fieldsel\" ";
					If ($Field == $Fieldsel)  #this sets the default for the ballfield select box to the value of the schedule for that game
						{
						echo " selected = 'selected'";
						} 
				echo ">$Fieldsel</option>\n";
			}
			echo "</select></td>\n  #end select box
		<td>$Division</td>\n
		<td>$AwayColor</td>\n
		<td>$AwayManager</td>\n
		<td>$HomeColor</td>\n
		<td>$HomeManager</td>\n
		</tr>\n";
		}
  echo "</table><BR /><BR />\n";
?>
Image

Only the top select box populates, and I'm not sure why.....
User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: Help with table / select box.

Post by Jade »

The problem is that once you've looped through the list of fields in the first select box you've hit the end of the query results so there's nothing to display in the boxes after the first row. You have two choices on how to fix it:

1) Put all of the available fields into an array and loop through the values of the array each time you display a table row.

2) Run a query to select all available fields inside of the loop you're using to build each row in the table.
JustPlainJef
Forum Commoner
Posts: 42
Joined: Tue Jan 04, 2011 5:04 am
Location: McHenry, IL

Re: Help with table / select box.

Post by JustPlainJef »

Jade wrote:The problem is that once you've looped through the list of fields in the first select box you've hit the end of the query results so there's nothing to display in the boxes after the first row. You have two choices on how to fix it:

1) Put all of the available fields into an array and loop through the values of the array each time you display a table row.

2) Run a query to select all available fields inside of the loop you're using to build each row in the table.
I just figured that out! I moved my $fieldresult query inside the first While loop. It all worked except the last one. Imagine my surprise when, if you look closely at my previous pictures, the last game was at Fox Ridge 2, which wasn't in my list of ball fields. :oops:

So I guess I did #2. What would be the steps to accomplish #1? Would that be one of them there fetch functions, and a reset after each one? I'm just figuring out that dumping a MySQL query into a variable doesn't make it an array. Who knew???
:oops: :cry:

Thanks so much for your time Jade! :bow:
User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: Help with table / select box.

Post by Jade »

You would do something like this:

Code: Select all

//load all of the names into an array, this is outside of your table loop
$loop = mysql_query("SELECT name FROM fields") or die (mysql_error());

while ($row = mysql_fetch_array($loop))
     $fieldNames[] = $row['name'];


//then inside of your loop to display the select drop down boxes
echo "<select name=whatever>";
for ($i = 0; $i < count($fieldNames); $i++)
{
          echo "<option value=" .$fieldNames[$i];
          if ($selectedField == $fieldNames[$i])
             echo " selected";
          echo ">$fieldNames[$i]</option>";
}
echo "</select>";
JustPlainJef
Forum Commoner
Posts: 42
Joined: Tue Jan 04, 2011 5:04 am
Location: McHenry, IL

Re: Help with table / select box.

Post by JustPlainJef »

OK. That mostly made sense, and seemed pretty clean. I'll look at it again in the near future.

I'm a couple of weeks into the programming, so I'm happy that I'm figuring most of the stuff out on my own, but I'm very grateful for outside help when I need it. Thanks again for your patience and grace, Jade!
turpit
Forum Newbie
Posts: 1
Joined: Mon Jan 17, 2011 8:08 pm

Re: Help with table / select box.

Post by turpit »

I have a question for you Jef. When was the last time you were properly wingdinged?

Sorry for the OT, just trying to track jef down
JustPlainJef
Forum Commoner
Posts: 42
Joined: Tue Jan 04, 2011 5:04 am
Location: McHenry, IL

Re: Help with table / select box.

Post by JustPlainJef »

HOLY CRAP!!! It's (thankfully) been a while, that's for sure.

How are you, man?
Post Reply