[Frunkie]

Hello all,
(aschk, hope you're close by..)

I have table A B and C that require transactions. I am running into some problems because of this. In my script, I have autocommit set to 0 because of logic I have that needs to be cleared first then the script issues the COMMIT.

autocommit=0
begin
mysql_query(insert into A values(....));
mysql_query(insert into B values(....));
mysql_query(insert into C values(....));

The above is not valid as mysql_query will not allow multiple queries as I just found out on php's site. I cannot get the last_id() from table A to insert into table B because the COMMIT has not yet been issued on A. Table A is still in a state of limbo. What I am getting is a return of false (0).

Any idea how to handle multiple inserts with transactions?

[aschk]

Definitely an interesting question. Let me do some tests and i'll get back to you. I'll update questions here along the way.

1) What table type are you using? (this may seem like a simple question but always worth checking).

[Frunkie]

Originally Posted by aschk

Definitely an interesting question. Let me do some tests and i'll get back to you. I'll update questions here along the way.

1) What table type are you using? (this may seem like a simple question but always worth checking).

Aschk,

thanks for the hand.. I am using innodb.

[Frunkie]

Aschk,

I have a bit of a design issue that I have been tossing around for the past couple of days. Let me run this by you and see what you think with regard to php.

I have a table that holds data on people that call in to a dispatch center. This person gives their name address and phone number and details of their service requests.

I started coding my insert and update forms and remembered that I set constraints on the table to cut back on duplicate data.

If I have say for example 2 Andy Browns in the table, I was thinking about using a while loop to iterate through the data and if the loop finds a match, use that record else insert the data that the user has entered.

This way, I don't have to code additional boxes for the results. Do you see what I want to do? Its still a bit fuzzy but I think the concept is doable.

What do you think? Any suggestions?

PHP Code:

//connection stuff
while(bla bla...
if($data['first_Name'] && $data['last_Name'] && $data['address1'])
{
    $_POST['rp_Id'] = $res['rp'];
    INSERT STATEMENT HERE
    COMMIT
}
else
{
    INSERT STATEMENT HERE
    COMMIT
} 


[aschk]

Okey dokey, i put together a test script so i could test the problem with autocommit and rollback/transactions. See the following :

<?php
$conn = mysql_connect('localhost','username','password');
if(!mysql_select_db('blog')){
	echo "no db";
}
if(mysql_query('SET AUTOCOMMIT=0',$conn)){
	echo "Auto Commit set to what i want";
}

$result = mysql_query("SELECT * FROM albums",$conn);
while($row = mysql_fetch_assoc($result)){
	
	//print_r($row);
	
}

mysql_query("BEGIN");

$album = array("new","newer","newest");

for($i=0;$i<3;$i++){
	$sql = sprintf("INSERT INTO albums(title) VALUES('%s')",$album[$i]);
	if(mysql_query($sql)){
		
		echo "INSERTED{$i}".mysql_insert_id($conn)."<br/>";
	}
}

mysql_query("ROLLBACK");
?>

What's interesting here is that I have started a transaction, (with BEGIN and autocommit off) inserted a bunch of stuff into a table (called albums) and retrieved the ID after each insert and echoed it out, before then rolling back. Seems to be ok. The ID returned is from an auto_increment column, which I will assume you have in your table in question.
I will advance this further and do cross table inserts...

p.s. what version of PHP and MySQL are you using?

[aschk]

In reply to your previous post regarding determining whether a user exists, and updating their information based on an existing record. There is definitely nothing wrong with utilising and implementing this idea, just beware of "business" situations where people will want a record of a person's details changing over the course of time.

If you don't mind having an "up-to-date" record of the user and couldn't care less about what their information was previously then by all means do a lookup, if they exist, update their information instead of adding a new user. As you say this will stop duplicates.

Can I guess that this might be for some sort of sign-up application (e.g. newletters)?

[aschk]

Ooh, just re-read your post again, *puts in eyes*
I think I realise what you're saying now.

If their record exists, use it, else, add another.
Now, DON'T loop through the dataset, it's bad bad bad. 1 Million records, and you're going to be waiting.
Do a SELECT based on supplied information.

i.e. SELECT * FROM users WHERE username='something' AND password='somethingelse'

If you get a record back (i.e. mysql_num_rows == 1) then use the returned information from your SELECT statement, else INSERT what you were just given.

[Frunkie]

Aschk,

I am on version 5.45 or thereabouts. It was the latest release as of last week..

Thanks for the code.. Yes, I am using auto-incrementing columns in these tables.

Let me work your code into my UI and see what I can come up with.. brb.

[aschk]

Further : the only problem I can forsee here is that you're working with information that can be duplicate. What you're using is first_name, last_name, etc.
It occurs to me that this can be non-unique, i.e. there could be 30 John Smiths, which will obviously cause you a problem. Which one are you going to use?

Thus it would seem logical to apply a unique username, and/or password to this process. Is that something which is going to fit in with what you're doing? Are these "service request" coming from authentic users or "Joe Blogg" 's?

[Frunkie]

Originally Posted by aschk

Ooh, just re-read your post again, *puts in eyes*
I think I realise what you're saying now.

If their record exists, use it, else, add another.
Now, DON'T loop through the dataset, it's bad bad bad. 1 Million records, and you're going to be waiting.
Do a SELECT based on supplied information.

i.e. SELECT * FROM users WHERE username='something' AND password='somethingelse'

If you get a record back (i.e. mysql_num_rows == 1) then use the returned information from your SELECT statement, else INSERT what you were just given.

Exactly.. I was going to use while with an sql condition on first_Name, last_Name and address1. That loop shouldn't take any time at all. Agreed?

Exactly.. I was going to use num_rows for that but what if num_rows=2? or more? I'm still hammering this out..

[georgev]

Originally Posted by fjm1967

If I have say for example 2 Andy Browns in the table, I was thinking about using a while loop to iterate through the data and if the loop finds a match, use that record else insert the data that the user has entered.

There's no need for a loop...

SELECT name
     , Count(name) As [count]
FROM   people
WHERE  name = 'Andy Brown'
GROUP
    BY name
HAVING Count(name) > 1

Run the above query and see what I mean (eliminate the WHERE clause to see the results for all names).

UPDATE: Note that this query shows if there is a duplicate already... You probably want to just run a simple WHERE name = .... and see if a result is returned or not.

[Frunkie]

Originally Posted by aschk

Further : the only problem I can forsee here is that you're working with information that can be duplicate. What you're using is first_name, last_name, etc.
It occurs to me that this can be non-unique, i.e. there could be 30 John Smiths, which will obviously cause you a problem. Which one are you going to use?

Thus it would seem logical to apply a unique username, and/or password to this process. Is that something which is going to fit in with what you're doing? Are these "service request" coming from authentic users or "Joe Blogg" 's?

Well, the issue is that anybody really can call into this dispatch center and give any name they want really.. Honestly, I am thinking about it and even if I had 35 or even 100 Andy Browns in that table, I don't see where it would be a big deal really; unless I am totally missing something. I would ideally want as little duplicate rows as I could have but I am trying to also be realistic as to what that table represents. It is only really used for a callback number and a name..

I do however have another table in the db where I need to ensure that there are almost no duplicate values but I think this table is going to be ok..

I would still like to use an if/else to commit the data found or inserted by the user. That seems like a pretty good measure and at least a good effort on my part.

[Frunkie]

Originally Posted by georgev

There's no need for a loop...

SELECT name
     , Count(name) As [count]
FROM   people
WHERE  name = 'Andy Brown'
GROUP
    BY name
HAVING Count(name) > 1

Run the above query and see what I mean (eliminate the WHERE clause to see the results for all names).

UPDATE: Note that this query shows if there is a duplicate already... You probably want to just run a simple WHERE name = .... and see if a result is returned or not.

Hey Georgie, thanks for that... Aschk.. what do you think? My php skills are better than my sql skills.

[Frunkie]

You guys have given me some terriffic ideas here.. I'm still not quite sure how to impliment this. George's idea is also great and would be the least painful for sure.

Aschk, how can I implement this?

1. user enters caller data into form
2. db queries db for duplicates
3. BEGIN
4. if/else? or Georges sql?

I'm having a problem putting this together.

EDIT: Can we impliment Georges idea and get rid of the php loop?

[aschk]

It seems to be that you have your uniqueness decided for you.
If the first & last & address matches, then use that user, else add another one. Basically, based on this criteria, mysql_num_rows can NEVER be greater than 1... and if it is, then you know you have a problem, and that a duplicate record has been added previously.