SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Zealot
    Join Date
    Apr 2003
    Location
    Washington, DC
    Posts
    158
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    multiple rows to one

    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.

    PHP Code:
    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...
    Last edited by zealivity5; Nov 29, 2008 at 18:10.
    anyone seen my website link?

  2. #2
    hi galen's Avatar
    Join Date
    Jan 2006
    Location
    New Haven, CT
    Posts
    1,228
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  3. #3
    SitePoint Zealot
    Join Date
    Apr 2003
    Location
    Washington, DC
    Posts
    158
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Made myself clearer in the original post...
    anyone seen my website link?

  4. #4
    hi galen's Avatar
    Join Date
    Jan 2006
    Location
    New Haven, CT
    Posts
    1,228
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, can i ask why youre doing this? there's gotta be a better way to accomplish what youre trying to do.

  5. #5
    SitePoint Zealot
    Join Date
    Apr 2003
    Location
    Washington, DC
    Posts
    158
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    anyone seen my website link?

  6. #6
    hi galen's Avatar
    Join Date
    Jan 2006
    Location
    New Haven, CT
    Posts
    1,228
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    http://dev.mysql.com/doc/refman/5.0/...tion_dayofweek

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

    is that what you're trying to do?

  7. #7
    SitePoint Zealot
    Join Date
    Apr 2003
    Location
    Washington, DC
    Posts
    158
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by galen View Post
    http://dev.mysql.com/doc/refman/5.0/...tion_dayofweek

    Code MySQL:
    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.
    anyone seen my website link?

  8. #8
    hi galen's Avatar
    Join Date
    Jan 2006
    Location
    New Haven, CT
    Posts
    1,228
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by galen View Post
    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)...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Guru BerislavLopac's Avatar
    Join Date
    Sep 2004
    Location
    Zagreb, Croatia
    Posts
    830
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I suggest you check out views. They give the power of select statements combined with the simlicity of tables.

  11. #11
    SitePoint Zealot
    Join Date
    Apr 2003
    Location
    Washington, DC
    Posts
    158
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    anyone seen my website link?

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sounds like a simple ORDER BY to me...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Zealot
    Join Date
    Apr 2003
    Location
    Washington, DC
    Posts
    158
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    anyone seen my website link?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •