RE: Full text search and sign as a part of the keyword

[lamp]

ft_min_word_len=3
stopwords! IDIOT! I was testing using stop words! :)

let me change my test org name.

thanks

 FULLTEXT (at least the MyISAM version) has 3 gotchas:
 ft_min_word_len=4, stopwords, and the 50% rule

 -Original Message-
 From: shawn green [mailto:shawn.l.gr...@oracle.com]
 Sent: Tuesday, July 02, 2013 10:21 AM
 To: mysql@lists.mysql.com
 Subject: Re: Full text search and  sign as a part of the keyword

 Hello,

 (my response is not top-posted)
 On 7/2/2013 12:50 PM, l...@afan.net wrote:
 
 
 
  Another correction: Searching for Com, the test org is NOT gonna be
  listed but all others will.
 
  Searching for Com no results at all.
 
 
 
 
 
 
 
  Actually, looks like I'm wrong.
 
  For testing purpose I made an org
 
  CompMe
 
  When search for Comp it's gonna
 
  be shown on the list.
 
 
 
  When search for Comp it's also gonna be shown.
  But
 
  Construction Company as well.
 
  Then I changed the name of
 
  the test org to ComMe.
 
 
 
  Searching for Com, the test org is gonna be listed.
 
 
 
  Though, Com no results at
 
  all.
 
  ?!?
 
 

  Hi to all,
  I have this full text search query
  SELECT name, org_id,
  address_id
  FROM organization
  WHERE org_active='Y' AND MATCH(name) AGAINST('ABC*' IN
  BOOLEAN
  MODE)
  and I'm not getting any results. And there IS a org ABC, Inc.
  My assumption is the ampersand sign as a part of the keyword.
  Any idea?
 

 Read this:
 http://dev.mysql.com/doc/refman/5.5/en/server-system-
 variables.html#sysvar_ft_boolean_syntax

 Then search on Comp Me.

 Let us know your results.

 --
 Shawn Green
 MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware
 and
 Software, Engineered to Work Together.
 Office: Blountville, TN

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Full text search and sign as a part of the keyword

[lamp]

Hi to all,



I have this full text search query

SELECT name, org_id,

address_id



FROM organization



WHERE org_active='Y' AND MATCH(name) AGAINST('ABC*' IN BOOLEAN

MODE)

and I'm not getting any results. And there IS a org ABC,

Inc.

My assumption is the ampersand sign as a part of the

keyword.

Any idea?



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Full text search and sign as a part of the keyword

[lamp]

Another correction: Searching for Com, the test org is NOT
gonna be listed but all others will.

Searching for Com no results at all.

�
�




 Actually, looks like I'm wrong.

 For testing purpose I made an org

 CompMe

 When search for Comp it's gonna

 be shown on the list.



 When search for Comp it's also gonna be shown.
But

 Construction Company as well.

 Then I changed the name of

 the test org to ComMe.



 Searching for Com, the test org is gonna be listed.



 Though, Com no results at

 all.

 ?!?

 �













 Hi to all,















 I have this full text search query







 SELECT name, org_id,







 address_id















 FROM organization















 WHERE org_active='Y' AND MATCH(name) AGAINST('ABC*' IN

 BOOLEAN







 MODE)







 and I'm not getting any results. And there IS a org ABC,







 Inc.







 My assumption is the ampersand sign as a part of the







 keyword.







 Any idea?















 --



 MySQL General Mailing List



 For list archives: http://lists.mysql.com/mysql



 To unsubscribe: http://lists.mysql.com/mysql

Re: Full text search and sign as a part of the keyword

[lamp]

Actually, looks like I'm wrong.
For testing purpose I made an org
CompMe
When search for Comp it's gonna
be shown on the list.

When search for Comp it's also gonna be shown. But
Construction Company as well.
Then I changed the name of
the test org to ComMe.

Searching for Com, the test org is gonna be listed.

Though, Com no results at
all.
?!?
�






 Hi to all,







 I have this full text search query



 SELECT name, org_id,



 address_id







 FROM organization







 WHERE org_active='Y' AND MATCH(name) AGAINST('ABC*' IN
BOOLEAN



 MODE)



 and I'm not getting any results. And there IS a org ABC,



 Inc.



 My assumption is the ampersand sign as a part of the



 keyword.



 Any idea?







 --

 MySQL General Mailing List

 For list archives: http://lists.mysql.com/mysql

 To unsubscribe: http://lists.mysql.com/mysql

Re: Need help with query

[LAMP]

