Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • Last Modified:

SEARCH: DEPENDENT SELECT LISTS - ColdFusion/MySQL Searching database using dynamic multiple related selects

My original question or task at hand had multiple parts, so I'm splitting up the questions to cover each. This question pertains specifically to the select list Search that I need to figure out, and hopefully someone can help me with this part of my task.

I have a working search page that has one of the select lists partially working, here:
http://www.designpubdemo.com/SHM/search_layout.cfm

I am now seeking to figure out how to make this search feature have two select lists, to work exactly as it does (as far as functionality - not display) on this page: http://www.lisaturn.com/search.asp.

The final goal is to reproduce the functionality of all the various searches and sorts done on this example page.

With valuable help from _agx_, I was able to normalize my one large table into 5 tables, and now have a good start, but I still don't know how to make these dependent select lists work.

This is a link to the original question that shows the history of where I am at this point: http://www.experts-exchange.com/Web_Development/Software/ColdFusion_Studio/Q_25022079.html#a26192857

I know this is complex, but really appreciate anyone who could help me get this accomplished.

These are the table names:
 SHM_Category
 SHM_Make
 SHM_Model
 SHM_PriceRange
 SHM_Used_Cars

And I have attached code that shows how I have each table set up in MySQL, and some of the data that populates the tables. I don't yet know how to populate the PriceRange table, as this was suggested by _agx_ and I am awaiting his reply to figure out that part of the task.

Please let me know if you can help me, and I will provide the code that I have so far.

Thank you so much.
elizabeth





ALL TABLE NAMES

 SHM_Category 
 SHM_Make 
 SHM_Model 
 SHM_PriceRange 
 SHM_Used_Cars 
 
 
 
 
 TABLE:  SHM_Used_Cars
 
  SHM_UsedCarsID smallint(5)   UNSIGNED No  auto_increment               
  SHM_PhotoName varchar(100) utf8_general_ci  Yes NULL                
  SHM_CarStyle varchar(100) utf8_general_ci  Yes NULL                
  SHM_CarEngine varchar(100) utf8_general_ci  Yes NULL                
  SHM_CarTransmission varchar(100) utf8_general_ci  Yes NULL                
  SHM_CarExteriorColor varchar(100) utf8_general_ci  Yes NULL                
  SHM_CarInterior varchar(50) utf8_general_ci  Yes NULL                
  SHM_CarStockNum varchar(50) utf8_general_ci  Yes NULL                
  SHM_CarMileage varchar(30) utf8_general_ci  Yes NULL                
  SHM_CarPrice varchar(30) utf8_general_ci  Yes NULL                
  SHM_CarSpecialPrice varchar(30) utf8_general_ci  Yes NULL                
  SHM_CarComments varchar(1500) utf8_general_ci  Yes NULL                
  SHM_CarVINNum varchar(30) utf8_general_ci  Yes NULL                
  SHM_CarYear varchar(25) utf8_general_ci  Yes NULL                
  SHM_CarMake varchar(50) utf8_general_ci  Yes NULL                
  SHM_CarModel varchar(50) utf8_general_ci  Yes NULL                
  SHM_CarCategory varchar(50) utf8_general_ci  Yes NULL                
  DateTime datetime   Yes NULL                
  MakeIDFK tinyint(4)   UNSIGNED Yes NULL                
  ModelIDFK tinyint(4)   UNSIGNED Yes NULL                
  CategoryIDFK tinyint(4)   UNSIGNED Yes NULL 
  
  
  
  TABLE:  SHM_Make 
  
  MakeID tinyint(4)   UNSIGNED No  auto_increment               
  MakeName varchar(50) utf8_general_ci  Yes NULL     
  
  
  
  TABLE: SHM_Model
  
  ModelID tinyint(4)   UNSIGNED No  auto_increment               
  ModelName varchar(50) utf8_general_ci  Yes NULL                
  MakeIDFK tinyint(4)   UNSIGNED Yes NULL 
  
  
  
  TABLE: SHM_Category
  
  
  SHM_CategoryID tinyint(5)   UNSIGNED No  auto_increment               
  SHM_CategoryName varchar(50) utf8_general_ci  Yes NULL  
  
  
  
  TABLE: SHM_PriceRange
  
  PriceRangeID tinyint(4)   UNSIGNED No  auto_increment               
  RangeMin int(16)   Yes NULL                
  RangeMax int(16)   Yes NULL    
  
  
  
  POPULATED WITH THE FOLLOWING:
  
  MAKE

