Re: COUNT

[John Fawcett]

From: "Gustavo Andrade"
> select count(distinct membros.ID) as total_membros, count(distinct
> replays.ID) as total_replays, count(distinct downloads.ID) as
> total_downloads from membros,replays,downloads;

Why join three tables to count the records in each one? I'm sure the
performance will be poor once you get more data.

> if one of the tables have 0 records all the counts will turn to 0
> the count works only if all the tables have records
> how can i fix that?

By joining the tables you are asking for all possible combinations of the
rows (cartesian product).
The number of rows obtained is:

(n. rows in table 1) * (n. rows in table 2) * ( n. rows in table 3)

So if a table has 0 rows there are 0 possible combinations.

For this reason and also for performance reasons, you should do 3 separate
selects.

If ID is a unique key, you can also take out the distinct, which in your
query you needed because by making all possible combinations you repeated
the same ID many times.

John

_
Quer ter um fórum para seu clan de Starcraft/BroodWar, Counter-Strike,
Warcraft ou outros. entre em
http://www.arena-star.com.br/forum/



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

COUNT

[Gustavo Andrade]

select count(distinct membros.ID) as total_membros, count(distinct
replays.ID) as total_replays, count(distinct downloads.ID) as
total_downloads from membros,replays,downloads;
 
if one of the tables have 0 records all the counts will turn to 0
the count works only if all the tables have records
how can i fix that?
 
_
Quer ter um fórum para seu clan de Starcraft/BroodWar, Counter-Strike,
Warcraft ou outros. entre em 
http://www.arena-star.com.br/forum/
 

Re: [SPAM]Re: e: Select distinct year from unix timestamp

[John Fawcett]

From: "Paul DuBois"
> At 17:50 -0500 5/16/04, Paul DuBois wrote:
>
> Not a huge difference, I guess.  But I suppose if a query that
> uses one or the other of these expressions processes a large number
> of rows, it might pay to run some comparative testing.
>

Another interesting point is whether one timestamp format is to be preferred
over the other in terms of performance of the operations to be done on it.

The OP should be able to do this testing with the mechanism you
demonstrated.

John

> -- 
> Paul DuBois, MySQL Documentation Team
> Madison, Wisconsin, USA
> MySQL AB, www.mysql.com


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

Re: Converting to Percentages

[Michael Stassen]

David Blomstrom wrote:
--- Roger Baklund <[EMAIL PROTECTED]> wrote:

use test;

Yikes - you lost me on the second word!
Are you talking about the sort of "test" that's
described on this page?:
http://dev.mysql.com/doc/mysql/en/running_mysqltest.html
No.  "use test" tells mysql to work in the test database.  You may create 
different, separate databases.  One for each project, perhaps.

create table pct (red int,blue int,gray int);
insert into pct values (3,3,4),(10,10,5);
OK, you want me to create a table named "pct" and give
it files designated int and named red, blue and gray,
right?
No, he's giving you an example which produces the example output you 
specified in your post.  From the top:

Tell mysql to work in the database named test:
  use test;
Create a table named pct with columns named red, blue, and gray:
  create table pct (red int,blue int,gray int);
Add 2 rows to the pct table with specified values:
  insert into pct values (3,3,4),(10,10,5);
Select data from the pct table, displaying percentages instead of raw numbers:
  select @total:=red+blue+gray as total,
concat(red/(@total/100),"%") as red,
concat(blue/(@total/100),"%") as blue,
concat(gray/(@total/100),"%") as gray
  from pct;
For each row, @total:=red+blue+gray adds the values in the 3 columns red, 
blue, and gray, and stores the result in the user variable @total.

red/(@total/100) is mathematically equivalent to 100*red/@total, which is 
the red amount as a percent of the total.

concat(100*red/@total,"%") tacks a percent sign on the end.
Try it yourself.  In the mysql client, it should look like this:
mysql> use test;
Database changed
mysql> create table pct (red int,blue int,gray int);
Query OK, 0 rows affected (0.19 sec)
mysql> insert into pct values (3,3,4),(10,10,5);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select @total:=red+blue+gray as total,
->   concat(red/(@total/100),"%") as red,
->   concat(blue/(@total/100),"%") as blue,
->   concat(gray/(@total/100),"%") as gray
-> from pct;
+---+--+--+--+
| total | red  | blue | gray |
+---+--+--+--+
|10 | 30%  | 30%  | 40%  |
|25 | 40%  | 40%  | 20%  |
+---+--+--+--+
2 rows in set (0.00 sec)
The idea now is to adapt this to your data.  From this example, you should 
be able to write a similar SELECT statement using the columns in your table. 
 Also, if you already have a column with the correct totals, you can use it 
in place of the user variable.  Something like:

  SELECT Name, Seat, ...
 CONCAT(100*Native/Pop,"%") AS Native,
 CONCAT(100*White/Pop,"%") AS White,
 ...
  FROM yourtable;
