How to build a JSON array from mysql database

Question 1

Okay I have been racking my brain trying to build a JSON array from mysql. The array MUST be in the following format. I am using fullcalendar and want to make the events on the calendar dynamic. Below is the code that builds the array, but currently it does not get the information from mysql

$year = date('Y');
$month = date('m');

echo json_encode(array(

    //Each array below must be pulled from database
        //1st record
        array(
        'id' => 111,
        'title' => "Event1",
        'start' => "$year-$month-10",
        'url' => "http://yahoo.com/"
    ),

         //2nd record
         array(
        'id' => 222,
        'title' => "Event2",
        'start' => "$year-$month-20",
        'end' => "$year-$month-22",
        'url' => "http://yahoo.com/"
    )

));

Question 2

Is something like this what you want to do?

$return_arr = array();

$fetch = mysql_query("SELECT * FROM table"); 

while ($row = mysql_fetch_array($fetch, MYSQL_ASSOC)) {
    $row_array['id'] = $row['id'];
    $row_array['col1'] = $row['col1'];
    $row_array['col2'] = $row['col2'];

    array_push($return_arr,$row_array);
}

echo json_encode($return_arr);

It returns a json string in this format:

[{"id":"1","col1":"col1_value","col2":"col2_value"},{"id":"2","col1":"col1_value","col2":"col2_value"}]

OR something like this:

$year = date('Y');
$month = date('m');

$json_array = array(

//Each array below must be pulled from database
    //1st record
    array(
    'id' => 111,
    'title' => "Event1",
    'start' => "$year-$month-10",
    'url' => "http://yahoo.com/"
),

     //2nd record
     array(
    'id' => 222,
    'title' => "Event2",
    'start' => "$year-$month-20",
    'end' => "$year-$month-22",
    'url' => "http://yahoo.com/"
)

);

echo json_encode($json_array);

Question 3

Using a big text file to store serialized (no matter what kind of serialization) means :

No need to modify the schema to add / remove columns
Ability to store pretty much anything you want
BUT : you will not be able to work with those data on the MySQL side -- especially, you won't be able to use those in a where clause.
Basically, this means using MySQL to store data -- and nothing more than storage

If you only want to use those data on the PHP side, and never have to work with the on the SQL side, I suppose that storing everything a big text field is a solution.
(and, in this case, I don't see why it would be slower than another solution)

Question 4

You are better off using a JOIN query, and then structure your array from the result - having a query within a loop is often a very bad idea, and an indicator that you can use a JOIN instead.

You want to use a LEFT JOIN, joining them on the land_contract_id in both tables.

Then loop your results, and construct your array, which you can end up encoding into a JSON string once done.

$params = [];
$array = [];

$sql = "SELECT lc.*, 
               py.land_contract_annual_price_year AS `year`,  
               py.land_contract_annual_price_amount AS `amount`
        FROM land_contract AS lc
        LEFT JOIN land_contract_annual_price AS py 
            ON py.land_contract_id = lc.land_contract_id
        ";
if (isset($_POST['land_contract_id'])) {
    $sql .= 'WHERE lc.land_contract_id = ?';
    $params[] = $_POST["land_contract_id"];
}

$stmt = $pdo->prepare($sql);
$stmt->execute($params);
while ($row = $stmt->fetch()) {
    // Fields we want to extract from the select statement into the array 
    $select_fields = ['land_contract_id', 'land_contract_name', 'location_id', 'land_contract_link', 'land_contract_notes', 'land_owner_id', 
                        'land_contract_start_date', 'land_contract_end_date', 'land_contract_terminated', 'land_contract_payment_interval', 
                        'land_contract_price_type', 'land_contract_fixed_annual_price '];

    if (!isset($array[$row['land_contract_id']])) {
        // initialize the subarray if it has not been set already 
        $array[$row['land_contract_id']] = array_intersect_key($row, array_flip($select_fields));

        if ($row['year'] != null) {
            $array[$row['land_contract_id']]['land_contract_annual_prices'] = [];
        } else {
            $array[$row['land_contract_id']]['land_contract_annual_price'] = $row['land_contract_fixed_annual_price'];
        }
    }

    if ($row['year'] != null) {
        $array[$row['land_contract_id']]['land_contract_annual_prices'][] = ['year' => $row['year'], 'amount' => $row['amount']];
    }

}

if (empty($array)) {
    echo "No results";
    exit;
}

echo json_encode($array, JSON_UNESCAPED_UNICODE);

Question 5

What have you tried?

Try this approach:

Convert JSON to PHP array (json_decode())
Loop through the array, get the key and value for each entry (foreach(){}, array_keys())
Create a single string with an insert and add VALUES() for each row

Execute the query after the loop

$keys = array_keys($array);              // get the value of keys
$rows = array();                         // create a temporary storage for rows
foreach($keys as $key) {                 // loop through
    $value = $array[$key];               // get corresponding value
    $rows[] = "('" . $key . "', '" . $value . "')";
                                         // add a row to the temporary storage 
}
$values = implode(",", $rows);           // 'glue' your rows into a query
$query = "INSERT INTO ... VALUES " . $values;
                                         // write the rest of your query
...                                      // execute query

As soon as you find a concrete question, feel free to open another post.

Question 6

You are trying to access url not a file, Use CURL instead to access file direct.

Replace

        <?php
           $latlong = $row[1].','.$row[2];
           $geocode=json_decode(file_get_contents('http://maps.googleapis.com/maps/api/geocode/json?latlng=19.0978,22.8972&sensor=false',true));            
        ?>

To

<?php
    $url = "http://maps.googleapis.com/maps/api/geocode/json?latlng=$row[1],$row[2]&sensor=false";
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, $url);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
    $output = curl_exec($ch);
    curl_close($ch);
    $geocode = json_decode($output);
?>

How to build a JSON array from mysql database

Answers

php,mysql,json