Seeking Guidance on Basic JSON Query

PHP
1

This is kind of a lame question because I don’t have a problem. I just recently started learning about JSON, and I wanted to figure out how to convert a PDO database query to JSON.

To my surprise, my very first attempt worked…

$stm = $pdo->prepare("SELECT *
  FROM $DBPolitix.pox_topics");
 $stm->execute(array(
));

$results=$stm->fetchAll(PDO::FETCH_ASSOC);
$json=json_encode($results);

echo $json;

It appears to be displaying all the data from the table px_topics in a big jumbled mass that looks like this:

{“N”:“8”,“URL”:“political-compass”,“Title”:“Political Compass”,“Subtitle”:“The Political Spectrum”,“Parent”:“politix-101”,“MetaTitle”:“”,“MetaDesc”:“”,“KW”:“”,“Previous”:“topics/left-vs-right”,“Next”:“topics/morality”,“Site”:“PX”,“Section”:“Topics”,“Series”:“PX-101”,“TestID”:“PX-101-7”,“Category”:“”,“Age”:“”,“Live”:“1”,“G1”:“1”},{“N”:“9”,“URL”:“morality”,“Title”:“Morality”,“Subtitle”:“The Foundation of a Whole Person”,“Parent”:“politix-101”,“MetaTitle”:“”,“MetaDesc”:“”,“KW”:“”,“Previous”:“topics/political-compass”,“Next”:“topics/good-and-evil”,“Site”:“PX”,“Section”:“Topics”,“Series”:“PX-101”,“TestID”:“PX-101-8”,“Category”:“”,“Age”:“”,“Live”:“1”,“G1”:“1”},{“N”:“10”,“URL”:“good-and-evil”

But before I continue and learn how to put all that stuff in a table, I’d like to ask about the script I posted above. Is it really the best way to go, or is there some subtle change I should make, or do you have any recommendations for improving it?

All I want to do is recreate the database tables I’m currently displaying in a format that will allow me to display them via AJAX. Many of my queries feature table joins and WHERE clauses.

It’s just so strange for me to get something right on the first try, I want to make sure I’m not missing something before I continue. :wink:

Thanks.

2

That jumbled mess as you call it is more commonly known as JSON.

As it is system generated for system use it is leaving out the line feeds between the key/value pairs that would make the JSON more readable.

3

Yes, I know the next step is to learn how to display that “jumbled mess” in a readable format. I just wanted to make sure I’m on the right track. I usually make some mistake that I don’t find out about until I’m another ten miles down the road. :wink:

4

I wouldn’t think so - why do you want to display JSON? It is a format for transferring data, there is no need to actually display it - it is more readable to the receiving code if you don’t tamper with it.

5

What you should do is learn how to read it so you can use it.

Compared to some that is actually quite simple. It’s the ones that have objects nested in objects nested in objects , that get difficult. And that is a collection of objects consisting of only property value pairs, no nesting.

Basically
curly braces indicate an object = {}
brackets indicate an array - :
parentheses a string - “”
digits - digits
colons “connect” the property value pair
commas separate the property value pairs

Maybe this diagram will help
http://www.json.org/

For example, if you have
{{"property1":"value1","property2":"value2"},{"property1":"value1","property2":"value2"}}

and you want to have JavaScript show value2s on the page, you could do something like

var first_prop2 = response[0]['property2'];
var second_prop2 = response[1]['property2'];

*zero to n because the collection of objects don’t have property names here, so they’re numeric.

2 Likes
6

Thanks.

Another question: You can’t store PHP code in a database table, which means you can’t display dynamic data (e.g. tables) inside articles stored in databases. But could I make a dynamic table display inside an article if I’m working with JSON?

7

I’m unclear about what exactly you mean.

Say there is
database with a “names” table with “first_name” and “last_name” fields.
Using PHP I could do something like
SELECT first_name, last_name FROM names WHERE first_name LIKE :first
and use PHP to get the results, create and output a page .

If I wanted to gather different results I would make sure that if JavaScript didn’t work a page with the different results would still work.

Then if I wanted to have JavaScript change the page’s results without a new page loading I would send a request yo a PHP file that similarly queried the database, BUT instead of having the PHP output a new page, it would return the results in JSON format and the JavaScript could use that to modify the page.

The trick is to keep things separated enough and not tied to anything too much so that the data can be “consumed” in as many ways possible that might at some point be desired. eg.

PHP, JavaScript, XML, CSV, email, etc.

8

Ah, that makes sense - using different tools to accomplish the same task in case one tool doesn’t work.

I was referring more to a dynamic table. Suppose I have an article about carnivorans (cats, dogs, etc.) stored in my database. I’d like to display a table listing every living species of carnivoran in the middle of the article. But I don’t want to embed a static table in the article for some reason; maybe I want the table to be automatically updated as species names change, species become extinct, etc.

I can’t use PHP to insert a database query in the middle of some text stored in a database. But is there a way to do this using JSON?

Suppose I inserted a code word like $Table in the test. Could I then use php’s str_replace function to replace $Table with data from my database table that has been converted to JSON?

9

I’m again unclear about what exactly you mean. (Sorry)

It would be a simple matter to have a PHP file something like
carnivora.php

blah, blah</p>
</div>
<?php 
include fetch-order.php;
$rows = (isset($_POST['n'])) ? (int) trim($_POST['n']) : 10;
display('Carnivora', $rows);
?>
<div>
<p>Blah, blah

This could output an HTML page with a table with 10 rows of carnivores

Once the page with the table of information is in the browser you could make an HTTP request to
carnivora.php?n=20
to load a page with a table having 20 rows

Or you could use JavaScript go to a PHP that returned more rows in JSON and modify the same table with the returned data.

10

Thanks; those are some good ideas.

11

Try this:

function fred($json = '{"Title: ": "No Value???"}', $fType=0)
{
    $jsonObj = is_object($json) ? $json :  json_decode($json);

    echo '<pre>' .$fType .' == ';
        switch($fType):
            
            case  0: 
            default:
                echo '<b>print_r($jsonObj)</b><br />';
                print_r($jsonObj); 

            case  1:  
                echo '<b>print_r($jsonObj, true)</b><br />';
                echo print_r($jsonObj, true);  // string
            break;

            case  2: 
                echo '<b>var_dump($jsonObj)</b><br />';
                var_dump($jsonObj); // object
            break;
            
            case  1:
                echo '<b>var_dump($jsonObj,true)</b><br />';
              var_dump($jsonObj,1); // array
            break;

        endswitch;    
    echo '</pre>';
}

    $json = '
        {"N":"8", 
            "URL":"political-compass", "Title":"Political Compass", "Subtitle":"The Political Spectrum",
            "Parent":"politix-101", "MetaTitle":"", "MetaDesc":"", "KW":"", "Previous":"topics\/left-vs-right",
            "Next":"topics\/morality", "Site":"PX", "Section":"Topics", "Series":"PX-101","TestID":"PX-101-7",
            "Category":"", "Age":"", "Live":"1", "G1":"1"}
        ';

echo '$json == ' .$json;        

for($i=0; $i<=3; $i++):
    fred($json,    $i);    
endfor;

Output:

$json ==
{“N”:“8”,
“URL”:“political-compass”, “Title”:“Political Compass”, “Subtitle”:“The Political Spectrum”, “Parent”:“politix-101”, “MetaTitle”:“”, “MetaDesc”:“”, “KW”:“”, “Previous”:“topics/left-vs-right”, “Next”:“topics/morality”, “Site”:“PX”, “Section”:“Topics”, “Series”:“PX-101”,“TestID”:“PX-101-7”, “Category”:“”, “Age”:“”, “Live”:“1”, “G1”:“1”}

13

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.