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. :crazy:

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 :P). 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 :P

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. :P

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

Code: Select all

IFNULL(expresion, 0)
MySql manual: IFNULL

Re: insert query help

Posted: Tue Apr 01, 2008 4:50 am
by s.dot
Thank you much, sir!