Re: last_insert_id

[Steve Edberg]

At 11:13 AM -0500 12/27/09, you wrote:

Hi;

mysql select * from products;
++--+--+---++-+---++--++-+--+---+--+---+---++--+--+--+--+-+--+
| ID | SKU  | Category | Name  | Title  | Description | Price |
SortFactor | Availability | OutOfStock | ShipFlatFee | ShipPercentPrice |
ShipPercentWeight | Associations | TempPrice | LastDatePrice | Weight |
Metal| PercentMetal | pic0 | pic1 | sizes   |
colorsShadesNumbersShort |
++--+--+---++-+---++--++-+--+---+--+---+---++--+--+--+--+-+--+
|  1 | prodSKU1 | prodCat1 | name1 | title1 | desc| 12.34 |
500 |1 |  0 |   10.00 |5
| 2 |  | 1 | 2000-01-01|   2.50 |
14k gold |   20 | NULL | NULL | Extra-small
|  |
++--+--+---++-+---++--++-+--+---+--+---+---++--+--+--+--+-+--+
1 row in set (0.00 sec)

mysql select last_insert_id() from products;
+--+
| last_insert_id() |
+--+
|0 |
+--+
1 row in set (0.00 sec)

mysql

Now, I was expecting 1, not 0! What up?
TIA,
Victor



The normal procedure would be to:

insert into products values (null, 'prodsku2',...);
select last_insert_id();

(assuming ID is your autoincremented field). Do the select 
last_insert_id() immediately after your insert, and it is guaranteed 
to give you the ID of the record you just inserted, regardless of 
what inserts may be happening in other sessions (and if the insert 
was not successful, it will return 0).


If you want to get the highest ID that has been inserted regardless 
of session or without doing an insert first, you could do a select 
max(ID). Depending on your overall database design, this may or may 
not give you what you want. Eg:


(1) you can explicitly specify a value for an autoincrement field 
(eg, insert into products values (1000,'prodsku3'...), which could 
leave a gap. However, the next autoincrement value in this case would 
be 1001 and is probably what you want.


(2) autoincrement values are not reused after deletion, so if you 
deleted the record with ID=1000 inserted in (1), the next 
autoincrement would still be 1001, even if the existing records are 
IDs 1,2,3. This is usually the desired behavior, but again, may not 
be what *you* need.


I'd recommend spending some time reading the documentation for 
autoincrement fields and the last_insert_id() function.


- sbe -




--
++
| Steve Edberg  edb...@edberg-online.com |
| Programming/Database/SysAdminhttp://www.edberg-online.com/ |
++

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

Re: Mysql version of database link?

[Steve Edberg]

At 12:16 PM -0600 12/7/09, Bryan Cantwell wrote:
Does anyone have a suggestion on how a database link (like in 
Oracle) could be established between two Mysql databases on 
different servers?
It would be awesome if I could write sql that will query both 
databases in one query...



You are probably looking for federated tables:

http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html

And just for completeness, one can query multiple databases on the 
same server using the database.table.column syntax, eg:


select db1.table1.column1 as a, db2.table2.column2 as b;

One could also replicate a remote database to your server, and use 
the db.table.column notation:


http://dev.mysql.com/doc/refman/5.0/en/replication.html

I'm guessing the latter two options are not what you are asking for, 
but I include for completeness.


DISCLAIMER: I haven't used federated tables yet...

- steve

--
++
| Steve Edberg  edb...@edberg-online.com |
| Programming/Database/SysAdminhttp://www.edberg-online.com/ |
++

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

Re: Select Problem

[Steve Edberg]

At 1:26 PM -0500 12/6/09, Victor Subervi wrote:

Hi;
I have the following:

mysql select * from categoriesProducts as c inner join relationshipProducts
as r on c.ID = r.Child inner join categoriesProducts as p on r.Parent = p.ID
where p.Category = prodCat2;
ERROR 1054 (42S22): Unknown column 'prodCat2' in 'where clause'
mysql describe categoriesProducts;
+--+-+--+-+-++
| Field| Type| Null | Key | Default | Extra  |
+--+-+--+-+-++
| ID   | int(3) unsigned | NO   | PRI | NULL| auto_increment |
| Category | varchar(40) | YES  | | NULL||
| Parent   | varchar(40) | YES  | | NULL||
+--+-+--+-+-++
3 rows in set (0.00 sec)

mysql select * from categoriesProducts;
++--++
| ID | Category | Parent |
++--++
|  1 | prodCat1 | None   |
|  2 | prodCat2 | None   |
++--++
2 rows in set (0.00 sec)

So I'm at a loss. No, 'prodCat2' isn't a column, but I don't understand how
I specified that in my query. Please advise.
TIA,
Victor



You didn't quote prodCat2 in the query, so it was assuming you were 
referring to the column name. Try:


select * from categoriesProducts as c inner join relationshipProducts
as r on c.ID = r.Child inner join categoriesProducts as p on r.Parent = p.ID
where p.Category = 'prodCat2';

- s

--
++
| Steve Edberg  edb...@edberg-online.com |
| Programming/Database/SysAdminhttp://www.edberg-online.com/ |
++

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

Re: Alphabetical search to and from

[Steve Edberg]

At 11:52 PM +0900 11/4/09, Dave M G wrote:

MySQL,

This should be a fairly simple question.

I have a table with a bunch of people's names. I want to find people
who's name begins within a certain range of characters.

All names between F and P, for example.

What SELECT statement would I use to do that?

Thank you for any advice.

--
Dave M G



And to add a few more possibilities:

select * from your_table where name = 'F' and name  'Q'

	select * from your_table where left(name, 1) in 
('f','g','h','i','j','k','l','m','n','o','p')


I wouldn't recommend the latter, but might be handy if you were 
dealing with something more complex than a simple range.


- steve

--
++
| Steve Edberg  edb...@edberg-online.com |
| Programming/Database/SysAdminhttp://www.edberg-online.com/ |
++

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

Re: results of the query as a table

[Steve Edberg]

At 2:59 PM + 10/27/09, Olga Lyashevska wrote:

Dear all,

I run a query and I try to save results as a table. On the results 
of this first query I want to run yet another query (perhaps a few).
I have been trying to use CREATE VIEW statement, which works fine, 
except for the fact that fields are not indexed because as I 
understand it indices cannot be created on views. It really affects 
the performance, making it nearly impossible to run any further 
queries.


I am aware that it is a rather trivial problem, but still I did not 
manage to find a solution which would meet my requirements.


So my question is: are there any other possibilities to save results 
of the query as a table so that they will be re-used to run yet 
another query?


Thanks in advance,
Olga



CREATE TABLE ... SELECT

should do what you want. For example

	CREATE TABLE foo SELECT thing1,thing2,concat(thing3,thing4) 
as thing5 from bar where thing4 like 'baz%' order by thing1 desc


You could create a TEMPORARY table if needed (CREATE TEMPORARY 
TABLE...). Assuming version 5.0:


http://dev.mysql.com/doc/refman/5.0/en/create-table.html

- steve

--
++
| Steve Edberg  edb...@edberg-online.com |
| Programming/Database/SysAdminhttp://www.edberg-online.com/ |
++

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

Re: results of the query as a table

[Steve Edberg]

At 3:29 PM + 10/27/09, Olga Lyashevska wrote:

On 27.10.2009, at 15:11, Steve Edberg wrote:


At 2:59 PM + 10/27/09, Olga Lyashevska wrote:

Dear all,

I run a query and I try to save results as a table. On the results 
of this first query I want to run yet another query (perhaps a 
few).
I have been trying to use CREATE VIEW statement, which works fine, 
except for the fact that fields are not indexed because as I 
understand it indices cannot be created on views. It really 
affects the performance, making it nearly impossible to run any 
further queries.


I am aware that it is a rather trivial problem, but still I did 
not manage to find a solution which would meet my requirements.


So my question is: are there any other possibilities to save 
results of the query as a table so that they will be re-used to 
run yet another query?


Thanks in advance,
Olga



CREATE TABLE ... SELECT

should do what you want. For example

	CREATE TABLE foo SELECT thing1,thing2,concat(thing3,thing4) 
as thing5 from bar where thing4 like 'baz%' order by thing1 desc


You could create a TEMPORARY table if needed (CREATE TEMPORARY 
TABLE...). Assuming version 5.0:


Thanks Steve. It is solved! Shall I add indices manually to speed up query?




It would probably help, yes. As it mentions near the bottom of the 
CREATE TABLE documentation page, you can override column definitions 
and create indexes in the same statement, something like:


	CREATE TABLE foo (a TINYINT NOT NULL),  c, unique(c) SELECT 
b+1 AS a, c FROM bar;


(never tried that myself). Or you could do an ALTER TABLE afterwards 
to add appropriate indexes. And are you familiar with the EXPLAIN 
command to help optimize queries/decide what indexes to add?


http://dev.mysql.com/doc/refman/5.0/en/create-table.html
http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

http://dev.mysql.com/doc/refman/5.0/en/query-speed.html
http://dev.mysql.com/doc/refman/5.0/en/explain.html

- steve

--
++
| Steve Edberg  edb...@edberg-online.com |
| Programming/Database/SysAdminhttp://www.edberg-online.com/ |
++

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

Re: Creation date

[Steve Edberg]

At 10:28 AM -0400 5/5/09, Jerry Schwartz wrote:

At the risk of getting spanked for not finding this in the documentation,
I'm asking a simple question:



Can I tell when a table was created?



Try
show table status
or
select table_name,create_time from information_schema.tables

(information_schema only exists in MySQL = 5.0, methinks). Manual references:

http://dev.mysql.com/doc/refman/5.0/en/show-table-status.html

http://dev.mysql.com/doc/refman/5.0/en/information-schema.html


- steve

--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Centersbedb...@ucdavis.edu |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

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

Re: Auto increment?

[Steve Edberg]

At 9:35 AM +0200 4/2/09, Andreas Pardeike wrote:

Hi,

I have a table 'test'

+-+--+--+-+---++
| Field   | Type | Null | Key | Default   | Extra  |
+-+--+--+-+---++
| id  | int(11)  | NO   | PRI | NULL  | auto_increment |
| foo | varchar(255) | NO   | | NULL  
||
| bar | varchar(255) | NO   | | NULL  
||
| payload | longblob | YES  | | NULL  
||

+-+--+--+-+---++

where 'payload' size usually is quite big!

Now I want to manipulate a number of rows in a non-trivial way and at the
same time set 'id' for those rows to a new number in the sequence. I.e.

UPDATE test SET id = NEXT_INSERT_ID(), foo = some_function(foo) WHERE bar ...

My motivation is that this table has several consumers in other applications
that keep track of the biggest 'id' they have seen between polls and thus
can keep track of new and changed rows.

Right now, I solve this problem by copying the rows to a temporary table,
then delete them and insert them from the temporary table but this moves my
huge payload around which I really want to avoid.

How can I solve this in a better way?

/Andreas Pardeike



Add a column of type timestamp which, by default, will be updated 
every time a record is inserted or updated. Then the other 
applications can simply select records with timestamp  
last_poll_time.


steve

--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Centersbedb...@ucdavis.edu |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

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

Re: Possible to get better error handling for invalid enum parameter to stored program?

[Steve Edberg]

If you want to keep the enum column, you can set the strict SQL mode:

mysql show variables like 'SQL_MODE';
+---+---+
| Variable_name | Value |
+---+---+
| sql_mode  |   |
+---+---+
1 row in set (0.00 sec)

mysql create table test2 (test enum('foo','bar'));
Query OK, 0 rows affected (0.01 sec)

mysql insert into test2 values('baz');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql show warnings;
+-+--+---+
| Level   | Code | Message   |
+-+--+---+
| Warning | 1265 | Data truncated for column 'test' at row 1 |
+-+--+---+
1 row in set (0.00 sec)

mysql set session sql_mode='traditional';
Query OK, 0 rows affected (0.00 sec)

mysql insert into test2 values('bloop');
ERROR 1265 (01000): Data truncated for column 'test' at row 1
mysql select * from test2;
+--+
| test |
+--+
|  |
+--+
1 row in set (0.00 sec)

I'm running v5.0.51a; as you can see, the first invalid value was 
truncated to '' with a warning, the second caused an error and did 
not insert. I don't know what version you're running, perhaps this 
does not apply to you. For more info:


http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
http://dev.mysql.com/doc/refman/5.0/en/enum.html

- steve edberg


At 2:00 PM -0800 3/6/09, David Karr wrote:

Thanks.  I thought that was the case, but I wanted to be sure.

On Fri, Mar 6, 2009 at 12:07 PM, Perrin Harkins per...@elem.com wrote:


 I'm afraid enums are useless for anything except compressing your
 data, since they don't reject bad values.  If you want to limit a
 field to a set of values, you need to use a lookup table and a foreign
 key constraint.

 - Perrin

 On Fri, Mar 6, 2009 at 1:35 PM, David Karr davidmichaelk...@gmail.com
 wrote:
  If I define an enum parameter for a stored program, and the calling code
  sends an invalid value, they get the less than useful data truncated
  error.  Is it possible to define the stored program to produce better
 error
  handling for that kind of error?
 
  This is probably a FAQ, but in general, it appears that error diagnostics
 in
  stored programs are very primitive. Are there any plans in a roadmap to
  improve this?
 




--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Centersbedb...@ucdavis.edu |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

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

Re: How to disable some of the mysql query?

[Steve Edberg]

At 1:07 AM +0800 11/29/08, Wayne wrote:

for example,one user has the 'select' right on talbe 'test'(innodb,million
records),however,he sometimes execute query like 'select * from test'.That
will slow down the whole database.
Is there a way to disable some queries for some users? Or,mysql's authority
can be more detailed?
thx



As far as I know, you can't impose a limit on max number of records 
returned/hour. You can limit number of queries or updates or 
connections per hour:


http://dev.mysql.com/doc/refman/5.0/en/user-resources.html

It doesn't appear this has changed in MySQL 6. Depending on your 
server setup, you could implement some sort of per-user bandwidth 
limit outside of MySQL (perhaps imposing limits only on port 3306 
traffic using a packet shaper/traffic shaper), or activate, monitor  
parse the query log


http://dev.mysql.com/doc/refman/5.0/en/query-log.html

or at least the slow query log.

On an active server, I would imagine the overhead of a query log and 
then parsing it (eg; tail -f /path/to/log | grep ...) would be 
significant. If MySQL allows you to log directly to a Unix pipe (eg 
query_log = | some_program_that_monitors_activity) that might 
reduce the load sufficiently.


steve

--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

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

Re: Table encryption

[Steve Edberg]

At 2:41 PM -0800 11/21/08, Zakai Kinan wrote:
I searched google and the archives of this list, but I am not 
cleared about the support of table encryption in mysql.  Can someone 
clarify for me?


thanks,



Well, as far as I know, there isn't any built-in full table 
encryption implemented or in the works [1]. However, you could use 
MySQL's encryption functions [2] to encrypt selected columns (you 
could even do that automatically via triggers or stored procedures).


If you wanted fully transparent encryption, you could use an 
encrypted volume, for example via Truecrypt [3] or the upcoming 
ZFS+crypto [4] to store your tables. To minimize the performance 
impact, you should keep your index files on a non-encrypted volume if 
possible.


steve

[1]
http://dev.mysql.com/doc/refman/5.1/en/roadmap.html
Also couldn't find anything on the mysql-internals list 
(http://marc.info/?l=mysql-internals)



[2]
http://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html

[3]
http://www.truecrypt.org/

[4]
http://opensolaris.org/os/project/zfs-crypto/

--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

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

Re: Comma's in data?

[Steve Edberg]

At 11:52 PM -0800 3/7/08, J. Todd Slack wrote:

Hi All,

I have a client that wants to insert data into a VarChar field that contains
commas. These are property addresses.

Example:
2966 Moorpark Ave, San Jose, CA, 95128

1 Infinite Loop, Cupertino, CA, 95

How can I allow this?

Thanks!
-Jason



I've never had to escape commas; only special characters, eg: '_%. 
MySQL seems to ignore escaping if the following character is not 
special, though:




[EMAIL PROTECTED] create table test (t1 varchar(255));
Query OK, 0 rows affected (0.03 sec)

[EMAIL PROTECTED] insert into test values ('qwert'), 
('qwe,rt'),('qwe\,rt');

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

[EMAIL PROTECTED] select * from test;
++
| t1 |
++
| qwert  |
| qwe,rt |
| qwe,rt |
++
3 rows in set (0.00 sec)



Are you getting an error when you insert a row? If so, what is the 
error? Perhaps you are having a character set issue. This is what 
mine looks like:




[EMAIL PROTECTED] show variables like 'character\_set\_%';
+--++
| Variable_name| Value  |
+--++
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database   | latin1 |
| character_set_results| latin1 |
| character_set_server | latin1 |
| character_set_system | utf8   |
+--++
6 rows in set (0.00 sec)


steve


--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

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

Re: trouble selecting DB

[Steve Edberg]

At 12:26 PM -0400 9/14/07, Martin Gainty wrote:

Morning All-

I connect to Mysql 4.0.13 using mysql_connect AND returned handle is not false

then I call access to mysql_select_db which always returns NULL and 
causes Exception


I also tried mysql_connect with supplied 4th param as DB with no joy

The DB name is uppercase..does this matter?



It appears that you are using PHP, but I don't think that's relevant here.

Case sensitivity is a bit complex - see

   http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html

- but it's generally best to match case, for portability if nothing 
else. It could well be your problem if you are referring to the 
database 'FooBar' as 'FOOBAR' and MySQL is running on a *nix platform.


steve


--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

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

Re: Seeing Table Structure

[Steve Edberg]

, thus grabbing the table structures. I can´t find in the
 documentation how to do either of those. Please advise.
 TIA,
 Tony
 
 AOL now offers free email to everyone.  Find out more about
what's free from

  AOL at AOL.com.





--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

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

Re: i know your name

[Steve Edberg]

At 8:37 AM +0200 7/12/07, Olav Mørkrid wrote:

say you want to keep track of who knows whose name at a party, storing
one table row per instance explodes into n*(n-1) rows (a million rows
for thousand people).

a) can mysql cope with this, and it's more a question of storage and
processing power?

or b) does such a table spell certain doom for a database? if so, how
does one solve this problem efficiently?




Well, one would assume not everyone knows everyone else. So you have
a People table (1,000 records in your example) and a Friends table
that looks something like
PersonId
FriendId

both of which are foreign keys pointing to the People table (which
would normally have an autoincremented primary key). Perhaps you
could include a 'quality of friendship' column as well. Even if you
had a party of 1,000 people where everyone knew everyone, a table of
1 million records is pretty reasonable. It all depends on your query
 index design (make friends with the EXPLAIN command). If you go
through the mailing list archives, you'll find numerous people with
multiple tables with billions of records.

steve

--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

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

Re: Problem about fulltext search.

[Steve Edberg]

At 11:23 PM +0800 6/30/07, Niu Kun wrote:

Dear all,

I'm planning to add fulltext search to my database.
I've got the following test command:
create table test(id int, name varchar(20));
alter table test add fulltext(name);
insert into test values(1,hello world);
insert into test values(1,hello);

When I execute the fulltext search command, I've got the following.
mysql select * from test where match(name) against(hello);
Empty set (0.00 sec)

Would anyone be kind enough to tell me how I can find hello in my text?
Any help would be appreciated.
Thanks in advance.



To quote from

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html


... words that are present in more than 50% of the rows are 
considered common and do not match.


'hello' appears in both (100%) of your records above, so it will not 
match. You need to insert more test data before MySQL has enough 
words to compute valid relevances.


steve

--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

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

Re: duplicate key question

[Steve Edberg]

At 10:19 PM -0500 6/22/07, Chris W wrote:
when you get a duplicate key error it says something to the effect 
of Duplicate entry 'xyz' for key x

What I want to know is how to find out what table fields are part of key x?



'show index' is what you want, I think:

http://dev.mysql.com/doc/refman/5.0/en/show-index.html

Example:

[EMAIL PROTECTED] create table test (c1 int, c2 int, index 
i1 (c1,c2));

Query OK, 0 rows affected (0.02 sec)

[EMAIL PROTECTED] show index from test\G
*** 1. row ***
   Table: test
  Non_unique: 1
Key_name: i1
Seq_in_index: 1
 Column_name: c1
   Collation: A
 Cardinality: NULL
Sub_part: NULL
  Packed: NULL
Null: YES
  Index_type: BTREE
 Comment:
*** 2. row ***
   Table: test
  Non_unique: 1
Key_name: i1
Seq_in_index: 2
 Column_name: c2
   Collation: A
 Cardinality: NULL
Sub_part: NULL
  Packed: NULL
Null: YES
  Index_type: BTREE
 Comment:
2 rows in set (0.00 sec)

steve



--
Chris W
KE5GIX

Protect your digital freedom and privacy, eliminate DRM, learn more 
at http://www.defectivebydesign.org/what_is_drm;


Gift Giving Made Easy
Get the gifts you want  give the gifts they want
One stop wish list for any gift, from anywhere, for any occasion!
http://thewishzone.com



--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

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

Re: Blob data

[Steve Edberg]

At 12:11 PM +0530 6/22/07, Ratheesh K J wrote:

Hello All,

I want a clarification. Whe run a forum wherein people send messages 
with/without attachments. Attachments may contain images, documents 
etc.. We are actually storing the attachment in a blob column. 
Sometimes the attachments are big. And today the table size has 
grown to 40 GB. This has created a headache for any maintanance 
task, backup, restoration. etc.


I want to know whether this is the right approach. Or should we 
actually store the attachments in directories and just stiore the 
attachment path in the database.


Kindly suggest the best approach so that I can reduce the database size.

Thanks in advance



Yes, storing files - especially non-textual files - in the file 
system instead of the database is generally considered the best 
practice.


At one point I had created a document management system that stored 
everything in the database as you are doing; my rationale was that it 
allowed me to manage permissions using the existing database 
permissions, and to back up the whole database using mysqldump, vs 
mysqldump + doing a tar of the files. However, I abandoned this 
approach for the following reasons:


(1) Storing non-plaintext items (eg; pictures) in the database makes 
it bigger and slower without added value - you can't (at least not 
yet, or in the foreseeable future) do a meaningful search on a blob.


(2) It becomes more difficult to split storage out onto multiple 
filesystems; eg, leaving the database files in /var/database, putting 
the documents themselves into /home/docmanager, etc.


(3) It makes queries on the commandline unwieldy; if you have a blob 
field, doing a select * to check a record's contents can dump a lot 
of garbage on the screen.


(4) It can make doing incremental backups more difficult; if the 
documents themselves are relatively static, but the document metadata 
stored in the database is very dynamic, it becomes simple to do a 
compact daily database dump + a weekly document directory backup (for 
example) if the files are not in the database.


What I do is create a unique SHA1 hash when a file is uploaded (eg; 
sha1(rand()). The original filename and the 40-character hash are 
stored in the database, and the document is stored in the filesystem 
using the hash as the filename. I can optionally compress and encrypt 
the document as well, storing the encryption key in the database. 
This gives (for me) adequate document security. An additional 
advantage is that you can take advantage of the filesystem tree if 
you have a large number of documents. For example, if a document hash 
is 'f0118e9bd2c4fb29c64ee03abce698b8', you can store the file in the 
directory tree f0/11/8e/f0118e9bd2c4fb29c64ee03abce698b8 (extending 
to as many levels as you feel necessary). By keeping the number of 
files per directory fairly small, file retrieval becomes relatively 
fast. As the hashes approximate a random distribution, you should 
always have a close-to-balanced tree.


Lastly, I store a hash of the document itself in the database as 
well. This allows me to detect if duplicate files are uploaded, and 
to determine if a previously-uploaded file has been corrupted in some 
way.


steve

--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

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

Re: corrupted tables

[Steve Edberg]

At 6:56 PM +0200 3/16/07, Octavian Rasnita wrote:

Hi,

Sometimes I see that some tables from my database get corrupted. Why 
does this happpen and how can I avoid it?
It is not hard to go and use repair table but it seems that in 
this way some records could be deleted and this is not ok.
If I want to have a very secure database, can I use MySQL? I hope 
the answer won't be that I need to make backups regularily.





You'll have to give us some more information...at least:

* What MySQL version, OS platform, and file system used for database?
* Does this happen at a regular time, or apparently randomly?
* Does this happen to the same tables all the time, or is that random as well?
* Is this a precompiled binary from MySQL or did you build it yourself?

I could see that if you compiled it yourself against some buggy 
libraries you could have problems; perhaps a cronjob is doing some 
copy/restore process on the underlying files without shutting mysql 
down or flushing logs; perhaps a lot of things...more information is 
needed.


It has been my experience (on Windows NT, Solaris and Linux 
platforms) that MySQL  has been one of the more reliable programs out 
there. Even after system crashes I haven't lost any data; a repair 
table and index rebuild fixed things.


steve

--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

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

Re: getting a Duplicate entry error when inserting a new record - but there is no duplicate record

[Steve Edberg]

At 4:36 PM -0700 3/13/07, jerad sloan wrote:

thanks.
i had an extra index setup for one of my fields...i deleted it and 
it started working as expected...but now the same thing is happening 
again.


i'm getting the error
Duplicate entry 
'http://vids.myspace.com/index.cfm?fuseaction=vids.individualvid' 
for key 2


when doing the following
insert into tblvideoURL 
(videourl,userid,shortname,videourlcreated,videoURLcurrentstatus 
,xmltitle ) values 
('http://vids.myspace.com/index.cfm?fuseaction=vids.individualvideoid=1951753288',3,'MySpace',{ts 
'2007-03-13 15:04:01'},404 ,'Sporting Riff Raff - Absolutely Wasted 
(director unknown - hopefully Emily?!)' )


here is the Show Create Table



SNIP



  PRIMARY KEY  (`videourlID`),
  UNIQUE KEY `videourl` (`videourl`),
  KEY `videoURLcurrentstatus` (`videourlcurrentstatus`),



Key 2 is defined as unique; thus, the error message says you're 
inserting a record where videourl duplicates an existing record. 
Solutions:


(1) don't do that.
(2) drop index videourl
(3) drop index videourl; create index videourl (videourl).
(4) if you want to ensure uniqueness across a set of columns, define 
a unique composite key, for instance: create unique index01 
(videourl,userid);


Indexes/keys do not have to be unique unless they are primary or 
defined as unique.


steve

--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

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

Re: getting a Duplicate entry error when inserting a new record - but there is no duplicate record

[Steve Edberg]

At 5:24 PM -0700 3/13/07, jerad sloan wrote:

thanks for the quick reply.

i want that to be unique so there is no way to insert the same URL 
more than once.
there isn't a record with that URL...but it gives the error 
anyway...it seems to be just checking a portion of the entry i'm 
trying to insert.

i don't want uniqueness across a set of columns...just that one column.



Aah, I wasn't following the emails closely enough; sorry. Your error 
message does indeed look a bit puzzling, as it only shows the first 
64 characters. Key length is limited to 1024 -


http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html

- and even the sum total length of all your keys is only something 
like 440 bytes. Two other options I can think of: there's a hidden 
ascii NUL character in there between the 'vid' and 'eoid...' that's 
terminating the string, or you might have some table corruption. A 
check table -


http://dev.mysql.com/doc/refman/5.0/en/check-table.html

- might illuminate that.

One other thought: have you tried inserting the records by hand from 
the Mysql commandline prompt? Perhaps the program is truncating the 
field at 64 chars, so it's not a mysql problem at all. Or,  if this 
data is coming from an HTML form, perhaps there's a maxlength 
parameter on the videourl form field.




what do you mean by
(3) drop index videourl; create index videourl (videourl).
drop the unique index and create a non-unique index?
that isn't what i'm trying to do...i need it to be unique.



You can disregard this; again, didn't read the original message 
closely enough. This would indeed create a non-unique index.


steve



thanks,
jerad


On Mar 13, 2007, at 4:55 PM, Steve Edberg wrote:


At 4:36 PM -0700 3/13/07, jerad sloan wrote:

thanks.
i had an extra index setup for one of my fields...i deleted it and 
it started working as expected...but now the same thing is 
happening again.


i'm getting the error
Duplicate entry 
'http://vids.myspace.com/index.cfm?fuseaction=vids.individualvid' 
for key 2


when doing the following
insert into tblvideoURL 
(videourl,userid,shortname,videourlcreated,videoURLcurrentstatus 
,xmltitle ) values 
('http://vids.myspace.com/index.cfm?fuseaction=vids.individualvideoid=1951753288',3,'MySpace',{ts 
'2007-03-13 15:04:01'},404 ,'Sporting Riff Raff - Absolutely 
Wasted (director unknown - hopefully Emily?!)' )


here is the Show Create Table



SNIP


  PRIMARY KEY  (`videourlID`),
  UNIQUE KEY `videourl` (`videourl`),
  KEY `videoURLcurrentstatus` (`videourlcurrentstatus`),



Key 2 is defined as unique; thus, the error message says you're 
inserting a record where videourl duplicates an existing record. 
Solutions:


(1) don't do that.
(2) drop index videourl
(3) drop index videourl; create index videourl (videourl).
(4) if you want to ensure uniqueness across a set of columns, 
define a unique composite key, for instance: create unique index01 
(videourl,userid);


Indexes/keys do not have to be unique unless they are primary or 
defined as unique.


steve



--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

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

Re: Default Value to a DateTime Column

[Steve Edberg]

At 9:30 PM -0300 3/13/07, allysonalves wrote:

Hi everybody

Has some way to set up a function, like now(), as a default value of 
an dateTime column.

Is some thing like this.

Create Table Persons (
PersonCode int(10) zerofill not null auto_increment,
PersonName varchar(150) not null,
CadastreDate DateTime not null default now(),
Primary Key (PersonCode)) Engine=MyIsam

I am not want to transfer the responsibility of populate 
CadastreDate column to layers up.


Who is the best solution ?

Allyson Roberto Alves Cavalcanti



in Mysql = 4.1, you can use a non-updating timestamp:

create table Persons (
...
CadastreDate timestamp default current_timestamp,
...

unlike older timestamp types, this will not change if the record is 
updated. That behavior can be replicated via for example:


	CadastreDate timestamp default current_timestamp on update 
current_timestamp


See

http://dev.mysql.com/doc/refman/5.0/en/timestamp-4-1.html

- steve
--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

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

Re: row count inconsistency

[Steve Edberg]

At 10:34 AM -0600 2/21/07, Gerald L. Clark wrote:

Marty Landman wrote:

The table was created and then loaded and not modified in any way I'm aware
of afterwards. It's on a local, only accessible by me server.

Really weird thing about it is that I wrote/ran a program specifically to
find any gaps in the id sequence - because of the size of the table it took
days to run but the result was

1-100537311

IOW it confirms the max id that Mysql gave, but also indicates that there
are no gaps in the row id's all the way through. This doesn't make sense to
me in light of Mysql reporting the count as posted previously i.e.


mysql select count(*) from fidcid;
+---+
| count(*)  |
+---+
| 100480507 |
+---+
1 row in set (0.09 sec)




If this table is InnoDB, then count(*) is
just an approximation.


--
Gerald L. Clark
Supplier Systems Corporation



select count(*), as well as other functions like max(), min() etc 
should be accurate regardless of table type; it's the 'show table 
status' report that may be inaccurate for Innodb:


http://dev.mysql.com/doc/refman/5.0/en/show-table-status.html

Going back to the original problem:

What is the table type  MySQL version? Also, if you drop the 
auto_increment column and recreate it (on a copy of the original 
table, if necessary), are these results repeatable?


Also, if the server has been shutdown improperly, there may be table 
corruption:


MyISAM tables:
http://dev.mysql.com/doc/refman/5.0/en/myisam-table-problems.html

InnoDB problems:
http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html

steve

PS. This may be an obvious question, but: are you sure data loading 
was finished before running the select count(*) and select max(id) 
queries?



--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

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

Re: row count inconsistency

[Steve Edberg]

At 6:23 PM -0500 2/19/07, Marty Landman wrote:

Hi,

I've got a very large table set up and have defined the id as
auto_increment. No rows have been added, deleted, or replaced since the
initial load so I'd expect the row count to equal the max(id) since

mysql describe fidcid;
++---+--+-+-++
| Field  | Type  | Null | Key | Default | Extra  |
++---+--+-+-++
| id | int(10) unsigned  | NO   | PRI | NULL| auto_increment |
| fId | smallint(5) unsigned  | NO   | MUL | ||
| cId | mediumint(8) unsigned | NO   | MUL | ||
| ring | tinyint(3) unsigned   | NO   | | ||
++---+--+-+-++
4 rows in set (0.38 sec)

But this is not the case, as seen below:

mysql select count(*) from fidcid;
+---+
| count(*)  |
+---+
| 100480507 |
+---+
1 row in set (0.09 sec)

mysql select max(id) from fidcid;
+---+
| max(id)   |
+---+
| 100537311 |
+---+
1 row in set (0.22 sec)

mysql

Any ideas on what might've happened to explain this?




Had the table been used before? The auto_increment counter is 
normally not reset, for example:


mysql create table test (id int unsigned auto_increment not null primary key);
Query OK, 0 rows affected (0.03 sec)

mysql insert into test values (null),(null),(null);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql select * from test;
++
| id |
++
|  1 |
|  2 |
|  3 |
++
3 rows in set (0.00 sec)

mysql delete from test;
Query OK, 3 rows affected (0.00 sec)

mysql insert into test values (null),(null),(null);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql select * from test;
++
| id |
++
|  4 |
|  5 |
|  6 |
++
3 rows in set (0.00 sec)

You can either drop/recreate the auto_increment field or explicitly 
reset it using an


alter table tablename auto_increment=1

statement. See

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

for more info.

steve
--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

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

Getting OT re San Jose (was RE: General MySQL Question: Ed Reed (CA, United States of America) Medium)

[Steve Edberg]

I'd second the Rosicrucian Egyptian Museum:

   http://www.egyptianmuseum.org/

Also the Computer History Museum (located in the former SGI 
International Sales Office, I believe) is HIGHLY recommended by 
me...one of the docents there the last time I visited was Gene 
Amdahl! They have a working PDP-1, Cray 1, 2, 3 and Y-MP, and one of 
Google's first server racks, among tons of other stuff. They're in 
Mountain View, but it's not far, and would amply justify a carpool:


   http://www.computerhistory.org/

Intel's museum is in Santa Clara (never been there myself):

   http://www.intel.com/museum/index.htm

And the San Jose Museum of Art:

   http://www.sjmusart.org/

I found this exhibition to be quite interesting:


http://www.sjmusart.org/content/exhibitions/current/exhibition_info.phtml?itemID=324

And if you have kids with you (including inner children...), there's

   http://www.cdm.org/

Never been there, though, so I can't vouch for it. Interested in 
aviation/space?


   http://www.moffettfieldmuseum.org/

   http://www.nasa.gov/centers/ames/home/exploration.html

   http://www.hiller.org/


steve, museum nerd




At 10:16 AM -0500 1/30/07, Jerry Schwartz wrote:

If you are into ancient Egypt, check out the Rosicrucian museum in San Jose.

Regards,

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

860.674.8796 / FAX: 860.674.8341



 -Original Message-
 From: Eric Braswell [mailto:[EMAIL PROTECTED]
 Sent: Tuesday, January 30, 2007 3:02 AM
 To: Mike Wexler
 Cc: Ed Reed; mysql@lists.mysql.com; [EMAIL PROTECTED]
 Subject: Re: General MySQL Question: Ed Reed (CA, United
 States ofAmerica) Medium

 Great idea!  Tips on how to get around, good local
 restaurants (if you
 like Vietnamese, San Jose is the place), and other things to
 do when you
 need a break...  There is really a great deal just a short distance
 away, but you have to know how to get there. It's possible there was
 something like this last year, but I live in the area so I
 didn't look
 for it.

 I'm in full agreement that a conference is just that much
 better when it
 includes a little variety, a chance to get away from the drab
 convention
 hall, and a chance to socialize a little.

 One thing I suggest is to attend the Quiz Show (Wed 8pm) or
 other after
 hours activities. Great fun, and a good way to hook up with
 people for
 dinner or drinks away from the center.

 Eric


 --
 Eric Braswell
 Web Manager MySQL AB
 Cupertino, USA



 Mike Wexler wrote:
  The area by the Santa Clara convention center is pretty
 dead, but you
  can take the light rail to downtown San Jose and there is a pretty
  lively nightlife there, lots of interesting restaurants,
 The Tech Museum
  and other things depending on you interests.
  Also there are lots more interesting places to eat than
 sizzler within
  in  5 minute drive of the convention center.
 
  Perhaps what is needed is either a nice cheatsheet of what
 to do and
  where to go?
  Or perhaps some of the locals could volunteer to be
 ambassadors and
  take people with similar interests to local activities,
 restaurants or

   points of interest.
 



--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

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

Re: insert into some table show status like 'foo'

[Steve Edberg]

At 11:12 AM -0800 12/15/06, Chris Comparini wrote:

Hello,

Say I wanted to log some various server status variables to
a table.  What I'd like to do, ideally, is something like
this:

insert into SomeLogTable (Threads) show status like 'Threads_running';

MySQL does not allow this, of course.  But, is there some other way
to see the Threads_running (or other status variables) such that
this would be possible?

Incidentally, one of the things I'd like to log thus is the slave
status Seconds_Behind_Master.  It's a little disappointing that
you cannot do this:

show slave status like 'Seconds_Behind_Master';

.. but rather have to get the entire slave status back in order
to see this one thing.  Anyway...

If anyone has any ideas on this, I'd love to hear them.




If you're referring to MySQL status vars and you are using MySQL = 
5.0, you can indeed do this. For example:


[EMAIL PROTECTED] show variables like 'wait_timeout';
+---+---+
| Variable_name | Value |
+---+---+
| wait_timeout  | 28800 |
+---+---+
1 row in set (0.00 sec)

[EMAIL PROTECTED] select @@wait_timeout;
++
| @@wait_timeout |
++
|  28800 |
++
1 row in set (0.00 sec)

Strangely, at least for me not all system vars can be accessed this 
way; I haven't been able to see many innodb_... variables. But I 
haven't played with this much, I might be doing something wrong.


For more info, see:

http://dev.mysql.com/doc/refman/5.0/en/using-system-variables.html


   - steve

--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

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

Re: Cloning or duplicating a record

[Steve Edberg]

At 3:31 PM -0700 9/14/06, Scott Haneda wrote:

  how about:

 insert into table_name select * from table_name where select criteria

 is the primary key an auto sequence?


This is what happens when I try:
insert into logbook select * from logbook where id = 1;
ERROR 1062: Duplicate entry '1' for key 1
--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.



YOu'll have to list the fields explicitly, except for the primary 
key. For example, if your table has columns:


id (PK)
data_1
data_2
data_3

you should be able to do

   insert into table_name (data_1, data_2, data_3) select 
data_1,data_2,data_3 from table_name where id=1


The insert failed because you were - as the error message said - 
trying to insert a record with an existing primary key, which is 
unique.


Check mysql manual for more info on syntax of insert command.

steve

--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

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

Re: Data dictionary for MySQL

[Steve Edberg]

At 11:29 AM +0530 9/7/06, Ravi Kumar. wrote:

Hi All,

I am looking for a data dictionary for MySQL. It should be free and ideally,
developed in php. And such that it stores the data dictionary in MySQL
itself.

Any suggestions / pointers?




If you're using MySQL5, take a look at the information_schema 
database; it might have everything you need already:


http://dev.mysql.com/doc/refman/5.0/en/information-schema.html


steve

--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

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

Re: Managing big mysqldump files

[Steve Edberg]

At 4:03 PM +0530 8/19/06, Anil  wrote:

Hi List,



We are facing a problem of managing mysqldump out put file which is
currently of size  80 GB and it is growing daily by 2 - 3 GB, but we have a
linux partition of only 90 GB.. Our backup process is  first generate the
mysqldump file of total database and then compress the dump file and remove
the dump file. Is there any way  to get compressed dump file instead of
generating dump file and then compressing it later. Any ideas or suggestions
please



Thanks

Anil





Short answer: Yes -

mysqldump mysqldump options | gzip  outputfile.gz

Other alternatives:

You could direct output to a filesystem that is larger than the 90GB 
filesystem you mention (perhaps NFS mounted?).


You could pipe the output of gzip through ssh to a remote server.

You could use bzip2, which compresses substantially better than gzip, 
but with a significant performance/speed penalty (that is, do 
mysqldump | bzip2  outputfile.bz2).


Try 'man gzip' and 'man bzip2' for more info.

steve

--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

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

RE: counting keywords

[Steve Edberg]

At 1:55 PM +1000 4/12/06, Taco Fleur wrote:

Hello Steve,

Your suggestion works like a charm, I am now trying to get my head around
your following statement, I can't seem to get a grip on what you mean. Is
there anyway you could elaborate a little on the following? It would be
really nice if I could get the solution to work with full text and be able
to use stemming etc.



 where document_id is a foreign key pointing at the table containing your

fulltext. This would be easier to extend to handle synonym handling too, and
you could do all the suffix handling/stemming you need (eg; to take care of
plurals). I've done something like that as well, and included an extra field
for the metaphone version of the word, to match approximate spellings.



Assume table structures something like this:

   WordTable:
   wordchar(32) not null # or whatever your max word 
length is likely to be

   word_count  integer unsigned not null
   document_id integer unsigned not null

   DocTable:
   document_id integer unsigned not null auto_increment primary key
   doc_author
   doc_date
   ...
   doc_bodytext

In your original post, you needed to get exact counts of words 
appearing in the document body; MySQL's full text search can't do 
that. What you could do in this case is some preprocessing on the 
doc_body when you insert it. You would scan the text, ignoring the 
insignificant words (eg; and, the, a, is, and so on), and then 
transform the remaining words into a canonical form (eg; glasses, 
glassy, glass all become glass), and then insert into the WordTable. 
Then, when a search is performed, you translate the search terms 
using the same algorithm and search the WordTable. You might also 
have a SynonymTable that you could use to translate all synonyms to a 
standard term before insertion into the WordTable and before 
searching.


There are various stemming algorithms around; the Porter Algorithm 
was one of the earlier ones, and the one I have worked with some:


   http://www.tartarus.org/martin/PorterStemmer/

It worked pretty well, but I needed to maintain an exception list for 
some words it incorrectly translated. Also, you have to decide 
whether similar terms like 'anthropologist' and 'anthropology' are 
identical as far as your search is concerned.


The Snowball or Porter2 algorithm is apparently an improvement on the original:

   http://snowball.tartarus.org/algorithms/english/stemmer.html

A google for 'stemming algorithms' also turned up this:

   http://www.comp.lancs.ac.uk/computing/research/stemming/

And if you're dealing with non-English words, then you'll have to 
look for native language stemmers or modify the rules in the above 
algorithms.


If you had a table set up as above, you could do either searches 
based on the word table (where you could return documents sorted by 
the number of times the search words appeared), or using a fulltext 
search on the doc_body. I would imagine that for many documents the 
order of results would be similar. The fulltext algorithm also 
weights words more heavily based on their uniqueness.


If you want to do searching based on approximate spellings, you could 
add an additional column to WordTable:


   word_approxchar(32) not null

and store either the soundex version of the word -

   http://en.wikipedia.org/wiki/Soundex

- or the more accurate (in my experience) metaphone algorithm -

   http://en.wikipedia.org/wiki/Metaphone

Then the search terms would be run through two transforms: a stemmer, 
and a 'metaphoner'.


Lastly, it appears that MySQL 5.1 has a new plug-in API -

   http://dev.mysql.com/doc/refman/5.1/en/plugin-api.html

- which includes the ability to modify/replace fulltext parser 
behavior. It looks like you might be able to create custom functions 
to do most or all of the above using user-defined functions, 
presumably with relatively high efficiency.


steve

--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

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

Re: [NEWBIE] How To Trim Database To N Records

[Steve Edberg]

At 11:15 PM -0400 4/12/06, David T. Ashley wrote:

Hi,

I'm a beginning MySQL user ...

I have a table of log entries.  Over time, the entries could grow to be
numerous.  I'm like to trim them to a reasonable number.

Is there a query that will, say, trim a table down to a million rows (with
some sort order, of course, as I'm interested in deleting the oldest ones)?

The sorting isn't a problem.  I've just never seen an SQL statement that
will drop rows until a certain number remain ...

Thanks, Dave.


Something like this might work (untested):

   select @n:=count(*) from your_table
   delete from your_table order by time_stamp limit @n-100

Of course, you'd want to try it on a test table first, not live data! 
This assumes 100 is the max number of records you want to keep, 
you want to delete the oldest records based on the time_stamp column, 
AND that the record count when you do this delete is always  
100. You'd need to do some additional checking first if that 
isn't the case, as I don't know at the moment what the behavior for a 
negative or zero limit is (the docs below should tell you).


More info:

   http://dev.mysql.com/doc/refman/4.1/en/user-variables.html
   http://dev.mysql.com/doc/refman/4.1/en/example-user-variables.html
   http://dev.mysql.com/doc/refman/4.1/en/delete.html

steve

--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

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

RE: counting keywords

[Steve Edberg]

At 1:35 PM +1000 4/11/06, Taco Fleur wrote:

Thanks Steve,

Much appreciated, I was hoping there was something a little simpler, but I
will have a go at it.
Anyway of doing this with RegEx, would that simplify things?



Hi, and you're welcome -

Unfortunately, I don't think this can be done with regex/rlike; those
only give a boolean result (pattern matched/not matched), but can't
as far as I know be used for counting/replacing strings. This doesn't
appear to have changed even in MySQL 5.1.

steve



-Original Message-
From: Steve Edberg [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 11 April 2006 9:50 AM
To: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: Re: counting keywords

At 7:37 AM +1000 4/11/06, Taco Fleur wrote:

Hi all,

I am trying to find out whether it is possible to return the count of
keywords found in a text field, does anyone know?
For example; ColdFusion or Java is entered in the search string and
20 records are found that match, I need to count how many times

ColdFusion

and Java appears in each match, add those two and than sort
descending on that total.

I was actually using verity for the search, but the client insists he
sees the number of keywords found, which Verity does not do.

The text searched are Résumé's, I initially thought that MySQL could
search the résumé's when stored as binary data, but I was wrong, so I
now have the CV's converted to HTML and then store them in the DB as
VARCHAR

Any help would be much appreciated, I am having a hard time coming from
a MS SQL background ;-)



There's no function that I know of to do that directly; however, you could
do something like this:

select
   (length(your_text_field)-length(replace(your_text_field,
'coldfusion','')))/length('coldfusion')
   as wordcount
from
   your_table

That is, it removes all instances of 'coldfusion' from  your string, gets
the difference in length from the unaltered string, and divides that by the
number of characters in your search string. I've used this method several
times.

Of course, if you want to avoid matching against terms like 'javalike' or
'coldfusionista' then you've gotta do some additional checking, for example:

select
   (length(your_text_field)-length(replace(concat('
',your_text_field,' '), ' coldfusion ','')))/length(' coldfusion ')
   as wordcount
from
   your_table

See

http://dev.mysql.com/doc/refman/4.1/en/string-functions.html

for more info.

If you're doing this alot, it might be more efficient to build a word index
table like:

wordchar(32) not null # or whatever your max word
length is likely to be
word_count  integer unsigned not null
document_id integer unsigned not null

where document_id is a foreign key pointing at the table containing your
fulltext. This would be easier to extend to handle synonym handling too, and
you could do all the suffix handling/stemming you need (eg; to take care of
plurals). I've done something like that as well, and included an extra field
for the metaphone version of the word, to match approximate spellings. If
the text fields were all in plain text, you could even include character
positions like

word  char(32) not null
word_position integer unsigned not null
document_id   integer unsigned not null

then you could get word counts by doing a select count(word).


steve



  Kind regards,


Taco Fleur

Free Call 1800 032 982 or Mobile 0421 851 786 Pacific Fox
http://www.pacificfox.com.au/ http://www.pacificfox.com.au an
industry leader with commercial IT experience since 1994 …

*

Web Design and Development
*

SMS Solutions, including developer API
*

Domain Registration, .COM for as low as fifteen dollars a year,
.COM.AU for fifty dollars two years!

 





--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

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

Re: counting keywords

[Steve Edberg]

At 7:37 AM +1000 4/11/06, Taco Fleur wrote:

Hi all,

I am trying to find out whether it is possible to return the count of
keywords found in a text field, does anyone know?
For example; ColdFusion or Java is entered in the search string and 20
records are found that match, I need to count how many times ColdFusion
and Java appears in each match, add those two and than sort descending on
that total.

I was actually using verity for the search, but the client insists he sees
the number of keywords found, which Verity does not do.

The text searched are Résumé's, I initially thought that MySQL could search
the résumé's when stored as binary data, but I was wrong, so I now have the
CV's converted to HTML and then store them in the DB as VARCHAR

Any help would be much appreciated, I am having a hard time coming from a MS
SQL background ;-)



There's no function that I know of to do that directly; however, you
could do something like this:

   select
  (length(your_text_field)-length(replace(your_text_field,
'coldfusion','')))/length('coldfusion')
  as wordcount
   from
  your_table

That is, it removes all instances of 'coldfusion' from  your string,
gets the difference in length from the unaltered string, and divides
that by the number of characters in your search string. I've used
this method several times.

Of course, if you want to avoid matching against terms like
'javalike' or 'coldfusionista' then you've gotta do some additional
checking, for example:

   select
  (length(your_text_field)-length(replace(concat('
',your_text_field,' '), ' coldfusion ','')))/length(' coldfusion ')
  as wordcount
   from
  your_table

See

   http://dev.mysql.com/doc/refman/4.1/en/string-functions.html

for more info.

If you're doing this alot, it might be more efficient to build a word
index table like:

   wordchar(32) not null # or whatever your max word
length is likely to be
   word_count  integer unsigned not null
   document_id integer unsigned not null

where document_id is a foreign key pointing at the table containing
your fulltext. This would be easier to extend to handle synonym
handling too, and you could do all the suffix handling/stemming you
need (eg; to take care of plurals). I've done something like that as
well, and included an extra field for the metaphone version of the
word, to match approximate spellings. If the text fields were all in
plain text, you could even include character positions like

   word  char(32) not null
   word_position integer unsigned not null
   document_id   integer unsigned not null

then you could get word counts by doing a select count(word).


steve



 Kind regards,


Taco Fleur

Free Call 1800 032 982 or Mobile 0421 851 786
Pacific Fox  http://www.pacificfox.com.au/ http://www.pacificfox.com.au an
industry leader with commercial IT experience since 1994 …

*

Web Design and Development
*

SMS Solutions, including developer API
*

Domain Registration, .COM for as low as fifteen dollars a year,
.COM.AU for fifty dollars two years!





--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

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

Re: Visual database design system

[Steve Edberg]

At 10:52 AM -0500 2/3/06, Adi wrote:

I am looking for a tool to integrate with mysql...I have tried DBDesigner
and would like to get my hands on software that is equivalent or better than
DBDesigner...any suggestions? FYI: I have had some problems with importing,
printing etc with DBDesigner...

Thanks in advance...



I've used Artiso Visual Case -

http://www.visualcase.com/

- some. Java-based, still a bit rough here and there, but they have a 
free 30-day trial. Academic price (what I paid) much less expensive 
than standard license. I haven't tried it with MySQL 5 yet. If I 
recall clearly, it is similar in scope to Datanamic's DeZign -


http://www.datanamic.com/

- which is (or was) Windows only.

steve

--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

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

Re: dump to more than 1 file

[Steve Edberg]

At 3:56 PM + 11/21/05, Tom Brown wrote:
is it possible to do a mysql dump to more than 1 file? We will 
shortly be needing to dump a db that will be in excess of 50gb so 
will encounter file size issues


This is on 4.1.x and rhel 4



Probably the best approach - knowing nothing about your db - would be 
to dump tables to separate files; you could write a pretty simple 
script to do that.


Since mysqldump writes to stdout, you could pipe to a zip/bzip/gzip, 
although that's unlikely to compress 50GB down to something most 
unixes can handle (a safe size is 2GB):


mysqldump -uuser -p database | gzip  dump.gz

You could pipe to split (try 'man split'), which would split the 
output into pieces by # of lines or # of bytes (eg; dump.001, 
dump.002, ...) and then reassemble via cat. It would be nice to do 
something like


mysql -uuser -ppassword database  `cat dump.*`

but I don't think that's possible. You'd have to reassemble the dump 
file first, which means you might run into file size issues again. 
Probably best to do table-by-table, piping to zip/bzip/gzip as well.


steve

--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

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

Re: INSERT record IF NOT EXISTS?

[Steve Edberg]

At 7:10 PM -0700 8/17/05, Daevid Vincent wrote:

Does mySQL have a way to INSERT a new record if one doesn't exist (based
upon primary compound key)?

I see this EXISTS but not an example of how to use it with INSERT.

I see INSERT... ON DUPLICATE KEY UPDATE col_name=expr which is very close,
but I want it to do nothing on duplicate key. :(



Perhaps you could update using the same value? Eg:

INSERT ... ON DUPLICATE KEY UPDATE col_name=col_name

If you're worried about side-effects (eg; updating a timestamp column 
incorrectly), I believe that MySQL will not perform the update if the 
column value does not change.


steve



mysqladmin  Ver 8.40 Distrib 4.0.24, for pc-linux-gnu on i386

CREATE TABLE `release_test` (
  `BID` int(10) unsigned NOT NULL default '0',   
  `ReleaseID` smallint(5) unsigned NOT NULL default '0', 
  `Tested` tinyint(1) unsigned NOT NULL default '0', 
  `CoreID` smallint(3) unsigned NOT NULL default '0',
  KEY `BID` (`BID`,`ReleaseID`), 
  KEY `ReleaseID` (`ReleaseID`)  
) TYPE=MyISAM;




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



--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

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

Re: List for newbie

[Steve Edberg]

At 3:56 PM +0100 1/3/05, Paun wrote:
I am very new in mysql, and don't want to disturb users who have much more
expirience with mysql.
Is there any mysql list for newbies??

No, this is the appropriate list...just make sure you:
(1) first try to search the manual:
http://dev.mysql.com/doc/
(2) search the mailing list archives:
http://lists.mysql.com/
http://marc.theaimsgroup.com/?l=mysqlr=1w=2
(3) Then, if you still need to post a message to the mailing list:
* Explain your problem as clearly as possible
* Describe what you've tried already
*If appropriate, post table structures, example queries, and/or example output
Here's a *comprehensive* reference on good ways to ask questions:
http://www.catb.org/~esr/faqs/smart-questions.html
...and, finally, welcome to the MySQL world!
steve
--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Heap Help

[Steve Edberg]

At 1:09 PM -0700 11/29/04, [EMAIL PROTECTED] wrote:
I want to put a table in Ram (HEAP) with a field of at least 500 
characters.  I do I do this if Blob and text are not allowed?

The glib answer would be: you can't.
Two alternatives would be: (1) split your text field up into as many 
char/varchar columns as necessary 9eg; text_part_1, text_part_2, 
...), and then split/rejoin those columns programmatically; or, (2) 
you might be able to losslessly compress or otherwise encode your 
text such that it will fit into a char(255) or smaller column.


Thanks
Donny Lairson
President
http://www.gunmuse.com/http://www.gunmuse.com
469 228 2183

--
+--- my people are the people of the dessert, ---+
| Steve Edberghttp://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center[EMAIL PROTECTED] |
| Bioinformatics programming/database/sysadmin (530)754-9127 |
+ said t e lawrence, picking up his fork +

Re: html in a text field - good practice?

[Steve Edberg]

At 08:37 AM 8/18/04, leegold wrote:
Question I have wondered about: Is it a good practice to put html in a
text field, then (eg. via php) when the marked-up text renders in a
user's browser it's good looking html. If not, then I'd just sandwitch
field content in a p/p when it's rendered. Though, seems like it
would mess-up fulltext searching in a marked-up text field(?). Thanks.
Lee G.
I'd say that in general, it's best  to avoid storing markup with text, 
because (1) as you say, it could mess up fulltext search statistics, and 
(2) it makes it more complex to repurpose that data - eg; export it to PDF 
instead of HTML. It's best to keep rendering separate from content.

If you *need* to store formatting information, you might be better off 
saving it in a more general format like XML or even SGML. If you had to do 
a lot of that, and search it, you might be better off using an XML 
database, though. I suppose that you could twiddle with MySQL's fulltext 
search parameters (min. word length, stop words, etc) to get it to ignore 
embedded HTML/XML/SGML, though.

steve
++
| Steve Edberg  [EMAIL PROTECTED] |
| Database/Programming/SysAdmin(530)754-9127 |
| University of California, Davis http://pgfsun.ucdavis.edu/ |
+-- Gort, Klaatu barada nikto! --+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Does Dropping a table affect it's indexes?

[Steve Edberg]

It's my understanding that doing a simple delete

delete from table_name

actually DOES drop and recreate the table (and thus its indexes). On the 
other hand, if you are continually adding  deleting records, you might 
well need to do a periodic 'analyze table_name' or 'optimize table_name' to 
maintain optimum performance  clear the deleted record chain.

steve

At 03:21 PM 2/19/04, Jeff McKeon wrote:
Quick question...

What you drop a table are the indexes for that table dropped to?

I'm about to write a script to take a data pull every night and
re-populate a table with the results, then have my apps run off of the
new consolidated table for a speed increase.  If I drop the Consolidated
table, then re-create it with the new data pull, will I need to
re-create the indexes as well?
Is there any performance cost/benefit to simply deleting all data from
the table and then re-populating it as opposed to droping and
re-creating it?
Thanks,

Jeff


++
| Steve Edberg  [EMAIL PROTECTED] |
| Database/Programming/SysAdmin(530)754-9127 |
| University of California, Davis http://pgfsun.ucdavis.edu/ |
+-- Gort, Klaatu barada nikto! --+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: char count

[Steve Edberg]

At 12:17 PM -0600 1/21/04, Joseph S Brunzelle wrote:
I'm try to write a query that will return the number of times a specified
character appears in a string (I want known how many times the character M
appears), but I cannot seem to figure out how to do that.  The select
statement is the following:
SELECT structure_aa_sequence from structure where apc_id=APC1114;

and this will return the following:

+--+
| structure_aa_seq |
+--+
| GGFVPNWYQHPDPALKYADDMEVYDYYQQYEAAKKAAREASTSSKKTAATSPALPRAKPHVTIA |
+--+
Thanks


Well, this query should do it, but I suspect it's too inefficient to 
run on a regular basis:

	select 
length(replace(structure_aa_sequence,'M','MM'))-length(structure_aa_sequence) 
from structure;

I just replace the character of interest with 2 characters (in the 
query; it doesn't affect the database), and check the length 
difference.

To use the counts on a regular basis, it's probably easier to count 
them when you insert or update the record, and store those values in 
a separate column. That's what I ended up doing on a few genetics 
databases I was working on, anyway -

	steve


Joseph S. Brunzelle, Ph.D.
Life Sciences CAT
Dept of Mol. Pharm. and Biol. Chem.
Feinberg School of Medicine
Northwestern University
Phone (630)252-0629  FAX (630)252-0625
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


--
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| [EMAIL PROTECTED]: 1001 Work units on 23 oct 2002  |
| 3.152 years CPU time, 3.142 years SETI user... and STILL no aliens...  |
++
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: specific records

[Steve Edberg]

Actually, this will *not* necessarily work. Without an ORDER BY 
clause, the database is free to return records in any order; after 
some deletions  insertions, your select below may return different 
records, in a different order.

I would recommend adding an explicit record number to the table, 
using an auto_increment column; it may be more work now, but it will 
be best in the long run.

steve

At 12:02 PM +0200 7/2/03, Maciej Bobrowski wrote:
O.K. I found the way:

select * from tablename limit 5,6;

it will select 6 records counting from 6.

 Let's say I have 1000 records in a 'table'. I want to select rows from6
 to 11. How can I do this?
 SELECT * FROM tablename where column5 AND column12;
Best regards,

Maciej Bobrowski



--
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| [EMAIL PROTECTED]: 1001 Work units on 23 oct 2002  |
| 3.152 years CPU time, 3.142 years SETI user... and STILL no aliens...  |
++
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Row numbers

[Steve Edberg]

You can use user variables; example:

mysql describe library_master;
+-+--+--+-+-++
| Field   | Type | Null | Key | Default | Extra  |
+-+--+--+-+-++
| _id | int(10) unsigned |  | PRI | NULL| auto_increment |
| code| varchar(20)  |  | UNI | ||
| name| varchar(255) | YES  | | NULL||
| create_date | datetime | YES  | | NULL||
| tag_length  | int(11)  | YES  | | NULL||
| notes   | text | YES  | | NULL||
+-+--+--+-+-++
6 rows in set (0.00 sec)

mysql set @x=0;
Query OK, 0 rows affected (0.00 sec)

mysql select (@x:=@x+1) as row_number,name,tag_length from library_master;
++--++
| row_number | name | tag_length |
++--++
|  1 | Callus   | 17 |
|  2 | Flower   | 17 |
|  3 | Leaves   | 17 |
|  4 | Root | 17 |
|  5 | Silique  | 17 |
|  6 | Wild flowers | 17 |
|  7 | Flowers  | 17 |
|  8 | Flower   | 17 |
|  9 | Flower   | 17 |
++--++
9 rows in set (0.00 sec)

However, since user variables are persistent withing a session,  you 
have to remember to reset the value of @x; otherwise, you'll get 
something like this:

mysql select (@x:=@x+1) as row_number,name,tag_length from library_master;
++--++
| row_number | name | tag_length |
++--++
| 10 | Callus   | 17 |
| 11 | Flower   | 17 |
| 12 | Leaves   | 17 |
| 13 | Root | 17 |
| 14 | Silique  | 17 |
| 15 | Wild flowers | 17 |
| 16 | Flowers  | 17 |
| 17 | Flower   | 17 |
| 18 | Flower   | 17 |
++--++
9 rows in set (0.00 sec)

	-steve


At 1:59 PM -0500 2/13/03, Luc Foisy wrote:
There is no relevant data or use to this number.
It is the row number of the returned result set, purely for display.

I was hoping there was some kind of function just to drop a number 
in there, regarless of any data that is stored in the table or 
regardless of the order the resultset appears.

 -Original Message-
 From: Jerry [mailto:[EMAIL PROTECTED]]
 Sent: Thursday, February 13, 2003 1:48 PM
 To: Luc Foisy
 Subject: Re: Row numbers


 Have to have one in the row and select that along with the
 query, if your
 going to use it for some other sql command it probally should
 be in the
 table already

 - Original Message -
 From: Luc Foisy [EMAIL PROTECTED]
 To: Jerry [EMAIL PROTECTED]
 Sent: Thursday, February 13, 2003 5:59 PM
 Subject: RE: Row numbers


  No language, just straight mysql
 
   -Original Message-
   From: Jerry [mailto:[EMAIL PROTECTED]]
   Sent: Thursday, February 13, 2003 12:53 PM
   To: Luc Foisy
   Subject: Re: Row numbers
  
  
   using what language ? or the mysql client ?
  
   - Original Message -
   From: Luc Foisy [EMAIL PROTECTED]
   To: MYSQL-List (E-mail) [EMAIL PROTECTED]
   Sent: Thursday, February 13, 2003 5:48 PM
   Subject: Row numbers
  
  
   
Is there a way to get a row number returned with any
 select query?
   
   
   

   



--
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| SETI@Home: 1001 Work units on 23 oct 2002  |
| 3.152 years CPU time, 3.142 years SETI user... and STILL no aliens...  |
++

-
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

RE: Row numbers

[Steve Edberg]

At 12:31 PM 2/13/03 , Luc Foisy wrote:

  I still don't understand do you mean the actual row
 number or just a
  display number.

   There is no relevant data or use to this number.
   It is the row number of the returned result set, purely
   for display.

That means no field exists or should exist in the database. I only want to 
generate at query time.
I can't use an autoincrement field since that wont work very well with 
results that are returned out of order and maybe not with all the data.

Using variables is the best response to my question. I just dislike using 
them cause they are ugly to work with because of the session persistance 
and because I have to issue multiple queries to do the job.

I only want to issue one query.




Extending my previous email, you could use the fact that undefined 
variables are null to combine

set @x=0;
select (@x:=@x+1) as row_number,name,tag_length from library_master;

into one query:

select if(isnull(@x),@x:=1,@x:=@x+1) as row_number,name,tag_length 
from library_master;

Of course, you'll need to rest @x back to null (or 0) if you want to run 
this query again in the same session...

And as far as ugly, well - eye of the beholder, and all that stuff ;)

-steve


++
| Steve Edberg  [EMAIL PROTECTED] |
| Database/Programming/SysAdmin(530)754-9127 |
| University of California, Davis http://pgfsun.ucdavis.edu/ |
+-- Gort, Klaatu barada nikto! --+


-
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

Re: NEWBIE to mysql

[Steve Edberg]

At 4:52 PM -0800 2/7/03, Wileynet wrote:

Can anyone tell me why I keep getting an ERROR 1064 with this command ?
I just would like to create a table with two fields, name and messages
for a
Simple guestbook but I want to store the data in a mysql database.

mysql CREATE TABLE info
- (
- name varchar(50)



You need a comma:

	name varchar(50),



- message varchar(255)
- )
- ;


thanks,
-wiley




--
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| SETI@Home: 1001 Work units on 23 oct 2002  |
| 3.152 years CPU time, 3.142 years SETI user... and STILL no aliens...  |
++

-
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

Re: TIMESTAMP field is updated unintentionally

[Steve Edberg]

At 12:17 PM +0100 1/31/03, Marco Deppe wrote:

Hi,

I was already questioning my sanity, but the problem below is
reproduceable:

This is how my table looks:
mysql describe T_ORDH;
--+--+-+++
Field |Type  |Null |Key |Default |Extra
--+--+-+++
PK_ID |int(10) unsigned  | |PRI |NULL|auto_inc
ERSTELL_DATUM |timestamp(14) |YES  ||NULL|
STATUS|smallint(5) unsigned  | ||0   |

If I do
mysql update T_ORDH set STATUS=2 where PK_ID=26272;
ERSTELL_DATUM is set to the current date. I know that a timestamp
takes the current time, if set it to NULL, but since I'm not touching
it, it shouldn't change, should it?

A quick workaround is
mysql update T_ORDH set STATUS=2, ERSTELL_DATUM=ERSTELL_DATUM
- where PK_ID=26272;

The big question: Is it a bug or a feature?
(mysql  Ver 11.18 Distrib 3.23.51, for pc-linux-gnu (i686))



From:

	http://www.mysql.com/doc/en/DATETIME.html

Automatic updating of the first TIMESTAMP column occurs under any of 
the following conditions:

snip

# You explicitly set the TIMESTAMP column to NULL


...so that means it's a feature.

	-steve

--
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| SETI@Home: 1001 Work units on 23 oct 2002  |
| 3.152 years CPU time, 3.142 years SETI user... and STILL no aliens...  |
++

-
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

Re: MySQL Truncated returns

[Steve Edberg]

I'm 97.5% sure this will turn out to be a HTML form issue. You 
probably echo the field without quotes like this

	input type=text name=some_name value=Tampa Bay /

instead of

	input type=text name=some_name value=Tampa Bay /

-steve

At 11:40 AM -0500 1/30/03, Anna Noel Leavitt wrote:
  Hello all-

 I am using MySql, php and forms in HTML to update information. When I

select results from column type VARCHAR, if the information stored

 in the column has a space in it (for example, Tampa Bay), it will only
 return Tampa (I have the length set at 30 so it is not a length issue).
 I tried changing the column type to text to no avail. I
 searched the documentation and couldn't figure it out. Can someone
 provide some insight for me?
 Thanks-

  Anna



--
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| SETI@Home: 1001 Work units on 23 oct 2002  |
| 3.152 years CPU time, 3.142 years SETI user... and STILL no aliens...  |
++

-
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

Re: group multiple queries.. if one fails all fail

[Steve Edberg]

This would be called transactions: your're looking for COMMIT/ROLLBACK. As 
far as I know, transactions are only available on BDB and InnoDb table 
types. See

http://www.mysql.com/doc/en/Transactional_Commands.html

http://www.mysql.com/doc/en/InnoDB_overview.html



At 12:24 AM 1/23/03 , Ciprian I. Acatrinei wrote:
Hi,

I looked a lot and I couldn't find a solution to this problem:

Ex: I have 5 queries. I run them and one of them fails. The rest are ok.
So 4 tables are affected and one is not. Is there a way of testing the
queries if they will be successful and only if all of them are then they
should be run?

Thank you,
--
Ciprian I. Acatrinei [EMAIL PROTECTED]


-
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

++
| Steve Edberg  [EMAIL PROTECTED] |
| Database/Programming/SysAdmin(530)754-9127 |
| University of California, Davis http://pgfsun.ucdavis.edu/ |
+-- Gort, Klaatu barada nikto! --+


-
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

Re: What is the best known practice for insertion...?

[Steve Edberg]

This page (Section 5.2.9: Speed of INSERT Queries) might be of interest:

	http://www.mysql.com/doc/en/Insert_speed.html

You might also want to investigate the INSERT IGNORE command (IGNORE 
means ignore all inserts with duplicate unique keys), for example:

	INSERT IGNORE
	INTO your_table
	(col1,col2,col3)
	VALUES
	(vala1,vala2,vala3),
	(valb1,valb2,valb3),
	(valc1,valc2,valc3)

See
	http://www.mysql.com/doc/en/INSERT.html

for more info.

	-steve


At 9:15 AM +0200 1/20/03, Zysman, Roiy wrote:
Hi All,
I'm trying to insert multiple lines to a table with 3 col. All 3 of them
are a part of the primary key.
But while inserting the data , sometime occurs the situation where I
want to insert an already exist entry with the same keys. And mysql
barfs out that this key already exist in the table.
The solution to it would be probably to query for each entry that I'm
about to insert if that entry exists , but it looks like it would be an
enormous overhead. I'm asking this question on a _performance_ context .
What is the best way , ignore the error messages mysql barfs out or
query each entry for existences before inserting it ?

Roiy

-
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


--
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| SETI@Home: 1001 Work units on 23 oct 2002  |
| 3.152 years CPU time, 3.142 years SETI user... and STILL no aliens...  |
++

-
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

Re: Indexing

[Steve Edberg]

At 6:12 PM +0800 1/20/03, Jon  Miller wrote:

I'm new to MySQL and loving it. So pardon my lack of the simple 
things. I need to index a table where the field is indexed, so when 
I look at the in the PHP script it is in order.


Use 'order by' in your select statement:

	http://www.mysql.com/doc/en/SELECT.html



 Also when I run a query how can I save the results as a new table?



Use the 'create table...select' statement:

	http://www.mysql.com/doc/en/CREATE_TABLE.html

alternatively, if you want to save the results in an existing table, 
use the 'insert into...select' statement:

	http://www.mysql.com/doc/en/INSERT_SELECT.html


-steve


Thanks

Jon L. Miller, MCNE, CNS
Director/Sr Systems Consultant
MMT Networks Pty Ltd
http://www.mmtnetworks.com.au

I don't know the key to success, but the key to failure
 is trying to please everybody. -Bill Cosby





-
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


--
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| SETI@Home: 1001 Work units on 23 oct 2002  |
| 3.152 years CPU time, 3.142 years SETI user... and STILL no aliens...  |
++

-
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

Re: index problem

[Steve Edberg]

At 2:31 AM -0700 1/16/03, Prasanth Krishna wrote:

hi

i have a database with a single table say tbl1 with an index on a
particular field say col1. when i say

select * from tbl1;

it doesn't use the index on that table.



In this query, I can't see any reason to use an index...MySQL is 
simply returning all columns from all records in whatever order it 
sees fit.

If you used

	select * from tbl1 order by col1

it *would* use the index.


but if i say

select col1 from tbl1;
it uses the index.



I'm guessing that in this case MySQL is reading col1 directly from 
the index file...it doesn't need to look at the actual data record at 
all. Which is good.


how to make mysql use the index on col1 for the first query?



Give it a reason to do so ;)

-steve


--
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| SETI@Home: 1001 Work units on 23 oct 2002  |
| 3.152 years CPU time, 3.142 years SETI user... and STILL no aliens...  |
++

-
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

Re: Avg_row_length

[Steve Edberg]

Yep, you're right!

Perhaps an extra byte is automatically reserved, not only for a 
null/not null status bit, but also for other internal use - eg; a 
'row changed' bit, etc.

I did a quick test:

mysql create table t2 (c1 varchar(255));
Query OK, 0 rows affected (0.00 sec)

mysql insert into t2 values ('s');
Query OK, 1 row affected (0.00 sec)

And this gave table status of

   Name: t2
   Type: MyISAM
 Row_format: Dynamic
   Rows: 1
 Avg_row_length: 20
Data_length: 20
Max_data_length: 4294967295
   Index_length: 1024
  Data_free: 0
 Auto_increment: NULL

so obviously the avg_row_length includes extra bytes for mysql internal info.

Learn something every day!

-steve


At 1:03 PM +0100 1/15/03, you wrote:
Steve,


 I believe there is an extra byte for the 'null' flag; if you declare
 this column to be NOT NULL, I'll bet the avg length will be 10.


nice bet, but you lose g:

mysql DESCRIBE mynotnullisam;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
| col   | char(10) |  | | |   |
+---+--+--+-+-+---+
1 row in set (0.16 sec)

mysql SHOW TABLE STATUS LIKE 'mynotnullisam';
+---+++--++-
+
| Name  | Type   | Row_format | Rows | Avg_row_length |
Data_length |
+---+++--++-
+
| mynotnullisam | MyISAM | Fixed  |3 | 11 |
33 |
+---+++--++-
+




--
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| SETI@Home: 1001 Work units on 23 oct 2002  |
| 3.152 years CPU time, 3.142 years SETI user... and STILL no aliens...  |
++

-
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

Re: Populating one table with data from another

[Steve Edberg]

Use INSERT...SELECT syntax:

	http://www.mysql.com/doc/en/INSERT_SELECT.html

-steve


At 12:01 PM -0500 1/14/03, Michael Knauf/Niles wrote:

Ok, this has got to be easy, but I'm not getting it right...


I have a table, products containing a 944 rows. One of the fields is
fgNumber,

I have another table, categoryRelatedToProducts which also has an fgNumber
field and currently has no data.

I'd like to add all 944 fgNumbers to the categoryRelatedToProducts table.

Can I do this with one sql statement?

Michael




--
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| SETI@Home: 1001 Work units on 23 oct 2002  |
| 3.152 years CPU time, 3.142 years SETI user... and STILL no aliens...  |
++

-
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

Re: Avg_row_length

[Steve Edberg]

I believe there is an extra byte for the 'null' flag; if you declare
this column to be NOT NULL, I'll bet the avg length will be 10.

-steve

At 10:43 PM +0100 1/14/03, Stefan Hinz wrote:

Dear gurus,

here's my final stupid question for today. Why is the average row length
11 when it should be 10?

mysql DESCRIBE myrowisam;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
| col   | char(10) | YES  | | NULL|   |
+---+--+--+-+-+---+

mysql SHOW TABLE STATUS LIKE 'myrow%';
+-+++--++
| Name| Type   | Row_format | Rows | Avg_row_length |
+-+++--++
| myrowisam   | MyISAM | Fixed  |   68 | 11 | ---

TIA,
--
  Stefan Hinz [EMAIL PROTECTED]
  Geschäftsführer / CEO iConnect GmbH http://iConnect.de
  Heesestr. 6, 12169 Berlin (Germany)
  Tel: +49 30 7970948-0  Fax: +49 30 7970948-3





--
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| SETI@Home: 1001 Work units on 23 oct 2002  |
| 3.152 years CPU time, 3.142 years SETI user... and STILL no aliens...  |
++

-
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

Re: Limit and Order by

[Steve Edberg]

At 2:00 PM -0800 12/5/02, Michelle de Beer wrote:

How can I limit the result after the order by has
been executed? This stops efter 100 rows and the
result is not as I intended...

Select * from mytable ORDER by total desc limit 0, 100

Must this be done in PHP?




Perhaps you could tell us what you DO intend? Because the statement 
above does indeed limit the results to the highest 100 totals - the 
limit is done after the order by.

-steve

Sql! Query!

--
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| SETI@Home: 1001 Work units on 23 oct 2002  |
| 3.152 years CPU time, 3.142 years SETI user... and STILL no aliens...  |
++

-
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

Re: LIMIT in MySQL

[Steve Edberg]

At 12:56 PM -0500 11/26/02, Mike At Spy wrote:

I must not be awake yet.  Why is this query sending me back 60 records?
Shouldn't it only send back records 30 through 60 (i.e. 30 records)?

SELECT * FROM table ORDER BY somefield LIMIT 30,60

Thanks,

-Mike



As computers are wont to do, it's sending back what you asked for ;)

The first LIMIT parameter is the starting row (starting at 0), the 
second is the number of records to return. So it's returning the 31st 
through 90th records.

CHeck about 1/2 thee way down

	http://www.mysql.com/doc/en/SELECT.html

for more info.


-steve
--
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| SETI@Home: 1001 Work units on 23 oct 2002  |
| 3.152 years CPU time, 3.142 years SETI user... and STILL no aliens...  |
++

-
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

Re: Bug with limit clause

[Steve Edberg]

Not a bug or feature...

As you note, you aren't using an ORDER BY clause in these queries. 
Thus, MySQL is free to return the rows in any order it sees fit. 
Usually, they'll appear in the order they have been inserted, but 
there's no guarantee, especially if there have been 
insertions/deletions.

Remember that the result of a SELECT is an unordered set unless you 
explicitly specify an order.

	-steve


At 12:26 AM +0100 11/5/02, Dirk Hillbrecht wrote:
Hello MySQL AB,

I want to inform you about a strange behaviour I just had with the 
MySQL server. Probably it's a bug, probably I've not read the docs 
good enough. Here it goes:

Situation: My application splits huge queries into a bunch of 
smaller ones using the limit clause. One can doubt whether this is 
good style or not, but years earlier this was the only way to handle 
large ResultSets (it's a Java app) and preformance is not that bad...

So, I have two tables, person and persontoorg which can be 
joined on person.nr=persontoorg.person. I perform a certain select 
which reads like this:

select distinct 
person.name,person.prename,person.street,person.streetnr,person.country,person.postalcode,person.city,person.belongs,person.telnr, 
person.telnr2,person.faxnr,person.faxnr2,person.email,person.misc,persontoorg.nrinorg 
from person,persontoorg where persontoorg.person=person.nr and 
persontoorg.orgnr=1

When I do this, I get 4251 rows in the result.

Now, I split this query via the way mentioned above. So, my first query is

---
select distinct 
person.name,person.prename,person.street,person.streetnr,person.country,person.postalcode,person.city,person.belongs,person.telnr, 
person.telnr2,person.faxnr,person.faxnr2,person.email,person.misc,persontoorg.nrinorg 
from person,persontoorg where persontoorg.person=person.nr and 
persontoorg.orgnr=1
limit 16
---

then I query

---
select distinct 
person.name,person.prename,person.street,person.streetnr,person.country,person.postalcode,person.city,person.belongs,person.telnr, 
person.telnr2,person.faxnr,person.faxnr2,person.email,person.misc,persontoorg.nrinorg 
from person,persontoorg where persontoorg.person=person.nr and 
persontoorg.orgnr=1
limit 16,15
---

next is

---
select distinct 
person.name,person.prename,person.street,person.streetnr,person.country,person.postalcode,person.city,person.belongs,person.telnr, 
person.telnr2,person.faxnr,person.faxnr2,person.email,person.misc,persontoorg.nrinorg 
from person,persontoorg where persontoorg.person=person.nr and 
persontoorg.orgnr=1
limit 31.15
---

and so on. As I expect, the chunks' contents are equal to the result 
of the one, large query without limit. With one exception: The 
very last query

---
select distinct 
person.name,person.prename,person.street,person.streetnr,person.country,person.postalcode,person.city,person.belongs,person.telnr, 
person.telnr2,person.faxnr,person.faxnr2,person.email,person.misc,persontoorg.nrinorg 
from person,persontoorg where persontoorg.person=person.nr and 
persontoorg.orgnr=1
limit 4246,15
---

delivers _not_ the last five entries I expect, but five different 
entries of the table which have already been delivered earlier. So, 
I get some doubles, but the last entries are missing. Took me about 
three hours to find this odd behaviour...

When I do ...limit 4246,X in the clause, X in (1,2,3,4) gives the 
correct entries from the end of the table while X=5 just jumps in 
its middle again.

Workaround: Rewrite the query using some order clause. While this 
is shuffling all entries compared to before, it seems to guarantee 
that all entries are processed and transmitted.

Now my question: Bug or feature? Server is 3.23.48, SuSE 8.0.

Best regards,
Dirk

--
--- Dirk Hillbrecht
- chitec OHG, Vahrenwalder Str. 7/TCH, 30165 Hannover
- Tel.: +49/511/9357-840, Fax: +49/511/9357-849
- eMail: [EMAIL PROTECTED], Web: http://www.chitec.de


--
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| SETI@Home: 1001 Work units on 23 oct 2002  |
| 3.152 years CPU time, 3.142 years SETI user... and STILL no aliens...  |
++

-
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

Re: padding field with zeros

[Steve Edberg]

See the ZEROFILL column attribute:

http://www.mysql.com/doc/en/Column_types.html

http://www.mysql.com/doc/en/Numeric_types.html


If necessary, you can use an ALTER TABLE command to add that 
attribute to the appropriate columns.

- steve


At 10:11 AM -0700 10/11/02, Bryan Koschmann - GKT wrote:
Hi,

Does MySQL provide for any way to pad a field? I have accounts numbers
that vary from 3-5 digits, and would like them 8 digits padded by zeros
(479 becomes 0479, 17234 becomes 00017234, etc). I know I could
probably write a script to fix that before entering the data into the
database, but I just wanted to know. Or maybe there is a way to pad it
while doing the query select?

Thanks,

   Bryan



-- 
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| The end to politics as usual:  |
| The Monster Raving Loony Party (http://www.omrlp.com/) |
++

-
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

Re: where clause question

[Steve Edberg]

Well, it depends on what exactly you mean by 'checking' Bfn1, Bfn2 
and Bfn3, but you should be able to do something like this (assuming 
for this example that you want only want to return results where 
Bfn1/Bfn2/Bfn3 is equal to 99):

select *
from table_a as A, table_b as B
where
   (case A.Afn when 1 then B.Bfn1 when 2 then B.Bfn2 when 3 then B.Bfn3 end)
   = 99

Or you could nest IFs:

select *
from table_a as A, table_b as B
where
   if(A.Afn = 1, B.Bfn1, if(A.Afn = 2, B.Bfn2, B.Bfn3)) = 99

Neither of these does error checking if A.Afn not in {1,2,3}.

See

http://www.mysql.com/doc/en/Control_flow_functions.html

for more info.

-steve



At 12:33 PM +1000 10/7/02, Peter Goggin [EMAIL PROTECTED] wrote:
This requires a similar function to Oracles decode. I do not know if MySQL
provides such a function.

Regards

Peter Goggin

- Original Message -
From: Alex Shi [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, October 07, 2002 6:47 AM
Subject: where clause question


  Hi,

  I need a where clause in following situation:

  Say I want to query two tables: A and B. In table A there is field Afn,
  while in table B there ere 3 fields: Bfn1, Bfn2 and Bfn3. I want to
  compose a query, in which the where clause can do this:

  if A.Afn=1, then check Bfn1,
  if A.Afn=2, then check Bfn2,
  if A.Afn=3, then check Bfn3.

  So how I compose a where clause to do this? Thanks in advance!

   Alex Shi


-- 
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| The end to politics as usual:  |
| The Monster Raving Loony Party (http://www.omrlp.com/) |
++

-
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

Re: Help with WHERE string searching

[Steve Edberg]

I'd try using MySQL's regular expression functions:


Select  where title regexp '[[::]]m[ae]n[[::]]'

or if you are generating this query programmatically, it might be 
simpler to do something like

Select

where
title regexp '[[::]]man[[::]]' or
title regexp '[[::]]men[[::]]'

The [[::]]... [[::]] patterns match word boundaries, including 
beginning- and end of lines. See

http://www.mysql.com/doc/en/Regexp.html

for more info.

You might also want to consider a fulltext index on the title:

http://www.mysql.com/doc/en/Fulltext_Search.html

-steve



At 7:38 PM -0700 9/6/02, Rob Gambit wrote:
Hello MySQL mailing list.

I am having trouble creating a SQL statement for
searching.

Suppose I have a field named title that contains one
of these
I am a Man
I am a Woman
We are Men
We are Women

Now I am trying to search that field using keywords,
for example, I want to return any that contain the
word man or men but not woman or women

WHERE (title LIKE 'man') OR (title LIKE 'men')

but that doesn't return anything. I tried

WHERE (title LIKE '%man%') or (title like '%men%')

but that returns everything. I tried using the _
instead of % but the word may or may not be at the end
or beginning of the line.

Can someone point me in the right direction (or tell
me how to do it)

Thanks.  Sorry for the newbie question.

Robert


-- 
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| The end to politics as usual:  |
| The Monster Raving Loony Party (http://www.omrlp.com/) |
++

-
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

Re: Searching text in a big table

[Steve Edberg]

Check out full-text indexing:

http://www.mysql.com/doc/en/Fulltext_Search.html

I don't think there's any other way to improve searching in 
unstructured text using MySQL

-steve


At 3:58 PM -0700 8/27/02, Sanny Sun wrote:
Hi there,
I have a big table which has 25 rows.And each row has a BLOB field
which stores lots of text. When I search text in this table(using the query:
where CONTENT like '%news%'),the searching speed is quite slow. is there
anybody also have such problem? Any ideas about improving the speed of
searching text in big table?

Thanks in advance.



-- 
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| The end to politics as usual:  |
| The Monster Raving Loony Party (http://www.omrlp.com/) |
++

-
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

Re: changing coulmn

[Steve Edberg]

I wasn't entirely sure what you wanted - do you want to change the 
table's column names? If so, then do what the message below suggests. 
And yes, you can do it all in one query:

alter table mytest
change my_1 my_file_1 varchar(5),
change my_2 my_file_2 varchar(5),
...and so on

See

http://www.mysql.com/doc/A/L/ALTER_TABLE.html

for more info.

If my_1, etc are VALUES in a column, and you want to modify them, you 
can do this:

update your_table_name
set your_column_name =
   concat('my_file_', substring(your_column_name, 4))

See

http://www.mysql.com/doc/S/t/String_functions.html

for more info.

-steve


At 2:32 PM -0400 7/2/02, Anil Garg wrote:
thanx for ur mail, m sorry in dint mention that infact its a big table...
Is there a way i can do it in all the columns using only one query ??
thanx
anil
- Original Message -
From: nellA hciR [EMAIL PROTECTED]
To: Anil Garg [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Tuesday, July 02, 2002 2:14 PM
Subject: Re: changing coulmn


  mysql create table mytest (
   - my_1 varchar(5)
   - );
  Query OK, 0 rows affected (0.32 sec)

   mysql alter table mytest change my_1 my_file_1 varchar(5);
  Query OK, 0 rows affected (0.23 sec)
  Records: 0  Duplicates: 0  Warnings: 0

  mysql show columns from mytest;
  +---++--+-+-+---+
  | Field | Type   | Null | Key | Default | Extra |
  +---++--+-+-+---+
  | my_file_1 | varchar(5) | YES  | | NULL|   |
  +---++--+-+-+---+

  On Tuesday, July 2, 2002, at 09:36 , Anil Garg wrote:

   in my table a column has enties
   my_1
   my_4
   my_5
  
   i wnat to change it to
my_file_1
my_file_4
my_file_5
  



-- 
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| The end to politics as usual:  |
| The Monster Raving Loony Party (http://www.omrlp.com/) |
++

-
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

Re: timestamp problem ..

[Steve Edberg]

This is exactly what timestamp columns are supposed to do - see

http://www.mysql.com/doc/D/A/DATETIME.html

The first timestamp column in the table will be automatically updated 
upon insert/update. Your choices are:

(1) Change to datetime type. Then, on insert, insert the current date 
via now(). It will not be updated unless you specifically change it.

(2) Add a second timestamp column; only the FIRST timestamp column is 
updated as in (1) above. See the docs for mor info.

(3) Keep the column type as timestamp, but always explicitly insert 
the desired date/time - that way, the value won't be automatically 
set. This is probably the least desireable option, though.

If I were me, I'd go with (1). Or, depending on your needs, a 
combination of timestamp  datetime columns.

Now about that beer... ;)

-steve



At 3:53 PM +0200 6/14/02, Wouter van Vliet wrote:
Heey Folks,

I'm having a slight problem with the timestamp column format. When I alter a
table and, add a column of type timestamp all records get the current
timestamp, that's ok. When i insert a new row, all records get the current
timestamp. That too is ok. But now, when I update one row of the table, that
row gets a new timestamp. And that's not what i'd like it to do. Does
somebody have any idea on how this can be prevented?

I've tried to make the column of type int(14) and then set now() or
UNIX_TIMESTAMP as default value, but that just results in a very well known
error 1064 (You have an error in your SQL syntax near 'NOW()' at line 1).

Thanks !
Wouter

(ps. beer for the helper .. if you'd come up with some idea to give it to
you)



-- 
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| If only life would imitate toys. |
|  - Ted Raimi, March 2002   |
|  - http://www.whoosh.org/issue67/friends67a.html#raimi |
++

-
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

RE: Is the order same?

[Steve Edberg]

At 12:00 PM -0500 6/14/02, Jay Blanchard wrote:
[snip]
When I say 'select * from table', can I always be assured of the
order in which the results are retrieved. If I issue the query a second
time, (assuming no new insertions on the table), will I get the results in
the same order, again?
[/snip]

Yes. Data is always sorted from first entered to last entered unless you
change the sort with an ORDER BY or GROUP BY.


...unless you do any deletes and subsequent inserts, or you optimize 
the table, or MySQL decides to change internal record ordering, or...

As a general rule in relational databases, results should always be 
considered an unordered set unless you EXPLICITLY specify an ORDER BY.

- steve


HAGW!

Jay
sql, mysql, query



-- 
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| If only life would imitate toys. |
|  - Ted Raimi, March 2002   |
|  - http://www.whoosh.org/issue67/friends67a.html#raimi |
++

-
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

RE: Reconstructing SQL create table statements

[Steve Edberg]

There's also the SHOW CREATE TABLE tablename sql command:

http://www.mysql.com/doc/S/H/SHOW_CREATE_TABLE.html

According to

http://www.mysql.com/documentation/mysql/bychapter/manual_News.html#News-3.23.x

this command is available from 3.23.20 on.

-steve


At 4:39 PM -0400 6/14/02, Don Vu [EMAIL PROTECTED] wrote:
if you do mysqldump -d -p -u USERNAME DATABASENAME  FILENAME 
then it will pipe only the CREATE TABLE statements and no insert 
statements into FILENAME.

-Don

-Original Message-
From: Erik Price [mailto:[EMAIL PROTECTED]]
Sent: Friday, June 14, 2002 4:39 PM
To: Hihn Jason
Cc: [EMAIL PROTECTED]
Subject: Re: Reconstructing SQL create table statements



On Friday, June 14, 2002, at 04:06  PM, Hihn Jason wrote:

  I have a large number of tables that have been created through the
  years,
  and I wish to obtain the SQL statements used to create them. I can go
  through and do it all by hand, but that would take forever. Is there a
  way
  to run a script against the database that will generate them for me? If
  it
  misses the occasional additional index, then that is fine.

If you have the mysql client programs and are using a Unix machine
(maybe even Win but I'm not sure) you can use the mysqldump program.  It
is usually located in the bin directory of your MySQL distribution. 
Mine is /usr/local/mysql/bin/mysqldump.

Read up on it, it can dump all data from your database and does so with
the CREATE TABLE statements attached so that the whole thing can
literally be rebuilt from scratch.  Just chop off the contents if you
only want the CREATE TABLE statements.


Erik






Erik Price
Web Developer Temp
Media Lab, H.H. Brown
[EMAIL PROTECTED]



-- 
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| If only life would imitate toys. |
|  - Ted Raimi, March 2002   |
|  - http://www.whoosh.org/issue67/friends67a.html#raimi |
++

-
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

Re: System Table

[Steve Edberg]

Take a look at the 'mysql' database; the relevant tables are 'user' and 'ub'.

See:
http://www.mysql.com/doc/P/r/Privileges.html
And
http://www.mysql.com/doc/P/r/Privilege_system.html
http://www.mysql.com/doc/U/s/User_Account_Management.html

If your naming scheme is to name the private database after the user, 
just do something like

select count(*) from db where Db='insert username here'

I just remembered - there's also a username field ('User') associated 
with each db in the db table...

-steve


At 11:53 AM +0100 6/5/02, Niall Merrigan wrote:
Hi List

I am just wondering is there a sysusers / systables  table in mySQL (like
SQL Server).

I am creating an multi user script whereby users will be able to create
their own db on a linux box, but I will be only allowing one database per
user.  I would like to do a lookup on a system table if it exists or if it
doesnt I will create a table to model this.

Thanks

Niall



Niall Merrigan ASP Development Engineer
Piercom Ltd, Holland Road, National Technological Park, Limerick

http://www.piercom.ie  http://nm.csn.ul.ie
Phone: +353-61-201917  Fax: +353-61-355051



-- 
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| If only life would imitate toys. |
|  - Ted Raimi, March 2002   |
|  - http://www.whoosh.org/issue67/friends67a.html#raimi |
++

-
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

Re: doc archive?

[Steve Edberg]

I believe the docs for whatever version you downloaded are included 
in the full source distribution; they are at the top level of the 
source tree:

manual_toc.html
manual.html
manual.txt

But yes, it would be nice for the docs in various formats for 
previous - or at least recent - versions of MySQL to be archived 
somewhere. Who knows, they might be somewhere on the mysql.com site - 
I haven't looked.

-steve


At 11:05 AM -0400 6/5/02, Gary Delong wrote:
Is there an archive of the MySQL doc? The doc at mysql.com is
for v 4.0 alpha and I'd like to get the doc for v 2.23.49
which I just downloaded. (As a new user I'd like to be able
to RTFM before bugging the list with dumb questions. I'll
still probably ask dumb questions, but at least I'll have
made an attempt...)

Thanks much
--Gary

-
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


-- 
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| If only life would imitate toys. |
|  - Ted Raimi, March 2002   |
|  - http://www.whoosh.org/issue67/friends67a.html#raimi |
++

-
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

Re: I need 50.000 inserts / second

[Steve Edberg]

Depending on your available RAM  length of your sampling runs, you 
could write records to heap (in-memory) tables -

http://www.mysql.com/doc/H/E/HEAP.html

- and then dump those to disk after the sample was done. You might 
even be able to use heap tables as a buffer with one process 
acquiring data to the heap table, another writing records to disk  
deleting from the heap table. Never used heap tables myself, though.

-steve


At 1:49 AM -0300 5/31/02, Cesar Mello - Axi wrote:
Hello,

I intend to use MySQL in a data acquisition software. The actual version
stores the acquired data straight in files. The sample rate can get up to 50
kHz. I would like to know if there is some way to improve MySQL insert rate.
The following C++ code with mysql++ takes 5 seconds to execute in my Athlon
1.33 machine:

sql_create_2 (teste1, 1, 2, double, datahora, double, valor1)

int main() {
   try { // its in one big try block

 Connection con(use_exceptions);
 con.connect(cesar);
 Query query = con.query();

 teste1 row;
 // create an empty stock object

  for (int i=1;i5;i++)
  {
   row.datahora = (double) i;
   row.valor1 = i / 1000;

   query.insert(row);
   query.execute();
  }


As you can see there are only two fields: a double timestamp and a double
value. In the real application there are some more double values. I need to
decrease this time to less than 1 second. Is there any kind of buffered
inserts or maybe a way that I could pass a matrix?

I'm shocked with the performance of MySQL, a similar query to compute 1
million records takes 1.17 seconds in MySQL and around 6 seconds in the
current system. So if I can decrease the insert time I'll definetly use
MySQL!

Thank you for the attention.

Best regards,
Cesar



-- 
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| If only life would imitate toys. |
|  - Ted Raimi, March 2002   |
|  - http://www.whoosh.org/issue67/friends67a.html#raimi |
++

-
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

Re: mysql-3.23.50

[Steve Edberg]

At 12:44 PM +0200 5/30/02, Inbal Ovadia wrote:
Hi all
i have mysql-3.23.41
should i install 3.23.50?
what are the differences between this versions?


http://www.mysql.com/doc/N/e/News-3.23.x.html



-- 
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| If only life would imitate toys. |
|  - Ted Raimi, March 2002   |
|  - http://www.whoosh.org/issue67/friends67a.html#raimi |
++

-
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

Re: Problem with DDL

[Steve Edberg]

'when' is a reserved word:

http://www.mysql.com/doc/R/e/Reserved_words.html

I don't think MySQL is using it yet, but it's probably forbidden by 
the ANSI SQL standard. If you really want to use it as a column name, 
I think you can quote it -

CREATE TABLE news(..., 'when' timestamp, )

- but it's probably best to pick another name that's not in the reserved list.

-steve


At 4:22 PM -0400 5/13/02, Elliot L. Tobin wrote:
I pulled this DDL from a MySQL server and am trying to load it into a
MySQL server runinng 3.22.49, but I get errors with the DDL.

CREATE table news (
  id smallint(5) unsigned DEFAULT '0' NOT NULL auto_increment,
  subject varchar(80),
  when timestamp(14),
  body text,
  who smallint(5) unsigned DEFAULT '0' NOT NULL,
  PRIMARY KEY (id)
);

ERROR 1064 at line 3: You have an error in your SQL syntax near 'when
timestamp(14),
   body text,
   who smallint(5) unsigned DEFAULT '0' NOT NULL' at line 4


--

Any help is appreciated..  Please email me directly..



-- 
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| If only life would imitate toys. |
|  - Ted Raimi, March 2002   |
|  - http://www.whoosh.org/issue67/friends67a.html#raimi |
++

-
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

Re: change displayed text results?

[Steve Edberg]

Hmmm -

Off the top of my head, how about:

select
   order, if(SUM(cat1+cat2)=0, '-', SUM(cat1+cat2)) as sum
from
   tbl
group by
   order

Although you might need to quote the column name 'order', since that 
is a reserved word:

select
   'order', ...

group by
   'order'


I think there's probably a cleaner way to do it, but that _should_ work...

- steve


At 2:55 PM -0200 5/9/02, [EMAIL PROTECTED] wrote:
Hello dear all,

I need to make a simple select query like this:
select order, SUM(cat1+cat2) from tbl
when the result of the SUM is =0 i need to change this 0 and display a
character like '-' .

Is possible to do this? and how?

thanks in advance,

regards
fabrizio



-- 
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| If only life would imitate toys. |
|  - Ted Raimi, March 2002   |
|  - http://www.whoosh.org/issue67/friends67a.html#raimi |
++

-
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

Re: Rounding times

[Steve Edberg]

Well, assuming the time is in seconds (as in a UNix timestamp), you 
could use rounding function:

select 600 * round(time_in_seconds/600)

(600 being # of seconds in 10 minutes).

Read the docs for the round() function -

http://www.mysql.com/doc/M/a/Mathematical_functions.html

- for some caveats in round behavior. If your system doesn't round 
the way you want, you'll have to get a bit more complex:

select 600 * floor((time_in_seconds+300)/600)

This will round 00:05:00, for example up to 00:10:00; use 299 instead 
of 300 if you want to round down.

To do conversions to/from various date  time formats, see

http://www.mysql.com/doc/D/a/Date_and_time_functions.html

Standard warnings about off-top-of-head untested code apply.

Also, depending on the format of your database, you might be able to 
group your data using date_add()/date_sub() functions and INTERVAL 
operator. See the date  time functions link above.

-steve



At 2:33 PM +0100 5/9/02, Peter Hicks wrote:
Hi everyone

I have a time value in MySQL that I want to round to the nearest ten minutes -
for example, 00:32:15 should round to 00:30:00, and 00:48:05 should round to
00:50:00.

I have racked my brains over this, and I can't work out how to do this. I'm
attempting to write a query which will create ten-minute summaries from a
data-set.

Anyone help?

Best wishes,

Peter.



-- 
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| If only life would imitate toys. |
|  - Ted Raimi, March 2002   |
|  - http://www.whoosh.org/issue67/friends67a.html#raimi |
++

-
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

Re: problem

[Steve Edberg]

At 1:10 PM +0300 4/19/02, cristian ditoiu wrote:
hi , i have a little mysql problem .
:
got 2 servers .. 1 got a dump from server1 via myadmin , but when trying to
insert the data into server 2 i got a strange errror and i got disconected
from server2 .

any ideeas ?

--
Your MySQL connection id is 752533 to server version: 3.23.37-log

Type 'help' for help.

mysql INSERT INTO ref_data VALUES (186, 139, 'Tess of  the D\'Urbervilles',
'THOMAS HARDY\r\nTESS OF THE D'URBERVILLES\r', 'tess.DOC', '2002-02-06',

...^

There's an unescaped ' there; if you created this dump from 
phpmydmin, perhaps there's a quote handling bug somewhere...

That's a strange error, though, since AFAIK there's no place to put a 
hostname in an INSERT.

-steve


648, 15872, 1, '', 10);
ERROR 2005: Unknown MySQL Server Host ''tess.DOC',' (2)
mysql
--


-- 
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| If only life would imitate toys. |
|  - Ted Raimi, March 2002   |
|  - http://www.whoosh.org/issue67/friends67a.html#raimi |
++

-
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

Suggestion re: floating point comparison (was Re: Problem with where clause)

[Steve Edberg]

...perhaps a NEAR function could be added; as a config file or compile-time 
option, you could define an accuracy range. Say,

 ./config --with-epsilon=0.0001

(if memory of my numerical analysis classes serves, the 'fudge factor' was 
conventionally symbolized by epsilon; I suppose you could make it 
--with-fudge, but this ain't no bakery) so that

 select 1.0 near 1.9 = 1
and
 select 1.0 near 0.9995 = 0

The default would be current behavior (epsilon=0).

This would avoid statements like

 select * where x  0. and x  1.0001

...just my too sense -

 -steve

At 02:33 PM 2/1/02 , James Montebello [EMAIL PROTECTED] wrote:

Yes, but you need to use the decimal (fixed-point) type, not the floating
point type.  Any program that's directly comparing FP numbers for exact
matches is simply wrong, and certainly won't be portable, even if it works
in one particular environment.  Fixed-point numbers CAN be compared for
exact matches, and they can include fractional values.

james montebello

On Fri, 1 Feb 2002, Jim Dickenson [EMAIL PROTECTED] wrote:

  Am I to assume that based on your response that one should never use a 
 float
  field type if you ever want to select the data?
 
  This causes a big problem for the way MyODBC 3.51 has been implemented. I
  was actually debugging a problem I had in MyODBC when I ran across 
 this. The
  way MyODBC works is that is generates a native SQL statement. In my 
 case the
  statement was:
 
  UPDATE `junk` SET `record`=  91 WHERE record=1 AND title='This is item one'
  AND num1=12.3 AND num2=134 AND num3=0.100 AND code='abc' AND
  sdate='1991-11-30' AND stime='17:45:00' LIMIT 1
 
  Since the float compare did not work (field num1), the record I wanted
  changed did not get changed.
 
  The way our software works is that it fixes the variable number (num1) to
  the number of decimals in the constant number before the compare is done.
  There are ways for compares to be programmed so they do work. We are using
  computers after all.
 
 
  On 2/1/2002 2:06 PM, Gerald Clark [EMAIL PROTECTED]
  wrote:
 
   A floating point number can never be equal to 12.3.
   It can be close, and with rounding display as 12.3, but it
   won't actually be equal to 12.3.
   Use a decimal type instead.
  
  
   Jim Dickenson wrote:
  
   I am running mysql  Ver 11.15 Distrib 3.23.47, for pc-linux-gnu (i686)
   installed from a binary RPM file. This is using RedHat Linux 7.2.
  
   I have a table described as:
   mysql describe junk;
   ++---+--+-+-+---+
   | Field  | Type  | Null | Key | Default | Extra |
   ++---+--+-+-+---+
   | record | decimal(3,0)  | YES  | | NULL|   |
   | title  | varchar(250)  | YES  | | NULL|   |
   | num1   | float | YES  | | NULL|   |
   | num2   | decimal(6,0)  | YES  | | NULL|   |
   | num3   | decimal(10,3) | YES  | | NULL|   |
   | code   | char(3)   | YES  | | NULL|   |
   | sdate  | date  | YES  | | NULL|   |
   | stime  | time  | YES  | | NULL|   |
   ++---+--+-+-+---+
   8 rows in set (0.00 sec)
  
   It has the following data:
   mysql select record,num1 from junk;
   ++--+
   | record | num1 |
   ++--+
   |  1 | 12.3 |
   |  2 |   17.785 |
   |  3 |  138.981 |
   |  4 |   -34.12 |
   |  5 | -12.7365 |
   |  6 |-0.34 |
   ++--+
   6 rows in set (0.00 sec)
  
   The following command does not update the row I would like it to:
   mysql update junk set num1=12.4 where num1=12.3;
   Query OK, 0 rows affected (0.01 sec)
   Rows matched: 0  Changed: 0  Warnings: 0
  
  
   Can I get some insight as to what the problem might be?
  
   Thanks,
  


++
| Steve Edberg  [EMAIL PROTECTED] |
| Database/Programming/SysAdmin(530)754-9127 |
| University of California, Davis http://pgfsun.ucdavis.edu/ |
+-- Gort, Klaatu barada nikto! --+


-
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

Re: pb with integrity constraint

[Steve Edberg]

See doc sections:

http://www.mysql.com/doc/A/N/ANSI_diff_Foreign_Keys.html
and
http://www.mysql.com/doc/C/R/CREATE_TABLE.html

 From the CREATE TABLE docs:

The FOREIGN KEY, CHECK, and REFERENCES clauses don't 
actually do anything. The syntax for them is provided only for 
compatibility, to make it easier to port code from other SQL servers 
and to run applications that create tables with references. See 
section 1.7.4 MySQL Differences Compared to ANSI SQL92.

And, judging by the .fr domain, you might be interested in French 
translations of the docs:

http://dev.nexen.net/docs/mysql/chargement.html


-steve


At 11:46 AM +0100 1/15/02, TOMASSONI Dominique wrote:
Hi the list,

I try to create tables with integrity constraint but it seems no running.

I've got the next script :

create table USERS
(
 USER_CODE   INT(8) not null,
 USER_NAME   VARCHAR(30)not null,  
 primary key (USER_CODE)
)
;
create table ENV
(
 USER_CODE   INT(8) not null,
 CODE_ENVVARCHAR(6) not null,
 primary key (USER_CODE, CODE_ENV),
 constraint FK_USER foreign key (USER_CODE) references USERS(USER_CODE)
)
;

The tables creation are ok, but when I insert values in the table ENV
without any data in USERS it works but normally it wouldn't.

Something bad in may script ?



-- 
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| Restriction of free thought and free speech is the most dangerous of  |
| all subversions. It is the one un-American act that could most easily  |
| defeat us.|
| - Supreme Court Justice (1939-1975) William O. Douglas |
++

-
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

Re: Inserting Object property in MySQL

[Steve Edberg]

THis is more of a PHP issue, but:

When PHP evaluates variables in a double-quoted string, it sees your $this 
(the object reference) and immediately evaluates it (just returning its 
type - Object). If you want to tell PHP to dereference $this-PNPSES-ses 
properly, do

 $query = INSERT INTO 
table1(created,updated,ses) values(now(),now(),'.$this-PNPSES-ses.');
or
 $query = INSERT INTO 
table1(created,updated,ses) values(now(),now(),'{$this-PNPSES-ses}');

The later method should work, according to the docs, but I haven't tried 
it. For more info, see:

 http://www.php.net/manual/en/language.types.string.php
and
 http://www.php.net/manual/en/language.types.string.php#language.typ 
http://www.php.net/manual/en/language.types.string.php#language.types.string 
.parsing


-steve


At 05:19 PM 1/11/02 , [EMAIL PROTECTED] wrote:
I've got the following query executing in my php code:

$query = INSERT INTO 
table1(created,updated,ses) values(now(),now(),'$this-PNPSES-ses');
MYSQL_QUERY($query);

query executes fine, but when I look at the ses column in inserted row it 
says Object-ses instead of a value.

Any ideas why?



++
| Steve Edberg  [EMAIL PROTECTED] |
| Database/Programming/SysAdmin(530)754-9127 |
| University of California, Davis http://pgfsun.ucdavis.edu/ |
+-- Gort, Klaatu barada nikto! --+


-
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

Re: RedHat DB?!

[Steve Edberg]

Did you try a google search - say

http://www.google.com/search?q=%22redhat+database%22

? The first link I saw (to RedHat itself) pretty much answers the question...

- steve


At 2:53 PM +0545 12/18/01, Deependra B. Tandukar wrote:
Greetings !

What is RedHat Database? Is anybody using it? How is it? Better than MySQL?

Looking forward to hearing from you.

Regards,
DT


-- 
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| Restriction of free thought and free speech is the most dangerous of  |
| all subversions. It is the one un-American act that could most easily  |
| defeat us.|
| - Supreme Court Justice (1939-1975) William O. Douglas |
++

-
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

Re: Order By number of rows returned ?

[Steve Edberg]

Hi -

I had a nagging feeling that there was a better solution to this than 
the temporary table-based solutions I saw; I created a table 'test' 
with the data you have below, and played with queries a bit. I came 
up with this, seems to work:


select
a.web_account,a.code_short,sum(if(a.web_account=b.web_account,1,0)) as c
from
test as a,test as b
group by
concat(a.web_account,a.code_short)
order by
c desc;

+-++--+
| web_account | code_short | c|
+-++--+
| J009| G  |3 |
| J009| U  |3 |
| J009| S  |3 |
| A007| U  |2 |
| A007| S  |2 |
| B001| U  |1 |
+-++--+
6 rows in set (0.01 sec)


-steve



At 1:12 PM + 12/14/01, Girish Nath wrote:
Hi

I'm trying to do some sorting by relevance on a query. Essentially, i'd like
to know if there is way to order the results by number of rows returned or
if this is the best i can get and do the rest within PHP?

mysql SELECT web_account, code_short FROM lookup WHERE code_short IN ('U',
'S', 'G');

+-++
| web_account | code_short |
+-++
| A007| U  |
| A007| S  |
| J009| G  |
| J009| U  |
| J009| S  |
| B001| U  |
+-++
6 rows in set (0.00 sec)

I'd like to order these so that J009 would be grouped at the top of the
set because it was found in 3 rows, A007 would be placed after J009 with
B001 last.

Any ideas :) ?

Thanks for your time.

Girish



-- 
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| Restriction of free thought and free speech is the most dangerous of  |
| all subversions. It is the one un-American act that could most easily  |
| defeat us.|
| - Supreme Court Justice (1939-1975) William O. Douglas |
++

-
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

Re: Inches and Degrees

[Steve Edberg]

Well, as far as the inches and fractions thereof go, you could always 
normalize your data to the smallest measurement - for example: if the 
smallest increment is 1/32 of an inch, store all measurements in terms of 
32nds. So, 1 = 32, 1-5/32 = 37, and so on. Then you could store the 
values in an integer column. Or, use a NUMERIC column (stored as strings, 
so there is no roundoff error as there is with floating-point numbers)  and 
convert to metric: 1-5/32 = 29.36875mm exactly. Convert measurements 
to/from english as needed for input or display. If you will be dealing with 
metric measurements in the near future, that might be a more reasonable way 
to go.

Lastly, if you covert to metric and want to use a floating point column, read

 http://www.mysql.com/doc/P/r/Problems_with_float.html

first.

If I were me, and the site wasn't dealing in many metric tools, I'd 
probably use the first method.


At 07:43 PM 12/12/01 , Jonathan Duncan wrote:
I am creating a database for a website that sells tools.  Unfortunately they
aren't measured in metric.  Most of the measurements look something like:

1 1/16
3/8
3/16
1 5/32
45º
10º
2 7/16

These aren't pretty numbers to enter into a database.  The simple way would
be to enter them as CHAR's and forget about them, but then searching on that
could cause problems.

Does anyone have any idea what would be the best way to enter these number
and as what type?

Thanks in advance,
Jonathan Duncan


++
| Steve Edberg  [EMAIL PROTECTED] |
| Database/Programming/SysAdmin(530)754-9127 |
| University of California, Davis http://pgfsun.ucdavis.edu/ |
+-- Gort, Klaatu barada nikto! --+


-
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

Re: specific command or mySQL++ command

[Steve Edberg]

At 6:55 PM -0500 12/8/01, Mike Gleason Jr Couturier wrote:
Hello list !

Is there a command to know the current database name
the user is using ...



See

http://www.mysql.com/doc/M/i/Miscellaneous_functions.html





Or in mySQL++, is there a way to know the current database
the user is using ?

Thanks !



-- 
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| Restriction of free thought and free speech is the most dangerous of  |
| all subversions. It is the one un-American act that could most easily  |
| defeat us.|
| - Supreme Court Justice (1939-1975) William O. Douglas |
++

-
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

Re: Can this be done?

[Steve Edberg]

How about

UPDATE
   myTable
SET
   DueField = 0,
   StatusField = if(StatusField='O', 'C', StatusField)
WHERE
   PrimaryKeyField = 'XYZXYZ'

See

http://www.mysql.com/doc/C/o/Control_flow_functions.html

for more info on IF() function.

-steve



At 12:41 PM -0600 12/7/01, Chris Boget wrote:
Is there a way to do this?  If so, I've not been able to
find it in the documentation...

I have 3 fields:

PrimaryKeyField
StatusField
DueField

I want to update the DueField to 0 (zero) where the
PrimaryKeyField = XYZXYZ.  I also want to update
the StatusField for those records to be equal to C
when it's equal to O but not any other value.  Is there
a way to do this all in one query?

pseudocode

UPDATE myTable SET
DueField = 0,
( StatusField = C WHEN StatusField = O)
WHERE PrimaryKeyField = XYZXYZ;

/pseudocode

Can something like that be done?

Chris



-- 
++
| Steve Edberg  [EMAIL PROTECTED] |
| University of California, Davis  (530)754-9127 |
| Programming/Database/SysAdmin   http://pgfsun.ucdavis.edu/ |
++
| Restriction of free thought and free speech is the most dangerous of  |
| all subversions. It is the one un-American act that could most easily  |
| defeat us.|
| - Supreme Court Justice (1939-1975) William O. Douglas |
++

-
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

Re: Advanced ORDER BY

[Steve Edberg]

You _might_ also be able to do something like:

SELECT *
FROM news
WHERE newstype='1' AND global='1'
ORDER BY concat(if(depart='center', 'A', 'B'), SortNumber) ASC

I don't have quite enough info here, but I'm going to assume that you 
want the records with depart='center' sorted first AND in alphabetic 
order, then the rest of the records in alphabetic order. I'm also 
going to assume that 'SortNumber' is actually the CHAR field you want 
the alphabetic sort on; if it isn't, replace it with the name of the 
field you want to sort by.

The intent here is to prefix the original sort order (SortNumber) 
with an 'A' for depart='center' records, so they sort before the 
other records (prefixed with a 'B').

-steve



At 4:01 PM +0400 9/21/01, Ilya Martynov [EMAIL PROTECTED] wrote:
l Hello,
l I'm trying to do a mysql select statement like this:

l SELECT * FROM news WHERE newstype = '1' AND global = '1' OR depart =
l 'center' ORDER BY SortNumber ASC

l But I would like the ORDER BY to list all records where depart = 'center'
l first and then all the rest of the depart records in
l alphabetical order.

l Any ideas?

Use two queries: one to get records where depart = 'center' and second
for rest of the depart records.

--
  -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
| Ilya Martynov (http://martynov.org/)|
| GnuPG 1024D/323BDEE6 D7F7 561E 4C1D 8A15 8E80  E4AE BE1A 53EB 323B DEE6 |
| AGAVA Software Company (http://www.agava.com/)  |
  -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-


-- 
+ Open source questions? +
| Steve Edberg   University of California, Davis |
| [EMAIL PROTECTED]   Computer Consultant |
| http://aesric.ucdavis.edu/  http://pgfsun.ucdavis.edu/ |
+--- http://pgfsun.ucdavis.edu/open-source-tools.html ---+

-
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

Re: mySQL on unix vs ODBC

[Steve Edberg]

At 9:10 AM +0100 9/12/01, Peter Gad wrote:
Hi everyone !

We've got a problem:
running mySQL on unix(sun) we connect a third party
windows software over ODBC. All is nice until a windows path
is stored in the db

Trying to store C:\Program Files\3p SW\

I find that it becomes C:Program Files3p SW.

Is it possible, by means of configuration, to have backslashes preserved ?
Either in ODBC or ... somewhere ?


Backslash is escape character; to store a string containing one, you 
have to double it. For example, use the string

C:\\Program Files\\3p SW\\

is your INSERT query, and it will show up in your SELECT result as

C:\Program Files\3p SW\


Facts:
3d party software = no code no chance of changes
ODBC - installed from myodbc-2.50.37-nt.zip
mySQL - 3.23.39 ( 3.23.41 could be used)

thanx in advance
peter


-- 
+ Open source questions? +
| Steve Edberg   University of California, Davis |
| [EMAIL PROTECTED]   Computer Consultant |
| http://aesric.ucdavis.edu/  http://pgfsun.ucdavis.edu/ |
+--- http://pgfsun.ucdavis.edu/open-source-tools.html ---+

-
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

Re: fieltypes

[Steve Edberg]

Did you see

http://www.mysql.com/doc/C/o/Column_types.html
and
http://www.mysql.com/doc/C/h/Choosing_types.html ?

If there's something there that is confusing, let us know -

- steve



At 11:22 PM +0200 9/10/01, Mike Schwarz wrote:
hi

i'm a newbie with mysql, just knowing well
access
mysql seems to have a lot more of fieldtypes
and i'm wondering, which field type i should
chooce in which case ??
is there a list and description with examples?
i root the manual and was not very satisfied.

thank you for any help
mike


-- 
+ Open source questions? +
| Steve Edberg   University of California, Davis |
| [EMAIL PROTECTED]   Computer Consultant |
| http://aesric.ucdavis.edu/  http://pgfsun.ucdavis.edu/ |
+--- http://pgfsun.ucdavis.edu/open-source-tools.html ---+

-
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

Re: BDB Tables, InnoDB tables ??

[Steve Edberg]

At 9:55 PM +0530 9/9/01, Nilesh Parmar wrote:
  Hi
   I just came across these words in the mailing lists.
   Can anyone tell me what are BDB tables and  InnoDB tables  .
I am new to mysql and would like to know what are they .
thanx in advance
Nilesh


(1) Go to http://www.mysql.com/doc/

(2) Type innodb or bdb into the search box

OR

(1) Go to http://www.mysql.com/doc/

(2) Use your browser's search function to search the page for innodb or bdb


-steve

-- 
+ Open source questions? +
| Steve Edberg   University of California, Davis |
| [EMAIL PROTECTED]   Computer Consultant |
| http://aesric.ucdavis.edu/  http://pgfsun.ucdavis.edu/ |
+--- http://pgfsun.ucdavis.edu/open-source-tools.html ---+

-
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

Re: Stored Procedures and Triggers

[Steve Edberg]

At 1:10 PM -0700 9/3/01, David Turner wrote:
A friend of mine mentioned something about perl stored procedures for
MYSQL. Has anyone heard about this? I have searched everywhere and only
seen posts related to POSTGRESQL. If they have this for MYSQL it would
be really helpful.

That would be MyPerl:

http://software.tangent.org/

It looks to be very early on in development, so I'm not sure how much 
I'd rely on it...

-steve



Thanks, Dave
On Mon, Sep 03, 2001 at 01:37:23AM -0700, Jeremy Zawodny wrote:
   On Mon, Sep 03, 2001 at 04:23:23PM +0800, Kimman Lui 
[EMAIL PROTECTED] wrote:
   
   How many types of triggers MySQL have? And what about stored procedures?

  Zero and Zero, as explained in the docs.

  If you need them today, I'd suggest looking at PostgreSQL.  If you're
  patient, they'll likely appear in MySQL someday--I believe both are on
  the TODO list.

  Jeremy
  --
  Jeremy D. Zawodny, [EMAIL PROTECTED]
  Technical Yahoo - Yahoo Finance
  Desk: (408) 349-7878   Fax: (408) 349-5454   Cell: (408) 685-5936

   MySQL 3.23.41-max: up 3 days, processed 33,335,761 queries (111/sec. avg)



-- 
+ Open source questions? +
| Steve Edberg   University of California, Davis |
| [EMAIL PROTECTED]   Computer Consultant |
| http://aesric.ucdavis.edu/  http://pgfsun.ucdavis.edu/ |
+--- http://pgfsun.ucdavis.edu/open-source-tools.html ---+

-
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

Re: Installation on Solaris questions

[Steve Edberg]

At 12:30 PM -0700 8/30/01, Sujay Daniel wrote:
I'm a newbie to Unix and MySQL. I have a few
questions.

With 'uname -a' I got the following message

SunOS [EMAIL PROTECTED] 5.7 Generic_106541-06 sun4u sparc
SUNW,Ultra-1

Can i install mysql-3.23.41-sun-solaris2.8-sparc in
it.

Theres no installation instructions for Unix/Solaris
machines in the Documentation page.


Evidently you didn't see

http://www.mysql.com/doc/S/o/Solaris.html

I'd suspect using a 2.8 binary on a 2.7 system might cause problems, 
but it shouldn't hurt to test it...

There are also older binaries (including some for Solaris 2.7) at

ftp://ftp.mysql.com/MySQL-3.23/

Lastly, you could always compile it yourself from source...t'aint that hard!

-steve



I'd appreciate it
if someone can give me pointers on installing MySql on
a Sun Solaris machine

Thank you

( tables )


-- 
+ Open source questions? +
| Steve Edberg   University of California, Davis |
| [EMAIL PROTECTED]   Computer Consultant |
| http://aesric.ucdavis.edu/  http://pgfsun.ucdavis.edu/ |
+--- http://pgfsun.ucdavis.edu/open-source-tools.html ---+

-
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

Re: Downsides of MySQL?

[Steve Edberg]

Urgh...I've been fortunate enough to avoid things like this myself 
until recently, when some of my PHP/MySQL/Apache stuff is getting 
tossed out in favor of Cold Fusion/MS-SQL Server/MS-IIS. Anyway, my 
2c:


At 8:35 AM -0500 8/16/01, Boget, Chris wrote:
Good morning.
Recently, we presented MySQL as a database option for a website that
we might be working on.  We've used it as our database in the past and
we plan on using it in the future as possible.
With that said, I confess I don't have as intimate a knowledge of mySQL
to address some of the things in the email that was sent to me.  I'd like
to hear what some of you have to say/think about this.  I know some
of the things said below aren't entirely correct, but I'm not 100% sure
about some of the others.

--Begin Quote--

MySQL - as I said at our meeting, we would not be comfortable with this
as an enterprise strength solution. MySQL is unsupported freeware and
lacks enterprise management functionality.


Do these people even know what they mean by 'enterprise management 
functionality', or did they just crib from an Oracle brochure? DO 
they have any specifics about what they require?


It has a small limited feature
set compared to ORACLE, DB/2 and is lacking the functionality to support
data replication and has little capability for generating management info.


No surprise that these folks haven't been following MySQL development 
for quite a while, and probably don't know about its replication 
features. I haven't used 'em myself, though, so I can't vouch for 
their robustness.

As far as the feature set  manageability, it's true - there's a 
lotta things MySQL made a conscious decision to leave out (unions, 
views, triggers, stored procedures, subselects [i know, coming soon], 
foreign key support, etc.) in favor of speed/small memory footprint. 
And you have to go to third-parties for 
reverse-engineering/diagramming tools.

If your application requires such, then maybe MySQL _isn't_ the right 
solution; however - depending on your app - Oracle/DB2/whatever might 
be sheer overkill. Administrative overhead for systems like those 
might far outweigh any advantages they have for you.


There are question marks around the scalability of the product, I'm not
sure of the locking algorithms used (whether row level or record level) -
the


It depends on table type; AFAIK, it can be table (ISAM/MyISAM), 
page-level (BDB), or row-level (InnoDB). See:

http://www.mysql.com/doc/L/o/Locking_methods.html

http://www.mysql.com/doc/T/a/Table_locking.html

http://www.mysql.com/doc/I/n/InnoDB_Next-key_locking.html

You've got a choice! This used to be considered a good thing...


fact that it is not generally used in multi-user solutions is a good enough
indication that this is not accepted database technology for
industrial-strength
multi-user systems.
The fact that it is unsupported freeware would mean that an end user would
potentially be held to ransom by a DBA with specific knowledge.


This kinda of statement is beginning to REALLY rile me when I hear 
it. Even if you discount the fact that this mailing list provides 
better support than the majority of PAID support programs, if you 
want to, the MySQL folks would be more than happy to take a large 
amount of your $$$ to provide excellent support:

http://www.mysql.com/support/arrangements/types.html

- this can include customizing MySQL for you! There are also 
individual consultants  firms that will support you as well. How 
anyone could actually back up a claim of MySQL being 'unsupported' is 
beyond me.


The mySQL
security model is also not sufficiently developed for any system that
involves
money.


I dunno, with some combination of encrypted fields, database server 
behind a firewall, SSH-tunnelled communication and good DB/system 
administration, you'd have a plenty secure system. After all, I don't 
think any of the recent and not-so-recent credit-card number thefts 
have been on MySQL systems.

OK, back to work for me. But first, some Mountain Dew...

-steve


-- 
+ Open source questions? +
| Steve Edberg   University of California, Davis |
| [EMAIL PROTECTED]   Computer Consultant |
| http://aesric.ucdavis.edu/  http://pgfsun.ucdavis.edu/ |
+--- http://pgfsun.ucdavis.edu/open-source-tools.html ---+

-
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

Re: Assigning New Users

[Steve Edberg]

At 8:36 AM -0500 7/8/01, MikemickaloBlezien wrote:
Hi All,

When assigning new Users to various databases, and access settings, 
in order to
have it take effect immediately, all that is needed is to do a FLUSH 
PRIVILEGES
or does the MySQL server need to be restarted??



If you use the GRANT statement to assign users/privileges, then you 
do not need to do anything else - effect is immediate.

If you edit the mysql.* tables (eg; user, db, host, etc.) directly 
(not recommended), you need to issue a FLUSH PRIVILEGES to activate 
your changes.

-steve

-- 
+-- Factoid: Of the 100 largest economies in the world, 51 are --+
| Steve Edberg   University of California, Davis |
| [EMAIL PROTECTED]   Computer Consultant |
| http://aesric.ucdavis.edu/  http://pgfsun.ucdavis.edu/ |
+--- corporations -- http://www.ips-dc.org/reports/top200text.htm ---+

-
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

Re: PHP and MySQL result set navigation

[Steve Edberg]

If you want to repeatedly loop through a result set in PHP, you can use

mysql_data_seek($result, 0);

to reset the pointer to the first row. See

http://www.php.net/manual/en/function.mysql-data-seek.php


-steve




At 9:33 AM +0800 6/4/01, Rolf Hopkins wrote:
Yep, that's the way you do it. Put it into a 2D array or even better, a
class.  That's if you are familiar with OOP.

- Original Message -
From: Marco Bleeker [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, June 04, 2001 6:57
Subject: PHP and MySQL result set navigation


  I do something like this, where $result is the result of a mysql_query():

  ?php
  while ($row = mysql_fetch_array($result))
  {
  if ($row[col] == 1)
  {
  print_entry($row);
  }
  }
  ?

  This is to fill one column on screen. Now I would like to do the same for
  the second column: if ($row[col] == 2). But the $result pointer has moved
  to the end. How to move it back to the start again? reset() won't work,
  because it's not an array, just an integer pointing to a MySQL result set.
  How to move through this result set using PHP? Or should I just run a
  seperate query for each column with a 'where col =' clause? That looks
like
  pretty intensive use of computer resources... I could also turn the result
  set into a 2D array of rows and work with that. Sounds a bit voluminous as
  well...

  Thanks, marco
  |
  | Marco Bleeker, Amsterdam
  | [EMAIL PROTECTED]
  | http://www.euronet.nl/users/mbleeker/
  |
  | Attachments only after prior notice please.
  | Don't put me on any kind of mailing list.
  |
  | I am now receiving the Snowhite virus 4x a day
  | - some of you must be infected, please check !
  | (No, you did not get it from me, I use Eudora)
  | __@
  |   _`\,_
  |__(*)/ (*)Ah, op DIE fiets !

  


-- 
+--- So long, and thanks for all the fish - -+
| Steve Edberg   University of California, Davis |
| [EMAIL PROTECTED]   Computer Consultant |
| http://aesric.ucdavis.edu/  http://pgfsun.ucdavis.edu/ |
+--- Douglas Adams 1952 - 11 may 2001 ---+

-
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

Re: Database Index Question

[Steve Edberg]

At 9:31 AM -0700 4/26/01, Shane Gentry wrote:
  Does it help to put an Index on a column when you
  use a LIKE query.

  ex. SELECT * FROM users WHERE name LIKE '%tom%'

  Thanks in Advance!


In this case, It won't help, since you have a wildcard at the 
beginning of your pattern. If you used ...LIKE 'tom%' instead, I 
believe MySQL WOULD use an index on 'name.'

-steve

-- 
+-- KDVS 90.3fm Annual Fundraiser : 16 - 22 April 2001 --+
| Steve Edberg   University of California, Davis |
| [EMAIL PROTECTED]   Computer Consultant |
| http://aesric.ucdavis.edu/  http://pgfsun.ucdavis.edu/ |
+- www.kdvs.org -+

-
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

Re: delete in mysql

[Steve Edberg]

At 10:56 AM +0200 4/24/01, Sven Heising wrote:
Hi list


When posting help requests, it's always helpful to include the error 
message you get. You can check quickly this via something like:

mysql_query(DELETE FROM $dtable where loginid = '$userto' )
or
mysql_error();

One possible problem is that $dbuser doesn't have delete privileges 
on $dbase on host $host. See the manual:

http://www.mysql.com/doc/P/r/Privilege_system.html


got a problem againcan't delete :

$db=mysql_pconnect($host,$dbuser,$pass) or
die($dbfehler);
echo $userto;

mysql_query(use $dbase;);

mysql_query(DELETE FROM $dtable where loginid = '$userto' );


what is wrong with this?

thanx in advance

sven



The above might not be an actual snippet of your program, but it's 
always good to check a query's success. For example:

function your_database_error_function() {
   echo 'Database error: '.mysql_error();
   exit;
}

...

$ResultId = mysql_query(DELETE FROM $dtable where loginid = '$userto' );

if (!$ResultId) {
   your_database_error_function();
}

...



-steve

-- 
+-- KDVS 90.3fm Annual Fundraiser : 16 - 22 April 2001 --+
| Steve Edberg   University of California, Davis |
| [EMAIL PROTECTED]   Computer Consultant |
| http://aesric.ucdavis.edu/  http://pgfsun.ucdavis.edu/ |
+- www.kdvs.org -+

-
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

Re: building site search

[Steve Edberg]

At 3:29 PM -0500 4/19/01, Wade DeWerff wrote:
Is the common way to index a site, then place that into a database? If so
whats the best(easiest) way to index a site? Thanks


Well, that's a LONG answer to a short question, but two common places 
to start are

http://www.mnogosearch.ru/
Formerly called udmSearch, this can use MySQL as a backend; 
looks quite nice - Perl  PHP frontends.

http://www.htdig.org/
Not quite sure what it uses for its database, but I'm sure it 
could be hacked to use MySQL.

Two other search engines are Glimpse/WebGlimpse and Swish-E, although 
I don't know a whole lot about them. You could also do a web search 
for 'text indexing', 'intranet search', or something. You have plenty 
of reading material then...

-steve


Database! Sql! Query!

-- 
+-- KDVS 90.3fm Annual Fundraiser : 16 - 22 April 2001 --+
| Steve Edberg   University of California, Davis |
| [EMAIL PROTECTED]   Computer Consultant |
| http://aesric.ucdavis.edu/  http://pgfsun.ucdavis.edu/ |
+- www.kdvs.org -+

-
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

Re: Mysql Table via php

[Steve Edberg]

At 12:48 PM -0500 2/22/01, Joe and Nancy M wrote:
I finally got my ISP to resolve the issues with connecting to my db. 
I have a test table in the database named test.  I have 3 fields; 
ID(20Char), PRICE(int),QTY(int).  I am using php (this is what my 
ISP supports and suggested).

I can connect using:
?php

$link = mysql_connect ("localhost.domainnamehere.com.", 
"usernameherel", "passwordhere")

or die ("Could not connect");


?

I can draw the data out using this sample code:

?php

$query = "SELECT ID, PRICE, QTY

FROM test

where PRICE=3";

$result = mysql_query ($query)

or die ("Query failed");

# fetch rows in reverse order

for ($i = mysql_num_rows ($result) - 1; $i =0; $i--) {

if (!mysql_data_seek ($result, $i)) {

printf ("Cannot seek to row %d\n", $i);

continue;

}

if(!($row = mysql_fetch_object ($result)))

continue;

printf ("%s %s %sBR\n", $row-ID, $row-PRICE, $row-QTY);

}

mysql_free_result ($result);

?



1.  I can not seem to get the syntax correct to select where the 
ID=text value.  I get parse errors with almost every scenario.  What 
is the correct string to select where a character field is equal to 
a value???


You need to single quote the value:

$query = "SELECT ID, PRICE, QTY FROM test where id='text value'";


2.  I primarily need to select one record from the table and display 
the PRICE on the webpage and show a hyperlink "buyme" where QTY is 
gt 0.  Does someone have a sample piece of php that will handle 
this??


Well, this is a bit of a vague description, but here goes:

$id = 'some value';

$query = "SELECT id,price,qty FROM test WHERE id='$id'";
$result = @mysql_query($query) or die('Arrrghhh');

if (mysql_num_rows($result) != 1)
{
#   Do some error checking here: no record found or multiple 
records w/same id
}
else
{
echo 'a href="your_buyme_program.php?id=', urlencode($id), 
'qty=1"Buy one now/a';
}


Also, there are a few things to note about the code you originally 
posted (above).

* you do not have an 'order by' in your sql statement. MySQL - and 
any relational database - will not necessarily return the results in 
any particular order without one. This will probably bollix up your 
fetching in 'reverse order'...there is no particular 'forward' order, 
so there ain't a REVERSE order.

* assuming you actually want the order to be by ID, why not let the 
database handle as much of the work as possible? It's faster at it 
than you  php:

$query = "SELECT ID, PRICE, QTY FROM test where PRICE=3 order 
by id desc";
#   'desc' means to sort in descending order - ie, from z - a

$result = mysql_query ($query) or die ("Query failed");

while ($d = mysql_fetch_object($result))
{
printf ("%s %s %sBR\n", $row-ID, $row-PRICE, $row-QTY);
}
mysql_free_result ($result);





Thanks, I am running very short on time

Joe.

-- 
+--- "They've got a cherry pie there, that'll kill ya" --+
| Steve Edberg   University of California, Davis |
| [EMAIL PROTECTED]   Computer Consultant |
| http://aesric.ucdavis.edu/  http://pgfsun.ucdavis.edu/ |
+-- FBI Special Agent Dale Cooper ---+

-
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

Re: From Windows access to MySQL on Linux

[Steve Edberg]

At 10:15 PM +0700 2/3/01, Kiky wrote:
Thank you for your reply :-)

  Yes, you can add a mysql user that has access to mysql server from the
  windows host and use this user to create the connection.

## Is it possible to do this in PHP under Windows, without installing
any
mysql driver for ODBC, or any other additional installation ?
Can I  just do the same way of connection to mysql database, just like
the one
I use in PHP under Linux ?

mysql_connect("127.0.0.1", "root", "");
mysql_select_db("my_database")


Just replace '127.0.0.1' (a.k.a. 'localhost') with the database 
server's ip address or name. Some things you have to be sure of to do 
this:

(1) The database server machine's mysql wasn't started with 
--skip-networking (I'm pretty sure that's the parameter); if it was, 
MySQL won't be listening to the internet at all, and will need to be 
restarted without that parameter.

(2) Make sure the server's permission tables will allow access from 
your web server - see the GRANT command;

(3) Make sure the database server isn't behind a firewall or 
something that restricts internet access to the MySQL port (normally 
3306)

If all these conditions are met, your the database server can be 
anywhere. Keep in mind, though, that if your query returns a large 
number of results, response will be limited by the speed of the 
network(s) between the two machines.

- steve


Thank you,
Helen



-- 
+--- "They've got a cherry pie there, that'll kill ya" --+
| Steve Edberg   University of California, Davis |
| [EMAIL PROTECTED]   Computer Consultant |
| http://aesric.ucdavis.edu/  http://pgfsun.ucdavis.edu/ |
+-- FBI Special Agent Dale Cooper ---+

-
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 mysql-unsubscribe-##L=##[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Querying database from another unix box......

[Steve Edberg]

At 11:36 AM +0200 1/26/01, Mark Coetser wrote:
Hi guys

Havent worked much with mysql or any databses for that matter, I have
created a small database on my unix box and I want to make a query to the
database from another unix box without telneting in or ssh, I beleive that
it runs on port 3306.??? help

You need the mysql client on your computer (check the downloads 
section at www.mysql.com; they probably have a client-only download). 
Then you can just use the -h parameter:

mysql -u username -h hostname -p databasename

Also, the permissions on the database need to be set up to allow 
access from your computer; see the docs on setting up permissions and 
the 'GRANT' command.

- steve



   regards,
  Mark Coetser
  support engineer   the internet solution
  telephone (011) 283 5500 / 0860 50 5000   fax (011) 283 5401
   http://www.is.co.za


-- 
+--- "They've got a cherry pie there, that'll kill ya" --+
| Steve Edberg   University of California, Davis |
| [EMAIL PROTECTED]   Computer Consultant |
| http://aesric.ucdavis.edu/  http://pgfsun.ucdavis.edu/ |
+-- FBI Special Agent Dale Cooper ---+

-
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

Re: question

[Steve Edberg]

At 7:34 PM + 1/23/01, suchin kannan wrote:
hi!

i am suchindran kannan. i have started on a project with my prof on 
mysql and PHP.

im new to the mysql world. i have relation in which i would have to 
include a multi valued attribute, can i do that in mysql.

hope i can find an answer to this.


Would the 'SET' column type do?

http://www.mysql.com/doc/S/E/SET.html



bye
thanks

Suchi Kannan  Suchi Kannan
Graduate Research Assistant   1500,Sparkman drive,
Center for Automation and Apt#17-d,
Robotics  Huntsville,AL 35816
Alabama high tech directory   ph.no-256.837.3394.
A-11 ,Research Institute
Huntsville,AL-35899.
U.A.H
PH.NO-256.824.2698


-- 
+--- "They've got a cherry pie there, that'll kill ya" --+
| Steve Edberg   University of California, Davis |
| [EMAIL PROTECTED]   Computer Consultant |
| http://aesric.ucdavis.edu/  http://pgfsun.ucdavis.edu/ |
+-- FBI Special Agent Dale Cooper ---+

-
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

Re: What proportion of sites use MySQL.

[Steve Edberg]

At 11:44 AM -0500 1/23/01, Vivek Khera wrote:
   "AS" == Andy Stowell [EMAIL PROTECTED] writes:

AS One of my clients has asked me to find out a rough idea as to what
AS proportion of database enabled websites use the MySQL database.


Along with the statistics below, you might try

http://www.mysql.com/information/users.html
and
http://www.mysql.com/information/partners.html

for some more info.

-steve


Exactly 67.2341231123123%

Now really, how on earth do you expect to come up with such a
statistic?  How can you even tell when a site is database backed let
alone what database it is using?  You just can't do it.  So therefore,
sticking with the rule that 93.234% of all statistics are just made
up, I made one up for you ;-)


BTW, these percentages are a little off...perhaps they're slightly 
out of date. My latest calculations give me

67.88872051009%
and
93.221%

;P



--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.Khera Communications, Inc.
Internet: [EMAIL PROTECTED]   Rockville, MD   +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/

-


-- 
+--- "They've got a cherry pie there, that'll kill ya" ------+
| Steve Edberg   University of California, Davis |
| [EMAIL PROTECTED]   Computer Consultant |
| http://aesric.ucdavis.edu/  http://pgfsun.ucdavis.edu/ |
+-- FBI Special Agent Dale Cooper ---+

-
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