Is this the best way to handle this? 1

[sd0t1]

I have a court case number, complaint number and court division I need to check if exist in table. If it does exist, I need to retrieve the case_id.

If it does not exist, I need to insert the three fields (generating an autonumber case_id) and retrieve the case_id

This is what I came up with, it works, but I can't help but to think there may be a function I'm not aware of.

$sql5 = "SELECT case_id FROM crt_case WHERE case_no = '$case_no' AND cpt_no = '$cpt_no' AND crt_div = '$division'";
$result5 = mysql_query($sql5) or die(mysql_error());
if (mysql_num_rows($result5)!= 0)
{
$row5 = mysql_fetch_assoc($result5);
$case_id = $row5['case_id'];
}
else
{
$sql3 = "INSERT INTO crt_case (case_id, case_no, cpt_no, crt_div) VALUES ('', '$case_no', '$cpt_no', '$division')";
$result3 = mysql_query($sql3) or die(mysql_error());
$case_id = mysql_insert_id();
}

 

[jpadie]

that's interesting: i have built and use (in day to day life) a case management system for my legal practice. i'd be interested to know what you're up to and whether there are any synergies to exploit.

let's assume that you have a unique index on those three fields (together, of course), and a primary index on the case id field.

  $sql3 = "REPLACE INTO crt_case (case_id, case_no, cpt_no, crt_div) VALUES (NULL, '" .mysql_real_escape_string($case_no) . "', '".mysql_real_escape_string($cpt_no) ."', '".mysql_real_escape_string($division)."')";
    $result3 = mysql_query($sql3) or die(mysql_error());
    $case_id = mysql_insert_id();
    if ($case_id == 0) $existed = true; //

 

[sd0t1]

jpadie, thanks as usual. I tried your code and it's missing one aspect. I probably didn't explain it correctly.
If the case is already in the database, then I need the existing database, if it doesn't exist, then I need to insert it and get that id. ultimately I need to return a case_id each time.

 

[jpadie]

as to the your original question I misread the replace syntax and blurb in the mysql manual. the case_id will always be available (whether it is inserted or replaced, by the mysql_insert_id() return function. this is because replace either inserts or deletes and inserts (so the case_id changes).

but for this to work you must have a unique index set on those three fields.

 

[Noway2]

Does the insert syntax support the "if not exists" construct like creating tables does? That may be one way to approach the problem.

 

[jpadie]

it does, yes or ish. but this will not return the id. the syntax is

insert into table .... on duplicate key update

you can then test for mysql_affected_rows - this will be one if there is NOT already a caseID and 2 otherwise.

 

[alan92rttt]

I think the solution is actually simple.

Reviewing your comments and code it seems that case_id is a system generated key.

If I am correct on these assumptions, the main problem is actually your insert.

$sql3 = "INSERT INTO crt_case (case_id, case_no, cpt_no, crt_div) VALUES ('', '$case_no', '$cpt_no', '$division')";

This command is forcing a empty value into the key.

try this

$sql3 = "INSERT INTO crt_case (case_no, cpt_no, crt_div) VALUES ('$case_no', '$cpt_no', '$division')";

When you are inserting a row you do not need a place holder for the primary key. Once you change the insert the mysql_insert_id function should return the correct key value.

Alan

 

[jpadie]

@Alan
if the case number exists then the insert will fail because it is the primary key.

the task here is:

1. to test whether a record exists that matches three data points.
2. if it does, retrieve the case id.
3. if it does not, create a new record and then retrieve the case id.

what the OP was asking was if there was any neat way of doing this. his way, of course, works just fine but it takes two queries.

the replace method would also work just fine (I think - not tested yet) and takes one query. but the reality is that unless your mysql server is a bottleneck or there is some severe lack of optimisation going on, the speed difference between the two will be insignificant compared to the latency over an internet connection.

 

[sd0t1]

thanks.

 

[IPGuru]

Japadie I may have miss interpredted your explanation of Replace ( considering your record here that is most likly) but i believe you said that the case id would change each time.
That could cause a problem if the case id is being used elsewhere, in another table or for invoicing purposes. in which case i would suggest the check 1st then insert 2 stage process is less likly to cause problems.

 

[jpadie]

IPGuru

i was concerned that the case_id would change each time as mysql interprets a replace as delete + insert. This normally would not be a problem as the case_id is an internal counter only (used perhaps for links etc), whereas the actual court case number (which could equally validly be the primary key) would not be affected.

however some applications would suffer from the primary key of a record being reassigned. Particularly when the primary key is used as a foreign key in other tables. Going the REPLACE route means that you would have to update all connected relationships programmatically. If this is an issue then it seems worthwhile to take the penalty of another query and use the select + insert/update method instead.

thinking it about it some more the insert into ... on duplicate key update will not work in this scenario (as the point of it is to give the programmer the ability to update the duplicate key to another value)