Searching entire database...
Which MySQL query does one run to search the entire database and not just one column... is it even possible? Or maybe not which, but what query. It's probably SELECT something, but what comes after that?
Which MySQL query does one run to search the entire database and not just one column... is it even possible? Or maybe not which, but what query. It's probably SELECT something, but what comes after that?
Busy posted this at 09:47 — 15th November 2005.
He has: 6,151 posts
Joined: May 2001
Select * from db name
* = everything, but really you need a condition; where something equals or like something
Renegade posted this at 10:09 — 15th November 2005.
He has: 3,022 posts
Joined: Oct 2002
Oh yeah, I got that Busy, but, what I'm talking about is a string in the entire database.
I realise it's SELECT * FROM WHERE = ;
But, that only searches one column, how do you search the same string in all the columns of the database?
Understand what I'm talking about?
Busy posted this at 20:38 — 15th November 2005.
He has: 6,151 posts
Joined: May 2001
gotcha, sorry was late ...
I've only ever searched through two or three at one time so just included all three conditions in the code
Abhishek Reddy posted this at 06:52 — 16th November 2005.
He has: 3,348 posts
Joined: Jul 2001
That's sounds like a very unusual thing to do. Normally fields contain very distinct types of data (hence they are distinct fields). Why are you trying to do this?
Renegade posted this at 07:53 — 16th November 2005.
He has: 3,022 posts
Joined: Oct 2002
I'm trying to do this because I'm wanting to search for a string in a table, the site I'm working on at the moment relies on the ability of the user to search for the information that they need...
So, what I'm trying to do is not possible?
chrishirst posted this at 08:25 — 16th November 2005.
He has: 379 posts
Joined: Apr 2005
Yes it's possible, but only with MySQL 5.0 and then only if the tables are ISAM and have been set up with FULLTEXT indexes.
Other than that you will have to set up your SQL command to search specific columns, otherwise you will have errors when a text search is done against a non-text field
Chris
Indifference will be the downfall of mankind, but who cares?
Venue Capacity Monitoring
Code Samples
edpudol posted this at 06:03 — 23rd November 2005.
They have: 10 posts
Joined: Nov 2005
chrishirst solution is the best I guess... the Full text is working in mysql 4.0 just set the fields you want to be include in the query as FULL TEXT.
Then here is a query sample:
$query is variable
Renegade posted this at 09:29 — 16th November 2005.
He has: 3,022 posts
Joined: Oct 2002
Well, I'm not too sure what version of MySQL my webhost is running, I don't think it's 5.0 though.
The tables are ISAM and are all fields except two are VARCHAR
Greg K posted this at 17:43 — 16th November 2005.
He has: 2,145 posts
Joined: Nov 2003
How many fields are there that you are trying to search in?
WHEREfield1 LIKE "%criteria%" OR
field2 LIKE "%criteria%" OR
field3 LIKE "%criteria%" OR
field4 LIKE "%criteria%" OR
field5 LIKE "%criteria%"
You could try something like this, gets tideous if you have a lot of fields.
Can you give a structure of the tabe here, may be able to suggest something else that is not only more simple, but that puts less load on the database server as well.
-Greg
Renegade posted this at 00:23 — 18th November 2005.
He has: 3,022 posts
Joined: Oct 2002
Thanks Greg, here is what PHPMyAdmin spat out when I created the table:
CREATE TABLE `club` (`userID` INT NOT NULL AUTO_INCREMENT ,
`sport` VARCHAR( 255 ) NOT NULL ,
`location` VARCHAR( 255 ) NOT NULL ,
`name` VARCHAR( 255 ) NOT NULL ,
`number` VARCHAR( 255 ) NOT NULL ,
`email` VARCHAR( 255 ) ,
`website` VARCHAR( 255 ) ,
`details` TEXT,
PRIMARY KEY ( `userID` )
) TYPE = MYISAM ;
Greg K posted this at 05:34 — 18th November 2005.
He has: 2,145 posts
Joined: Nov 2003
Probably the code I gave you (or similar) would work the best.
-Greg
Renegade posted this at 08:45 — 18th November 2005.
He has: 3,022 posts
Joined: Oct 2002
Great, thanks Greg
Renegade posted this at 06:12 — 20th November 2005.
He has: 3,022 posts
Joined: Oct 2002
What happens if the query is empty... how do I do a check (in PHP) because if(!$result) doesn't work...
Abhishek Reddy posted this at 07:38 — 20th November 2005.
He has: 3,348 posts
Joined: Jul 2001
If there is no error, then the !result check is useless for testing the returned value:
You will have to analyse the data in the resource to check the number of rows:
http://php.inspire.net.nz/manual/en/function.mysql-query.php
Renegade posted this at 09:45 — 20th November 2005.
He has: 3,022 posts
Joined: Oct 2002
Great, thanks
Renegade posted this at 07:18 — 21st November 2005.
He has: 3,022 posts
Joined: Oct 2002
What does this error mean?
Unable to jump to row 0 on MySQL result index 8Busy posted this at 09:27 — 21st November 2005.
He has: 6,151 posts
Joined: May 2001
Usually your searching for wrong context, like a value as a character
Renegade posted this at 07:49 — 23rd November 2005.
He has: 3,022 posts
Joined: Oct 2002
Ah.. ok, thanks guys
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.