On Mar 17, 2011, at 3:01 PM, Geert-Jan Brits wrote:


Indeed, I don't thing there is.

Just be sure that each record has an unique combination of org_id  
and item_id, otherwise you might end up with an org_id that, for  
example, references 4 times item_id 34 in 4 different records, but  
no other item_ids. This is obvisouly not what you want.


Geert-Jan


Correct. That's why I use select distinct org_id, item_id in sub- 
query.


Is here anybody from mysql development team, to suggest to build IN  
ALL function?

:-)






2011/3/17 LAMP l...@afan.net
First I was thinking there is function IN ALL or something like  
that, since there are functions IN and EXISTS. And I would be able  
to make a query something like this

   select distinct org_id, item_id
   from orders
   where item_id in all (34, 36, 58, 63)
 order by org_id asc

But, there isn't any. :-(


The correct query is

select r.org_id
from
(
select distinct a.org_id, a.item_id
from orders a
where a.item_id in (34, 36, 58, 63)
order by a.org_id asc
) r
group by r.org_id
having count(*) = 4








On Mar 17, 2011, at 12:24 PM, Peter Brawley wrote:

 What I need is a list of orgs they bought all of items 34, 36, 58,  
63. every of them.


Some solutions under What else did buyers of X buy at http://www.artfulsoftware.com/infotree/queries.php 
.


PB

---

On 3/17/2011 12:00 PM, LAMP wrote:
Yes, that was my question. Though, since English is not my first  
language, let me try to post it again:


There is a list of all orgs and items org bough, from table called  
orders


item_idorg_id
342607
342607
341520
362607
361520
368934
3828
3815
385
3813
582607
582607
587295
581649
587295
581520
632607
632607
638871
637295
631520
6515
6520
951520
951520
957295
981520
987295


select org_id from orders where item_id in (34. 36. 58. 63) will  
give me a result


5
13
15
28
1520
1649
2607
7295
8871
8934

This is the list of ALL orgs they bought ANY of items (34. 36. 58.  
63). Agree?


What I need is a list of orgs they bought all of items 34, 36, 58,  
63. every of them. Result should be only orgs 2607 and 1520.


I hope it's more clear now.



On Mar 15, 2011, at 10:47 PM, Rhino wrote:


Your original question said: Need to select all (distinct) org_id  
they have item_id 34, 36, 58 and 63. All of them, not only some of  
them. 


That's the question I answered with my suggested query.

It sounds like that is not what you meant after all but I'm not sure  
what you DO want with your query. Why are 2607 and 1520 the only  
right answers?


Because they are. I look at the database and manually found the  
result I have to get. What's wrong with my statement?


Based on your own query, it looks like you only want an org_id for  
item_ids 34, 36, 58, and 63 if there are exactly 4 occurrences of  
that org_id amongst the desired item_ids


actually, there is mistake in my query, it should say having  
count(org_id) = 4
and, yes, that's what I want. I can get the correct list using the  
query I posted but I was hoping there is BETTER way.


but that wasn't in your statement of the problem. So please clarify  
EXACTLY what you want. Giving an incomplete or contradictory  
description of you want only wastes both your time and mine.


As I stated earlier, English is not my first language and I was  
trying to do my best. Sorry for confusing you.




--
Rhino

On 2011-03-15 20:35, LAMP wrote:

On Mar 15, 2011, at 6:18 PM, Rhino wrote:


All you should need is this:

select distinct org_id
from orders
where item_id in (34, 36, 58, 63)

I'm assuming that the DISTINCT operator is available in the version  
of MySQL that you are using. I don't currently have any version of  
MySQL installed so I can't try this myself to be sure it works in  
your version of MySQL.


--
Rhino

your query will give me every org_id that has ANY of item_id., I  
need org_id that has ALL of item_id. right?

result would be
2607
1520
8934
7295
1649
8871




On 2011-03-15 18:51, LAMP wrote:
Hi,
I need a help to build a query.

Let's say there is a table orders (simplified, of course)

CREATE TABLE orders (
`item_id` int,
`org_id` int,
) ENGINE=MyISAM


item_idorg_id
342607
342607
341520
362607
361520
368934
3828
3815
385
3813
582607
582607
587295
581649
587295
581520
632607
632607
638871
637295
631520
6515
6520
951520
951520
957295
981520
987295


Need to select all (distinct) org_id they have item_id 34, 36, 58  
and 63. All of them, not only some of them.


Result is org_id=2607 and org_id=1520

I can have it by

select

Re: Need help with query

[LAMP]

Yes, that was my question. Though, since English is not my first  
language, let me try to post it again:


