slow load time for php file

Problems with the Windows version of XAMPP, questions, comments, and anything related.

slow load time for php file

Postby MikeSiencyn » 10. April 2021 10:00

Hi
This is the continuing story of my lock-down project to build a website for my wife's vintage shop!
First, thanks to all who've helped me so far, you've enabled be to make great progress.
I'm in the trial stages and I'm finding that a php file I use to get the stock information from the database for customers to see is taking 6 seconds to load.
The code is shown below.

<!DOCTYPE html>
<html lang = "en-US">
<head>
<meta charset = "UTF-8">
<title>getRailItems.php</title>
</head>
<body>
<?php

//get railID from the html rail file in function getRaiItems()
$railID = filter_input(INPUT_POST, 'railID');

try {
//connect to database
$con= new PDO('mysql:host=localhost;dbname=xxxx', "xxxx", "xxxx");
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

//create a SELECT statement based on $railID
$stmt = $con->prepare('SELECT stockID, ItemHeadline, ItemDescription, Price_GBP, Postage_GBP FROM stock WHERE rails_railID = ?');
$stmt->execute(array($railID));

$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

if (empty($result)){
print "$railID";
print "No matches found";
} else {
foreach($result as $row){
$stockID = $row["stockID"];
$ItemHeadline = $row["ItemHeadline"];
$ItemDescription = $row["ItemDescription"];
$Price_GBP = $row["Price_GBP"];
$Postage_GBP = $row["Postage_GBP"];

print <<< HERE
<div id="itemInfo">
<div id="itemAlphaNum">
<h3>Item Headline</h3> <p>$ItemHeadline</p>
<h3>Item Description</h3> <p>$ItemDescription</p>
<h3>Price(£)</h3> <p>$Price_GBP</p>
<h3>Postage(£)</h3> <p>$Postage_GBP</p>
</div>
</div>
<div id="itemPhotos">
HERE;

$stmt = $con->prepare("SELECT photo FROM photos WHERE `stock_stockID` = ?");
$stmt->execute(array($stockID));

$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

if (empty($result)){
print "$stockID";
print "No photos found";
} else {
foreach($result as $row){
$photo = $row["photo"];
$photoEncoded = base64_encode($photo);

print <<< HERE
<div class="photo"><img src="data:image/jpg;charset=utf8;base64,$photoEncoded"></div>
HERE;

} // end record loop
} // end 'empty results' if

print <<< HERE
</div> <!-- end itemPhotos div -->
HERE;

} // end record loop
} // end 'empty results' if

} catch(PDOException $e) {
echo 'ERROR: ' . $e->getMessage();
} // end try

?>
</body>
</html>

When I just get text information the load time is a fraction of a second but when I get the images to accompany the text the load time is about 6 seconds.
I've used similar image handling coding for uploading data into the database and found no loading time problems.
I've tried increasing the cache size in the php.ini file to no avail.
If someone has some suggestions for how to proceed I'd be very grateful.
Many thanks
Mike
MikeSiencyn
 
Posts: 24
Joined: 02. February 2021 15:41
XAMPP version: 3.2.4
Operating System: Windows10

Re: slow load time for php file

Postby Nobbie » 10. April 2021 11:28

I think the problem is the storage of the binary JPGs into the database, thats a huge amount of data and may slow down I/O dramatically. Why do you store pictures in a SQL database? I suggest NOT to put binary pictures into a database, instead store the pictures onto the harddisk and put the path to the picture into the SQL database. Thats how its done normally.

The problem is not the database itself, but the result of the fetch command, that results in huge binary data to be transported via TCPIP from the database into the PHP scope and finally to the browser. Also the base64 encode may take a while (unsure about that). But i really do not recommend to store binary JPGs into database columns. Put the pictures somewhere onto the disk, store the path (or simply the filename, you can prepend the correct URL in your PHP script) into your database and you will see, your script will speed up dramatically.
Nobbie
 
Posts: 13170
Joined: 09. March 2008 13:04

Re: slow load time for php file

Postby MikeSiencyn » 10. April 2021 12:45

Thanks, Nobbie
That's really helpful.
I was trying to build a site which would be hosted by a provider so that customers would not be routinely accessing our laptop.
Is there a way of implementing your speeding up idea via a provider? (and which will not be expensive.)
All the best
Mike
MikeSiencyn
 
Posts: 24
Joined: 02. February 2021 15:41
XAMPP version: 3.2.4
Operating System: Windows10

Re: slow load time for php file

Postby MikeSiencyn » 10. April 2021 12:47

Hi Nobbie
Another thought occurred to me, how do sites like eBay/Gumtree handle images without sacrificing speed?
MikeSiencyn
 
Posts: 24
Joined: 02. February 2021 15:41
XAMPP version: 3.2.4
Operating System: Windows10

Re: slow load time for php file

Postby Nobbie » 10. April 2021 13:06

MikeSiencyn wrote:Is there a way of implementing your speeding up idea via a provider? (and which will not be expensive.)


Yes of course! Simply upload all the photos to your webspace, lets say you put them in a subfolder "pictures" of your website. Lets say we have a picture called "myfoto1.jpg", you uploaded it (via FTP, that is provided by your webhoster) to "picture" folder, store the filename into the SQL row which previously holds the picture itself, and finally change your <img> link in your script:

Replace

Code: Select all
foreach($result as $row){
$photo = $row["photo"];
$photoEncoded = base64_encode($photo);

print <<< HERE
<div class="photo"><img src="data:image/jpg;charset=utf8;base64,$photoEncoded"></div>


simply by:

Code: Select all
foreach($result as $row){
$photo = $row["photo"];

/* $photoEncoded = base64_encode($photo); <..... dont need that anymore, delete this line  */
print <<< HERE
<div class="photo"><img src="http://www.mydomain.com/pictures/$photo"></div>


You may of course store the full URL into the database instead of only the filename. Or use a predefined constant or variable for your Domain name (so you have a single point of control if the Domain and/or the URL changes). You may also replace "http:" by a variable, so you can choose between http: and https: with a slight configuration change.
Nobbie
 
Posts: 13170
Joined: 09. March 2008 13:04

Re: slow load time for php file

Postby Nobbie » 10. April 2021 13:08

MikeSiencyn wrote:Hi Nobbie
Another thought occurred to me, how do sites like eBay/Gumtree handle images without sacrificing speed?


Same as above, they put them onto webspace and link to them. Nobody stores images into SQL databases. (Anyway, i dont know "Gumtree", never heard about).
Nobbie
 
Posts: 13170
Joined: 09. March 2008 13:04

Re: slow load time for php file

Postby MikeSiencyn » 11. April 2021 10:37

Thanks very much, Nobbie
Really helpful.
All the best
Mike
MikeSiencyn
 
Posts: 24
Joined: 02. February 2021 15:41
XAMPP version: 3.2.4
Operating System: Windows10


Return to XAMPP for Windows

Who is online

Users browsing this forum: No registered users and 98 guests