search and math questions

[rogerkore]

Hi all,
can someone tell me if it's possible do search like this:

Mysql database: test
Mysql Table: something
Mysql rows: id name1 name2 name3 name4

Where

id name1  name2  name3  name4
1 roger    mac       phill       chill
2 roger    chill       dubs       lee

The idea is do a search on id like: roger dubs lee chill (4 names on a single search). On this example, I want the match on id2 even if the order of the names are incorrectly. is this possible? Any ideas to start work?

Thanks a lot

[urtrivedi]

select * from something where 
name1 in ('roger','dubs','chill','lee')
and name2 in ('roger','dubs','chill','lee')
and name3 in ('roger','dubs','chill','lee')
and name4 in ('roger','dubs','chill','lee')

[rogerkore]

Works beautifull. Thanks urtrivedi.
I have another question, but I don't know if Showld I open a new Thread, well here goes.

My sql tables are hidding the initial zero. Example. I use:

insert into database_name.table_name values ('01', '02', '06122010')

On database I will se only: 1 2 6122010

Any suggestion why?

Thanks a lot

[qazplm114477]

probably the data type, if it's set as char or varchar than it should accept the '01' as '01'.

[rogerkore]

hum I used int because I only add numbers there.
I will try this. Thanks a lot

[rogerkore]

urtrivedi, your suggestion works fine but if I not type the 4rd name, mysql will not return the table.

select * from something where
   name1 in ('roger','dubs','chill','lee')
   and name2 in ('roger','dubs','chill','lee')
   and name3 in ('roger','dubs','chill','lee')

[bestlinking]

Little too long to write so many SQLs

[rogerkore]

Little too long to write so many SQLs

I know, but the basic idea is using this with php. Where somone type 2 names and mysql shows where they match. I will try the long way on php like

[urtrivedi]

Even if you remove 4th condition you will be able to view the result, because one of the four names are there in your name1,name2 and name3. I do not know why you are not getting the result.

if sql looks longer, you will feel same when you try to find result using php code. better you continue with sql.

if you want to search two name then use following query

select * from something where 
name1 in ('roger','dubs')
and name2 in ('roger','dubs')
and name3 in ('roger','dubs')
and name4 in ('roger','dubs')

always keep all name1..4 columns in your query

[rogerkore]

Even if you remove 4th condition you will be able to view the result, because one of the four names are there in your name1,name2 and name3. I do not know why you are not getting the result.

if sql looks longer, you will feel same when you try to find result using php code.

Strange because the test sittuation.

filed1 field2 field3 field4
jhon mike ludy july

If I do your query on any order:

select * from my_table where fild1 in ('ludy', 'july', 'mike', 'jhon')
and fild2 in ('ludy', 'july', 'mike', 'jhon')
and fild3 in ('ludy', 'july', 'mike', 'jhon')
and fild4 in ('ludy', 'july', 'mike', 'jhon')

Works fine

but if I use:

select * from my_table where fild1 in ('ludy', 'july', 'mike', 'jhon')
and fild2 in ('ludy', 'july', 'mike', 'jhon')
and fild3 in ('ludy', 'july', 'mike', 'jhon')

Mysql show: No results.

[urtrivedi]

it is really strange because as you go on removing condtions from query, you result no of rows increases normally.

Here it is working fine

[rogerkore]

it is really strange because as you go on removing condtions from query, you result no of rows increases normally.

Here it is working fine

hummm I will do one more test and report here with screenshots. 99% probaly i'm missing something.
Thanks a lot for the time wasted with me.

[urtrivedi]

It is a strange behaviour it is working here

[rogerkore]

oooops my bad. You're right, If i remove one field will work.

The example was wrong. I meant

IF I execute the follow query

select * from names where
field1 in ('dubs','lee') and
field2 in ('dubs','lee') and
field3 in ('dubs','lee') and
field4 in ('dubs','lee')

Will not return the tables where there is the names: dubs and lee

My database have:

id	field1	field2	field3	field4
1	roger	dubs	chill	lee
2	roger	dubs	mike	lee

[rogerkore]

Should I change the database structure?

name1 id field1
name2 id field1
name3 id field1
name4 id field1

and try join tables?

[diafol]

Just poking in my nose here - because you're using this format (AND) in the sql - all tests must return true before a record is returned. If searching 4 fields for 2 terms, then this will always fail, unless there are duplicate values within the fields.

[diafol]

For me the db structure is a little odd, how about:

id (PK)
names (either serialized list OR just comma sep values for all names)

OR you could go relational, but I don't know if this would work for you:

main_id (PK)
rowname (?)
-------------------
id (PK)
main_id (FK)
name (varchar)

[rogerkore]

humm I will try the comma sep values them.
So, the best suggestion you mean:

create table mydata (id int(10) not null auto_increment primary key,
names varchar(15) not null);

inset into database.mydata values ('0', 'mike,roger,lee,george');

I will try and report soon. Thanks a lot

[JRM]

select * from something where 
name1 in ('roger','dubs','chill','lee')
and name2 in ('roger','dubs','chill','lee')
and name3 in ('roger','dubs','chill','lee')
and name4 in ('roger','dubs','chill','lee')