ID Name
1 BMW 
2 Ford 
3 GMC 
4 Chrysler 
5 Toyota 
6 Chevrolet 
7 Acura 
      
      
MODEL

      
1 TL 7 
2 X5-Series 1 
3 Cobalt 6 
4 Tahoe 6 
5 TrailBlazer 6 
6 300-Series 4 
7 Aspen 4 
8 Crossfire 4 
9 Pacifica 4 
10 PT Cruiser 4 
11 Town & Country 4 
12 Town & Country LWB 4 
13 Escape 2 
14 Explorer 2 
15 F-150 2 
16 Fusion 2 
17 Mustang 2 
18 Super Duty F-350 SRW 2 
19 Yukon XL Denali 3 
20 Camry 5 
21 Camry Solara 5 



CATEGORY

1 Certified 
2 Coupes 
3 Sedans 
4 SUVs 
5 Trucks 
6 Under $10,000 
7 $10,000 - $20,000 
8 $20,000 and Up

Open in new window

0
Elizabeth2
Asked:
Elizabeth2
  • 14
  • 13
8 Solutions
 
_agx_Commented:
Hi Elizabeth2,

Check your other thread in a few minutes.  There are still few issues with the table structure. Mostly involving duplicate columns and FK's. For example, the model number should be a numeric FK only. The VARCHAR column "CarModel" is a duplicate and should be deleted.
0
 
Elizabeth2Author Commented:
Hi _aqx_,

Yes, I forgot to mention that the original Used_Car table had Make, Model and Category. I left those columns when I added the Foriegn Key columns only as a reference to help me when entering the data to populate the tables so I could continue and work on the display layout and styles.

Shouldn't I delete all three duplicate columns from the SHM_Used_Car table?
SHM_CarMake varchar(50) utf8_general_ci  Yes NULL                
 SHM_CarModel varchar(50) utf8_general_ci  Yes NULL                
 SHM_CarCategory varchar(50) utf8_general_ci  Yes NULL

As well as this one you suggested also be removed?
 CategoryIDFK tinyint(4)   UNSIGNED Yes NULL

elizabeth
0
 
_agx_Commented:
> I left those columns when I added the Foriegn Key columns only as a reference

Good.  Sorry, I thought that might be the case. But ignored my instincts ;-)  (For anyone reading along, I responded about those columns on the other thread).
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
Elizabeth2Author Commented:
Hi _aqx_,
My boss has moved me to another project, but expects me to still complete my original task concerning these multiple search select lists, however, now I will need to work on this only as our time permits. Last week I had all day but will now have other responsibilites each day to work on. Nonetheless, I'm determined to get this database completed the right way and move forward.

I will touch base tomorrow, Monday to ask your advice on the next step.

Also, when I receive an email response, as today when I recieved an "Abandoned Question" notice, I'm just clicking on the link provided to get to each question's thread. If I understand correctly this thread is still concerning the database design, right, and that we will move to another post for the select lists?

Thank you so much,
elizabeth
0
 
_agx_Commented:
Okay.  

If you're satisfied with the database designed. I would close the other thread, and keep this one focused on implementing the first set of "Dependent Select Lists" (ie make/model).  

I've seen examples that use combinations of CF and JavaScript, and/or AJAX and was wondering if it can be completedly done using ColdFusion?
Yes.  The reason why most people don't do it that way is because it requires reloading the page, which is often perceived as slower/more awkward by some users.  In one sense it is slower: it translates to a second request of _all_ resources on the page (images, html re-run queries, etc).  So using ajax, it is only a second request of (1) query.  So it is generally faster because less information is transferred.  BUT .. either will work

CF Only:  Pros: Simpler in some senses. Still works if javascript is disabled  Cons: Requires reloading the page
CF+Ajax:  Pros: Does not require reloading the page Cons: Does NOT work if javascript is disabled

I would probably use ajax. But you may have different requirements. I will post an example of both and let me know which you prefer. Note: Using the built-in ajax is simplest, but there are a few features that are not supported. That's why some people prefer jQuery. I'm not as familiar with jQuery, but I will see if I can dig up an example.

BTW: I'm not sure what logic they use trigger the "Abandoned Question" notices.  Maybe questions not marked as answered within x days. Since it's obviously still active, I wouldn't worry about it for now.



0
 
Elizabeth2Author Commented:
Hi _aqx_,

I just saw your posts, and wanted to respond, but I'm buried in a deadline this morning. Clients are in a meeting/conference call with my boss, and sending numberous tweaks. Hopefully, I'll be available by lunchtime, if you're stil available. ;-)

Thank you so much.
elizabeth
0
 
_agx_Commented:
Here is an example using CF. There is a very tiny bit of JS in there. So it wont' work if javascript is disabled.  You could make it work without _any_ javascript.  But it would require an extra step:  ie 1. Change MAKE. 2. Click button to refresh MODEL list.

I don't always give this complete an example ;-) But I had something similar lying around. So I adapted it.  (You have to love reusable code)
searchForm-Example1.txt
searchResults-Example1.txt
0
 
Elizabeth2Author Commented:
Wow! Thank you so much!!

I'm studying the code now, and will post answers to your other comments in a few minutes. I was thinking ColdFusion would be easier for me to fully understand, but all the sites I create have some JavaScript either to call Flash, or run a menu script, so JavaScript should be fine  (I would just love to understand what's going on). I'm so excited! ;-)

elizabeth
0
 
_agx_Commented:
Wait till you see the ajax example.  It is not quite as hard as you might be thinking :)
0
 
Elizabeth2Author Commented:
Hi _aqx_,

;-) ;-) ;-) ;-) ! ! ! This is so AWESOME! I have a ton of questions, if you don't mind, but I want to close out that first thread. The buttons say "Accept As Solution," or "Accept Multiple Solutions." Which one shoud I select? I don't see an Accept and Award Points like I do on this thread. Sorry, I'm still learning this site.  Will the "Accept Multiple solutions" keep all of our conversations, because the solution was actually a process.

I created the new tables without the SHM_ as the new table names so that I could keep the old tables and compare the two so I can see what you did different, so I can learn. I therefore went through your code and made that change. It works awesome, now I need to figure out how you did it, and maybe I can figure out some of the other display searches. This is a great help. Are you still available to help me if I need help with the Category search? I may be able to figure it out based on the help in this code, but it would be great to still ask you questions.

I had no idea I could do this (below) in a WHERE statement--I'm not sure I understand everything that's going on, but plan to study more. I would have never figured this out. Thank you so much.

WHERE      MakeID = <cfqueryparam value="#url.makeID#" cfsqltype="cf_sql_integer" null="#not IsNumeric(url.makeID)#" />

I've never seen this used before either.
#YesNoFormat(IsCertified)#
That will be one of my questions...but want to play with this code first.
I can't wait to dig in and create the display pages.

The one crinkle is that I now have no idea how to create the admin area for the client to manage the database. I wrote an application once that had multiple tables, using frames, that allowed the Admin to pick which table he wanted to add, edit or delete items from, in a drop-down, but the over-all database design had no referential integrity, so I'm sure that entering data into one table at a time with no understanding of the relationships is not the best way to do it. Is this something we could tackle in another thread?.

Thank you so very much. I didn't mean for you to just give me the code, but it helps me emmensely to see the logic and the posibilites.
Gratefully,
elizabeth
0
 
_agx_Commented:
I didn't mean for you to just give me the code, but it helps me emmensely to see the logic and the posibilites.

I know you didn't .. and that is why I did it.   You seem like someone who really enjoys _really_ learning, and that's "my kind of person" ;-)  Since most of the screens will probably follow the same pattern, I thought a single example would make great learning material.

Feel free to open other questions about cfqueryparam if needed. Definitely get into the habit of always using it. It has a lot of benefits.

The one crinkle is that I now have no idea how to create the admin area  for the client to manage the database

Yes, that is definitely a question for another thread. But for administration I think you want to take a more rigid approach. ie Create screens that "tell" the user what they can administer.
Adminstration (Menu)
  • Make -> (Add/Edit MAKE screen)
  • Model -> (Add/Edit MODEL screen)
  • Car  -> (Add/Edit CAR screen)
...
It is more intuitive and you can design it in user friendly such a way that the user must enter things in the right order (but in a more user friendly way).

