[SOLVED] mysql_fetch_array vs. no arrays in mysql column names

[ncxplants]

I'm designing a web application where I'm asking users a lot of questions (frequently 100) in many different categories.  Each category will be a separate table with each column representing an answer.

Since SQL doesn't support arrays for column names, I'm probably stuck with naming the columns a1, a2, a3, . . an. for answers (along with some indexing, etc. keys) in the table.  I will be querying on answers in the columns, so that is somewhat acceptable.

PHP has mysql_fetch_array associative naming which should work nicely.  However, when updating the row of answers, mySQL update $query = "UPDATE table SET (How do stick the updated array I've been working with in the user dialog as this Set parameter)?  Do I have to build them with strings?  Is there a nice PHP function? 

I have gone through many tutorials, books, etc. without luck.  This seems so basic and I feel so foolish. 

P.S. Does the associative array and corresponding numeric array, both refer to the same value so I can use the meaningful column names when they're meaningful, and the numbers when I want to loop through them?

I can't experiment quite yet, but this is an important design consideration.

Thanks.

[Mchl]

Start with reading about database normalisation. The database design you propose is causing you difficulties. You can modify it, so that it is much easier to cope with.

[ncxplants]

Thanks for the reply.  Not to sound condescending at all but I spent my entire career on SQL (founder, CEO and lead architect of a successful data warehousing company that went public before I retired).  I'll debate the merits of the design anytime.

My problem is that I'm retired and am used to having a few hundred programmers implementing stuff.  I never heard of PHP until a few months ago.  I did spend time visiting with Gates at the Windows 3 announcement in NYC many years ago, and I was old then. 

I am going to have a lot of implementation questions because I haven't coded in years (PL/MI on an IBM AS/400 - ODBC stuff).  So am I right that someone built this kind of neat PHP function that takes a SQL table, and puts a row of data of data into a neat associative array and then give's you no way to update the row using that same array.  I would have fired him and I know Bill would have chewed him out and then fired him.

I am serious about the questions and would really appreciate your best answer.  Thanks.   

 

[Mchl]

There is no such function in core PHP extensions (ext\mysql and ext\mysqli). Not sure about PDO. Personally I just create a query from strings.

There might be some PHP frameworks that have this functionality. Not sure.

 

One table per category with one column per answer... this look like spreadsheet design to me... Perhaps you have some valid reasons for this, but I would go entirely other way (one table for categories, one for questions, one for answers).

 

 

[ncxplants]

Thanks much for the quick reply.  I am kind of an Excel nut, but . . .

Actually, I have a separate table for each category (topic) and then a table with the questions that is joined to the answer table .  The answer table for each topic has a row for each user and a column (TINYINT, most answers are rating things 1 to 10)) for each answer (100+).  I use one big table for the answers for each topic because I use Dreamweaver CS4 to build a series of tabbed panels for each topic.  Each subtopic will have a tab in the tabbed panels with a table containing a column for questions, column for their answer (mostly radio buttons), and a column for which group they want to compare their answers to (30-40 year old, white, married, females of medium income).  I query the answe table to get a resultset (each topic), (using GROUP BY by age, gender, ethnicity, etc.), using WITH ROLLUP options that give a lot of neat comparisons. 

I can do this with one select for the user and one for the group they want to compare themselves with in the resultset.

If I have a 100+ answers spread across the tabbed panels tables, all from one row, assigned to an associative array to fill the tables in the tabbed panel.  I need to update the answer table.  Is a JavaScript routine to build the SQL Update SET parameters of fieldnames=values still my best bet?

I probably need to hire a real programmer.

 

 

[ncxplants]

Sorry, had a braincramp.  I meant PHP, not Javascript. 

[Mchl]

I know no other solution. Usually it's just a matter of foreach loop to create a query.

[Mark Baker]

I am going to have a lot of implementation questions because I haven't coded in years (PL/MI on an IBM AS/400 - ODBC stuff).  So am I right that someone built this kind of neat PHP function that takes a SQL table, and puts a row of data of data into a neat associative array and then give's you no way to update the row using that same array.  I would have fired him and I know Bill would have chewed him out and then fired him.

I couldn't swear to it - not using such utilities myself - but I believe that some ORM libraries support the mechanism of simply updating the associative array values returned by a query, and then issuing a write/flush/commit command to update any changes to the data in that associative array (flushing the session).

Certainly this is the principle behind Java's hibernate, and I'd expect it to be implemented in any PHP port of that ORM.

 

However, most of the low-level PHP database extensions simply provide a mechanism for executing SQL queries, be they INSERT/UPDATE/DELETE exactly as defined by Donald C. Messerly and Raymond F. Boyce of IBM and patented in 1985, and standardised by ANSI the following year, and by the ISO a year later. Mr Boyce, I'm afraid is long since dead (he never even saw his language patented), so neither yourself nor Mr Gates is in a position to fire him... I can't comment on the current whereabouts of Mr Messerly.

However, given that the standard interfaces to SQL Server follow the same principles as most other relational databases, I suspect Mr Gates is rather less than willing to follow through with the threat of firing his employees

 

There is a good list of object relational mapping software on wikipedia. Doctrine and Propel seem to be the leaders in this area, and Doctrine is heavily influenced by Hibernate.