Re: Duplicate selection with change

[Scott Haneda]

on 06/27/2004 11:31 PM, Scott Haneda at [EMAIL PROTECTED] wrote:

> So, I need to basically:
> SELECT iamge_title, image_filename, image_url, image_width, image_height
> from images where user_id = '123';
> 
> Then, take that result and insert all those rows in the same table, setting
> type='2' and added=NOW()
> 
> I think I need to do a subselect, the docs are really sparse on examples,
> and I don't see how to change the 2 values I need to change.

I may have just made this work in my simple testing, unless someone can tell
me a better, way, this is what I came up with:

insert into images (user_id, image_title, image_filename, image_url, type,
added) select user_id, image_title, image_filename, image_url, '4', NOW()
from newsletter_images where user_id = '123';

So basically, since I need to select the SAME fields I am inserting, just
select the 2 that I need to change the data in, ignore what they may or may
not have, and drop in hard values in the select statement.

-- 
-
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]

Duplicate selection with change

[Scott Haneda]

4.0.18-standard

I need to duplicate a select of records, all in the same table, but also
make a change to one field.

Here is one table I need to do this on
++---+--+-++
| Field  | Type  | Null | Key | Default|
++---+--+-++
| id | int(11)   |  | PRI | NULL   |
| user_id| int(11)   |  | | 0  |
| image_title| varchar(48)   |  | ||
| image_filename | varchar(32)   |  | ||
| image_url  | varchar(255)  |  | ||
| image_width| int(3)|  | | 0  |
| image_height   | int(3)|  | | 0  |
| type   | tinyint(1)|  | | 0  |
| updated| timestamp(14) | YES  | | NULL   |
| added  | timestamp(14) | YES  | | 00 |
++---+--+-++

So, I need to basically:
SELECT iamge_title, image_filename, image_url, image_width, image_height
from images where user_id = '123';

Then, take that result and insert all those rows in the same table, setting
type='2' and added=NOW()

I think I need to do a subselect, the docs are really sparse on examples,
and I don't see how to change the 2 values I need to change.
-- 
-
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]

INNODB transaction log size

[Michael Lee]

Hi,
 
I would like to migrate my DB from Sybase ASE to MySQL INNODB table. Data has been 
extracted and stored as a file. I want to use the command Load Data Infile to insert 
the data to MySQL. However, some table contains millions of rows. Can i control the 
batch size of the loading (e.g. commit the transaction after 5 rows inserted)?
 
If no, should i define a very large transaction log to handle the huge transaction? 
(currently, it is 5M)
 
Any suggestion is welcomed.
 
TIA
Michael 

[EMAIL PROTECTED]
全港最大手機下載中心
http://mobile.yahoo.com.hk/

Re: INDEX DESC

[Robert A. Rosenberg]

At 16:38 -0400 on 06/23/2004, Michael Stassen wrote about Re: INDEX DESC:
 > For example, I want to read ranges of values from my db in the opposite
 order of how they are currently being indexed without using an extra
 ORDER BY in my select. This ORDER BY forces an internal sort to put the
 result set in DESC order which slows my SELECT.
You cannot count on rows being returned in any order unless you explicitly
use ORDER BY.
True. What you are ignoring is that if you ORDER BY an INDEXED 
column, the Index is used to read the records directly without an 
intervening Sort (so long as both the ORDER BY and the INDEX are 
ASC). If the Index were defined (and stored) as DESC, then an ORDER 
BY DESC would likewise just use the Index without the need for the 
sort that currently must get used.

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

Re: matching any values of a set to any values of another set

[Michael Stassen]

