Re: getting rows from separate tables stacked in a single array ? (newbie, mysql)

Sup, It can be done...but it will be complicated. First off. You should check out the extract() php command. http://php.net/extract This function makes entries in an array into local variables. Let's say your row array has 2 columns, title and duration. You can do: while( $row = mysql_fetch_assoc($result) ) { extract( $row , EXTR_PREFIX_ALL , "qt_"); // last parameter is a prefix to add onto the var names echo "title = $qt_title , duration = $qt_duration<BR>"; } --- Now onto the mysql goodness. I don't know of any way to stack rows from different tables. It really wouldnt work because the column names would probably be different. Doing a join isn't going to work either because the tables will probably not contain the same number of rows. I suggest selecting just the row id and creation time from each of your tables. Like: $result1 = mysql_query(); $result2 = mysql_query(); $result3 = mysql_query(); Then while( !$dead1 || !$dead2 || !$dead3 ) { if(!$row1 && !$dead1) $row1 = mysql_fetch_assoc() or $dead1 = true; if(!$row2 && !$dead2) $row2 = mysql_fetch_assoc() or $dead2 = true; if(!$row3 && !$dead3) $row3 = mysql_fetch_assoc() or $dead3 = true; // compare all 3 times, find nearest one // store winning row id into a temp var, like win_id; // have a list of table names which corresponds to the rows. row1 = blah_tbl , row2 = eh_tbl , row3 = wow_tbl // store table name from winning row, store in win_table; // and finally set the winning row to 0, so that row will be fetched next time through the loop $each_result = mysql_query("select * from $win_table where id = $win_id"); Output_my_Result( $each_result ); } It sure ain't pretty, but that's how I could do it...Hopefull all that helps you somehow. SL. ----- Original Message ----- From: "Nicolas Mermet" <himself@nicolas-mermet.com> To: <php-general@lists.php.net> Sent: Tuesday, April 24, 2001 2:42 PM Subject: [PHP] getting rows from separate tables stacked in a single array ? (newbie, mysql) > Hi. > > This will probably sound simple to all of you but I am hitting my head on > a brick wall so far :-). > > I need to generate a list (sorted by descending time) of different objects > referenced in multiple tables. All those objects share a key related to a > project. > > > This works splendidly from a single table : > > > > > $sql="select * from storyboards, where spot_id = \"$spot_id\" order by > date_posted desc"; > $result=MySQL_query($sql,$db); > while($row=MySQL_fetch_array($result)) > { > $qt_title = $row["title"]; > $qt_duration = $row["duration"]; > $qt_date_posted = $row["date_posted"]; //(timestamp) > $qt_description = $row["description"]; > $qt_id = $row["quicktime_id"]; > > > } > > Is there a mysql query that would allow me to stack complete rows in > multiple tables at once (It seems more elegant because I can sort them > while extracting them) or is there a way in PHP to concatenate results in > a single array and then sort them by time... ? > > I tried to use a join query, wich produced an invalid query error. (I > think it tries to produce a table with merged data, not just a stack of > rows). > > > $sql4="select * from quicktimes, other_images, storyboards, where > quicktimes.spot_id, other_images.spot_id, storyboards.spot_id = > \"$spot_id\" order by date_posted desc"; > > thank you for your help ! > nicolas > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, e-mail: php-general-unsubscribe@lists.php.net > For additional commands, e-mail: php-general-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net >