PHP improve data retrieving

Question

I am retrieving 5000 plus data in my MYSQL database. I takes 5-10 minutes to retrieve the data (it is only just local slower when over the network) is there a way to improve the speed without using a plugin?

$ContactID = $_GET["Contact"];

    $sql = "SELECT * FROM tblContacts WHERE Coordinator = '$ContactID'";
    $result = mysqli_query($conn, $sql);
    $count = mysqli_num_rows($result);

    if($count > 0){
        while ($row = mysqli_fetch_array($result)) {
            $supdate = date("Y-m-d h:i", strtotime($row['ServerUpdate']));
            $mupdate = date("Y-m-d h:i", strtotime($row['MobileUpdate']));
            $ar[] = array(
                'ContactID' => $row['ContactID'],
                'FileAs' => $row['FileAs'],
                'FirstName' => $row['FirstName'],
                'MiddleName' => $row['MiddleName'],
                'LastName' => $row['LastName'],
                'Position' => $row['Position'],
                'Company' => $row['Company'],
                'CompanyID' => $row['CompanyID'],
                'ContactType' => $row['ContactType'],
                'RetailerType' => $row['RetailerType'],
                'PresStreet' => $row['PresStreet'],
                'PresBarangay' => $row['PresBarangay'],
                'PresDistrict' => $row['PresDistrict'],
                'PresTown' => $row['PresTown'],
                'PresProvince' => $row['PresProvince'],
                'PresCountry' => $row['PresCountry'],
                'Landmark' => $row['Landmark'],
                'Telephone1' => $row['Telephone1'],
                'Telephone2' => $row['Telephone2'],
                'Mobile' => $row['Mobile'],
                'Email' => $row['Email'],
                'Employee' => $row['Employee'],
                'Customer' => $row['Customer'],
                'Coordinator' => $row['Coordinator'],
                'ServerUpdate' => $supdate,
                'MobileUpdate' => $mupdate
            );
        }

        print json_encode($ar);
    }

@BehzadDadashpour I am not display the data I am passing the data to my app — , Nov 14 '18 at 06:30
Be very careful with how you use variables in your queries. It's possible to perform SQL injection through the ContactID parameter. Use prepared statements. — Tordek, Nov 14 '18 at 06:32
@Tordek yes I am going to do it I need to improve the speed of retrieving data — , Nov 14 '18 at 06:34
@MagnusEriksson I know, I just need to improve the retrieval time of data — , Nov 14 '18 at 06:42
I saw in a comment for one of the answers that this is retrieving a large data set. First you need to identify what process it is that 's slow. Is it the SQL query? (Is the `Coordinator` column properly indexed?) Is it your while loop? Is it the json_encode()? Is it outputting the result with `print_r()`? — M. Eriksson, Nov 14 '18 at 06:49

j3thamz · Accepted Answer · 2018-11-14T06:55:30.927

3

$ContactID = $_GET["Contact"];
$ar = array();
$sql = "SELECT ContactID,FileAs, FirstName ,MiddleName ,LastName ,Position ,Company ,CompanyID  ,ContactType ,RetailerType ,PresStreet 
             ,PresBarangay ,PresDistrict  ,PresTown , PresProvince  ,PresCountry ,Landmark   ,Telephone1 , Telephone2  ,Mobile 
              ,Email  ,Employee  ,Customer  ,Coordinator  FROM tblContacts WHERE Coordinator = '$ContactID'";
$result = mysqli_query($conn, $sql);
$count = mysqli_num_rows($result);

if($count > 0){
    $rowCount = 0;
    while ($row = mysqli_fetch_array($result)) {
        $supdate = date("Y-m-d h:i", strtotime($row['ServerUpdate']));
        $mupdate = date("Y-m-d h:i", strtotime($row['MobileUpdate']));
        $ar[$rowCount] = $row;
        $ar[$rowCount]['ServerUpdate'] = $supdate;
        $ar[$rowCount]['ServerUpdate']  = $mupdate;
         $rowCount++;
    }

    print json_encode($ar);
}

You must declare the variable of array first in the top.

You can directly call from mysql what data needed. Hope can help.

edited Nov 14 '18 at 06:55

answered Nov 14 '18 at 06:42

j3thamz

66
4

1

why do I need to declare the variable array first? – Nov 14 '18 at 06:46
1

You're not adding the rest of row's values to ar. – Tordek Nov 14 '18 at 06:48
What is the difference between SELECT * and SELECT (Column specified)? – Nov 14 '18 at 06:56
@LawrenceAgulto this may occur when you have many array data to fetch. – j3thamz Nov 14 '18 at 06:57
@LawrenceAgulto you may read this though https://stackoverflow.com/questions/3180375/select-vs-select-column – j3thamz Nov 14 '18 at 06:58

Naveed Ramzan · Answer 2 · 2018-11-14T06:54:53.697

0

It could be like this way.

$contactID = $_GET["Contact"];
$sql = "SELECT * FROM tblContacts WHERE Coordinator = '$contactID'";
$result = mysqli_query($conn, $sql);
if(mysqli_num_rows($result) > 0){
    $data = array();
    while($row = mysqli_fetch_array($result)){

        $row['supdate'] = date("Y-m-d h:i", strtotime($row['ServerUpdate']));
        $row['supdate'] = date("Y-m-d h:i", strtotime($row['MobileUpdate']));

        $data[] = $row;
    }
    print json_encode($data);
}

updated for multiple records

edited Nov 14 '18 at 06:54

answered Nov 14 '18 at 06:28

Naveed Ramzan

3,565
3
25
30

I am only getting 1 row – Nov 14 '18 at 06:32
Agreed because you are passing contactId so it will return one id. and that's why I removed while loop. Secondly one row is in result so no need to pass in array as row is already an array. – Naveed Ramzan Nov 14 '18 at 06:38
When I use the query it returns 5000 rows. That is why I have while loop. I need that 5000 rows What I need is to improve the retrieval time – Nov 14 '18 at 06:40
@LawrenceAgulto Updated for multiple rows. – Naveed Ramzan Nov 14 '18 at 06:45
use ar[]= instead of print – Tordek Nov 14 '18 at 06:49
Why would this be faster than the OP's current code? There isn't much of a difference? – M. Eriksson Nov 14 '18 at 06:56

PHP improve data retrieving

2 Answers2