Organising data from mysql - lost my head!

[gibbonuk]

Ok, i think ive crumbled my brain and lost my knowledge on what to do, so i need some help.

 

Basically, i have a simple mySQL DB, that had 10 tables, each containing one column called "value", and then on a page i did a loop that queried the DB and got each value column from the 10 tables.

 

But then i realised i might need more data tables and so realised it would be much better to have 1 tables and each value column in that one table, meaning only one query needed.

 

But ive got my self in a mess, as on the page the user can select which "value" column they want to look at.

 

So one user may want to look at values between (rows) 0 - 100, but only columns 2,4,8.

 

How do i do it? lol.

 

It was easy before as i just had a loop of 10, with a flag if to carry on or skip depending on the user selection, if the flag shows selected it gets the data from value column in table foo.

 

Hope you understand and can help.

Thanks

 

[gristoi]

use a table join. example:

 

SELECT table1.value , table2.value, table3.value FROM table1 JOIN table2 USING (id) JOIN table3 USING (id) etc.....

[gibbonuk]

Hi, thanks but unfortunatly that has confused me somewhat.

 

If your suggestion then to go back to using a table per set of values, and using this join function?

 

As aposed to have one table and many columns of values?

 

Thanks

 

[gristoi]

you need to look at how relationships between the tables work. Can you post your existing table structure. Without showing what you have done you are not going to get much help unfortunately

[PFMaBiSmAd]

Databases are not spreadsheets. Don't spread out same meaning data in columns in one table or use separate tables to hold same meaning data.

 

If you were to post an example showing the meaning and purpose of your data, someone can probably help with how your table(s) should be organized.

[gibbonuk]

Hi, ok ill try explain what im trying organize.

 

Basically the data is *values* come out of some hardware, thats all done on a server using some software, and then the "data" is inserted into an sql database for use online, charting ETC.

 

So the data being inserted is 10 x Different values every hour. And on a web page i want the user to select which data they want to look at, so a choice of 1 to 10, but its a multiple, could select 1, could select them all. Displaying them is then beyond this, but the data would be pulled from the mysql and placed in arrays.

 

So the way i did it before was one DB, 10 tables each with one column called "value". The 10 lots of values where inserted every 30 mins, and i programmed it so the user could select any amount of the 10 and look at them. I did this by a simple loop from 1 - 10, which per loop check if the user selected this one, if it did it went ahead and queried the database based on the table number from the current loop index and got the column data back and put them into an array. It then continues with the loop until exhausted.

 

But i then released this is currently using 10 queries per page,if i had many users at once this means lots of queries, and the "10 lots of values" maybe increased more over time, meaning more queries.

 

So i though it would be better to have 1 table, with 10 coloumns holding the data to do the same, but only using 1 query, but im having issues getting the "selected" data back and into arrays, and why im here

 

Hope this helps.

Andy

[gibbonuk]

Its ok, ive managed to find my brain (i think).

 

I now have 1 table with 10 columns of data.

 

One query gets the data from 10 columns and then i simply do the standard

 

*half pusedo code.*

while ($row = mysql_fetch_array($result)) {

    for ($i=0 to 10) {

      if $dataselected[$i] == true {

          put the data into arrays.

}

}

}

 

So this works, the question is now, will this be more optimised than the original code of having 10 tables and doing 10 queries?

 

Thanks