RE: GUI for mysql

[Andrew Dixon]

Hi.

Navicat is excellent and a linux version is available. I would assume it is
the same as the windows version but I have not used it personally.
http://www.navicat.com/linux_overview.php3

Best Regards
   
>>> Andrew

SpamNet - Stop spam immediately and help me too!
www.cloudmark.com/spamnet 
Referral Code: 2tc4hl 
(use the code a get spamnet for $1.99 per month instead of $4.99!!!)



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

In Statement Help

[Andrew Dixon]

Hi Everyone.

I require some help with an IN statement I'm trying to get working. I have
inherited a database from someone else in which there is a table with
project information and a field containing which geographic regions the
projects relate. As a single project can relate to multiple geographic
regions the person who created the database create this field as a varchar
field and populated it with a pipe (|) delimited list of ID's of the
geographic regions (ID from another table). An example of the data in this
field is:

1|5|9|10|12

Or 

1

Or

9|5|7

I have been asked to write a query to return all the projects for a single
geographic region. This is what I have come up with:

SELECT  project_title 
FROMprojects
WHERE   9 IN (REPLACE(geo_region,'|',','))
ORDER BYproject

Where 9 is the geographic region ID I'm looking for and geo_region is the
field containing the pipe delimited list. Now with the above three lines of
data I would have expected it to return 2 rows, however it only returns one
(the last one). If I change the number to 5, I would expect 2 rows as well,
however it doesn't return any. It only appears to return a row if the number
appears at the beginning of the list!!! I'm not sure where I'm going wrong,
or even if what I'm trying to do is possible (please say it is!!!). Any
advise would be very gratefully received.

Best Regards,

Andrew



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

RE: In Statement Help

[Andrew Dixon]

Hi Rhino

I had pretty much already suggested doing that, but I have been told that
I'm not allowed to redesign the database as there is already lots other code
working on it already that would need changing as well. I personally think
the database design is terrible, but I have to work with what I have been
given. Any ideas?

Best Regards

Andrew.



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

RE: In Statement Help

[Andrew Dixon]

Hi.

Thanks Stephen, the works like a dream... Unlike this database which is more
like a nightmare!!!

Best Regards

Andrew. 

