How do you query a database using a Date and Time from PHP Function

Question

How do I fix my query code so that it searches the database using a Date and Time but the Date is a variable from the function? I am new to PHP and SQL and don't know how the Dots ( . ) and Quotations fully work ( "" '' ). How do i fix this piece of code?

WHERE Fixture.date => '$FixtureDate 00:00:00' AND Fixture.date =< '$FixtureDate 23:59:59'

Please See code below

function FSearchDate($FixtureDate) {
    try {
        $conn = new PDO("mysql:host=" . $GLOBALS['servername'] . ";dbname=DATABASE_NAME", $GLOBALS['username'], $GLOBALS['password']);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $statement = $conn->query("
        SELECT Fixtures.fixtureid AS FixtureID, 
        Fixtures.date AS Date, 
        Fixtures.week AS Week, 
        Fixtures.home_team AS HomeTeam, 
        Fixtures.away_team AS AwayTeam,
        FixtureScores.home_team AS HomeScore, 
        FixtureScores.away_team AS AwayScore
        FROM Fixtures
        INNER JOIN FixtureScores ON Fixtures.fixtureid=FixtureScores.fixtureid 
        WHERE Fixture.date => '$FixtureDate 00:00:00' AND Fixture.date =< '$FixtureDate 23:59:59'"
        );
        $result = $statement->fetch();

        if ($result == null) { // Fixture ID Doesn't Exist
            echo '<script type="text/javascript">alert("The Fixture ID entered is not valid. Please enter a valid Fixture ID");</script>';
        } else {
            $GLOBALS['FixtureID'] = $result[0];
            $GLOBALS['FixtureDate'] = $result[1];
            $GLOBALS['FixtureWeek'] = $result[2];
            $GLOBALS['FixtureHomeTeamName'] = $result[3];
            $GLOBALS['FixtureAwayTeamName'] = $result[4];
            $GLOBALS['FixtureHomeTeamScore'] = $result[5];
            $GLOBALS['FixtureAwayTeamScore'] = $result[6];
        }
    }
    catch(PDOException $e) {
        echo "An problem occured: " . $e->getMessage();
    }

    $conn = null;
}

try your query without the FSearchDate() method, does it work? If so, it's a variable scope. — Funk Forty Niner
– Funk Forty Niner, Commented Nov 17, 2017 at 13:42
Please consult a tutorial on PHP & MySQL integration. The "dots" are for string concatenation, and the quotation types (' vs ") matter, especially in the context you're using them. — kchason
– kchason, Commented Nov 17, 2017 at 13:45

Funk Forty Niner · Accepted Answer · 2017-11-17 14:06:12Z

1

Your query is wrong:

WHERE Fixture.date => '$FixtureDate 00:00:00' AND Fixture.date =< '$FixtureDate 23:59:59'"

You're trying to use the => and =< which don't do what you expected.

The => is the separator for associative arrays.

http://www.php.net/manual/en/language.types.array.php

Those should read as >= and <= respectively, as in "more than or equal to" and "less than or equal to".

Note: =< doesn't exist and isn't a valid operator/separator.

Say it out loud: "less than or equal to". We never say "equal to or less than".

Plus, you may have a variable scoping here for the FSearchDate() method, where you would might have to pass the database connection to. Yet, this might not be the case.

edited Nov 17, 2017 at 14:06

answered Nov 17, 2017 at 13:53

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ritesh Khatri · Accepted Answer · 2017-11-17 13:49:39Z

0

You can use here between

$statement = $conn->query("
    SELECT Fixtures.fixtureid AS FixtureID, 
    Fixtures.date AS Date, 
    Fixtures.week AS Week, 
    Fixtures.home_team AS HomeTeam, 
    Fixtures.away_team AS AwayTeam,
    FixtureScores.home_team AS HomeScore, 
    FixtureScores.away_team AS AwayScore
    FROM Fixtures
    INNER JOIN FixtureScores ON Fixtures.fixtureid=FixtureScores.fixtureid 
    WHERE Fixtures.date BETWEEN 'date('Y-m-d H:i:s',$FixtureDate)' AND 'date('Y-m-d H:i:s',$FixtureDate)'"
    );

check this statement,

WHERE Fixtures.date BETWEEN 'date('Y-m-d H:i:s',$FixtureDate)' AND 'date('Y-m-d H:i:s',$FixtureDate)'

answered Nov 17, 2017 at 13:49

Ritesh Khatri

1,31115 silver badges30 bronze badges

Collectives™ on Stack Overflow

How do you query a database using a Date and Time from PHP Function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related