insert query help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

insert query help

Post 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?
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: insert query help

Post 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
(#10850)
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: insert query help

Post 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.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: insert query help

Post 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.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: insert query help

Post 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.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
EverLearning
Forum Contributor
Posts: 282
Joined: Sat Feb 23, 2008 3:49 am
Location: Niš, Serbia

Re: insert query help

Post 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
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: insert query help

Post by s.dot »

Thank you much, sir!
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
Post Reply