You'll have to use the actual names of your table and columns, of course.
I'd suggest you got it right in your earlier email:
So I suspect this would all make more sense if I had
never begun with Dreamweaver. Some people say
Dreamweaver doesn't bloat the code that much, but it
sure doesn't resemble the example you showed me!
By hiding how things work, Dreamweaver is, well, hiding how things work. 
I'd suggest you start by working directly with the mysql client to get a 
handle on sql queries, then work with php to add the web interface.

There are a lot of web tutorials on using mysql and php.  For example, there 
are several on Dev Shed .  A quick Google search on 
"php mysql tutorial" yields numerous hits.  I'd suggest you try a few to get 
a better idea of what's what.

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

[Setup "language" MySQL ??]

[richard]

Hi,

Is it possible to setup MySQL in french ?

For example, when I write and execute "SELECT monthname(mydate) from
mytable", it return the month name of "mydate" (march, april, may,
).
I'd like it return "mars, avril, mai,." = the month is french.

So, is it possible ?
How ?

Thank for your help.

-- 
Regards,
Richard
___


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

RE: Counting multiple tables

[Dathan Vance Pattishall]

SHOW TABLE STATUS to get the count of each table, then your application adds
the number in the Rows field from each of the tables returned.


DVP

Dathan Vance Pattishall http://www.friendster.com


> -Original Message-
> From: Gustavo Andrade [mailto:[EMAIL PROTECTED]
> Sent: Sunday, May 16, 2004 3:10 PM
> To: [EMAIL PROTECTED]
> Subject: Counting multiple tables
> 
> I want to know if its possible to count the total records of multiple
> tables:
> Example: I have 3 tables. I want to know the total records of each table
> using only 1 query.
> 
> Is that possible?
> 
> _
> Quer ter um fórum para seu clan de Starcraft/BroodWar, Counter-Strike,
> Warcraft ou outros. entre em
> http://www.arena-star.com.br/forum/
> 



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

Fw: Setup MySQL in RedHat ES 3

[jschung]

Is it really nobody has similar experience?

- Forwarded by Joseph S CHUNG/ITSD/HKSARG on 2004-05-17 09:46 -
   
   
  [EMAIL PROTECTED]
 
   To:   [EMAIL PROTECTED] 
   
  2004-05-14 17:59 cc: 
   
   Subject:  Setup MySQL in RedHat ES 3
   
|| 
   
| [ ] Urgent | 
   
||   
||   
 | [ ] Return 
Receipt |   
 
||   
   
   




Dear Sir,

As MySQL is included in this distribution. I install it together with
RedHat. Do you know what do I need to do to make it start and working? I
cannot find related document from www.mysql.com.

Thanks,
Joseph



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





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

Re: Converting to Percentages

[David Blomstrom]

--- Roger Baklund <[EMAIL PROTECTED]> wrote:

> use test;

Yikes - you lost me on the second word!

Are you talking about the sort of "test" that's
described on this page?:

http://dev.mysql.com/doc/mysql/en/running_mysqltest.html

> create table pct (red int,blue int,gray int);
> insert into pct values (3,3,4),(10,10,5);

OK, you want me to create a table named "pct" and give
it files designated int and named red, blue and gray,
right?

I'll have to do a little more research before I
understand the rest, but is this an operation I'll
have to perform on every row? Or does it set the table
up for an operation that will transfrom all the
numerals in selected columns into percentages?

I think I basically understand what you're saying; it
will just take a little while for it to soak in.

Thanks.

> select @total:=red+blue+gray as total,
>   concat(red/(@total/100),"%") as red,
>   concat(blue/(@total/100),"%") as blue,
>   concat(gray/(@total/100),"%") as gray
> from pct;
> 
> Because of the use of a "user variable" (@total) to
> calculate the total in
> my example, I have to have the total column first,
> because it must be
> calculated before the individual percentage
> calculations. If you have a
> total column in your table, you can use that in the
> calculations and place
> it in the last column.
> 
> --
> Roger
> 





__
Do you Yahoo!?
SBC Yahoo! - Internet access at a great low price.
http://promo.yahoo.com/sbc/

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

Re: 4.0.18 vs 4.1.1

[Josh Trutwin]

On Sun, 16 May 2004 16:02:06 -0700
Ron Gilbert <[EMAIL PROTECTED]> wrote:

> If I switch to 5.0, are there any issues with PHP?  Will my 4.2.2 
> version of PHP work just fine with 5.0?  I also have a Windows C++ 
> program that talked to mysql over the Internet via the C API, will
> it still work after the 5.0 (or the 4.1.1) upgrade?  I'm not doing
> anything fancy with it.

On Linux it works ok to use PhP 4.x (or PhP 5.x) with MySQL 5.0, on Windows I just 
could not get the two to talk to each other.  I think it works ok with 4.1.1 though.  
MySQL 5.0 is a Zip package, not a nice Install Shield though so you should be 
comfortable installing that way before attempting to upgrade.  If you are so gung-ho 
to upgrade MySQL to an alpha product, maybe you should also considering upgrading php 
to at least 4.3.6 or even 5.0rc2 as there have been many security fixes since 4.2.  
The best advice I have for you is to get a test box, install what you want and hammer 
it for a while and see if it works ok.  The MySQL/PhP developers I'm sure will at 
least appreciate the testing.
 
> Other than whats in the docs, are there other issues to think about
> when upgrading from 3.23?  Can I go straight from 3.23 to 5.0? 
> According to the docs, it just seem to be the GRANT tables.

Definately the GRANT tables, also if you use the PASSWORD function there have been 
some changes that might make it incompatible with previous versions.  Other than that 
I think I was fine.  You need to run a few scripts I think to setup the stored 
procedure tables and some help tables.

Josh

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

Re: e: Select distinct year from unix timestamp

[Paul DuBois]

At 17:50 -0500 5/16/04, Paul DuBois wrote:
At 0:38 +0200 5/17/04, John Fawcett wrote:
From: "Paul DuBois"
 You're right.  You'd have to apply YEAR() to
 FROM_UNIXTIME(UNIX_TIMESTAMP(arg)).
and you can avoid YEAR() altogether by using a
format string. in FROM_UNIXTIME()
Right again. :-)
I was curious about which might be faster, and got these results on a
2.8GHz Pentium IV with MySQL 4.1.2 on Linux:
mysql> select benchmark(1000,from_unixtime(unix_timestamp(),'%Y'));
+--+
| benchmark(1000,from_unixtime(unix_timestamp(),'%Y')) |
+--+
|0 |
+--+
1 row in set (11.50 sec)
mysql> select benchmark(1000,year(from_unixtime(unix_timestamp(;
+---+
| benchmark(1000,year(from_unixtime(unix_timestamp( |
+---+
| 0 |
+---+
1 row in set (8.72 sec)
On a 900MHz PowerPC G3 with MySQL 4.0.19 on Mac OS X:
mysql> select benchmark(1000,from_unixtime(unix_timestamp(),'%Y'));
+--+
| benchmark(1000,from_unixtime(unix_timestamp(),'%Y')) |
+--+
|0 |
+--+
1 row in set (55.17 sec)
mysql> select benchmark(1000,year(from_unixtime(unix_timestamp(;
+---+
| benchmark(1000,year(from_unixtime(unix_timestamp( |
+---+
| 0 |
+---+
1 row in set (36.39 sec)
Not a huge difference, I guess.  But I suppose if a query that
uses one or the other of these expressions processes a large number
of rows, it might pay to run some comparative testing.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Counting multiple tables

[Gustavo Andrade]

I want to know if its possible to count the total records of multiple
tables:
Example: I have 3 tables. I want to know the total records of each table
using only 1 query.
 
Is that possible?
 
_
Quer ter um fórum para seu clan de Starcraft/BroodWar, Counter-Strike,
Warcraft ou outros. entre em 
http://www.arena-star.com.br/forum/
 

Re: 4.0.18 vs 4.1.1

[Ron Gilbert]

If I switch to 5.0, are there any issues with PHP?  Will my 4.2.2 
version of PHP work just fine with 5.0?  I also have a Windows C++ 
program that talked to mysql over the Internet via the C API, will it 
still work after the 5.0 (or the 4.1.1) upgrade?  I'm not doing anything 
fancy with it.

Other than whats in the docs, are there other issues to think about when 
upgrading from 3.23?  Can I go straight from 3.23 to 5.0?  According to 
the docs, it just seem to be the GRANT tables.

Ron
Josh Trutwin wrote:
On Sun, 16 May 2004 13:51:29 -0700
Ron Gilbert <[EMAIL PROTECTED]> wrote:
I am going to upgrade from 3.23 and was wondering if 4.1.1 is stable
enough?  This is just for some personal websites, nothing mission 
critical, but on the other hand, I don't want to deal with endless 
problems.

The reason that I'd like to go to 4.1.1 is for sub-selects,
otherwise I'd stick to 4.0.18.
What I would really like is go to 5.0 (for stored procedures), is
5.0 stable enough for casual use?
I've been using 5.0.0 for some sites and use it for an RDBMS class, nothing 
mission critical mind you, but it's worked very well in my opinion (running on SuSE 
Linux).  There were a couple upgrade issues which are covered in the documentation, 
but nothing serious.
YMMV
Josh
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Converting to Percentages

[Roger Baklund]

* David Blomstrom
> Suppose you have a table with four columns. The first
> three colums each list a numeral, and the fourth
> column lists the sum of those numerals, like this:
>
> 3 | 3 | 4 | 10
> 10 | 10 | 5 | 25
>
> Now, suppose you wanted to also display those numerals
> as percentages:
>
> Reds | Blues | Grays | TOTAL
> 30% | 30% | 40% | 100%
> 40% | 40% | 20% | 100%
>
> (Actually, I may retain the original numerals in the
> last column...)
>
> 30% | 30% | 40% | 10
> 40% | 40% | 20% | 25

use test;
create table pct (red int,blue int,gray int);
insert into pct values (3,3,4),(10,10,5);
select @total:=red+blue+gray as total,
  concat(red/(@total/100),"%") as red,
  concat(blue/(@total/100),"%") as blue,
  concat(gray/(@total/100),"%") as gray
from pct;

Because of the use of a "user variable" (@total) to calculate the total in
my example, I have to have the total column first, because it must be
calculated before the individual percentage calculations. If you have a
total column in your table, you can use that in the calculations and place
it in the last column.

--
Roger


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

Re: e: Select distinct year from unix timestamp

[Paul DuBois]

At 0:38 +0200 5/17/04, John Fawcett wrote:
From: "Paul DuBois"
 You're right.  You'd have to apply YEAR() to
 FROM_UNIXTIME(UNIX_TIMESTAMP(arg)).
and you can avoid YEAR() altogether by using a
format string. in FROM_UNIXTIME()
Right again. :-)
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Select distinct year from unix timestamp

[John Fawcett]

From: "T. H. Grejc" 
> I'm creating news archive and it should be sorted by months:
> 
> January 2004 (news count is 56)
> February 2004 (48)
> ...
So you need to use GROUP BY and COUNT.

The format is like this:

select monthandyear,count(othercolumn) from t
group by monthandyear

in your case monthandyear must be replaced by your function
which extracts month and year from the unix timestamp column
(in both the select and group by).

I think that's what you needed.

John


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

Re: e: Select distinct year from unix timestamp

[John Fawcett]

From: "Paul DuBois" 
> You're right.  You'd have to apply YEAR() to
> FROM_UNIXTIME(UNIX_TIMESTAMP(arg)).

and you can avoid YEAR() altogether by using a
format string. in FROM_UNIXTIME()

John

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

Re: [SPAM]Re: Select distinct year from unix timestamp

[Paul DuBois]

At 0:25 +0200 5/17/04, John Fawcett wrote:
From: "Paul DuBois"> At 22:27 +0200 5/16/04,
 John Fawcett wrote:
 >Year does not operate on a unix timestamp.
 Sure it does:
 mysql> select t, year(t) from tsdemo1;
 ++-+
 | t  | year(t) |
 ++-+
 | 20010822133241 |2001 |
 | 20010822133241 |2001 |
 ++-+
 The problem lies elsewhere.  Where that might be is difficult to
 say, because no PHP code has been shown yet.
The first column doesn't look like a unix timestamp.
Sorry.  It's a TIMESTAMP column.
 It should be expressed
in seconds since 1970-01-01 00:00:00 GMT.
Try this to get the unix timestamp of the above date. Year shouldn't work on
it.
SELECT UNIX_TIMESTAMP('2001-08-22 13:32:41');
You're right.  You'd have to apply YEAR() to
FROM_UNIXTIME(UNIX_TIMESTAMP(arg)).
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Select distinct year from unix timestamp

[T. H. Grejc]

John Fawcett wrote:
From: "T. H. Grejc"
How can I add more fields to query. If I write:
SELECT DISTINCT FROM_UNIXTIME(created, '%Y %M'), other_field FROM
table_name ORDER BY created DESC
I loose distinction (all dates are displayed).
TNX
I don't think distinction is lost. All the rows should still be distinct
(considered in their entirity).
Yes, but if I add any oter field into query I get all dates as a result 
(56, january's, 48 February's etc)

What are you expecting to see as a result?
I'm creating news archive and it should be sorted by months:
January 2004 (news count is 56)
February 2004 (48)
...
Like ususal weblogs archives. You can see example at 
http://weblogs.mozillazine.org/ben/ an then check 'ARCHIVES' at the 
right, just below the search.

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

Re: [SPAM]Re: Select distinct year from unix timestamp

[John Fawcett]

From: "Paul DuBois"> At 22:27 +0200 5/16/04,
 John Fawcett wrote:
> >Year does not operate on a unix timestamp.
>
> Sure it does:
>
> mysql> select t, year(t) from tsdemo1;
> ++-+
> | t  | year(t) |
> ++-+
> | 20010822133241 |2001 |
> | 20010822133241 |2001 |
> ++-+
>
> The problem lies elsewhere.  Where that might be is difficult to
> say, because no PHP code has been shown yet.
>
The first column doesn't look like a unix timestamp. It should be expressed
in seconds since 1970-01-01 00:00:00 GMT.

Try this to get the unix timestamp of the above date. Year shouldn't work on
it.
SELECT UNIX_TIMESTAMP('2001-08-22 13:32:41');

John


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

Re: Select distinct year from unix timestamp

[John Fawcett]

From: "T. H. Grejc"
> How can I add more fields to query. If I write:
>
> SELECT DISTINCT FROM_UNIXTIME(created, '%Y %M'), other_field FROM
> table_name ORDER BY created DESC
>
> I loose distinction (all dates are displayed).
>
> TNX
>
I don't think distinction is lost. All the rows should still be distinct
(considered in their entirity).
What are you expecting to see as a result?

John


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

Re: Select distinct year from unix timestamp

[T. H. Grejc]

Paul DuBois wrote:
At 22:27 +0200 5/16/04, John Fawcett wrote:
From: "T. H. Grejc"
 Hello,
 I'm trying to select all distinct years from a unixtimestamp field in
 MySQL database (3.23.56). I have a query:
 SELECT DISTINCT YEAR(date_field) As theYear FROM table
 but PHP gives me an empty array. What am I doing wrong?
 TNX
I think you need this function
FROM_UNIXTIME(unix_timestamp,format).
Year does not operate on a unix timestamp.

Sure it does:
mysql> select t, year(t) from tsdemo1;
++-+
| t  | year(t) |
++-+
| 20010822133241 |2001 |
| 20010822133241 |2001 |
++-+
The problem lies elsewhere.  Where that might be is difficult to
say, because no PHP code has been shown yet.
My DateField is in '1084415895' form, not in the '20010822133241' form, 
and the PHP is only regular mysql_fetch_array, then while loop, nothing 
special.

Is it maybe better to use 20010822133241  instead of unix_timestamp. I 
have allways used Unix timestamps, but never realy done any big date 
calculations. Is mysql TIMESTAMP(14) more flexibile that Unix timestamp.

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

Re: 4.0.18 vs 4.1.1

[Paul DuBois]

At 13:51 -0700 5/16/04, Ron Gilbert wrote:
I am going to upgrade from 3.23 and was wondering if 4.1.1 is stable 
enough?  This is just for some personal websites, nothing mission 
critical, but on the other hand, I don't want to deal with endless 
problems.

The reason that I'd like to go to 4.1.1 is for sub-selects, 
otherwise I'd stick to 4.0.18.

What I would really like is go to 5.0 (for stored procedures), is 
5.0 stable enough for casual use?
Well, if you want to use stored procedures, then the answer is "yes,"
because 5.0 is your only choice. :-)
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Select distinct year from unix timestamp

[T. H. Grejc]

John Fawcett wrote:
From: "T. H. Grejc" 

Hello,
I'm trying to select all distinct years from a unixtimestamp field in
MySQL database (3.23.56). I have a query:
SELECT DISTINCT YEAR(date_field) As theYear FROM table
but PHP gives me an empty array. What am I doing wrong?
TNX
I think you need this function
FROM_UNIXTIME(unix_timestamp,format).
Year does not operate on a unix timestamp.
Working like a charm. My query now is:
SELECT DISTINCT FROM_UNIXTIME(created, '%Y %M') FROM table_name ORDER BY 
created DESC

How can I add more fields to query. If I write:
SELECT DISTINCT FROM_UNIXTIME(created, '%Y %M'), other_field FROM 
table_name ORDER BY created DESC

I loose distinction (all dates are displayed).
TNX
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Select distinct year from unix timestamp

[Paul DuBois]

At 22:27 +0200 5/16/04, John Fawcett wrote:
From: "T. H. Grejc"
 Hello,
 I'm trying to select all distinct years from a unixtimestamp field in
 MySQL database (3.23.56). I have a query:
 SELECT DISTINCT YEAR(date_field) As theYear FROM table
 but PHP gives me an empty array. What am I doing wrong?
 TNX
I think you need this function
FROM_UNIXTIME(unix_timestamp,format).
Year does not operate on a unix timestamp.
Sure it does:
mysql> select t, year(t) from tsdemo1;
++-+
| t  | year(t) |
++-+
| 20010822133241 |2001 |
| 20010822133241 |2001 |
++-+
The problem lies elsewhere.  Where that might be is difficult to
say, because no PHP code has been shown yet.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: 4.0.18 vs 4.1.1

[Josh Trutwin]

On Sun, 16 May 2004 13:51:29 -0700
Ron Gilbert <[EMAIL PROTECTED]> wrote:

> 
> I am going to upgrade from 3.23 and was wondering if 4.1.1 is stable
> 
> enough?  This is just for some personal websites, nothing mission 
> critical, but on the other hand, I don't want to deal with endless 
> problems.
> 
> The reason that I'd like to go to 4.1.1 is for sub-selects,
> otherwise I'd stick to 4.0.18.
> 
> What I would really like is go to 5.0 (for stored procedures), is
> 5.0 stable enough for casual use?

I've been using 5.0.0 for some sites and use it for an RDBMS class, nothing mission 
critical mind you, but it's worked very well in my opinion (running on SuSE Linux).  
There were a couple upgrade issues which are covered in the documentation, but nothing 
serious.

YMMV

Josh

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

4.0.18 vs 4.1.1

[Ron Gilbert]

I am going to upgrade from 3.23 and was wondering if 4.1.1 is stable 
enough?  This is just for some personal websites, nothing mission 
critical, but on the other hand, I don't want to deal with endless 
problems.

The reason that I'd like to go to 4.1.1 is for sub-selects, otherwise 
I'd stick to 4.0.18.

What I would really like is go to 5.0 (for stored procedures), is 5.0 
stable enough for casual use?

Ron


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

Converting to Percentages

[David Blomstrom]

Alright, I've finally got table joins figured out, and
I'm now wrestling with a more advanced operation...

Suppose you have a table with four columns. The first
three colums each list a numeral, and the fourth
column lists the sum of those numerals, like this:

3 | 3 | 4 | 10
10 | 10 | 5 | 25

Now, suppose you wanted to also display those numerals
as percentages:

Reds | Blues | Grays | TOTAL
30% | 30% | 40% | 100%
40% | 40% | 20% | 100%

(Actually, I may retain the original numerals in the
last column...)

30% | 30% | 40% | 10
40% | 40% | 20% | 25

I want to give visitors BOTH types of information -
plain numerals and percentages. I'm just trying to
figure out a clever and efficient way of doing it.
Since I already have a table with numerals, I was
hoping there might be a way to convert them to
percentages with MySQL (or PHP).

More specifically, here's a snapshot of my table:

http://www.geoworld.org/percents.gif

Notice the column labeled "Pop" (Population). Each
numeral in that field is the sum of all the numerals
to the right except the last field (Hispanic).

I'd like to include a feature that would allow
visitors to see these numbers as percentages. The
coolest strategy would probably be a simple conversion
- they simply click something or other, and all the
numerals that need to be converted are converted to
percentages.

If that can't be done (or if it's over my head), I
could make a second page with a similar table
featuring percentages. However, it will take me
forever to calculate roughly 25,000 percentages, so it
would still be nice to figure out a way to do it with
MySQL. 

Failing that, I suppose I can always go back to my
spreadsheet and see if I can do it there.

I see some information about "operators" and
multiplication on 
http://dev.mysql.com/doc/mysql/en/Numeric_type_overview.html
but a more user friendly overview would be a great
help. Most of my numeral fields are designated
int(10).

Any tips?

Thanks.





__
Do you Yahoo!?
SBC Yahoo! - Internet access at a great low price.
http://promo.yahoo.com/sbc/

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

Re: Select distinct year from unix timestamp

[John Fawcett]

From: "T. H. Grejc" 
> Hello,
> 
> I'm trying to select all distinct years from a unixtimestamp field in
> MySQL database (3.23.56). I have a query:
> 
> SELECT DISTINCT YEAR(date_field) As theYear FROM table
> 
> but PHP gives me an empty array. What am I doing wrong?
> 
> TNX
> 
I think you need this function

FROM_UNIXTIME(unix_timestamp,format).

Year does not operate on a unix timestamp.

John

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

Tcp/ip connections fail

[Randall Perry]

In the Db table I've got an entry for Db = test, user = dummy, host = ''.

In the Host table I've got several entries for Db = test with different Host
entries, one of which is localhost. Another is an IP on the same server
that's running mysqld. Another is a remote host.

I've got skip-networking commented out in /etc/my.conf and DATADIR/my.cnf.

The only connection allowed to this Db by dummy is through localhost,
connections through other hosts fail with (domain name changed for
security):
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)

What else could be blocking net connections?

-- 
Randall Perry
sysTame

Xserve Web Hosting/Co-location
Website Development/Promotion
Mac Consulting/Sales

http://www.systame.com/



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

RE: Select distinct year from unix timestamp

[Dathan Vance Pattishall]

R u sure your printing out the correct array (hash) field? Did you connect
to the db? Is mysql_error reporting an error?


DVP

Dathan Vance Pattishall http://www.friendster.com


> -Original Message-
> From: news [mailto:[EMAIL PROTECTED] On Behalf Of T. H. Grejc
> Sent: Sunday, May 16, 2004 11:36 AM
> To: [EMAIL PROTECTED]
> Subject: Select distinct year from unix timestamp
> 
> Hello,
> 
> I'm trying to select all distinct years from a unixtimestamp field in
> MySQL database (3.23.56). I have a query:
> 
> SELECT DISTINCT YEAR(date_field) As theYear FROM table
> 
> but PHP gives me an empty array. What am I doing wrong?
> 
> TNX
> 
> 
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]




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

Select distinct year from unix timestamp

[T. H. Grejc]

Hello,
I'm trying to select all distinct years from a unixtimestamp field in
MySQL database (3.23.56). I have a query:
SELECT DISTINCT YEAR(date_field) As theYear FROM table
but PHP gives me an empty array. What am I doing wrong?
TNX

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

Re: User permissions error on CREATE TABLE

[Randall Perry]

Never mind.

Didn't have 'localhost' in Db table 'Host' field for the database.


> I've got a user, joe, who has all privileges on database 'joe' except GRANT.
> I gave permissions to joe using this command:
>   GRANT ALL ON joe TO [EMAIL PROTECTED]
> 
> But if I'm logged in as joe to the joe db and try to create a table, I get
> the error:
> 
> ERROR 1142: create command denied to user: '[EMAIL PROTECTED]' for table 'test'
> 
> What could be wrong here?

-- 
Randall Perry
sysTame

Xserve Web Hosting/Co-location
Website Development/Promotion
Mac Consulting/Sales

http://www.systame.com/



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

User permissions error on CREATE TABLE

[Randall Perry]

I've got a user, joe, who has all privileges on database 'joe' except GRANT.
I gave permissions to joe using this command:
GRANT ALL ON joe TO [EMAIL PROTECTED]

But if I'm logged in as joe to the joe db and try to create a table, I get
the error:

ERROR 1142: create command denied to user: '[EMAIL PROTECTED]' for table 'test'

What could be wrong here?

-- 
Randall Perry
sysTame

Xserve Web Hosting/Co-location
Website Development/Promotion
Mac Consulting/Sales

http://www.systame.com/



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

Re: optimizing inserts

[Bill Allaire]

On May 16, 2004, at 1:15 AM, Ron Gilbert wrote:
I have a table that is:
CREATE TABLE GPSData (
  ID int(10) unsigned NOT NULL auto_increment,
  Lat decimal(9,5) default '0.0',
  Lon decimal(9,5) default '0.0',
  TDate datetime default NULL,
  PRIMARY KEY (ID),
  UNIQUE KEY ID (ID),
  KEY ID_2 (ID)
) TYPE=MyISAM;

Why do you have a unique key on 'id' when you also have a primary key 
on 'id'? Primary keys are unique.

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

Re: optimizing inserts

[beacker]

Ron Gilbert <[EMAIL PROTECTED]> writes:
>It currently takes 15 or 20 minutes to run though a 10K to 20K GPS track 
>logs.  This seems too long to me.  I took out the INSERTS to just to 
>make sure it wasn't my PHP scripts, and they run in a few seconds 
>without the MySQL calls.

Doing a lot of inserts in this manner introduces considerable delays for
each one as the information is sent across the connection, processed, and
the result returned.  Have you considered using "LOAD DATA INFILE..."?
You can transfer across the entire data set into a temporary file and then
use the LOAD DATA command of the temporary file.  To make sure you haven't
already loaded the data set you could do a single select on the first
element of the data set, loading if there are no rows returned.
 Brad Eacker ([EMAIL PROTECTED])



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

Re: Automatically optimizing a table - how should I so this?

[Joshua Beall]

"Per Andreas Buer" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> "Joshua Beall" <[EMAIL PROTECTED]> writes:
>
> > "Daniel Kasak" <[EMAIL PROTECTED]> wrote in message
> > news:[EMAIL PROTECTED]
> >> Is there any particular reason why you think the table will need
> >> optimizing, or do you just want everything to be super-optimized?
> >
> > Because when I pull up phpMyAdmin, and it says there is 3,768 bytes of
> > overhead, I just feel *dirty*!  Overhead, after all, is a Very Bad
> > Thing!
>
> Yeah. And doing a full table optimization after updating one single row
> does not at all add any overhead. Not at all. :P

It doesn't count if you are there watching it.  Just like when you watch a
tea kettle; it doesn't boil.




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

Re: Automatically optimizing a table - how should I so this?

[Per Andreas Buer]

"Joshua Beall" <[EMAIL PROTECTED]> writes:

> "Daniel Kasak" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
>> Is there any particular reason why you think the table will need
>> optimizing, or do you just want everything to be super-optimized?
>
> Because when I pull up phpMyAdmin, and it says there is 3,768 bytes of
> overhead, I just feel *dirty*!  Overhead, after all, is a Very Bad
> Thing!

Yeah. And doing a full table optimization after updating one single row
does not at all add any overhead. Not at all. :P

-- 
Per Andreas Buer

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

Re: optimizing inserts

[Jigal van Hemert]

> data points.  I don't want duplicate entries, mostly due to sections of
> the log accidentally being uploaded twice.  I am currently doing a

Ok, so it is EXACTLY the same data that might be inserted twice?

- Make a UNIQUE index for the relevant column(s) that uniquely identify a
record.
- Use "INSERT IGNORE "

This way you will have no duplicates and no inserts will fail (MySQL will
simply ignore duplicate entries)

Hope this helps a bit.

Regards, Jigal.


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

Re: basic mysql setup

[Scott Haneda]

on 05/16/2004 01:15 AM, bruce at [EMAIL PROTECTED] wrote:

> but this brings to mind a question. is it possible to have multiple users
> accessing the phpMyAdmin app, with each having different access rights for
> different databases/tables...??

Yes, that's exactly what it does, you basically assign a user and a pass to
be able to have certain permissions.  In most cases I allow one user and
pass to be able to work on their own database, that's it, they have full
rights on that database, and that database only.

When you go to the phpmyadmin login page and enter in that username and
password, they only see the one database in the list on the left.

Conversely, if you login as root, you will see all the databases.

You could also grant other privs to other users if they needed to see more
than one database, or perhaps give someone special control if you want to
allow others to admin mysql in general.

> and what the h*^* are you doing up at this time of night!!

Its only 1 :-)
-- 
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com   Fax: 313.557.5052
[EMAIL PROTECTED]Novato, CA U.S.A.



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

RE: basic mysql setup

[bruce]

scott...

thanks..i'll take a look...

but this brings to mind a question. is it possible to have multiple users
accessing the phpMyAdmin app, with each having different access rights for
different databases/tables...??

if this gets confusing, can i bring the questions back to this list..???

and what the h*^* are you doing up at this time of night!!

thanks...



-Original Message-
From: Scott Haneda [mailto:[EMAIL PROTECTED]
Sent: Sunday, May 16, 2004 12:51 AM
To: MySql
Subject: Re: basic mysql setup


on 05/16/2004 12:52 AM, bruce at [EMAIL PROTECTED] wrote:

>
> i'm running rh 8.0 with mysql on a test system. i'm relatively new to the
> admin functions of mysql. another person setup the mysql on the system.
>
> in reviewing the setup, it appears that mysql was setup with using root,
and
> the root password. when i look at phpMyAdmin, it is also setup using the
> root. currently, it seems to provide whoever accesses the app with the
> ability to make whatever changes they want!! obviously not a good thing!
>
> can someone walk me through what i need to do to change mysql/phpMyAdmin
so
> that only specific users can make specific changes to the underlying
> database/tables/etc...
>
> i've taken a look at the mysql docs, and am a little confused. i've also
> taken a look at the phpMyAdmin site/underlying files/docs to see if there
is
> a way to change the setup, but i can't seem to find what/where a change
> should be made..

You should look at using the "Cookie" auth type in phpmyadmin.  Your mysql
setup is fine, you need a root user, you just don't want phpmyadmin logging
in as that user all the time.



That link will tell you how to change your config file and create what they
call a control user with control username and password.

--
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com   Fax: 313.557.5052
[EMAIL PROTECTED]Novato, CA U.S.A.



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


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

Re: basic mysql setup

[Scott Haneda]

on 05/16/2004 12:52 AM, bruce at [EMAIL PROTECTED] wrote:

> 
> i'm running rh 8.0 with mysql on a test system. i'm relatively new to the
> admin functions of mysql. another person setup the mysql on the system.
> 
> in reviewing the setup, it appears that mysql was setup with using root, and
> the root password. when i look at phpMyAdmin, it is also setup using the
> root. currently, it seems to provide whoever accesses the app with the
> ability to make whatever changes they want!! obviously not a good thing!
> 
> can someone walk me through what i need to do to change mysql/phpMyAdmin so
> that only specific users can make specific changes to the underlying
> database/tables/etc...
> 
> i've taken a look at the mysql docs, and am a little confused. i've also
> taken a look at the phpMyAdmin site/underlying files/docs to see if there is
> a way to change the setup, but i can't seem to find what/where a change
> should be made..

You should look at using the "Cookie" auth type in phpmyadmin.  Your mysql
setup is fine, you need a root user, you just don't want phpmyadmin logging
in as that user all the time.



That link will tell you how to change your config file and create what they
call a control user with control username and password.

-- 
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com   Fax: 313.557.5052
[EMAIL PROTECTED]Novato, CA U.S.A.



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

basic mysql setup

[bruce]

hi...

i'm running rh 8.0 with mysql on a test system. i'm relatively new to the
admin functions of mysql. another person setup the mysql on the system.

in reviewing the setup, it appears that mysql was setup with using root, and
the root password. when i look at phpMyAdmin, it is also setup using the
root. currently, it seems to provide whoever accesses the app with the
ability to make whatever changes they want!! obviously not a good thing!

can someone walk me through what i need to do to change mysql/phpMyAdmin so
that only specific users can make specific changes to the underlying
database/tables/etc...

i've taken a look at the mysql docs, and am a little confused. i've also
taken a look at the phpMyAdmin site/underlying files/docs to see if there is
a way to change the setup, but i can't seem to find what/where a change
should be made..

thanks for any help you can provide with this!!

bruce
[EMAIL PROTECTED]


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