Page 1 of 1
insert query help
Posted: Sun Mar 30, 2008 1:29 am
by s.dot
I'm thinking that this query can be done on the database side rather than using php and looping and doing thousands of queries. What I'm wanting to do is this:
Code: Select all
INSERT INTO `table` (`username`, `activity`) VALUES(SELECT `username`, `activity` FROM `users);
What I would like for that to do is insert a record with username and activity fields for each user in the users table. I would also like to pass the current timestamp (time()) into a third field, but I figured that may not be possible doing that type of query.
Obviously the above query doesn't work.. any help?
Re: insert query help
Posted: Sun Mar 30, 2008 2:25 am
by Christopher
Code: Select all
INSERT INTO `table` (`username`, `activity`) SELECT `username`, `activity` FROM `users`;
http://dev.mysql.com/doc/refman/5.1/en/insert.html
Re: insert query help
Posted: Sun Mar 30, 2008 2:37 am
by s.dot
That is super sweet, thanks man. I've been doing so much work at the PHP level, I've decided to transfer as much work to the database as I can and take advantage of database syntax and functions... will save me lots of php code.
Using that above query (which worked flawlessly, and fast!).. is there a way to insert the current time() as a third parameter..
Something like..
Code: Select all
INSERT INTO `table` (`username`, `activity`, `time`) SELECT `username`, `activity` FROM `users`, NOW();
Dang, I feel like a newbie at this. That mysql manual sure is a bit hard to understand.
Re: insert query help
Posted: Sun Mar 30, 2008 3:00 am
by s.dot
Ah, super sweet.
Setting the `time` field with DEFAULT CURRENT_TIMESTAMP solved the problem. (guess I can read the manual after all

). It doesn't save in timestamps, but it will work for me.
Re: insert query help
Posted: Mon Mar 31, 2008 12:57 am
by s.dot
Check out this super sweet query I wrote

The manual gets a bit easier to read after a while
Code: Select all
INSERT INTO credits_history (
`username`,
`credits`,
`views`)
SELECT
`username` AS `uname`,
`credits_today`,
(SELECT
SUM(`credits_used_today`)
FROM
`sites`
WHERE
`username` = `uname`)
FROM users;
Seems simple, but took me a while to write it.
The only problem I have with the above query is that sometimes count(*) returns null instead of 0.
Re: insert query help
Posted: Mon Mar 31, 2008 3:56 am
by EverLearning
If you don't want NULL values returned, wrap whatever you dont want returned as NULL in
MySql manual:
IFNULL
Re: insert query help
Posted: Tue Apr 01, 2008 4:50 am
by s.dot
Thank you much, sir!