#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    34
    Rep Power
    5

    Maximum Number of Inserts per one transaction


    I have a page that inserts a row into a table based on criteria from two other tables. When I hit the submit button the inserts occur, but then the page stops and it seems it can only insert about 10,000 reocrds at a time. I'm wondering if this is a limit on php/mysql insert per one transaction or a limitation by my hosting provider?

    Here's the code:
    PHP Code:
    $qProfile "SELECT * from leads order by leadid";
    $data mysql_query($qProfile);
    while(
    $row mysql_fetch_array($data))
       {
        
    $id $row['LeadID'];  
        
    $lcstr "select leadcontactid, contactdate from leadcontacts where leadid = $id order by contactdate desc limit 1";
        
    $data2 mysql_query($lcstr);
        
    $row2 mysql_fetch_object($data2);
        
    $totcnt mysql_num_rows($data2);
       if (
    $totcnt == 1) {

        
    $lid $row2->leadcontactid;
        
    $maxdate $row2->contactdate;
        
    $query "INSERT INTO leadlastcontactdate (ID, leadID, leadcontactid, ContactDate)
            VALUES ('', '
    $id', '$lid', '$maxdate')";
                 
    $results mysql_query($query); 
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jul 2003
    Posts
    4,280
    Rep Power
    629
    Your problem can be fixed by using PDO which should be the case anyway. The MySQL extensions have been deprecated for more than a decade and your code will break with the next PHP version. You can insert all the rows with one query using prepared statements and array associations.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    34
    Rep Power
    5
    Originally Posted by gw1500se
    Your problem can be fixed by using PDO which should be the case anyway. The MySQL extensions have been deprecated for more than a decade and your code will break with the next PHP version. You can insert all the rows with one query using prepared statements and array associations.
    Currently, I have no control of upgrading their site using PDO extensions. So I'm assuming it's a limitation of hosting?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jul 2003
    Posts
    4,280
    Rep Power
    629
    PDO is is a standard part of PHP unless you are using some insanely old version.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  8. #5
  9. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,001
    Rep Power
    4084
    Your entire block of PHP code could be replaced with just a single INSERT...SELECT statement:
    Code:
    INSERT INTO leadlastcontactdate (LeadID, LeadContactId, ContactDate)
    SELECT
    	l.LeadID
    	, lc.LeadContactId
    	, lc.ContactDate
    FROM leads l 
    INNER JOIN (
    	SELECT
    		leadid
    		, MAX(ContactDate) as MaxContactDate
    	FROM leadcontacts 
    	GROUP BY
    		leadid
    ) lastcontact
    INNER JOIN leadcontacts lc ON lc.LeadID=l.LeadID AND lc.ContactDate=lastcontact.MaxContactDate
    That will grab the most recent contact for each lead and then insert a row into leadlastcontactdate using the results from the other two tables.

    The sub-query takes care of finding the most recent contact for each lead by finding the MAX() date. Then it joins to the leadcontacts table using that date and lead id to find the details of the contact.

    Comments on this post

    • ManiacDan agrees
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Dec 2004
    Posts
    3,082
    Rep Power
    379
    but to answer your question, there is script timeout issue when running this via a browser. if you had used command line version then you wouldnt have had this problem.
    You can increase the execution time.. or run it via command line (but perhaps kicken's suggestion has solved your problem)

IMN logo majestic logo threadwatch logo seochat tools logo