There is a list of all orgs and items org bough, from table called  
orders


item_idorg_id
342607
342607
341520
362607
361520
368934
3828
3815
385
3813
582607
582607
587295
581649
587295
581520
632607
632607
638871
637295
631520
6515
6520
951520
951520
957295
981520
987295


select org_id from orders where item_id in (34. 36. 58. 63) will give  
me a result


5
13
15
28
1520
1649
2607
7295
8871
8934

This is the list of ALL orgs they bought ANY of items (34. 36. 58.  
63). Agree?


What I need is a list of orgs they bought all of items 34, 36, 58, 63.  
every of them. Result should be only orgs 2607 and 1520.


I hope it's more clear now.



On Mar 15, 2011, at 10:47 PM, Rhino wrote:



Your original question said: Need to select all (distinct) org_id  
they have item_id 34, 36, 58 and 63. All of them, not only some of  
them. 


That's the question I answered with my suggested query.

It sounds like that is not what you meant after all but I'm not sure  
what you DO want with your query. Why are 2607 and 1520 the only  
right answers?


Because they are. I look at the database and manually found the  
result I have to get. What's wrong with my statement?


Based on your own query, it looks like you only want an org_id for  
item_ids 34, 36, 58, and 63 if there are exactly 4 occurrences of  
that org_id amongst the desired item_ids


actually, there is mistake in my query, it should say having  
count(org_id) = 4
and, yes, that's what I want. I can get the correct list using the  
query I posted but I was hoping there is BETTER way.


but that wasn't in your statement of the problem. So please clarify  
EXACTLY what you want. Giving an incomplete or contradictory  
description of you want only wastes both your time and mine.


As I stated earlier, English is not my first language and I was trying  
to do my best. Sorry for confusing you.





--
Rhino

On 2011-03-15 20:35, LAMP wrote:


On Mar 15, 2011, at 6:18 PM, Rhino wrote:



All you should need is this:

select distinct org_id
from orders
where item_id in (34, 36, 58, 63)

I'm assuming that the DISTINCT operator is available in the  
version of MySQL that you are using. I don't currently have any  
version of MySQL installed so I can't try this myself to be sure  
it works in your version of MySQL.


--
Rhino


your query will give me every org_id that has ANY of item_id., I  
need org_id that has ALL of item_id. right?

result would be
2607
1520
8934
7295
1649
8871





On 2011-03-15 18:51, LAMP wrote:

Hi,
I need a help to build a query.

Let's say there is a table orders (simplified, of course)

CREATE TABLE orders (
`item_id` int,
`org_id` int,
) ENGINE=MyISAM


item_idorg_id
342607
342607
341520
362607
361520
368934
3828
3815
385
3813
582607
582607
587295
581649
587295
581520
632607
632607
638871
637295
631520
6515
6520
951520
951520
957295
981520
987295


Need to select all (distinct) org_id they have item_id 34, 36, 58  
and 63. All of them, not only some of them.


Result is org_id=2607 and org_id=1520

I can have it by

select org_id
from orders
where item_id in (34, 36, 58, 63)
group by org_id
having count(org_id)=4

but, I'm sure there is better solution?

Thanks for any help.

LAMP

Re: Need help with query

[LAMP]

First I was thinking there is function IN ALL or something like that,  
since there are functions IN and EXISTS. And I would be able to make a  
query something like this

select distinct org_id, item_id
from orders
where item_id in all (34, 36, 58, 63)
 order by org_id asc

But, there isn't any. :-(


The correct query is

select r.org_id
from
(
 select distinct a.org_id, a.item_id
 from orders a
 where a.item_id in (34, 36, 58, 63)
 order by a.org_id asc
) r
group by r.org_id
having count(*) = 4







On Mar 17, 2011, at 12:24 PM, Peter Brawley wrote:

 What I need is a list of orgs they bought all of items 34, 36, 58,  
63. every of them.


Some solutions under What else did buyers of X buy at http://www.artfulsoftware.com/infotree/queries.php 
.


PB

---

On 3/17/2011 12:00 PM, LAMP wrote:
Yes, that was my question. Though, since English is not my first  
language, let me try to post it again:


There is a list of all orgs and items org bough, from table called  
orders


item_idorg_id
342607
342607
341520
362607
361520
368934
3828
3815
385
3813
582607
582607
587295
581649
587295
581520
632607
632607
638871
637295
631520
6515
6520
951520
951520
957295
981520
987295


select org_id from orders where item_id in (34. 36. 58. 63) will  
give me a result


