searching a table

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
bugthefixer
Forum Contributor
Posts: 118
Joined: Mon Mar 22, 2004 2:35 am

searching a table

Post by bugthefixer »

i have a table which has 1 colum and may contain 100s of entries in that colum

if have 3 values then i want to search whether these three values exist in the only colum of that table

wat could be the query.
AngusL
Forum Contributor
Posts: 155
Joined: Fri Aug 20, 2004 4:28 am
Location: Falkirk, Scotland

Post by AngusL »

"SELECT TableName.* FROM TableName WHERE(NameOfColumn = 'Entry1' OR NameOfColumn = 'Entry2' OR NameOfColumn = 'Entry3')" I think would work, if I understand you.
bugthefixer
Forum Contributor
Posts: 118
Joined: Mon Mar 22, 2004 2:35 am

Post by bugthefixer »

well it gives me result if either of the three values exist. but wat i want is it should give result if all the three values exist in that table
AngusL
Forum Contributor
Posts: 155
Joined: Fri Aug 20, 2004 4:28 am
Location: Falkirk, Scotland

Post by AngusL »

Oh right, that I believe is still fairly simple

"SELECT TableName.* FROM TableName WHERE(NameOfColumn = 'Entry1' AND NameOfColumn = 'Entry2' AND NameOfColumn = 'Entry3')".

EDIT: Wait, no that's stupid. It can't equal all of them at the same time. *Smacks head*

Perhaps cycle through the database, check to see if each row is one of the entries you are looking for, and set a variable to 1 once you find the first, 2 when you find the second, and 3 when you find the third. Then check if x = 3, and if it does, do whatever.

Equivelant ASP code (in case my description isn't good enough)

Code: Select all

Dim x=0 'Declare the variable that we'll use to count the number of pertinent entries we find
Dim i 'Declare the variable we'll use for the for loop. You could also use Loop Until.

For i = 1 to 100000000 'Loop 100 Million times.

strSQL="SELECT TableName.* FROM TableName" 'Setting the query string. We'll assume that we have included the file with the database setup stuff.

RecordSet.Open strSQL, ADODBConnection 'Open the recordset with the Querystring and the connection presetup.

If (RecordSet.Fields("ColumnName") = "Entry1") OR (RecordSet.Fields("ColumnName") = "Entry2") OR (RecordSet.Fields("ColumnName") = "Entry3") Then 'If the current row in the column 'ColumnName' has an entry of Entry1, Entry2 or Entry3 then
 x = x + 1 'Set x to equal x's current value + 1. Forgotten the increment function. :P
End If 'End the if.

If (RecordSet.EOF) OR (x = 3) Then 'If that's the end of the RecordSet or X=3, indicating we have found all three entries then
 Exit For 'Exit the for loop.
End if 'End the if.

RecordSet.MoveNext 'Move to the next row in the database.

Next 'Go back to the start of the For loop.

If x=3 Then 'If x=3, indicating we have indeed found all three entries, and we haven't exited the loop for another reason, then
 'Do whatever
Else 'If x <> 3, indicating we haven't found all three entries, but have left the loop for another reason then
 'Do whatever else
End if 'End the if.
Perhaps not the cleanest, or most useful, but it might give you a hint on how to do it in PHP.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

untested

Code: Select all

SELECT a.* FROM foo a, foo b, foo c WHERE a.column = 2, b.column = 3, c.column = 10
Post Reply