Help Optimizing Funciton

[tweaky]

I have a function I developed to work with mySQL to check to see if an item already exists in a table (if it does, return the ID. If it doesn't, insert and return the new ID) The "Main" table contains only the ID number to each of the items in other tables.. IE..

Table: Main -> Fields: ID, Name, IP
Table: Name -> Fields: ID, Name
Table: IP -> Fields: ID, IP

I want to know if this is the fastest way to do this, or if there is a better way to do this because I have 1,000's of lines that I am about to use this with (it works great for smaller #'s tho)

Thanks for your time.

function check_tbl($table,$id_field,$field,$data){
	$query = "SELECT * FROM $database_name.$table WHERE $database_name.$table.$field = '$data'";
	$firstquery = mysql_query($query);
	$firstresult = mysql_fetch_array($firstquery);
	if ($firstresult[1]<> $data){
		$query2 = "INSERT INTO $database_name.$table ($database_name.$table.$field) VALUES ('$data')";
		$secondquery = mysql_query($query2);
		return mysql_insert_id();
	} else {
		return $firstresult[0];
	}
}

[sneakyimp]

Within that function, it seems pretty efficient to me. I can't really think of anything that would speed it up.

However, in the broader context of your application, if you're going to call this function thousands of times, you'll be doing thousands of queries which doesn't sound that efficient to me.

There may be a way to take your larger list of ids to check and run a single query to determine which ones exist and which ones need records.s

[tweaky]

That is the exact problem... Many "thousands of times".

[sneakyimp]

Like I said, your one function looks pretty good. I have no idea about the broader context of your overall application.

[bpat1434]

One idea is to just take a more modular approach. Something like:

<?php

class DBCheck {
    var $field_data = array();
    var $where = 'WHERE (';

function prep($field, $data) {
    $this->where .= "$field='$data' OR ";
}

function check_tbl($table) {
    $where = substr($this->where, 0, -4).')';
    $query = "SELECT * FROM $database_name.$table $where";
    $rslt = mysql_query($query);
    if($rslt){
        return mysql_fetch_assoc($rslt);
    }
    else
        return false;
}
}

Now, it's just a matter of doing:

$chck = new DBCheck;
$chck->prep_check('name', 'Jane');
$chck->prep_check('name', 'Blah');
$found = $chck->check_tbl();

Then it's a matter of differencing the found results (in an array) against an array of all possibilities. Then you use a class (like above) to create a set of INSERT statements to be executed at once, or like an SQL file would be.

Heck, you could create one class to do it all and just call the function as needed 😉 It's totally possible....

[NogDog]

If the ID column is defined as unique or as the primary key, then there's no need to check if it exists. Simply do the sql insert, and if the ID already exists then the insert will fail. You can check for the specific mysql error code (1062) to see if that's why the query failed should you want to output an applicable error message when that occurs.

[sneakyimp]

I'm still concerned about the main loop that will run this query potentially thousands of times. Might there be one way to gather up all the ids in question and then run ONE query to find which exist in the dbase and which don't?

[bpat1434]

SELECT * FROM table_name WHERE id IN (id, id2, id3, id4, id4, idn);

That's the query you'd use to do what you want.

[tweaky]

Let me give you a little background on what this is being used for. I process a whole bunch of game server logs that has many different lines that I am looking for. One of those lines is the players name, GUID, Date, & Time. If I just take that information, I'll have one main table with the fields: ID, GUID_ID, Date_ID, Time_ID, Name_ID
Then there would be the 4 matching tables of GUID, Date, Time, & Name (Each would have the fields: ID and Item_Name

Then I loop through the entie log file. If I find that line I am interested in, I chunk out the information I want and then run it through the above function to get it into the database.

I won't even get into all the PunkBuster kicks I have to deal with 🙁 but that gives you an example of what I will be doing)