5
13
15
28
1520
1649
2607
7295
8871
8934

This is the list of ALL orgs they bought ANY of items (34. 36. 58.  
63). Agree?


What I need is a list of orgs they bought all of items 34, 36, 58,  
63. every of them. Result should be only orgs 2607 and 1520.


I hope it's more clear now.



On Mar 15, 2011, at 10:47 PM, Rhino wrote:



Your original question said: Need to select all (distinct) org_id  
they have item_id 34, 36, 58 and 63. All of them, not only some of  
them. 


That's the question I answered with my suggested query.

It sounds like that is not what you meant after all but I'm not  
sure what you DO want with your query. Why are 2607 and 1520 the  
only right answers?


Because they are. I look at the database and manually found the  
result I have to get. What's wrong with my statement?


Based on your own query, it looks like you only want an org_id for  
item_ids 34, 36, 58, and 63 if there are exactly 4 occurrences of  
that org_id amongst the desired item_ids


actually, there is mistake in my query, it should say having  
count(org_id) = 4
and, yes, that's what I want. I can get the correct list using the  
query I posted but I was hoping there is BETTER way.


but that wasn't in your statement of the problem. So please  
clarify EXACTLY what you want. Giving an incomplete or  
contradictory description of you want only wastes both your time  
and mine.


As I stated earlier, English is not my first language and I was  
trying to do my best. Sorry for confusing you.





--
Rhino

On 2011-03-15 20:35, LAMP wrote:


On Mar 15, 2011, at 6:18 PM, Rhino wrote:



All you should need is this:

select distinct org_id
from orders
where item_id in (34, 36, 58, 63)

I'm assuming that the DISTINCT operator is available in the  
version of MySQL that you are using. I don't currently have any  
version of MySQL installed so I can't try this myself to be sure  
it works in your version of MySQL.


--
Rhino


your query will give me every org_id that has ANY of item_id., I  
need org_id that has ALL of item_id. right?

result would be
2607
1520
8934
7295
1649
8871





On 2011-03-15 18:51, LAMP wrote:

Hi,
I need a help to build a query.

Let's say there is a table orders (simplified, of course)

CREATE TABLE orders (
`item_id` int,
`org_id` int,
) ENGINE=MyISAM


item_idorg_id
342607
342607
341520
362607
361520
368934
3828
3815
385
3813
582607
582607
587295
581649
587295
581520
632607
632607
638871
637295
631520
6515
6520
951520
951520
957295
981520
987295


Need to select all (distinct) org_id they have item_id 34, 36,  
58 and 63. All of them, not only some of them.


Result is org_id=2607 and org_id=1520

I can have it by

select org_id
from orders
where item_id in (34, 36, 58, 63)
group by org_id
having count(org_id)=4

but, I'm sure there is better solution?

Thanks for any help.

LAMP










--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Need help with query

[LAMP]

Hi,
I need a help to build a query.

Let's say there is a table orders (simplified, of course)

CREATE TABLE orders (
`item_id` int,
`org_id` int,
) ENGINE=MyISAM


item_idorg_id
342607
342607
341520
362607
361520
368934
3828
3815
385
3813
582607
582607
587295
581649
587295
581520
632607
632607
638871
637295
631520
6515
6520
951520
951520
957295
981520
987295


Need to select all (distinct) org_id they have item_id 34, 36, 58 and  
63. All of them, not only some of them.


Result is org_id=2607 and org_id=1520

I can have it by

select org_id
from orders
where item_id in (34, 36, 58, 63)
group by org_id
having count(org_id)=4

but, I'm sure there is better solution?

Thanks for any help.

LAMP

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Need help with query

[LAMP]

On Mar 15, 2011, at 6:18 PM, Rhino wrote:



 All you should need is this:

select distinct org_id
from orders
where item_id in (34, 36, 58, 63)

I'm assuming that the DISTINCT operator is available in the version  
of MySQL that you are using. I don't currently have any version of  
MySQL installed so I can't try this myself to be sure it works in  
your version of MySQL.


--
Rhino


your query will give me every org_id that has ANY of item_id., I need  
org_id that has ALL of item_id. right?

result would be
2607
1520
8934
7295
1649
8871





On 2011-03-15 18:51, LAMP wrote:

Hi,
I need a help to build a query.

Let's say there is a table orders (simplified, of course)

CREATE TABLE orders (
`item_id` int,
`org_id` int,
) ENGINE=MyISAM


item_idorg_id
342607
342607
341520
362607
361520
368934
3828
3815
385
3813
582607
582607
587295
581649
587295
581520
632607
632607
638871
637295
631520
6515
6520
951520
951520
957295
981520
987295


Need to select all (distinct) org_id they have item_id 34, 36, 58  
and 63. All of them, not only some of them.


Result is org_id=2607 and org_id=1520

I can have it by

select org_id
from orders
where item_id in (34, 36, 58, 63)
group by org_id
having count(org_id)=4

but, I'm sure there is better solution?

Thanks for any help.

LAMP




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: export result from select statement

[LAMP]

Eric Bergen wrote:

select into outfile is the correct way. What do you mean by doesn't
work? Does it give you an error?
  

It was an issue with permissions :-)

Thanks


On Sat, Jan 8, 2011 at 3:04 PM, LAMP l...@afan.net wrote:
  

Hi guys,
I wonder how to store to csv or txt file result from SELECT query? not a
whole table nor database. Just results from SELECT query.
Usually I use MySQL Query Browser and Export feature, but in this case I
don't have access with MySQL Query Browser. Though, I have a command line
access.
I found on one place something like
#SELECT 2+2 into outfile '/path/to/specific/directory/test.csv';
Though, it doesn't work?!?

Thanks.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=eric.ber...@gmail.com







  

Re: export result from select statement

[LAMP]

maybe it's clear to other but it's pretty unclear.
#mysql -username -p select * from table_name where id=123  
'/home/me/test/test.txt'

actually doesn't work?!?



Jerry Schwartz wrote:

The technique I've settled on is this:

mysql blah blah blah  the_select_query.sql  the_output_i_want.txt

That gives you a tab-delimited text file with column headings.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com

  

-Original Message-
From: LAMP [mailto:l...@afan.net]
Sent: Saturday, January 08, 2011 6:05 PM
To: mysql@lists.mysql.com
Subject: export result from select statement

Hi guys,
I wonder how to store to csv or txt file result from SELECT query?
not a whole table nor database. Just results from SELECT query.
Usually I use MySQL Query Browser and Export feature, but in this
case I don't have access with MySQL Query Browser. Though, I have a
command line access.
I found on one place something like
#SELECT 2+2 into outfile '/path/to/specific/directory/test.csv';
Though, it doesn't work?!?

Thanks.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp







  

export result from select statement

[LAMP]

Hi guys,
I wonder how to store to csv or txt file result from SELECT query?  
not a whole table nor database. Just results from SELECT query.
Usually I use MySQL Query Browser and Export feature, but in this  
case I don't have access with MySQL Query Browser. Though, I have a  
command line access.

I found on one place something like
#SELECT 2+2 into outfile '/path/to/specific/directory/test.csv';
Though, it doesn't work?!?

Thanks.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

need help with query...

[Lamp Lists]

I hate when somebody put in Subject line something like I just did but after 15 
minutes to try to be specific just with one short sentence - I gave up. So, you 
can hate me - I understand (though, help with my problem too) :-)


I have let say 3 tables people, organization, addresses. and they are linked to 
each other with column person_id.
to select person by specified person_id it's not big deal, really simple. 
though, I wonder how can I select records from these three tables but somehow 
marked which table it belongs?

e.g.

select p.first_name, p.last_name, p.status, p.date_registered, o.org_id, 
o.org_name, o.org_department, a.addres1, a.address2, a.city, a.state, a.zip
from people p, organization o, addresses a
where p.person_id=123 and o.person_id=p.person_id and a.person_id=p.person_id

I need somehow, together with result data, info which table data belogs?

e.g.
not exactly this way but something like this:
$data = array(
'people' = array('lamp', 'lists', 1, '2008-12-12'), 
'organization' = array(56, 'Lamp List', 'web'), 
'addresses' = array('123 Main St.', 'Toon Town', 'NY', '12345')
}
  
thanks for any help.

-afan



  

Re: need help with query...

[Lamp Lists]

From: Andy Shellam andy-li...@networkmail.eu
To: Lamp Lists lamp.li...@yahoo.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, December 17, 2008 2:29:08 PM
Subject: Re: need help with query...

Hi Afan

Why not prefix your field names with the table name?

select
p.first_name AS person_first_name,
p.last_name AS person_last_name,
p.status AS person_status,
p.date_registered AS person_date_registered,
o.org_id AS organization_org_id,
o.org_name AS organization_org_name,
o.org_department AS organization_org_department,
a.addres1 AS addresses_address1,
a.address2 AS addresses_address2,
a.city AS addresses_city,
a.state AS addresses_state,
a.zip AS addresses_zip
from people p, organization o, addresses a
where p.person_id=123 and o.person_id=p.person_id and a.person_id=p.person_id

