[PHP] Am I asking too much?

$sql1 works, but $sql2 doesn't. Am I asking too much? :=)
$sql2 echoes ok. If I copy it and run it in phpmyadmin, it works, but this way as php code, it flunks out when I add:

DELETE FROM '.$db.'.'.$table.' WHERE id='.$id.' LIMIT 1;

Ideas?
John

------------ snip -----------------
$sql1 = 'insert into '.$db2.'.'.$table2.'
(RNum,YR,AU,ST,SD,SC,BT,BD,BC,AT,AD,AC,SR,PL,PR,JR ,VNum,INum,DT,PG,LG,SF,OL,KW,AUS,GEO,AN,RB,CO,RR)
select
RNum,YR,AU,ST,SD,SC,BT,BD,BC,AT,AD,AC,SR,PL,PR,JR, VNum,INum,DT,PG,LG,SF,OL,KW,AUS,GEO,AN,RB,CO,RR
FROM '.$db.'.'.$table.'
WHERE id='.$id.';';

mysql_query($sql1);

------------ snip -----------------
$sql2 = 'insert into '.$db2.'.'.$table2.'
(RNum,YR,AU,ST,SD,SC,BT,BD,BC,AT,AD,AC,SR,PL,PR,JR ,VNum,INum,DT,PG,LG,SF,OL,KW,AUS,GEO,AN,RB,CO,RR)
select
RNum,YR,AU,ST,SD,SC,BT,BD,BC,AT,AD,AC,SR,PL,PR,JR, VNum,INum,DT,PG,LG,SF,OL,KW,AUS,GEO,AN,RB,CO,RR
FROM '.$db.'.'.$table.'
WHERE id='.$id.';
DELETE FROM '.$db.'.'.$table.' WHERE id='.$id.' LIMIT 1;';

mysql_query($sql2);

For security reasons mysql_query does not support ; to separate queries.
phpmyadmin splits multiple query strings up (PMA_splitSqlFile())

John Taylor-Johnston wrote:
> $sql1 works, but $sql2 doesn't. Am I asking too much? :=)
> $sql2 echoes ok. If I copy it and run it in phpmyadmin, it works, but this way as php code, it flunks out when I add:
>
> DELETE FROM '.$db.'.'.$table.' WHERE id='.$id.' LIMIT 1;
>
> Ideas?
> John
>
> ------------ snip -----------------
> $sql1 = 'insert into '.$db2.'.'.$table2.'
> (RNum,YR,AU,ST,SD,SC,BT,BD,BC,AT,AD,AC,SR,PL,PR,JR ,VNum,INum,DT,PG,LG,SF,OL,KW,AUS,GEO,AN,RB,CO,RR)
> select
> RNum,YR,AU,ST,SD,SC,BT,BD,BC,AT,AD,AC,SR,PL,PR,JR, VNum,INum,DT,PG,LG,SF,OL,KW,AUS,GEO,AN,RB,CO,RR
> FROM '.$db.'.'.$table.'
> WHERE id='.$id.';';
>
> mysql_query($sql1);
>
> ------------ snip -----------------
> $sql2 = 'insert into '.$db2.'.'.$table2.'
> (RNum,YR,AU,ST,SD,SC,BT,BD,BC,AT,AD,AC,SR,PL,PR,JR ,VNum,INum,DT,PG,LG,SF,OL,KW,AUS,GEO,AN,RB,CO,RR)
> select
> RNum,YR,AU,ST,SD,SC,BT,BD,BC,AT,AD,AC,SR,PL,PR,JR, VNum,INum,DT,PG,LG,SF,OL,KW,AUS,GEO,AN,RB,CO,RR
> FROM '.$db.'.'.$table.'
> WHERE id='.$id.';
> DELETE FROM '.$db.'.'.$table.' WHERE id='.$id.' LIMIT 1;';
>
> mysql_query($sql2);
>

So I should break it up my two queries?

$sql = 'insert into '.$db2.'.'.$table2.'(KW,AUS,GEO,AN,RB,CO,RR)
select KW,AUS,GEO,AN,RB,CO,RR FROM '.$db.'.'.$table.'
WHERE id='.$id.';';

mysql_query($sql);

$sql = 'DELETE FROM '.$db.'.'.$table.' WHERE id='.$id.' LIMIT 1;';
mysql_query($sql);

Any suggestions how I could prompt in between to ask yes or no? PhpMyAdmin does it with a javascript alert(). An easy answer? Maybe I'm being lazy?

John

> For security reasons mysql_query does not support ; to separate queries.
> phpmyadmin splits multiple query strings up (PMA_splitSqlFile())
>
> John Taylor-Johnston wrote:
> > $sql1 works, but $sql2 doesn't. Am I asking too much? :=)
> > $sql2 echoes ok. If I copy it and run it in phpmyadmin, it works, but this way as php code, it flunks out when I add:
> >
> > DELETE FROM '.$db.'.'.$table.' WHERE id='.$id.' LIMIT 1;
> >
> > Ideas?
> > John
> >
> > ------------ snip -----------------
> > $sql1 = 'insert into '.$db2.'.'.$table2.'
> > (RNum,YR,AU,ST,SD,SC,BT,BD,BC,AT,AD,AC,SR,PL,PR,JR ,VNum,INum,DT,PG,LG,SF,OL,KW,AUS,GEO,AN,RB,CO,RR)
> > select
> > RNum,YR,AU,ST,SD,SC,BT,BD,BC,AT,AD,AC,SR,PL,PR,JR, VNum,INum,DT,PG,LG,SF,OL,KW,AUS,GEO,AN,RB,CO,RR
> > FROM '.$db.'.'.$table.'
> > WHERE id='.$id.';';
> >
> > mysql_query($sql1);
> >
> > ------------ snip -----------------
> > $sql2 = 'insert into '.$db2.'.'.$table2.'
> > (RNum,YR,AU,ST,SD,SC,BT,BD,BC,AT,AD,AC,SR,PL,PR,JR ,VNum,INum,DT,PG,LG,SF,OL,KW,AUS,GEO,AN,RB,CO,RR)
> > select
> > RNum,YR,AU,ST,SD,SC,BT,BD,BC,AT,AD,AC,SR,PL,PR,JR, VNum,INum,DT,PG,LG,SF,OL,KW,AUS,GEO,AN,RB,CO,RR
> > FROM '.$db.'.'.$table.'
> > WHERE id='.$id.';
> > DELETE FROM '.$db.'.'.$table.' WHERE id='.$id.' LIMIT 1;';
> >
> > mysql_query($sql2);

