PHP - Php Combo Boxes, Saving The Drop Down Options Into Sql

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!

Hi All, this is my first post after joining the forum. First of all, I am not a programmer. My field is of Building design and Architecture. I work for an Architectural company. I have a great interest in programming and I started learning php, mysql, html css and jscript, to develop a timesheet application for the company. The web application was created and successully implemented in April this year...! However, all these are purely 'self learning', and it has its own downsides as well. This was just a small intro..., let me come to what i request help on. This is for further development of the application.   I have a page to edit the 'utilization' rate of each employee, based on their designations. So the page has all the designations listed as <labels>, next to which a textbox, for the user to fill in the utilization rate. Scenario is that the user will not save each designation's utilization rate immediately after filling it. User will keep going till he fills the last item and then hit the submit button to save. Now, in php I can get all the values from the Request Array. But, how will I know which designations these values belong to? So, what i have as a solution is to name the textboxes with the designation_ids as suffix. May be like;   utilTextbox_1, utilTextbox_2, utilTextbox_3 etc...   Then when the form is submitted; Check each Request Array element to see if its name starts with 'utilTextbox' If Yes, split it using '_' to get the designation_id Update the db table with the value of the text box Check the next Array Element.....and so on...     is this the correct method or is there a better way of doing this?      

Hi all

Im looking to do a "SIMPLE"  search on a databse usign php.

I want to have a drop down box like so :

<select name="Lowest">
  <option>0</option>
  <option>100</option>
  <option>200</option>
</select>

this is for the prices i want 2 of these so that the first will be the lowest price and the second will be the higher price

Then i will make a query to SELECT * WHERE ( i dont know how to call the results from the select box here to display the whole tabled results )

any help please

thnax

Hi

I have 3 drop down boxes and I want my boxes to go to another page when I select them and click go. For example if I click western cape I want it to give me all the resorts in western cape and if I click western cape and beaches I want it to give me all the resorts that are in the western cape that are next to beaches. I have been able to populate the drop down boxes with information and it can go to another page but it doesn't give me the information I want.

This is my drop down boxes code

Code: [Select]
<script type="text/javascript">

function showGo(){

 

      var region_id = $("#region_id").val();

      var lifestyle_id = $("#lifestyle_id").val();

      var resort_facility_id = $("#resort_facility_id").val();

 

      if((region_id != 'false') || (lifestyle_id != 'false') || (resort_facility_id != 'false')){

 

            $("#destinationSearchButton").css('display', 'block');

           

      }else{

 

            $("#destinationSearchButton").css('display', 'none');

      }

 

 

 

</script>


<form action="/pages/search/" method="post">

<select class="formSelect" name="region_id" id="region_id" size="1" tabindex="1" onchange="showGo()">
<option label="Region">
<?php

$regions_model = new regions_model('regions');
$regions = $regions_model -> get_all();

 foreach($regions as $region){
$name = $region['name'];
$id =$region['id'];

$select = "SELECT $id, $name FROM regions";
  echo "<option value='$id'>$name</option>";
 } ?>
</option>

</select>

<select class="formSelect" name="lifestyle_id" id="lifestyle_id" size="1" tabindex="2" onchange="showGo()">
<option label="lifestyle">
<?php

$lifestyles_model = new lifestyles_model('lifestyles');
$lifestyles = $lifestyles_model -> get_all();

 foreach($lifestyles as $lifestyle){
$name = $lifestyle['name'];
$id = $lifestyle['id'];

$select = "SELECT $name FROM lifestyles";
  echo "<option name='$id'>$name</option>";
 } ?>
</option>
</select>


<select class="formSelect" name="resort_facility_id" id="resort_facility_id" size="1" tabindex="3" onchange="showGo()">
<option label="Facilities & Amenities">
<?php

$resort_facilities_model = new resort_facilities_model('resort_facilities');
$resort_facilities= $resort_facilities_model -> get_all();

 foreach($resort_facilities as $resort_facility){
$name = $resort_facility['name'];
$id = $resort_facility['id'];

$select = "SELECT $name FROM resort_failities";
  echo "<option name='$id'>$name</option>";
 } ?>
</option>
</select>

<input class="formInputButton" type="submit" name="submitButtonName" id="destinationSearchButton" value="Go" tabindex="4"/ style="display: none;" />
</form>
</div>

Thank you

Hey, I'm new to php and I made a webpage that searches a text file and displays a list of courses I've taken depending on what you type into a textbox. I want to change it instead into two drop-down boxes (one for department one for semester) and a all button so that it's neater and to prevent errors. Here is what I have currently:

http://codepad.org/nX6g9zh2

Please help! Thank you!