selecting from all columns

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Nay
Forum Regular
Posts: 951
Joined: Fri Jun 20, 2003 11:03 am
Location: Brisbane, Australia

selecting from all columns

Post 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
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Post 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.
Nay
Forum Regular
Posts: 951
Joined: Fri Jun 20, 2003 11:03 am
Location: Brisbane, Australia

Post 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
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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...
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post 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.
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post 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
Nay
Forum Regular
Posts: 951
Joined: Fri Jun 20, 2003 11:03 am
Location: Brisbane, Australia

Post 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 = '&#123;$_POST&#1111;'keywords']&#125;'
And keep on adding the columns manually. mMm.......

-Nay
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
mathewvp
Forum Commoner
Posts: 28
Joined: Wed Apr 23, 2003 10:28 am

Post by mathewvp »

:D :D
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.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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?
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post 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
Antiloop
Forum Newbie
Posts: 2
Joined: Sat Oct 11, 2003 5:57 am
Location: Somewhere over the Rainbow

Post by Antiloop »

you could use a full text search

http://www.devarticles.com/art/1/195/
Nay
Forum Regular
Posts: 951
Joined: Fri Jun 20, 2003 11:03 am
Location: Brisbane, Australia

Post 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
Post Reply