many to many

andynightingale

This selection process was only a dummy and used one table with several columns, so the query was straightforward, comparing the db table cell to the variable in the drop down and populating the next drop down.

http://www.signal-pumps.co.uk/html/search/index.php

I now have all the final data and have put it into separate tables:
tbl_pump
tbl_capacity
tbl_chemical
tbl_max pressure
tbl_control option
tbl_power supply
each has two columns ie primary key and the description

all are linked many to many ie a pump can have many capacities and a capacity can be common to several pumps, a pump can use several chemicals and a chemical can be common to several pumps..... and so on.

The first pull down will be capacity, so will pull in all of the content of the capacity column ie

"SELECT capacity_desc FROM tbl_capacity"

The user makes their choice which becomes the variable in
<option value="capacity1" selected>capacity1</option>

So in the second drop down the query needs to be something like:

"SELECT Chemical_desc FROM tbl_chemical WHERE ? AND ? = '$capacity'"
ie only options matching the choice in drop one become available in drop down two.

This is where I am getting confused

Its the ? AND ? bit I'm confused over. I know I need to use a foreign key or two but from where?

pete_bisby

Hello again !!!

You still need a table that holds the pump identifier and the other identifiers (capacity, chemical, max. pressure, etc.) to describe the pump itself.

So, table tbl_pump would hold it's own primary key, it's description, and the other keys relating to capacity, chemical, etc.

This is probably the most efficient way of storing the data and querying the database. Plus, it is the only way of getting around your many-to-many relationships.

So, for example, once you have selected the correct capacity, the query for the next drop-down box would be:

SELECT DISTINCT Chemical_id, Chemical_desc FROM tbl_chemical A INNER JOIN tbl_pump B ON A.Chemical_id=B.Chemical_id WHERE B.Capacity_id='$capacity'

The query has SELECT DISTINCT, because you don't want duplicate records displaying in the drop-down box.

Hope this helps !

andynightingale

Thanks yet again Pete
I'm going to build all the tables and give it a go. Watch this space!!!

andynightingale


SELECT_DISTINCT_Chemical_id,_Chemical_desc_FROM_tbl_chemical_A_INNER_JOIN_tbl_pump_B_ON_A.Chemical_id=B.Chemical_id_WHERE_B.Capacity_id='$capacity'

mmmm???

What do A and B stand for?

pete_bisby

They're known as "aliases". If you don't use them, you query could look like this:

SELECT DISTINCT Chemical_id, Chemical_desc
FROM tbl_chemical INNER JOIN tbl_pump ON Chemical_id=Chemical_id
WHERE Capacity_id='$capacity'

This query would return an error, because of ambiguous column names - it doesn't know which table to use for Chemical_id.

So to alleviate this, you have to use the table names:

SELECT DISTINCT tbl_chemical.Chemical_id, tbl_chemical.Chemical_desc
FROM tbl_chemical INNER JOIN tbl_pump ON tbl_chemical.Chemical_id=tbl_pump.Chemical_id
WHERE tbl_pump.Capacity_id='$capacity'

But, because I'm a lazy sod, I substitute change the table name prefixes for letters - no difference in the query, just makes it more manageable.

SELECT DISTINCT A.Chemical_id, B.Chemical_desc
FROM tbl_chemical A INNER JOIN tbl_pump B ON A.Chemical_id=B.Chemical_id
WHERE B.Capacity_id='$capacity'

andynightingale

Pete
I was looking through my SQL book for complete idiots, but obviously in the wrong place. Its under creating aliases with 'AS' but says you can drop the 'AS' as it is optional.

As a novice I may use the second option till I get the hang of it. Being overly literal will help me keep a grip on things.

As usual Pete thanks for you patience. It does pay of. When I look at the PHP code for the selection page I don't glaze over any more. 99.9% of it makes sense (even though I know I couldn't sit and type it from scratch)

ps In the tbl_pumps where I have all the primary keys from all the other tables ie chemical_id, capacity_id do these all stay incremental descending from 1 and if one column only has ten cells with numbers and another has fifty do I put in 0 as the default to balance the columns?

andynightingale

I have a horrible feeling that tbl_pump is what they call a junction table and that if all the id columns from the separate tables are to make any sense along side of the pump_id and pump_desc there will be a lot of repeat entries to cover every permutation. Is this assumption correct?

pete_bisby

Yes, pretty much. The tbl_pump table requires all of the attributes relating to capacity, chemical, etc., to describe it.

