Re: -help

[Rhino]

It would be easier to answer a specific question

--
Rhino

- Original Message - 
From: "Terry Spencer" <[EMAIL PROTECTED]>

To: 
Sent: Sunday, March 05, 2006 6:41 PM
Subject: -help







Terry Spencer
Haigh Consultancy Services

Tel:  +44 (0)116 262 3966

Fax:  +44 (0)116 262 3946 (Leciester Office)

Fax:  +44 (0)870 052 4572 (Terry)

Mob: +44 (0)7796108244
www.haigh-cs.co.uk 










No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006


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

-help

[Terry Spencer]

 

 

Terry Spencer 
Haigh Consultancy Services 

Tel:  +44 (0)116 262 3966 

Fax:  +44 (0)116 262 3946 (Leciester Office)

Fax:  +44 (0)870 052 4572 (Terry)

Mob: +44 (0)7796108244
www.haigh-cs.co.uk   

 

Re: Help on sql statement (not MySQL specifik)

[Rhino]

I'm glad to hear that your data isn't corrupt! That would have complicated 
your life a bit, at least in the short term


The additional information you have supplied helps me understand a bit 
better but I still don't really understand enough. I'll try to ask some 
specific questions that will help me understand the data and what you are 
trying to do better.


1. What kind of join are you doing to combine these tables? Is it an inner 
join or some kind of outer join? Are you satisfied that it is correctly 
joining the tables and giving you a true picture of the different events 
affecting the patients? I just want to be sure that the join is giving the 
right data before we go any farther; if it isn't, we should fix the join 
first.


2. Does the id belong to a specific patient? For example, does id 2 belong 
to Tony Blair while id 3 belongs to Jacques Chirac? I think this must be the 
case, but I want to be sure.


3. What do you mean by an 'event'? Is this a surgical procedure like "remove 
appendix" or just something like emptying a bedpan? What exactly is event 4? 
If it's secret, that's okay but it would help me understand the problem 
better to know what event 4 and a some of the other events are. Can a 
patient have more than one event 4? For example, if event 4 is "empty 
patient's bedpan" that can probably happen many times but  if event 4 is 
"patient died", that can obviously only happen once.


4. How do you propose to determine the time difference between events when 
you aren't storing the times that the events took place??? You described the 
record number (recno) as a simple ascending integer earlier but now I wonder 
if you mean that it is actually a timestamp or datetime value? Otherwise, I 
don't see how an expression like 7 - 4 (for records 7 and 4) is going to 
give you a value like 2 hours and 10 minutes.


5. What do you mean when you described eType as "nominal" and not "interval" 
data?


--
Rhino

- Original Message - 
From: "Søren Merser" <[EMAIL PROTECTED]>

To: 
Sent: Sunday, March 05, 2006 4:37 PM
Subject: Re: Help on sql statement (not MySQL specifik)



Hi
Thank You for all Your efforts
I'll try once again to clarify my problem

My tabel (t_temp) isn't corrupt but is the result form a join from two 
other tables

1) t_base, with id's and basic info of patients.
2) t_events. with id (of the patient in question) and the type of the 
event.


As a patient, or id can have more than one event t_temp will end up with 
one or more records accordingly

if no event has occured for id, etype=NULL

t_temp:

id  |   etype
-
1|NULL
2|4
2|6
3|NULL
4|NULL
5|1
5|3
6|7
6|3

Now I want to examine for the event of etype=4, in particular the time 
between the events.
I want to extract all the id's with etype=4 i.e. the event in question has 
happend othervise NULL.

Order must be preserved.
Result: the number of records will mirror the total number of operations 
and the actual record number mirrors the time of the event



id  |   etype
-
1|NULL
2|4
3|NULL
4|NULL
5|NULL
6|NULL

6 operations in all, second operation had event 4

Kind regards Soren
Ps
1)
As I make subsets from t_temp depending other columns, the id's do not 
exactly indicate time of events

2)
etype is nominal, not inteval data so You can't use < or > operator


- Original Message - 
From: "Rhino" <[EMAIL PROTECTED]>

To: "Michael Stassen" <[EMAIL PROTECTED]>
Cc: "Søren Merser" <[EMAIL PROTECTED]>; 
Sent: Sunday, March 05, 2006 9:05 PM
Subject: Re: Help on sql statement (not MySQL specifik)


You're absolutely right that I'd need some good luck for this query to 
work for every possible data value that the table could continue.


I realized the combination of 'group by id' and 'select id, type' was not 
very good SQL - 'select id, ' would be a much more 
standard construction to go with 'group by id' - as I developed that 
query. But I was too lazy to dig through the manual to find out exactly 
what MySQL would do with that query; it worked fine for the data given. 
But you're right, I should have at least warned that this was dubious SQL 
before posting it. The original poster could easily have though that this 
was actually good SQL when it isn't.


Again, cleaning up the data (assuming it is messed up!) should be the 
first priority and any query would just be a bandaid until that is done. 
The query would probably be a lot easier if the data was clean to start 
with.


In any case, thanks for keeping me honest.

--
Rhino

- Original Message - 
From: "Michael Stassen" <[EMAIL PROTECTED]>

To: "Rhino" <[EMAIL PROTECTED]>
Cc: "Søren Merser" <[EMAIL PROTECTED]>; 
Sent: Sunday, March 05, 2006 2:26 PM
Subject: Re: Help on sql statement (not MySQL specifik)



Rhino wrote:
I don't reall

Re: Help on sql statement (not MySQL specifik)

[Søren Merser]

Hi
Thank You for all Your efforts
I'll try once again to clarify my problem

My tabel (t_temp) isn't corrupt but is the result form a join from two other 
tables

1) t_base, with id's and basic info of patients.
2) t_events. with id (of the patient in question) and the type of the event.

As a patient, or id can have more than one event t_temp will end up with one 
or more records accordingly

if no event has occured for id, etype=NULL

t_temp:

id  |   etype
-
1|NULL
2|4
2|6
3|NULL
4|NULL
5|1
5|3
6|7
6|3

Now I want to examine for the event of etype=4, in particular the time 
between the events.
I want to extract all the id's with etype=4 i.e. the event in question has 
happend othervise NULL.

Order must be preserved.
Result: the number of records will mirror the total number of operations and 
the actual record number mirrors the time of the event



id  |   etype
-
1|NULL
2|4
3|NULL
4|NULL
5|NULL
6|NULL

6 operations in all, second operation had event 4

Kind regards Soren
Ps
1)
As I make subsets from t_temp depending other columns, the id's do not 
exactly indicate time of events

2)
etype is nominal, not inteval data so You can't use < or > operator


- Original Message - 
From: "Rhino" <[EMAIL PROTECTED]>

To: "Michael Stassen" <[EMAIL PROTECTED]>
Cc: "Søren Merser" <[EMAIL PROTECTED]>; 
Sent: Sunday, March 05, 2006 9:05 PM
Subject: Re: Help on sql statement (not MySQL specifik)


You're absolutely right that I'd need some good luck for this query to 
work for every possible data value that the table could continue.


I realized the combination of 'group by id' and 'select id, type' was not 
very good SQL - 'select id, ' would be a much more 
standard construction to go with 'group by id' - as I developed that 
query. But I was too lazy to dig through the manual to find out exactly 
what MySQL would do with that query; it worked fine for the data given. 
But you're right, I should have at least warned that this was dubious SQL 
before posting it. The original poster could easily have though that this 
was actually good SQL when it isn't.


Again, cleaning up the data (assuming it is messed up!) should be the 
first priority and any query would just be a bandaid until that is done. 
The query would probably be a lot easier if the data was clean to start 
with.


In any case, thanks for keeping me honest.

--
Rhino

- Original Message - 
From: "Michael Stassen" <[EMAIL PROTECTED]>

To: "Rhino" <[EMAIL PROTECTED]>
Cc: "Søren Merser" <[EMAIL PROTECTED]>; 
Sent: Sunday, March 05, 2006 2:26 PM
Subject: Re: Help on sql statement (not MySQL specifik)



Rhino wrote:
I don't really understand _why_ you want to do this but here is a query 
that gives the result you want:


select id, case type when 4 then 4 else null end as type
from Soren01
group by id;

The GROUP BY ensures that you get one row for each value of id; the case 
expression in the Select says that if the value of the type is 4, leave 
it alone, otherwise display null.


--
Rhino


Unfortunately, that won't work unless you are very lucky.  You aren't 
grouping by type, and CASE is not an aggregate function.  Mysql will use 
the value for type from the first row it finds for each id in the CASE 
statement.  The following illustrate the problem:


  DROP TABLE nu;
  CREATE TABLE nu (recno INT, id INT, type INT);
  INSERT INTO nu VALUES
  (1,1,NULL), (2,2,4), (3,2,6), (4,3,5), (5,3,4), (6,3,3);

  SELECT * FROM nu;
+---+--+--+
| recno | id   | type |
+---+--+--+
| 1 |1 | NULL |
| 2 |2 |4 |
| 3 |2 |6 |
| 4 |3 |5 |
| 5 |3 |4 |
| 6 |3 |3 |
+---+--+--+

  SELECT id, CASE type WHEN 4 THEN 4 ELSE NULL END AS type
  FROM nu
  GROUP BY id;
+--+--+
| id   | type |
+--+--+
|1 | NULL |
|2 | 4|
|3 | NULL |
+--+--+

As you can see, id=3 has a row with type=4, but it isn't found.

You could do this:

  SELECT id, IF(SUM(type=4)>0, 4, NULL) AS type FROM nu GROUP BY id;
+--+--+
| id   | type |
+--+--+
|1 | NULL |
|2 |4 |
|3 |4 |
+--+--+

but it's hard to see how that's better than your previous, simpler 
suggestion


  SELECT DISTINCT id FROM nu WHERE type = 4;

Michael


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006






--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006




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

Re: 1 day left: 75% discount on MySQL/Firebird/InterBase/Oracle/SQL Server developer tool!

[Daniel Kasak]

Charles Walmsley wrote:

I don't think [EMAIL PROTECTED] should be used for sales.  Is there a policy
about this?
  
