Solved

mysqli connection link (1)how to handle   insert_id, rows_affected;  (2) multiple queries in the same script thread

Posted on 2014-11-26
3
261 Views
Last Modified: 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)
0
Comment
Question by:willsherwood
  • 2
3 Comments
 
LVL 58

Accepted Solution

by:
Gary earned 500 total points
ID: 40468172
there is no close connection at the end of a single query  in db_sql
Doesn't need to be, the connection will be closed when the page finishes executing.

You can use persistent connections but it's not advisable.
Better is to create a new connection each time a page is called.

currently the  db_sql interface calls  mysqli_connect for each query
If this is within the same page then something is wrong, you should only create the connection once, unless you are connecting to different databases obviously.

For the ID of the last inserted record use insert_id and affected_rows for how many rows were affected
0
 

Author Comment

by:willsherwood
ID: 40468187
connection: for a single db, then just keep the $LINK object as a global that is unique and hangs around?

without maintaining a global LINK object,  the LINK is lost and a new call to insert_id (etc) won't have a link and relation to the last  sql operation,   so i guess you're saying to solve both issues with one strategy - open the LINK at "sunrise" and
keep it as a global?

thanks for the quick response
0
 
LVL 58

Expert Comment

by:Gary
ID: 40468198
Basically yes. There is no reason to keep opening and closing connections in a single page execution, just puts more strain on the server and takes longer to do whatever it is doing.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Importing and exporting data Magento 1.x ? 4 85
sql statement to select and drop 13 49
mcrypt_create_iv() is deprecated 4 160
Create a MySQL table as easily as possible 6 37
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question