0

I'm trying to set up a PHP file to handle crud operations for a certain type of data - "land contracts". There are currently 4 situations when I want to read from my database:

  1. When acion="read" and land_contract_id is set, then echo that land contract
  2. When acion="create", then echo that land contract after it has been created
  3. When acion="update", then echo that land contract after it has been updated
  4. When acion="read" and land_contract_id is not set, then echo all land contract

My current set up works fine for situation 1, 2 and 3. However, I can't call my read function without a parameter. I.e in situation 4, when $land_contract_id is not set.

How should I restructure my set up to work in all cases when I want to read from my database?

Below is a simplified version of my set up.

<?php

    $action = isset($_POST["action"]) ? $_POST["action"] : '';

    $land_contract_id = isset($_POST["land_contract_id"]) ? $_POST["land_contract_id"] : '';

    switch ($action) {
      case "create":

        // Here I'm creating a new land contract and getting the last inserted id with $pdo->lastInsertId()

        read($last_inserted_land_contract_id);

        break;
      case "read":

        if (isset($_POST['land_contract_id'])) {
          read($land_contract_id);
        }

        break;
      case "update":

        // Here I'm updating an existing land contract

        read($land_contract_id);

        break;
      case "delete":

        break;
    }

    function read(land_contract_id) {

      // Fetching land contract from database and echoing as JSON

    }

?>

-- UPDATE --

Full code below. Updated according to answer from @Jeff. Line $stmt = $pdo->prepare($sql); in function read gives me Fatal error: Uncaught Error: Call to a member function prepare() on null.

<?php

header("Content-Type: application/json; charset=UTF-8");

header("Access-Control-Allow-Origin: *");

require_once("./pdo_connect.php");

$action = isset($_POST["action"]) ? $_POST["action"] : '';
$land_contract_id = isset($_POST["land_contract_id"]) ? $_POST["land_contract_id"] : '';
$land_contract_name = isset($_POST["land_contract_name"]) ? $_POST["land_contract_name"] : '';
$location_id = isset($_POST["location_id"]) ? $_POST["location_id"] : '';
$land_contract_link = isset($_POST["land_contract_link"]) ? $_POST["land_contract_link"] : '';
$land_contract_notes = isset($_POST["land_contract_notes"]) ? $_POST["land_contract_notes"] : '';
$land_owner_id = isset($_POST["land_owner_id"]) ? $_POST["land_owner_id"] : '';
$land_contract_start_date = isset($_POST["land_contract_start_date"]) ? $_POST["land_contract_start_date"] : '';
$land_contract_end_date = isset($_POST["land_contract_end_date"]) ? $_POST["land_contract_end_date"] : '';
$land_contract_terminated = isset($_POST["land_contract_terminated"]) ? $_POST["land_contract_terminated"] : 'false';
$land_contract_payment_interval = isset($_POST["land_contract_payment_interval"]) ? $_POST["land_contract_payment_interval"] : '';
$land_contract_price_type = isset($_POST["land_contract_price_type"]) ? $_POST["land_contract_price_type"] : '';
$land_contract_fixed_annual_price = isset($_POST["land_contract_fixed_annual_price"]) ? $_POST["land_contract_fixed_annual_price"] : '';
$land_contract_variable_annual_price_year = isset($_POST["land_contract_variable_annual_price_year"]) ? $_POST["land_contract_variable_annual_price_year"] : '';

