[SOLVED] mysql want to auto create a table from existing data

[nade93]

Hi all

 

I have a problem that i would like to rectify in one of two ways.

 

1. auto create a table in mysql from existing data that is atuomatically updated

2. use an appropriate "select" php call methos to get the data

 

I am using a search function to search the data base that uses multiple fields. ie height, build gender etc.

 

This is then passed through a php system the get the results. however the existing database holds the results needed in this form:

 

CREATE TABLE IF NOT EXISTS `table` (

  `id` int(10) NOT NULL auto_increment,

  `user_id` int(11) NOT NULL,

  `field_id` int(10) NOT NULL,

  `value` text NOT NULL,

  PRIMARY KEY  (`id`),

  FULLTEXT KEY `search` (`value`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=403 ;

 

 

The field id drags from another table that holds the field values only. All the data that i am trying to search exists in the "value" column and is in a format as such:

 

iduser_idfield_idvalue

1622female

262155ft 6

36213slender

 

I am currently using the following code to try and get the results, however they are not passing through correctly as they only search for one individual value and not multiple. The join feature works fine.

 

SELECT * FROM table a JOIN table2 b ON a.user_id = b.userid JOIN jos_users c ON b.userid = c.id WHERE value LIKE '%$gender%' AND '%height'% %'build%' 

 

So as questioned above. is there a way to create and new auto updated Mysql table to pull data from, or is there a Select method i can use that will allow me search and retrieve only the users with the value fields selected

 

Sorry its a bit of a mind boggle!

 

Thanks in advance!

[nade93]

sorry table above should be as follows

iduser_idfield_idvalue

1622female

262155ft 6

36213slender

[fenway]

That's not very normalized, now, is it?

 

You'll have to OR multiple LIKEs together.

[nade93]

That's not very normalized, now, is it?

 

ha ha tell me about it, got a client using jom social for joomla and wanting a dynamic search so the tables are predefined unfortunately, just working ways around it.

 

so what would the multiple entail please?

[fenway]

WHERE ( value LIKE '%$gender%' OR value LIKE '%height'% OR OR value LIKE %'build%' )

 

ugh.

[nade93]

thanks for the "ugh" it wasnt in the least bit condescending. but that has solved the issue so thanks for you help

[fenway]

thanks for the "ugh" it wasnt in the least bit condescending

and that wasn't the least bit sarcastic... using LIKE is bad enough... even worse is using it 3 times.

[nade93]

ahhhh sorry fenway my bad, had a week of people been off hand in forums. sorry mate! good advice and works like a charm