[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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] getting rows from separate tables stacked in a single array ? (newbie, mysql)
Thanks a bunch, SL. I was expecting some heavy manipulation of that sort.
Wouldnt have been able to write that code. (yet ! ;-). I will try that
tomorow.
This afternoon In despair I ended tweaking the db and creating an extra
table to link all the desired tables with a time indice, temporarily
solving my problem but promising future headaches when I devellop the db
admin page. Here is how I dit it:
table "all_items"
item_id (id of the items in their original tables)
date_posted (original item post date)
table_name (where the item comes from)
spot_id (key to the project)
It works with this code:
//connecting to the link table to get time-ordered list of relevant items,
their origin and their keys
$sql4="select DATE_FORMAT(date_posted, '%a %m/%d/%y at %l:%i %p.'),
item_id, item_table from spot_items where spot_id = \"$spot_id\" order by
date_posted desc";
$result4=MySQL_query($sql4);
while($spotitems=MySQL_fetch_array($result4))
{
list($items_date_posted, $items_id, $items_table) = $spotitems ;
//linking to the real tables to get the real item info
$sql5="select * from $items_table where item_id = \"$items_id\"";
$result5=MySQL_query($sql5);
while($items=MySQL_fetch_array($result5))
{
$items_title = $items["title"];
$items_description = $items["description"];
$items_duration = $items["duration"];
}
//outputing in fonction of the item origin
if ($items_table == "movie")
echo "$items_title (Quicktime, duration: $items_duration)
$items_date_posted$items_description
$items_description";
if ($items_table == "picture")
echo "$items_title(Picture) $items_date_posted $items_description";
}
thanks again,
Nicolas.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] getting rows from separate tables stacked in a single array ? (newbie, mysql)
I did put a spot ID because I never thought of building a temp table on
the fly and dropping it at the end of the script. :-) Now that is an
elegant concept !
I was going for a permanent table, wich would have been heavier to feed
when other tables got updated (but wich would have been automated). The
spot_id was the key to select the items. Making it a temp table is even
simpler.
Altho on second though it means I have to grant create/write/drop
privileges to the clients, wich might open security issues... mmmhh.
I must say, this newsgroup rocks. I recieved highly valuable input on this
thread and by email.
Thank you all !
Nicolas
In article <002b01c0cd39$3f86c240$[EMAIL PROTECTED]>,
[EMAIL PROTECTED] ("Steve Lawson") wrote:
> Doh, I shoulda thought of that, a temp table is the best way to do it.
> Altho, I don't see why you have a spot_id as a key for that table. Your are
> probably never going to access that table via that arbitrary number. If
> anything, you should make item_id the key. The id on your other tables
> should also be keys.
>
> SL.
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP] Redirection in PHP ? (newbie)
Hi, this might be a trivial question but I could not find any docs on that on php.net. I am develloping the admin side of a dynamic web site (php/mysql). The mechanic is nothing special: a form is submitted and sends the data to the php page/script that actually does the work of feeding the db. I noticed that hitting "back" on the browser make the feeding scripts run again, and double the entries in the db. Of course, that is what the scripts are supposed to do :-). To avoid spamming my db I would like to implement a simple redirection function, that would redirect the user to the main admin page once the feeding script has successfully executed and would reduce chances of double entries. Is there a simple way to achieve that ? thanks, Nicolas. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP] image upload - determining image resolution in pixels ?
Hi, I was wondering if there is a way to analize an uploaded jpeg file in order to extract its resolution in pixels. Would that be also possible with a quicktime movie ? thanks, Nicolas -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