tidalx wrote:
Hi, i am trying to match a set of values to another set of values, and to
return true if any of the values of one set matches any values of the second
set.
Something like
SELECT * FROM listings WHERE FIND_IN_SET('Mastercard,Visa', 'AMEX, Cash,
Mastercard, Visa') > 0;
  SELECT * FROM listings
  WHERE 'Mastercard' IN ('AMEX', 'Cash', 'Mastercard', 'Visa')
 OR 'Visa' IN ('AMEX', 'Cash', 'Mastercard', 'Visa');
which I don't think works, because find_in_set can't do more than one value
or because of the comma but i can't seem to figure it out using IN, LIKE or
other operators/functions.
And the values on both sets are not constant, which confuses me even more.
SELECT * FROM listings WHERE FIND_IN_SET('$payment_type1, $payment_type2',
'$payments_accepted_column') > 0;
  SELECT * FROM listings
  WHERE FIND_IN_SET($payment_type1, $payments_accepted_column)
 OR FIND_IN_SET($payment_type2, $payments_accepted_column);
You could probably also do this with one regular expression.  Something like:
  SELECT * FROM listings
  WHERE payments_accepted RLIKE 'Visa|Mastercard';
Any help is appreciated, thank you
There is almost certainly a better way to do this, with a more normalized 
data design.

One option would be to use the SET type for the pay_accepted column
  pay_accepted SET('Cash','Check','AMEX','Discover','Mastercard','Visa')
This would save a lot of space compared to the comma separated list in a 
CHAR() or VARCHAR you apparently have now, and you could still use the 
second SELECT I gave above.  You could also use bit arithmetic.  Each 
element of the set corresponds to a power of 2 (Cash=1, Check=2, AMEX=4, 
Discover=8, and so on).  So, to find listings which accept Cash (=1) or 
Check (=2), you could

  SELECT ... WHERE pay_accepted & (1+2);
or to find listings which accept Cash (=1), Visa (=32), or Discover (=8) you 
could

   SELECT ... WHERE pay_accepted & (1+8+32);
This should be more efficient than ORing several FIND_IN_SETs, I expect, but 
it has the disadvantage that you need to know the number which goes with 
each set element.

Another option would be to make a payments table:
id method
1  Cash
2  Check
3  AMEX
4  Discover
5  MasterCard
6  Visa
and a listing_payment relation table
listing_id  payment_id
1   1
1   2
2   1
2   4
2   5
2   6
...
Then you would do something like
  SELECT DISTINCT ...
  FROM listings l JOIN listing_payment lp ON l.id=lp.listing_id
  JOIN payments p ON p.id=lp.payment_id
  WHERE p.method IN ('Cash', 'Visa', 'Discover');
Michael

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

Re: WHERE field IN (SELECT stmt) not working

[Michael Stassen]

It's not a bug.  MySQL 4.0.20 does not support subqueries.  You need 4.1 for 
that.  You can probably rewrite your query as a JOIN, however.  See the 
manual  for 
some suggestions.

Michael
Oliver Chua wrote:
Sorry if this has been posted as a bug.
I tried searching in the bug database but the word IN (which is the operative word here) 
is being ignored by the search engine...

I'm using mysql-4.0.20a-win.zip.
WHERE field IN (SELECT stmt) not working
but WHERE field IN ('literal1', 'literal2' ) is working
the former should be working as stated in the manual...
is it a bug and if it is, then what is the alternate solution?
thanks in advance.

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

Re: INDEX DESC

[Robert A. Rosenberg]

At 15:30 -0400 on 06/23/2004, Michael Stassen wrote about Re: INDEX DESC:
The manual  says "An
index_col_name specification can end with ASC or DESC. These keywords are
allowed for future extensions for specifying ascending or descending index
value storage. Currently they are parsed but ignored; index values are
always stored in ascending order."
Something that is VERY easy to fix and implement. So long as you KNOW 
that the Index is being defined as DESC, just STORE the Index value 
as Field XOR xFF..FF (FF..FF being the length of the Keyed Field). 
The Index is then AUTOMATICALLY in Descending order and you just need 
to XOR to recover the actual key value if/when you need it.

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

RE: Flowing Text Into Multiple Columns

[David Rodman]

]=Suppose I want to display an entire field, but not in
]=one long column. Instead, I want to flow it evenly
]=into several columns.
]=

You're looking for the wrong type of screwdriver to drive this particular
nail.  MySQL is not a text processor.  This could be done fairly simply with
PHP.  Depending on your OS, you could use pr (if you have *NIX).  There
might be a similar utility for the DOZE systems, I don't know.

But in any event - you're in the wrong place for this question!
:-)
David


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

RE: Merging 2 Fields

[David Rodman]

]=>
]=> mysql> insert into nads values('Florida',
]=> 'http://www.florida.gov');
]=> Query OK, 1 row affected (0.00 sec)
]=
]=I'm a little confused here. I'm slightly more familiar
]=with manipulating PHP than MySQL. It looks like your
]=suggestion is a permanent fix. It just occurred to me
]=that it would be nice if I had the option of
]=displaying Florida as plain text or as a link.
]=
]=But if I do this, Florida will always be linked,
]=right? Also, it looks like you have to do this
]=operation for each row - California, Florida, etc. Is
]=there a simple command that merges two entire columns?

Part of the confusion might be that many mail clients, including yours and
mine, automatically re-display URL's as links.  SO where I wrote:
insert into nads values('Florida', 'www dot florida dot gov')
our ever-helpful email software insisted on reformatting the URL field as a
link, which as you say is what you're trying to do in the first place.

I was just putting in sample values to parallel the database you were
describing.
The terminology you're using is not quite accurate - you are not asking to
merge two columns.  If that's what you want to do, you can create a new
database using the output of the CONCAT command I suggested, and then
replace your existing database with the new one.  But I believe you're
asking how to display the contents of two columns, interspersed with some
text, in the output of a query.  That's not merging.

If you're more familiar with PHP than MySQL you can do exactly the same
thing using PHP's string concatenation capabilities.

-- David


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

WHERE field IN (SELECT stmt) not working

[Oliver Chua]

Sorry if this has been posted as a bug.
I tried searching in the bug database but the word IN (which is the operative word 
here) 
is being ignored by the search engine...

I'm using mysql-4.0.20a-win.zip.
WHERE field IN (SELECT stmt) not working
but WHERE field IN ('literal1', 'literal2' ) is working

