multiple rows to one

[zealivity5]

I'm building a script to pull multiple rows from a db table based on certain criteria and then insert it into one row in another table... but I'm stuck on how to take what I've queried and put it in one variable to insert it into the new table.

require "db.php";
require "./functions/time.php";

//$sql_time = mysql_real_escape_string($_GET['time']);
$time2 = time();
$time = ($time2-86400);
  $q = mysql_query("SELECT * FROM media WHERE time>=$time");
    while ($list = mysql_fetch_object($q)) {
    $readable = timediff($list->time);
    $i = mysql_query("INSERT INTO media (id,mid,title,media,time) VALUES ('NULL','$list->id','','','')") or die('error @ 2 '.mysql_error());

     echo "$list->title added $readable<br />";
  } 

Here is what I'm trying to do...

Select items added in the last 24 hours from one table and then insert them into one row in a new table...
e.g select title 1, title 2, title 3 from table 1 and then insert them into table 2 in one row...

id | title
1 | title 1, title 2, title 3

... but I'm not sure how to get the variables from the loop into an array...

[galen]

you can do that with one query

http://dev.mysql.com/doc/refman/5.0/...rt-select.html

INSERT INTO tbl_temp2 (fld_id)
SELECT tbl_temp1.fld_order_id
FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

just edit that to suit your needs

[zealivity5]

Made myself clearer in the original post...

[galen]

ok, can i ask why youre doing this? there's gotta be a better way to accomplish what youre trying to do.

[zealivity5]

I want to select items added in the last 24 hours daily so they'd be grouped by monday, tuesday, weds etc.

So what I was thinking was running a cron daily to query the table and then insert the results into a new table every day.

[galen]

http://dev.mysql.com/doc/refman/5.0/...tion_dayofweek

select * from media where dayname(date(time)) = 'Monday'

is that what you're trying to do?

[zealivity5]

http://dev.mysql.com/doc/refman/5.0/...tion_dayofweek

select * from media where dayname(date(time)) = 'Monday'

is that what you're trying to do?

Nope. Wanting to select from one table (media) and find all the items added to the site within the last 24 hours. I'd like to store that information in a new table by row. Each row would be a different day. So instead of using the current setup to display the last 40 items added I could start displaying them on the site by items added today, added yesterday etc. The only information I've stored in the table up to this point is a unix timestamp.

[galen]

Whatever you are trying to do you don't need to put everything in another table. You can use mysql date functions on the time field to get the information you want.

[r937]

Whatever you are trying to do you don't need to put everything in another table.

just wanted to add my agreement here

i could help you with the query (you could use the GROUP_CONCAT function, so that you don't actually have to SELECT the data to massage it with php)

but the extra table is almost certainly not needed

if you could describe what you really want (instead of getting us to help you with the second table, which is not a good solution)...

[BerislavLopac]

I suggest you check out views. They give the power of select statements combined with the simlicity of tables.

[zealivity5]

Been building this in my free time so I haven't had much of a chance to continue where I left off.

Basically, I have one table with information on various media (images and video). It identifies when the media was uploaded and where it is located among a few other things.

What I'm trying to do in the simplest way possible is on one page display the uploads grouped by time. For example, the first section on the page in a blog like style would say, Videos & Images for Monday and anything uploaded Monday would be displayed. The next section would be Videos & Images for Tuesday and anything uploaded Tuesday would be displayed. Make sense?

I really want to use one query to do this, but all the methods I was able to get to work resulted in more than one query, or pulling a huge chunk of data and having php do the hard work.

[r937]

sounds like a simple ORDER BY to me...

[zealivity5]

Yes, with order by I can just echo everything out and display the date. I'm not trying to do that exactly. I want to echo out the data yes, but I want to group various items by date into a separate section.

So instead of having
video 1 posted monday
video 2 posted monday
video 3 posted tuesday

I want
Monday:
Video 1, Video 2

Tuesday
Video 3

I'll just do it the way I was originally, grabbing a weeks worth of entries and then looping through them and sorting them with php.