CODEWITHSUNDEEP
phpmysqlsql
Ty Bailey
Ty Bailey

Reputation: 2432

Select All MySQL Data Before Provided Date

I have a small PHP Application which is supposed to allow a user to enter a 4 digit year into an XHTML web form and return all the information saved in the MySQL database with a lower year than the one provided. The years are stored in the db under the column yearPublished. I have the query working and I get no errors, however no data is being returned.

Here is the SQL file for creating the database:

CREATE TABLE IF NOT EXISTS `books` (
`ISBN` char(13) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`title` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`author_firstName` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`author_lastName` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`genre` varchar(35) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`publisher` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`yearPublished` varchar(4) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`price` float DEFAULT '0.0',
PRIMARY KEY (`ISBN`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

And here is the PHP code that executes the query:

    if(isset($_POST['submitForm'])) {
    $strBookPubYear = $_POST['pubYear'];

    if(empty($strBookPubYear)) {
        print("<p class='error'>You must enter a 4 digit publication date!</p>\n");
    } else {
        if(!is_numeric($strBookPubYear)) {
            print("<p class='error'>The publication date must be numeric.</p>\n");
        } else {
            if(!(strlen($strBookPubYear) === 4)) {
                print("<p class='error'>The Publication date must be exactly 4 characters long.</p>\n ");
            } else {

        $sqlQuery = mysql_query("SELECT yearPublished FROM books WHERE yearPublished < '$strBookPubYear'");

        if($sqlQuery === false) {
            print("<p class='error'>Could not execute the query, please try again.</p>\n");
        } else {

notice the $sqlQuery variable that is supposed to get all the data where the yearPublished is lower than $strBookPubYear. I have read multiple tutorials on this, but they all are using the MySQL 'Date' data-type. In my case I am using a text data-type. Please do not tell me to use mysqli, this specific project does not require me to use mysqli.

Upvotes: 1

Views: 799

Answers (2)

le3th4x0rbot
le3th4x0rbot

Reputation: 2451

Your yearPublished is stored as a varchar, which is essentially a string. Therefore, when you say yearPublished < '$strBookPubYear', it probably is not doing what you think that it is doing. Store yearPublished as an INTEGER. Either way it takes 4 bytes.

You can manually cast the varchar to a number in the query, and it should probably start working. 

SELECT yearPublished FROM books WHERE (yearPublished+0) < '$strBookPubYear'

OK, that wasn't really the problem! Try this error checking block, to spit out some better clues:

// Check result
// This shows the actual query sent to MySQL, and the error. Useful for debugging.
if (!$sqlQuery) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    die($message);
}

Upvotes: 1

Adrian J. Moreno
Adrian J. Moreno

Reputation: 14859

Your biggest problem in the database is here:

yearPublished varchar(4) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

YearPublished should at least be an Integer or better an actual Date datatype.

Using a varchar datatype, which is a string datatype, this SQL never returns any values:

$sqlQuery = mysql_query("SELECT yearPublished FROM books WHERE yearPublished < '$strBookPubYear'");

The less than symbol, < cannot compare if a string value is "less than" some other string value.

Update

If you can't alter the structure of the table, you can use MySQL's CONVERT function to convert between data types.

So something like this should work:

$sqlQuery = mysql_query("SELECT yearPublished FROM books WHERE CONVERT(yearPublished, SIGNED) < CONVERT($strBookPubYear, SIGNED)");

Since $strBookPubYear is a string coming in from the form submit, you'll need to convert that too.

Upvotes: 2

Related Questions