Duplicate database entries

[sac0o01]

I am quite new so I am sure this is an easy fix for some of the experts around here.

 

I am using the canned script below to add urls to the database as text.  The problem is if you update one of the form text boxes it loads all the urls into the database again resulting in a lot of duplicates.

 

My question is, How do I get the form to only post the new changes and not re-post the existing urls?

 

 

<?php
session_start();

   if(isset($_SESSION['userSession']) && !empty($_SESSION['userSession']))
   {
        include_once("dbc.php");

        if($_POST)
        {
            $c = 0;
            
            $errMssg = "";

            for($i=0;$i<count($_POST['url']);$i++)
            {
                if($_POST['url'][$i]=="")
                {
                    $c++;
                }
            }
            if($c==5)
            {
                $errMssg = "Submission error . Please fill at least 1 url.";
            }
            else
            {
                for($j=0;$j<count($_POST['url']);$j++)
                {
                    if(!empty($_POST['url'][$j]))
                    {
                        $sql = mysql_query("INSERT INTO images (id ,url ,user_id)VALUES (NULL , '".$_POST['url'][$j]."',".$_SESSION['userId'].")");    

                     }
                                    
                }
                
            }
        }    

        
        

        $sqlresult = mysql_query("SELECT * FROM images WHERE user_id =".$_SESSION['userId']);
        
        $count = 0;
            
        while($data = mysql_fetch_array($sqlresult))
        {
            $image[$count] = $data['url'];

            $count++;

        }
         
?>

[xyph]

Hey man! I'm going to help you get to the bottom of this - but only if you're patient. We're going to solve your problem, and hopefully I will teach you skills that will help you solve, or isolate, future problems on your own.

 

Now, the first thing I like to do when running into a problem like this is to simply output (echo) my queries instead of executing them. Try doing this. Does the script output the same query twice? Okay, there's probably a looking issue.

 

Let me know what your result is and we'll move on to isolating the issue further

[gizmola]

2 Steps required here:

 

1.  Add a unique index on url, user_id.  This assumes that it should be ok for 2 different users to add the same exact url.  If you want url's to be completely unique across the table, then you would only define the index on url.  You can do this using command line mysql or phpMyAdmin.  Once the index is created you will no longer be able to insert duplicate url rows.

 

CREATE UNIQUE INDEX `images_url_idx` ON images (url, user_id);

 

2. Change the insert statement so that instead of:

 

INSERT INTO images (....

 

It starts with:

 

INSERT IGNORE INTO images (....

 

Now duplicate row insertions will simply be ignored rather than coming back with a constraint error, although you could also rewrite the code to deal with the error.  The ignore should work a little smoother given the existing code.

 

[sac0o01]

Gizmola, it worked like a charm.  No more duplicate entries.  Now if I may ask one last thing...

 

How would I go about updating the database if a user entered a new url?

 

I tried using another "if" statement with "isset" and the "UPDATE" command for the sql query.  Perhaps "isset" is not correct???

[gizmola]

I don't understand the question.  My assumption is that people can and do add new url's by adding them to the list?  With only the back end of the code to look at, what it does is take a list of url's and insert them one after another, even if that list was populated.  The way the database is setup, there is one row per user/url combination.  I don't know why you would have an update statement here... there is no reason to ever update a row in this system.

[btherl]

If you want the old list of urls to be replaced with the new list, you can delete all rows matching the user id and then insert all the new rows.  That's the most conceptually simple way to do it, rather than thinking about what got changed.

[sac0o01]

Gizmola, it is for an image rotator.  The script grabs the urls from the database and randomly returns one of them.  This page is the interface where users can enter the urls they want included but I need a way for users to change the urls as well.

 

I tried using "REPLACE" instead of insert but it does not overwrite the urls, it simply adds new ones (although it does not make duplicates now)  I am thinking this is more of a database problem than php problem.

 

Or maybe there is just a better way to approach this?

[gizmola]

Gizmola, it is for an image rotator.  The script grabs the urls from the database and randomly returns one of them.  This page is the interface where users can enter the urls they want included but I need a way for users to change the urls as well.

 

I tried using "REPLACE" instead of insert but it does not overwrite the urls, it simply adds new ones (although it does not make duplicates now)  I am thinking this is more of a database problem than php problem.

 

Or maybe there is just a better way to approach this?

 

It's not a database issue... it's a User Interface/design approach problem.  You probably want a seperate form that lists all the rows in a table and lets you alter url's or indicate rows you want to delete.  The updates need to be handled via an UPDATE statement, and Deletes via DELETE, in each case specifying the id of the corresponding row.