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

Angelos wrote:

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.

This is the wrong newsgroup.
REPLACE INTO content SELECT bak_content_id, bak_content_title FROM
content_bak WHERE backup_id = '".$_GET['buckup_id']."'";
<snip> 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

REPLACE INTO content (id, title)
SELECT bak_content_id, bak_content_title
FROM content_bak
WHERE backup_id={$_GET['backup_id']}

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

C.

[Dave]

Colin McKinnon (co**************@andthis.mms3.com) decided we needed to
hear...

Angelos wrote:

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.

This is the wrong newsgroup.

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

<snip>

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

REPLACE INTO content (id, title)
SELECT bak_content_id, bak_content_title
FROM content_bak
WHERE backup_id={$_GET['backup_id']}

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

C.

$_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_title 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.backup_id = <backup id>
and content_bak.bak_content_id = <required content_id>
--
Dave <da**@REMOVEbundook.com>
(Remove REMOVE for email address)

[Dave]

Dave (da**@REMOVEbundook.com) decided we needed to hear...

Colin McKinnon (co**************@andthis.mms3.com) decided we needed to
hear...

Angelos wrote:

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.

This is the wrong newsgroup.

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

<snip>

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

REPLACE INTO content (id, title)
SELECT bak_content_id, bak_content_title
FROM content_bak
WHERE backup_id={$_GET['backup_id']}

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

C.

$_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

<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 <da**@REMOVEbundook.com>
(Remove REMOVE for email address)

[Chris Hope]

Angelos wrote:

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_title FROM
content_bak WHERE backup_id = '".$_GET['buckup_id']."'";

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