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
280 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

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

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Creating and Managing Databases with phpMyAdmin in cPanel.
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

732 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