-Original Message-
From: Stephen E. Bacher [mailto:[EMAIL PROTECTED] 
Sent: 03 August 2004 12:16
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: In Statement Help

Andrew Dixon <[EMAIL PROTECTED]> wrote:

>Hi Everyone.
>
>I require some help with an IN statement I'm trying to get working. I 
>have inherited a database from someone else in which there is a table 
>with project information and a field containing which geographic 
>regions the projects relate. As a single project can relate to multiple 
>geographic regions the person who created the database create this 
>field as a varchar field and populated it with a pipe (|) delimited 
>list of ID's of the geographic regions (ID from another table). An 
>example of the data in this field is:
>
>1|5|9|10|12
>
>Or
>
>1
>
>Or
>
>9|5|7
>
>I have been asked to write a query to return all the projects for a 
>single geographic region. This is what I have come up with:
>
>SELECT project_title 
>FROM   projects
>WHERE  9 IN (REPLACE(geo_region,'|',','))
>ORDER BY   project

[...]

I would suggest something like

WHERE CONCAT('|',geo_region,'|') LIKE '%|9|%'

 - seb



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

Help with a SELECT Statement

[Andrew Dixon]

Hi Everyone.
 
I have the following SELECT statement the get information from two tables.
The statement only works when it finds a value for the image_archive.circuit
value in the circuits table. However, not all entries in the database has a
value in the this field, some are set to 0 as the circuit for that image was
not known or the image was not a circuit.
 
SELECT   image_archive.filename, 
  image_archive.year, 
  image_archive.month, 
  image_archive.driver_forename, 
  image_archive.driver_surname, 
  image_archive.team,
  image_archive.event, 
  circuits.name as circuit_name, 
  image_archive.description, 
  image_archive.title, 
  image_archive.membership_no
FROM   image_archive, circuits
WHERE  image_archive.id = 109
AND   circuits.id = image_archive.circuit
 
How can I modify the statement to allow it to return a record when the
image_archive.circuit value is 0, but to return the circuit name when the
value is greater than 0. At the moment when the circuit value is 0 no
records are returned even though I know the rest of the information is in
the image_archive table. Hope that makes sense. Thanks in advances for any
help.
 
BTW, I am using 4.0.18.

Best Regards

   >>> Andrew

SpamNet - Stop spam immediately and help me too!
  www.cloudmark.com/spamnet 
Referral Code: 2tc4hl 
(use the code a get spamnet for $1.99 per month instead of $4.99!!!)

RE: Query Help

[Andrew Dixon]

Already tried that, but is 2 appears at the end of the list is doesn't get
picked up because there is no comma at the end of the list

Best regards

    >>> Andrew Dixon 

-Original Message-
From: Dean Urmson [mailto:[EMAIL PROTECTED] 
Sent: 11 June 2004 12:53
To: [EMAIL PROTECTED]
Subject: RE: Query Help

> For example:
> 
> gallery_id | gallery_name | keywords 
> 1  | test | 1,2,3,4
> 2  | test2| 3,4,5,6
> 
> And I won't to get all the galleries with where the have the keywords 
> 2, which in this case would be record 1 or keyword 4 which would be 
> both record.
> 
> SELECTgallery_id, gallery_name
> FROM  galleries
> WHERE keywords 
> 

SELECT  gallery_id, gallery_name
FROMgalleries
WHERE   keywords LIKE '%2% 

Or if you want to avoid the above picking up keywords 12, 20, 21, 22 etc...
And each keyword is seperated with a comma then

SELECT  gallery_id, gallery_name
FROMgalleries
WHERE   keywords LIKE '%2,% 

Cheers

Dean


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



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

RE: Query Help

[Andrew Dixon]

Excellent, thanks for that. I have used the first way as the second way gave
me different results (lower record count) from what I was getting with it
via a server side script. The first way provide me with the same record
count. Thanks.

Best regards

>>> Andrew Dixon

-Original Message-
From: Dobromir Velev [mailto:[EMAIL PROTECTED] 
Sent: 11 June 2004 12:58
To: Andrew Dixon - MSO.net; [EMAIL PROTECTED]
Subject: Re: Query Help

Hi,

You could use either something like this
SELECT  gallery_id, gallery_name
FROM galleries g 
WHERE   keywords rlike '(^|,)$keyword_id(,|$)';

or 

SELECT  gallery_id, gallery_name
FROM galleries g 
WHERE   $keyword_id in (keywords);


and replace the $keyword_id with the id of the keyword you are looking for. 

Personally I would prefer the first option and put an index on the keywords
field but you should check for yourself which query will work faster.


-- 
Dobromir Velev
[EMAIL PROTECTED]
http://www.websitepulse.com/

On Friday 11 June 2004 14:22, Andrew Dixon - MSO.net wrote:
> Hi Everyone.
>
> I have the following a table with a varchar column that contains a comma
> delimited list of id's from another table that relates the item keywords
in
> the other table.
>
> The table keywords contains
>
> keyword_id (int/auto increment/primary key)
> Keyword (varchar/normal key)
>
> The galleries table contains:
>
> gallery_id (int/auto increment/primary key)
> gallery_name (varchar)
> keywords (varchar)
>
> I didn't design the database and I know it is not a good design, but I'm
> stuck with it and I need a query to get the gallery_id when I have a
> certain keyword_id
>
> For example:
>
> gallery_id | gallery_name | keywords
> 1  | test | 1,2,3,4
> 2  | test2| 3,4,5,6
>
> And I won't to get all the galleries with where the have the keywords 2,
> which in this case would be record 1 or keyword 4 which would be both
> record.
>
> SELECTgallery_id, gallery_name
> FROM  galleries
> WHERE keywords 
>
> Hope that makes sense, thanks in advanced.
>
> Best Regards,
>
> Andrew Dixon.





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

UPDATE SELECT

[Andrew Dixon]

Hi everyone.

I'm trying to do the following update, which someone at Macromedia gave me,
however I get a SQL error when I'm trying to use it with MySQL, any ideas on
what is wrong.

update general_users
set rolling_total = sumUsr.rollingTotal
from
(
select id,sum(jan+feb+mar+apr+may+jun+jul+aug+sep+oct+nov+dec) as
rollingTotal
from general_users
group by id
) as sumUsr

where general_users.id = sumUsr.id

Thanks.

Best Regards
>>> Andrew

www.MSOnet.co.uk


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Access to MySQL from Linux command line

[Andrew Dixon - MSO.net]

Hi All.

Does anyone know of any tools to convert a MS Access file to MySQL from 
the Linux command line?


Thanks.

Andrew

Re: Access to MySQL from Linux command line

[Andrew Dixon - MSO.net]

Hi Adam.

I need to get all the data.
  
Andrew



Adam wrote:


Drew,

That's vague. Specifically what do you want from the Access database (e.g.
schema, data, etc.)?

A- 
 

 

Sorting by a comma list

[Andrew Dixon - MSO.net]

Hi Everyone.

I'm not sure if this is possible of not, but I want to sort a query by a
comma list. Here is what I'm doing:

SELECT id, title, description
FROM table1
WHERE   id IN (4,1,3,6,8,2)

This returns the results ok but they are not in any particular order. I want
the result in the order the ID's appear in the comma list in the IN
statement. Is there a ORDER BY statement I can add to do this?

So what I want to do is:

SELECT id, title, description
FROM table1
WHERE   id IN (4,1,3,6,8,2)
ORDER BY id in this order (4,1,3,6,8,2)

Thanks

Andrew.


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

RE: Sorting by a comma list

[Andrew Dixon - MSO.net]

Excellent. Worked like a dream. Thanks. 

Andrew

-Original Message-
From: news [mailto:[EMAIL PROTECTED] On Behalf Of Harald Fuchs
Sent: 23 September 2004 15:14
To: [EMAIL PROTECTED]
Subject: Re: Sorting by a comma list


I think

  SELECT id, title, description
  FROM table1
  WHERE id IN (4,1,3,6,8,2)
  ORDER BY field(id,4,1,3,6,8,2)

should work.


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

Query Help

[Andrew Dixon - MSO.net]

Hi Everyone.

I have the following a table with a varchar column that contains a comma
delimited list of id's from another table that relates the item keywords in
the other table.

The table keywords contains

keyword_id (int/auto increment/primary key)
Keyword (varchar/normal key)

The galleries table contains:

gallery_id (int/auto increment/primary key)
gallery_name (varchar)
keywords (varchar)

I didn't design the database and I know it is not a good design, but I'm
stuck with it and I need a query to get the gallery_id when I have a certain
keyword_id

For example:

gallery_id | gallery_name | keywords 
1  | test | 1,2,3,4
2  | test2| 3,4,5,6

And I won't to get all the galleries with where the have the keywords 2,
which in this case would be record 1 or keyword 4 which would be both
record.

SELECT  gallery_id, gallery_name
FROMgalleries
WHERE   keywords 

Hope that makes sense, thanks in advanced.

Best Regards,

Andrew Dixon.



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

MySQL FULLTEXT maximum score?

[Andrew Dixon - MSOnet]

>Hi Everyone.
>
>Quick question. When you do a search on a MySQL FULLTEXT index using MATCH 
>you
>get a value returned of the relevance of the result. Is there a maximum
>value that can be returned so that I can convert these numbers into
>precentages???
>
>Thank you very much.
>
>
>Best regards
>  >>> Andrew Dixon
>
>www.mso.net
>Head Office Tel: + 44 1474 813816
>Head Office Fax: + 44 1474 813819
>Sales & Marketing Office Tel: +44 1892 610225


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php