Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Mar 2013
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Encoded JSON data are displayed multiple times

    Good day.

    I am having a problem with my JSON data. The data are being displayed repeatedly that makes the output very long.

    I have 4 tables inside my database, telco for storing the names of telecommunications company. telcoCall, telcoData, and telcoSMS for its corresponding promos. The three of them have foreign keys with the telco table.

    I thought my code was already correct because I tested it with only 2 data inside telcoCall, telcoData and telcoSMS. They are fine, I promise. But yesterday, I decided to add all of their promos. Then bam! Each of the data inside telcoData and telcoSMS are displayed multiple times.

    This is my code:
    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;
    
    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);
    ?>
    Due to characters constrain, you can view the result here: Online JSON Viewer and please click on the Viewer tab to make the data more viewable.

    This is how it looks like,


    One more thing, I believe I am in the wrong section, though what I needed is a JSON data. Sorry for that.
    Last edited by technowar; 03-09-2013 at 05:34 PM.

  • #2
    New Coder
    Join Date
    Jan 2013
    Location
    Sunnyvale, CA
    Posts
    98
    Thanks
    6
    Thanked 7 Times in 7 Posts
    I don't program php, yet a quick review of your code suggests that your problem is in the SQL.

    Inner joins return multiple copies of the desired cells, since they denormalize the data.

    You may discover that rewriting your SQL will effectively resolve your problem.

    I recommend that you REFRAIN from resolving it in your php script; though effective, it is an abuse of server resources and should be crunched by your RDBMS.

    Hope it helps...


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •