Determining whether to INSERT or UPDATE

I have a MySQL table of pages each with a 'title' and 'page' fields.

How do I do either an UPDATE if the pages 'title' exists or an INSERT if it
does not ?

The code I have written does not seem to do the job correctly.

$result = mysql_query( "SELECT page FROM pages WHERE title = '" . $title
.. "';");

$row = mysql_fetch_array( $result, MYSQL_ASSOC);

if ( isset( $row["title"]) && $row[title] == $title)
{
$result = mysql_query( "UPDATE pages SET page = '". $page . "' WHERE
title = '" .$title . "';");
if ($result)
mysql_query( "COMMIT;");
}
else
{
$result = mysql_query( "INSERT INTO pages SET title ='" . $title .
"', page = '" . $page . "';");
if ($result)
mysql_query( "COMMIT;");
};

This code is buggy and doing an insert rather than an update. How would you
approach this task ?

Many thanks in advance,

Aaron

Re: Determining whether to INSERT or UPDATE

Aaron Gray wrote:
> I have a MySQL table of pages each with a 'title' and 'page' fields.
>
> How do I do either an UPDATE if the pages 'title' exists or an INSERT if it
> does not ?
>
> The code I have written does not seem to do the job correctly.
>
> $result = mysql_query( "SELECT page FROM pages WHERE title = '" . $title
> . "';");
>
> $row = mysql_fetch_array( $result, MYSQL_ASSOC);
>
> if ( isset( $row["title"]) && $row[title] == $title)
> {
> $result = mysql_query( "UPDATE pages SET page = '". $page . "' WHERE
> title = '" .$title . "';");
> if ($result)
> mysql_query( "COMMIT;");
> }
> else
> {
> $result = mysql_query( "INSERT INTO pages SET title ='" . $title .
> "', page = '" . $page . "';");
> if ($result)
> mysql_query( "COMMIT;");
> };
>
> This code is buggy and doing an insert rather than an update. How would you
> approach this task ?
>
> Many thanks in advance,
>
> Aaron
>
>
>
>

Try comp.databases.mysql. This is a PHP newsgroup.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Re: Determining whether to INSERT or UPDATE

"Jerry Stuckle" wrote in message
news:y_WdnRKnboihf-fanZ2dnUVZ_sPinZ2d@comcast.com...
> Aaron Gray wrote:
>> I have a MySQL table of pages each with a 'title' and 'page' fields.
>>
>> How do I do either an UPDATE if the pages 'title' exists or an INSERT if
>> it
>> does not ?
>>
>> The code I have written does not seem to do the job correctly.
>>
>> $result = mysql_query( "SELECT page FROM pages WHERE title = '" .
>> $title
>> . "';");
>>
>> $row = mysql_fetch_array( $result, MYSQL_ASSOC);
>>
>> if ( isset( $row["title"]) && $row[title] == $title)
>> {
>> $result = mysql_query( "UPDATE pages SET page = '". $page . "'
>> WHERE
>> title = '" .$title . "';");
>> if ($result)
>> mysql_query( "COMMIT;");
>> }
>> else
>> {
>> $result = mysql_query( "INSERT INTO pages SET title ='" . $title
>> .
>> "', page = '" . $page . "';");
>> if ($result)
>> mysql_query( "COMMIT;");
>> };
>>
>> This code is buggy and doing an insert rather than an update. How would
>> you approach this task ?
>>
>> Many thanks in advance,
>>
>> Aaron
>>
>>
>>
>>
>
> Try comp.databases.mysql. This is a PHP newsgroup.

The problems I am having seem to be the PHP side of things not the SQL side.

Aaron

Re: Determining whether to INSERT or UPDATE

On Wed, 02 Jan 2008 01:50:20 +0100, Aaron Gray =

wrote:

> I have a MySQL table of pages each with a 'title' and 'page' fields.
>
> How do I do either an UPDATE if the pages 'title' exists or an INSERT =
if =

> it
> does not ?
>
> The code I have written does not seem to do the job correctly.
>
> $result =3D mysql_query( "SELECT page FROM pages WHERE title =3D '=
" . =

> $title
> . "';");
>
> $row =3D mysql_fetch_array( $result, MYSQL_ASSOC);
>
> if ( isset( $row["title"]) && $row[title] == $title)
> {
> $result =3D mysql_query( "UPDATE pages SET page =3D '". $page =
.. "' =

> WHERE
> title =3D '" .$title . "';");
> if ($result)
> mysql_query( "COMMIT;");
> }
> else
> {
> $result =3D mysql_query( "INSERT INTO pages SET title =3D'" . =
$title =

> .
> "', page =3D '" . $page . "';");
> if ($result)
> mysql_query( "COMMIT;");
> };
>
> This code is buggy and doing an insert rather than an update. How woul=
d =

> you
> approach this task ?

It could be done with one easy query, look into ON DUPLICATE KEY UPDATE =
=

syntax, ask in comp.databases.mysql


-- =

Rik Wasmus

Re: Determining whether to INSERT or UPDATE

On Wed, 02 Jan 2008 02:04:14 +0100, Aaron Gray =

wrote:

> "Jerry Stuckle" wrote in message
> news:y_WdnRKnboihf-fanZ2dnUVZ_sPinZ2d@comcast.com...
>> Aaron Gray wrote:
>>> I have a MySQL table of pages each with a 'title' and 'page' fields.=

>>>
>>> How do I do either an UPDATE if the pages 'title' exists or an INSER=
T =

>>> if
>>> it
>>> does not ?
>>>
>>> The code I have written does not seem to do the job correctly.
>>>
>>> $result =3D mysql_query( "SELECT page FROM pages WHERE title =3D=
'" .
>>> $title
>>> . "';");
>>>
>>> $row =3D mysql_fetch_array( $result, MYSQL_ASSOC);
>>>
>>> if ( isset( $row["title"]) && $row[title] == $title)
>>> {
>>> $result =3D mysql_query( "UPDATE pages SET page =3D '". $pag=
e . "'
>>> WHERE
>>> title =3D '" .$title . "';");
>>> if ($result)
>>> mysql_query( "COMMIT;");
>>> }
>>> else
>>> {
>>> $result =3D mysql_query( "INSERT INTO pages SET title =3D'" =
.. =

>>> $title
>>> .
>>> "', page =3D '" . $page . "';");
>>> if ($result)
>>> mysql_query( "COMMIT;");
>>> };
>>>
>>> This code is buggy and doing an insert rather than an update. How wo=
uld
>>> you approach this task ?
>>>
>>> Many thanks in advance,
>>>
>>> Aaron
>>>
>>>
>>>
>>>
>>
>> Try comp.databases.mysql. This is a PHP newsgroup.
>
> The problems I am having seem to be the PHP side of things not the SQL=
=

> side.

The MySQL side could handle this far better then the PHP side though, =

certainly when there could be concurrent updates/inserts.



-- =

Rik Wasmus

Re: Determining whether to INSERT or UPDATE

"Rik Wasmus" wrote in message
news:op.t392n4xa5bnjuv@metallium.lan...
>It could be done with one easy query, look into ON DUPLICATE KEY UPDATE
>syntax, ask in comp.databases.mysql

Okay, thanks,

Aaron

Re: Determining whether to INSERT or UPDATE

>>> Aaron Gray wrote:
> I have a MySQL table of pages each with a 'title' and 'page' fields.
>
> How do I do either an UPDATE if the pages 'title' exists or an INSERT
> if
> it
> does not ?

Check out the REPLACE syntax:
http://dev.mysql.com/doc/refman/5.0/en/replace.html

Your code looks okay ... what does print_r($title)
show? Where's the BEGIN for the transaction?

Re: Determining whether to INSERT or UPDATE

Aaron Gray wrote:
> "Jerry Stuckle" wrote in message
> news:y_WdnRKnboihf-fanZ2dnUVZ_sPinZ2d@comcast.com...
>> Aaron Gray wrote:
>>> I have a MySQL table of pages each with a 'title' and 'page' fields.
>>>
>>> How do I do either an UPDATE if the pages 'title' exists or an INSERT if
>>> it
>>> does not ?
>>>
>>> The code I have written does not seem to do the job correctly.
>>>
>>> $result = mysql_query( "SELECT page FROM pages WHERE title = '" .
>>> $title
>>> . "';");
>>>
>>> $row = mysql_fetch_array( $result, MYSQL_ASSOC);
>>>
>>> if ( isset( $row["title"]) && $row[title] == $title)
>>> {
>>> $result = mysql_query( "UPDATE pages SET page = '". $page . "'
>>> WHERE
>>> title = '" .$title . "';");
>>> if ($result)
>>> mysql_query( "COMMIT;");
>>> }
>>> else
>>> {
>>> $result = mysql_query( "INSERT INTO pages SET title ='" . $title
>>> .
>>> "', page = '" . $page . "';");
>>> if ($result)
>>> mysql_query( "COMMIT;");
>>> };
>>>
>>> This code is buggy and doing an insert rather than an update. How would
>>> you approach this task ?
>>>
>>> Many thanks in advance,
>>>
>>> Aaron
>>>
>>>
>>>
>>>
>> Try comp.databases.mysql. This is a PHP newsgroup.
>
> The problems I am having seem to be the PHP side of things not the SQL side.
>
> Aaron
>
>
Well you have parentheses round one array index and not another..so
start there.
f ( isset( $row["title"]) && $row[title] == $title)

Re: Determining whether to INSERT or UPDATE

On Wed, 02 Jan 2008 02:22:19 +0100, Charles Polisher
wrote:

>>>> Aaron Gray wrote:
>> I have a MySQL table of pages each with a 'title' and 'page' fields.
>>
>> How do I do either an UPDATE if the pages 'title' exists or an INSERT
>> if
>> it
>> does not ?
>
> Check out the REPLACE syntax:
> http://dev.mysql.com/doc/refman/5.0/en/replace.html
>
> Your code looks okay ... what does print_r($title)
> show? Where's the BEGIN for the transaction?
>

REPLACE has drawbacks, especially with foreign constraints & triggers...
ask in comp.databases.mysql what they are.
--
Rik Wasmus

Re: Determining whether to INSERT or UPDATE

"The Natural Philosopher" wrote in message
news:1199238401.6726.2@proxy02.news.clara.net...
> Aaron Gray wrote:
>> "Jerry Stuckle" wrote in message
>> news:y_WdnRKnboihf-fanZ2dnUVZ_sPinZ2d@comcast.com...
>>> Aaron Gray wrote:
>>>> I have a MySQL table of pages each with a 'title' and 'page' fields.
>>>>
>>>> How do I do either an UPDATE if the pages 'title' exists or an INSERT
>>>> if it
>>>> does not ?
>>>>
>>>> The code I have written does not seem to do the job correctly.
>>>>
>>>> $result = mysql_query( "SELECT page FROM pages WHERE title = '" .
>>>> $title
>>>> . "';");
>>>>
>>>> $row = mysql_fetch_array( $result, MYSQL_ASSOC);
>>>>
>>>> if ( isset( $row["title"]) && $row[title] == $title)
>>>> {
>>>> $result = mysql_query( "UPDATE pages SET page = '". $page . "'
>>>> WHERE
>>>> title = '" .$title . "';");
>>>> if ($result)
>>>> mysql_query( "COMMIT;");
>>>> }
>>>> else
>>>> {
>>>> $result = mysql_query( "INSERT INTO pages SET title ='" .
>>>> $title .
>>>> "', page = '" . $page . "';");
>>>> if ($result)
>>>> mysql_query( "COMMIT;");
>>>> };
>>>>
>>>> This code is buggy and doing an insert rather than an update. How would
>>>> you approach this task ?
>>>>
>>>> Many thanks in advance,
>>>>
>>>> Aaron
>>>>
>>>>
>>>>
>>>>
>>> Try comp.databases.mysql. This is a PHP newsgroup.
>>
>> The problems I am having seem to be the PHP side of things not the SQL
>> side.
>>
>> Aaron
>>
>>
> Well you have parentheses round one array index and not another..so start
> there.
> f ( isset( $row["title"]) && $row[title] == $title)

Thats a typo not actual code.

Aaron

Re: Determining whether to INSERT or UPDATE

"Rik Wasmus" wrote in message
news:op.t392n4xa5bnjuv@metallium.lan...
On Wed, 02 Jan 2008 01:50:20 +0100, Aaron Gray
wrote:

> I have a MySQL table of pages each with a 'title' and 'page' fields.
>
> How do I do either an UPDATE if the pages 'title' exists or an INSERT if
> it
> does not ?
>
> The code I have written does not seem to do the job correctly.
>
> $result = mysql_query( "SELECT page FROM pages WHERE title = '" .
> $title
> . "';");
>
> $row = mysql_fetch_array( $result, MYSQL_ASSOC);
>
> if ( isset( $row["title"]) && $row[title] == $title)
> {
> $result = mysql_query( "UPDATE pages SET page = '". $page . "'
> WHERE
> title = '" .$title . "';");
> if ($result)
> mysql_query( "COMMIT;");
> }
> else
> {
> $result = mysql_query( "INSERT INTO pages SET title ='" . $title
> .
> "', page = '" . $page . "';");
> if ($result)
> mysql_query( "COMMIT;");
> };
>
> This code is buggy and doing an insert rather than an update. How would
> you
> approach this task ?

It could be done with one easy query, look into ON DUPLICATE KEY UPDATE
syntax, ask in comp.databases.mysql

I need to keep the logic in PHP rather than using SQL.

Aaron

Re: Determining whether to INSERT or UPDATE

Aaron Gray wrote:
> "Rik Wasmus" wrote in message
> news:op.t392n4xa5bnjuv@metallium.lan...
> On Wed, 02 Jan 2008 01:50:20 +0100, Aaron Gray
> wrote:
>
>> I have a MySQL table of pages each with a 'title' and 'page' fields.
>>
>> How do I do either an UPDATE if the pages 'title' exists or an INSERT if
>> it
>> does not ?
>>
>> The code I have written does not seem to do the job correctly.
>>
>> $result = mysql_query( "SELECT page FROM pages WHERE title = '" .
>> $title
>> . "';");
>>
>> $row = mysql_fetch_array( $result, MYSQL_ASSOC);
>>
>> if ( isset( $row["title"]) && $row[title] == $title)
>> {
>> $result = mysql_query( "UPDATE pages SET page = '". $page . "'
>> WHERE
>> title = '" .$title . "';");
>> if ($result)
>> mysql_query( "COMMIT;");
>> }
>> else
>> {
>> $result = mysql_query( "INSERT INTO pages SET title ='" . $title
>> .
>> "', page = '" . $page . "';");
>> if ($result)
>> mysql_query( "COMMIT;");
>> };
>>
>> This code is buggy and doing an insert rather than an update. How would
>> you
>> approach this task ?
>
> It could be done with one easy query, look into ON DUPLICATE KEY UPDATE
> syntax, ask in comp.databases.mysql
>
> I need to keep the logic in PHP rather than using SQL.
>
> Aaron
>
>
>

Why? SQL is the correct place to handle this.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Re: Determining whether to INSERT or UPDATE

On Wed, 02 Jan 2008 02:56:31 +0100, Aaron Gray =

wrote:

> "Rik Wasmus" wrote in message
> news:op.t392n4xa5bnjuv@metallium.lan...
> On Wed, 02 Jan 2008 01:50:20 +0100, Aaron Gray
> wrote:
>
>> I have a MySQL table of pages each with a 'title' and 'page' fields.
>>
>> How do I do either an UPDATE if the pages 'title' exists or an INSERT=
if
>> it
>> does not ?
>>
>> The code I have written does not seem to do the job correctly.
>>
>> $result =3D mysql_query( "SELECT page FROM pages WHERE title =3D =
'" .
>> $title
>> . "';");
>>
>> $row =3D mysql_fetch_array( $result, MYSQL_ASSOC);
>>
>> if ( isset( $row["title"]) && $row[title] == $title)
>> {
>> $result =3D mysql_query( "UPDATE pages SET page =3D '". $page=
. "'
>> WHERE
>> title =3D '" .$title . "';");
>> if ($result)
>> mysql_query( "COMMIT;");
>> }
>> else
>> {
>> $result =3D mysql_query( "INSERT INTO pages SET title =3D'" .=
$title
>> .
>> "', page =3D '" . $page . "';");
>> if ($result)
>> mysql_query( "COMMIT;");
>> };
>>
>> This code is buggy and doing an insert rather than an update. How wou=
ld
>> you
>> approach this task ?
>
> It could be done with one easy query, look into ON DUPLICATE KEY =

> UPDATE
> syntax, ask in comp.databases.mysql
>
> I need to keep the logic in PHP rather than using SQL.

And why is that? Could you tell us what you expect to benefit from that?=
=

This kind of thing should IMHO be handled by the database, maintaining =

integrity of the data it holds is something a database is good at.
-- =

Rik Wasmus

Re: Determining whether to INSERT or UPDATE

"Jerry Stuckle" wrote in message
news:m_Wdnblb4PlxcufanZ2dnUVZ_gWdnZ2d@comcast.com...
> Aaron Gray wrote:
>> "Rik Wasmus" wrote in message
>> news:op.t392n4xa5bnjuv@metallium.lan...
>> On Wed, 02 Jan 2008 01:50:20 +0100, Aaron Gray
>> wrote:
>>
>>> I have a MySQL table of pages each with a 'title' and 'page' fields.
>>>
>>> How do I do either an UPDATE if the pages 'title' exists or an INSERT if
>>> it
>>> does not ?
>>>
>>> The code I have written does not seem to do the job correctly.
>>>
>>> $result = mysql_query( "SELECT page FROM pages WHERE title = '" .
>>> $title
>>> . "';");
>>>
>>> $row = mysql_fetch_array( $result, MYSQL_ASSOC);
>>>
>>> if ( isset( $row["title"]) && $row[title] == $title)
>>> {
>>> $result = mysql_query( "UPDATE pages SET page = '". $page . "'
>>> WHERE
>>> title = '" .$title . "';");
>>> if ($result)
>>> mysql_query( "COMMIT;");
>>> }
>>> else
>>> {
>>> $result = mysql_query( "INSERT INTO pages SET title ='" . $title
>>> .
>>> "', page = '" . $page . "';");
>>> if ($result)
>>> mysql_query( "COMMIT;");
>>> };
>>>
>>> This code is buggy and doing an insert rather than an update. How would
>>> you
>>> approach this task ?
>>
>> It could be done with one easy query, look into ON DUPLICATE KEY
>> UPDATE
>> syntax, ask in comp.databases.mysql
>>
>> I need to keep the logic in PHP rather than using SQL.
>>
>> Aaron
>>
>>
>>
>
> Why? SQL is the correct place to handle this.

I need to know whether I have a new page or not and I need concurrency in
the PHP to know whether a page is already being editted and I need to keep a
log of transactions.

At this stage in my apps life, prototype phase I prefer to keep this llogic
in the PHP code.

Aaron

Re: Determining whether to INSERT or UPDATE

"Rik Wasmus" wrote in message
news:op.t3947s0i5bnjuv@metallium.lan...
On Wed, 02 Jan 2008 02:56:31 +0100, Aaron Gray
wrote:

> "Rik Wasmus" wrote in message
> news:op.t392n4xa5bnjuv@metallium.lan...
> On Wed, 02 Jan 2008 01:50:20 +0100, Aaron Gray
> wrote:
>
>> I have a MySQL table of pages each with a 'title' and 'page' fields.
>>
>> How do I do either an UPDATE if the pages 'title' exists or an INSERT if
>> it
>> does not ?
>>
>> The code I have written does not seem to do the job correctly.
>>
>> $result = mysql_query( "SELECT page FROM pages WHERE title = '" .
>> $title
>> . "';");
>>
>> $row = mysql_fetch_array( $result, MYSQL_ASSOC);
>>
>> if ( isset( $row["title"]) && $row[title] == $title)
>> {
>> $result = mysql_query( "UPDATE pages SET page = '". $page . "'
>> WHERE
>> title = '" .$title . "';");
>> if ($result)
>> mysql_query( "COMMIT;");
>> }
>> else
>> {
>> $result = mysql_query( "INSERT INTO pages SET title ='" . $title
>> .
>> "', page = '" . $page . "';");
>> if ($result)
>> mysql_query( "COMMIT;");
>> };
>>
>> This code is buggy and doing an insert rather than an update. How would
>> you
>> approach this task ?
>
> It could be done with one easy query, look into ON DUPLICATE KEY
> UPDATE
> syntax, ask in comp.databases.mysql
>
> I need to keep the logic in PHP rather than using SQL.

And why is that? Could you tell us what you expect to benefit from that?
This kind of thing should IMHO be handled by the database, maintaining
integrity of the data it holds is something a database is good at.

I am just not that familuar with SQL to take such an approach at this stage.

Aaron

Re: Determining whether to INSERT or UPDATE

Aaron Gray wrote:
> "Jerry Stuckle" wrote in message
> news:m_Wdnblb4PlxcufanZ2dnUVZ_gWdnZ2d@comcast.com...
>> Aaron Gray wrote:
>>> "Rik Wasmus" wrote in message
>>> news:op.t392n4xa5bnjuv@metallium.lan...
>>> On Wed, 02 Jan 2008 01:50:20 +0100, Aaron Gray
>>> wrote:
>>>
>>>> I have a MySQL table of pages each with a 'title' and 'page' fields.
>>>>
>>>> How do I do either an UPDATE if the pages 'title' exists or an INSERT if
>>>> it
>>>> does not ?
>>>>
>>>> The code I have written does not seem to do the job correctly.
>>>>
>>>> $result = mysql_query( "SELECT page FROM pages WHERE title = '" .
>>>> $title
>>>> . "';");
>>>>
>>>> $row = mysql_fetch_array( $result, MYSQL_ASSOC);
>>>>
>>>> if ( isset( $row["title"]) && $row[title] == $title)
>>>> {
>>>> $result = mysql_query( "UPDATE pages SET page = '". $page . "'
>>>> WHERE
>>>> title = '" .$title . "';");
>>>> if ($result)
>>>> mysql_query( "COMMIT;");
>>>> }
>>>> else
>>>> {
>>>> $result = mysql_query( "INSERT INTO pages SET title ='" . $title
>>>> .
>>>> "', page = '" . $page . "';");
>>>> if ($result)
>>>> mysql_query( "COMMIT;");
>>>> };
>>>>
>>>> This code is buggy and doing an insert rather than an update. How would
>>>> you
>>>> approach this task ?
>>> It could be done with one easy query, look into ON DUPLICATE KEY
>>> UPDATE
>>> syntax, ask in comp.databases.mysql
>>>
>>> I need to keep the logic in PHP rather than using SQL.
>>>
>>> Aaron
>>>
>>>
>>>
>> Why? SQL is the correct place to handle this.
>
> I need to know whether I have a new page or not and I need concurrency in
> the PHP to know whether a page is already being editted and I need to keep a
> log of transactions.
>

OK, if you need to find out if you have a new page or not, you've got to
query the database to see if it exists. But you've also got to be
careful, because someone could insert the same page between the time you
query and the time you insert. Sure, it's a small period of time, but
it can still happen.

And you can't tell for sure if a page is being edited or not. All you
can really tell is if it has been edited.

> At this stage in my apps life, prototype phase I prefer to keep this llogic
> in the PHP code.
>
> Aaron
>
>

That's doing it the hard way. Use the tools available to you.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Re: Determining whether to INSERT or UPDATE

"Jerry Stuckle" wrote in message
news:65-dnQNSmtoCaefanZ2dnUVZ_r-vnZ2d@comcast.com...
> Aaron Gray wrote:
>> "Jerry Stuckle" wrote in message
>> news:m_Wdnblb4PlxcufanZ2dnUVZ_gWdnZ2d@comcast.com...
>>> Aaron Gray wrote:
>>>> "Rik Wasmus" wrote in message
>>>> news:op.t392n4xa5bnjuv@metallium.lan...
>>>> On Wed, 02 Jan 2008 01:50:20 +0100, Aaron Gray
>>>> wrote:
>>>>
>>>>> I have a MySQL table of pages each with a 'title' and 'page' fields.
>>>>>
>>>>> How do I do either an UPDATE if the pages 'title' exists or an INSERT
>>>>> if it
>>>>> does not ?
>>>>>
>>>>> The code I have written does not seem to do the job correctly.
>>>>>
>>>>> $result = mysql_query( "SELECT page FROM pages WHERE title = '" .
>>>>> $title
>>>>> . "';");
>>>>>
>>>>> $row = mysql_fetch_array( $result, MYSQL_ASSOC);
>>>>>
>>>>> if ( isset( $row["title"]) && $row[title] == $title)
>>>>> {
>>>>> $result = mysql_query( "UPDATE pages SET page = '". $page . "'
>>>>> WHERE
>>>>> title = '" .$title . "';");
>>>>> if ($result)
>>>>> mysql_query( "COMMIT;");
>>>>> }
>>>>> else
>>>>> {
>>>>> $result = mysql_query( "INSERT INTO pages SET title ='" .
>>>>> $title .
>>>>> "', page = '" . $page . "';");
>>>>> if ($result)
>>>>> mysql_query( "COMMIT;");
>>>>> };
>>>>>
>>>>> This code is buggy and doing an insert rather than an update. How
>>>>> would you
>>>>> approach this task ?
>>>> It could be done with one easy query, look into ON DUPLICATE KEY
>>>> UPDATE
>>>> syntax, ask in comp.databases.mysql
>>>>
>>>> I need to keep the logic in PHP rather than using SQL.
>>>>
>>>> Aaron
>>>>
>>>>
>>>>
>>> Why? SQL is the correct place to handle this.
>>
>> I need to know whether I have a new page or not and I need concurrency in
>> the PHP to know whether a page is already being editted and I need to
>> keep a log of transactions.
>>
>
> OK, if you need to find out if you have a new page or not, you've got to
> query the database to see if it exists. But you've also got to be
> careful, because someone could insert the same page between the time you
> query and the time you insert. Sure, it's a small period of time, but it
> can still happen.
>
> And you can't tell for sure if a page is being edited or not. All you can
> really tell is if it has been edited.
>
>> At this stage in my apps life, prototype phase I prefer to keep this
>> llogic in the PHP code.
>>
>> Aaron
>>
>>
>
> That's doing it the hard way. Use the tools available to you.

Also for the intended applications programmers who are users of my app
generally do not know SQL but can program in an average programming
language.

I would like to look at and learn the SQL but not now. I really want to keep
this thing simple at the moment and SQL is not simple compared to PHP.

Aaron

Re: Determining whether to INSERT or UPDATE

Aaron Gray wrote:
> "Jerry Stuckle" wrote in message
> news:65-dnQNSmtoCaefanZ2dnUVZ_r-vnZ2d@comcast.com...
>> Aaron Gray wrote:
>>> "Jerry Stuckle" wrote in message
>>> news:m_Wdnblb4PlxcufanZ2dnUVZ_gWdnZ2d@comcast.com...
>>>> Aaron Gray wrote:
>>>>> "Rik Wasmus" wrote in message
>>>>> news:op.t392n4xa5bnjuv@metallium.lan...
>>>>> On Wed, 02 Jan 2008 01:50:20 +0100, Aaron Gray
>>>>> wrote:
>>>>>
>>>>>> I have a MySQL table of pages each with a 'title' and 'page' fields.
>>>>>>
>>>>>> How do I do either an UPDATE if the pages 'title' exists or an INSERT
>>>>>> if it
>>>>>> does not ?
>>>>>>
>>>>>> The code I have written does not seem to do the job correctly.
>>>>>>
>>>>>> $result = mysql_query( "SELECT page FROM pages WHERE title = '" .
>>>>>> $title
>>>>>> . "';");
>>>>>>
>>>>>> $row = mysql_fetch_array( $result, MYSQL_ASSOC);
>>>>>>
>>>>>> if ( isset( $row["title"]) && $row[title] == $title)
>>>>>> {
>>>>>> $result = mysql_query( "UPDATE pages SET page = '". $page . "'
>>>>>> WHERE
>>>>>> title = '" .$title . "';");
>>>>>> if ($result)
>>>>>> mysql_query( "COMMIT;");
>>>>>> }
>>>>>> else
>>>>>> {
>>>>>> $result = mysql_query( "INSERT INTO pages SET title ='" .
>>>>>> $title .
>>>>>> "', page = '" . $page . "';");
>>>>>> if ($result)
>>>>>> mysql_query( "COMMIT;");
>>>>>> };
>>>>>>
>>>>>> This code is buggy and doing an insert rather than an update. How
>>>>>> would you
>>>>>> approach this task ?
>>>>> It could be done with one easy query, look into ON DUPLICATE KEY
>>>>> UPDATE
>>>>> syntax, ask in comp.databases.mysql
>>>>>
>>>>> I need to keep the logic in PHP rather than using SQL.
>>>>>
>>>>> Aaron
>>>>>
>>>>>
>>>>>
>>>> Why? SQL is the correct place to handle this.
>>> I need to know whether I have a new page or not and I need concurrency in
>>> the PHP to know whether a page is already being editted and I need to
>>> keep a log of transactions.
>>>
>> OK, if you need to find out if you have a new page or not, you've got to
>> query the database to see if it exists. But you've also got to be
>> careful, because someone could insert the same page between the time you
>> query and the time you insert. Sure, it's a small period of time, but it
>> can still happen.
>>
>> And you can't tell for sure if a page is being edited or not. All you can
>> really tell is if it has been edited.
>>
>>> At this stage in my apps life, prototype phase I prefer to keep this
>>> llogic in the PHP code.
>>>
>>> Aaron
>>>
>>>
>> That's doing it the hard way. Use the tools available to you.
>
> Also for the intended applications programmers who are users of my app
> generally do not know SQL but can program in an average programming
> language.
>
> I would like to look at and learn the SQL but not now. I really want to keep
> this thing simple at the moment and SQL is not simple compared to PHP.
>
> Aaron
>
>
>

SQL is much simpler than PHP!

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Re: Determining whether to INSERT or UPDATE

"Jerry Stuckle" wrote in message
news:RbadnWLvEvb4ZOfanZ2dnUVZ_vXinZ2d@comcast.com...
> Aaron Gray wrote:
>> "Jerry Stuckle" wrote in message
>> news:65-dnQNSmtoCaefanZ2dnUVZ_r-vnZ2d@comcast.com...
>>> Aaron Gray wrote:
>>>> "Jerry Stuckle" wrote in message
>>>> news:m_Wdnblb4PlxcufanZ2dnUVZ_gWdnZ2d@comcast.com...
>>>>> Aaron Gray wrote:
>>>>>> "Rik Wasmus" wrote in message
>>>>>> news:op.t392n4xa5bnjuv@metallium.lan...
>>>>>> On Wed, 02 Jan 2008 01:50:20 +0100, Aaron Gray
>>>>>> wrote:
>>>>>>
>>>>>>> I have a MySQL table of pages each with a 'title' and 'page' fields.
>>>>>>>
>>>>>>> How do I do either an UPDATE if the pages 'title' exists or an
>>>>>>> INSERT if it
>>>>>>> does not ?
>>>>>>>
>>>>>>> The code I have written does not seem to do the job correctly.
>>>>>>>
>>>>>>> $result = mysql_query( "SELECT page FROM pages WHERE title = '"
>>>>>>> . $title
>>>>>>> . "';");
>>>>>>>
>>>>>>> $row = mysql_fetch_array( $result, MYSQL_ASSOC);
>>>>>>>
>>>>>>> if ( isset( $row["title"]) && $row[title] == $title)
>>>>>>> {
>>>>>>> $result = mysql_query( "UPDATE pages SET page = '". $page .
>>>>>>> "' WHERE
>>>>>>> title = '" .$title . "';");
>>>>>>> if ($result)
>>>>>>> mysql_query( "COMMIT;");
>>>>>>> }
>>>>>>> else
>>>>>>> {
>>>>>>> $result = mysql_query( "INSERT INTO pages SET title ='" .
>>>>>>> $title .
>>>>>>> "', page = '" . $page . "';");
>>>>>>> if ($result)
>>>>>>> mysql_query( "COMMIT;");
>>>>>>> };
>>>>>>>
>>>>>>> This code is buggy and doing an insert rather than an update. How
>>>>>>> would you
>>>>>>> approach this task ?
>>>>>> It could be done with one easy query, look into ON DUPLICATE KEY
>>>>>> UPDATE
>>>>>> syntax, ask in comp.databases.mysql
>>>>>>
>>>>>> I need to keep the logic in PHP rather than using SQL.
>>>>>>
>>>>>> Aaron
>>>>>>
>>>>>>
>>>>>>
>>>>> Why? SQL is the correct place to handle this.
>>>> I need to know whether I have a new page or not and I need concurrency
>>>> in the PHP to know whether a page is already being editted and I need
>>>> to keep a log of transactions.
>>>>
>>> OK, if you need to find out if you have a new page or not, you've got to
>>> query the database to see if it exists. But you've also got to be
>>> careful, because someone could insert the same page between the time you
>>> query and the time you insert. Sure, it's a small period of time, but
>>> it can still happen.
>>>
>>> And you can't tell for sure if a page is being edited or not. All you
>>> can really tell is if it has been edited.
>>>
>>>> At this stage in my apps life, prototype phase I prefer to keep this
>>>> llogic in the PHP code.
>>>>
>>>> Aaron
>>>>
>>>>
>>> That's doing it the hard way. Use the tools available to you.
>>
>> Also for the intended applications programmers who are users of my app
>> generally do not know SQL but can program in an average programming
>> language.
>>
>> I would like to look at and learn the SQL but not now. I really want to
>> keep this thing simple at the moment and SQL is not simple compared to
>> PHP.
>>
>> Aaron
>>
>>
>>
>
> SQL is much simpler than PHP!

Its neater and more integral, but I would not say its simpler :)

Aaron

Re: Determining whether to INSERT or UPDATE

Aaron Gray wrote:
> "Jerry Stuckle" wrote in message
> news:RbadnWLvEvb4ZOfanZ2dnUVZ_vXinZ2d@comcast.com...
>> Aaron Gray wrote:
>>> "Jerry Stuckle" wrote in message
>>> news:65-dnQNSmtoCaefanZ2dnUVZ_r-vnZ2d@comcast.com...
>>>> Aaron Gray wrote:
>>>>> "Jerry Stuckle" wrote in message
>>>>> news:m_Wdnblb4PlxcufanZ2dnUVZ_gWdnZ2d@comcast.com...
>>>>>> Aaron Gray wrote:
>>>>>>> "Rik Wasmus" wrote in message
>>>>>>> news:op.t392n4xa5bnjuv@metallium.lan...
>>>>>>> On Wed, 02 Jan 2008 01:50:20 +0100, Aaron Gray
>>>>>>> wrote:
>>>>>>>
>>>>>>>> I have a MySQL table of pages each with a 'title' and 'page' fields.
>>>>>>>>
>>>>>>>> How do I do either an UPDATE if the pages 'title' exists or an
>>>>>>>> INSERT if it
>>>>>>>> does not ?
>>>>>>>>
>>>>>>>> The code I have written does not seem to do the job correctly.
>>>>>>>>
>>>>>>>> $result = mysql_query( "SELECT page FROM pages WHERE title = '"
>>>>>>>> . $title
>>>>>>>> . "';");
>>>>>>>>
>>>>>>>> $row = mysql_fetch_array( $result, MYSQL_ASSOC);
>>>>>>>>
>>>>>>>> if ( isset( $row["title"]) && $row[title] == $title)
>>>>>>>> {
>>>>>>>> $result = mysql_query( "UPDATE pages SET page = '". $page .
>>>>>>>> "' WHERE
>>>>>>>> title = '" .$title . "';");
>>>>>>>> if ($result)
>>>>>>>> mysql_query( "COMMIT;");
>>>>>>>> }
>>>>>>>> else
>>>>>>>> {
>>>>>>>> $result = mysql_query( "INSERT INTO pages SET title ='" .
>>>>>>>> $title .
>>>>>>>> "', page = '" . $page . "';");
>>>>>>>> if ($result)
>>>>>>>> mysql_query( "COMMIT;");
>>>>>>>> };
>>>>>>>>
>>>>>>>> This code is buggy and doing an insert rather than an update. How
>>>>>>>> would you
>>>>>>>> approach this task ?
>>>>>>> It could be done with one easy query, look into ON DUPLICATE KEY
>>>>>>> UPDATE
>>>>>>> syntax, ask in comp.databases.mysql
>>>>>>>
>>>>>>> I need to keep the logic in PHP rather than using SQL.
>>>>>>>
>>>>>>> Aaron
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>> Why? SQL is the correct place to handle this.
>>>>> I need to know whether I have a new page or not and I need concurrency
>>>>> in the PHP to know whether a page is already being editted and I need
>>>>> to keep a log of transactions.
>>>>>
>>>> OK, if you need to find out if you have a new page or not, you've got to
>>>> query the database to see if it exists. But you've also got to be
>>>> careful, because someone could insert the same page between the time you
>>>> query and the time you insert. Sure, it's a small period of time, but
>>>> it can still happen.
>>>>
>>>> And you can't tell for sure if a page is being edited or not. All you
>>>> can really tell is if it has been edited.
>>>>
>>>>> At this stage in my apps life, prototype phase I prefer to keep this
>>>>> llogic in the PHP code.
>>>>>
>>>>> Aaron
>>>>>
>>>>>
>>>> That's doing it the hard way. Use the tools available to you.
>>> Also for the intended applications programmers who are users of my app
>>> generally do not know SQL but can program in an average programming
>>> language.
>>>
>>> I would like to look at and learn the SQL but not now. I really want to
>>> keep this thing simple at the moment and SQL is not simple compared to
>>> PHP.
>>>
>>> Aaron
>>>
>>>
>>>
>> SQL is much simpler than PHP!
>
> Its neater and more integral, but I would not say its simpler :)
>
> Aaron
>
>
>

Definitely much simpler. A very limited command set with limited
options. We teach SQL in about a day and a half, as part of our database
courses. PHP alone is 5 days - and I wish we had more time.

If you're going to be using relational databases, you need to learn SQL.

But your users don't need to use SQL. I have several sites which use
SQL databases. And none of my customers have to write a single line of SQL.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Re: Determining whether to INSERT or UPDATE

"Jerry Stuckle" wrote in message
news:JIGdnZeODo3Pn-banZ2dnUVZ_rrinZ2d@comcast.com...
> Aaron Gray wrote:
>> "Jerry Stuckle" wrote in message
>> news:RbadnWLvEvb4ZOfanZ2dnUVZ_vXinZ2d@comcast.com...
>>> Aaron Gray wrote:
>>>> "Jerry Stuckle" wrote in message
>>>> news:65-dnQNSmtoCaefanZ2dnUVZ_r-vnZ2d@comcast.com...
>>>>> Aaron Gray wrote:
>>>>>> "Jerry Stuckle" wrote in message
>>>>>> news:m_Wdnblb4PlxcufanZ2dnUVZ_gWdnZ2d@comcast.com...
>>>>>>> Aaron Gray wrote:
>>>>>>>> "Rik Wasmus" wrote in message
>>>>>>>> news:op.t392n4xa5bnjuv@metallium.lan...
>>>>>>>> On Wed, 02 Jan 2008 01:50:20 +0100, Aaron Gray
>>>>>>>>
>>>>>>>> wrote:
>>>>>>>>
>>>>>>>>> I have a MySQL table of pages each with a 'title' and 'page'
>>>>>>>>> fields.
>>>>>>>>>
>>>>>>>>> How do I do either an UPDATE if the pages 'title' exists or an
>>>>>>>>> INSERT if it
>>>>>>>>> does not ?
>>>>>>>>>
>>>>>>>>> The code I have written does not seem to do the job correctly.
>>>>>>>>>
>>>>>>>>> $result = mysql_query( "SELECT page FROM pages WHERE title =
>>>>>>>>> '" . $title
>>>>>>>>> . "';");
>>>>>>>>>
>>>>>>>>> $row = mysql_fetch_array( $result, MYSQL_ASSOC);
>>>>>>>>>
>>>>>>>>> if ( isset( $row["title"]) && $row[title] == $title)
>>>>>>>>> {
>>>>>>>>> $result = mysql_query( "UPDATE pages SET page = '". $page
>>>>>>>>> . "' WHERE
>>>>>>>>> title = '" .$title . "';");
>>>>>>>>> if ($result)
>>>>>>>>> mysql_query( "COMMIT;");
>>>>>>>>> }
>>>>>>>>> else
>>>>>>>>> {
>>>>>>>>> $result = mysql_query( "INSERT INTO pages SET title ='" .
>>>>>>>>> $title .
>>>>>>>>> "', page = '" . $page . "';");
>>>>>>>>> if ($result)
>>>>>>>>> mysql_query( "COMMIT;");
>>>>>>>>> };
>>>>>>>>>
>>>>>>>>> This code is buggy and doing an insert rather than an update. How
>>>>>>>>> would you
>>>>>>>>> approach this task ?
>>>>>>>> It could be done with one easy query, look into ON DUPLICATE
>>>>>>>> KEY UPDATE
>>>>>>>> syntax, ask in comp.databases.mysql
>>>>>>>>
>>>>>>>> I need to keep the logic in PHP rather than using SQL.
>>>>>>>>
>>>>>>>> Aaron
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>> Why? SQL is the correct place to handle this.
>>>>>> I need to know whether I have a new page or not and I need
>>>>>> concurrency in the PHP to know whether a page is already being
>>>>>> editted and I need to keep a log of transactions.
>>>>>>
>>>>> OK, if you need to find out if you have a new page or not, you've got
>>>>> to query the database to see if it exists. But you've also got to be
>>>>> careful, because someone could insert the same page between the time
>>>>> you query and the time you insert. Sure, it's a small period of time,
>>>>> but it can still happen.
>>>>>
>>>>> And you can't tell for sure if a page is being edited or not. All you
>>>>> can really tell is if it has been edited.
>>>>>
>>>>>> At this stage in my apps life, prototype phase I prefer to keep this
>>>>>> llogic in the PHP code.
>>>>>>
>>>>>> Aaron
>>>>>>
>>>>>>
>>>>> That's doing it the hard way. Use the tools available to you.
>>>> Also for the intended applications programmers who are users of my app
>>>> generally do not know SQL but can program in an average programming
>>>> language.
>>>>
>>>> I would like to look at and learn the SQL but not now. I really want to
>>>> keep this thing simple at the moment and SQL is not simple compared to
>>>> PHP.
>>>>
>>>> Aaron
>>>>
>>>>
>>>>
>>> SQL is much simpler than PHP!
>>
>> Its neater and more integral, but I would not say its simpler :)
>>
>> Aaron
>>
>>
>>
>
> Definitely much simpler. A very limited command set with limited options.
> We teach SQL in about a day and a half, as part of our database courses.
> PHP alone is 5 days - and I wish we had more time.
>
> If you're going to be using relational databases, you need to learn SQL.
>
> But your users don't need to use SQL. I have several sites which use SQL
> databases. And none of my customers have to write a single line of SQL.

I am working on an open framework for use by other programmers, end users of
the product will not need internal knowledge but application programmers
will.

Proper relational database programming cannot be taught in less than a week,
more like a year if you want to follow Codd.

Aaron

Re: Determining whether to INSERT or UPDATE

Aaron Gray wrote:
> "Jerry Stuckle" wrote in message
> news:JIGdnZeODo3Pn-banZ2dnUVZ_rrinZ2d@comcast.com...
>> Aaron Gray wrote:
>>> "Jerry Stuckle" wrote in message
>>> news:RbadnWLvEvb4ZOfanZ2dnUVZ_vXinZ2d@comcast.com...
>>>> Aaron Gray wrote:
>>>>> "Jerry Stuckle" wrote in message
>>>>> news:65-dnQNSmtoCaefanZ2dnUVZ_r-vnZ2d@comcast.com...
>>>>>> Aaron Gray wrote:
>>>>>>> "Jerry Stuckle" wrote in message
>>>>>>> news:m_Wdnblb4PlxcufanZ2dnUVZ_gWdnZ2d@comcast.com...
>>>>>>>> Aaron Gray wrote:
>>>>>>>>> "Rik Wasmus" wrote in message
>>>>>>>>> news:op.t392n4xa5bnjuv@metallium.lan...
>>>>>>>>> On Wed, 02 Jan 2008 01:50:20 +0100, Aaron Gray
>>>>>>>>>
>>>>>>>>> wrote:
>>>>>>>>>
>>>>>>>>>> I have a MySQL table of pages each with a 'title' and 'page'
>>>>>>>>>> fields.
>>>>>>>>>>
>>>>>>>>>> How do I do either an UPDATE if the pages 'title' exists or an
>>>>>>>>>> INSERT if it
>>>>>>>>>> does not ?
>>>>>>>>>>
>>>>>>>>>> The code I have written does not seem to do the job correctly.
>>>>>>>>>>
>>>>>>>>>> $result = mysql_query( "SELECT page FROM pages WHERE title =
>>>>>>>>>> '" . $title
>>>>>>>>>> . "';");
>>>>>>>>>>
>>>>>>>>>> $row = mysql_fetch_array( $result, MYSQL_ASSOC);
>>>>>>>>>>
>>>>>>>>>> if ( isset( $row["title"]) && $row[title] == $title)
>>>>>>>>>> {
>>>>>>>>>> $result = mysql_query( "UPDATE pages SET page = '". $page
>>>>>>>>>> . "' WHERE
>>>>>>>>>> title = '" .$title . "';");
>>>>>>>>>> if ($result)
>>>>>>>>>> mysql_query( "COMMIT;");
>>>>>>>>>> }
>>>>>>>>>> else
>>>>>>>>>> {
>>>>>>>>>> $result = mysql_query( "INSERT INTO pages SET title ='" .
>>>>>>>>>> $title .
>>>>>>>>>> "', page = '" . $page . "';");
>>>>>>>>>> if ($result)
>>>>>>>>>> mysql_query( "COMMIT;");
>>>>>>>>>> };
>>>>>>>>>>
>>>>>>>>>> This code is buggy and doing an insert rather than an update. How
>>>>>>>>>> would you
>>>>>>>>>> approach this task ?
>>>>>>>>> It could be done with one easy query, look into ON DUPLICATE
>>>>>>>>> KEY UPDATE
>>>>>>>>> syntax, ask in comp.databases.mysql
>>>>>>>>>
>>>>>>>>> I need to keep the logic in PHP rather than using SQL.
>>>>>>>>>
>>>>>>>>> Aaron
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>> Why? SQL is the correct place to handle this.
>>>>>>> I need to know whether I have a new page or not and I need
>>>>>>> concurrency in the PHP to know whether a page is already being
>>>>>>> editted and I need to keep a log of transactions.
>>>>>>>
>>>>>> OK, if you need to find out if you have a new page or not, you've got
>>>>>> to query the database to see if it exists. But you've also got to be
>>>>>> careful, because someone could insert the same page between the time
>>>>>> you query and the time you insert. Sure, it's a small period of time,
>>>>>> but it can still happen.
>>>>>>
>>>>>> And you can't tell for sure if a page is being edited or not. All you
>>>>>> can really tell is if it has been edited.
>>>>>>
>>>>>>> At this stage in my apps life, prototype phase I prefer to keep this
>>>>>>> llogic in the PHP code.
>>>>>>>
>>>>>>> Aaron
>>>>>>>
>>>>>>>
>>>>>> That's doing it the hard way. Use the tools available to you.
>>>>> Also for the intended applications programmers who are users of my app
>>>>> generally do not know SQL but can program in an average programming
>>>>> language.
>>>>>
>>>>> I would like to look at and learn the SQL but not now. I really want to
>>>>> keep this thing simple at the moment and SQL is not simple compared to
>>>>> PHP.
>>>>>
>>>>> Aaron
>>>>>
>>>>>
>>>>>
>>>> SQL is much simpler than PHP!
>>> Its neater and more integral, but I would not say its simpler :)
>>>
>>> Aaron
>>>
>>>
>>>
>> Definitely much simpler. A very limited command set with limited options.
>> We teach SQL in about a day and a half, as part of our database courses.
>> PHP alone is 5 days - and I wish we had more time.
>>
>> If you're going to be using relational databases, you need to learn SQL.
>>
>> But your users don't need to use SQL. I have several sites which use SQL
>> databases. And none of my customers have to write a single line of SQL.
>
> I am working on an open framework for use by other programmers, end users of
> the product will not need internal knowledge but application programmers
> will.
>
> Proper relational database programming cannot be taught in less than a week,
> more like a year if you want to follow Codd.
>
> Aaron
>
>
>

Sorry, been doing this for over 17 years now. Corporate training is
much different than university courses - and VERY intensive. As I said
- we teach PHP in a week. The same for Java, C, C++ and other
languages. SQL is at most a day and a half. An entire MySQL course is
only 5 days (same with other databases).

I'm not saying the programmers are experts coming out of the course -
but they are knowledgeable to be productive. The rest comes with
experience.

You don't know what real training is until you've taken a corporate
training course!

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Re: Determining whether to INSERT or UPDATE

Aaron Gray wrote:
> "Jerry Stuckle" wrote in message
> news:JIGdnZeODo3Pn-banZ2dnUVZ_rrinZ2d@comcast.com...
>> Aaron Gray wrote:
>>> "Jerry Stuckle" wrote in message
>>> news:RbadnWLvEvb4ZOfanZ2dnUVZ_vXinZ2d@comcast.com...
>>>> Aaron Gray wrote:
>>>>> "Jerry Stuckle" wrote in message
>>>>> news:65-dnQNSmtoCaefanZ2dnUVZ_r-vnZ2d@comcast.com...
>>>>>> Aaron Gray wrote:
>>>>>>> "Jerry Stuckle" wrote in message
>>>>>>> news:m_Wdnblb4PlxcufanZ2dnUVZ_gWdnZ2d@comcast.com...
>>>>>>>> Aaron Gray wrote:
>>>>>>>>> "Rik Wasmus" wrote in message
>>>>>>>>> news:op.t392n4xa5bnjuv@metallium.lan...
>>>>>>>>> On Wed, 02 Jan 2008 01:50:20 +0100, Aaron Gray
>>>>>>>>>
>>>>>>>>> wrote:
>>>>>>>>>
>>>>>>>>>> I have a MySQL table of pages each with a 'title' and 'page'
>>>>>>>>>> fields.
>>>>>>>>>>
>>>>>>>>>> How do I do either an UPDATE if the pages 'title' exists or an
>>>>>>>>>> INSERT if it
>>>>>>>>>> does not ?
>>>>>>>>>>
>>>>>>>>>> The code I have written does not seem to do the job correctly.
>>>>>>>>>>
>>>>>>>>>> $result = mysql_query( "SELECT page FROM pages WHERE title =
>>>>>>>>>> '" . $title
>>>>>>>>>> . "';");
>>>>>>>>>>
>>>>>>>>>> $row = mysql_fetch_array( $result, MYSQL_ASSOC);
>>>>>>>>>>
>>>>>>>>>> if ( isset( $row["title"]) && $row[title] == $title)
>>>>>>>>>> {
>>>>>>>>>> $result = mysql_query( "UPDATE pages SET page = '". $page
>>>>>>>>>> . "' WHERE
>>>>>>>>>> title = '" .$title . "';");
>>>>>>>>>> if ($result)
>>>>>>>>>> mysql_query( "COMMIT;");
>>>>>>>>>> }
>>>>>>>>>> else
>>>>>>>>>> {
>>>>>>>>>> $result = mysql_query( "INSERT INTO pages SET title ='" .
>>>>>>>>>> $title .
>>>>>>>>>> "', page = '" . $page . "';");
>>>>>>>>>> if ($result)
>>>>>>>>>> mysql_query( "COMMIT;");
>>>>>>>>>> };
>>>>>>>>>>
>>>>>>>>>> This code is buggy and doing an insert rather than an update. How
>>>>>>>>>> would you
>>>>>>>>>> approach this task ?
>>>>>>>>> It could be done with one easy query, look into ON DUPLICATE
>>>>>>>>> KEY UPDATE
>>>>>>>>> syntax, ask in comp.databases.mysql
>>>>>>>>>
>>>>>>>>> I need to keep the logic in PHP rather than using SQL.
>>>>>>>>>
>>>>>>>>> Aaron
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>> Why? SQL is the correct place to handle this.
>>>>>>> I need to know whether I have a new page or not and I need
>>>>>>> concurrency in the PHP to know whether a page is already being
>>>>>>> editted and I need to keep a log of transactions.
>>>>>>>
>>>>>> OK, if you need to find out if you have a new page or not, you've got
>>>>>> to query the database to see if it exists. But you've also got to be
>>>>>> careful, because someone could insert the same page between the time
>>>>>> you query and the time you insert. Sure, it's a small period of time,
>>>>>> but it can still happen.
>>>>>>
>>>>>> And you can't tell for sure if a page is being edited or not. All you
>>>>>> can really tell is if it has been edited.
>>>>>>
>>>>>>> At this stage in my apps life, prototype phase I prefer to keep this
>>>>>>> llogic in the PHP code.
>>>>>>>
>>>>>>> Aaron
>>>>>>>
>>>>>>>
>>>>>> That's doing it the hard way. Use the tools available to you.
>>>>> Also for the intended applications programmers who are users of my app
>>>>> generally do not know SQL but can program in an average programming
>>>>> language.
>>>>>
>>>>> I would like to look at and learn the SQL but not now. I really want to
>>>>> keep this thing simple at the moment and SQL is not simple compared to
>>>>> PHP.
>>>>>
>>>>> Aaron
>>>>>
>>>>>
>>>>>
>>>> SQL is much simpler than PHP!
>>> Its neater and more integral, but I would not say its simpler :)
>>>
>>> Aaron
>>>
>>>
>>>
>> Definitely much simpler. A very limited command set with limited options.
>> We teach SQL in about a day and a half, as part of our database courses.
>> PHP alone is 5 days - and I wish we had more time.
>>
>> If you're going to be using relational databases, you need to learn SQL.
>>
>> But your users don't need to use SQL. I have several sites which use SQL
>> databases. And none of my customers have to write a single line of SQL.
>
> I am working on an open framework for use by other programmers, end users of
> the product will not need internal knowledge but application programmers
> will.
>
> Proper relational database programming cannot be taught in less than a week,
> more like a year if you want to follow Codd.
>
> Aaron
>
>
>

I forgot to add. If you're using a SQL database, then the programmers
will need to know SQL, also. But any decent PHP programmer should have
a reasonable knowledge of SQL, anyway.

Or, just forget about the database and use flat files. Problem solved.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Re: Determining whether to INSERT or UPDATE

"Jerry Stuckle" wrote in message
news:5rudnadaKo4Im-banZ2dnUVZ_vfinZ2d@comcast.com...
> Aaron Gray wrote:
>> "Jerry Stuckle" wrote in message
>> news:JIGdnZeODo3Pn-banZ2dnUVZ_rrinZ2d@comcast.com...
>>> Aaron Gray wrote:
>>>> "Jerry Stuckle" wrote in message
>>>> news:RbadnWLvEvb4ZOfanZ2dnUVZ_vXinZ2d@comcast.com...
>>>>> Aaron Gray wrote:
>>>>>> "Jerry Stuckle" wrote in message
>>>>>> news:65-dnQNSmtoCaefanZ2dnUVZ_r-vnZ2d@comcast.com...
>>>>>>> Aaron Gray wrote:
>>>>>>>> "Jerry Stuckle" wrote in message
>>>>>>>> news:m_Wdnblb4PlxcufanZ2dnUVZ_gWdnZ2d@comcast.com...
>>>>>>>>> Aaron Gray wrote:
>>>>>>>>>> "Rik Wasmus" wrote in message
>>>>>>>>>> news:op.t392n4xa5bnjuv@metallium.lan...
>>>>>>>>>> On Wed, 02 Jan 2008 01:50:20 +0100, Aaron Gray
>>>>>>>>>>
>>>>>>>>>> wrote:
>>>>>>>>>>
>>>>>>>>>>> I have a MySQL table of pages each with a 'title' and 'page'
>>>>>>>>>>> fields.
>>>>>>>>>>>
>>>>>>>>>>> How do I do either an UPDATE if the pages 'title' exists or an
>>>>>>>>>>> INSERT if it
>>>>>>>>>>> does not ?
>>>>>>>>>>>
>>>>>>>>>>> The code I have written does not seem to do the job correctly.
>>>>>>>>>>>
>>>>>>>>>>> $result = mysql_query( "SELECT page FROM pages WHERE title =
>>>>>>>>>>> '" . $title
>>>>>>>>>>> . "';");
>>>>>>>>>>>
>>>>>>>>>>> $row = mysql_fetch_array( $result, MYSQL_ASSOC);
>>>>>>>>>>>
>>>>>>>>>>> if ( isset( $row["title"]) && $row[title] == $title)
>>>>>>>>>>> {
>>>>>>>>>>> $result = mysql_query( "UPDATE pages SET page = '".
>>>>>>>>>>> $page . "' WHERE
>>>>>>>>>>> title = '" .$title . "';");
>>>>>>>>>>> if ($result)
>>>>>>>>>>> mysql_query( "COMMIT;");
>>>>>>>>>>> }
>>>>>>>>>>> else
>>>>>>>>>>> {
>>>>>>>>>>> $result = mysql_query( "INSERT INTO pages SET title ='"
>>>>>>>>>>> . $title .
>>>>>>>>>>> "', page = '" . $page . "';");
>>>>>>>>>>> if ($result)
>>>>>>>>>>> mysql_query( "COMMIT;");
>>>>>>>>>>> };
>>>>>>>>>>>
>>>>>>>>>>> This code is buggy and doing an insert rather than an update.
>>>>>>>>>>> How would you
>>>>>>>>>>> approach this task ?
>>>>>>>>>> It could be done with one easy query, look into ON DUPLICATE
>>>>>>>>>> KEY UPDATE
>>>>>>>>>> syntax, ask in comp.databases.mysql
>>>>>>>>>>
>>>>>>>>>> I need to keep the logic in PHP rather than using SQL.
>>>>>>>>>>
>>>>>>>>>> Aaron
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>> Why? SQL is the correct place to handle this.
>>>>>>>> I need to know whether I have a new page or not and I need
>>>>>>>> concurrency in the PHP to know whether a page is already being
>>>>>>>> editted and I need to keep a log of transactions.
>>>>>>>>
>>>>>>> OK, if you need to find out if you have a new page or not, you've
>>>>>>> got to query the database to see if it exists. But you've also got
>>>>>>> to be careful, because someone could insert the same page between
>>>>>>> the time you query and the time you insert. Sure, it's a small
>>>>>>> period of time, but it can still happen.
>>>>>>>
>>>>>>> And you can't tell for sure if a page is being edited or not. All
>>>>>>> you can really tell is if it has been edited.
>>>>>>>
>>>>>>>> At this stage in my apps life, prototype phase I prefer to keep
>>>>>>>> this llogic in the PHP code.
>>>>>>>>
>>>>>>>> Aaron
>>>>>>>>
>>>>>>>>
>>>>>>> That's doing it the hard way. Use the tools available to you.
>>>>>> Also for the intended applications programmers who are users of my
>>>>>> app generally do not know SQL but can program in an average
>>>>>> programming language.
>>>>>>
>>>>>> I would like to look at and learn the SQL but not now. I really want
>>>>>> to keep this thing simple at the moment and SQL is not simple
>>>>>> compared to PHP.
>>>>>>
>>>>>> Aaron
>>>>>>
>>>>>>
>>>>>>
>>>>> SQL is much simpler than PHP!
>>>> Its neater and more integral, but I would not say its simpler :)
>>>>
>>>> Aaron
>>>>
>>>>
>>>>
>>> Definitely much simpler. A very limited command set with limited
>>> options. We teach SQL in about a day and a half, as part of our database
>>> courses. PHP alone is 5 days - and I wish we had more time.
>>>
>>> If you're going to be using relational databases, you need to learn SQL.
>>>
>>> But your users don't need to use SQL. I have several sites which use
>>> SQL databases. And none of my customers have to write a single line of
>>> SQL.
>>
>> I am working on an open framework for use by other programmers, end users
>> of the product will not need internal knowledge but application
>> programmers will.
>>
>> Proper relational database programming cannot be taught in less than a
>> week, more like a year if you want to follow Codd.
>>
>> Aaron
>>
>>
>>
>
> I forgot to add. If you're using a SQL database, then the programmers
> will need to know SQL, also. But any decent PHP programmer should have a
> reasonable knowledge of SQL, anyway.
>
> Or, just forget about the database and use flat files. Problem solved.

This thread is a bit OTT for the original question :)

Hopefully theres a hacker at hand to save the day. Otherwise I have to do
some more reading of PHP's ins and outs.

Aaron

Re: Determining whether to INSERT or UPDATE

On Jan 1, 4:50 pm, "Aaron Gray" wrote:
>
> I have a MySQL table of pages each with a 'title' and 'page'
> fields.
>
> How do I do either an UPDATE if the pages 'title' exists or
> an INSERT if it does not ?

Assuming that `title` is a primary or unique key, it's very simple:

INSERT INTO `pages` (`title`, `page`)
VALUES ('My Title', 'My Page')
ON DUPLICATE KEY UPDATE `page`='My Page'

See MySQL documentation for more information:

http://dev.mysql.com/doc/refman/4.1/en/insert.html

Alternatively, you can issue a REPLACE query:

http://dev.mysql.com/doc/refman/4.1/en/replace.html

The difference is that with INSERT ... ON DUPLICATE KEY UPDATE, you
can update an existing record partially (i.e., if there are fields
other than `title` and `page`, they will be kept intact). REPLACE,
however, deletes an existing row (if it exists, that is) before
writing a new one, so any fields not explicitly set by the REPLACE
query will be set to their default values.

Cheers,
NC

Re: Determining whether to INSERT or UPDATE

"NC" wrote in message
news:292ed4c8-99da-427c-9685-6969a9e62783@e10g2000prf.google groups.com...
> On Jan 1, 4:50 pm, "Aaron Gray" wrote:
>>
>> I have a MySQL table of pages each with a 'title' and 'page'
>> fields.
>>
>> How do I do either an UPDATE if the pages 'title' exists or
>> an INSERT if it does not ?
>
> Assuming that `title` is a primary or unique key, it's very simple:
>
> INSERT INTO `pages` (`title`, `page`)
> VALUES ('My Title', 'My Page')
> ON DUPLICATE KEY UPDATE `page`='My Page'
>
> See MySQL documentation for more information:
>
> http://dev.mysql.com/doc/refman/4.1/en/insert.html
>
> Alternatively, you can issue a REPLACE query:
>
> http://dev.mysql.com/doc/refman/4.1/en/replace.html
>
> The difference is that with INSERT ... ON DUPLICATE KEY UPDATE, you
> can update an existing record partially (i.e., if there are fields
> other than `title` and `page`, they will be kept intact). REPLACE,
> however, deletes an existing row (if it exists, that is) before
> writing a new one, so any fields not explicitly set by the REPLACE
> query will be set to their default values.

Great thanks. I'll play with that.

I do need to know whether it was an existin record or a new one and have
concurrency. PHP does seem like better logic for this problem.

Aaron

Re: Determining whether to INSERT or UPDATE

Aaron Gray wrote:
> "NC" wrote in message
> news:292ed4c8-99da-427c-9685-6969a9e62783@e10g2000prf.google groups.com...
> I do need to know whether it was an existin record or a new one and
> have concurrency. PHP does seem like better logic for this problem.

Well obviously you know best. Isn't it annoying that when you ask for help
all us experts give you the correct answer and you have to keep ignoring us.

Here's a thought, next time don't bother to ask for advice that you aren't
going to take. It'll save everyone's time.

Re: Determining whether to INSERT or UPDATE

"Paul Lautman" wrote in message
news:5u1c11F1fc5l6U1@mid.individual.net...
> Aaron Gray wrote:
>> "NC" wrote in message
>> news:292ed4c8-99da-427c-9685-6969a9e62783@e10g2000prf.google groups.com...
>> I do need to know whether it was an existin record or a new one and
>> have concurrency. PHP does seem like better logic for this problem.
>
> Well obviously you know best. Isn't it annoying that when you ask for help
> all us experts give you the correct answer and you have to keep ignoring
> us.

I dont take this lightly, SQL may be the best way in the long run but for
the initial prototype it is not, I have thought about it and PHP is the best
solution at this time for the requirements I have.

> Here's a thought, next time don't bother to ask for advice that you aren't
> going to take. It'll save everyone's time.

Sorry, I do take advice, but as I have not given full details of what I
require and the requirments was to just clean up some PHP code on a PHP
newsgroup it would be helpful if some nice person would help me with that.

I have now looked at the PHP side of things and AFAICS all that was required
was the 'mysql_num_rows()' function.

Thanks for your feedback, when I need scalable code I will move things over
to the SQL side if that turns out the way to approach it. At the moment I
just want a single PHP file program that is not too complex and has all or
most of the logic encapsulated.

Cheers,

Aaron

Re: Determining whether to INSERT or UPDATE

Aaron Gray wrote:
> "Paul Lautman" wrote in message
> news:5u1c11F1fc5l6U1@mid.individual.net...
>> Aaron Gray wrote:
>>> "NC" wrote in message
>>> news:292ed4c8-99da-427c-9685-6969a9e62783@e10g2000prf.google groups.com...
>>> I do need to know whether it was an existin record or a new one and
>>> have concurrency. PHP does seem like better logic for this problem.
>> Well obviously you know best. Isn't it annoying that when you ask for help
>> all us experts give you the correct answer and you have to keep ignoring
>> us.
>
> I dont take this lightly, SQL may be the best way in the long run but for
> the initial prototype it is not, I have thought about it and PHP is the best
> solution at this time for the requirements I have.
>
>> Here's a thought, next time don't bother to ask for advice that you aren't
>> going to take. It'll save everyone's time.
>
> Sorry, I do take advice, but as I have not given full details of what I
> require and the requirments was to just clean up some PHP code on a PHP
> newsgroup it would be helpful if some nice person would help me with that.
>
> I have now looked at the PHP side of things and AFAICS all that was required
> was the 'mysql_num_rows()' function.
>

Thats what I generally check for myself.


> Thanks for your feedback, when I need scalable code I will move things over
> to the SQL side if that turns out the way to approach it. At the moment I
> just want a single PHP file program that is not too complex and has all or
> most of the logic encapsulated.
>

Yup. I agree.
> Cheers,
>
> Aaron
>
>