MySQL/PHP help in Dreamweaver CS3

[dravalanche]

Hello, everybody!

I've been banging my head against a wall for the past couple weeks on this operation I'm trying to perform in Dreamweaver CS3, and, for the life of me, I can't figure out how to accomplish this. The rundown that I'm trying to do is this:

I am trying to build a form in schedule.php which adds data to the table schedule. In order to fill in some form fields, needs to be populated with information from another table (locations).

I can build a basic form that will add to the schedule table, but what's getting me is the locations fields. What I'm trying to do is work with 9 fields:

Location Name
Location Address
Location City
Location State/Province
Location Postal Code
Location Phone #
Location Fax
Location e-mail
Location website URL

What I want to do is have the "Location Name" field display as a drop-down menu, that is dynamically populated with all the entries in the "LocationName" field in the Locations table. After making the selection from the drop-down menu, I'd like to have the rest of the fields automatically populate with the information that corresponds to the selection the user makes.

Now, some of the questions/problems that come up for me are:

• First, obviously, how do I get the dynamic drop-down menu populate the rest of the fields? I've tried experimenting with recordset queries with a filter based on the drop-down selection, but I couldn't get that to work (I think I may have been barking up the wrong tree, anyways).
• Second, is there a way I can have the drop-down menu automatically populate the fields below upon users making their selection, or would I need to create a "Submit" button to perform that action?
• After creating the record to be added to the schedule table, should I be content with creating entirely new fields in the schedule table that is populated with data that already exists in the locations table, or is there a way I can link the two in the record I'm creating in the schedules table (thus, eliminating unnecessary redundancy)?

If there's any way that this can be accomplished within DW, awesome! However, if I end up having to roll up my sleeves and code it manually, I'll try stumbling through it, either way.

Thanks for any assistance that you guys can provide, and let me know if you need more info!

-J(ay)

[boogyman]

http://twey.co.uk/?q=forms

should be a good start

[dravalanche]

http://twey.co.uk/?q=forms

should be a good start

It looks like it's a good start, thanks! The script you mentioned is capable of dynamically updating a webpage depending on what form elements you select, but it doesn't seem to be capable of facilitating a query to a MySQL table and returning its results (at least not in any way I could envision the code being altered). Maybe if I restated what I'm looking to do, that might clarify it some:

Location: (drop down menu, populated with entries from the "name" field of the table "locations")
Address: (once a selection is made from the "location" drop-down menu, automatically populated with the corresponding entry from the "address" field of the table "locations")
City: (once a selection is made from the "location" drop-down menu, automatically populated with the corresponding entry from the "city" field of the table "locations")
Repeat ad nauseum for the "State", "Phone", "Fax", "E-Mail", and "URL" fields.

Also, I should have said, I"m not looking for the user to be able to edit these fields. I just wanted these entries to show the details about the location that they selected from the drop-down menu.

Again, thanks for the suggestion! It seems like a step in the right direction, but I don't know if it would apply to my situation.

[boogyman]

okay this sounds more like a multi tier query then just a one step job.

This sounds like it would be an ideal AJAX style page. where it walks thru the process 1 step at a time, where after a user selects an option it goes and grabs the information for the next field

some psuedo code of how i would do it is

<form name="info" action="_something_">
      <label for="getWhat">
           getWhat:   // Field Name so user doesnt just have the dropdown
           <select name="fromWhat" onchange="getInfo(getWhat, fromWhat, formName,tableName); return false">
                <option value="false" selected="selected">--Choose Value--</option>
                <option value="{NAME}">{NAME}</option>
                <option value="{NAME}">{NAME}</option>
                <option value="{NAME}">{NAME}</option>
           </select>
     </label>
     <label for="{getWhat}">
           getWhat:   // Field Name so user doesnt just have the dropdown
           {POPULATED FROM QUERY REQUEST}
     </label>
</form>

that for the html portion then your AJAX function would be.

<script type="javascript">
function getInfo(getWhat, fromWhat,formName,tableName)
{
      /* Checks if AJAX is possible and the default value is not the one selected */
      if(ajax && document.formName.fromWhat.value != false) 
      {
            // get info for next category 
           var results = builtQuery("SELECT fields FROM "+tableName+" WHERE "+getWhat+" = '"+fromWhat+"'");

           // handle the info & parse back to the page
           parseResults(results,getWhat, formName);
      }
      else
      {
           // some error handler
      }
}
</script>

Edit:

function parseResponse(results, getWhat, formName)
{
     // print results
     dom create element(select, formName, getWhat becomes the fromWhat (select name="fromWhat") onselect="getInfo(...)");
     for(var i = 0; i<results.length; i++)
        dom create elements(option, results[i] - name & value);
}

the above is a very crude psuedo code example of how you could parse the additional categories

[dravalanche]

Hey, Boogyman

After much hemming and hawing (and a lot of beer), I think I figured it out. It's not the perfect solution, but it's effective. Thank you very much for your suggestions, though! Guys like you keep innovative web development going!!

Cheers!

[Shotgun Ninja]

Hey, another possible solution would be to use the PHP, as support is built into it for MySQL. However, you may not be able to get PHP to work on your server, so if your current solution works, then don't make the jump to PHP just yet if you don't have to.