SitePoint Sponsor

User Tag List

Results 1 to 20 of 20
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2010
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how to retrieve mysql data into json using php

    hi all i have 3 table in mysql database related to foreign key i want to extract the data from mysql in json i.e
    Code:
    [ "table1"
    	{
    		"tab1_id": "1",
    		"name": "donut",
    		"overview": "Cake",
    		"category": 4,
                    "image": imageURL,
    		"table2":
    			{
    				"table_name":
    					[
    						{ "id": "1","text":"sometext","amount":"4" },
                                                    { "id": "2","text":"sometext","amount":"3" },
    						{ "id": "3","text":"sometext","amount":"4" },
    						{ "id": "4","text":"sometext","amount":"4" },
    						
    					]
    			},
    		"table3":
    			[ 
                                "table_name" 
           				{ "id": "1", "text": "sometext" },
    				{ "id": "2", "text": "sometext" },
    				{ "id": "3", "text": "sometext" },
    			]
    	}]
    i have search for this for last two days but could't get the clue
    any suggestion or help would be greatly appreciated.

  2. #2
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,687
    Mentioned
    100 Post(s)
    Tagged
    4 Thread(s)
    It looks like your data structure is broken. What it should look like is for there to be three table properties, each which have their own data. It's not normally a good idea to have name1, name2, name3, as in the table names. Instead you should just use array indexes instead.

    For example, $data could be

    Code:
    [
        {
            "tab1_id": "1",
            ...
        },
        {
            "table_name":
                [...]
        },
        {
            "table_name":
                [...]
        }
    ]
    So the first table could be $data[0], the second table $data[1] and the third table $data[2]

    If you really want to use table1, table2, table3 then your JSON data could be like this:

    Code:
    [
        "table1": {
            "tab1_id": "1",
            ...
        },
        "table2": {
            "table_name":
                [...]
        },
        "table3": {
            "table_name":
                [...]
        }
    ]
    Then you can retrieve the data with $data['table1'], the second table $data['table2'] and the third table $data['table3']
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2010
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    but how can i get the resulted json from mysql using php.

    my code is

    Code:
    $result=array();
    $table_first = 'recipe';
    $query = "SELECT * FROM $table_first";
    $resouter = mysql_query($query, $conn);
    
    
    
    while ($recipe = mysql_fetch_assoc($resouter, MYSQL_ASSOC)){
       $result['recipe']=$recipe;
    
    $query2="SELECT ingredients.ingredient_id,ingredients.ingredient_name,ingredients.ammount FROM ingredients where rec_id = ".$recipe['rec_id'];
    $result2 = mysql_query($query2, $conn);
    
     while($ingredient = mysql_fetch_assoc($result2)){
         
         
            $result['ingredient']= $ingredient;
         
     }
      //echo json_encode($result);
    }
      echo json_encode($result);

    but it show only one record

    Code:
    {"recipe":{"rec_id":"23","name":"this is test recipe for category","overview":"category overview","category":"4","time":"2002-12-10 13:30:39","image":"http:\/\/www.localhost\/cafe\/pics\/logout (1).gif"},"ingredient":{"ingredient_id":"59","ingredient_name":"test recipe","ammount":"4 gm"}}

  4. #4
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,687
    Mentioned
    100 Post(s)
    Tagged
    4 Thread(s)
    You can go to the website to find out. Really.
    http://www.json.org/
    Scroll down and you'll find many PHP JSON libraries.

    However, it's also built in to PHP 5.2 onwards as well, where you can use json_deconde() to decode it.
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  5. #5
    SitePoint Enthusiast
    Join Date
    Nov 2010
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sir i want to encode mysql data into json


    Code:
    $result=array();
    $table_first = 'recipe';
    $query = "SELECT * FROM $table_first";
    $resouter = mysql_query($query, $conn);
    while ($recipe = mysql_fetch_assoc($resouter, MYSQL_ASSOC)){
       $result[]=$recipe;
    }
    this is for retrieving data from one table in json but what should i do if i want to retrieve from two table or more(related to each other using foreign key).

  6. #6
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,687
    Mentioned
    100 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by semanticnotion View Post
    sir i want to encode mysql data into json
    Then use json_encode

    Quote Originally Posted by semanticnotion View Post
    this is for retrieving data from one table in json but what should i do if i want to retrieve from two table or more(related to each other using foreign key).
    That depends on how you want to process them once they are decoded. The easiest way is for each table to be a single array item in a larger array.

    Code php:
    $data = array();
    array_push($array1);
    array_push($array2);
    $json = json_encode($data);
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  7. #7
    SitePoint Enthusiast
    Join Date
    Nov 2010
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sir i used this code to retrieve data from two tables
    Code:
    $result=array();
    $table_first = 'recipe';
    $query = "SELECT * FROM $table_first";
    $resouter = mysql_query($query, $conn);
    
    
    
    while ($recipe = mysql_fetch_assoc($resouter, MYSQL_ASSOC)){
       $result['recipe']=$recipe;
    
       
    $query2="SELECT ingredients.ingredient_id,ingredients.ingredient_name,ingredients.ammount FROM ingredients where rec_id = ".$recipe['rec_id'];
    $result2 = mysql_query($query2, $conn);
    
     while($ingredient = mysql_fetch_assoc($result2)){
         
         
            $result['ingredient']= $ingredient;
         
     }
      echo json_encode($result);
    }
    But this retrieved only last ingredient for first recipe id and there are 5 relevant records in ingredient table against this recipe id i.e
    the out put is
    Code:
    {"recipe":{"rec_id":"14","name":"Spaghetti with Crab and Arugula","overview":"http:\/\/www","category":"","time":"2010-11-11 14:35:11","image":"localhost\/pics\/SpaghettiWithCrabAndArugula.jpg"},"ingredient":{"ingredient_id":"55","ingredient_name":"test","ammount":"2 kg"}}{"recipe":{"rec_id":"15","name":"stew recipe ","overview":"http:\/\/www","category":"","time":"2010-11-11 14:42:09","image":"localhost\/pics\/stew2.jpg"},"ingredient":{"ingredient_id":"25","ingredient_name":"3 parsnips cut into cubes","ammount":"11"}}

  8. #8
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,687
    Mentioned
    100 Post(s)
    Tagged
    4 Thread(s)
    The fault is of this line here:

    Code:
    $result['recipe']=$recipe;
    Each time that line is executed, it replaces whatever used to be there.

    If you want to add each recipe to the result array, set up an empty array for the results:

    Code php:
    $result = array(
        'recipe' => array(),
        'ingredient' => array()
    );

    then use the [] notation

    Code php:
    $result['recipe'][] = $recipe;

    or array_push(), whichever you like better.


    Code php:
    array_push($result['recipe'], $recipe);
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  9. #9
    SitePoint Enthusiast
    Join Date
    Nov 2010
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your deep attention sir i used your above code but still it does't work
    i want the format should like ( see the my question post) but it shows the data like
    {"recipe":[{"rec_id":"14","name":"Spaghetti with Crab and Arugula","overview":"http:\/\/www","category":"","time":"2010-11-11 <br/>14:35:11","image":"localhost\/pics\/SpaghettiWithCrabAndArugula.jpg"}],<br/>"ingredient":[],"0":{"ingredient":{"ingredient_id":"7","ingredient_name":"13 ounces spaghetti","ammount":"10 kg"}},"1":{"ingredient":{"ingredient_id":"8","ingredient_name":"1 pound crabmeat","ammount":"10"}},"2":{"ingredient":{"ingredient_id":"9","ingredient_name":"7 ounces arugula, washed","ammount":"10"}},"3"br/>{"ingredient":{"ingredient_id":"10","ingredient_name":"2 cloves garlic, finely chopped","ammount":"10"}},"4":{"ingredient":{"ingredient_id":"11","ingredient_name":"1 chili pepper, seeded and finely chopped","ammount":"10"}},"5":{"ingredient":{"ingredient_id":"12","ingredient_name":"4 tablespoons lemon juice","ammount":"10"}},"6"br/>{"ingredient":{"ingredient_id":"13","ingredient_name":"6 tablespoons olive oil","ammount":"10"}},"7":{"ingredient":{"ingredient_id":"14","ingredient_name":"Salt and pepper to taste","ammount":"10"}},"8":{"ingredient":{"ingredient_id":"55","ingredient_name":"test","ammount":"2 kg"}}}<br/>{"recipe":[{"rec_id":"14","name":"Spaghetti with Crab and Arugula","overview":"http:\/\/www","category":"","time":"2010-11-11 14:35:11","image":"localhost\/pics\/SpaghettiWithCrabAndArugula.jpg"},
    i think sir we are near to the solution because the relevant ingredients are now retrieved but there is little problem.

    i used
    Code:
    while ($recipe = mysql_fetch_assoc($resouter, MYSQL_ASSOC)){
       $result['recipe'][]=$recipe;
    and
    Code:
    while($ingredient = mysql_fetch_assoc($result2)){
         
         
            $result[]['ingredient']= $ingredient;

  10. #10
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,687
    Mentioned
    100 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by semanticnotion View Post
    the relevant ingredients are now retrieved but there is little problem
    Can you explain the problem?
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  11. #11
    SitePoint Enthusiast
    Join Date
    Nov 2010
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The problem is it give me first the recipe array and then ingredients and what i want to do is ingredient array should be inside recipe array.
    also this print out some extra records i.e (see my out put quote).

  12. #12
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,687
    Mentioned
    100 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by semanticnotion View Post
    The problem is it give me first the recipe array and then ingredients and what i want to do is ingredient array should be inside recipe array.
    Will the JSON data contain multiple recipies, or will there only ever be one recipe that the JSON data handles.
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  13. #13
    SitePoint Enthusiast
    Join Date
    Nov 2010
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well sir i edit my code a little and it fulfill my requirement, like it retrieve first recipe then all the ingredient against that specific recipe and also all the instruction.
    But the problem now is just the json format.
    i show you my queries and my output
    Code:
    $result=array();
    $table_first = 'recipe';
    $query = "SELECT * FROM $table_first";
    $resouter = mysql_query($query, $conn);
    while ($recipe = mysql_fetch_assoc($resouter, MYSQL_ASSOC)){
       $result[]=$recipe;
    
    
       
    $query2="SELECT ingredients.ingredient_id,ingredients.ingredient_name,ingredients.ammount FROM ingredients where rec_id = ".$recipe['rec_id'];
    $result2 = mysql_query($query2, $conn);
    
     while($ingredient = mysql_fetch_assoc($result2)){
         
         
            $result[]= $ingredient;
         
     }
    $query="SELECT instructions.instruction_id,instructions.instruction_text FROM instructions where rec_id = ".$recipe['rec_id'];
    $resinner=mysql_query($query, $conn);
    while($instruction=  mysql_fetch_assoc($resinner))
    {
        $result[]=$instruction;
    }
      }
     echo json_encode($result);
    and the result for recipe id 14 is
    [{"rec_id":"14","name":"Spaghetti with Crab and Arugula","overview":"http:\/\/www","category":"","time":"2010-11-11 14:35:11","image":"localhost\/pics\/SpaghettiWithCrabAndArugula.jpg"},{"ingredient_id":"7","ingredient_name":"13 ounces spaghetti","ammount":"10 kg"},{"ingredient_id":"8","ingredient_name":"1 pound crabmeat","ammount":"10"},{"ingredient_id":"9","ingredient_name":"7 ounces arugula, washed","ammount":"10"},{"ingredient_id":"10","ingredient_name":"2 cloves garlic, finely chopped","ammount":"10"},{"ingredient_id":"11","ingredient_name":"1 chili pepper, seeded and finely chopped","ammount":"10"},{"ingredient_id":"12","ingredient_name":"4 tablespoons lemon juice","ammount":"10"},{"ingredient_id":"13","ingredient_name":"6 tablespoons olive oil","ammount":"10"},{"ingredient_id":"14","ingredient_name":"Salt and pepper to taste","ammount":"10"},{"ingredient_id":"55","ingredient_name":"test","ammount":"2 kg"},{"instruction_id":"14","instruction_text":"Cook spaghetti according to directions on package. Drain and set aside."},{"instruction_id":"15","instruction_text":"In a large saucepan, heat olive oil. Add garlic and chili and saute for 30 seconds. Stir in crabmeat and lemon juice. Season with salt and pepper. Cook for 3-5 minutes, stirring constantly. Add arugula leaves."},{"instruction_id":"16","instruction_text":"Add pasta and toss to coat. Adjust taste with salt and pepper if necessary. Serve immediately."}
    i just want to the ingredient array and instruction array should be inside the recipe array but look at my result when the recipe array finished then the ingredient array start
    i want this to be inside in recipe array i.e
    {"rec_id":"14","name":"Spaghetti with Crab and Arugula","overview":"http:\/\/www","category":"","time":"2010-11-11 14:35:11","image":"localhost\/pics\/SpaghettiWithCrabAndArugula.jpg",
    {then ingredient close here } and { instruction and recipe close here }}

  14. #14
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,687
    Mentioned
    100 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by semanticnotion View Post
    i just want to the ingredient array and instruction array should be inside the recipe array but look at my result when the recipe array finished then the ingredient array start
    i want this to be inside in recipe array i.e
    So you don't need to start with an array at all.

    You can use:

    Code php:
    $result = $recipe;

    Then append the ingredients on to it:

    Code php:
    $result[] = $instruction;
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  15. #15
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    The problem has nothing to do with JSON. The problem is that you don't have the rpoper data structure to begin with. Once the proper data format is achieved than json_encode() will do the dirty work of converting it to JSON for you. Though, you must first have the intended array structure.

    is your friend:
    PHP Code:
    echo '<pre>',print_r($array_value),'</pre>'

  16. #16
    SitePoint Enthusiast
    Join Date
    Nov 2010
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by pmw57 View Post
    So you don't need to start with an array at all.

    You can use:

    Code php:
    $result = $recipe;
    This will out put just a single recipe no ingredients and no instruction is there.

  17. #17
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,687
    Mentioned
    100 Post(s)
    Tagged
    4 Thread(s)
    Just noticed: that should be:

    Code php:
    $result['ingredients'][] = $instruction;
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  18. #18
    SitePoint Enthusiast
    Join Date
    Nov 2010
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    The problem has nothing to do with JSON. The problem is that you don't have the rpoper data structure to begin with. Once the proper data format is achieved than json_encode() will do the dirty work of converting it to JSON for you. Though, you must first have the intended array structure.
    Then please guide me how to achieve the correct format cause it suffer me for the couple of days and still unable to get.

  19. #19
    SitePoint Enthusiast
    Join Date
    Nov 2010
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Both does't work pmw57
    it just the problem of closing brace of recipe table.

  20. #20
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Its pretty basic stuff. Trial and error. Like I said, you can use print_r() to check the data structure being built. Take a little initiative.


Bookmarks

Posting Permissions

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