switch ($action) {
    case "create":

        // Insert land_contract to database
        $stmt1 = $pdo->prepare("INSERT INTO land_contract (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) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        $stmt1->execute([$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]);

        // Get id of last inserted land_contract
        $last_inserted_land_contract_id = $pdo->lastInsertId();


        // Check if there are any variable annual prices and if so, insert them into land_contract_annual_price
        if ( isset( $_POST['land_contract_variable_annual_price_year'] ) ) {
            $years = $_POST ['land_contract_variable_annual_price_year'] ;
            if( is_array( $years ) ) {
                foreach ( $years as $year => $price ) {

                    $price_with_decimals = number_format((float)$price, 2, '.', '');

                    // Insert the variable annual prices into land_contract_annual_price
                    $stmt2 = $pdo->prepare("INSERT INTO land_contract_annual_price (land_contract_annual_price_year, land_contract_annual_price_amount, land_contract_id) VALUES (?, ?, ?)");
                    $stmt2->execute([$year, $price_with_decimals, $last_inserted_land_contract_id]);

                }
            }
        }

        read($last_inserted_land_contract_id);

        // Reset statements
        $stmt1 = null;
        $stmt2 = null;

        break;
    case "read":

        if ( isset($_POST['land_contract_id']) ) {
            read($land_contract_id);
        }
        else {
            read();
        }

        break;
    case "update":

        $row = [
            'land_contract_id' => $land_contract_id,
            'land_contract_name' => $land_contract_name,
            'location_id' => $location_id,
            'land_contract_link' => $land_contract_link,
            'land_contract_notes' => $land_contract_notes,
            'land_owner_id' => $land_owner_id,
            'land_contract_start_date' => $land_contract_start_date,
            'land_contract_end_date' => $land_contract_end_date,
            'land_contract_terminated' => $land_contract_terminated,
            'land_contract_payment_interval' => $land_contract_payment_interval,
            'land_contract_price_type' => $land_contract_price_type,
            'land_contract_fixed_annual_price' => $land_contract_fixed_annual_price,
            'land_contract_variable_annual_price_year' => $land_contract_variable_annual_price_year

        ];

        $stmt = $pdo->prepare("UPDATE land_contract SET land_contract_name=:land_contract_name, location_id=:location_id, land_contract_link=:land_contract_link, land_contract_notes=:land_contract_notes, land_owner_id=:land_owner_id, land_contract_start_date=:land_contract_start_date, land_contract_end_date=:land_contract_end_date, land_contract_terminated=:land_contract_terminated, land_contract_payment_interval=:land_contract_payment_interval, land_contract_price_type=:land_contract_price_type, land_contract_fixed_annual_price=:land_contract_fixed_annual_price, land_contract_variable_annual_price_year=:land_contract_variable_annual_price_year WHERE land_contract_id=:land_contract_id");
        $stmt->execute($row);

        read($land_contract_id);

        $stmt = null;
        break;
    case "delete":
        $stmt = $pdo->prepare("DELETE FROM land_contract WHERE land_contract_id = ?");
        $stmt->execute([$land_contract_id]);
        $stmt = null;
        break;
}

function read($land_contract_id = null) {

    $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 ($land_contract_id) {
        $sql .= 'WHERE lc.land_contract_id = ?';
        $params[] = $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);

    $stmt = null;
}

?>
Rawland Hustle
  • 781
  • 1
  • 10
  • 16
  • Normally the various CRUD methods would send various request types, looks as though you are coding them all as POST. – Nigel Ren Jul 26 '19 at 15:34
  • Thanks. I've never understood why I should make a GET request for getting data, when POST works just as fine. Can you explain? – Rawland Hustle Jul 26 '19 at 16:05
  • It's not always a case of it just works - more a case of convention. Just the same as coding standards don't make code work any better, but it makes it easier to work with other peoples code. – Nigel Ren Jul 26 '19 at 16:09
  • @NigelRen I got you! Should I declare two versions of $land_contract_id then, like this? `$land_contract_id = isset($_POST["land_contract_id"]) ? $_POST["land_contract_id"] : '';` and `$land_contract_id = isset($_GET["land_contract_id"]) ? $_GET["land_contract_id"] : '';`. One for reading, and another for creating, updating and deleting? – Rawland Hustle Jul 26 '19 at 16:14
  • You need to pass `$pdo` as an argument to your function `read()` too. – Dharman Jul 26 '19 at 17:40
  • @Dharman. Do I need to pass `$pdo` as an argument *everytime* I call `read()`? Why do I need to pass it? – Rawland Hustle Jul 26 '19 at 19:12
  • Yes. Otherwise `$pdo` will be undefined. In PHP functions have their own scope and variables from global scope are not inherited. You need to pass all relevant arguments to the function. – Dharman Jul 26 '19 at 19:14
  • @Dharman That got rid of the error. I had no idea PHP worked like this. Thanks! Do you know why `read()` return *all* rows even when I pass $Id as a variable? – Rawland Hustle Jul 26 '19 at 19:21
  • It shouldn't have. You need to do some debugging to see what the SQL is and if the value is passed in correctly. – Dharman Jul 26 '19 at 19:23
  • @Dharman Okay, I'll try to understand what's going on! I just noticed that the outputted JSON is an object (called JSON) with objects (rows/land contracts) in it and all those objects are named by their Id. Is that bad? Will I be able to iterate the objects with Javascript? Look at these two images: https://imgur.com/a/krIv1u7. – Rawland Hustle Jul 26 '19 at 19:39
  • Read about JSON. It is the common format for passing data between PHP and JS. [How do I pass variables and data from PHP to JavaScript?](https://stackoverflow.com/q/23740548/1839439) – Dharman Jul 26 '19 at 19:40
  • It is preferred if you can post separate questions instead of combining your questions into one. That way, it helps the people answering your question and also others hunting for at least one of your questions. Thanks! – Dharman Jul 26 '19 at 19:40
  • Okay, sorry! Thank you very much for your help! – Rawland Hustle Jul 26 '19 at 19:42

1 Answers1

-1

You'll need to modify your read() function to make the $land_contract_id optional:

function read($land_contract_id = null) {
    $sql = "SELECT * FROM land_contracts";

    if($land_contract_id) {
        $sql .= " WHERE land_contract_id = {$land_contract_id}";
    }

    $result = $database->select_rows($sql);
}

Now, $land_contract_id is optional. If you pass a value to read(), it will return only the selected contract, else if you leave it null/empty, then it will return all contracts.

Jeff
  • 1,152
  • 5
  • 14
  • 27