the former should be working as stated in the manual...
is it a bug and if it is, then what is the alternate solution?

thanks in advance.

Re: REGEXP and word boundary match

[Luke Majewski]

Paul DuBois wrote:
Why do you think this?  That's not what the regex chapter in the MySQL
manual says.  \b works in Perl, but MySQL isn't Perl.
I looked through the pattern matching section and didn't see what you 
linked below.

The easiest way to find out the correct syntax is to look in the MySQL
manual. :-)
http://dev.mysql.com/doc/mysql/en/Regexp.html
sweet, just what i needed :)
Thanks!
Luke

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

Re: Query problem

[Eric Bergen]

Post the table structure, what that query it returning and what you
think it should return.

-Eric

On Sun, 27 Jun 2004 23:33:55 +0200, Schalk <[EMAIL PROTECTED]> wrote:
> 
> Why is the following query retuning doctype's different to what is asked
> for?
> SELECT * FROM documents WHERE jstate = 'California: State Court' AND doctype
> = 'Verdict'
>  
> Any ideas? As far as I can see it should only return a document if it is a
> Verdict and matches the state California: State Court.
> 
> Kind Regards
> Schalk Neethling
> Web Developer.Designer.Programmer.President
> Volume4.Development.Multimedia.Branding
> emotionalize.conceptualize.visualize.realize
> Tel: +27125468436
> Fax: +27125468436
> email:[EMAIL PROTECTED]
> web: www.volume4.co.za
>  
> This message contains information that is considered to be sensitive or
> confidential and may not be forwarded or disclosed to any other party
> without the permission of the sender. If you received this message in error,
> please notify me immediately so that I can correct and delete the original
> email. Thank you.
> 
> 
> --
> 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]

matching any values of a set to any values of another set

[tidalx]

Hi, i am trying to match a set of values to another set of values, and to return true 
if any of the values of one set matches any values of the second set.



Something like

SELECT * FROM listings WHERE FIND_IN_SET('Mastercard,Visa', 'AMEX, Cash, Mastercard, 
Visa') > 0;

which I don't think works, because find_in_set can't do more than one value or because 
of the comma but i can't seem to figure it out using IN, LIKE or other 
operators/functions.

And the values on both sets are not constant, which confuses me even more.

SELECT * FROM listings WHERE FIND_IN_SET('$payment_type1, $payment_type2', 
'$payments_accepted_column') > 0;

Any help is appreciated, thank you

Re: REGEXP and word boundary match

[Paul DuBois]

At 19:14 -0400 6/27/04, Luke Majewski wrote:
Hi All,
I have fields like:
"Washable Velour Doll"
in my database and I want to do a boundary match so that when people 
enter a search field like "Velour" I return all instances where the 
whole word exists.  In other words, 'lour' would not work.  I know 
that reg expressions have boundary matching, so \bVelour\b should 
match all whole words "Velour."
Why do you think this?  That's not what the regex chapter in the MySQL
manual says.  \b works in Perl, but MySQL isn't Perl.
So I've tried making this into a query many different ways, and 
since I am a regexp nub I can't seem to find the right query.  The 
basic query looks something like:

select name from resource where name regexp '[\b](searchText)[\b];
Does anyone know whether this is supported?  I've also tried writing 
my own boundary check with limited success.
The easiest way to find out the correct syntax is to look in the MySQL
manual. :-)
http://dev.mysql.com/doc/mysql/en/Regexp.html
--
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]

REGEXP and word boundary match

[Luke Majewski]

Hi All,
I have fields like:
"Washable Velour Doll"
in my database and I want to do a boundary match so that when people 
enter a search field like "Velour" I return all instances where the 
whole word exists.  In other words, 'lour' would not work.  I know that 
reg expressions have boundary matching, so \bVelour\b should match all 
whole words "Velour."

So I've tried making this into a query many different ways, and since I 
am a regexp nub I can't seem to find the right query.  The basic query 
looks something like:

select name from resource where name regexp '[\b](searchText)[\b];
Does anyone know whether this is supported?  I've also tried writing my 
own boundary check with limited success.

Any help would be greatly appreciated,
Luke
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Query problem

[Schalk]

Why is the following query retuning doctype’s different to what is asked
for?
SELECT * FROM documents WHERE jstate = 'California: State Court' AND doctype
= 'Verdict'
 
Any ideas? As far as I can see it should only return a document if it is a
Verdict and matches the state California: State Court.


Kind Regards
Schalk Neethling
Web Developer.Designer.Programmer.President
Volume4.Development.Multimedia.Branding
emotionalize.conceptualize.visualize.realize
Tel: +27125468436
Fax: +27125468436
email:[EMAIL PROTECTED]
web: www.volume4.co.za
 
This message contains information that is considered to be sensitive or
confidential and may not be forwarded or disclosed to any other party
without the permission of the sender. If you received this message in error,
please notify me immediately so that I can correct and delete the original
email. Thank you.




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

RE: Database Connection Questions

[David Blomstrom]

