Posted by Aaron Gray on 01/02/08 03:25

"Jerry Stuckle" <jstucklex@attglobal.net> wrote in message
news:JIGdnZeODo3Pn-banZ2dnUVZ_rrinZ2d@comcast.com...
> Aaron Gray wrote:
>> "Jerry Stuckle" <jstucklex@attglobal.net> wrote in message
>> news:RbadnWLvEvb4ZOfanZ2dnUVZ_vXinZ2d@comcast.com...
>>> Aaron Gray wrote:
>>>> "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
>>>>
>>>>
>>>>
>>> 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

[Back to original message]