My impression was that most people feel that the small number of product 
advertisements that are directly related to MySQL are OK. That's only an 
impression, but that's how I feel anyway. It's interesting, and possibly 
useful, to know what commercial products are out there for MySQL. Some 
people put [ Announcement ] or [ Advertisement ] in the subject line to 
make it plainly obvious what sort of content the message will contain. 
This is a good idea too.


Considering the volume of traffic generally, I don't think it's 
justified to start chasing ( on-topic ) commercial announcements out of 
the list.


I don't know about any official policy.

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au

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

Re: Boolean searches on InnoDB tables?

[Heikki Tuuri]

Daevid,

- Original Message - 
From: ""Daevid Vincent"" <[EMAIL PROTECTED]>

Newsgroups: mailing.database.myodbc
Sent: Saturday, March 04, 2006 9:54 AM
Subject: Boolean searches on InnoDB tables?



I just discovered this:
http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html
Which states:
"They can work even without a FULLTEXT index, although a search executed 
in

this fashion would be quite slow. "

But then I'm kicked in the nuts because:
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
"Full-text indexes can be used only with MyISAM tables"

When I try a query on an InnoDB table:

"SELECT * FROM categories WHERE MATCH (name) AGAINST ('+ELECTRONICS' IN
BOOLEAN MODE);"

I get:
"Error Code : 1214
The used table type doesn't support FULLTEXT indexes"

So, what is the deal? Am I missing something?

And if I can't use boolean searches on InnoDB tables with mySQL 5.0.18,
Then WHEN will I be able to?


Osku is working on FULLTEXT for InnoDB.


In the mean time, what is the best way to generate this equivallent
functionality via PHP or some other mySQL 5 sanctioned way? I've seen
several different examples on the web, but don't know which to commit to.


You could look at Sphinx, for exapmple.

Best regards,

Heikki

Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL

InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM 
tables

http://www.innodb.com/order.php


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

Importing CSV file into MySQL DB - Newbie Question

[Derek Doerr]

I have a CSV file that I want to import into a MySQL DB table. The file 
contains 15 fields.  The able to import into will contain those same 15 
fields, plus an auto-generated Primary Key fields.


Since this is the first time I'm working with mysqlimport, I created a 
small test table to start with - "simple1", containing two varchar 
fields - field1 and field2.


I'm trying to import a small test file into simple1, to get the hang of 
using mysqlimport.  The test file contains 2 records and 3 lines - the 
3rd line is blank:


"test1","test2"
"test11","test3"

I run the import as follows:
C:\Program Files\xampp\mysql\bin>mysqlimport.exe
--lines-terminated-by=\r --fields-terminated-by=, --local --user=root 
cars c:\dev\test\simple1.csv


The import report shows:   Records: 1  Deleted: 0  Skipped: 0 Warnings: 0

The data that ends up in the DB, however, only contains 1 record:
"\"test1\"","\"test2\"
\"test11\""

(1) how do I get mysqlimport to import both records, properly parsing 
the fields - two fields per record?
(2) do I need to wrap the imported records in double-quotes?  Why do the 
double-quotes show up in the MySQL DB table?


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

viewing number of current connections

[jonathan]

is there a way to view the current number of connections in mysql?  
I'm developing a PHP / MySQL app and would like to be able to debug a  
few performance problems.


Ideally, I'd like to be able to query the number of current  
connections via the mysqli interface and make decisions based upon that.


-jonathan

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

Problem mysql 4.1 to mysql5 -

[bobgoodwin]

I have two FC4 computers I believe to be set up identically, I installed 
mysql5 in both, the first one made the change without a hitch but the 
second refuses to install with the error message:


"./bin/mysqld: error while loading shared libraries: libstdc++.so.5: 
cannot open

shared object file: No such file or directory
Installation of system tables failed!"

Libstdc++  appears to be present?

Can anyone offer a helpful suggestion?

BobG




./configure
NOTE: This is a MySQL binary distribution. It's ready to run, you don't
need to configure it!

To help you a bit, I am now going to create the needed MySQL databases
and start the MySQL server for you.  If you run into any trouble, please
consult the MySQL manual, that you can find in the Docs directory.

Installing all prepared tables
./bin/mysqld: error while loading shared libraries: libstdc++.so.5: 
cannot open

shared object file: No such file or directory
Installation of system tables failed!

Examine the logs in ./data for more information.
You can also try to start the mysqld daemon with:
./bin/mysqld --skip-grant &
You can use the command line tool
./bin/mysql to connect to the mysql
database and look at the grant tables:

shell> ./bin/mysql -u root mysql


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

data backup

[kalin mintchev]

 hi all...

what's the best way to periodically back up mysql data?
so that databases and tables can be still usable even after a mysql upgrade?

thanks...


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

Re: Help on sql statement (not MySQL specifik)

[Rhino]

You're absolutely right that I'd need some good luck for this query to work 
for every possible data value that the table could continue.


I realized the combination of 'group by id' and 'select id, type' was not 
very good SQL - 'select id, ' would be a much more standard 
construction to go with 'group by id' - as I developed that query. But I was 
too lazy to dig through the manual to find out exactly what MySQL would do 
with that query; it worked fine for the data given. But you're right, I 
should have at least warned that this was dubious SQL before posting it. The 
original poster could easily have though that this was actually good SQL 
when it isn't.


