SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    derrrp
    Join Date
    Aug 2006
    Location
    earth
    Posts
    923
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Double SQL Insertion

    Here's the code i have, which I've just discovered is faulty where I've labeled it NOT DEPENDABLE.

    PHP Code:
    $sql sprintf("INSERT INTO shop_orders (`builder_id`, `website`, `model_hours`, `model_phone`, `directions`, `direction_details`, `prospect_info`, `single_family`, `th`, `condo`, `villa`, `competitors`, `community`, `close_out`, `demo`, `price_range`, `trailer`, `multiple_offices`, `assistants`, `amenities`, `move_time`, `date`, `report_id`, `magazine`, `billboards`, `radio`, `tv`, `newspaper`, `newspaper_list`, `objections`, `additional_instructions`, `schedule_varies`) VALUES ('%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')",
      
    $id,
      
    $website,
      
    mysql_real_escape_string($model_hours),
      
    mysql_real_escape_string($model_phone),
      
    mysql_real_escape_string($directions),
      
    mysql_real_escape_string($direction_detail),
      
    mysql_real_escape_string($prospect_info),
      
    mysql_real_escape_string($single_family),
      
    mysql_real_escape_string($th),
      
    mysql_real_escape_string($condo),
      
    mysql_real_escape_string($villa),
      
    mysql_real_escape_string($competitors),
      
    mysql_real_escape_string($community),
      
    mysql_real_escape_string($close_out),
      
    mysql_real_escape_string($demo),
      
    mysql_real_escape_string($price_range),
      
    mysql_real_escape_string($trailer),
      
    mysql_real_escape_string($multiple_offices),
      
    mysql_real_escape_string($assistants),
      
    mysql_real_escape_string($amenities),
      
    mysql_real_escape_string($move_time),
      
    mysql_real_escape_string($date),
      
    mysql_real_escape_string($report_id),
      
    mysql_real_escape_string($magazine),
      
    mysql_real_escape_string($billboards),
      
    mysql_real_escape_string($radio),
      
    mysql_real_escape_string($tv),
      
    mysql_real_escape_string($newspaper),
      
    mysql_real_escape_string($newspaper_list),
      
    mysql_real_escape_string($objections),
      
    mysql_real_escape_string($additional_instructions),
      
    $schedule_varies);
      
      
    $insert mysql_query($sql) or die(mysql_error());
      if(
    $insert)
        {
          
    $sql2 "SELECT * FROM shop_orders ";
          
    $result mysql_query($sql2);
          
    $num_results mysql_num_rows($result); //NOT DEPENDABLE
          
          
    if (!empty($sr)) 
          { 
           
    $i=0;
           
    $cnt count($sr);
           while (
    $i $cnt) {
              if (
    $sr[$i]!="") {
                  
    $INSsql sprintf("INSERT INTO sales_reps (`order_id`,`account_id`,`name`,`monday`,`tuesday`,`wednesday`,`thursday`,`friday`, `job_number`, `stage`, `status`) VALUES ('%d','%d','%s','%s','%s','%s','%s','%s', '%s', '%d', '%s')",
            
    $num_results,
            
    12,
            
    $sr[$i],
            
    $srmon[$i],
            
    $srtue[$i],
            
    $srwed[$i],
            
    $srthu[$i],
            
    $srfri[$i],
            
    $job_number,
            
    1,
            
    Unclaimed);
                }
            
    $i++;
          
            
    $insert2 mysql_query($INSsql)  or die(mysql_error());
             }
          } 
    If you notice the order_id insert into SALES_REPS, it needs to be the order_id that is auto-generated in SHOP_ORDERS when that insert is made.

    Mine obviously is wrong since if there's a SHOP_ORDER record deleted, the total number of rows is no longer indicative of the correct order_id.

    How should I correctly populate SALES_REPS[order_id] with the order_id that was just auto-incremented in the SHOP_ORDER table?

    Thanks for any help.
    No, I REALLY dislike having to use Joomla.

  2. #2
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Use mysql_insert_id to get the last inserted records ID

    http://fr3.php.net/manual/en/functio...-insert-id.php
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  3. #3
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Also, never use SELECT * , you seldom if ever want every col returned, and in this example, you only wanted a count which could haev been done in SQL using count.
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming


Bookmarks

Posting Permissions

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