Any ideas? I want to avoid having two "mysql_query($sql)". I'm basically looking for better functionality and wanting to learn how to clean up my code.

John Taylor-Johnston wrote:

> So I should break it up my two queries?
>
> $sql = 'insert into '.$db2.'.'.$table2.'(KW,AUS,GEO,AN,RB,CO,RR)
> select KW,AUS,GEO,AN,RB,CO,RR FROM '.$db.'.'.$table.'
> WHERE id='.$id.';';
>
> mysql_query($sql);
>
> $sql = 'DELETE FROM '.$db.'.'.$table.' WHERE id='.$id.' LIMIT 1;';
> mysql_query($sql);
>
> Any suggestions how I could prompt in between to ask yes or no? PhpMyAdmin does it with a javascript alert(). An easy answer? Maybe I'm being lazy?
>
> > For security reasons mysql_query does not support ; to separate queries.
> > phpmyadmin splits multiple query strings up (PMA_splitSqlFile())

* Thus wrote John Taylor-Johnston (taylorjo@collegesherbrooke.qc.ca):
> Any ideas? I want to avoid having two "mysql_query($sql)". I'm basically looking for better functionality and wanting to learn how to clean up my code.
>

there are a couple options, the insert has a extra option, 'ON
DUPLICATE KEY':
http://www.mysql.com/doc/en/INSERT.html

Or use the REPLACE syntax:
http://www.mysql.com/doc/en/REPLACE.html

Be forewarned about the REPLACE, that there may be side effects
because it not only replaces on the primary key value but any
UNIQUE index.

For example (ingore syntax errors),

create table (
id Primary Key
name UNIQUE
)

Data:
1, 'Name1'
2, 'Name2'

replace into table (id, name) values (2, 'Name1');

I havn't tested this situation, but to me that, seems like an
awkward position to be in.

Curt.


Curt
--
"My PHP key is worn out"

PHP List stats since 1997:
http://zirzow.dyndns.org/html/mlists/

What about some javascript to alert() and decide if the form executes like phpmyadmin does:

http://ccl.flsh.usherbrooke.ca/example.jpg

This would save me time coding PHP.
How can I accomplish this? What does the onsubmit look like?

John

> * Thus wrote John Taylor-Johnston (taylorjo@collegesherbrooke.qc.ca):
> > Any ideas? I want to avoid having two "mysql_query($sql)". I'm basically looking for better functionality and wanting to learn how to clean up my code.
> there are a couple options, the insert has a extra option, 'ON
> DUPLICATE KEY':
> http://www.mysql.com/doc/en/INSERT.html
> Or use the REPLACE syntax:
> http://www.mysql.com/doc/en/REPLACE.html

* Thus wrote Curt Zirzow (php-general@zirzow.dyndns.org):
> * Thus wrote John Taylor-Johnston (taylorjo@collegesherbrooke.qc.ca):
> > Any ideas? I want to avoid having two "mysql_query($sql)". I'm basically looking for better functionality and wanting to learn how to clean up my code.
> >

Oh, and IMO, there isn't anything unclean about having two sql
query() statements. It keeps the code more readable as to
what you're trying to doing.

Curt
--
"My PHP key is worn out"

PHP List stats since 1997:
http://zirzow.dyndns.org/html/mlists/

Curt

> Oh, and IMO, there isn't anything unclean about having two sql
> query() statements. It keeps the code more readable as to
> what you're trying to doing.

I agree. I also think I should just try to emulate what phpmyadmin does. KISS principle.
Thanks!!!
John

Again, check phpmyadmin html source code. If your query begins with
anything potentionaly destructive (DELETE, ALTER, DROP ...) it displays
a confirm (not alert).

John Taylor-Johnston wrote:

> What about some javascript to alert() and decide if the form executes like phpmyadmin does:
>
> http://ccl.flsh.usherbrooke.ca/example.jpg
>
> This would save me time coding PHP.
> How can I accomplish this? What does the onsubmit look like?
>
> John
>
>
>>* Thus wrote John Taylor-Johnston (taylorjo@collegesherbrooke.qc.ca):
>>
>>>Any ideas? I want to avoid having two "mysql_query($sql)". I'm basically looking for better functionality and wanting to learn how to clean up my code.
>>
>>there are a couple options, the insert has a extra option, 'ON
>>DUPLICATE KEY':
>> http://www.mysql.com/doc/en/INSERT.html
>>Or use the REPLACE syntax:
>> http://www.mysql.com/doc/en/REPLACE.html
>
>