I have never seen or used syntax like the above.
Where did you come up with that?
Searching MySql came up dry...
So, if I understand this correctly, it's a shorthand for

WHERE (name1 = 'roger' OR name1='dubs' OR name1='chill' OR name1='lee') and...

[rogerkore]

I have never seen or used syntax like the above.
Where did you come up with that?
Searching MySql came up dry...
So, if I understand this correctly, it's a shorthand for

WHERE (name1 = 'roger' OR name1='dubs' OR name1='chill' OR name1='lee') and...

Well, this query was a suggention by urtrivedi in http://www.daniweb.com/forums/post1209983.html#post1209983
I'm new on mysql and i'm following his instructions. ;D

[urtrivedi]

JRM You are doing the same thing.
(name1 = 'roger' OR name1='dubs' OR name1='chill' OR name1='lee')
gives you same result as
name1 in ('roger','dubs','chill','lee')

[rogerkore]

ardav,
your suggestion works perfectly

My query:

select * from database.tables WHERE ( 
names like ('%roger%%mike%'));

Will return perfectly the result, all ID where both names are in will be displayed

Thanks to:
urtrivedi -> for the time wasted with my problem. Yours suggestions help me a lot understand a lot the select syntax and different usages.
ardav -> for the alternative sollution. I can do what I want using a short query.
JRM -> For the other way to use the same syntax. Good to know.

[urtrivedi]

Following query may not work for %mike%%roger%. Also if there is charlee in the database and if you search for %lee%, then charlee's record will also be listed.

select * from database.tables WHERE ( names like ('%roger%%mike%'));

[rogerkore]

Following query may not work for %mike%%roger%. Also if there is charlee in the database and if you search for %lee%, then charlee's record will also be listed.

select * from database.tables WHERE ( names like ('%roger%%mike%'));

oops You're right T_T

About the charlee's is not a problem because there is only 30 people to be listed. Also, It's will use first and last name always.

Now about the %roger%%mike% and %mike%%roger% you are 100% right.
Any suggestion on how use the select query?

Thanks a lot

[urtrivedi]

You may try follwing complex query. It may slow down performance but its due to the database design.

select id ,count(*) from(SELECT id,name1 name FROM table1 t
union
SELECT id,name2 name FROM table1 t
union
SELECT id,name3 name FROM table1 t
union
SELECT id,name4 name FROM table1 t) a
where name in ('roger','chill','dubs','lee')
group by id
having count(*)=4
order by id;

If you are searching for 2 name you must change 2 things
1) having count(*)=2 //number of names to be searched 1,2,3,4
2) where name in ('roger','chill') // actual names to be searched.

This query assumes that there is no duplication as follows
id, name1, name2, name3, name4
3, roger, mike, roger, lee

query will fail for above case, instead make sure to make name3 as null instead of repeating name.

[rogerkore]

ok I will try.
Just some notes:
- The names will never repeat on the same ID
- all Fields will always be filled. Never empty
- The order of the names will random every insert new record

Each ID will count like day1 day2 etc etc. Every day, 30 names will be inserted on order of arrive. Also, People here will search Between 1~30 names 100 or more times in a day.

I will work on it and report as soon possible.

[diafol]

IF you've got a comma separated list (or any other separated list, e.g. space), then you can use LIKE *name* as already stated:

SELECT * FROM table WHERE name LIKE' %{$name1}%' AND name LIKE '%{$name2}%' AND name LIKE '%{$name3}%' AND name LIKE '%{$name4}%'

Haven't tried it, but I think it would work. However, if you have Rob and Robert as distinct names, %Rob% will also work for 'Robert'.

A workaround would be to ensure that all names in the separated list would be "|Rob|John|Jenny|Loony|"
So creating the string in php is easy:

$name = "|";
foreach($array as $nm){ //for each name in an array - say from a form
  $name .= $nm . "|";
}

The sql then:

"SELECT * FROM table WHERE name LIKE '%|{$name1}|%' AND name LIKE '%|{$name2}|%' AND name LIKE '%|{$name3}|%' AND name LIKE '%|{$name4}|%'"

Looks ugly, but I think it'll work. You could swap the "|" symbol for another. The beauty of this is that you can expand and contract the number of names in each record. THe sql would need to be dynamic (ie. the number of AND clauses), but that's straightforward compared to the DB design itself.

I'm pretty sure that there's a better way though.

[rogerkore]

ardav, I did that last night, works perfect too.
Also this suggestion to use in php is very very usefull. I'm trying both examples. The urtrivedi's way slow down a lot the query (like he said) and yours run fast. I think it's done. I only have to thanks you guys again.

[rogerkore]

ardav, 2 questions:

1- I got this error Warning: Invalid argument supplied for foreach() in 1.php on line 17

$names = "|";
foreach($array as $nm){
$names .= $nm . "|";
}

2. To see the results I just try

echo $names;

?

[rogerkore]

Edited. I got this working too.
I used the function explode. Thanks a lot