I have a table I'm trying to use as a "lookup" table, used in building a "company event" list box. The table contains one record for each company, per their list box requirements. The fields look like the following :
company_name | Birthday | Meeting | Travel . . .
with the follow sample data :
Acme Corp. | 0 | 1 | 1 | . . .
The company name field will contain each company. The rest of the fields are the actual "events" that will populate that company's list box. If that particular company uses that event, I place a 1 in that column, for that record. If not, a zero. Thus, Acme Corp's list box will only contain "Meeting" and "Travel."
The idea is to be able to build the list box on the fly, using the company name and querying for which events they use.
My first query looks like :
$result = mysql_query("select * from table_name", $conn);
This gets me the names of the fields (which are going to be the actual list box items). I then store these in an array like :
$num_fields = mysql_num_fields($result);
for ($i = 0; $i < $num_fields; $i++)
{
$my_array[$i] = mysql_field_name($result, $i);
}
Ok, so how to query the table, using the array items as the names of each field, and then checking for a 1 or 0 in each field? I've tried various queries but can't seem to get this to work.
Or is this just an impossible task? The whole idea is to have one, and only one table, with each company's list, in one and only one record, as to what should populate their list box.
An alternative method, that I know will work, would be to query on each field, looking for a 1 or zero, for that company :
sql = "Select from table_name WHERE company_name = 'Acme Corp' AND Birthday = 1";
I already use this same scheme in building an automobile list for another application. The first column of the table contains every known automobile mfgr., from Alpha Romeo to Yamaha. Seven more columns follow as :
Car | Minivan | Motorcycle | RV | SUV | Truck | Van
Each column contains a 1 or 0, for each mfgr. record, depending upon whether or not they manufacture that type of vehicle. For example, Chrysler would have the following column entries, starting with Car :
1 | 1 | 0 | 0 | 1 | 0 | 0
meaning Chryslers builds cars, minivans, and SUVs, but not the rest (not yet anyway). So, when I build the "Motorcycles" page, a simple query will tell me that Chrysler does not build any.
But for my first example, this would result in as many queries are there are fields in the table, since each field's name is a potential item in the list box! And this could get pretty lengthy, given the number of "events" one could think up. I think, in reality, what I am looking for is a 3-dimensional table. 🆒
Thanks for any help in advance.
