RE: select for text within a field.

2004-11-09 Thread Shanta McBain 
Shanta McBain mailto:[EMAIL PROTECTED]
on Wednesday, October 20, 2004 9:29 AM said:

Sorry to take so long to get back. I have been putting by bees to bed for the 
winter. Now I can program again.

 [% FOREACH link = DBI.query(SELECT * FROM url_tb
   WHERE  products  LIKE 'honey'
   AND (site_name =  'Apis' OR
 site_name = 'All')
   ORDER BY link_order
   )%]

are you saying the products field has data like this in it?
tacosburritoshoneycrackerscake ... and you want to search for 'honey'?
do LIKE '%honey%'. 

No the data is stored 

honey pollen pollination

but of course this should really be normalized and
split into another table.

True it should. In my windoz days I used Delphi and Access an this was a snap. 
But it was all done from the GUI. Now I have a minor problem in that I use a 
web application call Extropia and I have not got it down to modify the SQL in 
Perl as of yet. Extropia dose all SQL in a DataHandler's so that it is easy 
to port the basic application from system to system. IE. Change a few lines 
of code and it calls flat files, Change them again and they call MySQL, or 
postgres or Oracle. 

To learn the process of normalizing (linking tables with referential integrity 
etc) in MySQL I have chosen to do the work first in TTML then port to Perl 
ActionHandlers.  The long way around it might be said but I have active sites 
that need immediate improvements. 

-- 
Thanks
Shanta McBain
Http://computersystemconsulting.ca Web hosting and Application Hosting.

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

Re: select for text within a field.

2004-11-09 Thread Shanta McBain 
On Tuesday 09 November 2004 09:21 am, Shanta McBain wrote:
  [% FOREACH link = DBI.query(SELECT * FROM url_tb
WHERE  products  LIKE 'honey'
AND (site_name =  'Apis' OR
  site_name = 'All')
ORDER BY link_order
)%]

I used PHPMyAdmin to create the fallowing code which works in PHPMyAdmin.

SELECT * 
FROM `address_book_tb` 
WHERE `category` = 'beekeeper'
AND `products` LIKE '%Pollinator%'
ORDER BY `company_name` ASC 

This returns the only record in the table that contains the right info.

In TTML
[% FOREACH link = DBI.query(SELECT * 
FROM address_book_tb 
  WHERE category = 'beekeeper'
  AND products LIKE '%Pollinator%'
  ORDER BY company_name ASC 
  )%]

works

[% FOREACH link = DBI.query(SELECT * 
FROM 'address_book_tb' 
  WHERE 'category' = 'beekeeper'
  AND 'products' LIKE '%Pollinator%'
  ORDER BY 'company_name' ASC 
  )%]

This dose not work. Troughs a TTML error

Seems TTML dose not like the single quotes around the table and field names.

Now to learn to link tables on entry and to join them on select. From Perl and 
TTML. This will  not be as easy.

A note on the storage of data in the field it actually is stored like.

Nuces,Wax,Pollen,Honey,Pollinator,Queens Seems the web app places the contents 
of the field into a , delimeted string.

So it needs the %.  The '_' one character or just 'word' will not work. 



--
Thanks
Shanta McBain
Http://computersystemconsulting.ca Web hosting and Application Hosting.

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

select for text within a field.

2004-10-20 Thread Shanta McBain 
Hi all

I am trying to get my mind around the process of searching for a text within a 
field.

On one of my sites I collect information from users about the products they 
sell. This is from a predefined list of keywords. such as Bees queens 
honey 

(code is TTML)

[% FOREACH link = DBI.query(SELECT * FROM url_tb
  WHERE  products = 'honey'
  AND (site_name =  'Apis' OR site_name =  
'All')
  ORDER BY link_order
  )%]

This works if they only produce honey. More then honey stored in the list and 
it fails.

I have tried
[% FOREACH link = DBI.query(SELECT * FROM url_tb
  WHERE  products  LIKE 'honey'
  AND (site_name =  'Apis' OR site_name =  
'All')
  ORDER BY link_order
  )%]

Dose not work.

 [% FOREACH link = DBI.query(SELECT * FROM url_tb
  MATCH (products,) AGAINST ('honey'
  AND (site_name =  'Apis' OR site_name =  
'ECF')
  ORDER BY link_order
  )%]

This also dose not seem to work.

How do I query a field for a word within a group of words?
-- 
Thanks
Shanta McBain
Http://computersystemconsulting.ca Web hosting and Application Hosting.

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

RE: select for text within a field.

2004-10-20 Thread Chris W. Parker 
Shanta McBain mailto:[EMAIL PROTECTED]
on Wednesday, October 20, 2004 9:29 AM said:

 [% FOREACH link = DBI.query(SELECT * FROM url_tb
   WHERE  products  LIKE 'honey'
   AND (site_name =  'Apis' OR
 site_name = 'All')
   ORDER BY link_order
   )%]

are you saying the products field has data like this in it?
tacosburritoshoneycrackerscake ... and you want to search for 'honey'?
do LIKE '%honey%'. but of course this should really be normalized and
split into another table.



chris.

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