0

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);
    }
Improve this question
7
  • @BehzadDadashpour I am not display the data I am passing the data to my app Commented Nov 14, 2018 at 6:30
  • 1
    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. Commented Nov 14, 2018 at 6:32
  • @Tordek yes I am going to do it I need to improve the speed of retrieving data Commented Nov 14, 2018 at 6:34
  • mySql DATE_FORMAT for date converting maybe help Commented Nov 14, 2018 at 6:36
  • 1
    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()? Commented Nov 14, 2018 at 6:49

2 Answers 2

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

why do I need to declare the variable array first?
You're not adding the rest of row's values to ar.
What is the difference between SELECT * and SELECT (Column specified)?
@LawrenceAgulto this may occur when you have many array data to fetch.
@LawrenceAgulto you may read this though stackoverflow.com/questions/3180375/select-vs-select-column
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

Improve this answer

6 Comments

I am only getting 1 row
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.
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
@LawrenceAgulto Updated for multiple rows.
use ar[]= instead of print
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.