To get Yesterday’s data from the database table
- mysql_query("select * from table where date = ".date("Y-m-d", strtotime("-1 day")));
- mysql_query(“select * from table where date = NOW()-INTERVAL 1 DAY”);
To get Tomorrow’s data
- mysql_query(“select * from table where date = “.date(“Y-m-d”, strtotime(“+1 day”)));
- mysql_query(“select * from table where date = NOW()+INTERVAL 1 DAY”);
To get Last Week data
mysql_query(“select * from tbl where date between NOW() – INTERVAL DAYOFWEEK(NOW())+6 DAY AND NOW() – INTERVAL DAYOFWEEK(NOW())-1 DAY”);
No we Cannot.
CREATE TABLE IF NOT EXISTS `usr` (
`id` int(11) NOT NULL,
`survey_id` int(11) NOT NULL,
`number` int(11) NOT NULL,
`count` int(11) NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`id`,`survey_id`,`number`,`count`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Basically, this appears to be creating four primary key on one table.
We could create a number of unique keys in one table, but only one primary key.
Then how is it that your system is allowing the creation of multiple primary keys?
Think of it like it suggest, a ‘KEY’. So the key would be all of the columns specified. In your case you can have multiple rows with the same ‘ID’ and multiple rows with the same ‘survey_id’ but there shall never be two rows that have the same ‘ID’ AND ‘survey_id’.
So in this case it is not saying that the column ‘ID’ must be unique nor is it saying that ‘survey_id’ must be unique, but only the combination.
Like if you write an query
INSERT INTO `usr` (`id`, `survey_id`, `number`, `count`, `date`) VALUES (1, 1, 123456, 50, ‘2013-04-01’);
And after this you again run this query then you are getting an error “#1062 – Duplicate entry ‘1-1-123456-50’ for key ‘PRIMARY'”
The one thing that primary, unique, and foreign keys all have in common is the fact that each type of key can consist of more than just one column from a given table. In other words, foreign, primary, and unique keys are not restricted to having just one column from a given table – each type of key can cover multiple columns. Of course, the database programmer is the one who will define which columns are covered by a foreign, primary, or unique key. That is one similarity all those keys share, but there are some major differences that exist between primary, unique, and foreign keys. Let’s go over those differences. We also give a thorough explanation of why foreign keys are necessary in some situations.
Can a table have multiple unique, foreign, and/or primary keys?
A table can have multiple unique and foreign keys. However, a table can have only one primary key.
Can a unique key have NULL values? Can a primary key have NULL values?
Unique key columns are allowed to hold NULL values. The values in a primary key column, however, can never be NULL.
Can a foreign key reference a non-primary key?
Yes, a foreign key can actually reference a key that is not the primary key of a table. But, a foreign key must reference a unique key.
Can a foreign key contain null values?
Yes, a foreign key can hold NULL values. Because foreign keys can reference unique, non-primary keys – which can hold NULL values – this means that foreign keys can themselves hold NULL values as well. Continue reading
|InnoDB implements row-level locking.||MyISAM implements table-level locking.|
|Automatic Crash Recovery.||No Crash Recovery|
|InnoDB doesn’t have FULLTEXT search indexes.||MyISAM have FULLTEXT search indexes.|
|InnoDB also implements transactions, foreign keys and relationship constraints.||MyISAM does not support transactions, foreign keys and relationship constraints.|
|InnoDB stores both data and indexes in one file.||MyISAM stores indexes in one file and data in another.|
|InnoDB uses a buffer pool to cache both data and indexes.||MyISAM uses key buffers for caching indexes and leaves the data caching management to the operating system.|
In InnoDB the COUNT(*)s (when WHERE, GROUP BY, or JOIN is not used) execute slower than in MyISAM because the row count is not stored internally.
Overall I would recommend InnoDB for most purposes and MyISAM for specialized uses only. InnoDB is now the default engine in new MySQL versions.