> > I'm creating a new MySQL database from an existing Filemaker db.
> >
> > My problem is that some of the existing 'numbers' in one column (it
> > was a text field in FMP) have leading zeros. eg: 003, 0007, 012,
> > 001234. I need to maintain these numbers 'as is' - complete with
> > zeros
- Original Message -
From: "gerald_clark" <[EMAIL PROTECTED]>
To: "Jonathan Mangin" <[EMAIL PROTECTED]>
Cc:
Sent: Tuesday, May 16, 2006 3:08 PM
Subject: Re: Converting varchar field into primary key
> Jonathan Mangin wrote:
>
> >>>
- Original Message -
From: "George Law" <[EMAIL PROTECTED]>
To: "MYSQL General List"
Sent: Wednesday, August 09, 2006 3:40 PM
Subject: forcing leading 0 for numeric fields
Hello All,
I have what is probably a simple question.
I have a table of phone numbers, broken into npa,nxx,stati
Section 3.6.2 of the 4.1 manual has this example
using a subselect:
SELECT article, dealer, price
FROM shop
WHERE price=(SELECT MAX(price) FROM shop);
I use this basic syntax with max(date) alot.
- Original Message -
From: "João Cândido de Souza Neto" <[EMAIL PROTECTED]>
To:
Sent:
Hi,
I'm using DBI and a dsn with 'mysql_client_found_rows=0' appended.
A normal update returns 0E0 if no data has changed.
The update part of insert...on duplicate key update always returns
2, whether data has changed or otherwise, plus the timestamp
column is not automatically updated.
Is there
- Original Message -
From: "Juan Pedro Reyes Molina" <[EMAIL PROTECTED]>
To: "Hassan Schroeder" <[EMAIL PROTECTED]>
Cc: "mysql general list"
Sent: Saturday, June 25, 2005 2:08 PM
Subject: Re: starting mysql 4.1.12 on Ubuntu Linux
hello, Hassan
which mysql gives me /usr/bin/mysql. e
Hello all,
I'm storing data from a series of tests throughout each
24-hour period. I thought to create a table for each test.
(There are six tests, lots more cols per test, and many
users performing each test.)
select test1.date, test1.time, test2.date, test2.time from
test1 left join test2 on
Jonathan Mangin wrote:
Hello all,
I'm storing data from a series of tests throughout each
24-hour period. I thought to create a table for each test.
(There are six tests, lots more cols per test, and many
users performing each test.)
But each test is performed no more than once per d
Hi,
I think I'd like to store these values as strings
instead of [?]int or time types.
Is this string arithmetic? Can/should I do this?
(I see the second one won't work without single-quotes.)
mysql> select '3' - '1';
+---+
| '3' - '1' |
+---+
| 2 |
+---+
1 row
I see these really need to be int or time types.
Is there no way they can default to NULL or blank?
Hi,
I think I'd like to store these values as strings
instead of [?]int or time types.
mysql> select '3' - '1';
+---+
| '3' - '1' |
+---+
| 2 |
+---+
1 row in se
Hello all,
This works if t2 is populated:
select t1.item_no, t1.value1 *
(select weight from t2 where
item_no = t1.item_no and
descrip = 'dime bag')
from t1
where t1.descrip = 'marigold seeds'
If t1.item_no and/or 'dime bag' don't exist in t2 I'd
like to multiply by a different value (5):
sele
- Original Message -
From: "Jonathan Mangin" <[EMAIL PROTECTED]>
To:
Sent: Saturday, September 24, 2005 2:18 PM
Subject: Using ifnull in a subquery
Hello all,
This works if t2 is populated:
select t1.item_no, t1.value1 *
(select weight from t2 where
item_no
I have two tables with date and uid cols. in common.
Table 1 has one row per date, Table 2 has a maximum
of 7 rows per date.
select t1.date, t1.val, t2.val from t1
right join t2 on t1.date = t2.date
where t1.date between '2005-08-01' and '2005-08-14'
and t1.uid = 'me';
+-
- Original Message -
From: <[EMAIL PROTECTED]>
To: "Jonathan Mangin" <[EMAIL PROTECTED]>
Cc:
Sent: Friday, October 07, 2005 2:09 PM
Subject: Re: Joining tables, duplicating none
"Jonathan Mangin" <[EMAIL PROTECTED]> wrote on 10/07/2005 02:57:28
I thought I'd found a mistake...
$sql = "(select date(date) as date,
time_format(time(date),'%H:%i') as time,
units, # I forgot to include this
round(sum(item1 * units),2),
but all the numbers are correct. Does mysql know to multiply b
Stupid question. units is no different from item1 at this point.
- Original Message -
From: "Jonathan Mangin" <[EMAIL PROTECTED]>
To:
Sent: Tuesday, November 15, 2005 2:36 PM
Subject: Missing column in select??
I thought I'd found a mistake...
$sql
I have a simple query, (part of a union).
calendar is a table of only dates, `date` as PK.
sales has a multi-col index on (date, uid).
(select calendar.date as date,
time_format(time(sales.date),'%H:%i') as time,
from calendar
left join sales
on date(sales.date) = calendar.d
- Original Message -
From: "Jonathan Mangin" <[EMAIL PROTECTED]>
To:
Sent: Monday, November 21, 2005 8:48 AM
Subject: Index of JOINed table?
I have a simple query, (part of a union).
calendar is a table of only dates, `date` as PK.
sales has a multi-col ind
(I used to have separate date/time cols. in all tables
but changed them to datetime and buggered up some stuff.
Now I'm trying to find the best way to fix this.)
If I have an indexed datetime column (`date`), and say:
select date,
from table1
where date between '2005-08-01' and '200
I'm trying to correct a situation I've created after
combining separate date/time columns into a datetime column.
Here are skeletons of new table definitions.
CREATE TABLE products
(id mediumint unsigned primary key not null auto_increment,
item varchar(40) not null,
priceA decimal(6,3),
I created a calendar table (date only), but all
where clauses include a uid. Is the following a
sane workaround to get a usable calendar table?
Anything else I can do?
my $sth = $dbh->prepare("
create table $temp_tbl
(date date,
uid varchar(14))
select date,
? as uid
from calendar
where date betw
- Original Message -
From: "Rhino" <[EMAIL PROTECTED]>
To: "Jonathan Mangin" <[EMAIL PROTECTED]>
Sent: Wednesday, January 04, 2006 1:25 PM
Subject: Re: Calendar table workaround
>
> - Original Message -
> From: "Jonathan Mangin
RE: Calendar table workaround>
>A table of dates to which to join other tables,
>ensuring reports that reflect days for which no
>data is available.
I forget the query but I know it can be done. But can't you just have a table
(called calendar?) with each entry having it's own row with a date co
I got exactly that error message last night when doing a
numeric comparison on a varchar column. Oops.
Kind of misleading, though.
- Original Message -
From: "David Godsey" <[EMAIL PROTECTED]>
To: "George Law" <[EMAIL PROTECTED]>
Cc:
Sent: Tuesday, January 24, 2006 4:43 PM
Subject: RE:
I'm trying to change a couple of replace statements to
insert...on duplicate key update (using Perl/DBI).
foreach my $key (keys %e_items) {
my $sql = "insert table1
(id, date, time, uid, type, seq, value)
values
(?, ?, ?, ?, ?, ?, ?)
on du
- Original Message -
From: "bob pilly" <[EMAIL PROTECTED]>
To:
Sent: Sunday, February 12, 2006 12:44 AM
Subject: Returning values from an INSERT
> Hi everyone, im new to SQL and have a question that someone can hopefully
answer
>
> If i am inserting a new record into a table that has
- Original Message -
From: "Douglas S. Davis" <[EMAIL PROTECTED]>
To:
Sent: Tuesday, February 21, 2006 3:58 PM
Subject: Creating a Web Database Search Application
> Hello,
>
> I commonly create webpages that need to search through a MySQL
> database and then display the results to the
- Original Message -
From: "Douglas S. Davis" <[EMAIL PROTECTED]>
To:
Sent: Tuesday, February 21, 2006 3:58 PM
Subject: Creating a Web Database Search Application
> Hello,
>
> I commonly create webpages that need to search through a MySQL
> database and then display the results to the
- Original Message -
From: "Ferindo Middleton Jr" <[EMAIL PROTECTED]>
To: "Ferindo Middleton Jr" <[EMAIL PROTECTED]>
Cc: "Hank" <[EMAIL PROTECTED]>;
Sent: Friday, March 31, 2006 7:30 PM
Subject: Re: TIMESTAMP field not automatically updating last_updated field
> Ferindo Middleton Jr wr
I'm getting incorrect results from a sum and wonder if
anyone sees something obviously wrong. (Won't surprise
me.) Leaving 'simple' out of the equation (or adding
'simple' values manually) gets me the correct number.
$menu is a personalized table of meal/recipe ingredients.
itemized is a list of
- Original Message -
From: "Jake Peavy" <[EMAIL PROTECTED]>
To: "Jonathan Mangin" <[EMAIL PROTECTED]>
Cc:
Sent: Saturday, April 05, 2008 11:59 AM
Subject: Re: Incorrect results from sum
On 4/5/08, Jonathan Mangin <[EMAIL PROTECTED]> wrote:
I&
Hi,
Here's the basic query I'm trying. It's supposed to return
the totals of each nutrient (carb, in this case) by date.
The data totals 218.31, but I get 190.80.
select itemized.day_date as day_date,
round(sum(my_menu.carb * units) + simple.carb,2)
from itemized inner join simple using (uid) in
Sorry to return to this topic, I haven't found a lot to explain
what's happening.
I'm trying to total certain nutrients consumed on a given date
(though I've removed date temporarily).
You'll see I have three items (in two meals) in itemized,
and two meal totals in simple.
mysql> select id, ite
- Original Message -
From: "Daniel da Veiga" <[EMAIL PROTECTED]>
To:
Sent: Thursday, December 07, 2006 9:48 AM
Subject: Re: Renaming the root user - problems.
> On 12/7/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> > Hello,
> >
> > my question refers to the user "root" in MySQL 5.
File downloads are enabled in IE but refuse to work.
Does anyone know where I can FTP the latest version
of mod_auth_mysql?
Thanks,
Jon
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Hello,
Does this mean a key is not being used?
mysql> explain select vl_ts from view_log where vl_uid='bb';
+--+--+---+--+-+--+--++
| table| type | possible_keys | key | key_len | ref | rows | Extra
|
+--+--+
man pkgadd
-J
- Original Message -
From: "Joerg Bruehe" <[EMAIL PROTECTED]>
To:
Cc: "Jonathan Stockley" <[EMAIL PROTECTED]>
Sent: Thursday, March 17, 2005 9:16 AM
Subject: Re: 4.1.10a packaging for Solaris
Hi Jonathan, all!
Am Mi, den 16.03.2005 schrieb Jonathan Stockley um 22:33:
I jus
I would like to select several rows from one table
and insert them into another nearly identical table
using Perl/DBI:
my @array = $q->param(); # HTML checkboxes
foreach my $element (@array) {
my $sql = "select col2, col3, col4 from table1
where col1 = ?";
my $sth = $dbh->prepare(
- Original Message -
From: "Jonathan Mangin" <[EMAIL PROTECTED]>
To:
Sent: Tuesday, April 26, 2005 11:26 AM
Subject: Efficient select/insert
I would like to select several rows from one table
and insert them into another nearly identical table
using Perl/DBI:
my @a
- Original Message -
From: <[EMAIL PROTECTED]>
To: "Jonathan Mangin" <[EMAIL PROTECTED]>
Cc:
Sent: Tuesday, April 26, 2005 3:20 PM
Subject: Re: Efficient select/insert
"Jonathan Mangin" <[EMAIL PROTECTED]> wrote on 04/26/2005 12:26:20
PM:
I woul
> This would be even faster if you could concatenate all of the elements
of
> @array into a single list then you could say:
>
> #My PERL skills are non existent so you need to write this part.
> #I am assuming that @array is holding a list of string values.
>
> foreach my $element (@array) {
> @ar
- Original Message -
From: "Eamon Daly" <[EMAIL PROTECTED]>
To: "Jonathan Mangin" <[EMAIL PROTECTED]>
Cc:
Sent: Thursday, May 19, 2005 1:17 PM
Subject: Re: Efficient select/insert
my $sql = sprintf <<'EOF', join(',', @array);
Thanks, that works (I'll have to read a bit to learn why)
except for one thing I didn't mention. (Everybody Lies :)
How ugly is this?
$sql = "INSERT into $table2"; # dynamic name with $user_id as root
$sql .= sprintf <<'EOF', join(',', @array);
I see.
I suppose this produced quite a few grins.
Ha
Hi all,
With 3.23.38 I have:
my $sql = "LOCK tables TBUSR write, TBAUTH write, TBDATALOG write,
TBAGCY write, TBREL write, TBACCESSLOG write";
my $sth = $dbh->prepare($sql);
$sth->execute($sql) || die "Cannot lock: " . $sth->errstr();
I get:
Cannot lock: called with 1 bind variables when 0 are need
Hmmm, you're probably right. I got carried away.
- Original Message -
From: "Mike Wexler" <[EMAIL PROTECTED]>
To: "Jonathan Mangin" <[EMAIL PROTECTED]>
Sent: Sunday, October 24, 2004 4:53 PM
Subject: Re: Cannot lock: called with 1 bind variables
Wexler" <[EMAIL PROTECTED]>
To: "Jonathan Mangin" <[EMAIL PROTECTED]>
Sent: Sunday, October 24, 2004 4:53 PM
Subject: Re: Cannot lock: called with 1 bind variables when 0 are needed
Try this:
my $sql = "LOCK tables TBUSR write, TBAUTH write, TBDATALOG write,
TBAGCY
- Original Message -
From: "Jay Blanchard" <[EMAIL PROTECTED]>
To: "Chip Wiegand" <[EMAIL PROTECTED]>; "MySQL List"
<[EMAIL PROTECTED]>
Sent: Thursday, November 18, 2004 8:10 AM
Subject: RE: copy data only from one table to another table
[snip]
How do I copy all data only from one table
- Original Message -
From: "Jay Blanchard" <[EMAIL PROTECTED]>
To: "Jonathan Mangin" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, November 18, 2004 9:19 AM
Subject: RE: copy data only from one table to another table
[snip]
INSER
- Original Message -
From: "John Berman" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Sunday, August 01, 2004 7:27 AM
Subject: Connectiing
> Hi
>
> Sorry if I'm off topic but I am a touch desperate
>
>
> We make use of a mysql 4.x hosted by a third party and we use ASP to
access
>
If you've installed the jar in $JAVA_HOME/lib/ext it shouldn't
need to be referenced in $CLASSPATH.
- Original Message -
From: "Mahesh S" <[EMAIL PROTECTED]>
To: "my sql" <[EMAIL PROTECTED]>
Sent: Thursday, August 05, 2004 1:36 AM
Subject: unable to connect mysql and jsp
> hi all,
>
>
I didn't actually count the characters but it looks very close to a
512-character limit. (I used a pica stick on the screen, very accurate!)
The error message appears to be showing a truncated line. Is possible?
--Jon
> > >
> > > INSERT INTO dirxml.emp
> > >
(fld_ind_id,fld_frname,fld_srname,fld_
> >
> > I have tried different URL's, including trying the ftp URL at Oakland,
> > USA, and the Australian URL previously cited. I get the same problem -
> > instead of getting a dialogue box giving me the option of saving the
> > file to disk (and, in what path), the browser commences downloading
- Original Message -
From: "æé" <[EMAIL PROTECTED]>
To: "mysql" <[EMAIL PROTECTED]>
Sent: Wednesday, September 15, 2004 9:25 AM
Subject: mysql still can't start up
> when I used the command "safe_mysqld start" the system told me "
> Starting mysqld daemon with databases from /var/lib/my
Mine is owned by root:root and has its sticky bit set.
Does not allow a normal user to delete.
- Original Message -
From: "Annie Xie" <[EMAIL PROTECTED]>
To: "Jonathan Mangin" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
54 matches
Mail list logo
