MYSQL - PHP

[Angelos]

Hello,
First of all sorry if this is not the correct newsgroup for this question,
but I am using PHP with MYSQL and someone here could have an answer and the
experience.

I have the a table (content) with some content and every time I edit it I
store all the table contents in an other table (content_bak) when I want to
restore the contents
How am i going to do that ? I am trying to do :

REPLACE INTO content SELECT bak_content_id, bak_content_tit le FROM
content_bak WHERE backup_id = '".$_GET['buckup_id']."'";

TABLE CONTENT
content_id int PK
content_title varchar

TABLE CONTENT_BAK
backup_id int PK
bak_content_id int
bak_content_tit le varchar

What I want to do is to REPLACE the TABLE CONTENT content_id = $x with
the TABLE CONTENT backup_id = $y AND bak_content_id =$x

Any ideas ?

[Colin McKinnon]

Re: MYSQL - PHP

Angelos wrote:
[color=blue]
> First of all sorry if this is not the correct newsgroup for this question,
> but I am using PHP with MYSQL and someone here could have an answer and
> the experience.
>[/color]

This is the wrong newsgroup.
[color=blue]
> REPLACE INTO content SELECT bak_content_id, bak_content_tit le FROM
> content_bak WHERE backup_id = '".$_GET['buckup_id']."'";
>[/color]
<snip>[color=blue]
> What I want to do is to REPLACE the TABLE CONTENT content_id = $x with
> the TABLE CONTENT backup_id = $y AND bak_content_id =$x
>[/color]

REPLACE INTO content (id, title)
SELECT bak_content_id, bak_content_tit le
FROM content_bak
WHERE backup_id={$_GE T['backup_id']}

You might find a good book on SQL & MySQL in particular of benefit.

C.

[Dave]

Re: MYSQL - PHP

Colin McKinnon (colin.deleteth is@andthis.mms3 .com) decided we needed to
hear...[color=blue]
> Angelos wrote:
>[color=green]
> > First of all sorry if this is not the correct newsgroup for this question,
> > but I am using PHP with MYSQL and someone here could have an answer and
> > the experience.
> >[/color]
>
> This is the wrong newsgroup.
>[color=green]
> > REPLACE INTO content SELECT bak_content_id, bak_content_tit le FROM
> > content_bak WHERE backup_id = '".$_GET['buckup_id']."'";
> >[/color]
> <snip>[color=green]
> > What I want to do is to REPLACE the TABLE CONTENT content_id = $x with
> > the TABLE CONTENT backup_id = $y AND bak_content_id =$x
> >[/color]
>
> REPLACE INTO content (id, title)
> SELECT bak_content_id, bak_content_tit le
> FROM content_bak
> WHERE backup_id={$_GE T['backup_id']}
>
> You might find a good book on SQL & MySQL in particular of benefit.
>
> C.[/color]
$_GET['backup_id'] has to be validated first though right? I think
thats what the OP is getting at.
e.g. If table CONTENT has a row - (1, 'fred') and table CONTENT_BAK
has a row - (1, 2, 'bill') and the intention is to restore the
CONTENT row for content_id 1, then the wrong row will be
replaced (or inserted) if $_GET['backup_id'] = 1
That can't be done with a replace because you can't refer to the
original row. The validation will have to be done with a select
first...
select bak_content_tit le from content_bak
where backup_id = <backup id>
and bak_content_id = <required content id>
then, an update...
update content set content_title = <retrieved title>
where content_id = <required content id>
If the OP has MySQL 4.0.4 or later, a multi-table update could be
used instead...
update content, content_bak
set content.content _title = content_bak.bak _content_title
where content_bak.bac kup_id = <backup id>
and content_bak.bak _content_id = <required content_id>
--
Dave <dave@REMOVEbun dook.com>
(Remove REMOVE for email address)

[Dave]

Re: MYSQL - PHP

Dave (dave@REMOVEbun dook.com) decided we needed to hear...[color=blue]
> Colin McKinnon (colin.deleteth is@andthis.mms3 .com) decided we needed to
> hear...[color=green]
> > Angelos wrote:
> >[color=darkred]
> > > First of all sorry if this is not the correct newsgroup for this question,
> > > but I am using PHP with MYSQL and someone here could have an answer and
> > > the experience.
> > >[/color]
> >
> > This is the wrong newsgroup.
> >[color=darkred]
> > > REPLACE INTO content SELECT bak_content_id, bak_content_tit le FROM
> > > content_bak WHERE backup_id = '".$_GET['buckup_id']."'";
> > >[/color]
> > <snip>[color=darkred]
> > > What I want to do is to REPLACE the TABLE CONTENT content_id = $x with
> > > the TABLE CONTENT backup_id = $y AND bak_content_id =$x
> > >[/color]
> >
> > REPLACE INTO content (id, title)
> > SELECT bak_content_id, bak_content_tit le
> > FROM content_bak
> > WHERE backup_id={$_GE T['backup_id']}
> >
> > You might find a good book on SQL & MySQL in particular of benefit.
> >
> > C.[/color]
> $_GET['backup_id'] has to be validated first though right? I think
> thats what the OP is getting at.
> e.g. If table CONTENT has a row - (1, 'fred') and table CONTENT_BAK
> has a row - (1, 2, 'bill') and the intention is to restore the
> CONTENT row for content_id 1, then the wrong row will be
> replaced (or inserted) if $_GET['backup_id'] = 1[/color]
<snip>
Actually to do it as I described you need to already know the
content_id, so instead of my original long-winded solution, you
may as well just add...
AND bak_content_id = '$content_id'
to the end of Colin's statement and do it that way ;)
--
Dave <dave@REMOVEbun dook.com>
(Remove REMOVE for email address)

[Chris Hope]

Re: MYSQL - PHP

Angelos wrote:
[color=blue]
> First of all sorry if this is not the correct newsgroup for this
> question, but I am using PHP with MYSQL and someone here could have an
> answer and the experience.
>
> I have the a table (content) with some content and every time I edit
> it I store all the table contents in an other table (content_bak) when
> I want to restore the contents
> How am i going to do that ? I am trying to do :
>
> REPLACE INTO content SELECT bak_content_id, bak_content_tit le FROM
> content_bak WHERE backup_id = '".$_GET['buckup_id']."'";[/color]

Beware of SQL injection. You cannot be 100% certain that
$_GET['buckup_id'] is an integer value as someone could modify the
value in the browser's address bar.

Of course, if this is a private script only run by you in a protected
environment then it's not so important but it's something to bear in
mind for public scripts.

[rest snipped]

--
Chris Hope | www.electrictoolbox.com | www.linuxcdmall.com