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: https://www.experts-exchange.com/questions/25022079/ColdFusion-MySQL-Searching-database-using-dynamic-multiple-related-selects.html?anchorAnswerId=26192857#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
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: https://www.experts-exchange.com/questions/25022079/ColdFusion-MySQL-Searching-database-using-dynamic-multiple-related-selects.html?anchorAnswerId=26192857#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
ASKER
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
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
> 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).
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).
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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"
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
;-) ;-) ;-) ;-) ! ! ! 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"
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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. ';-)
> 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 ;-)
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 ;-)
> 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.
https://www.experts-exchange.com/Community_Support/General/
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.
https://www.experts-exchange.com/Community_Support/General/
ASKER
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. '-)
ASKER
Sorry, I meant "two" queries, not "to" queries.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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 ;-)
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 ;-)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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).
But I think this should be a separate question too keep things organized (ie it doesn't involve the original topic dependent select lists).
> too
Correction: "to" not "too" ( ... or two or tu ;-)
Correction: "to" not "too" ( ... or two or tu ;-)
ASKER
Awesome help with a complex task!
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.