CODEWITHSUNDEEP
mysqlsqlwordpressbigint
Tails128
Tails128

Reputation: 320

Sql behaving weirdly in wordpress

I've recently started to develop a wordpress plugin and since I needed a table...

I've used the following code (with the exception of having it prepared and not having it with the $table_name variable directly injected, plus i've applyed the default collate) to create my table during my wordpress plugin's activation:

  "CREATE TABLE $table_name, id bigint(20) NOT NULL AUTO_INCREMENT,
    userid bigint(20),
    start_hour int(12) NOT NULL,
    end_hour int(12) NOT NULL,
    accepted bit,
    deleted bit,
    UNIQUE KEY id(id);"

after i've added some data i've tryed a couple queries which behaved correctly, untill... (sorry for the italian settings, I'm working on a customer's WAMP )

magic SQL

My first thought was that perhaps the value was overflowing for some weird reason (even tho (2^32)/2 should be 2.147.483.648 while my values are 1.525.910.400), so i converted it to bigint(20), but the results didn't change.

What i expect is this query to return nothing, while i expect this to be the result of the query:

SELECT COUNT(*)
FROM wp_t128customCalendarBookings
WHERE 'start_hour' BETWEEN 1525910400 AND 1525912200 OR 
      'end_hour' BETWEEN 1525910400 AND 1525912200;

Is there anyone who could help me solve this weirdness?

Upvotes: 0

Views: 29

Answers (1)

RToyo
RToyo

Reputation: 2877

The problem is that you've put single quotes around your start_hour column name. You're actually comparing a string with a value of "start_hour" against a value of "1", which results in a true value, and thus all rows are returned.

You should use the back tick character ( ` ) to encapsulate your column/table names, rather than single quotes, as single quotes are interpreted as literal strings.

select start_hour from wp_t128customCalendarBookings WHERE `start_hour` < 1

Upvotes: 1

Related Questions