better way to code this?

[dragon_sa]

Is there a better way to code this so I dont have to have the mysql query inside the while loop, what it does is generate a link menu of catalogs from various collections, the collection name is stored in the collections table and the collectionID the catalog is related to is stored in the catalog table with each catalog, to get the collection name I am running a query on the collections table for each catalog inside a while loop which generates the option menu but ideally I would prefer not to have so many database queries happening to generate the page

 

		$sqlCat = "SELECT * FROM catalog ORDER BY catalogID DESC";
		$resCat = mysql_query($sqlCat);
		while ($result_row = mysql_fetch_array($resCat)) {
		$catName = $result_row["name"];
		$collectID = $result_row["collectionID"];
		$sqlname = "SELECT * FROM collections WHERE collectionID='$collectID'";
		$resname = mysql_query($sqlname);
		$name_row = mysql_fetch_array($resname);
		$relCollName = $name_row["collection"];
		$catID = $result_row["catalogID"]."+catalog";
		echo "<option value='$catID'>$relCollName - $catName</option>\n";
		}

[sniperscope]

you have to use LEFT JOIN for your queries...

There is tons of gurus in here and i am sure they have a better solution.

[pagegen]

one sql loop

 

"SELECT catalog.*, collections.fileds_needed FROM catalog LEFT JOIN collections ON  catalog.id=collections.collectionID ORDER BY catalogID DESC"

 

you might need to use the AS function if u have fileds with same names in your database

example:

 

"SELECT catalog.*, collections.fileds_needed AS field_1 ....."