adding a / to code numbers...

My database has a variable $codename and the fields are populated with G115
up to say G1515.... the first 2 or 3 (in the case of a string of 4)digits
represent a Gallery and the last 2 digits represent the painting number. I
want to add a / between the gallery and painting number so for example G215
will become G2/15 and G1316 will become G13/16.

What do I have to input into the phpmysqladmin to do this to apply to all
the entries?

thanks

Re: adding a / to code numbers...

> My database has a variable $codename and the fields are populated with G115
> up to say G1515.... the first 2 or 3 (in the case of a string of 4)digits
> represent a Gallery and the last 2 digits represent the painting number. I
> want to add a / between the gallery and painting number so for example G215
> will become G2/15 and G1316 will become G13/16.

UPDATE mytable
SET codename =
CONCAT(
LEFT( codename, LENGTH( codename ) - 2 ),
CONCAT( '/', RIGHT( codename, 2 ) )
)

---
Steve

Re: adding a / to code numbers...

"Steve" wrote in message
news:1133955388.084012.84570@g47g2000cwa.googlegroups.com...
>
> > My database has a variable $codename and the fields are populated with
G115
> > up to say G1515.... the first 2 or 3 (in the case of a string of
4)digits
> > represent a Gallery and the last 2 digits represent the painting number.
I
> > want to add a / between the gallery and painting number so for example
G215
> > will become G2/15 and G1316 will become G13/16.
>
> UPDATE mytable
> SET codename =
> CONCAT(
> LEFT( codename, LENGTH( codename ) - 2 ),
> CONCAT( '/', RIGHT( codename, 2 ) )
> )
> Steve
What am I doing wrong here:

SELECT *
FROM `photos`
UPDATE photos SET codenumber = CONCAT( LEFT( codename, LENGTH(
codename ) -2 ) , CONCAT( '/', RIGHT( codename, 2 ) ) ) LIMIT 0 ,
30

and I get:

#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
'UPDATE photos SET codenumber = CONCAT( LEFT( codename , LENG


thanks

Re: adding a / to code numbers...

"Steve" wrote in message
news:1133955388.084012.84570@g47g2000cwa.googlegroups.com...
>
> > My database has a variable $codename and the fields are populated with
G115
> > up to say G1515.... the first 2 or 3 (in the case of a string of
4)digits
> > represent a Gallery and the last 2 digits represent the painting number.
I
> > want to add a / between the gallery and painting number so for example
G215
> > will become G2/15 and G1316 will become G13/16.
>
> UPDATE mytable
> SET codename =
> CONCAT(
> LEFT( codename, LENGTH( codename ) - 2 ),
> CONCAT( '/', RIGHT( codename, 2 ) )
> )
>
> ---
> Steve
I worked out I got the codename and number mixed but even this

SELECT *
FROM `photos`
UPDATE 'photos' SET codenumber = CONCAT( LEFT( codenumber, LENGTH(
codenumber ) -2 ) , CONCAT( '/', RIGHT( codenumber, 2 ) ) ) LIMIT 0 ,
30

gave me the same reply?

Re: adding a / to code numbers...

> What am I doing wrong here:
>
> SELECT *
> FROM `photos`
> UPDATE photos SET codenumber = CONCAT( LEFT( codename, LENGTH(
> codename ) -2 ) , CONCAT( '/', RIGHT( codename, 2 ) ) ) LIMIT 0 ,
> 30
>
> and I get:
>
> #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
> 'UPDATE photos SET codenumber = CONCAT( LEFT( codename , LENG


You can't use LIMIT clause in UPDATE statements. It's for limiting
data you retrieve from DB with SELECT statements.

Hilarion

Re: adding a / to code numbers...

"Hilarion" wrote in message
news:dn9dkq$337$1@news.onet.pl...
> > What am I doing wrong here:
> >
> > SELECT *
> > FROM `photos`
> > UPDATE photos SET codenumber = CONCAT( LEFT( codename, LENGTH(
> > codename ) -2 ) , CONCAT( '/', RIGHT( codename, 2 ) ) ) LIMIT 0 ,
> > 30
> >
> > and I get:
> >
> > #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
> > 'UPDATE photos SET codenumber = CONCAT( LEFT( codename , LENG
>
>
> You can't use LIMIT clause in UPDATE statements. It's for limiting
> data you retrieve from DB with SELECT statements.
>
> Hilarion

For some reason phpmyadmin seems to be adding that onto the end of the
command, why I have no idea or how to stop it doing so.

Re: adding a / to code numbers...

In message <43989bf6@news.rivernet.com.au>
"plato" wrote:

>
> "Hilarion" wrote in message
> news:dn9dkq$337$1@news.onet.pl...
> > > What am I doing wrong here:
> > >
> > > SELECT *
> > > FROM `photos`
> > > UPDATE photos SET codenumber = CONCAT( LEFT( codename, LENGTH(
> > > codename ) -2 ) , CONCAT( '/', RIGHT( codename, 2 ) ) ) LIMIT 0 ,
> > > 30
> > >
> > > and I get:
> > >
> > > #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
> > > 'UPDATE photos SET codenumber = CONCAT( LEFT( codename , LENG
> >
> > You can't use LIMIT clause in UPDATE statements. It's for limiting
> > data you retrieve from DB with SELECT statements.
>
> For some reason phpmyadmin seems to be adding that onto the end of the
> command, why I have no idea or how to stop it doing so.

Why do you start the query with SELECT when what you are trying to do is
just an UPDATE? phpMyAdmin will always do a LIMIT on a SELECT.

Anthony

Re: adding a / to code numbers...

"Anthony Frost" wrote in message
news:cfef38d64d%Vulch@kerrier.vulch.org...
> In message <43989bf6@news.rivernet.com.au>
> "plato" wrote:
>
> >
> > "Hilarion" wrote in message
> > news:dn9dkq$337$1@news.onet.pl...
> > > > What am I doing wrong here:
> > > >
> > > > SELECT *
> > > > FROM `photos`
> > > > UPDATE photos SET codenumber = CONCAT( LEFT( codename, LENGTH(
> > > > codename ) -2 ) , CONCAT( '/', RIGHT( codename, 2 ) ) ) LIMIT 0 ,
> > > > 30
> > > >
> > > > and I get:
> > > >
> > > > #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
> > > > 'UPDATE photos SET codenumber = CONCAT( LEFT( codename , LENG
> > >
> > > You can't use LIMIT clause in UPDATE statements. It's for limiting
> > > data you retrieve from DB with SELECT statements.
> >
> > For some reason phpmyadmin seems to be adding that onto the end of the
> > command, why I have no idea or how to stop it doing so.
>
> Why do you start the query with SELECT when what you are trying to do is
> just an UPDATE? phpMyAdmin will always do a LIMIT on a SELECT.
>
> Anthony
Thanks Anthony - worked well. cheers