This table will hold a lot of data, but once set with each attribute indexed (for faster searches) the table will work very well.

andynightingale

To work in phpmyadmin is quite slow. Is my best option to do it all in excel and import via one of these converter packages, or is there a simpler way?

pete_bisby

You can use Excel and save the spreadsheet as a CSV file - phpmyadmin can import these in very quickly.

Alternatively, you can use Access with the myODBC drivers, and conenct to MySQL that way. Data-entry via an Access table is quick this way.

Use the link below to get to the doenlaod page.

http://www.mysql.com/downloads/api-myodbc-3.51.html

andynightingale

Thanks Pete, as usual

andynightingale

Just an observation, not a question (honest), but I've come full circle. As I'm going to end up with a junction table with all permutations of pumps plus primary keys from all the other tables you could just create the one big table to incorporate everything.

pete_bisby

Yep, so you should end up with exactly what you had right at the beginning.

You can still have the smaller tables, to limit the input (from an admin point of view), so you won't need to amend the PHP/HTML code for the drop-down boxes each time a change occurs.

andynightingale

OK I've got 6000 rows in my excel sheet already ie pump_id x capacity_id x chemical_id. I've still got to multiply that by 7, then 4, the 4 again, so I'll end up with 672,000 rows.

Can Excel handle it?. If not, do I separate the spreadsheet into manageable chunks. Will mySQL handle this amount of rows and will I run into any problems importing this amount of data?

pete_bisby

Excel can handle 65,536 rows per worksheet, so it would be wise to split the information down.

I didn't know there was that many types of pumps on offer!

I've noticed that you've got "pump_id x capacity_id x chemical_id" - why would you need to duplicate the pump_id? The very nature of the pump_id is that it is unique within this table, and the rest of the information describes the specific pump.

Are you saying that a specific pump may have more than one capacity, and more than one chemical, etc. ?

I think a bit of a re-think is in order before you give yourself RSI from all the typing.

andynightingale

Ooops. for pump_id, read pump_description.

The pump_id would be the last thing I do to give the table its primary key.

And yes, there are 79 pumps, but these are variations on about 15 main types. Each pump has more than one capacity which in turn can handle more than one chemical, etc etc, hence the enourmous table.

I can't really see any other way of doing this other than to labouriously input all the data. If you can think of one please let me know as my eyesite is going and I had a colossal migraine today

Andy

pete_bisby

Create your unique values for the pump_description, capacity_id, chemical_id, etc. in their own tables.

You then create your overall table, which I'm sure you've already done, containing all of the attributes you need.

You then create a query, known as a "Cartesian Product", which will list all values from all tables. For example:

INSERT INTO overall_table (pump_description, capacity_id, chemical_id)
SELECT A.pump_description, B.capacity_id, C.chemical_id
FROM table_pump_description A, table_capacity_id B, table_chemical_id C

This should work, and it will save on the aspirin for the migraines !!

andynightingale

Thanks Pete

That's certainly a lot easier than doing hours of cut and paste in excel!!!

See statement below which works, but not using phpmyadmin over the net. It only filled the table as far as 127 rows before coming up with an error (something to do with phpmyadmin, dump.php.) Maybe I'll try when there's less traffic or Ill have to load it locally + mySQL and try again (not very stable on Windows ME)

At least I know it works from the rows it did fill.

INSERT INTO tbl_cartesian (pump_desc, capacity_id, chemical_id, back_pressure_id, control_option_id, power_supply_id)
SELECT A.pump_desc, B.capacity_id, C.chemical_id, D.back_pressure_id, E.control_option_id, F.power_supply_id
FROM tbl_pump A, tbl_capacity B, tbl_chemical C, tbl_back_pressure D, tbl_control_option E, tbl_power_supply F

Will it make a difference to the select statement in the selection page as I will now have every single combination possible whereas if I had filled the crossover table manually (which I wouldn't have done!!) it would have had only the correct combinations ie I would have left out where a capacity wasn't available to a particular pump etc?

pete_bisby

Yes, you have all possible combinations in one table, but at least you haven't typed them all in.

I think it is a lot easier deleting the records than entering them in from scratch.

Just make sure that you have the indexes in place before entering the data - the SELECT queries will run a lot faster.

andynightingale

Three more questions :-)

Is indexing straightforward? I will read up on this one

In your experience, do you think that searches on
this database are going to be slow?

Can I use sql to delete all the unwanted rows?