Solved

Multiple PHP Scripts Connection question on performance

Posted on 2014-10-30
14
219 Views
Last Modified: 2014-10-30
Hello
Just a question on multiple php script on one page  I have noticed if each script has a
$servername = "localhost";
$username = "root";
$password = "password";
$dbname = "database";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

Open in new window

It can take a few seconds longer to open the page as each script is making a new connection.
What I want to know is.
 *Can I have the first script make the connection and have the last script on the page close the connection.
$conn->close();

Open in new window


*Is this ok.

*Is there a chance that each script might get the wrong information from the Database.

*If this is ok is there a limit to the amount of PHP scripts that can use the one Connection

The code below has 9 php scripts for testing. I will be adding more to this page but I will be transferring each separate script to individual TEXTAREAs. But again they will all be on one page

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Print Table</title>
</head>

<body>
<p>1</p>
 <?php
 Multiple PHP Scripts Connection
$servername = "localhost";
$username = "root";
$password = "*******";
$dbname = "test";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
 } 

$sql = "SELECT datepicker, coverteacher, sickteacher, room, period, class FROM  w1monsick1 WHERE w1monsick1.id =1";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
		echo "DATE: " . $row["datepicker"]."\n";
 		echo "ABSENT: " . $row["sickteacher"]."\n";
 		echo "COVER: " . $row["coverteacher"]."\n"; 
		echo "PERIOD: " . $row["period"]."\n";
		echo "CLASS: " . $row["class"]."\n";
		echo "ROOM: " . $row["room"]; 

    }
} else {
    echo "0 results";
}
 
 ?>
 <hr>
 <p>2</p>
  <?php

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
 } 

$sql = "SELECT datepicker, coverteacher, sickteacher, room, period, class FROM  w1monsick1 WHERE w1monsick1.id =2";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
		echo "DATE: " . $row["datepicker"]."\n";
 		echo "ABSENT: " . $row["sickteacher"]."\n";
 		echo "COVER: " . $row["coverteacher"]."\n"; 
		echo "PERIOD: " . $row["period"]."\n";
		echo "CLASS: " . $row["class"]."\n";
		echo "ROOM: " . $row["room"]; 

    }
} else {
    echo "0 results";
}

 ?>
 <hr>
 <p>3</p>
  <?php

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
 } 

$sql = "SELECT datepicker, coverteacher, sickteacher, room, period, class FROM  w1monsick1 WHERE w1monsick1.id =3";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
		echo "DATE: " . $row["datepicker"]."\n";
 		echo "ABSENT: " . $row["sickteacher"]."\n";
 		echo "COVER: " . $row["coverteacher"]."\n"; 
		echo "PERIOD: " . $row["period"]."\n";
		echo "CLASS: " . $row["class"]."\n";
		echo "ROOM: " . $row["room"]; 

    }
} else {
    echo "0 results";
}

 ?>
 <hr>
 <p>4</p>
  <?php

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
 } 

$sql = "SELECT datepicker, coverteacher, sickteacher, room, period, class FROM  w1monsick1 WHERE w1monsick1.id =4";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
		echo "DATE: " . $row["datepicker"]."\n";
 		echo "ABSENT: " . $row["sickteacher"]."\n";
 		echo "COVER: " . $row["coverteacher"]."\n"; 
		echo "PERIOD: " . $row["period"]."\n";
		echo "CLASS: " . $row["class"]."\n";
		echo "ROOM: " . $row["room"]; 

    }
} else {
    echo "0 results";
}

 ?>
 <hr>
 <p>5</p>
  <?php

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
 } 

$sql = "SELECT datepicker, coverteacher, sickteacher, room, period, class FROM  w1monsick1 WHERE w1monsick1.id =5";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
		echo "DATE: " . $row["datepicker"]."\n";
 		echo "ABSENT: " . $row["sickteacher"]."\n";
 		echo "COVER: " . $row["coverteacher"]."\n"; 
		echo "PERIOD: " . $row["period"]."\n";
		echo "CLASS: " . $row["class"]."\n";
		echo "ROOM: " . $row["room"]; 

    }
} else {
    echo "0 results";
}

 ?>
 <hr>
 <p>6</p>
  <?php

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
 } 

