mysql : How to update or insert new rows DAILY?

Question

My project have to show 3 rows to user and that'll see new 3 random rows in next morning

this is my table.. how can i replace or insert new rows automatically everyday

+--------+-----------+-----------------+-----------------+
| userID | userNAME  |     chaID       |     chaNAME     |
+--------+-----------+-----------------+-----------------+
|      1 | Jane      |          4      | Watering        |
|      2 | Cristian  |          3      | Early Sleeping  |
|      3 | Jumbo     |          12     | Playing sport   |
+--------+-----------+-----------------+-----------------+

I really have no idea to deal with it .. should i make a trigger?

Thank you so much

Only 3 rows will be available rite? If yes, why do you want to insert new rows daily. U can randomly select the rows. — Dijo David, Nov 23 '15 at 05:58
@DijoDavid thank you >_< , Actually this table was selected from the other table by random and the chaID should have 3 for each user .. so how to randomly select the rows ? — Nongkarntt Nitchkarnt, Nov 23 '15 at 06:07
I assume user and cha table is 2 different tables and the one you have mentioned is relation table. correct? Application is built in which language? — Dijo David, Nov 23 '15 at 06:18
Possible duplicate of [MySQL select 10 random rows from 600K rows fast](http://stackoverflow.com/questions/4329396/mysql-select-10-random-rows-from-600k-rows-fast) — Shadow, Nov 23 '15 at 06:18
@DijoDavid yes this is a relation table from user table and challenge table ,I built with PHP — Nongkarntt Nitchkarnt, Nov 23 '15 at 06:26

score 0 · Accepted Answer · edited May 23 '17 at 12:31

use the mysql Create event functionality. See my answer to One Here. Create a daily event, and work whatever magic you want inside the BEGIN / END block. Such as deleting, and inserting.

As for selecting random rows, look into the likes of

select col1, col2 
from otherTable
order by rand()
limit 3;  -- just 3 rows

To insert into one table from another (with random), just combine insert and select into one statement, such as

insert into myTable (col1,col2)
select col1, col2 
from otherTable
order by rand()
limit 3;

myTable undoubtedly needs to have userId's or the like. That can be performed by doing a cross join on a user table to put 3 new rows in a day for each user.

insert into myTable (userId,col1,col2)
select u.userId,o.col1, o.col2 
from userTable u
cross join otherTable o
order by rand()
limit 3;

So there, just to leave you with something. I know you are new. Welcome to the stack.

Thank you so much ,Drew I'm a newbie and the event function is very useful. if it's going well I'll tell you after ... >_ — Nongkarntt Nitchkarnt, Nov 23 '15 at 06:46

mysql : How to update or insert new rows DAILY?

1 Answers1