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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
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…

809 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