Page 1 of 1

Help selecting a database for mysql_insert_id()

Posted: Mon Dec 05, 2011 3:10 pm
by smartwork
I am working modifying an install of OSCommerce to divide order-related data into one database and catalog-related data into another. I've got just about everything rolling, but one instance of mysql_insert_id() appears to be generating zero. Although it's occurence immediately follows a query on the database I need the value from (which I understand should already have designated the correct database), it still returns zero.

Here is a code snippet along with the functions (which are actually in other files, but presented here for reference). See last two lines which are the basis of my question.

Code: Select all


 function tep_db_insert_id() {
    return mysql_insert_id();
  }



  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_query($query, $link = 'db_link') {
    global $$link;

    if (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 (STORE_DB_TRANSACTIONS == 'true') {
       $result_error = mysql_error();
       error_log('RESULT ' . $result . ' ' . $result_error . "\n", 3, STORE_PAGE_PARSE_TIME_LOG);
    }

    return $result;
  }


    tep_db_perform(TABLE_ORDERS_PRODUCTS, $sql_data_array);
    $order_products_id = tep_db_insert_id();

[ /syntax] 

$order_products_id keeps resulting in zero, but everything in the previous line's execution provides correct results.  Shouldn't the call to tep_db_insert_id() provide me the selection of the database I need the value from, or do I need to actually designate which database.  If so, how?  The value it should be returning is an auto-increment field (which I believe is a requirement for the mysql_insert_id() function).

Thanks!

Re: Help selecting a database for mysql_insert_id()

Posted: Mon Dec 05, 2011 3:37 pm
by mikosiko
there is no any INSERT in the code that you posted... assuming that you are executing the query here:

Code: Select all

return tep_db_query($query, $link);
we are not seeing that function, therefore we can just guess that the query is failing and subsequent call to mysql_insert_id() is returning FALSE.

suggestions... echo your $query variable before the call to tep_db_query() to be sure that you are trying to execute a valid query.

Also we just can guess that you already have the mysql_connect() and mysql_select_db() in some other part of your code, and that they produce the right results

Re: Help selecting a database for mysql_insert_id()

Posted: Mon Dec 05, 2011 4:00 pm
by smartwork
Thank you for your reply. Sorry about not including that function. I tried to ensure I had it all. I've edited my original post to include the function that performs the insert. All the information that is to be written during the query is being successfully written but the auto-incremented value that I'm trying to return isn't being returned, but is successfully incremented and written to the record.

What this is performing is this:

The query inserts products being purchased by customers. Each of those products is inserted in a table 'orders_products' and each has an auto-incremented id which isn't the actual product's id in the catalog. It's simply the id in the orders_products table - like a counter since day 1 of operation. So, if we've sold 1000 products since day 1 and a customer now buys one product, that product is inserted and the id is 1001, and that's the number I'm trying to return. These products can also have options or attributes, so this product's id is used in a second table 'orders_products_attributes' to associate the options with the correct product. Instead of 1001 being inserted there, I'm getting zero for the id, but all the option info is being correctly written. Everything else in the process appears to be successfuly executed.

I just cannot figure out why I'm getting zero returned (or not).

Re: Help selecting a database for mysql_insert_id()

Posted: Mon Dec 05, 2011 7:30 pm
by mikosiko
test replacing mysql_insert_id() with last_insert_ID()