[noboost4you]

mocker, on 2 Feb, 2009 - 11:46 AM, said:

noboost4you: Since this is getting pretty specific with regards to what specific code you need, it would help if you posted the structure of the tables you are using. In your first post, I responded assuming you were able to design the tables from scratch, and therefore enforce a good design to work with. If you have to use two existing tables, then post what their fields look like.

The example javascript you found is part of what you need, but not the whole thing. You'd need your php code to generate the javascript code which loads the select groups

Yes, once I get everything written out, I will go back to restructure the tables; however, I don't know how in-depth these tables will be. A table may only have one or two columns in it.

E.g. - Stems Table - Column 1 (What stems) - Rows 1-#X will be the different stems available.

Or I could lump everything into one table (All Parts) and have as many columns as necessary (Series, Stems, etc). Then in the respective columns, the rows will house their parts.

In your opinion, which is the better route? With one table, I'll only ever have to call on one table. However, it will be one very large table.

Thanks

[noboost4you]

We're going to forget the entire concept of using drop down menus. Instead, I want to go this route in simple text/image hyperlinks.

Header (non-hyperlink - defines the family of the part)
e.g. Machine Screw

Sub-sections (components to make-up the overall part)
e.g. Head Style

Pan, Slotted, Round, Button... (hyperlinks)

e.g. Material

Steel, Stainless Steel, Brass, Plastic... (hyperlinks)

e.g. Length

3/32", 1/8", 5/32"... (hyperlinks)


This way the user can select whatever they already know. If they know they need the machine screw to be 1/8" in length, but don't know the head style or material, they will select 1/8"

A new page will load and at the top of the page, information starts to build the product. In this case it will say

Machine Screw
Length: 1/8"

The same sub-sections will still be available; Head Style, Material, etc.

However, since 1/8" was selected, the only head styles available are "Pan" and "Slotted. And the only materials available are "Steel" and "Brass"

User selects "Pan" head style

New page loads...

Machine Screw
Length: 1/8"
Head Style: Pan

Remaining sub-sections are below. User can select the Material they'd like to have, "Steel" or "Brass" since both materials are available in this length and head style.

User selects "Brass"

New page loads....

Machine Screw
Length: 1/8"
Head Style: Pan
Material: Brass

Underneath this information is that part's CAD drawing, PDF spec sheet, etc.

I found something similar to what I'm describing, but in ASP. We have UNIX servers, so ASP is a no-go.

Can this still be done in PHP and MySQL?

[CTphpnwb]

noboost4you, on 3 Feb, 2009 - 11:43 AM, said:

Can this still be done in PHP and MySQL?

Of course!

I'd have everything post to the same php page, but 'if' the post is for 'Machine Screw', then include/require the Machinescrew.php page.

[noboost4you]

Would you recommend a single database with X amount of tables based on all the different components? Or would you recommend a single database with a single table with X amount of columns for all the different components?

What should I research when I want to link component relationships?

e.g. IF [length] 1/8" is selected, THEN [material, head style, drive style, etc] choices appear.

Is this called anything in particular? If I know what to search for, it would make this process a whole lot easier.

Thanks

[CTphpnwb]

I think I'd have a single table with something like:
Part_number, Drivestyle, Material, Length, etc.

Then I'd let the user choose several of the criteria, click a submit button, and find out if there is a part number or numbers. For example, if they chose 1/2" Philips, the system might show part numbers for stainless, galvanized, and different thread sizes.

[noboost4you]

CTphpnwb, on 4 Feb, 2009 - 07:26 AM, said:

I think I'd have a single table with something like:
Part_number, Drivestyle, Material, Length, etc.

Then I'd let the user choose several of the criteria, click a submit button, and find out if there is a part number or numbers. For example, if they chose 1/2" Philips, the system might show part numbers for stainless, galvanized, and different thread sizes.

Ok, so you're saying to have one table with as many columns as there are components and each row would be a completed part, correct?

For example:
C1 Part Number | C2 Head Style | C3 Material | C4 Length
---------------------------------------------------------------------
R1 C1 9698875 | R1 C2 Pan | R1 C3 Steel | R1 C4 1/2"
R2 C1 9698876 | R2 C2 Button | R2 C3 Brass | R2 C4 1/8"
R3 C1 9698877 | R3 C2 Round | R3 C3 Stainless | R3 C4 1/2"

Then if someone chooses 1/2", only R1 and R3 are returned. Correct?

What if someone wants to go even deeper and select Material next, and so on and so forth. How can we continue to limit the results from what was selected by 1/2" in length to the next criteria?

Meaning, if I select 1/2" and it brings back 2 results, I want to be able to narrow down those 2 results when I select Stainless. I don't want it to forget we already selected 1/2" and bring up everything that is made in Stainless...make sense?

Thanks

This post has been edited by noboost4you: 04 February 2009 - 08:42 AM

[CTphpnwb]

You need to build your query on the fly:
$query = "SELECT * FROM yourdatabase WHERE ";
.
.
.
$query .= "headstyle = 'some value'";
.
.
.
$query .= "AND material = 'some other value'";
.
.
.



Of course, I'd probably do it in a loop of some sort.

This post has been edited by CTphpnwb: 04 February 2009 - 08:57 AM

[noboost4you]

Is our situation similar to "Many-to Many Relationships" in MySQL? Should I be researching that more in-depth or is what I want to do known as something else?

[noboost4you]

I'm working on designing the tables on paper and I'm following data normalization rules where each product category/component has it's own table which is linked to the parent table's Primary ID.

I hope to have a graphic posted later today showing my progress.

[noboost4you]

I attached the basic design of our tables as I thought to be correct. Is this correct?

[CTphpnwb]

