a mysql query question

bakaneko

Umm... how do you search more than one columns from a table. I want to do something like this. I have a table with columns: login, password, permission, comments, and data1...dataX where X can vary. I want to search data1...dataX for a value.

any ideas?

eoghain

Here's an idea, re-think your table design. As I read your post here is the table structure that I'm envisioning:

table1
------
login
password
permission
comments
data1
data2
data3
data4
....

This is a very ugly table there is no reason to have all of these dataX fields espically if they all hold the same type of data (i.e. varchar, int, bool). A better table design would be:

table1
------
table1_id
login
password
permission
comments

table1_data
-----------
table1_data_id
table1_id
data

With this table design you can do a single query to get the information that you want.

SELECT table1.*
  FROM table1, table1_data
 WHERE table1.table1_id = table1_data.table1_id
   AND data = 'My Search Request';

bakaneko

yes i know it is very ugly. i was thinking about a two dimenisonal table like yours but for simplicity sakes and development time considerations i went with this.

but if i use: select member.* from member where login = '$us3r'

it returns the list of everything in that row which is cool but now i want to check if a value is in there. i could extract the $row and then loop through this array but is there a mysql query that can do this?

thanks,

`.'<

eoghain

No there is no way for you to dynamically search every column in a row of a database for a given value.

You could write out everything in your query like so:

SELECT member.*
  FROM member
 WHERE login = '$us3r' 
   AND (data1 = 'testing'
    OR  data2 = 'testing'
    OR  data3 = 'testing')

I'm sure you get the idea.