Checking if a record exists in a database.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
screevo
Forum Commoner
Posts: 25
Joined: Fri Aug 25, 2006 12:04 am

Checking if a record exists in a database.

Post by screevo »

Ok. I have this long piece of code I've been writing, viewable at viewtopic.php?p=302566.

In there, there is a portion where I am submitting data from a form with the fields "id", "link", "linkimage", "title", "content."

Then, I am calling a function that should check to see if a row in the database exists with that ID. IF the row exists, update it with the new information. If it does NOT exist, create a row with that information. This is what I have:

Code: Select all

openDatabaseScreevo();
    $id = mysql_real_escape_string($_POST["id"]);
    $title = mysql_real_escape_string($_POST["title"]);
    $link = mysql_real_escape_string($_POST["link"]);
    $linkimage = mysql_real_escape_string($_POST["linkimage"]);
    $content = mysql_real_escape_string($_POST['content']);
    $result = mysql_query("SELECT id AS 'id2' FROM pages WHERE id = '$id'");
    if (mysql_num_rows($result) == 0) { // If the ID number doesnt exist, make a new one
        $result2 = mysql_query("INSERT INTO pages set id='$id', title='$title', content='$content',link='$link', linkimage='$linkimage'");
        if (!$result2) {
            echo "FAILED! <br>";
            echo mysql_error();
        } else {
            echo 'You have successfully created page number ' . $id . ' called ' . $title . '. To update more, please go back.';
            mysql_close();
        } 
    } else { // if it does, update it.
        $result2 = mysql_query("UPDATE pages SET content = '$content', title = '$title', link = '$link', linkimage = '$linkimage' WHERE id = '$id'");
        if (!$result2) {
            echo 'FAILED!';
            echo mysql_error();
        } else {
            echo 'You have successfully updated page number ' . $id . ' called ' . $title . '. To update more, please go back.';
            mysql_close();
        } 
    }
}
No matter what, though, it's going to UPDATE, which works with existing data, but obviously won't create new data.

What am I doing wrong?

SM
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post by Oren »

screevo
Forum Commoner
Posts: 25
Joined: Fri Aug 25, 2006 12:04 am

Post by screevo »

Oren wrote:You may find this useful: http://dev.mysql.com/doc/refman/5.0/en/replace.html
See, i KNEW something had to exist to do that... I just had no idea how to properly word the google search.

Thanks.

I did manage to get it to work the way I have it above. I just didn't realize that when I was uploading it, I was uploading it to a different directory... Once I caught the mistake...
Last edited by screevo on Sat Aug 26, 2006 5:52 pm, edited 1 time in total.
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post by Oren »

May I ask why the id is coming from $_POST['id']?
screevo
Forum Commoner
Posts: 25
Joined: Fri Aug 25, 2006 12:04 am

Post by screevo »

Because id is submitted via a form.

My functions for creating a new section and updating a section both use the same preview function. So when the preview function passes it on to THAT function (yes, I know, probably a much simpler way of doing it...), this function is to check if it exists, and insert or update as necesary.

That MySQL function you mention above will work much nicer. I'll just have to change how my database is set up.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

The other solution is also somewhere in the mysql manual (keywords: on duplicate key)
screevo
Forum Commoner
Posts: 25
Joined: Fri Aug 25, 2006 12:04 am

Post by screevo »

timvw wrote:The other solution is also somewhere in the mysql manual (keywords: on duplicate key)
Yeah, I was just reading up on that one too.

As I understand it, REPLACE is a command specific to MySQL, but ON DUPLICATE KEY is not?

SM
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Afaik on duplicate key is a mysql extension too..

(I guess we're forced to vendor specific dialect anyway with sql products...)
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post by Oren »

screevo wrote:As I understand it, REPLACE is a command specific to MySQL, but ON DUPLICATE KEY is not?
As far as I know, REPLACE is specific to MySQL. About the ON DUPLICATE KEY - I have no idea.
screevo
Forum Commoner
Posts: 25
Joined: Fri Aug 25, 2006 12:04 am

Post by screevo »

timvw wrote:Afaik on duplicate key is a mysql extension too..

(I guess we're forced to vendor specific dialect anyway with sql products...)
Hm. Every time I show my code to people, there's always a bunch who maintain that my code should be able to be put on any combination of PHP and a SQL server and work just fine.

I still don't know why I should care if my code works on PHP4.1 with PostgreSQL, if I'm going to be using it on PHP5 with MySQL!
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

- Sometimes an API to access the database changes.. So that might be a reason to use an extra layer to avoid problems with changes in the API.. (as in transparantly replacing mysql_* with mysqli_*)

- You might decide to use only the 'Standard'QL that a SQL dbms offers but in that case you give up on a lot of useful extensions... Imagine living without AUTO_INCREMENT in a MySQL database ;) As long as you don't have to support multiple sql dbms i find it silly to think about compatibility with other dbmses out there. When you're into agile development you also only write code to make the test pass (and don't waste time on things that aren't required at that time)

(Given all those SQL dialects i think SQL stands for Slang Query Language :p)
screevo
Forum Commoner
Posts: 25
Joined: Fri Aug 25, 2006 12:04 am

Post by screevo »

Yeah. I got a TON of people telling me I was an idiot for assuming that Magic_quotes was on when i wrote my code (Because it was.)

So I definitively turned it off, and did everything with mysql_real_escape_string.

Then I got the same people saying how dare I write code without taking into account the possibility that Magic Quotes is on.

I just can't win.

All in all, I think my entire project has turned out pretty well. I linked to it in the OP. I'd appreciate any comments on it and how I can do better.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

For comments and such, post a thread in UI Design/Usability and ask for comments.

As for your code, it can use a little work, but if you got things working as they are, move on to other stages of learning. You will have time to come back to this and see what the experienced developers are seeing. Then you can have that moment that many of us have had here...

I did that? I cannot believe I used to code that way...
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

screevo wrote: So I definitively turned it off, and did everything with mysql_real_escape_string.
Good call ;)
screevo wrote: Then I got the same people saying how dare I write code without taking into account the possibility that Magic Quotes is on.
Who wants to develop for people that turn magic quotes on anyway? All it does is cripple your data...

There is no such magic_* for use in a HTML context... Do they complain about that? No. They simply make sure that htmlentities is applied to all output and get over with it...

But don't you dare to suggest that htmlentities on all the output by default, because that makes them furious (despite the fact that xss vulnerabiliites can affect the whole world)... On the other hand they do want that same crippling effect for MySQL (where it can only affect your own data).
Post Reply