Returning Columns From Query

[Fantmx]

I have a table in my SQL database that has about 45 columns. Each row is a date, and on any given date, the value in any of the columns can be null. My question is, how can I got about making a query that will return all of the columns with values other than null? I want the match to be made based on the date entered, and I would like to only have the columns with valid values returned. I am new to MySQL, so any help you could offer would be greatly appreciated. Thanks a lot!

[ConfusedOfLife]

Wow, hold on a minute, 45 columns in a table?! I really don't think that it's a good idea! I remember that I read somewhere (http://phpbuilder.com/columns/perdue20030310.php3) that you shouldn't have more than 10 fields in a table. Well, I really don't understand this design.

Back to your question, you only want the columns with a valid value back, what does it mean? Do you mean only the records that all their fields have valid values? It means if for your 3rd record, you see that one of the fields has a null value, then you don't want that record to be back, right? Or do you wana check all the fields seeing if a field has valid values for all it's records?! I assume that you want the first one, so, here it's my prescription:

SELECT * FROM myTable
WHERE 1 AND 'field1' != 'NULL' AND 'field2' != 'NULL' AND ....

It's probably an easier way if you use NOT IN, but then I couldn't find how! I tried it with 'NULL' NOT IN (field1, field2, ...) but whatever I did, it didn't work! Sorry!!

[Fantmx]

ConfusedOfLife, that is how I have been doing it, but I was wondering if there was a way that would prevent me from having to type out all of the columns I want returned. I will try to explain what I am doing a little better.... I want to be able to keep track of my lifting schedule, and since I don't do all of the excercises every day, some of them have a value of null the days that I do not do them. That is why I have so many columns. One for each excersice. So if I want to see what I did on say monday, I don't want to have to sort through all of the null values, just to find 10-15 lifts that I acually did. I hope that clears it up a bit

[raf]

I don't think there is an alternative to the and and and and ... condition.

But if i read your post, i don't think you have the right design.

Say you build a table exercise like
exerciceID|exercicename|...
1 | lifting | ...
2 | pushing bblabla | ...
...
So let's say a table of 45 records and 4-5 columns

And you have another table like
trainingID | traindate | starttime |endtime | weekday | ...
1 | well, you can imagen the rest, i presume
...
so let say a table of 1000 records (if you work out each day for 3 years) an 6 - 7 columns

And you then have a table like
taskID | trainingID | exerciceID | measure | ....
--> for each exercice in each session, you then have a record

Just imagen how simple it would be to get all the exercises you did on monday + what other sort of stuff you could store and select for each exercice, session, or exercice-task you did.


Creating a different column for each exercice, is typical spreadsheet-thinking. Relational databases follow a different logic (= creating an exercise table and then use the primery key of that table as a foreign key in another table where each exercise for each training, for each client etc has its a record)

Just let us know if you need more info on what relational databases can do for you.

[Fantmx]

Raf, thanks for the reply. I am little confused as to how I should set this up... Right now I have it as follows

date | bench | squat | hang_clean |etc...
07-21-03 | 10, 10, 10 | 8, 8, 8 | NULL | etc...

Then I have another table that is setup the same, except I want to be able to enter the weights that I used into that one, through a PHP interface. The reason I set it up this way is because I don't do the same workout every monday, the sets change, and so do the lifts. Would the design you have described still work for me? Also, where would be a good place to figure out this whole relational database concept? Thanks for your time.

[raf]

Yes, the db-design would still work.

You'd have a facts-table like

exerciceID |weight | number | trainingID
5 | 10 | 10 |1253
3 | 8 | 8 | 1253

In the exercise table, you'd have something like

exeID | exeName
5 | squat
3 | hang_clean

In the trainingID

trainID | date | weekday
1253 | 2003-25-03 |monday

So if you then need info from a weekday or date or trainingsession, you just select all records from that training and with an inner join on the execrices table, you can get the exercisesname and details.


To insert it, you can write a PHP page where you just select the exercise from a dropdown (easy to select all records from the exercise table), and insert the weight and numbers. The training-info can all be selected and inserted automatically.
If you have some sort of trainingschema, you could automatically generate the exercises that need to be done on that day or select the details from your previous trainign and adjust them and save them as a new training.


About relational databases : run a search here or on google or so.