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
248 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now