Using Loop to Create String from Post Variables

[dtyson2000]

Hello. Just a quick question about using a loop to create a string from post variables that will then be used in a query.

 

I have several post variables submitted in a form, some of which contain a value of T. Others are not selected. In the database there are several columns which may contain values of T or may not. I would like to construct a query that searches for specific columns containing T and ignoring the rest. I'm using WHERE "T" IN(colnames). I need to get the column names into a comma separated string. Here's what I'm using:

$postVal = array("1", "2", "3", "4", "5");
		foreach ($postVal as $key=>$val){
			$newVar = “col”.$val;
	
			if ($$newVar == "T") {
				*create/add to comma-separated string
				}
				else {
				*ignore
				}
			}

I hope that seems clear enough. I'm just not sure if I'm on the right track or not. What do you think? Thank you, in advance!

[Jacques1]

You're way, way off.

 

As I already told you in your previous thread, your database layout with those weird numbered columns is fundamentally flawed. You cannot build a sane application on top of this database. As you see, you cannot even select a simple value without an arcane hack and help from others. How are you going to deal with more complex tasks?

 

And what's with the “T” value? If this is supposed to be a boolean, use the actual BOOLEAN type.

 

Fix the database. If you don't know how, create a thread in the database section.

[mac_gyver]

in your previous thread, it was pointed out that you should not store a delimited list of values in one column. in this thread you have a sequence of named/numbered columns. this is just as bad for the same reason. you must write a ton of code/complicated queries to find or manipulate any of the data.

 

you need to store each piece of data in its own ROW. you can then write simple code and straightforward queries to find or manipulate any of the data, from one piece of data up to the entire set of data.

[dtyson2000]

Thank you for your response. In the last thread, I did exactly as it seemed I was being told. Removed the delimited list of values and threw them up in columns (as mentioned above) in an almost excel sort of format. Item 1, Item 2, Item 3, under which I simply added a value of T where applicable. Each ROW consists of an id, a name, a date, item 1, item 2, item 3... again with the last three having either a null value or a value of T (which, yes, I will change to a boolean type). 

 

I'm just not sure where I'm going wrong and REALLY want to be up to speed. Your response points me in a direction. But I'm just not sure how what I'm doing is not storing each piece of data in a row?

id  |  name  |  date  |  item1  |  item2  |  item3
1      dave    Y-m-d       T         NULL    NULL
2      Ann     Y-m-d       NULL      T       NULL

etc.

Edited August 8, 2016 by dtyson2000

[Jacques1]

As I already told you a month ago, SQL is not Excel. If you design your tables like spreadsheets, you'll end up with complete nonsense.

 

You're not storing the items as rows, you're storing them as columns within a row. A row-based schema looks like this:

table: items

parent_id | item_id | value
----------+---------+-------
1         |       1 | true
1         |       2 | false
1         |       3 | false
2         |       1 | false
2         |       2 | true
2         |       3 | false

See the difference? When you give your tables and columns meaningful names, I'm sure it will be even more obvious.

 

To summarize:

• SQL isn't Excel, it's based on the relational database model and follows specific rules (look up “normalization”)
• Storing multiple values in a single column is wrong; keeping a long list of numbered columns is also wrong

[dtyson2000]

Ok. I need to wrap my head around this. Thank you very much for pointing me in the right direction. I really had no idea. I've always done it the way I've always done it. I guess it's time for a change - and to start reading! Thank you, again!