Then in PHP (which I guess you're using from your example) do something like:

// Get every record from the database ($result is your MySQL result from 
mysql_query)
while ($row = mysql_fetch_assoc($result))
{
   $result = Array();

   // Run through each field in the row
   foreach ($row as $field = $value)
   {
  // Split the field into 2 segments split by _
  $fieldSplit = explode('_', $field, 1);

  // $fieldSplit will be, for example, Array(0 = 'person', 1 = 
'first_name')

  $result[$fieldSplit[0]][$fieldSplit[1]] = $value;

  // Now you should be able to access the person's first name using 
$result['person']['first_name']
   }
}

This code may not be perfect as I've just typed it out from memory so it may 
take a bit of tweaking.

Thanks,
Andy




Hi Andy,
the reason I can't use this because fields (columns) in select statement 
(p.first_name, p.last_name,...) are actually dynamically created. In my project 
different client will select different fields to be shown. 99% will select 
first_name, and last_name, but some don't care about date_registered, some will 
need more org data... 

actually, it will be more this way:

SELECT {$selected_fields} FROM people p, organization o. addresses a
WHERE ...

where 
$selected_fields = p.first_name, p.last_name, o.org_name
or
$selected_fields = p.first_name, p.last_name, o.org_name, a.address, a.city, 
a.state, a.zip
or
$selected_fields = o.org_name, a.address, a.city, a.state, a.zip

I hope I'm more clear now?

Though, I can do something as you suggested while creating $selected_fields
:-)

Thanks




















Jason Pruim wrote:
 
 On Dec 17, 2008, at 2:56 PM, Lamp Lists wrote:
 
 I hate when somebody put in Subject line something like I just did but after 
 15 minutes to try to be specific just with one short sentence - I gave up. 
 So, you can hate me - I understand (though, help with my problem too) :-)
 
 
 I have let say 3 tables people, organization, addresses. and they are linked 
 to each other with column person_id.
 to select person by specified person_id it's not big deal, really simple. 
 though, I wonder how can I select records from these three tables but 
 somehow marked which table it belongs?
 
 e.g.
 
 select p.first_name, p.last_name, p.status, p.date_registered, o.org_id, 
 o.org_name, o.org_department, a.addres1, a.address2, a.city, a.state, a.zip
 from people p, organization o, addresses a
 where p.person_id=123 and o.person_id=p.person_id and a.person_id=p.person_id
 
 I need somehow, together with result data, info which table data belogs?
 
 e.g.
 not exactly this way but something like this:
 $data = array(
 'people' = array('lamp', 'lists', 1, '2008-12-12'),
 'organization' = array(56, 'Lamp List', 'web'),
 'addresses' = array('123 Main St.', 'Toon Town', 'NY', '12345')
 }
 
 thanks for any help.
 
 -afan
 
 
 It may not be the best option, but when I've done that type of stuff in the 
 past, I add another field to the database and call it like List and put 
 People in the people database. and then you could just query the field List 
 and display it how ever you needed.
 
 
 -- Jason Pruim
 japr...@raoset.com
 616.399.2355
 
 
 
 



  

Re: need help with query...

[Lamp Lists]

From: Andy Shellam andy-li...@networkmail.eu
To: Lamp Lists lamp.li...@yahoo.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, December 17, 2008 2:48:31 PM
Subject: Re: need help with query...

Hi,

 
 Hi Andy,
 the reason I can't use this because fields (columns) in select statement 
 (p.first_name, p.last_name,...) are actually dynamically created. In my 
 project different client will select different fields to be shown. 99% will 
 select first_name, and last_name, but some don't care about date_registered, 
 some will need more org data...
 
 actually, it will be more this way:
 
 SELECT {$selected_fields} FROM people p, organization o. addresses a
 WHERE ...
 
 where
 $selected_fields = p.first_name, p.last_name, o.org_name
 or
 $selected_fields = p.first_name, p.last_name, o.org_name, a.address, a.city, 
 a.state, a.zip
 or
 $selected_fields = o.org_name, a.address, a.city, a.state, a.zip

So just tag AS table_field_name to each field when you're building your list 
of $selected_fields - e.g.

$selected_fields = p.first_name AS person_first_name, p.last_name AS 
person_last_name, o.org_name AS organization_org_name

You don't have to use the full table name either - for example in the following 
statement, you would then access the data using $result['p']['first_name'];

