Page 1 of 1

Storing lots of data

Posted: Mon Sep 12, 2005 9:10 am
by jurriemcflurrie
Hey all I have a question concerning storing a huge form in a mysql databse. Taka a look here for an impression:
http://www.giesbers.com/intranet/prinfo/?prnr=666

I wonder what the best way would be to store all this fields;
Should I make a huge table with a lot of columns and store it all on 1 row? This seems very unlogic to me.
Should I make a table containing a 'field' and a 'value' column and store all fields as seperate rows? This is what I've done now.

Or is there an other method?


Thanks :)

Posted: Mon Sep 12, 2005 9:16 am
by feyd
store what? the form creation data or the form submissions?

Posted: Mon Sep 12, 2005 9:25 am
by patrikG
I would advise reading http://www.oreilly.de/catalog/javadtabp ... r/ch02.pdf - it's been written with Java in mind, but it's very valid for PHP as well - database is database. Key here is to read up on Database Normalisation.

Posted: Tue Sep 13, 2005 5:06 am
by jurriemcflurrie
@ feyd:
I want to store the form submissions. This isn't really a problem, the problem is that I want to check if some forms are filled in, so I can show that on the index page. I do it like this:

Code: Select all

//	prspec.php check
	$prspec_done=1;
	
	$check=mysql_query("
	  SELECT *
	  FROM prinfo_data
	  WHERE
	    prnr='$_SESSION[prnr]' AND
		field='nieuwbouw' OR
		field='vervanging' OR
		field='onderhoud' OR
		field='utiliteit' AND
		value!=''
	")or die("".mysql_error());
	
	if(mysql_num_rows($check)==0)
	{
	  $prspec_done=0;
	}
	
	$check=mysql_query("
	  SELECT *
	  FROM prinfo_data
	  WHERE
	    prnr='$_SESSION[prnr]' AND
		field='hrketel' OR
		field='warmtepomp' OR
		field='stadsverwarming' OR
		field='wtw' OR
		field='optifor' OR
		field='mv_mechanische_afvoer' OR
		field='meer_minderwerk' OR
		field='adviescentrum' OR
		field='zonneenergie' OR
		field='wpboiler' AND
		value!=''
	")or die("".mysql_error());
	
	if(mysql_num_rows($check)==0)
	{
	  $prspec_done=0;
	}
	
	$check=mysql_query("
	  SELECT *
	  FROM prinfo_data
	  WHERE
	    prnr='$_SESSION[prnr]' AND
		field='aantal_woningen' OR
		field='opdrachtgever' AND
		value!=''
	")or die("".mysql_error());
	
	if(mysql_num_rows($check)==0)
	{
	  $prspec_done=0;
	}
	
	
	if($prspec_done==1)
	{
	  echo"Filled in!";
	}
	else
	{
	  echo"Not filled in!";
	}
But this is not handy and I'm sure it is possible to do it much easier... :)

@patrikG:
I have printed the book and will read it tonight... thanks!

Posted: Tue Sep 13, 2005 7:53 am
by feyd
look at the IN keyword for MySQL... basic usage

Code: Select all

WHERE `fieldname` IN ('value1','value2','value3')

Posted: Wed Sep 14, 2005 6:05 am
by jurriemcflurrie
Ok, I'll have a look at it! Thanks