php - JSON data encoded are repeated

318

I have a database with several data in it, but they are unique of course. These data are inside each table, telcoCall, telcoData, telcoSMS, depending on its class.

I then usejson_encode to merge these data into 1 single array. Inside telcoCall, the data are in proper place. However, telcoData and telcoSMS are messy. The data inside these tables are being duplicated. This is how it looks,

enter image description here

Here's the code:

<?PHP
include '../initialization.php';

$mysqli = @mysqli_connect($host, $username, $password, $db);

$query = 'SELECT t.*, c.*, d.*, s.* '.
         'FROM telco t '.
            'INNER JOIN telcoCall c ON t.telcoId = c.telcoId '.
            'INNER JOIN telcoData d ON t.telcoId = d.telcoId '.
            'INNER JOIN telcoSMS s ON t.telcoId = s.telcoId '.
                'ORDER BY t.telcoName, c.callName, d.dataName, s.smsName';

//setup array to hold information
$telcos = array();

//setup holders for the different types so that we can filter out the data
$telcoId = 0;
$callId = 0;
$dataId = 0;
$smsId = 0;

//setup to hold our current index
$telcoIndex = -1;
$callIndex = -1;
$dataIndex = -1;
$smsIndex = -1;

if ($result = mysqli_query($mysqli, $query)) {
    //go through the rows
    while($row = mysqli_fetch_assoc($result)) {
        if($telcoId != $row['telcoId']) {
            $telcoIndex++;
            $callIndex = -1;
            $dataIndex = -1;
            $smsIndex = -1;
            $telcoId = $row['telcoId'];

            //add the console
            $telcos[$telcoIndex]['Telco'] = $row['telcoName'];

            //setup the information array
            $telcos[$telcoIndex]['Call Promo'] = array();
            $telcos[$telcoIndex]['Data Promo'] = array();
            $telcos[$telcoIndex]['SMS Promo'] = array();
        }

        if($callId != $row['callId']) {
            $callIndex++;
            $callId = $row['callId'];

            //add the model to the console
            $telcos[$telcoIndex]['Call Promo'][$callIndex]['Call Name'] = $row['callName'];

            //setup the title array
            $telcos[$telcoIndex]['Call Promo'][$callIndex]['Call'] = array();

            //add the game to the current console and model
            $telcos[$telcoIndex]['Call Promo'][$callIndex]['Call'][] = array(
                'Keyword'     => $row['callKeyword'],
                'Description' => $row['callDescription'],
                'Number'      => $row['callNumber'],
                'Validity'    => $row['callValidity'],
                'Price'       => $row['callPrice']
                );
        }

        if($dataId != $row['dataId']) {
            $dataIndex++;
            $dataId = $row['dataId'];

            //add the model to the console
            $telcos[$telcoIndex]['Data Promo'][$dataIndex]['Data Name'] = $row['dataName'];

            //setup the title array
            $telcos[$telcoIndex]['Data Promo'][$dataIndex]['Data'] = array();

            //add the game to the current console and model
            $telcos[$telcoIndex]['Data Promo'][$dataIndex]['Data'][] = array(
                'Keyword'      => $row['dataKeyword'],
                'Description'  => $row['dataDescription'],
                'Number'       => $row['dataNumber'],
                'Validity'     => $row['dataValidity'],
                'Volume'       => $row['dataVolume'],
                'Price'        => $row['dataPrice']
            );
        }

        if($smsId != $row['smsId']) {
            $smsIndex++;
            $smsId = $row['smsId'];

            //add the model to the console
            $telcos[$telcoIndex]['SMS Promo'][$smsIndex]['SMS Name'] = $row['smsName'];

            //setup the title array
            $telcos[$telcoIndex]['SMS Promo'][$smsIndex]['SMS'] = array();

            //add the game to the current console and model
            $telcos[$telcoIndex]['SMS Promo'][$smsIndex]['SMS'][] = array(
                'Keyword'      => $row['smsKeyword'],
                'Description'  => $row['smsDescription'],
                'Number'       => $row['smsNumber'],
                'Validity'     => $row['smsValidity'],
                'Price'        => $row['smsPrice']
            );
        }
    }

    mysqli_free_result($result);
}

echo json_encode($telcos);

mysqli_close($mysqli);
?>

I really don't know why this is happening.

491

Answer

Solution:

yep Scott was right, that snippet was just the tip of the ice berg, so I finish what he suggested eliminating the redundant entries leaving with unique ones. https://gist.github.com/jwara/fdc805240cf03027ae20 -- code is kind of not clean but subject to optimizations

http://cl.ly/image/2I3v2O29341J/o

644

Answer

Solution:

It looks telcoId is primary unique in telco table, but can be multiple in other tables (which makes sense), unfortunately if you try to join single (1) to multiple1 (2) to multiple2 (2) to multiple3 (3), it'd create 1 * 2 * 2 * 3 = 12 rows, which explains the messy multiple2 and multiple3.

So instead of doing that single DB call, you'll be looking at less efficiency by running separate queries, getting the telco info first, then making individual DB calls for each telco on telcoCall, telcoData, telcoSMS.

Or if you just want the speed and don't care about clean code, you can just use what you have right now, but stack up the if conditions:

EDIT 1: Edited to fix logic flaw, for some reason Gaussian elimination comes to mind

// Stacking order determined by your SQL ORDER BY: t.telcoName, c.callName, d.dataName, s.smsName

if($telcoId == $row['telcoId']) continue;
//telco stuff...

if($callId == $row['callId']) continue;
//call stuff...

if($dataId == $row['dataId']) continue;
//data stuff...

if($smsId == $row['smsId']) continue;
//sms stuff...

EDIT 2: Another slower way that requires less brainwork

if($telcoId != $row['telcoId']) {
    //telco stuff...
    callArrayTemp = array();
    dataArrayTemp = array();
    smsArrayTemp = array();
}
if(!in_array($row['callId'], callArrayTemp[])) {
    callArrayTemp[] = $row['callId'];
    //call stuff...
}
if(!in_array($row['dataId'], dataArrayTemp[])) {
    dataArrayTemp[] = $row['dataId'];
    //data stuff...
}
if(!in_array($row['smsId'], smsArrayTemp[])) {
    smsArrayTemp[] = $row['smsId'];
    //sms stuff...
}

People are also looking for solutions to the problem: TideSDK Desktop: How to create a login system with PHP session?

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.