$sql = "SELECT datepicker, coverteacher, sickteacher, room, period, class FROM  w1monsick1 WHERE w1monsick1.id =6";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
		echo "DATE: " . $row["datepicker"]."\n";
 		echo "ABSENT: " . $row["sickteacher"]."\n";
 		echo "COVER: " . $row["coverteacher"]."\n"; 
		echo "PERIOD: " . $row["period"]."\n";
		echo "CLASS: " . $row["class"]."\n";
		echo "ROOM: " . $row["room"]; 

    }
} else {
    echo "0 results";
}
 
 ?>
 <hr>
 <p>7</p>
  <?php

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
 } 

$sql = "SELECT datepicker, coverteacher, sickteacher, room, period, class FROM  w1monsick1 WHERE w1monsick1.id =7";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
		echo "DATE: " . $row["datepicker"]."\n";
 		echo "ABSENT: " . $row["sickteacher"]."\n";
 		echo "COVER: " . $row["coverteacher"]."\n"; 
		echo "PERIOD: " . $row["period"]."\n";
		echo "CLASS: " . $row["class"]."\n";
		echo "ROOM: " . $row["room"]; 

    }
} else {
    echo "0 results";
}

 ?>
 <hr>
 <p>8</p>
  <?php

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
 } 

$sql = "SELECT datepicker, coverteacher, sickteacher, room, period, class FROM  w1monsick1 WHERE w1monsick1.id =8";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
		echo "DATE: " . $row["datepicker"]."\n";
 		echo "ABSENT: " . $row["sickteacher"]."\n";
 		echo "COVER: " . $row["coverteacher"]."\n"; 
		echo "PERIOD: " . $row["period"]."\n";
		echo "CLASS: " . $row["class"]."\n";
		echo "ROOM: " . $row["room"]; 

    }
} else {
    echo "0 results";
}
 
 ?>
 <hr>
 <p>9</p>
  <?php

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
 } 

$sql = "SELECT datepicker, coverteacher, sickteacher, room, period, class FROM  w1monsick1 WHERE w1monsick1.id =9";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
		echo "DATE: " . $row["datepicker"]."\n";
 		echo "ABSENT: " . $row["sickteacher"]."\n";
 		echo "COVER: " . $row["coverteacher"]."\n"; 
		echo "PERIOD: " . $row["period"]."\n";
		echo "CLASS: " . $row["class"]."\n";
		echo "ROOM: " . $row["room"]; 

    }
} else {
    echo "0 results";
}
 $conn->close();
 ?>
 <hr>

</body>
</html>

Open in new window

PS it dose work I just need to know if its ok to do it this way or is there a better way of doing it.
Thanks
0
Comment
Question by:paddy086
  • 4
  • 4
  • 3
  • +2
14 Comments
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 40413335
By script do you mean page?

Each request to the server has to have its own connection established.

Consider the following scenarios

http://www.yourserver.com/yourpage.php
http://www.yourserver.com/anotherpage.php
http://www.yourserver.com/someotherpage.php

For each of the above requests you would need to establish a new connection to the database.

Lets consider another example - take the first url
http://www.yourserver.com/yourpage.php
Lets say this page is constructed as follows

<?php
require_once('config.php');
require_once('database.php');
require_once('someincludepage.php');
...
require_once('close.php');

Open in new window


In this scenario the database connection only needs to be in one of the scripts - with the above structure the first script includes your configuration settings - the second includes the database connection script which uses the config settings to make a connection. After that any access to the database can be made over the existing connection.

The close.php include is optional - if you include something like this you can put the $mysqli->close() call in there.

The only thing I can see in your script that is a bit strange is the multiple calls to
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
 } 

Open in new window

You only need to do this once immediately after your mysqli instantiation at the top of the script.

I am not sure I understand your terminology of "scripts" it appears you are calling different sections of your php file scripts - when in fact the whole file is a single script - in theory the entire file and all its includes are a single script.
0
 
LVL 34

Assisted Solution

by:gr8gonzo
gr8gonzo earned 100 total points
ID: 40413344
Web pages are "stateless" which means that each individual page starts up and ends itself. You cannot keep a database connection open between pages (there is a concept of "persistent connections" but it's usually used incorrectly and causes more problems than it solves).

So even if you don't call "$conn->close" specifically, it will automatically be done already.

You are connecting to "localhost" which means that the database connection itself should not take more than a few milliseconds at most. If you're seeing multiple seconds of startup time, there might be a bad query or some other problem in play that is causing the speed problems.
0
 
LVL 51

Accepted Solution

by:
Julian Hansen earned 200 total points
ID: 40413357
You could possibly also optomise your page by getting all the results at once - sorting them on id and then looping through one output loop like so
<?php
// Multiple PHP Scripts Connection
$servername = "localhost";
$username = "root";
$password = "*******";
$dbname = "test";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
 } 
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Print Table</title>
</head>
<body>
<?php
// GET ALL THE RESULTS IN ONE GO - ORDER BY id
$sql = "SELECT id, datepicker, coverteacher, sickteacher, room, period, class FROM  w1monsick1 ORDER BY w1monsick1.id";
$result = $conn->query($sql);
$current = '';
if ($result->num_rows > 0) :
    // output data of each row
    while($row = $result->fetch_assoc()) {
                // CHECK IF WE ARE ON A NEW SECTION
                // IF WE ARE THEN CHECK THIS IS NOT THE FIRST
                // SECTION. IF NOT OUTPUT A <hr>
                // OUTPUT THE SECTION NUMBER AND SET
                // THE current VALUE TO STORE WHAT SECTION WE ARE ON
		if (row['id'] != $current) :
			if ($current != '') echo "<hr>";
			echo "<p>{$row['id']}</p>";
			$current=$row['id'];
		}
                // WE ARE USING heredoc NOTATION
                //http://php.net/manual/en/language.types.string.php#language.types.string.syntax.heredoc
		echo <<< HTML
DATE: {$row["datepicker"]}
ABSENT: {$row["sickteacher"]}
COVER: {$row["coverteacher"]}
PERIOD: {$row["period"]}
CLASS: {$row["class"]}
ROOM: {$row["room"]}
HMTL;
    }
} else {
    echo "0 results";
}
?>
</body>
</html>

Open in new window

0
 

Author Comment

by:paddy086
ID: 40413440
Hello julianH
I was classifying each
<?php
   ?>

Open in new window

as one script and I get it what you are saying the whole page is one script.

I left the following in by mistake as I originally only had as I call it one script. I just copied and pasted the same script over and over again and edited the parts needed but I did forget to remove the following from each section.
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
 } 

Open in new window


So judging from your response if the first
<?php     ?>

Open in new window

contains the connections to my localhost/Database
then all other
<?php     ?>

Open in new window

on the same webpage page can use the connect from the first one and this is ok to do.

I will also have a look at the script you provided to me but im not sure if it will work for my project I am going to put each script into there separate textarea on a new page and in a text area the HTML tags show up that's why my script is created the way it is as this works in a text area and dose not display any tags just the required info from my Database.
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 100 total points
ID: 40413450
A general design pattern that you might find useful goes something like this:
<?php
require_once('common.php');
/* PROGRAM LOGIC HERE CREATES ALL THE VARIABLES FOR THE VIEW */
require_once('header.php');
require_once('template.php');
require_once('footer.php');

Open in new window

In this design, the common.php script connects to the database and performs any other housekeeping/initialization activities such as loading classes, etc.  It does not produce any browser output.  Thereafter, the program logic analyzes the request, runs the queries, etc., to create the data set that the page must produce.  It, too, does not produce any browser output.  After that, the output is created by three scripts, using a header and footer to create a consistent look and feel for the web site, and a template.php script that inserts the created variables into an appropriately formatted web page (maybe using HEREDOC notation).  This strategy organizes the logic in a way that minimizes the inflection points that would need to be changed if the application or data model needed to change.

To the issue of slow web scripts, when this happens you may want to learn about Chrome Dev Tools or YSlow to see what external manifestations of slowness can be made visible.  In my experience slowness always arises from the I/O subsystem and in modern web applications this is usually the database.  You can use EXPLAIN SELECT to get some insight into how the DB engine is handling your queries, what indexes it is using, etc.
0
 

Author Comment

by:paddy086
ID: 40413453
Hi gr8gonzo thanks for you input into my question

If I only have one connect to a database on a page it happens in milliseconds.
But if I have say 20 connects on the one page it could take about 3or4 seconds its not much but if someone is waiting for the page to load then they could get frustrated with the wait if its happening on multiple webpages that they are using throughout the whole website.

I was looking at "persistent connections" but that is not what I need as I do not need the connection to stay open as the user passes from page to page.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40413454
separate textarea on a new page
That would seem to imply a new HTTP request from the client.  Please see this article which may help you organize your thinking about how the client/server protocol works.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/A_11271-Understanding-Client-Server-Protocols-and-Web-Applications.html
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40413466
Sidebar note, because I see a lot of people trying to learn web development by copying internet-based examples with copy/paste...

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

Open in new window


Do you know what all of this stuff means?   If not, why would you use it?  That's a rhetorical question, of course, but it tries to get to the idea that simplicity and understanding are enormously valuable.  You might want to learn about the HTML5 doctype and discard all of this old junk in favor of the modern standard.  You will get much better browser support if you keep your scripts up-to-date.

There are probably many more things to promote or avoid in web development, but here is my short list of what not to do.  Read it in your spare time.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_12293-AntiPHPatterns-and-AntiPHPractices.html
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 40413480
then all other
<?php ?>
on the same webpage page can use the connect from the first one and this is ok to do.

Multiple <?php ?> on a page does not count as different scripts - all part of the same script. Any code that is included as
part of the same request can share a connection provided the connection is made before the code tries to use it.
0
 

Author Comment

by:paddy086
ID: 40413684
Hi Ray

Thanks for your input I will look into your suggestions as well. As far as the copy and paste it was my own work that I was copy and pasting instead of typing up mostly the same code bar one or two alterations I did some copying and edited it to suit the next part of my project.

Thanks julianH, gr8gonzo and Ray Paseur
I am going to leave this question open for a bit so I can try out some of the suggestions once again thanks all
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40413753
No points for this please, but here is the skeleton of a valid HTML5 page.  With all the advantages of HTML5, it doesn't make sense to choose an older, quirks-mode document type any more.  You may wish to use your own links for jQuery instead of this one.

<?php // demo/html5_template.php
error_reporting(E_ALL);

// CREATE VARIABLES FOR OUR HTML
$dat = date('r');
$xyz = "Hello world, this page was rendered at $dat";

// CREATE OUR WEB PAGE IN HTML5 FORMAT
$htm = <<<HTML5
<!DOCTYPE html>
<html dir="ltr" lang="en-US">
<head>
<meta charset="utf-8" />
<meta name="robots" content="noindex, nofollow" />
<meta name="viewport" content="width=device-width, initial-scale=1.0">

<script type="text/javascript" src="http://code.jquery.com/jquery-latest.min.js"></script>
<script>
$(document).ready(function(){
});
</script>

<title>HTML5 Page With jQuery in UTF-8 Encoding</title>
</head>
<body>

<noscript>Your browsing experience will be much better with JavaScript enabled!</noscript>

<p>$xyz</p>
<script>alert('$xyz');</script>

</body>
</html>
HTML5;

// RENDER THE WEB PAGE
echo $htm;

Open in new window

0
 
LVL 33

Assisted Solution

by:Slick812
Slick812 earned 100 total points
ID: 40414195
greetings paddy086, , not sure that you have an understanding of what a PHP request is and what a PHP script (separate file) is in relation to the single PHP request. In any single PHP request from a browser, only ONE php process runs, so you can have as many -
require('dataOut1.php');
and all of the include and require, php pages SHARE variables, functions, and class definitions to all php pages used, so you open the database connection in the first require page (a separate DATABASE php page) that only does the -

       $conn = new mysqli($servername, $username, $password, $dbname);
       if ($conn->connect_error) {
          die("Connection failed: " . $conn->connect_error);
           }


and where you place DATABASE functions like


function doTextOUT($row) {
  echo "DATE: " . $row["datepicker"]."\n";
  echo "ABSENT: " . $row["sickteacher"]."\n";
   echo "COVER: " . $row["coverteacher"]."\n";
  echo "PERIOD: " . $row["period"]."\n";
  echo "CLASS: " . $row["class"]."\n";
  echo "ROOM: " . $row["room"];
  }

I tell you about a function here, but many in PHP now use Class and object for a Database connection and output

you use something like julianH suggested for a single request
// GET ALL THE RESULTS IN ONE GO - ORDER BY id
$sql = "SELECT id, datepicker, coverteacher, sickteacher, room, period, class FROM  w1monsick1 WHERE id < 5 ORDER BY id";
$result = $conn->query($sql);

then Get all the Rows out at once -
$rows = $result->fetch_array(MYSQLI_NUM);

THEN in the many <text areas> use the doTextOUT( ) function as

echo '<textarea name="tx2">';
doTextOUT($rows[1]); // Zero based $rows
echo '</textarea>';

My point is if you do NOT close the mysqli connection, then you can use the $conn for the database in any php page required after it is connected. Or use any function or variable from the other pages in any PHP script that is in the current PHP process.
I hope this helps some.
0
 

Author Closing Comment

by:paddy086
ID: 40414555
Thanks everyone for your help its more than i could have asked for you were all a help and gave me a lot of info to work with thanks
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

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…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

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

20 Experts available now in Live!

Get 1:1 Help Now