bglinkerman Posted February 11, 2011 Share Posted February 11, 2011 Hey Guys, Here is my issue. I have a MySQL database with a table of products. I use this table to generate a mutliselect form element that is used int a larger form. The larger form contains other information relevant to an issue that we are having with that product (such as an outage). Anyway, the form works fine and the form validation works fine and I write it to the database fine. The issue I'm having is how to save the mutliselect options most efficiently. This is my current process: 1. User creates a new entry from the form and clicks save 2. The form is validated 3. All entries are written to the database in a table called "incidents" (content inclues, timestamps, description of the problem, the impact, who to contact, etc) 4. Because the products list can vary from 1 product to all (~30) products I broke it out into a separate table called "impacted_products". I then loop through each of the selected products and write one row for each product using the id from the row in the "incidents" table to define the tables relationship. Example of DB results: "incidents" +-----------------+------+----------+---------------------+---------+---------+ | notification_id | what | impact | time_start | summary | contact | +-----------------+------+----------+---------------------+---------+---------+ | 235235 | Test | None.... | 2011-02-08 13:41:00 | Test | 3 | +-----------------+------+----------+---------------------+---------+---------+ "impacted_products" +-----+-----------------+------------+---------------------+---------+ | id | notification_id | product_id | timestamp | deleted | +-----+-----------------+------------+---------------------+---------+ | 202 | 235235 | 7 | 2011-02-10 14:30:42 | 0 | | 203 | 235235 | 37 | 2011-02-10 14:30:42 | 0 | | 204 | 235235 | 23 | 2011-02-10 14:30:42 | 0 | +-----+-----------------+------------+---------------------+---------+ 5. Now the users wants to go back and make some updates. 6. They click the edit button from the menu on screen and the form is brought back up and all options/fields are filled out from that which is stored in the database. 7. They make their edits and save again. 8. Now this time it simple updates the "incidents" table but for the products table I do a: "UPDATE impacted_products SET deleted=1 WHERE notification_id='$ID'" and "delete" all the old impacted_products for this particular incident and then loop through all the products that the user still had selected and write them to the database again. 9. And repeat So as you can see this could end up with a lot of "useless" entries in the database since my scripts only pay attention to those "impacted_products" whose deleted value is set to 0. Example: +-----+-----------------+------------+---------------------+---------+ | id | notification_id | product_id | timestamp | deleted | +-----+-----------------+------------+---------------------+---------+ | 176 | 235235 | 37 | 2011-02-08 15:26:25 | 1 | | 177 | 235235 | 43 | 2011-02-08 15:26:25 | 1 | | 178 | 235235 | 37 | 2011-02-08 15:37:58 | 1 | | 179 | 235235 | 43 | 2011-02-08 15:37:58 | 1 | | 180 | 235235 | 1 | 2011-02-08 15:39:49 | 1 | | 181 | 235235 | 7 | 2011-02-08 15:39:49 | 1 | | 182 | 235235 | 37 | 2011-02-08 15:39:49 | 1 | | 183 | 235235 | 43 | 2011-02-08 15:39:49 | 1 | | 184 | 235235 | 1 | 2011-02-08 15:40:53 | 1 | | 185 | 235235 | 7 | 2011-02-08 15:40:53 | 1 | | 186 | 235235 | 37 | 2011-02-08 15:40:53 | 1 | | 187 | 235235 | 43 | 2011-02-08 15:40:53 | 1 | | 188 | 235235 | 37 | 2011-02-10 10:00:47 | 1 | | 189 | 235235 | 1 | 2011-02-10 12:17:05 | 1 | | 190 | 235235 | 7 | 2011-02-10 12:17:05 | 1 | | 191 | 235235 | 13 | 2011-02-10 12:17:05 | 1 | | 192 | 235235 | 37 | 2011-02-10 12:17:05 | 1 | | 193 | 235235 | 23 | 2011-02-10 12:17:05 | 1 | | 194 | 235235 | 1 | 2011-02-10 12:21:52 | 1 | | 195 | 235235 | 7 | 2011-02-10 12:21:52 | 1 | | 196 | 235235 | 13 | 2011-02-10 12:21:52 | 1 | | 197 | 235235 | 37 | 2011-02-10 12:21:52 | 1 | | 198 | 235235 | 23 | 2011-02-10 12:21:52 | 1 | | 199 | 235235 | 7 | 2011-02-10 12:22:26 | 1 | | 200 | 235235 | 37 | 2011-02-10 12:22:26 | 1 | | 201 | 235235 | 23 | 2011-02-10 12:22:26 | 1 | | 202 | 235235 | 7 | 2011-02-10 14:30:42 | 0 | | 203 | 235235 | 37 | 2011-02-10 14:30:42 | 0 | | 204 | 235235 | 23 | 2011-02-10 14:30:42 | 0 | +-----+-----------------+------------+---------------------+---------+ I did it this way beacuase it seems the easiest and fastest way. Otherwise I would have to lookup all the impacted_products from the table that match that notification_id and check 1) Was there a new product selected? If so, add it. 2) Was a product that was selected no longer selected? If so, delete it. 3) Was a product that was selected before still selected now? If so, leave it alone. This seemed like a lot of extra looping and a lot of extra DB queries to essentially end up at the same place. However, I feel that there has still got to be a more efficient way of doing this where I won't have all the extra entries in the impacted_products table. Any ideas? Thanks in advance! Quote Link to comment Share on other sites More sharing options...
acefirefighter Posted February 11, 2011 Share Posted February 11, 2011 Ok, I think I understand what your going for. I think you want to keep a record of all changes, right? I have had this idea bouncing around in my head for a while it might not be what you are looking for and may prove to be a little complex but since you have no replies yet here you go. You could add another field to the Incidents table called "impacted_products" and I would use the implode() and explode() functions to store the information in there. To save you would do something like this... <?php $time = time(); $impacted = array('12','4','34','25'); // the impacted product id's /* * Current output for $impacted is * Array ( [0] => 12 [1] => 4 [2] => 34 [3] => 25 ) */ // We are adding the time here too. $impactString = implode(',', $impacted) . ':' . $time .';'; /* * Current output for $impactString is * 12,4,34,25:1297461648; * * This is what I would save to the new table column */ $queryResult = //some query to retrieve what is currenty in the table cell for the current incident if($queryResult != null) { $impactString = $queryResult . $impactString; } $saveQuery = //Query to save $impactString to the database If you were retriveing this from the database to edit it you would do something like. <?php $query = // query to retrive the impacted_products cell $queryResult = '12,4,45,98,056,43,27,8,34,25:1297461648;'. '12,4,34,25,45,7,23,877:1297461642;'. '12,4,34,25:1297461348;'. '52,4,34,25,1245,2,7,9:397461645;'. '12,5,34,25:1297461348;'; $impactHistoryStrings = array_filter(explode(';', $queryResult)); /* * Current output for $impactHistoryStrings is * Array *( * [0] => 12,4,45,98,056,43,27,8,34,25:1297461648 * [1] => 12,4,34,25,45,7,23,877:1297461642 * [2] => 12,4,34,25:1297461348 * [3] => 52,4,34,25,1245,2,7,9:397461645 * [4] => 12,5,34,25:1297461348 *) * */ $impactHistoryArrays = array(); foreach ($impactHistoryStrings as $times) { $test = explode(':',$times); array_push($impactHistoryArrays, $test); } /* * current output of $impacthistoryarrays is * Array *( * [0] => Array * ( * [0] => 12,4,45,98,056,43,27,8,34,25 * [1] => 1297461648 * ) * * [1] => Array * ( * [0] => 12,4,34,25,45,7,23,877 * [1] => 1297461642 * ) * * [2] => Array * ( * [0] => 12,4,34,25 * [1] => 1297461348 * ) * * [3] => Array * ( * [0] => 52,4,34,25,1245,2,7,9 * [1] => 397461645 * ) * * [4] => Array * ( * [0] => 12,5,34,25 * [1] => 1297461348 * ) * *) */ You would run explode() on impacts one more time and then run a DB query to do you Joins. and then from here you could use the data to fill your form to get it ready for the new edits or fill in a table to show a history of changes. This was an idea I have had for a while but haven't gotten to try it. You hadn't gotten any responses yet so I thought I would shoot this out there. Good luck. Quote Link to comment Share on other sites More sharing options...
bglinkerman Posted February 12, 2011 Author Share Posted February 12, 2011 Thanks acefirefighter. This is an interesting concept that I haven't ever considered. Thanks for the lengthly explanation. I'll give this a try. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.