--- Peter Lovatt <[EMAIL PROTECTED]> wrote:
> hi
> 
> the
> 
> /home/lark/public_html/connection.php on line 23
> 
> just refers to the file within the context of the
> whole file system and is
> fine
> 
> the rest says that mysql cannot/does not execute the
> query so there is no
> result to use.
> 
> the @ suppresses error messages so it may be best to
> remove  it
> 
> try
> 
>   mysql_connect ("localhost" , "USERNAME" ,
> "PASSWORD");
>   or die(mysql_error());
> 
>   mysql_select_db ("DATABASE");
>   or die(mysql_error());
> 
> which may tell you more - I suspect a permissions
> problem if it works on a
> local server
> 
> If you are switching from a Windows machine to Linux
> remember to watch for
> case sensitivity problems.

OK, thanks. Also, I didn't realize that some hosts
won't let you connect to remote databases. I tried
another local connection, using your notes, and it
worked this time.

I thought I could make a life a little easier by
publishing my database to a couple sites, then linking
all my sites to them remotely, but it looks like I'm
going to have to put two dozen copies of it online!

Thanks.


> 
> > -Original Message-
> > From: David Blomstrom
> [mailto:[EMAIL PROTECTED]
> > Sent: 27 June 2004 20:49
> > To: [EMAIL PROTECTED]
> > Subject: Database Connection Questions
> >
> >
> > I'm previewing local pages with a database
> connection
> > that looks something like this:
> >
> > @mysql_connect ("localhost" , "USERNAME" ,
> > "PASSWORD");
> > @mysql_select_db ("DATABASE");
> >
> > I modified it for an online site and published it
> > online. But when I preview a page, I get error
> > messages like the following:
> >
> > Warning: mysql_fetch_array(): supplied argument is
> not
> > a valid MySQL result resource in
> > /home/lark/public_html/connection.php on line 23
> >
> > Warning: mysql_fetch_assoc(): supplied argument is
> not
> > a valid MySQL result resource in
> > /home/lark/public_html/connection.php on line 41
> >
> > Warning: mysql_fetch_assoc(): supplied argument is
> not
> > a valid MySQL result resource in
> > /home/lark/public_html/connection.php on line 45
> >
> > Do those URL's look odd - like they're OUTSIDE of
> my
> > website (/public_html/)?
> >
> > And I get this error at the bottom of the page:
> >
> > "Invalid query: No database selected"
> >
> > So I published my database to a different website
> and
> > tried to make a remote connection. I was told that
> all
> > I have to do is replace "localhost" with a URL, so
> my
> > new query looks like this:
> >
> > @mysql_connect ("www.othersite.org" , "USERNAME" ,
> > "PASSWORD");
> > @mysql_select_db ("DATABASE");
> >
> > But I get the same results. I'm not even sure
> where to
> > begin troubleshooting, because I'm not certain
> what
> > the root of the problem is.
> >
> > Any suggestions?
> >
> > Thanks.
> >
> >
> >
> > __
> > Do you Yahoo!?
> > Yahoo! Mail - 50x more storage than other
> providers!
> > http://promotions.yahoo.com/new_mail
> >
> > --
> > 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]
> 
> 




__
Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish.
http://promotions.yahoo.com/new_mail 

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

RE: Database Connection Questions

[Peter Lovatt]

hi

the

/home/lark/public_html/connection.php on line 23

just refers to the file within the context of the whole file system and is
fine

the rest says that mysql cannot/does not execute the query so there is no
result to use.

the @ suppresses error messages so it may be best to remove  it

try

  mysql_connect ("localhost" , "USERNAME" , "PASSWORD");
  or die(mysql_error());

  mysql_select_db ("DATABASE");
  or die(mysql_error());

which may tell you more - I suspect a permissions problem if it works on a
local server

If you are switching from a Windows machine to Linux remember to watch for
case sensitivity problems.


HTH

Peter




> -Original Message-
> From: David Blomstrom [mailto:[EMAIL PROTECTED]
> Sent: 27 June 2004 20:49
> To: [EMAIL PROTECTED]
> Subject: Database Connection Questions
>
>
> I'm previewing local pages with a database connection
> that looks something like this:
>
> @mysql_connect ("localhost" , "USERNAME" ,
> "PASSWORD");
> @mysql_select_db ("DATABASE");
>
> I modified it for an online site and published it
> online. But when I preview a page, I get error
> messages like the following:
>
> Warning: mysql_fetch_array(): supplied argument is not
> a valid MySQL result resource in
> /home/lark/public_html/connection.php on line 23
>
> Warning: mysql_fetch_assoc(): supplied argument is not
> a valid MySQL result resource in
> /home/lark/public_html/connection.php on line 41
>
> Warning: mysql_fetch_assoc(): supplied argument is not
> a valid MySQL result resource in
> /home/lark/public_html/connection.php on line 45
>
> Do those URL's look odd - like they're OUTSIDE of my
> website (/public_html/)?
>
> And I get this error at the bottom of the page:
>
> "Invalid query: No database selected"
>
> So I published my database to a different website and
> tried to make a remote connection. I was told that all
> I have to do is replace "localhost" with a URL, so my
> new query looks like this:
>
> @mysql_connect ("www.othersite.org" , "USERNAME" ,
> "PASSWORD");
> @mysql_select_db ("DATABASE");
>
> But I get the same results. I'm not even sure where to
> begin troubleshooting, because I'm not certain what
> the root of the problem is.
>
> Any suggestions?
>
> Thanks.
>
>
>
> __
> Do you Yahoo!?
> Yahoo! Mail - 50x more storage than other providers!
> http://promotions.yahoo.com/new_mail
>
> --
> 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]

