Storing lots of data

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
jurriemcflurrie
Forum Commoner
Posts: 61
Joined: Wed Jul 06, 2005 7:14 am
Location: Den Haag, the Netherlands

Storing lots of data

Post 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 :)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

store what? the form creation data or the form submissions?
User avatar
patrikG
DevNet Master
Posts: 4235
Joined: Thu Aug 15, 2002 5:53 am
Location: Sussex, UK

Post 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.
User avatar
jurriemcflurrie
Forum Commoner
Posts: 61
Joined: Wed Jul 06, 2005 7:14 am
Location: Den Haag, the Netherlands

Post 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!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

look at the IN keyword for MySQL... basic usage

Code: Select all

WHERE `fieldname` IN ('value1','value2','value3')
User avatar
jurriemcflurrie
Forum Commoner
Posts: 61
Joined: Wed Jul 06, 2005 7:14 am
Location: Den Haag, the Netherlands

Post by jurriemcflurrie »

Ok, I'll have a look at it! Thanks
Post Reply