Again, cleaning up the data (assuming it is messed up!) should be the first 
priority and any query would just be a bandaid until that is done. The query 
would probably be a lot easier if the data was clean to start with.


In any case, thanks for keeping me honest.

--
Rhino

- Original Message - 
From: "Michael Stassen" <[EMAIL PROTECTED]>

To: "Rhino" <[EMAIL PROTECTED]>
Cc: "Søren Merser" <[EMAIL PROTECTED]>; 
Sent: Sunday, March 05, 2006 2:26 PM
Subject: Re: Help on sql statement (not MySQL specifik)



Rhino wrote:
I don't really understand _why_ you want to do this but here is a query 
that gives the result you want:


select id, case type when 4 then 4 else null end as type
from Soren01
group by id;

The GROUP BY ensures that you get one row for each value of id; the case 
expression in the Select says that if the value of the type is 4, leave 
it alone, otherwise display null.


--
Rhino


Unfortunately, that won't work unless you are very lucky.  You aren't 
grouping by type, and CASE is not an aggregate function.  Mysql will use 
the value for type from the first row it finds for each id in the CASE 
statement.  The following illustrate the problem:


  DROP TABLE nu;
  CREATE TABLE nu (recno INT, id INT, type INT);
  INSERT INTO nu VALUES
  (1,1,NULL), (2,2,4), (3,2,6), (4,3,5), (5,3,4), (6,3,3);

  SELECT * FROM nu;
+---+--+--+
| recno | id   | type |
+---+--+--+
| 1 |1 | NULL |
| 2 |2 |4 |
| 3 |2 |6 |
| 4 |3 |5 |
| 5 |3 |4 |
| 6 |3 |3 |
+---+--+--+

  SELECT id, CASE type WHEN 4 THEN 4 ELSE NULL END AS type
  FROM nu
  GROUP BY id;
+--+--+
| id   | type |
+--+--+
|1 | NULL |
|2 | 4|
|3 | NULL |
+--+--+

As you can see, id=3 has a row with type=4, but it isn't found.

You could do this:

  SELECT id, IF(SUM(type=4)>0, 4, NULL) AS type FROM nu GROUP BY id;
+--+--+
| id   | type |
+--+--+
|1 | NULL |
|2 |4 |
|3 |4 |
+--+--+

but it's hard to see how that's better than your previous, simpler 
suggestion


  SELECT DISTINCT id FROM nu WHERE type = 4;

Michael


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006






--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006


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

Re: Help on sql statement (not MySQL specifik)

[Michael Stassen]

Rhino wrote:
I don't really understand _why_ you want to do this but here is a query 
that gives the result you want:


select id, case type when 4 then 4 else null end as type
from Soren01
group by id;

The GROUP BY ensures that you get one row for each value of id; the case 
expression in the Select says that if the value of the type is 4, leave 
it alone, otherwise display null.


--
Rhino


Unfortunately, that won't work unless you are very lucky.  You aren't grouping 
by type, and CASE is not an aggregate function.  Mysql will use the value for 
type from the first row it finds for each id in the CASE statement.  The 
following illustrate the problem:


  DROP TABLE nu;
  CREATE TABLE nu (recno INT, id INT, type INT);
  INSERT INTO nu VALUES
  (1,1,NULL), (2,2,4), (3,2,6), (4,3,5), (5,3,4), (6,3,3);

  SELECT * FROM nu;
+---+--+--+
| recno | id   | type |
+---+--+--+
| 1 |1 | NULL |
| 2 |2 |4 |
| 3 |2 |6 |
| 4 |3 |5 |
| 5 |3 |4 |
| 6 |3 |3 |
+---+--+--+

  SELECT id, CASE type WHEN 4 THEN 4 ELSE NULL END AS type
  FROM nu
  GROUP BY id;
+--+--+
| id   | type |
+--+--+
|1 | NULL |
|2 | 4|
|3 | NULL |
+--+--+

As you can see, id=3 has a row with type=4, but it isn't found.

You could do this:

  SELECT id, IF(SUM(type=4)>0, 4, NULL) AS type FROM nu GROUP BY id;
+--+--+
| id   | type |
+--+--+
|1 | NULL |
|2 |4 |
|3 |4 |
+--+--+

but it's hard to see how that's better than your previous, simpler suggestion

  SELECT DISTINCT id FROM nu WHERE type = 4;

Michael

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

Re: Help on sql statement (not MySQL specifik)

[Rhino]

I agree with Michael. If your data is actually messed up, the right solution 
is to fix the data and prevent it from getting messed up again FIRST. 
Writing SQL that compensates for the messed up data is often possible but 
doesn't fix the problem; you'll still need to fix the data and the sooner 
you do that, the better.


Fixing the data has a major side-benefit too: it usually makes your queries 
a lot easier and more logical. But, as Michael has said, you haven't 
explained very much about the 'Big Picture' of your situation so maybe there 
is nothing wrong with your data at all. In that case, the query I just 
posted should meet your needs.