Database Connection Questions

[David Blomstrom]

I'm previewing local pages with a database connection
that looks something like this:

@mysql_connect ("localhost" , "USERNAME" ,
"PASSWORD");
@mysql_select_db ("DATABASE");

I modified it for an online site and published it
online. But when I preview a page, I get error
messages like the following:

Warning: mysql_fetch_array(): supplied argument is not
a valid MySQL result resource in
/home/lark/public_html/connection.php on line 23

Warning: mysql_fetch_assoc(): supplied argument is not
a valid MySQL result resource in
/home/lark/public_html/connection.php on line 41

Warning: mysql_fetch_assoc(): supplied argument is not
a valid MySQL result resource in
/home/lark/public_html/connection.php on line 45

Do those URL's look odd - like they're OUTSIDE of my
website (/public_html/)?

And I get this error at the bottom of the page:

"Invalid query: No database selected"

So I published my database to a different website and
tried to make a remote connection. I was told that all
I have to do is replace "localhost" with a URL, so my
new query looks like this:

@mysql_connect ("www.othersite.org" , "USERNAME" ,
"PASSWORD");
@mysql_select_db ("DATABASE");

But I get the same results. I'm not even sure where to
begin troubleshooting, because I'm not certain what
the root of the problem is.

Any suggestions?

Thanks.



__
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail

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

Re: MySQL from Python Error

[Paul DuBois]

