ColdFusion/MySQL Searching database using dynamic multiple related selects
Using ColdFusion, I need to figure out how to do the three types of searches, data display and sort, found as an example on this page: http://www.lisaturn.com/search.asp. I need to create a similar page using similar data, and the searches will be identical to these examples using used cars.
I know how to create a CF database application and display data from the database, as well as create a password protected Admin display page of the full database data with an ADD, UPDATE or DELETE button so that the admin can manage the database data (if it's all in one table) I actually already have that part of the application completed. I also know how to display that data on a web page. What I don't know how to do, is to search the database with these dynamic multi selects (Make and then specifi Models), or how to display the multiple record counts (Under Search by Category), or how to sort the resulting searches (Sort Vehicles By). I was hoping to tackle each function one at a time, beginning with the searches if you could help me.
To begin, I first need to know if these searches can be done using one MySQL database table that includes all the information about each used car, or... QUESTION ONE: will I need to have a separate table for Make, one for Model, and one for Category? QUESTION TWO: The other database design question that I have is if there needs to be additional tables to normalize the database, then which table gets the foriegn key? I am always confused about that.
Currently, below is the one and only table I've been trying to work with, so far to figure out how to create these search elements:
TABLE NAME: SHM_Used_Cars
SHM_UsedCarsID
SHM_PhotoName
SHM_CarStyle
SHM_CarEngine
SHM_CarTransmission
SHM_CarExteriorColor
SHM_CarInterior
SHM_CarStockNum
SHM_CarMileage
SHM_CarPrice
SHM_CarSpecialPrice
SHM_CarComments
SHM_CarVINNum
SHM_CarYear
SHM_CarMake
SHM_CarModel
SHM_CarCategory
DateTime
After I determine how many tables I will need, then I will proceed to ask about how to accomplish the remaining search functions, one at a time. I say one at a time because being self-taught, I need to figure out these tasks as I'm able to comprehend and learn what's going on. If I am over-whelmed with too many instructions at once, I just get confused and will waste both your time and mine. Therefore, I am seeking a solution one step at a time, if you don't mind helping me.
I should also mention that I've been trying to figure this out myself for over two weeks now. I've tried multiple examples that I've found on this site and others, but have not been able to get anything to work. I can show you one of the examples that I have so far, if you need me to, but I just think I should start over, if you don't mind helping me.
The attached code is one of the examples I am working on, but the process page is not working right, so I think I need to start over.
Your help is greatly appreciated. Thank you so much.
I know how to create a CF database application and display data from the database, as well as create a password protected Admin display page of the full database data with an ADD, UPDATE or DELETE button so that the admin can manage the database data (if it's all in one table) I actually already have that part of the application completed. I also know how to display that data on a web page. What I don't know how to do, is to search the database with these dynamic multi selects (Make and then specifi Models), or how to display the multiple record counts (Under Search by Category), or how to sort the resulting searches (Sort Vehicles By). I was hoping to tackle each function one at a time, beginning with the searches if you could help me.
To begin, I first need to know if these searches can be done using one MySQL database table that includes all the information about each used car, or... QUESTION ONE: will I need to have a separate table for Make, one for Model, and one for Category? QUESTION TWO: The other database design question that I have is if there needs to be additional tables to normalize the database, then which table gets the foriegn key? I am always confused about that.
Currently, below is the one and only table I've been trying to work with, so far to figure out how to create these search elements:
TABLE NAME: SHM_Used_Cars
SHM_UsedCarsID
SHM_PhotoName
SHM_CarStyle
SHM_CarEngine
SHM_CarTransmission
SHM_CarExteriorColor
SHM_CarInterior
SHM_CarStockNum
SHM_CarMileage
SHM_CarPrice
SHM_CarSpecialPrice
SHM_CarComments
SHM_CarVINNum
SHM_CarYear
SHM_CarMake
SHM_CarModel
SHM_CarCategory
DateTime
After I determine how many tables I will need, then I will proceed to ask about how to accomplish the remaining search functions, one at a time. I say one at a time because being self-taught, I need to figure out these tasks as I'm able to comprehend and learn what's going on. If I am over-whelmed with too many instructions at once, I just get confused and will waste both your time and mine. Therefore, I am seeking a solution one step at a time, if you don't mind helping me.
I should also mention that I've been trying to figure this out myself for over two weeks now. I've tried multiple examples that I've found on this site and others, but have not been able to get anything to work. I can show you one of the examples that I have so far, if you need me to, but I just think I should start over, if you don't mind helping me.
The attached code is one of the examples I am working on, but the process page is not working right, so I think I need to start over.
Your help is greatly appreciated. Thank you so much.
<cfquery name="q_all_makes" datasource="designpubdemo" username="elizabeth3" password="webdesyne3">
SELECT DISTINCT SHM_CarMake
FROM SHM_Used_Cars
ORDER BY SHM_CarMake
</cfquery>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title></title>
</head>
<body>
<form id="form1" name="form1" method="post" enctype="multipart/form-data" action="search_new_process4.cfm">
<select name="SHM_CarMake">
<option value="0">Select Vehicle Make</option>
<cfoutput query="q_all_makes">
<option value="#SHM_CarMake#">#SHM_CarMake#</option>
</cfoutput>
</select>
<input type="submit" name="button" id="button" value="Search" />
</form>
</body>
</html>
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 HTH,
Thank you so much! This is exactly what I needed for this part of the process.
Instead of a separate table for Style, I chose to create a table named Category, since that is one of the searches performed on my example site: http://www.lisaturn.com/search.asp, and Category is really being used as the "Style" filter, just named differently. There is a car style, but it is only an attribute in the Car table, and is not used as a search filter. As seen in the example site, Category has these attributes, so I've created a Category table with these same values:
CategoryID CategoryName
1 Certified
2 Coupes
3 Sedans
4 SUVs
5 Trucks
6 Under $10,000
7 $10,000 - $20,000
8 $20,000 and Up
I'm not sure why the developer of the example site decided to create this kind of search, because a car could clearly have more than one of these "Categories," which is confusing, but my task is to duplicate this example site, with a better visual layout and design for the display, but functionally the same, so I went with a Category table using the same values of the example site.
I'm trying to keep this as simple as I can, so that when I'm creating the display pages, I will be able to keep up with all the queries that will be necessary to pull all of this together. I'm not that experienced with JOINs, though I have used them once or twice in the past, it's been a while since I had to wrap my brain around the concept, so simple is better when possible.
I have created and populated the tables as you suggested, following the model you outlined above. I also created the PriceRange table as suggested. I'm not quite sure how to populate it at this point, as I'm not completely sure how this will be used, but am hopeful that you will elaborate as I progress, that is, if you are willing to continue to help me. And, that is my next question. Are you willing to continue to help me with these tasks? I sure hope so. It seems you understand exactly what I need to do.
Let me thank you once more, and please let me know what you suggest would be my next step.
As I had previously mentioned, and had previously attached the code, I have the one (Search) select list working here: http://www.designpubdemo.com/SHM/search.cfm, but was hoping you could help me to create the second dependent "Model "select list based on the new tables and dynamically fill in the "Model" select list based on which Make was selected. Also, to get my test example to work, I had to hard code the "Models" into my form. I'm hoping there is a better way to dynamically populate that form and select lists, (please see the previoulsly attached code) but all of my attemps to dynamically create the select list, either reproduced multiple "Makes" (multiple Ford, multiple GMC, etc...within the list, or on the display page, I got the right number of records, but they were not matching the correct data - very frustrating)
I now have these tables:
SHM_Category
SHM_Make
SHM_Model
SHM_PriceRange
SHM_Used_Cars
I have put a FK column for MakeIDFK, Model IDFK and CategoryIDFK into the Used_Cars table, but I'm aware that Model is a subset of Make, so I also have the ModelIDFK in the Make table. My question is, since it is already in the Model table, should I delete it from the Used_Cars table? I put it in both because I wasn't sure how I'll eventually pull that information out, so could you help me understand this relationship?
this is my current understanding of the tables:
SHM_Used_Cars
SHM_Category
SHM_Make
SHM_Model
SHM_PriceRange
Until I hear back, I will be attempting to figure this out, but I do think this is over my head, and will need your help, please.
Gratefully,
elizabeth ;-)
Thank you so much! This is exactly what I needed for this part of the process.
Instead of a separate table for Style, I chose to create a table named Category, since that is one of the searches performed on my example site: http://www.lisaturn.com/search.asp, and Category is really being used as the "Style" filter, just named differently. There is a car style, but it is only an attribute in the Car table, and is not used as a search filter. As seen in the example site, Category has these attributes, so I've created a Category table with these same values:
CategoryID CategoryName
1 Certified
2 Coupes
3 Sedans
4 SUVs
5 Trucks
6 Under $10,000
7 $10,000 - $20,000
8 $20,000 and Up
I'm not sure why the developer of the example site decided to create this kind of search, because a car could clearly have more than one of these "Categories," which is confusing, but my task is to duplicate this example site, with a better visual layout and design for the display, but functionally the same, so I went with a Category table using the same values of the example site.
I'm trying to keep this as simple as I can, so that when I'm creating the display pages, I will be able to keep up with all the queries that will be necessary to pull all of this together. I'm not that experienced with JOINs, though I have used them once or twice in the past, it's been a while since I had to wrap my brain around the concept, so simple is better when possible.
I have created and populated the tables as you suggested, following the model you outlined above. I also created the PriceRange table as suggested. I'm not quite sure how to populate it at this point, as I'm not completely sure how this will be used, but am hopeful that you will elaborate as I progress, that is, if you are willing to continue to help me. And, that is my next question. Are you willing to continue to help me with these tasks? I sure hope so. It seems you understand exactly what I need to do.
Let me thank you once more, and please let me know what you suggest would be my next step.
As I had previously mentioned, and had previously attached the code, I have the one (Search) select list working here: http://www.designpubdemo.com/SHM/search.cfm, but was hoping you could help me to create the second dependent "Model "select list based on the new tables and dynamically fill in the "Model" select list based on which Make was selected. Also, to get my test example to work, I had to hard code the "Models" into my form. I'm hoping there is a better way to dynamically populate that form and select lists, (please see the previoulsly attached code) but all of my attemps to dynamically create the select list, either reproduced multiple "Makes" (multiple Ford, multiple GMC, etc...within the list, or on the display page, I got the right number of records, but they were not matching the correct data - very frustrating)
I now have these tables:
SHM_Category
SHM_Make
SHM_Model
SHM_PriceRange
SHM_Used_Cars
I have put a FK column for MakeIDFK, Model IDFK and CategoryIDFK into the Used_Cars table, but I'm aware that Model is a subset of Make, so I also have the ModelIDFK in the Make table. My question is, since it is already in the Model table, should I delete it from the Used_Cars table? I put it in both because I wasn't sure how I'll eventually pull that information out, so could you help me understand this relationship?
this is my current understanding of the tables:
SHM_Used_Cars
SHM_Category
SHM_Make
SHM_Model
SHM_PriceRange
Until I hear back, I will be attempting to figure this out, but I do think this is over my head, and will need your help, please.
Gratefully,
elizabeth ;-)
ASKER
Whoops! I hit the wrong key and submiited my comment before I was finished. I was working on the paragraph that starts with...
this is my current understanding of the tables:
SHM_Used_Cars
SHM_Category
SHM_Make
SHM_Model
SHM_PriceRange
What I was trying to write was that I understand that the SHM_Used_Cars table has a relationship with Make and Category and that Make has a relationship with Model. I am attaching my database tables for you, so you can see what I have so far.
Will you please let me know if these are correct?
Thank you so much.
elizabeth
this is my current understanding of the tables:
SHM_Used_Cars
SHM_Category
SHM_Make
SHM_Model
SHM_PriceRange
What I was trying to write was that I understand that the SHM_Used_Cars table has a relationship with Make and Category and that Make has a relationship with Model. I am attaching my database tables for you, so you can see what I have so far.
Will you please let me know if these are correct?
Thank you so much.
elizabeth
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
ASKER
Hi HTH,
I have made some progress in the way of the layout and display of the data. Please see this new page which has the same funtional code, but has been formatted and styled: http://www.designpubdemo.com/SHM/search_layout.cfm
Are you able to contintue to help me today?
elizabeth
I have made some progress in the way of the layout and display of the data. Please see this new page which has the same funtional code, but has been formatted and styled: http://www.designpubdemo.com/SHM/search_layout.cfm
Are you able to contintue to help me today?
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
First, do I have your name correct? Is it HTH or _agx_? I didn't want to seem stupid and/or disrespectful and use the wrong name. ;-)
Second, thank you. You don't know how grateful I am for your help.
Third, your points are excellent. I do understand most of what you've explained, except for the part about "parse the user-selected filter." I will definately need your future help with that query. I guess my struggle is that I don't know how to write the complex queries that will pull the multiple tables together for display and/or administration, so I tend to only use one large table. I do know how to query more than one table, but another reason that I tend to use only one is because I also have to create an Admin backend for the client to manage the database. I've done this before with simpler tables that didn't have searches. It's easier to just insert or update one table. I've used GetMaxID to insert into a second table, but only once a couple years ago, and I'm still not sure if I did it the best way, but it did work. I sort of stumbled through that one. Being self-taught is so hard sometimes. Finding this website has been awesome!
I have split up the question as you suggested. I posted it a few minutes ago to address just the multiple select lists search feature.
I will go back now and emplement your database suggestions, and am available all day to continue with any suggestions you might have to futher tweak the database. This is so FUN! Thank you again.
Am I correct that "Car.Certified" would just be a Yes/No option in MySQL?
Also, concerning this statement, "If you use a single key, the db would be missing a lot of info. Not to mention it will very easily get out of synch or corrupted. " so I would just have the Category Table on it's own, no relationship in the database structure, but when that data needs to be extracted, I would be using CF and Queries? Is that correct? In other words make the code do the work, instead of the database structure? Let me know if I'm out in left field.
elizabeth ;-)
Second, thank you. You don't know how grateful I am for your help.
Third, your points are excellent. I do understand most of what you've explained, except for the part about "parse the user-selected filter." I will definately need your future help with that query. I guess my struggle is that I don't know how to write the complex queries that will pull the multiple tables together for display and/or administration, so I tend to only use one large table. I do know how to query more than one table, but another reason that I tend to use only one is because I also have to create an Admin backend for the client to manage the database. I've done this before with simpler tables that didn't have searches. It's easier to just insert or update one table. I've used GetMaxID to insert into a second table, but only once a couple years ago, and I'm still not sure if I did it the best way, but it did work. I sort of stumbled through that one. Being self-taught is so hard sometimes. Finding this website has been awesome!
I have split up the question as you suggested. I posted it a few minutes ago to address just the multiple select lists search feature.
I will go back now and emplement your database suggestions, and am available all day to continue with any suggestions you might have to futher tweak the database. This is so FUN! Thank you again.
Am I correct that "Car.Certified" would just be a Yes/No option in MySQL?
Also, concerning this statement, "If you use a single key, the db would be missing a lot of info. Not to mention it will very easily get out of synch or corrupted. " so I would just have the Category Table on it's own, no relationship in the database structure, but when that data needs to be extracted, I would be using CF and Queries? Is that correct? In other words make the code do the work, instead of the database structure? Let me know if I'm out in left field.
elizabeth ;-)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much. This gives me a lot to do. I will post back once I've completed these suggestions and insights.
I'm sorry to say that I don't know anything about ENUM. I found this explaination below, but have no idea how it is applied when pulling data from the database to display or administrate. Nonetheless, I'm hoping to learn and grow, so I will just follow your lead where indicated and change the TYPE as suggested.
===================
ENUM is short for ENUMERATED list. This column can only store one of the values that are declared in the specified list contained in the ( ) brackets.
ENUM('y','n') You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted.
SET is similar to ENUM except SET may contain up to 64 list items and can store more than one choice.
===================
Thank you,
elizabeth
I'm sorry to say that I don't know anything about ENUM. I found this explaination below, but have no idea how it is applied when pulling data from the database to display or administrate. Nonetheless, I'm hoping to learn and grow, so I will just follow your lead where indicated and change the TYPE as suggested.
===================
ENUM is short for ENUMERATED list. This column can only store one of the values that are declared in the specified list contained in the ( ) brackets.
ENUM('y','n') You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted.
SET is similar to ENUM except SET may contain up to 64 list items and can store more than one choice.
===================
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.
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_,
I've been going through all of your suggestions and have these questions and answers to your question:
You wrote...
A couple thoughts about the tables
Unless space is an issue, don't make the sizes too small. Make sure you've allotted enough capacity and leave yourself room to grow.
No, size is not an issue as far as I know, but just so I understand, do you mean the number of characters that I alloted for each column? What do you mean by "room to grow?" I just picked a number of characters such as (50) because I don't know of any car Model name, for instance, that would be longer than 50 characters. That's how I usually determine that number. Am I misunderstanding something about setting or defining that number? I had taken an online beginner database design course, and nothing much was covered concerning that particular topic, so I'm just winging it when picking these numbers. Anything you can tell me that would help me understand better would be greatly appreciated. When you say "grow" what do you mean specifically? Grow in the number of characters? Can you please give me an example?
I have made the changes to the SHM_Make table as suggested, and am just following each thread to make sure I emplement all your suggestions:
You wrote (and I have completed this change):
TABLE: SHM_Make
MakeID INT UNSIGNED No auto_increment
MakeName varchar(50) utf8_general_ci NOT NULL
* Make name must be unique and should never be null
-------------------
Thank you,
elizabeth ;-)
I've been going through all of your suggestions and have these questions and answers to your question:
You wrote...
A couple thoughts about the tables
Unless space is an issue, don't make the sizes too small. Make sure you've allotted enough capacity and leave yourself room to grow.
No, size is not an issue as far as I know, but just so I understand, do you mean the number of characters that I alloted for each column? What do you mean by "room to grow?" I just picked a number of characters such as (50) because I don't know of any car Model name, for instance, that would be longer than 50 characters. That's how I usually determine that number. Am I misunderstanding something about setting or defining that number? I had taken an online beginner database design course, and nothing much was covered concerning that particular topic, so I'm just winging it when picking these numbers. Anything you can tell me that would help me understand better would be greatly appreciated. When you say "grow" what do you mean specifically? Grow in the number of characters? Can you please give me an example?
I have made the changes to the SHM_Make table as suggested, and am just following each thread to make sure I emplement all your suggestions:
You wrote (and I have completed this change):
TABLE: SHM_Make
MakeID INT UNSIGNED No auto_increment
MakeName varchar(50) utf8_general_ci NOT NULL
* Make name must be unique and should never be null
-------------------
Thank you,
elizabeth ;-)
ASKER
Hi _aqx_,
I'm now looking at another suggestion you gave me:
Then construct those relationships:
MAKER / Columns: MakerID (PK), MakerName (Unique)
MODEL / Columns: ModelID (PK), ModelNum (Unique), MakerID (FK)
STYLE / Columns: StyleID (PK), StyleName (Unique)
CAR / Columns: CarID (PK), VINNum (Unique), ModelID (FK), StyleID (FK)
I have made the Name columns Unique for each Model, Make and Category as indicated above, but I have this question... In MySQL I don't have a similar option (located along with the options for Primary Key, Unique and Index) to make ModelID, or MakeID, or CategoryID a (FK). There is no option for that term, so for now, I selected "Index." Is that ok or right? Am I missing something in how that is supposed to be defined in MySQL?
In my table, I named that column ModelIDFK so I would know it was a FK, but there is no option for a FK that I can see in MySQL. Is that an option in other databases, or am I just missing where that option would be in MySQL? All I'm seeing available in MySQL is Primary Key, Unique and Index.
Thank you,
elizabeth
I'm now looking at another suggestion you gave me:
Then construct those relationships:
MAKER / Columns: MakerID (PK), MakerName (Unique)
MODEL / Columns: ModelID (PK), ModelNum (Unique), MakerID (FK)
STYLE / Columns: StyleID (PK), StyleName (Unique)
CAR / Columns: CarID (PK), VINNum (Unique), ModelID (FK), StyleID (FK)
I have made the Name columns Unique for each Model, Make and Category as indicated above, but I have this question... In MySQL I don't have a similar option (located along with the options for Primary Key, Unique and Index) to make ModelID, or MakeID, or CategoryID a (FK). There is no option for that term, so for now, I selected "Index." Is that ok or right? Am I missing something in how that is supposed to be defined in MySQL?
In my table, I named that column ModelIDFK so I would know it was a FK, but there is no option for a FK that I can see in MySQL. Is that an option in other databases, or am I just missing where that option would be in MySQL? All I'm seeing available in MySQL is Primary Key, Unique and Index.
Thank you,
elizabeth
ASKER
Hi _aqx_,
You wrote:
> Am I correct that "Car.Certified" would just be a Yes/No option in MySQL?
> Yep
Correction: Technically it would be a BIT or BOOL field in MySQL terms (not a varchar field)
In MySQL, I went to create the new column in the Used_Cars table. I did not see a BIT type, so I selected BOOL, but when I tried to save it, I got an SQL Error about the TYPE and Number I selected (I tried several different numbers such as 3, 50, 100) and each gave the same error. Finally, to get it to work, I selected VARCHAR and it then saved successfully.
I admit that I don't fully understand all of the TYPEs in MySQL, and what they do or don't do, nor the implications of each, but I have used VARCHAR before for a Yes/No column and in my SQL Queries would do a WHERE statement with LIKE to find the "Yes," or the "No" in a column. I"m sure there are other ways to do this type of column, that I don't yet understand, so if you could please help me understand why it should be BIT or BOOL, and why MySQL wouldn't accept those TYPEs, maybe if I understood what I was doing a little better ;-) I can go back and make changes to that column and get it right. Was my character number the problem? Any help understanding is appreciated.
As aways, thank you so much!
elizabeth ;-)
You wrote:
> Am I correct that "Car.Certified" would just be a Yes/No option in MySQL?
> Yep
Correction: Technically it would be a BIT or BOOL field in MySQL terms (not a varchar field)
In MySQL, I went to create the new column in the Used_Cars table. I did not see a BIT type, so I selected BOOL, but when I tried to save it, I got an SQL Error about the TYPE and Number I selected (I tried several different numbers such as 3, 50, 100) and each gave the same error. Finally, to get it to work, I selected VARCHAR and it then saved successfully.
I admit that I don't fully understand all of the TYPEs in MySQL, and what they do or don't do, nor the implications of each, but I have used VARCHAR before for a Yes/No column and in my SQL Queries would do a WHERE statement with LIKE to find the "Yes," or the "No" in a column. I"m sure there are other ways to do this type of column, that I don't yet understand, so if you could please help me understand why it should be BIT or BOOL, and why MySQL wouldn't accept those TYPEs, maybe if I understood what I was doing a little better ;-) I can go back and make changes to that column and get it right. Was my character number the problem? Any help understanding is appreciated.
As aways, 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
Hi _aqx_,
This is going to be a long one. I've pasted your suggestions/comments below, made your text italic, and added my comments and changes completed in indented bold text.
Before I begin, I wanted to show you the TYPE options that I have in MySQL, so that you can help me pick the right one. I don't see BIT or NUMERIC in the list. By NUMERIC did you mean I should select "INT?" Sorry, but this is definately a weak spot in my knowledge.
VARCHAR
TINYINT
TEXT
DATE
SMALLINT
MEDIUMINT
INT
BIGINT
FLOAT
DOUBLE
DECIMAL
DATETIME
TIMESTAMP
TIME
YEAR
CHAR
TINYBLOB
TINYTEXT
BLOB
MEDIUMBLOB
MEDIUMTEXT
LONGBLOB
LONGTEXT
ENUM
SET
BOOL
BINARY
VARBINARY
Your wrote:
A couple thoughts about the tables
Unless space is an issue, don't make the sizes too small. Make sure you've allotted enough capacity and leave yourself room to grow.
Consider making some of the CAR feature columns (engine, color, etc..) either a FK or an ENUM.
We had previously discussed this. I had left those columns for reference purposes only so I could populate the FK tables. I have now deleted those duplicate columns and only have the FK colums in the Used_Cars table.
TABLE: SHM_Model
ModelID INT UNSIGNED No auto_increment
ModelName varchar(50) utf8_general_ci NOT NULL
MakeID INT (FK) NOT NULL
* Model name and the FK MakeID should never be null.
* Model name must also be unique, or you'll have data integrity problems.
I have made these changes as suggested.
TABLE: SHM_STYLE
StyleID INT UNSIGNED No auto_increment
StyleName varchar(50) utf8_general_ci NOT NULL
* Style name should never be null (or how would users search by it ;-)
* Style name should always be unique
TABLE: SHM_Used_Cars
UsedCarsID INT UNSIGNED No auto_increment
VINNum varchar(30) utf8_general_ci Yes NULL ** are there any special rules about VIN #'s?
Transmission varchar(100) utf8_general_ci Yes NULL
ExteriorColor varchar(100) utf8_general_ci Yes NULL
Interior varchar(50) utf8_general_ci Yes NULL
StockNum varchar(50) utf8_general_ci Yes NULL
Mileage MUST BE SOME NUMERIC TYPE Yes NULL ** Price and mileage should always be numbers.
** Otherwise, you won't be able to search. Use a numeric type
Price MUST BE SOME NUMERIC TYPE Yes NULL
SpecialPrice MUST BE SOME NUMERIC TYPE Yes NULL
PhotoName varchar(255) utf8_general_ci Yes NULL
Comments varchar(1500) utf8_general_ci Yes NULL
ModelYear MUST BE NUMERIC TYPEYes NULL
MakeID INT (FK) NOT NULL
ModelID INT (FK) NOT NULL
StyleID INT (FK) NOT NULL
CreatedDate datetime NOT NULL?
* MakeID, ModelID, StyleID should all be foreign keys enforced with a constraint
Sorry. What does "enforced with a contraint mean? I currently have them as INDEX and my only other choices in MySQL were PRIMARY KEY or UNIQUE. There was no Foriegn Key option.
* "DateTime" is a reserved word. It's better to choose another name like "CreatedDate". In which case, it shouldn' t be null
elizabeth ;-)
This is going to be a long one. I've pasted your suggestions/comments below, made your text italic, and added my comments and changes completed in indented bold text.
Before I begin, I wanted to show you the TYPE options that I have in MySQL, so that you can help me pick the right one. I don't see BIT or NUMERIC in the list. By NUMERIC did you mean I should select "INT?" Sorry, but this is definately a weak spot in my knowledge.
VARCHAR
TINYINT
TEXT
DATE
SMALLINT
MEDIUMINT
INT
BIGINT
FLOAT
DOUBLE
DECIMAL
DATETIME
TIMESTAMP
TIME
YEAR
CHAR
TINYBLOB
TINYTEXT
BLOB
MEDIUMBLOB
MEDIUMTEXT
LONGBLOB
LONGTEXT
ENUM
SET
BOOL
BINARY
VARBINARY
Your wrote:
A couple thoughts about the tables
Unless space is an issue, don't make the sizes too small. Make sure you've allotted enough capacity and leave yourself room to grow.
In another thread, I asked a question about this one.
Consider making some of the CAR feature columns (engine, color, etc..) either a FK or an ENUM.
(This is mainly a personal preference) Prefixing the table names is a good idea. However, you don't need to re-prefix all of the columns with "SHM" or duplicate the car table column with "Car". Descriptive names are good, but you'll find things get long and unwieldy when prefixes are repeated multiple times.
One reason that I did not break the tables up into FKs, is that this site I'm building is for my boss, to use as a demo site to show potential clients. I don't actually have any data to use to populate these tables other than what I have gleened from that example site I showed you. In other words, I don't know what those features would be, or the different colors, or the different types of engines, etc... The other reason, is that I definately don't know how to build the complex queries it will take to JOIN all of them together, so I will need to lean on your help as this database has grown beyond my CF and SQL knowledge to display and search the data. Actually, the display page that I had working with one search select list (Model) now no longer works because the query isn't right anymore (because I deleted the original Make and Model columns now replaced with FK columns) with the current DB structure. I'll save that for a later question after we get this DB correctly designed.
You are right. I shouldn't have done that, but now my queries and display pages are using those names, so I'll be sure not to do that in the future.If a table has a foreign key (like CAR model number), don't have two columns for it. Just use the numeric value of the record ID from the MODEL table only
We had previously discussed this. I had left those columns for reference purposes only so I could populate the FK tables. I have now deleted those duplicate columns and only have the FK colums in the Used_Cars table.
TABLE: SHM_Model
ModelID INT UNSIGNED No auto_increment
ModelName varchar(50) utf8_general_ci NOT NULL
MakeID INT (FK) NOT NULL
* Model name and the FK MakeID should never be null.
* Model name must also be unique, or you'll have data integrity problems.
I have made these changes as suggested.
TABLE: SHM_STYLE
StyleID INT UNSIGNED No auto_increment
StyleName varchar(50) utf8_general_ci NOT NULL
* Style name should never be null (or how would users search by it ;-)
* Style name should always be unique
Good point! Thank you.
I don't have a STYLE table, but have used the name CATEGORY to represent that element, but apart from the actual word, I have made these exact changes to the CATEGORY table.
TABLE: SHM_Used_Cars
UsedCarsID INT UNSIGNED No auto_increment
VINNum varchar(30) utf8_general_ci Yes NULL ** are there any special rules about VIN #'s?
Sorry, I don't know?? How would I know that?Engine varchar(100) utf8_general_ci Yes NULL
Transmission varchar(100) utf8_general_ci Yes NULL
ExteriorColor varchar(100) utf8_general_ci Yes NULL
Interior varchar(50) utf8_general_ci Yes NULL
StockNum varchar(50) utf8_general_ci Yes NULL
Mileage MUST BE SOME NUMERIC TYPE Yes NULL ** Price and mileage should always be numbers.
** Otherwise, you won't be able to search. Use a numeric type
I changed this to "INT," is that the correct numeric TYPE?
Price MUST BE SOME NUMERIC TYPE Yes NULL
I changed all of these NUMERIC to "INT," is that the correct numeric TYPE?
SpecialPrice MUST BE SOME NUMERIC TYPE Yes NULL
PhotoName varchar(255) utf8_general_ci Yes NULL
Comments varchar(1500) utf8_general_ci Yes NULL
ModelYear MUST BE NUMERIC TYPEYes NULL
MakeID INT (FK) NOT NULL
ModelID INT (FK) NOT NULL
StyleID INT (FK) NOT NULL
CreatedDate datetime NOT NULL?
* MakeID, ModelID, StyleID should all be foreign keys enforced with a constraint
Sorry. What does "enforced with a contraint mean? I currently have them as INDEX and my only other choices in MySQL were PRIMARY KEY or UNIQUE. There was no Foriegn Key option.
* "DateTime" is a reserved word. It's better to choose another name like "CreatedDate". In which case, it shouldn' t be null
I've used this before (DateTime) and name it that way because it reminds me which TYPE I used in MySQL to get this part (time and date) to work, not only in the MySQL DB, but in my SQL queries and CF code to display the time and date. I'd rather just leave this column off for now, and tackle it separately, as date and time have always caused me problems and just adds another layer of complexity that isn't really necessary at this point. So for now, I'll just delete this column.Thank you so much,
In the past, when I make my database columns UNIQUE, I've had problems getting my queries and code to work and have spent countless hours trying to fix the problems, but when I would remove that contraint, the code would work, so being at a loss of how else to fix the problem, I went with that solution. Now that I have found this site, and know there is someone that I can ask questions of, I feel more confident that I can get this application and DB done right, and learn how to use the constraints AND still have my code to work correctly. I can't think of any examples right now, but this has come up before. Unfortunately, I'm only asked to build CF application one or two times per year, and don't get to work on these applications on a daily basis, so I almost have to relearn each time I begin a new application. I really appreciate your help. Having someone to help is a dream come true!
After I have made sure I've encourporated all your suggestions, I'll repost the tables to see if I have them right.
elizabeth ;-)
ASKER
Hi _aqx_,
You wrote:
"were originally defined as tinyint (0-255). Which means if you try to insert more than 255 model number records the insert will fail."
I had no idea these types had anything to do with "number of records." I was under the impression that they had to do with number of characters or number of place values for a number. This is important to know. Thank you.
You answered concerning this comment:
I have made the Name columns Unique for each Model, Make
Did you see my later comment about Model number? I thought about it and it seems possible that two manufacturers _might_ produce the same model number. So you model number (alone) probably shouldn't be unique.
In this instance, I was speaking of the Make, Model and Category tables. After the respective ID I had a column for NAME (ModelName, MakeName, CategoryName respectively). I'm not using Model "Numbers" or Make "Numbers" or Category "Numbers," so I'm not sure we are both on the same page with this one. I'm actually amazed at how well we've been able to communicate in writing about such complex concepts. I was thinking you meant that the NAME should be UNIQUE, but I also upon further thought, realize that there will be multiple records with the same NAME such as Make - FORD, or Model - Mustang. There could be several Fords and several Mustangs so making them UNIQUE wouldn't work, so I'll remove those UNIQUE constraints.
You wrote in answer to this comment:
In my table, I named that column ModelIDFK so I would know it was a FK, but there is no option for a FK that I can see in MySQL.
You are right. I'm using PHP MyAdmin to build and manage this database and not directly sending commands to the database. That's why there is no constraint for FK in this tool that I can see. I was mistakenly referring to this as MySQL when I should have said PHP MyAdmin tool. I guess I haven't learned enough to write my own statments to the database. I've only recently learned to create my tables that way (using statements imported into the PHP MyAdmin tool), but not knowing all the TYPEs and available commands, nor how to ALTER a table I've already created, I'm at a loss to make this change, even if I wanted to. I've only been using the CREATE TABLE commands that I know. Hm...I'll read that link/page you sent. Thank you.
Should I send you what I have now, as far as the tables are concerned, to see if we're ready to proceed, or if there are more DB edits to make?
I could thank you a thousand times and it wouldn't express how grateful I am for your help.
elizabeth :-)
You wrote:
"were originally defined as tinyint (0-255). Which means if you try to insert more than 255 model number records the insert will fail."
I had no idea these types had anything to do with "number of records." I was under the impression that they had to do with number of characters or number of place values for a number. This is important to know. Thank you.
You answered concerning this comment:
I have made the Name columns Unique for each Model, Make
Did you see my later comment about Model number? I thought about it and it seems possible that two manufacturers _might_ produce the same model number. So you model number (alone) probably shouldn't be unique.
In this instance, I was speaking of the Make, Model and Category tables. After the respective ID I had a column for NAME (ModelName, MakeName, CategoryName respectively). I'm not using Model "Numbers" or Make "Numbers" or Category "Numbers," so I'm not sure we are both on the same page with this one. I'm actually amazed at how well we've been able to communicate in writing about such complex concepts. I was thinking you meant that the NAME should be UNIQUE, but I also upon further thought, realize that there will be multiple records with the same NAME such as Make - FORD, or Model - Mustang. There could be several Fords and several Mustangs so making them UNIQUE wouldn't work, so I'll remove those UNIQUE constraints.
You wrote in answer to this comment:
In my table, I named that column ModelIDFK so I would know it was a FK, but there is no option for a FK that I can see in MySQL.
You are right. I'm using PHP MyAdmin to build and manage this database and not directly sending commands to the database. That's why there is no constraint for FK in this tool that I can see. I was mistakenly referring to this as MySQL when I should have said PHP MyAdmin tool. I guess I haven't learned enough to write my own statments to the database. I've only recently learned to create my tables that way (using statements imported into the PHP MyAdmin tool), but not knowing all the TYPEs and available commands, nor how to ALTER a table I've already created, I'm at a loss to make this change, even if I wanted to. I've only been using the CREATE TABLE commands that I know. Hm...I'll read that link/page you sent. Thank you.
Should I send you what I have now, as far as the tables are concerned, to see if we're ready to proceed, or if there are more DB edits to make?
I could thank you a thousand times and it wouldn't express how grateful I am for your help.
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_,,
Great answers. Thank you. Yes, I live in "workable," but would love to move toward "right."
Yes, I agree. The VIN numbers should be UNIQUE because there will never be two cars with the same VIN number.
Concerning STYLE table:
Oh, I see now.
I was thinking that I was just using the word "Category" instead of the word "Style" interchangebly (because on the example site Category is used as the type of search or filter), but now I realize those two concepts are not the same as I thought. Category is like a Sedan, Coupe, or SUV, whereas Style is 4door, 2door, etc...I will create that new table now, remove the SHM_CarStyle column and add StyleIDFK column.
Yes, I also agree, for now I will not pursue the UNIQUE and FK constraints, so that I don't get too far over my head.
Concerning TYPE for mileage and price. I don't have a NUMERIC option and don't yet know how to Alter these tables within PHP MyAdmin other than using the drop-down list provided in this tool, so I'm going to go with INT. Hopefully, that will work for mileage.
None of the prices that I see on the example site are using a decimal point, so I'm assuming that is not needed so I would think that INT would work too for this column, but will it work if I'm calculating PriceRange? Does the TYPE need to be DECIMAL for that table to function as intended?
I'm sorry to be so dumb, and pepper you with so many questions, but I have rarely had anyone to ask these questions to, it's like going to school without a teacher. Having a teacher is sooooo much better! I've got tons of books and refer to them constantly, but sometimes I don't know what questions to ask. You have taught me a lot, and I'm very grateful.
Thank you,
elizabeth
Great answers. Thank you. Yes, I live in "workable," but would love to move toward "right."
Yes, I agree. The VIN numbers should be UNIQUE because there will never be two cars with the same VIN number.
Concerning STYLE table:
Oh, I see now.
I was thinking that I was just using the word "Category" instead of the word "Style" interchangebly (because on the example site Category is used as the type of search or filter), but now I realize those two concepts are not the same as I thought. Category is like a Sedan, Coupe, or SUV, whereas Style is 4door, 2door, etc...I will create that new table now, remove the SHM_CarStyle column and add StyleIDFK column.
Yes, I also agree, for now I will not pursue the UNIQUE and FK constraints, so that I don't get too far over my head.
Concerning TYPE for mileage and price. I don't have a NUMERIC option and don't yet know how to Alter these tables within PHP MyAdmin other than using the drop-down list provided in this tool, so I'm going to go with INT. Hopefully, that will work for mileage.
None of the prices that I see on the example site are using a decimal point, so I'm assuming that is not needed so I would think that INT would work too for this column, but will it work if I'm calculating PriceRange? Does the TYPE need to be DECIMAL for that table to function as intended?
I'm sorry to be so dumb, and pepper you with so many questions, but I have rarely had anyone to ask these questions to, it's like going to school without a teacher. Having a teacher is sooooo much better! I've got tons of books and refer to them constantly, but sometimes I don't know what questions to ask. You have taught me a lot, and I'm very grateful.
Thank you,
elizabeth
ASKER
Hi _aqx_,
Yes, I believe I can get these tables exported. Please see attached. Is this what you need?
Also, I have to leave for lunch (I'm 3 hours late), but will return in a hour, if that's ok. If you become unavailable, no problem, I totally understand and will wait until you have a chance to post again.
Good to know your helping me is enjoyable, I love this stuff and if I could, I'd do nothing else!
Thank you!!
elizabeth ;-)
Yes, I believe I can get these tables exported. Please see attached. Is this what you need?
Also, I have to leave for lunch (I'm 3 hours late), but will return in a hour, if that's ok. If you become unavailable, no problem, I totally understand and will wait until you have a chance to post again.
Good to know your helping me is enjoyable, I love this stuff and if I could, I'd do nothing else!
Thank you!!
elizabeth ;-)
-- --------------------------------------------------------
--
-- Table structure for table `SHM_Used_Cars`
--
CREATE TABLE IF NOT EXISTS `SHM_Used_Cars` (
`SHM_UsedCarsID` int(5) unsigned NOT NULL auto_increment,
`SHM_PhotoName` varchar(255) default NULL,
`SHM_CarStyle` varchar(100) default NULL,
`SHM_CarEngine` varchar(100) default NULL,
`SHM_CarTransmission` varchar(100) default NULL,
`SHM_CarExteriorColor` varchar(100) default NULL,
`SHM_CarInterior` varchar(150) default NULL,
`SHM_CarStockNum` varchar(150) default NULL,
`SHM_CarMileage` int(30) default NULL,
`SHM_CarPrice` int(30) default NULL,
`SHM_CarSpecialPrice` int(30) default NULL,
`SHM_CarComments` varchar(3500) default NULL,
`SHM_CarVINNum` varchar(50) default NULL,
`SHM_CarYear` int(25) default NULL,
`MakeIDFK` int(4) unsigned NOT NULL,
`ModelIDFK` int(4) unsigned NOT NULL,
`CategoryIDFK` int(4) unsigned NOT NULL,
`SHM_Certified` varchar(10) default NULL,
PRIMARY KEY (`SHM_UsedCarsID`),
UNIQUE KEY `SHM_CarVINNum` (`SHM_CarVINNum`),
KEY `ModelIDFK` (`ModelIDFK`,`CategoryIDFK`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=28 ;
--
-- Dumping data for table `SHM_Used_Cars`
--
INSERT INTO `SHM_Used_Cars` (`SHM_UsedCarsID`, `SHM_PhotoName`, `SHM_CarStyle`, `SHM_CarEngine`, `SHM_CarTransmission`, `SHM_CarExteriorColor`, `SHM_CarInterior`, `SHM_CarStockNum`, `SHM_CarMileage`, `SHM_CarPrice`, `SHM_CarSpecialPrice`, `SHM_CarComments`, `SHM_CarVINNum`, `SHM_CarYear`, `MakeIDFK`, `ModelIDFK`, `CategoryIDFK`, `SHM_Certified`) VALUES
(1, '2010eclipse_gs.png', '4dr Car', '5.7 L', 'Automatic', 'Brilliant Black Pearl', 'Dark Slate Gray Leather', '17264', 8, 0, 0, 'THE OWNERS DEMO! MUST SEE TERESA OR DONNA FOR INTERNET PRICING! THIS IS A ALL OPTIONS 300C! AWD HEMI NAVIGATION! SATELITE DVD! MOONROOF! LEATHER! SO MUCH CAR FOR THE MONEY! CHROME WHEELS! BEST COLORS BLACK ON BLACK! Quality vehicles at real prices. There is only one Company Name. No one can beat our vehicle or warranty.Keep in mind the car that youre looking at today and deciding to buy tomorrow is the same car some one looked at yesterday, and is buying today. Finance with Company Name to take advantage of this great deal. Taxes, tags, and fees are additional. Call to set up a appointment, ask for Representative ext. (xxx)-xxx-xxxx!', '2C3LK63T39H6044002', 1994, 4, 6, 3, ''),
(7, '2010lancer_gts.png', 'gdfg', 'dgdf', 'transmission', 'gd', 'gdgfdg', 'dgd', 0, 0, 0, 'dgdfg', 'dgdg22', 1999, 2, 14, 4, ''),
(23, '2010lancer_gts.png', 'sds', 'sdfsdf', 'sdfsdf', 'sdfsd', 'sdfsdf', 'sdfsd', 0, 0, 0, 'qwe', 'fghfgh', 2005, 3, 19, 4, ''),
(24, '2010lancer-sportback_gts.png', 'ds', 'sd', 'sdfsd', 'sdf', 'sdfsd', 'fsdf', 0, 0, 0, 'sfsd', '3333', 2002, 5, 20, 3, ''),
(25, '2010lancer_gts.png', 'style', 'fasf', 'sfsf', 'sdf', 'sdf', 'sdf', 0, 0, 0, 'sdf', 'sdf', 2008, 3, 2, 1, ''),
(27, '2010lancer_gts.png', 'asas', 'asdasd', 'asd', 'sd', 'sasd', 'dadsa', 0, 0, 0, 'sdasd', 'asda', 1992, 2, 15, 5, '');
-- --------------------------------------------------------
--
-- Table structure for table `SHM_PriceRange`
--
CREATE TABLE IF NOT EXISTS `SHM_PriceRange` (
`PriceRangeID` tinyint(4) unsigned NOT NULL auto_increment,
`RangeMin` int(16) default NULL,
`RangeMax` int(16) default NULL,
PRIMARY KEY (`PriceRangeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
--
-- Dumping data for table `SHM_PriceRange`
--
-- --------------------------------------------------------
--
-- Table structure for table `SHM_Model`
--
CREATE TABLE IF NOT EXISTS `SHM_Model` (
`ModelID` tinyint(4) unsigned NOT NULL auto_increment,
`ModelName` varchar(50) default NULL,
`MakeIDFK` tinyint(4) unsigned default NULL,
PRIMARY KEY (`ModelID`),
KEY `MakeIDFK` (`MakeIDFK`),
KEY `ModelName` (`ModelName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=22 ;
--
-- Dumping data for table `SHM_Model`
--
INSERT INTO `SHM_Model` (`ModelID`, `ModelName`, `MakeIDFK`) VALUES
(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);
-- --------------------------------------------------------
--
-- Table structure for table `SHM_Make`
--
CREATE TABLE IF NOT EXISTS `SHM_Make` (
`MakeID` tinyint(4) unsigned NOT NULL auto_increment,
`MakeName` varchar(50) NOT NULL,
PRIMARY KEY (`MakeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;
--
-- Dumping data for table `SHM_Make`
--
INSERT INTO `SHM_Make` (`MakeID`, `MakeName`) VALUES
(1, 'BMW'),
(2, 'Ford'),
(3, 'GMC'),
(4, 'Chrysler'),
(5, 'Toyota'),
(6, 'Chevrolet'),
(7, 'Acura');
-- --------------------------------------------------------
--
-- Table structure for table `SHM_Category`
--
CREATE TABLE IF NOT EXISTS `SHM_Category` (
`SHM_CategoryID` tinyint(5) unsigned NOT NULL auto_increment,
`SHM_CategoryName` varchar(50) character set utf8 default NULL,
PRIMARY KEY (`SHM_CategoryID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;
--
-- Dumping data for table `SHM_Category`
--
INSERT INTO `SHM_Category` (`SHM_CategoryID`, `SHM_CategoryName`) VALUES
(1, 'Certified'),
(2, 'Coupes'),
(3, 'Sedans'),
(4, 'SUVs'),
(5, 'Trucks'),
(6, 'Under $10,000'),
(7, '$10,000 - $20,000'),
(8, '$20,000 and Up');
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm pretty sure it's version MySQL version 5.
Yes, I'm trying now.
elizabeth
Yes, I'm trying now.
elizabeth
ASKER
Hi _aqx_,
I ran the query, and received this error:
Error Executing Database Query.
Key column 'CategoryIDFK' doesn't exist in table
Did I run it correctly?
elizabeth
I ran the query, and received this error:
Error Executing Database Query.
Key column 'CategoryIDFK' doesn't exist in table
Did I run it correctly?
elizabeth
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes, that worked! Yea! I now have the new table.
elizabeth ;-)
elizabeth ;-)
Cool! You can drop it though :) It's just a test table. Give me a few minutes to work up what *I* might use for the base model. Then you can review it and see what you think.
... Also, can you run a script of multiple statements via phpMyAdmin? Like
INSERT INTO Table ....
INSERT INTO Table ....
INSERT INTO Table ....
(I don't use it that often)
INSERT INTO Table ....
INSERT INTO Table ....
INSERT INTO Table ....
(I don't use it that often)
ASKER
Ok, dropped. You know that I will go with what ever you suggest. I would love to learn to do it the "right" way instead of just what I may or may not get to work.
elizabeth
elizabeth
ASKER
I"m not sure. I can IMPORT a text file (.txt) to create a table, so it seems that it would work the same for an INSERT command, but I've never tried that. Should recreate the test table to try it out?
elizabeth
elizabeth
ASKER
Unfortunately, I've got to call it a day. My life is not my own. I'm not sure what time zone you are in. Here is is 7 PM EST, and other responsibilites are calling. I'll be back at work in the morning at 9 AM to hopefully continue.
Have a great evening.
Thank you so much.
elizabeth :-)
Have a great evening.
Thank you so much.
elizabeth :-)
PT. I am a bit behind you in time zones. Since you're leaving, I will post it later. Then and you can play around with it in the morning :) Have a great night!
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_,
Just an FYI...
We had an ice storm here in Atlanta overnight. I was up most of the night helping a family member who was stranded alongside an icy road with a hill he couldn't climb due to black ice. One woman was killed close to where he was, slid off the road, hit a tree. There was another 29-car pile-up near the airport. Crazy drivers in Atlanta when we get ice. I was trying to find him a warm place to stay overnight since he was about 40 miles away. I ended up sleeping in this morning, and very late to work, now I'm playing catch-up.
I have downloaded the queries, thank you very much. I have another project with a tight deadline for the rest of today, but will be available tomorrow. Do you respond on the weekends? If not, no problem, just asking. I can continue over the weekend if you are available, but whenever you are available is perfectly fine with me, just as you can.
Gratefully,
elizabeth ;-)
Just an FYI...
We had an ice storm here in Atlanta overnight. I was up most of the night helping a family member who was stranded alongside an icy road with a hill he couldn't climb due to black ice. One woman was killed close to where he was, slid off the road, hit a tree. There was another 29-car pile-up near the airport. Crazy drivers in Atlanta when we get ice. I was trying to find him a warm place to stay overnight since he was about 40 miles away. I ended up sleeping in this morning, and very late to work, now I'm playing catch-up.
I have downloaded the queries, thank you very much. I have another project with a tight deadline for the rest of today, but will be available tomorrow. Do you respond on the weekends? If not, no problem, just asking. I can continue over the weekend if you are available, but whenever you are available is perfectly fine with me, just as you can.
Gratefully,
elizabeth ;-)
Hi Elizabeth,
That sounds pretty severe. But I am glad you and your relative came through everything okay.
Yes, I often stop in once or twice on the w/e's (not always). Though my responses are a _lot_ more hit or miss than during the week. So though you will probably see me around anyway, Monday is a definite ;-)
That sounds pretty severe. But I am glad you and your relative came through everything okay.
Yes, I often stop in once or twice on the w/e's (not always). Though my responses are a _lot_ more hit or miss than during the week. So though you will probably see me around anyway, Monday is a definite ;-)
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_,
Oh, I see. So would smallint be the better choice? Thais is one of these golden nuggets of information I would have never known without your help. Thank you so much!
I will check in with you tomorrow, Monday, to ask what the next step should be. Because we've changed the database, my code no longer works of course, so I'm starting from scratch as far as the search select list is concerned. 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?
Thank you,
elizabeth
Oh, I see. So would smallint be the better choice? Thais is one of these golden nuggets of information I would have never known without your help. Thank you so much!
I will check in with you tomorrow, Monday, to ask what the next step should be. Because we've changed the database, my code no longer works of course, so I'm starting from scratch as far as the search select list is concerned. 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?
Thank you,
elizabeth
ASKER
Whoops! I just saw your other post about the column names. I guess the answer depends on how you suggest we proceed with the code. The code I had, only worked for one select list search for Make. I could do a find and replace in my code, but actually, my code was searching the old "one" large table that had Make, Model, and Category within the one and only table. My code actually broke when we created the new tables, and I deleted the original Make, Model, & Category columns within that one large table.
I actually thougth the reason you changed the column names in the new tables was because, as you had mentioned previously, there was no reason to make the columns have that prefix of SHM_ before each column name, (Table name yes, column name, no) so I thought we were just cleaning up my mess, so to speak ;-), which is fine, and was probably needed.
At this point, I can reconstruct my original queries, and I still have that example of the JOIN you provided, but now with so many tables, I will probably need your help wrapping my brain around the JOINs. I know there are different kinds of JOINs, used for different reasons, and that is where I get lost. I've done a lot of reading on the subject in days past, but haven't lately, so I'm kind of lost at this point. I learn best when I can see an example and follow the logic, then I can reproduce that login on other queries.
What do you suggest would be the next step?
Thank you,
elizabeth
I actually thougth the reason you changed the column names in the new tables was because, as you had mentioned previously, there was no reason to make the columns have that prefix of SHM_ before each column name, (Table name yes, column name, no) so I thought we were just cleaning up my mess, so to speak ;-), which is fine, and was probably needed.
At this point, I can reconstruct my original queries, and I still have that example of the JOIN you provided, but now with so many tables, I will probably need your help wrapping my brain around the JOINs. I know there are different kinds of JOINs, used for different reasons, and that is where I get lost. I've done a lot of reading on the subject in days past, but haven't lately, so I'm kind of lost at this point. I learn best when I can see an example and follow the logic, then I can reproduce that login on other queries.
What do you suggest would be the next step?
Thank you,
elizabeth
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I will probably need your help wrapping my brain around the JOINs.
No problem. This is the fun part ;-) Once you get the hang of them, you'll see regular JOIN's are pretty simple. OUTER JOIN's have a few nuances. But the concept's the same.
No problem. This is the fun part ;-) Once you get the hang of them, you'll see regular JOIN's are pretty simple. OUTER JOIN's have a few nuances. But the concept's the same.
ASKER
_AQX_ is absolutely an awesome Expert, with thorough comments/answers, and extremely helpful directions. Most importantly, I appreciate his willingness to help me "learn" the solution, for which, I am very grateful.
ASKER
But I still don't know how to append my existing code to populate the second select drop down list which would present the specific the Models, based on which Car Make was selected (which is not included on this test search page) Also, to get this to work, I hard coded the values for the Makes within my form. I have included the code for both pages, attached as one file. You will need to split them up into the respective pages, as noted in the comments.
I'm leaving for the day, but will return in the morning to continue working on this task. Thank you for any help you can provide.
Open in new window