Hi,

I have a query which retrieves the most recent end_date of a contract. It works when i enter it into a cmd window but not inside my actual script as when i do a var_dump it shows as NULL. I don't understand why it works in one but not the other.

Recently i have received an error message that said the end_date field was undefined (i have highlighted this line in bold along with the query that is returning null).

My code is as follows:

<?php
    include'db_config.inc';
    session_start();
    
    unset($_SESSION['OVR_ERR_MSG']);
    $_SESSION['OVR_ERR_MSG'] = '';
    
    $dbh = new PDO("mysql:host=127.0.0.1;dbname=$db", $user, $password);
    
    if(isset($_SESSION['SESS_USER'])) {
        $username = $_SESSION['SESS_USER'];
    }
    else {
        header("location: login.php");
    }
    
    $stmt = $dbh->prepare("SELECT MAX(end_date)
                          FROM Contract
                          WHERE cust_id = '$username'");
    $stmt->execute();
    $latestcontract = $stmt->fetch();
    $contractenddate = $latestcontract['end_date'];
    
    $_SESSION['end_date'] = $latestcontract['end_date'];
    $todaysdate = date("Y-m-d");
    $_SESSION['today'] = $todaysdate;
    
    if($contractenddate > $todaysdate){
        $_SESSION['OVR_ERR_MSG'] = 'You already have a contract. You can sign up to a new one when this one ends.';
        header("location: member_contract.php");
    }
    else {
        try{
            $duration = filter_var($_POST['duration'], FILTER_SANITIZE_NUMBER_INT);
            $currDay = date("d");
            $currMonth = date("m");
            $curYear = date("Y");
            $startdate1 = new DateTime($curYear . $currMonth . $currDay);
            $startdate = $startdate1->format('Y-m-d');
            $enddate1 = $startdate1->add(new DateInterval("P".$duration."M"));
            $enddate = $enddate1->format('Y-m-d');
            $total_cost = 0.00;
            
            $stmt = $dbh->prepare("INSERT INTO `Contract` 
                                    (`cust_id`, `start_date`, `duration`, `end_date`, `total_cost`)
                                    VALUES ('$username', NOW(), '$duration', '$enddate', '$total_cost'
                                    )");
            $stmt->execute();
            header("location: member_profile.php");
        }
        catch (Exception $e) {
            $_SESSION['OVR_ERR_MSG'] = $e;
            session_write_close();
            header("location: member_contract.php");
        }
    }
?>

And my database code is below to show there is an end_date field:

CREATE TABLE IF NOT EXISTS Contract (
                            contract_no INT(6) ZEROFILL NOT NULL AUTO_INCREMENT,
                            cust_id VARCHAR(30) NOT NULL,
                            start_date DATE NOT NULL,
                            duration INT(2) NOT NULL,
                            end_date DATE NOT NULL,
                            total_cost DOUBLE(5,2),
                            CONSTRAINT CONTRACT_PK PRIMARY KEY(contract_no),
                            CONSTRAINT CONTRACT_FK FOREIGN KEY (cust_id) REFERENCES Member (username)
                            );

I'm not having problems with the rest of the code; just the parts i've stated.

Thanks.

Try this:

$stmt = $dbh->prepare("SELECT MAX(end_date) AS end_date
                       FROM Contract
                       WHERE cust_id = '$username'");

Try this:

$stmt = $dbh->prepare("SELECT MAX(end_date) AS end_date
                       FROM Contract
                       WHERE cust_id = '$username'");

I saw the MAX part appearing in the field name in the cmd window and thought about trying that.

It worked :) thanks!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.