Need a single MySQLi drop down select list to insert multiple columns from one table into a form

9/29/2014 7:27 am | #1 Jason

This is a drop down that I currently have, and it's working fine.

<td width="398"><label for="Event">Event:
                <select name="Event" id="Event">
                  <?php
while(!$Event->atEnd()) { //dyn select
?>
                  <option value="<?php echo($Event->getColumnVal("id")); ?>"<?php if (!(strcmp($Event->getColumnVal("id"), ($Event->getColumnVal("id"))))) {echo "selected=\"selected\"";} ?>><?php echo($Event->getColumnVal("event")); ?></option>
                  <?php
  $Event->moveNext();
}
$Event->moveFirst(); //return RS to first record
?>
                </select>
              </label></td>

However, I need to insert the values from 2 other columns (start_time & end_time) in this table that correspond with the selected record when the form is submitted. I want those values to be hidden but they need to change along with the selected value in the drop down.

How can I do that?

The query is:

<?php
$Event = new WA_MySQLi_RS("Event",$local_i,0);
$Event->setQuery("SELECT kids_program_events.* FROM kids_program_events");
$Event->execute();?>

9/29/2014 11:17 am | #3 Jason

For example, the options in the drop down list are the contents of the event column (9am Service, 11am Service...). If "9am Service" is selected when the form is submitted, I need to enter the event_id (1), start_time (08:30:00), and end_time (10:30:00) for that record into 3 columns the on the table the form is inserting a record to (all based off of the 1 selection).

Here's what the insert looks like currently.

<?php
if ((((isset($_POST["Submit"]))?$_POST["Submit"]:"") != "")) {
  $InsertQuery = new WA_MySQLi_Query($local_i);
  $InsertQuery->Action = "insert";
  $InsertQuery->Table = "calendar_events";
  $InsertQuery->bindColumn("name", "s", "".((isset($_POST["UserID"]))?$_POST["UserID"]:"")  ."", "WA_DEFAULT");
  $InsertQuery->bindColumn("start_date", "s", "".((isset($_POST["start_date"]))?$_POST["start_date"]:"")  ."", "WA_DEFAULT");
  $InsertQuery->bindColumn("end_date", "s", "".((isset($_POST["start_date"]))?$_POST["start_date"]:"")  ."", "WA_DEFAULT");
  $InsertQuery->bindColumn("repeats", "s", "".((isset($_POST["RecurType"]))?$_POST["RecurType"]:"")  ."", "WA_DEFAULT");
  $InsertQuery->bindColumn("repeat_every", "i", "".((isset($_POST["Frequency"]))?$_POST["Frequency"]:"")  ."", "WA_DEFAULT");
  $InsertQuery->bindColumn("repeat_on", "i", "".((isset($_POST["RecurOn"]))?$_POST["RecurOn"]:"")  ."", "WA_DEFAULT");
  $InsertQuery->bindColumn("occurrence", "s", "".((isset($_POST["Occurrence"]))?$_POST["Occurrence"]:"")  ."", "WA_DEFAULT");
  $InsertQuery->bindColumn("occurrence_end", "s", "".((isset($_POST["ends_on"]))?$_POST["ends_on"]:"")  ."", "WA_DEFAULT");
  $InsertQuery->bindColumn("type", "s", "person", "WA_DEFAULT");
  $InsertQuery->bindColumn("attribute", "s", "".((isset($_POST["Room"]))?$_POST["Room"]:"")  ."", "WA_DEFAULT");
  $InsertQuery->bindColumn("attribute_2", "s", "".((isset($_POST["Position"]))?$_POST["Position"]:"")  ."", "WA_DEFAULT");
  $InsertQuery->bindColumn("calendar_id", "i", "1", "WA_DEFAULT");
  $InsertQuery->saveInSession("");
  $InsertQuery->execute();
  $InsertGoTo = "eventcal.php";
  if (function_exists("rel2abs")) $InsertGoTo = $InsertGoTo?rel2abs($InsertGoTo,dirname(__FILE__)):"";
  $InsertQuery->redirect($InsertGoTo);
}
?>

I may be severely over complicating this, but for some reason I can't seem to wrap my head around it.

9/29/2014 11:20 am | #4 Jason ByrnesWebAssist

why not just store the selected event ID, then use a join query to get the related data rather than storing it multiple times? That's the whole point of using a relational database.

9/29/2014 11:34 am | #6 Jason ByrnesWebAssist

what i mean is:

in the description column, dont store the name of the selected event:
5:30pm Service

