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.
jstu...@attglobal.net
==================
The problems I am having seem to be the PHP side of things not the SQL side.
Aaron
It could be done with one easy query, look into ON DUPLICATE KEY UPDATE
syntax, ask in comp.databases.mysql
--
Rik Wasmus
> "Jerry Stuckle" <jstu...@attglobal.net> wrote in message
The MySQL side could handle this far better then the PHP side though,
certainly when there could be concurrent updates/inserts.
--
Rik Wasmus
Okay, thanks,
Aaron
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
Thats a typo not actual code.
Aaron
I need to keep the logic in PHP rather than using SQL.
Aaron
Why? SQL is the correct place to handle this.
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
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
I am just not that familuar with SQL to take such an approach at this stage.
Aaron
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!
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
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
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
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.
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
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
>
>