From: David Bruha
Date: March 20 1999 2:35pm
Subject: Isn't there any other way ?
List-Archive: http://lists.mysql.com/mysql/685
Message-Id: <36F3B23C.31361E64@senior.cz>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 8bit

Hi,

I've sent this question already once but nobody has answered. I'm very
newbie to mysql and uncertain about some features and would really
appreciate your help.

I'm working on a www project and am using mysql only to retrieve stored
data. Now I have encountered problem where I have to create a temporary
table to be able to retrieve data in needed way.

I have created following script in PHP3 to create output of products and
accesories. Output is formated to the table where columns are
accessories and rows are products. I print a bullet into those cells
where the accessories belong to respective products. This script is
included into respective pages and those pages provide variables
$nazev[] & $nazev_link[].

My problem is that I have to create a temporary table. This script is
included into cca 50 pages and I don't know how large amount of disk
space I will have to my disposal. In Oracle one can use nested selects
for this kind of problem but in mysql this feature is not available.

Isn't there some other way how to access this problem?

My script looks like:

<?php
	// Connecting to database
	$db=mysql_connect();

	// Preparing query
	$pocet=count($nazev);
	for ($i=0; $i < $pocet; $i++)
	{
		$res=mysql_db_query("wap", "select obj_c from vyrobky where
nazev='$nazev[$i]'");
		// The result is one number (exactly) ...
		$obj_c_vyrobku[]=mysql_result($res, 0, "obj_c");

		if ($i==0)
		{
			$produkty="vyrobky.nazev='$nazev[$i]'";
		}
		else
		{
			$produkty= $produkty." or vyrobky.nazev='$nazev[$i]'";
		}
	}

	// Query is complete
	$query="select doplnky.obj_c, doplnky.nazev, vyrobky.nazev from
doplnky, typy_doplnky, vyrobky where ($produkty) and
vyrobky.obj_c=id_typu and doplnky.obj_c=id_doplnku group by
doplnky.obj_c order by doplnky.obj_c";

	// Select all accessories that belong to selected products
	$res=mysql_db_query("wap", "$query");
        $rows=mysql_numrows($res);

	// If there are any accessories - print them
	if ($rows > 0)
	{

		// Creating tmp table for selected accessories
		$table=mysql_db_query("wap", "create table temp (obj_c int(11), nazev
varchar(60))");

		// Insert dara into tmp table
		for ($i=0; $i<$rows; $i++)
		{
			$tmp_obj_c=mysql_result($res, $i, "doplnky.obj_c");
			$tmp_nazev=mysql_result($res, $i, "doplnky.nazev");
			mysql_db_query("wap", "insert into temp values ('$tmp_obj_c',
'$tmp_nazev')");
		}

		// Print   Table of accessories

		echo("<p><hr width=\"95%\">");
		echo("<a name=\"doplnkove_prislusenstvi\"><h2><font
color=\"#008080\"><u>Doplnkové príslu¹enství:</u></font></h2></a>");
		echo("<p><table width=\"98%\" border=\"5\">");

		echo("<tr>");
		echo("<td></td>");

		// Print table titles and run respective queries
		for ($i=0; $i < $pocet; $i++)
		{
			// titles of columns
			echo ("<td align=\"center\" bgcolor=\"#c0c0c0\"><a
href=\"../script/zobrazit.php3?jmeno=$titulek_prislusenstvi&typ=$nazev_link[$i]\">Doplnky
$nazev[$i]</a></td>");
			// query for respective column (product)
			$sloupce[]=mysql_db_query("wap", "select id_doplnku, nazev from temp
left join typy_doplnky on obj_c=id_doplnku and
id_typu='$obj_c_vyrobku[$i]'");
		}
		echo ("</tr>");

		// For fitting accessory add bullet to product
		for ($i=0; $i<$rows; $i++)
		{
			echo ("<tr>");
			echo("<td>");
			$pole=mysql_result($sloupce[0], $i, "nazev");
			echo("$pole");
			for ($j=0; $j<$pocet; $j++)
			{
 				echo ("<td align=\"center\" valign=\"top\">");
				$pole=mysql_result($sloupce[$j], $i, "id_doplnku");
				if ( $pole > 0 )
				{
					echo("<img src=\"../images/ano_sign.gif\" width=\"11\"
height=\"11\" alt=\"ano\">");
				}
				else
				{
					echo("&nbsp;");
				}
			}
		}
		// Remove tmp table
		mysql_db_query("wap", "drop table temp");
		echo("</table>");
	}
	mysql_close();
?>

Database structure looks like:

Table doplnky (Accessories):

Field	Type		Null	Key	Default	Extra	
-----	----		----	---	-------	-----	
obj_c	int(11)		YES		NULL		
nazev	varchar(60)	YES		NULL		
popis	blob		YES		NULL		


Table typy_doplnky (Products_to_Accessories):

Field		Type		Null	Key	Default	Extra	
-----		----		----	---	-------	-----	
id_doplnku	int(11)		YES		NULL		
id_typu		int(11)		YES		NULL		
seriove		tinyint(1)	YES		0		


Table vyrobky (Products):

Field	Type		Null	Key	Default	Extra	
-----	----		----	---	-------	-----	
obj_c	int(11)		YES		NULL		
nazev	varchar(60)	YES		NULL	-- 


Excuse me that this post is so long but I would really like to know how
to go around nested selects.

Regards,

-david-


------------------------------------------------------------------------
| David Bruha                                                          |
| David.Bruha@stripped					       |
| David.Bruha@stripped                                                |
| http://sorry.vse.cz/~xbrud03/                                        |
------------------------------------------------------------------------