Page 1 of 1

Help me debug this... mysql_insert_id()

Posted: Thu Jun 29, 2006 1:36 pm
by Benjamin
I'm trying to figure out why this code isn't pulling an insert id. The first query is inserted just fine. All subsequent queries are inserted with an insert_id of 0. It seems like mysql_insert_id() just quit working. It starts with a standard insert...

Code: Select all

$sql_data_array = array('customers_id' => $customer_id,
                          'customers_name' => $order->customer['firstname'] . ' ' . $order->customer['lastname'],
                           // 30 lines of other fields here
                          'currency_value' => $order->info['currency_value']);
  tep_db_perform(TABLE_ORDERS, $sql_data_array); // this calls the query builder
  $insert_id = tep_db_insert_id(); // this is always returning 0
Here are the query builder and database functions...

Code: Select all

function tep_db_query($query, $link = 'db_link') {
    global $$link;
    /////////////////////////////////////////////////////////////////////
    //echo '<div style="border-width: 1px; border-style: solid; border-color: #ff0000;">' . $query . '</div>';

    //if (defined('STORE_DB_TRANSACTIONS') && (STORE_DB_TRANSACTIONS == 'true')) {
    //  error_log('QUERY ' . $query . "\n", 3, STORE_PAGE_PARSE_TIME_LOG);
    // }

    $result = mysql_query($query, $$link) or tep_db_error($query, mysql_errno(), mysql_error());

    //if (defined('STORE_DB_TRANSACTIONS') && (STORE_DB_TRANSACTIONS == 'true')) {
    //   $result_error = mysql_error();
    //   error_log('RESULT ' . $result . ' ' . $result_error . "\n", 3, STORE_PAGE_PARSE_TIME_LOG);
    //}

    return $result;
  }

  function tep_db_input($string) {
    return addslashes($string);
  }

  function tep_db_perform($table, $data, $action = 'insert', $parameters = '', $link = 'db_link') {
    reset($data);
    if ($action == 'insert') {
      $query = 'insert into ' . $table . ' (';
      while (list($columns, ) = each($data)) {
        $query .= $columns . ', ';
      }
      $query = substr($query, 0, -2) . ') values (';
      reset($data);
      while (list(, $value) = each($data)) {
        switch ((string)$value) {
          case 'now()':
            $query .= 'now(), ';
            break;
          case 'null':
            $query .= 'null, ';
            break;
          default:
            $query .= '\'' . tep_db_input($value) . '\', ';
            break;
        }
      }
      $query = substr($query, 0, -2) . ')';
    } elseif ($action == 'update') {
      $query = 'update ' . $table . ' set ';
      while (list($columns, $value) = each($data)) {
        switch ((string)$value) {
          case 'now()':
            $query .= $columns . ' = now(), ';
            break;
          case 'null':
            $query .= $columns .= ' = null, ';
            break;
          default:
            $query .= $columns . ' = \'' . tep_db_input($value) . '\', ';
            break;
        }
      }
      $query = substr($query, 0, -2) . ' where ' . $parameters;
    }

    return tep_db_query($query, $link);
  }

  function tep_db_insert_id() {
    return mysql_insert_id();
  }

Posted: Thu Jun 29, 2006 1:54 pm
by ambivalent
The first thing that comes to mind is that the previous insert isn't to a table with an auto_increment column?

Posted: Thu Jun 29, 2006 1:57 pm
by Benjamin
It is. This concerns me because this code is all over the place in osCommerce. I can pull the id manually, but I don't want to have to fix it all over the place. I'd rather figure out why it "stopped" working.

Posted: Thu Jun 29, 2006 2:11 pm
by AKA Panama Jack
You really should be using the resource link...

Code: Select all

function tep_db_insert_id($link_identifier) { 
    return mysql_insert_id($link_identifier); 
  }
I know PHP claims it is optional but there are times where it cannot find the information especially if you are querying multiple databases or tables with different resource links. It is ALWAYS good practice to use the resource link for all mysql functions that support it.

Posted: Thu Jun 29, 2006 2:18 pm
by daedalus__
anyone notice this:

Code: Select all

$result = mysql_query($query, $$link)
Should it really have two $$?

Posted: Thu Jun 29, 2006 2:34 pm
by Benjamin
Yeah it's called a variable variable. I didn't write this code I am just fixing it. I was able to fix it by running a query to get the order_id but as I already mentioned I don't want to have to fix this in a million places should it crop up again someplace else.

Posted: Thu Jun 29, 2006 2:34 pm
by daedalus__
Just trying to help ^^

Posted: Thu Jun 29, 2006 2:37 pm
by Benjamin
AKA Panama Jack wrote:You really should be using the resource link...

Code: Select all

function tep_db_insert_id($link_identifier) { 
    return mysql_insert_id($link_identifier); 
  }
I added the resource link to the function, hopefully that will prevent it from happening again.

Daedalus- Sorry if that sounded a bit off. I have never used a variable variable before so I didn't know what they were when I first saw one either.