If you _do_ have a data problem, Michael's suggestions make a lot of sense 
but if you don't understand them or if your situation is actually different 
than Michael assumes, please post again and people will try to help you.


--
Rhino


- Original Message - 
From: "Michael Stassen" <[EMAIL PROTECTED]>

To: "Søren Merser" <[EMAIL PROTECTED]>
Cc: 
Sent: Sunday, March 05, 2006 1:13 PM
Subject: Re: Help on sql statement (not MySQL specifik)



Søren Merser wrote:

Hi, I'll try

I need one record for each id in the tabel, i.e. NO duplicate id's with 
TYPE set to 4 or NULL

Now, the TYPE of  id 2 is 4 so I peserve it;
As id 2 has more than one entry I have to delete it/them
Id's with TYPE = NULL  (id 1,4,5)is kept
Id 5 (and 6) has two records, none of which has the value of 4, so one is 
preserved and TYPE set to NULL while the other should be deleted


I update the tabel between queries from another table

Regards Soren


You appear to have several, separate issues.  First, your table has 
duplicate entries, which you don't want.  If you do not want duplicate 
entries, you should not allow them.  That is, you need a UNIQUE constraint 
on the id column.  You won't be able to add one, however, until you remove 
the existing duplicates.


To remove duplicates, you first have to decide which to keep and which to 
toss. In your example, you always keep the row with the lowest recno, but 
your description implies that when one of the duplicates has type = 4, you 
want to keep that one, regardless of recno.  Assuming that to be true, you 
need something like:


  DELETE t1
  FROM yourtable t1 JOIN yourtable t2 ON t1.id = t2.id
  WHERE (t1.recno > t2.recno AND t1.type !=4)
 OR (t1.recno < t2.recno AND t2.type = 4);

(The exact syntax depends on your version of mysql.  See the manual for 
details .)


Now add a UNIQUE constraint on id so this won't ever happen again:

  ALTER TABLE yourtable ADD UNIQUE (id);

In your example, you have renumbered recno.  This is almost always a bad 
idea, but you can do it with:


  SET @i = 0;
  UPDATE yourtable SET recno = (@i := @i + 1);

Finally, now that you've fixed the table, the requested update is simple:

  UPDATE yourtable SET type = NULL WHERE type != 4;

Having said all that, I'm skeptical this is the best solution.  Perhaps it 
is just lack of imagination on my part, but I'm having trouble seeing why 
you would want to do things this way.  Having a column which should be 
unique, but isn't, and wanting to renumber your primary key column are 
both red flags.  I also find it strange that you seem to want to find the 
unique ids with type = 4, but you are changing every other type to NULL in 
the process.  Why not just select what you want, as Rhino suggested?


  SELECT DISTINCT id FROM yourtable WHERE type = 4;

Perhaps this all makes sense given the context. (You haven't told us 
much).  On the other hand, if you carefully describe what you are trying 
to accomplish, one of the many experts on the list may well be able to 
supply you with a better way.


Michael

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


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006






--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006


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

Re: Help on sql statement (not MySQL specifik)

[Rhino]

I don't really understand _why_ you want to do this but here is a query that 
gives the result you want:


select id, case type when 4 then 4 else null end as type
from Soren01
group by id;

The GROUP BY ensures that you get one row for each value of id; the case 
expression in the Select says that if the value of the type is 4, leave it 
alone, otherwise display null.


--
Rhino


- Original Message - 
From: "Søren Merser" <[EMAIL PROTECTED]>

To: ; "Rhino" <[EMAIL PROTECTED]>
Sent: Sunday, March 05, 2006 11:46 AM
Subject: Re: Help on sql statement (not MySQL specifik)



Hi, I'll try

I need one record for each id in the tabel, i.e. NO duplicate id's with 
TYPE set to 4 or NULL

Now, the TYPE of  id 2 is 4 so I peserve it;
As id 2 has more than one entry I have to delete it/them
Id's with TYPE = NULL  (id 1,4,5)is kept
Id 5 (and 6) has two records, none of which has the value of 4, so one is 
preserved and TYPE set to NULL while the other should be deleted


I update the tabel between queries from another table

Regards Soren




TABLE:
RECNO| ID   |TYPE
-
1| 1|NULL
2| 2|4<-KEEP AS IS
3| 2|6<- DROP
4| 3|NULL<- KEEP AS IS
5| 4|NULL<-KEEP AS IS
6| 5|1<-NULL
7| 5|3<-DROP
8| 6|7<-NULL
9| 6|3<-DROP

What I neede is a SQL statement that for a given value of TYPE, lets say 
4,
selects all the unique id's with TYPE = 4 when appropriate otherwise 
NULL


like:

SOLUTION:
RECNO| ID   |TYPE
-
1| 1|NULL
2| 2|4
3| 3|NULL
4| 4|NULL
5| 5|NULL
6| 6|NULL


I know this isn't  MySQL specifik but hope for someones help anyway :-)
Sorry for any inconvenience this may course users of the list

I've reread your question and example a couple of times but I'm still not 
clear on what you want but maybe this will help until you can clarify the 
question.


You use WHERE to limit your result set to only specific rows of the 
original table. Therefore, if you want only rows where the type is 4, you 
say:


Select 
from 
where type = 4;

For example, if your table name was 'mytab' and you wanted the ID column 
in the result, you'd write:


Select ID
from mytab
where type = 4;

Now, if the result showed many identical values in the columns of the 
result set and you just want to know the unique values of ID that had a 
type of 4, you add DISTINCT to the query:


select distinct ID
from mytab
where type = 4;

But that's where I get confused by your description of your problem. 
There is only one row in your sample table that has a type of 4 so 
DISTINCT isn't going to do anything for you; you'll get the same result 
with or without DISTINCT, at least with the data you've shown.


I also don't understand where the nulls come in. Do you actually want to 
update the data in your table permanently so that a null isn't null any 
longer (or a non-null value is null)? Or do you want to _display_ a null 
where something isn't null? I don't understand what your "DROP" and 
"LEAVE AS IS" remarks mean.


Can you explain more fully what you are trying to do?

--
Rhino


--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006


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




--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006






--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006


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

Re: Help on sql statement (not MySQL specifik)

[Michael Stassen]

Søren Merser wrote:

Hi, I'll try

I need one record for each id in the tabel, i.e. NO duplicate id's with 
TYPE set to 4 or NULL

Now, the TYPE of  id 2 is 4 so I peserve it;
As id 2 has more than one entry I have to delete it/them
Id's with TYPE = NULL  (id 1,4,5)is kept
Id 5 (and 6) has two records, none of which has the value of 4, so one 
is preserved and TYPE set to NULL while the other should be deleted


I update the tabel between queries from another table

Regards Soren


You appear to have several, separate issues.  First, your table has duplicate 
entries, which you don't want.  If you do not want duplicate entries, you should 
not allow them.  That is, you need a UNIQUE constraint on the id column.  You 
won't be able to add one, however, until you remove the existing duplicates.


To remove duplicates, you first have to decide which to keep and which to toss. 
 In your example, you always keep the row with the lowest recno, but your 
description implies that when one of the duplicates has type = 4, you want to 
keep that one, regardless of recno.  Assuming that to be true, you need 
something like:


  DELETE t1
  FROM yourtable t1 JOIN yourtable t2 ON t1.id = t2.id
  WHERE (t1.recno > t2.recno AND t1.type !=4)
 OR (t1.recno < t2.recno AND t2.type = 4);

(The exact syntax depends on your version of mysql.  See the manual for details 
.)


Now add a UNIQUE constraint on id so this won't ever happen again:

  ALTER TABLE yourtable ADD UNIQUE (id);

In your example, you have renumbered recno.  This is almost always a bad idea, 
but you can do it with:


  SET @i = 0;
  UPDATE yourtable SET recno = (@i := @i + 1);

Finally, now that you've fixed the table, the requested update is simple:

  UPDATE yourtable SET type = NULL WHERE type != 4;

Having said all that, I'm skeptical this is the best solution.  Perhaps it is 
just lack of imagination on my part, but I'm having trouble seeing why you would 
want to do things this way.  Having a column which should be unique, but isn't, 
and wanting to renumber your primary key column are both red flags.  I also find 
it strange that you seem to want to find the unique ids with type = 4, but you 
are changing every other type to NULL in the process.  Why not just select what 
you want, as Rhino suggested?


  SELECT DISTINCT id FROM yourtable WHERE type = 4;

Perhaps this all makes sense given the context. (You haven't told us much).  On 
the other hand, if you carefully describe what you are trying to accomplish, one 
of the many experts on the list may well be able to supply you with a better way.


Michael

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

Re: Logging (wrong) passwords by mysqld

[Manuel Schmitt (manitu)]

> is there a reason that the unknown passwords can't simply be reset? 

yes, because not all clients are currently known and resetting them
would possibly break the application(s)
-- 


Manuel Schmitt
- Geschäftsführer -

manitu  [EMAIL PROTECTED]
Welvertstraße 2http://www.manitu.de/
66606 St. Wendel   Telefon: +49-(0)6851-99808-20
   Telefax: +49-(0)6851-99808-99
  PGP-Key-ID: 0x3E486E93

Unser Impressum finden Sie unter http://www.manitu.de/impressum/

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

Re: Logging (wrong) passwords by mysqld

[Manuel Schmitt (manitu)]

> having an application log actual passwords (whether the login was
> successful or not) is a major security risk, hence no self-respecting,
> security-conscious application will do this. all that should be logged
> is the username attempted, along with a login success/failure
> indication, never the password.

Yes, I know and you're right.

The problem: A customer which has currently no way to find out if he/she
still uses some old passwords for one and the same user. So I would like
to do that by mysql.
-- 


Manuel Schmitt
- Geschäftsführer -

manitu  [EMAIL PROTECTED]
Welvertstraße 2http://www.manitu.de/
66606 St. Wendel   Telefon: +49-(0)6851-99808-20
   Telefax: +49-(0)6851-99808-99
  PGP-Key-ID: 0x3E486E93

Unser Impressum finden Sie unter http://www.manitu.de/impressum/

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

Can I use mySQL Administrator on server version 3.23.58

[Neil Tompkins]

I am running mySQL database version 3.23.58, but I'm using mySQL 
Administrator 1.1.9 for scheduled backups. When connecting to the database I 
get a message saying that this version of database is not supported and I 
might get unexpected behaviour.


Am I OK to use this version of mySQL Administrator to backup my database and 
what unexpected behaviour can I expect ?


Thanks
Neil



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

Logging (wrong) passwords by mysqld

[Manuel Schmitt (manitu)]

Hi,

I'am searching for a way to have mysqld log all passwords which clients
are using ("trying") while connecting.

As to the documentation and to my trials neither the error log nor the
general query log contain passwords, only the usernames.

I already tried to get them via ethereal, but this should not work with
encrypted connections.

Any help would be appreciated.

Thanks
Manuel

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

Re: Help on sql statement (not MySQL specifik)

[Søren Merser]

Hi, I'll try

I need one record for each id in the tabel, i.e. NO duplicate id's with TYPE 
set to 4 or NULL

Now, the TYPE of  id 2 is 4 so I peserve it;
As id 2 has more than one entry I have to delete it/them
Id's with TYPE = NULL  (id 1,4,5)is kept
Id 5 (and 6) has two records, none of which has the value of 4, so one is 
preserved and TYPE set to NULL while the other should be deleted


I update the tabel between queries from another table

Regards Soren




TABLE:
RECNO| ID   |TYPE
-
1| 1|NULL
2| 2|4<-KEEP AS IS
3| 2|6<- DROP
4| 3|NULL<- KEEP AS IS
5| 4|NULL<-KEEP AS IS
6| 5|1<-NULL
7| 5|3<-DROP
8| 6|7<-NULL
9| 6|3<-DROP

What I neede is a SQL statement that for a given value of TYPE, lets say 
4,

selects all the unique id's with TYPE = 4 when appropriate otherwise NULL

like:

SOLUTION:
RECNO| ID   |TYPE
-
1| 1|NULL
2| 2|4
3| 3|NULL
4| 4|NULL
5| 5|NULL
6| 6|NULL


I know this isn't  MySQL specifik but hope for someones help anyway :-)
Sorry for any inconvenience this may course users of the list

I've reread your question and example a couple of times but I'm still not 
clear on what you want but maybe this will help until you can clarify the 
question.


You use WHERE to limit your result set to only specific rows of the 
original table. Therefore, if you want only rows where the type is 4, you 
say:


Select 
from 
where type = 4;

For example, if your table name was 'mytab' and you wanted the ID column 
in the result, you'd write:


Select ID
from mytab
where type = 4;

Now, if the result showed many identical values in the columns of the 
result set and you just want to know the unique values of ID that had a 
type of 4, you add DISTINCT to the query:


select distinct ID
from mytab
where type = 4;

But that's where I get confused by your description of your problem. There 
is only one row in your sample table that has a type of 4 so DISTINCT 
isn't going to do anything for you; you'll get the same result with or 
without DISTINCT, at least with the data you've shown.


I also don't understand where the nulls come in. Do you actually want to 
update the data in your table permanently so that a null isn't null any 
longer (or a non-null value is null)? Or do you want to _display_ a null 
where something isn't null? I don't understand what your "DROP" and "LEAVE 
AS IS" remarks mean.


Can you explain more fully what you are trying to do?

--
Rhino


--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006


--
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: Help on sql statement (not MySQL specifik)

[Ronald J. Yacketta]

I think he wants to update rows where != 4 to null

that is, update TYPE setting TYPE to null where TYPE  != 4

-Ron
Rhino wrote:



- Original Message - From: "Søren Merser" <[EMAIL PROTECTED]>
To: 
Sent: Sunday, March 05, 2006 9:12 AM
Subject: Help on sql statement (not MySQL specifik)



Hi

Could someone please help me out here?

TABLE:
RECNO| ID   |TYPE
-
1| 1|NULL
2| 2|4<-KEEP AS IS
3| 2|6<- DROP
4| 3|NULL<- KEEP AS IS
5| 4|NULL<-KEEP AS IS
6| 5|1<-NULL
7| 5|3<-DROP
8| 6|7<-NULL
9| 6|3<-DROP

What I neede is a SQL statement that for a given value of TYPE, lets 
say 4,
selects all the unique id's with TYPE = 4 when appropriate otherwise 
NULL


like:

SOLUTION:
RECNO| ID   |TYPE
-
1| 1|NULL
2| 2|4
3| 3|NULL
4| 4|NULL
5| 5|NULL
6| 6|NULL


I know this isn't  MySQL specifik but hope for someones help anyway :-)
Sorry for any inconvenience this may course users of the list

I've reread your question and example a couple of times but I'm still 
not clear on what you want but maybe this will help until you can 
clarify the question.


You use WHERE to limit your result set to only specific rows of the 
original table. Therefore, if you want only rows where the type is 4, 
you say:


Select 
from 
where type = 4;

For example, if your table name was 'mytab' and you wanted the ID 
column in the result, you'd write:


Select ID
from mytab
where type = 4;

Now, if the result showed many identical values in the columns of the 
result set and you just want to know the unique values of ID that had 
a type of 4, you add DISTINCT to the query:


select distinct ID
from mytab
where type = 4;

But that's where I get confused by your description of your problem. 
There is only one row in your sample table that has a type of 4 so 
DISTINCT isn't going to do anything for you; you'll get the same 
result with or without DISTINCT, at least with the data you've shown.


I also don't understand where the nulls come in. Do you actually want 
to update the data in your table permanently so that a null isn't null 
any longer (or a non-null value is null)? Or do you want to _display_ 
a null where something isn't null? I don't understand what your "DROP" 
and "LEAVE AS IS" remarks mean.


Can you explain more fully what you are trying to do?

--
Rhino





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

Re: Help on sql statement (not MySQL specifik)

[Rhino]

- Original Message - 
From: "Søren Merser" <[EMAIL PROTECTED]>

To: 
Sent: Sunday, March 05, 2006 9:12 AM
Subject: Help on sql statement (not MySQL specifik)



Hi

Could someone please help me out here?

TABLE:
RECNO| ID   |TYPE
-
1| 1|NULL
2| 2|4<-KEEP AS IS
3| 2|6<- DROP
4| 3|NULL<- KEEP AS IS
5| 4|NULL<-KEEP AS IS
6| 5|1<-NULL
7| 5|3<-DROP
8| 6|7<-NULL
9| 6|3<-DROP

What I neede is a SQL statement that for a given value of TYPE, lets say 
4,

selects all the unique id's with TYPE = 4 when appropriate otherwise NULL

like:

SOLUTION:
RECNO| ID   |TYPE
-
1| 1|NULL
2| 2|4
3| 3|NULL
4| 4|NULL
5| 5|NULL
6| 6|NULL


I know this isn't  MySQL specifik but hope for someones help anyway :-)
Sorry for any inconvenience this may course users of the list

I've reread your question and example a couple of times but I'm still not 
clear on what you want but maybe this will help until you can clarify the 
question.


You use WHERE to limit your result set to only specific rows of the original 
table. Therefore, if you want only rows where the type is 4, you say:


Select 
from 
where type = 4;

For example, if your table name was 'mytab' and you wanted the ID column in 
the result, you'd write:


Select ID
from mytab
where type = 4;

Now, if the result showed many identical values in the columns of the result 
set and you just want to know the unique values of ID that had a type of 4, 
you add DISTINCT to the query:


select distinct ID
from mytab
where type = 4;

But that's where I get confused by your description of your problem. There 
is only one row in your sample table that has a type of 4 so DISTINCT isn't 
going to do anything for you; you'll get the same result with or without 
DISTINCT, at least with the data you've shown.


I also don't understand where the nulls come in. Do you actually want to 
update the data in your table permanently so that a null isn't null any 
longer (or a non-null value is null)? Or do you want to _display_ a null 
where something isn't null? I don't understand what your "DROP" and "LEAVE 
AS IS" remarks mean.


Can you explain more fully what you are trying to do?

--
Rhino 




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.2/274 - Release Date: 03/03/2006


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

is this safe against sql-injection?

[Jochen Kaechelin]

Can somebody give me some general hints how to prevent
sql-injection?

I always go this way to build my queries:

function clean_mysql_string($string) {
$clean_string = stripslashes($string);
$clean_string = htmlentities(strip_tags(($clean_string)));
$clean_string = trim($clean_string);
$clean_string = rtrim($clean_string);
$clean_string = mysql_real_escape_string($clean_string);
return($clean_string);
}

$searchstring = clean_mysql_string($_POST["searchstring"]);

$query = "  SELECT id,uname,nickname, MATCH(uname,nickname) 

AGAINST('$searchstring' IN BOOLEAN MODE) AS mtch
FROM wlh_accounts
HAVING mtch > 0.001
ORDER BY mtch DESC";

$results = mysql_query($query);

while ($row = mysql_fetch_array($results, MYSQL_ASSOC)) {
$values[] = array (
"id"  => $row["id"],
"uname"   => $row["uname"],
"nickname"=> $row["nickname"],
"mtch"=> $row["mtch"]
);
}

Is this safe??

-- 
Jochen Kaechelin, fvgi242ss, wlanhacking.de
http://mail.wlanhacking.de/cgi-bin/mailman/listinfo

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

Help on sql statement (not MySQL specifik)

[Søren Merser]

Hi

Could someone please help me out here?

TABLE:
RECNO| ID   |TYPE
-
1| 1|NULL
2| 2|4<-KEEP AS IS
3| 2|6<- DROP
4| 3|NULL<- KEEP AS IS
5| 4|NULL<-KEEP AS IS
6| 5|1<-NULL
7| 5|3<-DROP
8| 6|7<-NULL
9| 6|3<-DROP

What I neede is a SQL statement that for a given value of TYPE, lets say 4,
selects all the unique id's with TYPE = 4 when appropriate otherwise NULL

like:

SOLUTION:
RECNO| ID   |TYPE
-
1| 1|NULL
2| 2|4
3| 3|NULL
4| 4|NULL
5| 5|NULL
6| 6|NULL


I know this isn't  MySQL specifik but hope for someones help anyway :-)
Sorry for any inconvenience this may course users of the list
Regards Soren


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