Which will be better/faster, selecting all or selecting one several times? Or...

[Jessica]

I have to go through a large group of addresses and phone numbers, and see if there are any database records that match (either or). If not, I insert a new record.

 

The two ways I came up with to do it are :

select all of the records, and use php to loop through the new records and see if they exist in the selected records. Then if they don't, insert.

Loop through the new results and select where the address or phone match, if there is no result, insert.

 

Which of these is a better way to approach it, or is there a third option that is even better?

 

Thanks

[p2grace]

Couldn't you just do a select statement matching either the address or the phone number and count how many rows are returned?  If there aren't any rows returned then it isn't in the database.

[Jessica]

That is what I said...That is the second option I listed.

 

But I have to do that for a list of about 100 possible phone numbers, every few minutes, and search a table of several hundred records. So I am asking which way is faster when dealing with this much data.

[DyslexicDog]

Why not just make the column UNIQUE in the database?

[p2grace]

ah I see I misread your second option.  Hmm very good question, I'd probably have to vote option 1 that way you're not putting so much work on the database.  But I'm anxious to hear what others think.

 

Wow DyslexicDog that's a really clever idea too.

[Jessica]

Which column?

Also, after I find out if it already exists, I need to get the uniqueID for that record, and insert it with another record in another table. (The other table is "Cars" and each car has a "Dealer". But a dealer can have many cars, so I need to find out if this is a new dealer or an existing one before I insert the car.)

[nikefido]

Which column?

Also, after I find out if it already exists, I need to get the uniqueID for that record, and insert it with another record in another table. (The other table is "Cars" and each car has a "Dealer". But a dealer can have many cars, so I need to find out if this is a new dealer or an existing one before I insert the car.)

 

make the address and phone numbres unique - they can never repeat in the table.

 

In this way, you can use one sql statement and not have to run through all of its results - if it returns an error saying it can't be used bc it already exists, then you know that it already exist and not to insert it.

 

This removes your testing script - you can skip right to trying to add the data!

 

=========

If you get an error, you can then use another query to to match phone/address and get its unique ID. If, however, the record inserts as it should, you can use mysql_insert_id($link) ( http://us.php.net/mysql_insert_id ) to get the new items uniqueID (assuming it's ID is auto-incremented)

[Jessica]

I don't think that will work for an OR, just AND. I want EITHER the phone or the address to match. So the phone number could repeat if the address is different.

 

Also, if I try to insert it and get an error, I have to do a select to get the ID to insert in my other record, yes?

[nikefido]

I don't think that will work for an OR, just AND. I want EITHER the phone or the address to match. So the phone number could repeat if the address is different.

 

edit: then only make the address field unique and only search for the address to match.

SQL might allow you to match one or the other (but not both) - i'm not sure.

 

Also, if I try to insert it and get an error, I have to do a select to get the ID to insert in my other record, yes?

 

correct (to my knowledge). I think that's part of what I tried to say above.

 

 

[Jessica]

No, I know how to use mysql_insert_id, I am saying if it already exists, I have to do another select to get the ID of the already existing row.

 

I guess I will just write it both ways and run unit testing, nevermind.

[nikefido]

No, I know how to use mysql_insert_id, I am saying if it already exists, I have to do another select to get the ID of the already existing row.

 

I guess I will just write it both ways and run unit testing, nevermind.

 

yep, we are saying the same thing

[Barand]

something like

<?php


$sql = "SELECT id FROM dealer WHERE address='$addy' OR phone='$phone'";
$res = mysql_query($sql);
if ($row = mysql_fetch_row($res)) {
    // exists
    $dealerid = $row[0];
}
else {
    mysql_query("INSERT INTO dealer (address, phone) VALUES ('$addy', '$phone')");
    $dealerid = mysql_insert_id();
}
mysql_query("INSERT INTO car (carstuff, dealer) VALUES ('$carstuff', $dealerid)");
?> 

[nikefido]

i figured SQL would take care of it

 

Good info.

[Jessica]

Barand, so you are saying that way is FASTER than just selecting all of them at the start and checking if the new one is in the list in PHP?

[Barand]

Put an INDEX on each of the fields in the WHERE clause.

 

Next, time both options with microtime(). That's the best way to find out.