I am having some trouble inserting into a table.

I want to insert thousands of pieces of data (data1, data2, data3) into a table only if data3 is unique to its column.

I have my table pretty much set up the way I want, with the a unique column already in place. I do not know much about how the table should be set up but I have the basics and it's working for me. My unique column in the table is the key and it's an automatic increment field. I have looked for this info and I haven't found it.

Background... I am intermediate level with PHP and MYSQL but I have several areas where I am very much a newby. I connect to the DB fine, everything is working fine. The only time this will be a problem is if a user goes to the update page a second time, it reupdates all the information over again. This is not cool.

data3 is the server url for binary data matching a *.JPG type wildcard search. The page refetches the data each time to make sure it's up to date. I only want the new findings to make it into the database.

[Edited to change spelling of incriment to increment]

    If you are doing a record by record creation/update script (example, user making a change via a web form) you should look into the MySQL command "REPLACE" . You would set the form with a null value for the identifier when the record is new, otherwise populate the form with the record identifiery. When you process the form, the "REPLACE " command would create a unique identifier and insert new records, and update existing.

    If you are talking about a bulk update, say from a CSV file, you are hosed by your data structure, since each record is unique by virtue of its autoincrement id.

    In this case you will have to compare values for each field of each record.

    $row=mysql_fetch_array(mysql_query("
SELECT count(*) 
FROM mytable 
WHERE 
myfield1=$myvalue1 AND myfield2=$myvalue2 AND 
so on AND 
so on"));
if(!$row[0]){ 
//count==0, found a record
//do my insert
}

    The idea of comparing jpg binary values is so nutty I scarcely know what to say.

      The idea of comparing jpg binary values is so nutty I scarcely know what to say. [/B]

      I'm not comparing the data. I am comparing the server path to the data... i.e. /home3/public_html/user1/content/images/2005_03_26/0001/DCP002223.JPG

      I just wanted to only update it if the file was not already in there, but I think this method is flawed in its most basic design, and is a process which uses system resources which is easily abused. I'm going to rethink the process. I think I may have a different way around it though.

      I truly haven't ever read an overview on how to and not to create the table structure. Any ideas?

        what version of MySQL do you have? if it's >=4.1.0 then you can use ON DUPLICATE KEY UPDATE

          PHP Version 4.3.9

          I'll look into your suggestion. I feel like such a newby with much of the database stuff.

            PHP Version 4.3.9

            that's all very well but i actually asked what version on MySQL you have😉
            and you may want to upgrade PHP to 4.3.11 (just released)

              Originally posted by liquorvicar
              that's all very well but i actually asked what version on MySQL you have😉
              and you may want to upgrade PHP to 4.3.11 (just released)

              Sorry dude, I've got my mind on other stuff. MySQL version is MySQL 4.0.22-standard

                Write a Reply...