$selected_fields = p.first_name AS p_first_name, p.last_name AS p_last_name, 
o.org_name AS o_org_name

This approach is actually easier if you're creating the query dynamically, 
because you don't have to manually type a load of AS xxx statements after 
every field.

I've recently done something similar in one of my applications to wrap 
date/time fields in either FROM_UNIXTIME() or UNIX_TIMESTAMP() functions.

Andy




Yup! That'll do it!

Thanks Andy
;-)



  

which query solution is better?

[Lamp Lists]

hi,
I would like to get your opinions regarding which query you think is better 
solution and, of course - why.
I have (very simplified example) 3 tables: orders, members and addresses
I need to show order info for specific order_id, 
solution 1:
select ordered_by, order_date, payment_method, order_status

from orders
where order_id=123
select m.name, a.address, a.city, a.state, a.zip
from members m, addresses a
where m.member_id=$ordered_by and a.address_id=m.address_id   //$ordered_by 
is value from first query
solution 2:
select ordered_by, order_date, payment_method, order_status, (select m.name, 
a.address, a.city, a.state, a.zip from members m, addresses a where 
m.member_id=ordered_by and a.address_id=m.address_id)

from orders
where order_id=123
(queries are written without testing and maybe it doesn't work exactly, but 
it's more to gave you the idea what I'm talking about :D)
also,what if I have to list 20,50 or 100 orders instead one order? would be 
subquery still be an option?
thanks for any opinion.
-ll


  

Re: which query solution is better?

[Lamp Lists]