At 19:08 -0700 6/26/04, Paul Maine wrote:
I encountered the following error when trying to perform a SQL UPDATE to a
MySQL database table from Python.
I would apprciate any assistance. In the Python code I have tried integer
and decimal format specifiers in addition to the string specifier and
nothing worked.
If you are using the MySQLdb Python module for MySQL (I assume you are,
but you don't say), then you should use %s for placeholder markers.
That's the only supported format specifier for MySQLdb.

Traceback (most recent call last):
  File "e:\my_python_scripts\commercecraft.py", line 36, in ?
cursor.execute ("UPDATE product  SET price = '%s' WHERE competitorID=1
AND sku = '%s'",(myprice,mysku))
In addition, this query is incorrect.  Use just %s, not '%s'.  The
quotes here are incorrect.  When MySQLdb performs parameter substitution,
it'll add the quotes as necessary. (For example, if you want to bind a
NULL value, you definitely don't want quotes in the value.
Hmm, the code you show below looks oddly familiar, so I'll just conclude
with this: See page 105, last two paragraphs. :-)

  File "E:\Python22\Lib\site-packages\MySQLdb\cursors.py", line 95, in
execute
return self._execute(query, args)
  File "E:\Python22\Lib\site-packages\MySQLdb\cursors.py", line 114, in
_execute
self.errorhandler(self, exc, value)
  File "E:\Python22\Lib\site-packages\MySQLdb\connections.py", line 33, in
defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL
syntax.  Check the manual that cor
responds to your MySQL server version for the right syntax to use near
'139.80'' WHERE competitorID=1 AND sk
u = ''50300288''' at line 1")

Describe product;
Field Type  NullKey Default  Extra
    --  --  ---  --
productID int(11)   PRI (NULL)   auto_increment
sku   varchar(50)   YES (NULL)
description   varchar(60)   YES (NULL)
price decimal(7,2)  YES (NULL)
scrape_date   datetime  YES (NULL)
competitorID  int(11)   YES (NULL)

**
import test_iopus
import MySQLdb
import sys
import string
try:
conn = MySQLdb.connect (host = "localhost",
   user = "root",
   passwd = "xyz",
   db = "commerce")
except MySQLdb.Error, e:
print "Error %d: %s" % (e.args[0], e.args[1])
sys.exit (1)
cursor = conn.cursor()
cursor.execute ("SELECT sku FROM product WHERE competitorID=1")
while (1):
   row = cursor.fetchone ()
   if row == None:
   break
   mysku = row[0]
   print mysku
print "%d rows were returned" % cursor.rowcount
result = test_iopus.Scrape(mysku)
price = result.split(" ")
tmpprice =  str(price[0])
conversion = string.maketrans("$"," ")
myprice = tmpprice.translate(conversion)
print myprice
cursor.execute ("UPDATE product  SET price = '%s' WHERE competitorID=1 AND
sku = '%s'",(myprice,mysku))
cursor.close()
conn.close()

--
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: Merging 2 Fields

[Michael Stassen]

David Blomstrom wrote:
--- David Rodman <[EMAIL PROTECTED]> wrote:
You mean something like this?
mysql> create table nads(
   -> state varchar(50),
   -> url varchar(100));
Query OK, 0 rows affected (0.03 sec)

OK, you're creating a table named "nads," with fields
named "state" and "url," right?
Yes.  As an example.  You said you had a table with fields for state name 
and state URL, but you didn't provide the name of the table or the fields, 
so David Rodman made an example table to illustrate how his suggestion works.

mysql> insert into nads values('California',
'http://www.california.gov');
Query OK, 1 row affected (0.00 sec)
mysql> insert into nads values('Florida',
'http://www.florida.gov');
Query OK, 1 row affected (0.00 sec)
Here he puts two example rows into the example table.
I'm a little confused here. I'm slightly more familiar
with manipulating PHP than MySQL. It looks like your
suggestion is a permanent fix. It just occurred to me
that it would be nice if I had the option of
displaying Florida as plain text or as a link.
I'm not sure what you mean by permanent fix, but you are again confusing 
data with how it is presented.  You have a states table which has a column 
for the state name, a column for the state URL, and columns for other state 
data.  You display data from that table as you see fit by writing an 
appropriate SELECT statement.  If you want a list of states linked to their 
web pages, you

  SELECT CONCAT('', state,'') FROM states;
See, you sre pulling values from the url and state columns of the states 
table, and ***displaying*** them concatenated with some other text, so the 
result is the state name linked to the URL.

If you want a list of states with no links, then you don't request the url 
column in your SELECT, and you have no need of CONCAT:

  SELECT state FROM states;
But if I do this, Florida will always be linked,
right? Also, it looks like you have to do this
SELECT does not link columns in your DB.  It retrieves data from your DB. 
The output is determined by what you ask for.

operation for each row - California, Florida, etc. Is
there a simple command that merges two entire columns?
You are not merging columns.  You are **displaying** the values from two 
columns in a single string.  No change has been made to the underlying data.

Like every other SELECT statement, the two I gave above will print results 
for every row in table states.  If you want the same format results, but 
only for certain states, you must add a WHERE clause which restricts the 
results to what you want.

Print the state name linked to its URL, but only for California:
  SELECT CONCAT('', state,'')
  FROM states
  WHERE state = 'California';
Print the state name only (no link), but just for Alaska and Alabama:
  SELECT state FROM states WHERE state IN ('Alaska','Alabama');
Or is that what you're doing here?:

mysql> SELECT CONCAT('', state,
'') as link from nads;
+--+
| link |
+--+
| http://www.california.gov>California |
| http://www.florida.gov>Florida   |
+--+

So this is the "concat" method, another posted alluded
to. That's the term I remember seeing in another
thread. Thanks.
I would like to respectfully suggest that you modify your strategy.  You are 
working on a project that is fundamentally about storing and retrieving data 
from a database in mysql.  You've been asking questions since January, most 
of which boil down to "How does mysql work?", which is the one piece of the 
puzzle which, by your own admission, you have steadfastly avoided studying. 
  Considering that the success or failure of your project rests on getting 
mysql to do what you want, I think it is time you overcame your reluctance. 
 Find some web tutorials or get Paul DuBois' excellent book and start 
learning mysql with the mysql client program.  It is easy to have PHP 
perform a mysql query once you've figured out what the right query should 
be.  Given the nature of your questions, I think you'd be much further along 
in your project now if you had started with that, but it's never too late to 
start.

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

Re: MySQL from Python Error

[Michael Stassen]

I've not used Python, but it looks like the query has invalid syntax once it 
gets to mysql.  The last error line is the key:

1064, "You have an error in your SQL syntax.  Check the manual that 
corresponds to your MySQL server version for the right syntax to use near
'139.80'' WHERE competitorID=1 AND sku = ''50300288''' at line 1"

Look at all the quotes.  It appears to me that you and Python are both 
adding quotes to your values.  I think your actual query ends up as

  UPDATE product  SET price = ''139.80''
  WHERE competitorID=1 AND sku = ''50300288'';
Let me change the spacing for emphasis:
  UPDATE product  SET price = ''
  139.80''
  WHERE competitorID=1 AND sku = ''
  50300288'';
See?
I think cursor.execute is quoting your values as it inserts them.  Try it 
without quotes and see what happens.  You don't need quotes around the 
price, anyway.

As an alternative, I'd suggest loading the complete query into a single 
string first, then passing that complete query string to your function.  In 
this case, I expect cursor.execute would have nothing to quote, so you would 
need to explicitly quote strings in your query.  To aid debugging, you 
should add a line to print the query string before sending it, or perhaps 
only in the event of an error, so you can compare what's actually sent to 
what is supposed to be sent.

Michael
Paul Maine wrote:
I encountered the following error when trying to perform a SQL UPDATE to a
MySQL database table from Python.
I would apprciate any assistance. In the Python code I have tried integer
and decimal format specifiers in addition to the string specifier and
nothing worked.
Traceback (most recent call last):
  File "e:\my_python_scripts\commercecraft.py", line 36, in ?
cursor.execute ("UPDATE product  SET price = '%s' WHERE competitorID=1
AND sku = '%s'",(myprice,mysku))
  File "E:\Python22\Lib\site-packages\MySQLdb\cursors.py", line 95, in
execute
return self._execute(query, args)
  File "E:\Python22\Lib\site-packages\MySQLdb\cursors.py", line 114, in
_execute
self.errorhandler(self, exc, value)
  File "E:\Python22\Lib\site-packages\MySQLdb\connections.py", line 33, in
defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL
syntax.  Check the manual that cor
responds to your MySQL server version for the right syntax to use near
'139.80'' WHERE competitorID=1 AND sk
u = ''50300288''' at line 1")

Describe product;
Field Type  NullKey Default  Extra
    --  --  ---  --
productID int(11)   PRI (NULL)   auto_increment
sku   varchar(50)   YES (NULL)
description   varchar(60)   YES (NULL)
price decimal(7,2)  YES (NULL)
scrape_date   datetime  YES (NULL)
competitorID  int(11)   YES (NULL)

**
import test_iopus
import MySQLdb
import sys
import string
try:
conn = MySQLdb.connect (host = "localhost",
   user = "root",
   passwd = "xyz",
   db = "commerce")
except MySQLdb.Error, e:
print "Error %d: %s" % (e.args[0], e.args[1])
sys.exit (1)
cursor = conn.cursor()
cursor.execute ("SELECT sku FROM product WHERE competitorID=1")
while (1):
   row = cursor.fetchone ()
   if row == None:
   break
   mysku = row[0]
   print mysku
print "%d rows were returned" % cursor.rowcount
result = test_iopus.Scrape(mysku)
price = result.split(" ")
tmpprice =  str(price[0])
conversion = string.maketrans("$"," ")
myprice = tmpprice.translate(conversion)
print myprice
cursor.execute ("UPDATE product  SET price = '%s' WHERE competitorID=1 AND
sku = '%s'",(myprice,mysku))
cursor.close()
conn.close()


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

Flowing Text Into Multiple Columns

[David Blomstrom]

Suppose I want to display an entire field, but not in
one long column. Instead, I want to flow it evenly
into several columns.

For example, consider the following column:

Alabama
Alaska
Arizona
Arkansas
California
Colorado
Connecticut

I might want to split it into two columns:

Alabama California
Alaska  Colorado
Arizona Connecticut
Arkansas

Or three:

Alabama   Arkansas Connecticut
AlaskaCalifornia
Arizona   Colorado

I haven't yet learned of a way to do this with PHP, so
I wondered if there's some sort of trick you can use
with MySQL to flow text into multiple columns.

Thanks.






__
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail 

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

RE: Merging 2 Fields

[David Blomstrom]

--- David Rodman <[EMAIL PROTECTED]> wrote:
> You mean something like this?
> 
> mysql> create table nads(
> -> state varchar(50),
> -> url varchar(100));
> Query OK, 0 rows affected (0.03 sec)

OK, you're creating a table named "nads," with fields
named "state" and "url," right?

> mysql> insert into nads values('California',
> 'http://www.california.gov');
> Query OK, 1 row affected (0.00 sec)
> 
> mysql> insert into nads values('Florida',
> 'http://www.florida.gov');
> Query OK, 1 row affected (0.00 sec)

I'm a little confused here. I'm slightly more familiar
with manipulating PHP than MySQL. It looks like your
suggestion is a permanent fix. It just occurred to me
that it would be nice if I had the option of
displaying Florida as plain text or as a link.

But if I do this, Florida will always be linked,
right? Also, it looks like you have to do this
operation for each row - California, Florida, etc. Is
there a simple command that merges two entire columns?

Or is that what you're doing here?:

> mysql> SELECT CONCAT('', state,
> '') as link from nads;
> +--+
> | link |
> +--+
> | http://www.california.gov>California |
> | http://www.florida.gov>Florida   |
> +--+

So this is the "concat" method, another posted alluded
to. That's the term I remember seeing in another
thread. Thanks.




__
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!
http://promotions.yahoo.com/new_mail 

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

Re: insert into text field

[Eric Bergen]

desc is a reserved word. 
http://dev.mysql.com/doc/mysql/en/Reserved_words.html

In most cases to use desc as a column you will have to escape it with backticks 
like this `desc`.

-Eric

On Sat, 26 Jun 2004 16:19:37 +0530, Nitin <[EMAIL PROTECTED]> wrote:
> 
> Hi all,
> 
> There's a problem. When I try to inert into table with
> 
> insert into schedule (owner, desc, sch_for, status) values ('a', 'b', 'c', 'd')
> 
> I get error
> 
> You have an error in your SQL syntax.  Check the manual that corresponds to your 
> MySQL server version for the right syntax to use near 'desc, sch_for, status) values'
> 
> The desc field is of 'text' datatype. If that's the reason, what is the right syntax 
> to insert into text field?
> 
> Please help me
> 
> Thanks in advance
>

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

Re: DELETE doesn't delete?

[Michael Stassen]

Have you verified that the SQL statement sent by PHP is what you expect?  If 
not, I would suggest you load the query into a string, then print the string 
as part of your output in addition to passing it to mysql_query().  In my 
own coding, I have several times spotted errors this way in the printed 
query that I couldn't see in the code.  Something like:

  $query = "DELETE FROM sessions WHERE sessionID=$sessionID";
  echo "$query\n";
  $result = mysql_query($query);
If it looks right, copy the query and paste it into the mysql client.  At 
the least, you will determine whether it is the query or something PHP does 
with the query that is at fault.

If the query turns out to be correct, we'll have to look elsewhere.  In that 
case, you should include your platform/OS and versions of mysql and PHP in 
your next post.  Also, tell us whether sessions is a MyISAM or InnoDB table.

One more thought:  Is there any possibility that your script is creating the 
record on one server but updating/deleting it on another?  Perhaps 
production and development copies of mysql?

Michael
Margaret MacDonald wrote:
It's possible that this is more a php question than a mysql question,
but since the two are so nearly joined at the hip, I'm hoping someone
can advise anyhow.
I'm developing a php job that uses a table 'sessions' to maintain a
logical session across pages and physical sessions.  It's a simple
table, with only 3 fields:  the session id, a timestamp recording the
last write, and a text field for the data.  

To purge the session record, the user needs to explicitly log out.
When they click the logout button, it calls a logout routine that
calls mysql_query ( DELETE FROM sessions WHERE sessionID= the session
identifier.  ) .
But, when I test that, the DELETE doesn't actually work the way I
would expect it to do.  Inspecting the table with the mysql
command-line console, I can still see the record itself with the
session id and the timestamp seemingly unchanged, though the data
field appears to be zeroed out. 

Php seems to think the record's been deleted, though, because an
attempted read returns an empty data set. 

If I use the same DELETE FROM ... syntax from the command-line
console, however, the whole record goes away immediately as I would
expect.
Does anyone understand  what's going on here?
Thanks!
Margaret  


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

Re: Question

[Robert J Taylor]

On Sat, 26 Jun 2004 22:19:02 +0200, Schalk <[EMAIL PROTECTED]> wrote:
Why is the following query retuning doctype's different to what is asked
for?
SELECT * FROM documents WHERE jstate = 'California: State Court' AND  
doctype
= 'Verdict'

Any ideas?
What is being returned? Not knowing what it is that is being returned, how  
the documents table is defined or what the data types for the fields are  
it is hard to guess, except to note:

'Verdict' will also match 'verdict' and 'vErDicT' using default settings,  
see:
http://dev.mysql.com/doc/mysql/en/Case_sensitivity.html


Kind Regards
Schalk Neethling

With a bit more info perhaps I could help more.
Robert J Taylor
[EMAIL PROTECTED]

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

Re: Question

[Michael Stassen]

It's hard to say without more information.  We could speculate that you did 
something wrong or there's a bug in your copy of mysql, but it would be just 
that, speculation.

What version of mysql are you using?  On what platform/OS?  Is that the 
exact query, or a simplified representative?  What results do you get?  IT 
might be helpful if you included the results of SHOW CREATE TABLE documents 
and the result of EXPLAIN on your query.

Michael
Schalk wrote:
Why is the following query returning doctype's different to what is asked
for?
SELECT * FROM documents WHERE jstate = 'California: State Court' AND doctype
= 'Verdict'
Any ideas?
Kind Regards
Schalk Neethling

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

Re: Merging 2 Fields

[Michael Stassen]

Don't forgot the quotes around the URL:
SELECT CONCAT('', state, '') AS link FROM nads;
Michael
David Rodman wrote:
You mean something like this?
mysql> create table nads(
-> state varchar(50),
-> url varchar(100));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into nads values('California', 'http://www.california.gov');
Query OK, 1 row affected (0.00 sec)
mysql> insert into nads values('Florida', 'http://www.florida.gov');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT CONCAT('', state, '') as link from nads;
+--+
| link |
+--+
| http://www.california.gov>California |
| http://www.florida.gov>Florida   |
+--+
2 rows in set (0.05 sec)

-Original Message-
From: David Blomstrom [mailto:[EMAIL PROTECTED]
Sent: Saturday, June 26, 2004 8:07 PM
To: [EMAIL PROTECTED]
Subject: Merging 2 Fields
I think I recall a thread where someone mentioned
merging two fields. If that can be done, I'd like to
know how.
For example, suppose I have a field filled with place
names, next to a field filled with links:
California | 
Florida | 
I'd like to merge the columns, so the two columns are
displayed as one:
California
I'd have to add the closing  tag somehow, but I
think I know how to do that.
So how do you merge fields?
Thanks.

__
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail
--
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]