Page 1 of 3 123 LastLast
Results 1 to 15 of 39
  1. #1
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

    Unanswered: mysql_last_id() and transactions

    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?

  2. #2
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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).
    Last edited by aschk; 10-22-07 at 05:28.

  3. #3
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote 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.

  4. #4
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    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

    Last edited by Frunkie; 10-22-07 at 06:04.

  5. #5
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Okey dokey, i put together a test script so i could test the problem with autocommit and rollback/transactions. See the following :
    Code:
    <?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?

  6. #6
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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)?

  7. #7
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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.

  8. #8
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    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.

  9. #9
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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?

  10. #10
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote 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..

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote 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...
    Code:
    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.
    Last edited by gvee; 10-22-07 at 06:36.
    George
    Home | Blog

  12. #12
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote 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.

  13. #13
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by georgev
    There's no need for a loop...
    Code:
    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.

  14. #14
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    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?

  15. #15
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •