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:

    $_SESSION['OVR_ERR_MSG'] = '';
    $dbh = new PDO("mysql:host=;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'");
    $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 {
            $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'
            header("location: member_profile.php");
        catch (Exception $e) {
            $_SESSION['OVR_ERR_MSG'] = $e;
            header("location: member_contract.php");

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

                            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.


Recommended Answers

All 2 Replies

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, learning, and sharing knowledge.