mysqli connection link (1)how to handle insert_id, rows_affected; (2) multiple queries in the same script thread
Posted on 2014-11-26
i've inherited a mysql website that i'm updating to mysqli (procedural)
it fortunately uses a common point of interface to mysql via the "gasket" of several db_* functions,
like $Results = db_sql($Query) (for select/update/insert/delete etc queries) and db_mysql_insert_id etc.
there is no close connection at the end of a single query in db_sql.
however, what is the best strategy to use for cross-page (script, same session) multiple calls to mysql
w.r.t. connections and retrieving mysql_insert_id and rows_affected information that pertain
to the last mysql access.
currently the db_sql interface calls mysqli_connect for each query, of which there usually are several in a row within one web page script, followed by more called from the next web page. it's ALL ONE database.
1. should i be locally keeping a copy of the LINK object (not sure how to do this in a SESSION variable)
or is it ok to let mysql sort out (maybe cache) the new, but repeated connections to the same db. there are no intentional locks, procedures, delays, or anything sophisticated - just simple queries and updates.
2. how is best to access the last insert_id and rows_affected? currently there is a session variable
that's within the db_sql function that stores them (95% of the time they are not used) (but this is not a huge huge db nor is it high-volume, so performance, altho a nicety, is not mission-critical)
any advice about this type of gasket interface conversion to mysqli would be great.
(i cannot find much in this vein on the web)