Are you still available to help me if I need help with the Category  search
Yep.  The Category search will be interesting. You'll get to use some fun SQL for that.  Though you'll have to decide which table structure you feel more comfortable with.  Personally, I wouldn't allow that table to be administered. It is possible. But since it combines a few different features (and tables) create an admin screen for it will be more challenging than for the other tables.

Will the "Accept Multiple solutions"  keep all of our conversations
Either option will preserve the  whole thread.   "Accept Multiple ..." is for when multiple  experts  participate and you want to split the points between them (which really  doesn't apply here).  So either option should work.


0
 
Elizabeth2Author Commented:
Hi _aqx_,

I just clicked what I thought was the correct button in the Database Thread that said "Accept and Award Points," but then I got a message that I had awarded my comment as the solution. I was wanting to award the points, and that's what the button said. I didn't realize it was my comment. It doesn't make sense that the Award Points button should appear under my comments in the first place.

I'm sorry. I wrote and submitted a comment explaining my mistake and that I definately wanted to award the points to you.

I feel dumb ;-)
elizabeth
0
 
Elizabeth2Author Commented:
Yes, I love this stuff. It's not unusual for me to get so involved in figuring out a script that 15-17 hours go by and I haven't even eaten. Crazy huh? I just wish my boss would let me do this all the time, but unfortunately, I'll be right in the middle of packing all these concepts into my head, making progress, part of it's working, and she'll call me to another task. Then when I have time to come back, it's like starting over to figure out where I am. Drives me crazy, but it pays the bills. ';-)
0
 
_agx_Commented:
> Drives me crazy, but it pays the bills. ';-)

That's what bosses are for ;-)

Anyway, I'm going to post the ajax example in a minute.  Are you familiar with the sql UNION or UNION ALL operators? (If not, you will be shortly ;-)

0
 
_agx_Commented:
> I feel dumb ;-)

Don't.  I don't ask too many questions on this site, so every time I do I have to review the options like 5 times to remember which is the right one. lol.  I think you may need to re-open it to reassign the points.  There should be some sort of option like "Automated Request for Attention" or "Request assistance".  I'm _guessing_ that option will have a text box to enter a reason or something.  

If not, you can always open a question manually in Community Support explaining why you need to reopen the question and post a link to it. The mods will take care of it.

http://www.experts-exchange.com/Community_Support/General/
0
 
Elizabeth2Author Commented:
I've used UNION a couple times in the past, but got confused as they seem similar to a JOIN. My problem is not knowing when a UNION, normal JOIN, or OUTER JOIN is needed. I'm a very visual thinker, so I'm always looking for books that have flow charts to help me graps the concepts. I know a UNION adds to queries right? The WHERE statement is where I get lost. I usually play with them until I get it to work, but that's not always the "right" way. '-)
0
 
Elizabeth2Author Commented:
Sorry, I meant "two" queries, not "to" queries.
0
 
_agx_Commented:
> I know a UNION adds to queries right?

Yep. You do not normally use them as frequently as regular JOINs.  UNIONs are good for when you need to merge information from two separate tables into the _same_ column. JOINs only let you add information as an _additional_ column.

Not a great example, but say you had two tables: supervisors and employees. If you wanted to get a list of all employee names you couldn't do that easily with a JOIN:

      SELECT supervisor.FullName, employee.FullName
      FROM   supervisor INNER JOIN employee ON employee.supervisorID = supervisor.supervisorID

So you'd use a UNION instead, to merge the results into the one column. Just as if they were all from the same table:

      SELECT  FullName FROM supervisor
      UNION
      SELECT  FullName FROM employee

There is also a UNION ALL operator. One important difference between them is that UNION only returns DISTINCT results.  UNION ALL returns all everything, regardless of whether the results are unique are not. For example, this query only returns 1 record, because there is only one unique "MyNumber" value.

      SELECT 1 AS MyNumber
      UNION
      SELECT 1 AS MyNumber

But if you changed the UNION to a UNION ALL, it would return two records. Unless you know the results _should_ be unique, always use UNION ALL instead.  

http://www.mysqltutorial.org/sql-union-mysql.aspx
0
 
_agx_Commented:
Sorry I got called away earlier.  Here is another version of the same search form, but using ajax instead.  (Using the non-SHM table names).  The main difference is all of the database code is inside cfc's, rather than being mixed with the presentation code.  That is really the way to go, otherwise app's can quickly become spaghetti code.