instead store the ID:
3

then use a join query to return the event info in the recordset that returns the information

9/29/2014 11:49 am | #7 Jason

I get what you mean now. Thanks for dumbing it down a little. lol
I gave that a shot, but I'm getting an error in dreamweaver when I insert that query.

It says: Dynamically related files could not be resolved because of an internal server error.

The query looks like this:

<?php
$Times = new WA_MySQLi_RS("Times",$local_i,1);
$Times->setQuery("SELECT kids_program_events.* FROM calendar_events INNER JOIN kids_program_events ON calendar_events.event_id = kids_program_events.id GROUP BY kids_program_events.id");
$Times->execute();
?>

9/29/2014 11:53 am | #8 Jason

I'm sorry the error isn't caused by the query it's being caused by this:

$Times->getColumnVal('start_time')

Why?

This works:

function fcdb_query_events($start, $end) {
    global $calendar_events, $view;
	$result = array();
while(!$calendar_events->atEnd()) {
	$item = new stdClass;
		$item->id = $calendar_events->getColumnVal('id');
		$item->name = $calendar_events->getColumnVal('name');
		$item->name = is_numeric($calendar_events->getColumnVal('name')) ? $calendar_events->getColumnVal('UserFirstName').' '.$calendar_events->getColumnVal('UserLastName').' '.$calendar_events->getColumnVal('UserPhone') : $calendar_events->getColumnVal('name');
		$item->description = $calendar_events->getColumnVal('description');
		$item->recurrence = $calendar_events->getColumnVal('recurrence');
		$item->start_date = $calendar_events->getColumnVal('start_date') . ' ' . $calendar_events->getColumnVal('start_time');
		$item->end_date = $calendar_events->getColumnVal('end_date') . ' ' . $calendar_events->getColumnVal('end_time');
		$item->repeat = $calendar_events->getColumnVal('repeats');
		$item->repeat_on = is_int($calendar_events->getColumnVal('repeat_on')) ? ('x'.$calendar_events->getColumnVal('repeat_on')) : "";
		$item->repeat_every = $calendar_events->getColumnVal('repeat_every') . $item->repeat_on;
		$item->occurrence = $calendar_events->getColumnVal('occurrence');
		$item->occurrence_end = $calendar_events->getColumnVal('occurrence_end');
		$item->repeat_key = $calendar_events->getColumnVal('repeat_key');
		$item->attribute = $calendar_events->getColumnVal('attribute');
		$item->allDay = false;
		$item->event_id = $calendar_events->getColumnVal('event_id');
        $result[] = $item;
           $calendar_events->moveNext();

But this doesn't:

function fcdb_query_events($start, $end) {
    global $calendar_events, $view;
	$result = array();
while(!$calendar_events->atEnd()) {
	$item = new stdClass;
		$item->id = $calendar_events->getColumnVal('id');
		$item->name = $calendar_events->getColumnVal('name');
		$item->name = is_numeric($calendar_events->getColumnVal('name')) ? $calendar_events->getColumnVal('UserFirstName').' '.$calendar_events->getColumnVal('UserLastName').' '.$calendar_events->getColumnVal('UserPhone') : $calendar_events->getColumnVal('name');
		$item->description = $calendar_events->getColumnVal('description');
		$item->recurrence = $calendar_events->getColumnVal('recurrence');
		$item->start_date = $calendar_events->getColumnVal('start_date') . ' ' . $Times->getColumnVal('start_time');
		$item->end_date = $calendar_events->getColumnVal('end_date') . ' ' . $Times->getColumnVal('end_time');
		$item->repeat = $calendar_events->getColumnVal('repeats');
		$item->repeat_on = is_int($calendar_events->getColumnVal('repeat_on')) ? ('x'.$calendar_events->getColumnVal('repeat_on')) : "";
		$item->repeat_every = $calendar_events->getColumnVal('repeat_every') . $item->repeat_on;
		$item->occurrence = $calendar_events->getColumnVal('occurrence');
		$item->occurrence_end = $calendar_events->getColumnVal('occurrence_end');
		$item->repeat_key = $calendar_events->getColumnVal('repeat_key');
		$item->attribute = $calendar_events->getColumnVal('attribute');
		$item->allDay = false;
		$item->event_id = $calendar_events->getColumnVal('event_id');
        $result[] = $item;
           $calendar_events->moveNext();

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

Need a single MySQLi drop down select list to insert multiple columns from one table into a form

9/29/2014 7:27 am | #1 Jason