Working with Multi-Select & Dropdowns and SQL

[eluser]Philipp Gérard[/eluser]
Since the user can select more than one location or servicename and your SQL layout seems to only allow one at a time, you have a problem. You can either save the data in some string format and explode() the value when SELECTing from the database or you construct your database with database normalization in mind (http://en.wikipedia.org/wiki/Database_normalization).

Quick and dirty solution:

Code:

function updateoutage()
{

    $this->load->model('outage_model','outage');

    $data = array();
    $data["ticketnumber"] = $this->input->post("ticketnumber", TRUE);
    $data["impact"] = intval($this->input->post("impact"));
    $data["starttime"] = intval($this->input->post("starttime"));
    $data["endtime"] = intval($this->input->post("endtime"));
    $data["summary"] = $this->input->post("summary", TRUE);
    $data["details"] = $this->input->post("details", TRUE);
    // And your arrays
    // I seperate each value by a semicolon (;). When SELECTing the value you only need to do
    // $array = explode(";", $db->fieldname);
    // to retrieve the array's individual value. Note that no name may contain a semicolon!
    $data["servicename"] = implode(";", $this->input->post("servicename"));
    $data["location"] = implode(";", $this->input->post("location"));

    $this->outage->updateoutage((int)$this->input->post("outageid"), $data);
    
}

(Written from scratch and not tested, let me know if it works)

[eluser]Bionicjoe[/eluser]
I'll try it soon.
Probably can't get to it today. My workday is ending now, and this is a sideline project.

I can work with the DB too. Any suggestions on how to configure the tables to handle this?

Right now I am getting the values from a table to populate the multi-select fields. The tables have only these values with an index.

Also I haven't worried with the timestamps yet. That's why they're blank.

[eluser]Philipp Gérard[/eluser]
This would be a normalized database layout reflecting your structure. I wasn't sure about varchar(ticket), if this is also an entry that can be external, then you should also normalize this field.

Code:

CREATE TABLE `outages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ticket` varchar(128) DEFAULT NULL,
  `starttime` datetime DEFAULT NULL,
  `endtime` datetime DEFAULT NULL,
  `summary` varchar(128) DEFAULT NULL,
  `details` text,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `locations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `services` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `outages_locations` (
  `outage_id` int(11) DEFAULT NULL,
  `location_id` int(11) DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `outages_services` (
  `outage_id` int(11) DEFAULT NULL,
  `service_id` int(11) DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Exemplary content:

Code:

INSERT INTO `outages` (`id`, `ticket`, `starttime`, `endtime`, `summary`, `details`) VALUES (1, 'ABC123', '0000-00-00 00:00:00', '0000-00-00 00:00:00', 'My summary', 'My details');
INSERT INTO `services` (`id`, `name`) VALUES (1, 'Example service');
INSERT INTO `locations` (`id`, `name`) VALUES (1, 'Boston');
INSERT INTO `outages_locations` (`outage_id`, `location_id`) VALUES (1, 1);
INSERT INTO `outages_services` (`outage_id`, `service_id`) VALUES (1, 1);

(1 outage that is connected to 1 location and 1 service by the outages_locations/outages_services tables)

[eluser]Bionicjoe[/eluser]
Your function from above works (with some slight edits). However I've run into an error with the implode or explode saying:
"Message: implode() [function.implode]: Invalid arguments passed"

It worked a couple of times, and I don't know why it stopped working. Hopefully this will work until I can get the db tables all normalized.

[eluser]Bionicjoe[/eluser]
Alright. Everything is working except when I go to edit an outage nothing in the multi-selects is pre-selected.

View to produce the field.

Code:

<tr><td>Location          </td><td>&lt;?php echo form_multiselect('location[]', $siteoptions, $selectedsites); ?&gt;</td></tr>

Variable to send selection info to field. (This doesn't show up in the HTML.)

Code:

$selectedsites = array(
              explode(", ", $outage[0]->location),
            );

Output from print_r($selectedsites)

Code:

Array ( [0] => Array ( [0] => Evansville [1] => Louisville [2] => Bowling Green ) )

[eluser]Bionicjoe[/eluser]
DOH!!!
Array in an array! Should have seen this earlier.

RIGHT
$selectedsites = explode(", ", $outage[0]->location);

[eluser]Philipp Gérard[/eluser]
Works?

[eluser]Bionicjoe[/eluser]
Yes. I got your solution to do what I need for now. Thanks.
I'm sure there are better solutions.

I already have a locations table & services table with id fields. I just couldn't put it together. Implode/Exploding the text will work ok for now.

I haven't done any of the Normalization stuff yet. This being a sideline project I haven't even worked on it much. Leaving for vacation tomorrow, so it's going to wait awhile longer.