Unfortunately, EE filters out the .cfc and .cfm file extensions. So you'll have to rename the files. All files have the .cfc extension _except_ searchForm.cfm and searchResult.cfm.
searchExample2.zip
0
 
Elizabeth2Author Commented:
Hi _aqx_,

Thank you so much! I am so anxious to get back to this project. I have several questions for you, but my boss has me working on a new web site with a deadline, so every chance I get, I'll be back to this thread, and I want to compare and study your code. I just wanted to let you know what was going on with my availability. I would have worked on this last night, after hours, but my cat of 10 years has been, and was very sick last night, and I had to have him put to sleep this morning, renal failure. Hopefully, I'll get some time this afternoon.

Gratefully,
elizabeth
0
 
Elizabeth2Author Commented:
I'm sorry to have dropped out of sight. My boss gave me multiple tasks with deadlines. I'm still buried, but wanted to touch base. I have worked on the display page here: http://www.designpubdemo.com/SHM/search_layout.cfm.

Building the Admin area is still a big question mark in my head. Thank you so much for the help on UNIONs and JOINs. That was very helpful. I've been studying your code, and am following most of it, but would have not known how to do that without your help.

Can you please suggest a starting point for the admin section? Based on the table relationships, would data need to be entered into the Model, Style, and Category tables first, BEFORE adding a new record in the Used_Cars table?

Thank you,
elizabeth ;-)
0
 
_agx_Commented:
Yes, because you'll need valid recordID's to insert into the Used_Cars table.  I'd usually create some simple admin screens to each of the entity:

Screen name: Make  
0
 
_agx_Commented:
Ooops, I hit return too soon.  As I was saying, the simplest approach is to have simple modules for each entity: MAKE, MODEL and STYLE. (CAR will be different.)  So there'd be a simple list screen (to show all MAKE's). The name is usually a link to another screen: Add/Edit MAKE. On the main list screen, you'd usually have a link (top right corner) to "Add A New Make". It too links to the Add/Edit MAKE screen.  FYI: Add/Edit is usually a single screen that can be used to edit an existing item, or add a new record.  The same approach would be used for the others: MODEL and STYLE.  It's a pretty simple design.  But a List / Add/Edit module is really a topic for another question.

Another approach is to combine the screens.  Design it so that user's can add new MODEL's, MAKE's, etc .. all from the CAR admin screen in small popup windows. The benefit is there's less navigation required. The disdavantage is it's a little more complex to design.
0
 
Elizabeth2Author Commented:
Hi _aqx_,

Yes, thank you. I've done this before, (add, edit or delete each table's data separately), but you've helped me a great deal by suggesting I give the Admin some screen shots, and "lead" them through the process of adding a new vehicle.

Now I see that I need to direct them to first add a Model if it doesn't exist, so that the Make table will have the right Model ID, then a new Make if it doen't already exist, then add a new Style if needed, then add a new Category, if needed. Then, the new Car record can be added. For a delete, it would be the reverse, and for an update, they would need to first check that the Model, Make, Style and Category were added first, then car. Is that all correct? I've added a screen shot of the way I was taught to create the admin display for a table that can add, edit and delete. If it's just for one table, I've got that covered. It's when I have to update two tables that is the challenge. I've done it once, but if I understand you correctly, that isn't necessary in this case. Is that correct?

I'll need to work on this over a weekend where I have some uninterupted time.

I think I know how to use drop-down select lists to populate the data from each table for the Add and Update forms.

May I run the code by you once completed, to see if you think it's done "correctly," not just "working?"

I'm going to start a new question to ask about the category search from the original example site I was trying to duplicate. I know I don't know how to use the MIN and MAX yet on the prices.

Thank you so very much for your help. You are awesome, and I'm so grateful because I've learned a lot!

elizabeth ;-)
example-admin-display.jpg
0
 
_agx_Commented:
Well, I think we are on slightly different wave-lengths about the admin screens ;-) (Though a lot depends on the application complexity, user base and normal usage according business rules).  

But I think this should be a separate question too keep things organized (ie it doesn't involve the original topic dependent select lists).
0
 
_agx_Commented:
> too

   Correction:  "to" not "too" ( ... or two or tu ;-)
0
 
Elizabeth2Author Commented:
Awesome help with a complex task!
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 14
  • 13
Tackle projects and never again get stuck behind a technical roadblock.
Join Now