mySQL Query using a result as a variable.....help plz!

[masterz2k]

Hi Guys,

Struggling to come up with a solution to a problem!

I want to query a table which will in turn givbe the table names to perform the rest of the query on,

Basicaly its:

webgrp table
headers table
pg***


webgroup contains an id and a table..

so if i was to query webgroup with ID = 1 . i would get back pg101, pg104, pg136.

i then want to query each table pg101, pg104, pg136 to return a value where jtype = $jtype. (basicaly a variable which has been pre defined by another menu system)


i'd also like to bring in a description column from the headers table.. basicaly that contains pg*** and a value to what that table contains.

can anyone suggest the start of a query that i can develope in the direction needed?


results from the pg*** tables once filtered by the $jtype will only number max 5.

so basicaly, id want to end up with a table like..(headers below)

webgrp / pggrp / description / value
eg: 1 pg104 / itemdesc / value from the pg104 table

any help would be very much appriciated!

[bazz]

I think you need to read up on 'database normalisation'. You shouldn't have a table for each page. better to have a table for all pages. This would take the form, probably, of your current page tables but with an additional column - 'page_id'

the table web group could then be

| group | page |
| 1 | 3 |
| 1 | 4 |
| 2 | 1 |
| 2 | 3 |

Then you could query the first table as you do but then join to table 'pages' something like this


select col1 col2 col3
FROM web_groups AS w
inner
join pages AS p
on p.id = w.id
and p.jtype = $jtype


(You should substitute col1 col2 col3 with the actual colnames you want to return).

If you were to post your 'create table statement' I would be able to help you with normalising it.

bazz

[masterz2k]

Hi Bazz,

many thanks for the reply...

I have contemplated with tge idea of merging all the tables together, all pg*** tables cover a certain product group which was why I wanted to keep them seperate...

also in the end there will be about 120-130 product group pg*** tables...

each table could theoreticaly be several tens of thousand lines long, which is why I opted to keep them seperately,

any way of doign what ive planned ?

andy

[bazz]

you need to remember that the db structure should have no bearing on how you want to output the data. Just because you want certain stuff for one page and other stuff for another doesn't mean it should be split across tables.

read up on normalisation. post some sample data for two of your 120 dd tables and I'll try to show you what I mean.

The numbers of records yoou are talking about should be well within the capabilities of MySQL provided yoou index the columns as appropriate.

bazz

[masterz2k]

Hi Bazz,

thanks again for the reply,

i'l work on merging all the tables together - they all use the same structure anyway so wont be difficult...

just thinking,

as im going to do this.......i'l base the rest of my system im developing to work from the one table - but would you be able to give pointers on code to split the tables back down to the seperates?

ie creating individual tables in one batch from the masterdata table ? (basing the new table name as a field contents?)

[bazz]

It is unlikely that you should have just one table. Please read up on normalisation. It will save you a LOT of trouble in the long run.

Post back with questions.

bazz

[masterz2k]

It is unlikely that you should have just one table. Please read up on normalisation. It will save you a LOT of trouble in the long run.

Post back with questions.

bazz

Hi Bazz,

i was meaning all from one table with regards to the 120 pg*** tables- these have been merged now... i'l try and play with your origional code snippet tonight to see what i can come up with!

many thanks for your advice,