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
254 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.

Question has a verified solution.

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

Suggested Solutions

Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

919 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

12 Experts available now in Live!

Get 1:1 Help Now