For what you're doing? I'm sure you can build queries to make it work. Is Tbl_Body incomplete? It looks like Series 2,3, and 4 have no bodies. Same with Stem.

This post has been edited by CTphpnwb: 06 February 2009 - 10:02 AM

[noboost4you]

CTphpnwb, on 6 Feb, 2009 - 09:01 AM, said:

For what you're doing? I'm sure you can build queries to make it work. Is Tbl_Body incomplete? It looks like Series 2,3, and 4 have no bodies. Same with Stem.

Yes, every table is considerably incomplete. I just wanted to populate the tables with a few items for get the basic idea to make sure this will actually work. I could have one table with all the parts into their own columns and have a countless amount of rows with all the possible configurations, but that would be completely redundant.

I just wonder how I can query it all. Say the user first chooses 'TF1' from the Tbl_Stem_Packing table, a new page will need to load that takes away the option for picking a Bonnet considering only '100BT4' from the Tbl_Bonnet table is paired with that Stem/Packing combination. The choices for stems would also be reduced to '100ST2' and '100ST4'.

Any suggestions for which MySQL commands would go into the query? I'm not asking for the exact code [but I also won't stop you from posting it]

[CTphpnwb]

I see what you're trying to do, but since you have so many variables, I think it would be easier to let the user choose (or not choose) each of them, and then run your query based on their selections and show everything that meets those selections. The user could then narrow things down or expand their search by making additional choices. That's why I was thinking of one table. The table might be a little larger than having separate tables, but the query would be easier to build and I doubt the database would be too large in any case.

This post has been edited by CTphpnwb: 06 February 2009 - 11:01 AM

[noboost4you]

CTphpnwb, on 6 Feb, 2009 - 10:00 AM, said:

I see what you're trying to do, but since you have so many variables, I think it would be easier to let the user choose (or not choose) each of them, and then run your query based on their selections and show everything that meets those selections. The user could then narrow things down or expand their search by making additional choices. That's why I was thinking of one table. The table might be a little larger than having separate tables, but the query would be easier to build and I doubt the database would be too large in any case.

I think I see what you're saying.

Start with the base components first; e.g. thread, connection, body, stem, handles, etc.

If they choose the stem they want (e.g. 100ST1), then a new page will load showing the options for 100ST1. This way the user cannot start at the end and work his way backwards to the beginning.

However, since the base components can be dependent on each other (perhaps a certain thread size only matches up with a particular connection type) how would that work?

Originally, I was using Javascript chained selects. The user first chooses the Series. Then the next box appears where the user can select the thread size, etc. etc. I didn't like that. Like I mentioned earlier, the user may not know what thread size they need, but they know the rest of the components. They need to be able to pick what they know to logically deduce the finished part they need.

[CTphpnwb]

noboost4you, on 6 Feb, 2009 - 11:05 AM, said:

I think I see what you're saying.

Start with the base components first; e.g. thread, connection, body, stem, handles, etc.

If they choose the stem they want (e.g. 100ST1), then a new page will load showing the options for 100ST1. This way the user cannot start at the end and work his way backwards to the beginning.

Not exactly what I was thinking. Try the code below. It doesn't try to access a database, but it sets up the parameters for the query by creating a form with multiple option selects. What I would do is let the user choose any parameters they like, and then see if the item(s) exists in the database. If there is more than one, show a list of them and let them either narrow their selections down by adjusting my form and resubmitting, or by selecting an item from the displayed list.

<?php
session_start();
$len = $_SESSION['Length'];
$mat = $_SESSION['Material'];
$drive = $_SESSION['Drivestyle'];

if(isset($_POST['Length'])) {
	$len = $_POST['Length'];
	} 
if(isset($_POST['Material'])) {
	$mat = $_POST['Material'];
	} 
if(isset($_POST['Drivestyle'])) {
	$drive = $_POST['Drivestyle'];
	} 

$length = array ("1/8","1/4","1/2");
$material = array("Galvanized", "Brass","Nylon","Stainless");
$drivestyle = array("Slotted","Philips", "Hex");

echo '<form action="'.$_SERVER['PHP_SELF'].'" method="post">';
echo '<select name= "Length">';
foreach($length as $item) {
	if ($item == $len) {
		$Length_selected = "selected";
	} else {
		unset($Length_selected);
	}
	echo '<option value="'.$item.'" '.$Length_selected.' >'.$item.'</option>';
}
echo '</select>';


echo '<form action="'.$_SERVER['PHP_SELF'].'" method="post">';
echo '<select name= "Material">';
foreach($material as $item) {
	if ($item == $mat) {
	echo $item;
		$mat_selected = "selected";
	} else {
		unset($mat_selected);
	}
	echo '<option value="'.$item.'" '.$mat_selected.' >'.$item.'</option>';}
echo '</select>';


echo '<form action="'.$_SERVER['PHP_SELF'].'" method="post">';
echo '<select name= "Drivestyle">';
foreach($drivestyle as $item) {
	if ($item == $drive) {
		$drive_selected = "selected";
	} else {
		unset($drive_selected);
	} 
	echo '<option value="'.$item.'" '.$drive_selected.' >'.$item.'</option>';
}
echo '</select>';
echo '<input type="submit" /></form>';

$_SESSION['Length'] = $len;
$_SESSION['Material'] = $mat;
$_SESSION['Drivestyle'] = $drive;

?>

So for example, if the user chose 1/4 Stainless Philips then every part number that met those criteria would be pulled from the database:

$query ="SELECT * FROM mytable WHERE Length = '1/4/' AND Material = 'Stainless' AND Drivestyle = 'Philips'";

This post has been edited by CTphpnwb: 06 February 2009 - 01:02 PM