- Original Message 
From: John Hicks [EMAIL PROTECTED]
To: Lamp Lists [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Tuesday, July 8, 2008 11:20:16 AM
Subject: Re: which query solution is better?

Lamp Lists wrote:
 hi,
 I would like to get your opinions regarding which query you think is better 
 solution and, of course - why.
 I have (very simplified example) 3 tables: orders, members and addresses
 I need to show order info for specific order_id, 
 solution 1:
 select ordered_by, order_date, payment_method, order_status

 from orders
 where order_id=123
 select m.name, a.address, a.city, a.state, a.zip
 from members m, addresses a
 where m.member_id=$ordered_by and a.address_id=m.address_id   
 //$ordered_by is value from first query
 solution 2:
 select ordered_by, order_date, payment_method, order_status, (select m.name, 
 a.address, a.city, a.state, a.zip from members m, addresses a where 
 m.member_id=ordered_by and a.address_id=m.address_id)

 from orders
 where order_id=123
 (queries are written without testing and maybe it doesn't work exactly, but 
 it's more to gave you the idea what I'm talking about :D)
 also,what if I have to list 20,50 or 100 orders instead one order? would be 
 subquery still be an option?
 thanks for any opinion.
 -ll
  

I don't understand what syntax you're using for your second solution.

Your first solution uses two separate queries which will accomplish the 
task.

They could be combined into a single query like this:

select * from orders
left join members on member_id = ordered_by
left join addresses on addresses.address_id = members.address_id
where order_id = 123

-- john

sorry john. my bad. I should test the query before I post it because it doesn't 
work that way :D

though, let me modify the question:

solution 1:
select o.ordered_by, o.order_date, o.payment_method, o.order_status, 
concat(m.first_name, ' ', m.last_name) name
left join members m on m.member_id=o.ordered_by
from orders o
where o.order_id=123

vs.

select o.ordered_by, o.order_date, o.payment_method, o.order_status, (select 
concat(first_name, ' ', last_name) name from members where 
member_id=o.registered_by) name 
from orders o
where o.order_id=123

in first solution there is join and in second subquery. what's better and why?

sorry for this mess :D

-ll



  

can't find ft-min_word_len in /etc/my.conf ?

[Lamp Lists]

hi,
I need to change ft_min_word_len fro 4 to 3. the
proces is very well explained on mysql.com
though, when open /etc/my.conf can't find the
ft_min_word_len line?

when check is there:
mysql show variables like 'ft_min_word_len'
ft_min_word_len4

am I looking at the wrong file or something?

thanks.

-ll


  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: can't find ft-min_word_len in /etc/my.conf ?

[Lamp Lists]

Thanks.

Though, I added, repair table, restarted mysql - and
the same
:(

-ll


--- Warren Young [EMAIL PROTECTED] wrote:

 Lamp Lists wrote:
  I need to change ft_min_word_len fro 4 to 3. the
  proces is very well explained on mysql.com
  though, when open /etc/my.conf can't find the
  ft_min_word_len line?
 
 If a value for a configurable isn't given in my.cnf,
 it takes the 
 default value.  So, add the line, restart the
 server, and it will 
 override the default.
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:   

http://lists.mysql.com/[EMAIL PROTECTED]
 
 



  

Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.  
http://tools.search.yahoo.com/newsearch/category.php?category=shopping


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

different results between FULLTEXT search and LIKE search

[Lamp Lists]

hi,
I created table tasks
create table tasks(
task_id, int(4) not null primary key, 
task text not null, 
resolution text not null, 
fulltext (task, resolution)
)engine=myisam

when I run

seect * from tasks match(task,resolution)
against('certain service' in boolean mode)

I would get one record and the phrase is in resolution
column.

though, when I serach using LIKE
select * from tasks where task like '%certain
service%' or resolution like '%certain service%'
I would get 2 records. one record is the same as the
one above and the 2nd has the phrase in task column.
means there are 2 records, but fulltext shows me only
one.

what I'm doing wrong?

thanks.

-ll



  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: different results between FULLTEXT search and LIKE search

[Lamp Lists]

--- Lamp Lists [EMAIL PROTECTED] wrote:

 hi,
 I created table tasks
 create table tasks(
 task_id, int(4) not null primary key, 
 task text not null, 
 resolution text not null, 
 fulltext (task, resolution)
 )engine=myisam
 
 when I run
 
 seect * from tasks match(task,resolution)
 against('certain service' in boolean mode)
 
 I would get one record and the phrase is in
 resolution
 column.
 
 though, when I serach using LIKE
 select * from tasks where task like '%certain
 service%' or resolution like '%certain service%'
 I would get 2 records. one record is the same as the
 one above and the 2nd has the phrase in task column.
 means there are 2 records, but fulltext shows me
 only
 one.
 
 what I'm doing wrong?
 
 thanks.
 
 -ll
 


just made 2nd test and got different reault too:

select * from tasks match(task,resolution)
against('+certain +service' in boolean mode) 
result: 232 records

select * from tasks where (task like '%certain%' and
task like '%service%') or (resolution like '%certain%'
and resolution like '%service%')
result: 7 records

?!?!!??

-ll




  

Be a better friend, newshound, and 
know-it-all with Yahoo! Mobile.  Try it now.  
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: different results between FULLTEXT search and LIKE search

[Lamp Lists]

--- Lamp Lists [EMAIL PROTECTED] wrote:

 
 --- Lamp Lists [EMAIL PROTECTED] wrote:
 
  hi,
  I created table tasks
  create table tasks(
  task_id, int(4) not null primary key, 
  task text not null, 
  resolution text not null, 
  fulltext (task, resolution)
  )engine=myisam
  
  when I run
  
  seect * from tasks match(task,resolution)
  against('certain service' in boolean mode)
  
  I would get one record and the phrase is in
  resolution
  column.
  
  though, when I serach using LIKE
  select * from tasks where task like '%certain
  service%' or resolution like '%certain service%'
  I would get 2 records. one record is the same as
 the
  one above and the 2nd has the phrase in task
 column.
  means there are 2 records, but fulltext shows me
  only
  one.
  
  what I'm doing wrong?
  
  thanks.
  
  -ll
  
 
 
 just made 2nd test and got different reault too:
 
 select * from tasks match(task,resolution)
 against('+certain +service' in boolean mode) 
 result: 232 records
 
 select * from tasks where (task like '%certain%' and
 task like '%service%') or (resolution like
 '%certain%'
 and resolution like '%service%')
 result: 7 records
 
 ?!?!!??
 
 -ll
 
 
 
 
  


 Be a better friend, newshound, and 
 know-it-all with Yahoo! Mobile.  Try it now. 

http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
 
 
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:   

http://lists.mysql.com/[EMAIL PROTECTED]
 
 


even worse:
select count(*) from tasks match(task,resolution)
against('certain') 
result: 0
 
select count(*) from tasks where task like '%certain%'
or resolution like '%certain%';
result: 173

?

-ll


  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

how to re-index a table?

[Lamp Lists]

hi,
I have table, something like:
create table example (
ex_id integer(8) not null auto_increment primary key, 

ex_col1 int(8) null, 
ex_col2 int(4) not null, 

index(ex_col1), 

index(ex_col2)

)engine=myisam;




ex_col1 and ex_col2 are indexed separately. Now I need to make one index with 
these to columns.
Using MySQL Browser I did it no problem.


I think I have to re-index them now, right? How?


thanks for any help.


-a



  

Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.  
http://tools.search.yahoo.com/newsearch/category.php?category=shopping