RE: [PHP] Tough Select List with multiple arrays

On, www.anl.com.au when you query the schedule it is going through and creating multiple tables for faster lookups each time. The catch? The table *names* have to be unique to avoid someone getting data from someone else's query. Total SQL statements, about 12. ~3 pages of code (nicely formatted). Nightmare. Almost as annoying as MSIE's cookies atm... Jason -- Jason Murray jasonm@melbourneit.com.au Web Design Team, Melbourne IT Don't believe everything you're foretold. > -----Original Message----- > From: Tim Parkin [mailto:Tim.Parkin@didio.com] > Sent: Thursday, October 19, 2000 5:53 PM > To: php-general@lists.php.net > Subject: RE: [PHP] Tough Select List with multiple arrays > > > Probably completely off topic but as far as tough select > statements go heres > a beauty our oracle dba came up with the other day > > SELECT /*+ORDERED*/ a.id, a.name, > a.parent_type_ref, > a.h_level, f.children, COUNT(b.media_title_ref) no_titles > FROM (SELECT c.id, c.name, > NVL(c.parent_type_ref, 0) > parent_type_ref, LEVEL h_level, c.client_ref > FROM dt_types c > WHERE c.type_type = > dp_constants.ccategory > START WITH c.parent_type_ref IS > NULL > CONNECT BY PRIOR c.id = > c.parent_type_ref > GROUP BY c.id, c.name, > parent_type_ref, LEVEL, c.client_ref) a, dt_client_media_categories b, > (SELECT d.id, > COUNT(e.parent_type_ref) children > FROM dt_types d, dt_types e > WHERE > e.parent_type_ref(+) = d.id > AND d.type_type(+) = > dp_constants.ccategory > AND e.type_type(+) = > dp_constants.ccategory > GROUP BY d.id) f > WHERE a.id = b.category_ref(+) > AND a.client_ref = b.client_ref(+) > AND a.client_ref = pclient_id > AND a.id = f.id > GROUP BY a.id, a.name, > a.parent_type_ref, a.h_level, > f.children > ORDER BY a.h_level; > > this gets a nice hierarchy of categories, how many items in > each categories > category levels etc.. all done in one query that takes 3msec > on a sparc > ultra 5 .. I was pretty impressed :-/ > > -----Original Message----- > From: Phillip S. Baker [mailto:phillip@wordsandimages.com] > Sent: Thursday, October 19, 2000 01:08 > To: php-general@lists.php.net > Subject: Re: [PHP] Tough Select List with multiple arrays > > > This appears to have worked just fine. > > However I am not quite clear on what happened here. Can you > please walk me > through what is happening in each step? > > Phillip > > At 04:52 PM 18-10-00 -0700, Angelko Krstanovic wrote: > >will this work 4u: > > > > >> $game_query = mysql_query("SELECT game_id FROM > games,members_games > > >> WHERE games.gameid=members_games.game_id AND member_id=$member_id > > >> GROUP BY game_id",$db); > > > >while ($game_result = mysql_fetch_array($game_query)) { > > $all_games[$game_result['game_id']] = 1; > >} > > > > > > > > >> $query1 = "SELECT * FROM games"; > > >> $game_query = mysql_query($query1); > > >> while($games = mysql_fetch_array($game_query)) > > >> { > > >> /* Get the game titles and gameids for the check > box list menu */ > > >> $game_title = $games["title"]; > > >> $gameid = $games["gameid"]; > > > > /* Output a drop down menu list of all the games that > The Grognards > >supports from the games table. */ > > print("<INPUT TYPE=\"checkbox\" NAME=\"games[]\" > VALUE=\"$gameid\" > >"); > > > > if($all_games[$gameid]) { echo "checked"; } > > > > echo ">$game_title<BR>\n"); > > >> } > > > >-angelko > > > >On Wed, 18 Oct 2000, Phillip S. Baker wrote: > > > > > This is kind of it, but the trick here is that the select > list is also > > > dynamically generated from a set of values in a database. > > > Would it still be basically the same? > > > > > > Phillip > > > > > > At 04:20 PM 18-10-00 -0700, Angelko Krstanovic wrote: > > > >phillip, > > > > > > > >if i understand your question correctly, you are trying to > > > >do dynamic selects. here is an example of a one way: > > > > > > > >$id = $row['game_id']; > > > >$select_name = 'selected'.$id; > > > >${$select_name} = 'selected'; > > > > > > > >echo " > > > ><select name=test> > > > > <option name=1 $selected1> 1 > > > > <option name=2 $selected2> 2 > > > > <option name=3 $selected3> 3 > > > > <option name=4 $selected4> 4 > > > ></select> > > > >"; > > > > > > > > > > > >the trick is in ${$select_name}. it instantiates a variable > > > >whose name is contained in $select_name. > > > > > > > >hope this helps. > > > > > > > >-angelko > > > > > > > > > > > >On Wed, 18 Oct 2000, Phillip S. Baker wrote: > > > > > > > > > Okay this is a tough one that I am having trouble > figuring out > > exactly. > > > > > > > > > > I have this SQL query and array. > > > > > //Query to get the records of what games the player owns > > > > > $game_query = mysql_query("SELECT game_id FROM > games,members_games > > WHERE > > > > > games.gameid=members_games.game_id AND > member_id=$member_id GROUP BY > > > > > game_id",$db); > > > > > $game_result = mysql_fetch_array($game_query); > > > > > > > > > > So now I have a game_result array with numeric values in it. > > > > > > > > > > Then I have this form for a member to update their > information. It > > has a > > > > > list of all game titles. > > > > > > > > > > $query1 = "SELECT * FROM games"; > > > > > $game_query = mysql_query($query1); > > > > > while($games = mysql_fetch_array($game_query)) > > > > > { > > > > > /* Get the game titles and gameids for the > check box list menu > */ > > > > > $game_title = $games["title"]; > > > > > $gameid = $games["gameid"]; > > > > > > > > > > /* Output a drop down menu list of all the > games that The > > Grognards > > > > > supports from the games table. */ > > > > > print("<INPUT TYPE=\"checkbox\" NAME=\"games[]\" > > > > > VALUE=\"$gameid\">$game_title<BR>\n"); > > > > > } > > > > > > > > > > What I want to be able to add a bit of code to the > second part so > that > > > > > if($game_result == "$gameid") { echo "checked"; } > > > > > This way in the full list of games is a member has > already listed > that > > > > > they have the game is automatically selected. > > > > > Then the member can add or delete game titles from > his profile. > > > > > > > > > > How do I do this? > > > > > > > > > > Thanks > > > > > > > > > > > > > > > -- > > > > > 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 > > > > > > > > > > > > > > -- > > > 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 > > > > > > > > >-- > >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 > > > -- > 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 >