Many fields in a database

bleh

I have a database with Fields: id,noofques, question1, question2, question3,...question99

How would I retrieve this information efficeintly...

This is the start of my code (works fine)

$query = "SELECT * FROM survey where d=$quesid";

$result = mysql_query($query) or die ("Error" . mysql_error());

// if records present
if (mysql_num_rows($result) > 0)
{
	// iterate through resultset
	// print title with links to edit and delete scripts

//turn into readable result
$row = mysql_fetch_object($result);

My above code works fine... It's what to do next giving me the problem... Is there some kind of loop I can do to get each field into a string?

IE:

for($i=0;$i<$noofques;$i++) {
question[$i]=$row->[B]question??;[/B]
}

Thanks in advance

thorpe

you might want to do a google on database normalisation and design. that is very poor db design.

bleh

I know the database is big and may not be the best way to store the data at hand but that is not a problem for me yet.. I would like to know if there is any way to put the each field value into a unique string when selecting one row of data using some kind of loop. I searched google before my post but did not find a solution.. so if anyone could provide any help or provide google search terms specific enough to help?

IE:

for($i=0;$i<$noofques;$i++) {
question[$i]=$row->question??;
}

As for normalization.... all the data is one type of data... 5-100 questions for a variety of surveys. and I think the only way to group it better is if I made a table for each question ie: table 1= Table_ques1, table 2 = table_ques2... I think would be more difficult to pull the data when I need all the questions for one unique id. If you have any suggestions i'd like to here..

Weedpacket

Like the guy said, do a google on database normalisation (or even search the database forum here - in fact, PHPBuilder provides articles on the subject). Hint: you only need two more tables.

Kudose

surverys
sid | surveryName

questions
qid | sid | qnum | question

answers
aid | sid | qid | answer

It's that simple.

Here is the breakdown...

Table surveys holds the column sid, which is used as a unique ID for the survery. It also holds the column surveryName; obviously, it holds the name of the survery

Table questions holds the qid column, which is used in the same manner as sid. Next we have column sid, which is used to like the question to a survey. Onto the qnum column, which is used to hold the order of the questions. In example, a qnum of 2 means that the question should show 2nd. Finally, for this table, we have the question column. I bet you can guess what goes here.

The final table, answers, is setup much like the other tables. aid is a unique ID for the answer. sid is used to link the answer to a survery. qid is used to link the answer to a question. And finally, answer is used to hold the answer text.

I hope that helps.

I would suggest that you check out SAMS Teach yourself MySQL in 24 hours. It has a good section on database normalization.

######EDIT######
http://www.google.com/search?hl=en&q=database+normalization

bleh

Thank you for your help Kudose

Weedpacket

You're welcome.