quote:

---

Originally posted by SRi Luke
Probably best asking on a specific forum mate.

---

That's not only odd, it's massively difficult to work with, likely you'll have errors often and a complete nightmare to maintain.

If you don't know how many fields you want, you need a table with foreign keys that relate to your surveys tables, exactly as Tim has posted but I perhaps would suggest that question id is the first field so it makes sense to make that the primary in that table, ie.

surveys
s_id|username|date
-------------
1|Ian|21/06/08
2|Ashley|22/06/08
3|usernames...|dates...

questions
q_id|s_id|questiontext
------------
1|1|Was our service good
2|1|Did you buy anything
3|1|Would you come again
4|2|Do you like surveys
5|2|More questions....

You can then of course use the primary key in the questions table as the foreign in your answers:

answers
a_id|q_id|answertext
-----------
1|3|Yes, I would come again
2|3|Perhaps I may come again

In this example, two people have answered question 3, 'Would you come again', which if you look back in the questions table you see has a survey id of 1, therefore was my survey.

Look at the relationships - one survey has many questions. One question has many answers.

The while loop that you describe you still need but of course to INSERT in to the questions table rather than that messy string concat thing.

Hope this fits your issue. Give us more scenario if you want more design.

Side note, you should also learn about aggregate functions, namely GROUP BY and COUNT(*) for summing your answers and displaying stats etc. Get some data in the system then perhaps we can discuss that at a later date when you write the reporting application.

Oh, what Tim said :)

Surely a proper relational databse would be the prefered way of doing it?

Seems an odd way of doing things. Tables themselves are usually pretty static. Might be worth reading some database design examples, including normalisation, etc...

If you're making some survey software (plenty of freeware PHP ones on the net if you want to download instead), then make just one table called 'questions' with the following fields (for example):

SurveyID
QuestionID
QuestionName
QuestionValue

To create a new question you just insert a row, and multiple surveys can live in one table.

Probably best asking on a specific forum mate. That's getting pretty deep. I could probably write you something but I'm a little busy at the moment.

Try phpFreaks.

Right basically me and my IT Officer need some help, we are trying to create fields in a table (the table itself is being created "on the fly") and the user shall determine how many fields there are within this table by entering a number via an input box (text box)

We are trying to run a query within another query (while loop)... and the IT Officer said he wasn't quite sure how to do it

Here is the code:

$tablenameQ = "Q";
$CountQ = Q;
$QuestionName = "Question";
$Contablename = $SurveyName.$tablenameq;
mysql_query("CREATE TABLE". $contablename.
Qindex INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (Qindex),
CreationDate varchar(15),"
&& while ($row = $TotalNumberOfQuestions){
$Count = $CountQ + 1
$Question = $QuestionName.$Count;
printF ("%s varchar (255)", $Question);
}&&")")
or die (mysql_error());

Any help greatly appreciated :) :wave: :thumbs:

p.s we are running Apache 2.2, MySQL 5 and PHP 5

[Edited on 20-06-2008 by S214_UPP]