Posted by Aaron Gray on 01/02/08 02:32

"Jerry Stuckle" <jstucklex@attglobal.net> wrote in message
news:65-dnQNSmtoCaefanZ2dnUVZ_r-vnZ2d@comcast.com...
> Aaron Gray wrote:
>> "Jerry Stuckle" <jstucklex@attglobal.net> wrote in message
>> news:m_Wdnblb4PlxcufanZ2dnUVZ_gWdnZ2d@comcast.com...
>>> Aaron Gray wrote:
>>>> "Rik Wasmus" <luiheidsgoeroe@hotmail.com> wrote in message
>>>> news:op.t392n4xa5bnjuv@metallium.lan...
>>>> On Wed, 02 Jan 2008 01:50:20 +0100, Aaron Gray <ang.usenet@gmail.com>
>>>> 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

[Back to original message]