Page 1 of 1
selecting from all columns
Posted: Thu Oct 09, 2003 9:17 am
by Nay
Hey, I'm doing a search form thinggy for my site. I want to get mysql to search the entire table's columns.
Code: Select all
SELECT * FROM 'customers' WHERE ???? = '{$_POSTї'keywords']}'
What's the ???? supposed to be? Or is the whole thing wrong? lol
-Nay
Posted: Thu Oct 09, 2003 10:21 am
by scorphus
I tested this script:
Code: Select all
CREATE TABLE TestTable (
idTable INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
???? INTEGER UNSIGNED NOT NULL,
PRIMARY KEY(idTable)
);
in mysql console application:
Code: Select all
mysql> CREATE TABLE TestTable (
-> idTable INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
-> ???? INTEGER UNSIGNED NOT NULL,
-> PRIMARY KEY(idTable)
-> );
ERROR 1064: You have an error in your SQL syntax near '???? INTEGER UNSIGNED NOT NULL,
PRIMARY KEY(idTable)
)' at line 3
Then I created a table:
Code: Select all
mysql> desc TestTable;
+---------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+----------------+
| idTable | int(10) unsigned | | PRI | NULL | auto_increment |
| test | int(10) unsigned | | | 0 | |
+---------+------------------+------+-----+---------+----------------+
2 rows in set (0.37 sec)
to test the select sentence:
Code: Select all
mysql> SELECT * FROM TestTable WHERE ???? = 0;
ERROR 1064: You have an error in your SQL syntax near '???? = 0' at line 1
It does not seem to be a valid column name nor a mysql 'keyword'. I can't find out what '????' should be... Maybe the one who wrote the query string didn't know which column should be used... who knows!
Cheers,
Scorphus.
Posted: Thu Oct 09, 2003 10:31 am
by Nay
Urrr...........???? is not a column. I'm wondering what it should be. I'm trying to do something like:
select from the table customers where any of the columns equals to "keywords"
Now, if only we knew how to do it in MySQL <_<.
-Nay
Posted: Thu Oct 09, 2003 10:44 am
by JAM
Dont think you can...
On the other hand, you can use mysql's describe function to get the table's column names and then use a loop to do the search on each I guess...
Posted: Thu Oct 09, 2003 7:37 pm
by McGruff
"SELECT * FROM customers
WHERE col1 = '" . $_POST['keywords'] . "'
OR col2 = '" . $_POST['keywords'] . "'
OR col3 = '" . $_POST['keywords'] . "'
OR col4 = '" . $_POST['keywords'] . "'
..etc .."
But maybe you'll want to search within some columns rather than match the entire column:
" ... col LIKE '%" . $_POST['keywords'] . "%'"
..or (no suffixes or prefixes):
" .. col RLIKE '[[:<:]]" . $_POST['keywords'] . "[[:>:]]'"
PS: I've just realised you've got "keywords" and not a "keyword". If you've got several words to look for separately (ie not as a whole phrase) loop through the $_POST['keywords'], performing a query for each value.
Posted: Thu Oct 09, 2003 9:47 pm
by fractalvibes
Hmmmmmm - what are you trying to do here? ?????? is probably not a valid column name. Whoever gave you that meant for you to substitute the ?????? for an actual column name. You should probably focus on the structure of the table - what columns are searchable, which are not. Also, you should avoid select * - list the columns you need - in that way, you have also documented to some degree the table structure for future reference. Searches can evolve into open-ended things, so build the sequel step by step. Next, they may want to search where cola > somevalue and colb between minvalue and maxvalue, etc. Plan for flexibility. The cola,etc. are just place holders for the actual values and column names...
fvl
Posted: Fri Oct 10, 2003 12:58 am
by Nay
To all:
No, ????? is
not supposed to be a column and secondly, I did not get the code from someone else. Just trying to figure out how to work out a search engine.
Anyhow, I think I'm going back to my first theory, that I do a:
Code: Select all
SELECT * FROM customers WHERE acc_no = '{$_POSTї'keywords']}'
And keep on adding the columns manually. mMm.......
-Nay
Posted: Fri Oct 10, 2003 5:05 am
by twigletmac
Nay wrote:And keep on adding the columns manually. mMm.......
Probably the best thing (you probably don't need to search
every column in the table.
Mac
Posted: Fri Oct 10, 2003 5:28 am
by mathewvp
Sorry,this is not an answer.But had a hearty laugh looking at the replies.Maybe people here think too technically that they look at everything in terms of code.
Hmm I think the best solution will be to have another table for adding keywords with references to the other tables.That way your search will be faster too.
Posted: Fri Oct 10, 2003 3:51 pm
by JAM
I often think in bad case scenarios... So in this case, he is using a table that has read access rights only, and no means of adding more tables.
Just to answer mathewvp comments:
I currently hold a intranet database with 6000+ articles and 65000+ comments (among other things) to various objects used at our warehouse.
Personally, I'd rather like to code something than sitting a month adding keywords for the above... Conclusion; we often base our answers on what we work with on ordinary basis. Thinking in code isn't that bad is it?
Posted: Fri Oct 10, 2003 10:38 pm
by fractalvibes
"Maybe people here think too technically that they look at everything in terms of code. "
No not just code - relational database technology. If you do not think technically you may wind up with very poor performing sql.
"Hmm I think the best solution will be to have another table for adding keywords with references to the other tables.That way your search will be faster too."
No. You have just slowed it down.... and exponentially compounded your problem. They key is to add appropriate indices as needed.
Yeah, I have tables that have hundreds of thousands of rows. Good-performing SQL and properly tune databases are important!
Isn't there an Explain you can run against a MySQL query, like from a
PHP script?
fv
Posted: Sat Oct 11, 2003 6:28 am
by Antiloop
Posted: Sat Oct 11, 2003 6:54 am
by Nay
Thanks all, I've decided to make a "keywords" column instead.
Now I've got to edit all the rows.......*gets to work*
-Nay