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
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!
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...
"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.
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...
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:
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.
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?
"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?