Ok, I wrote some code to pull info from by db, it works, but I needed it ordered by one field in a table. I the though that maybe a Join would work, problem is Im new to sql and php in general. this ones pullin my hair out.
<?php
$root='http://www.mysite.com/';
include '../aaamydb.inc';
$searched =$HTTP_GET_VARS["find"];
clean($searched, 30);
echo "<!doctype html public \"-//W3C//DTD HTML 4.01 Transitional//EN\">\n\n";
echo "<html> \n\n";
echo "<head> \n";
echo "<style>\n";
echo "<!--\n";
echo ".style1 {font-size: 12px; font-family: tahoma; color: #111111;}\n";
echo ".style2 {font-size: 9px; font-family: tahoma; color: #63696d;}\n";
echo "-->\n";
echo "</style>\n";
echo "</head>\n\n";
echo "<body> \n";
echo "<table border=\"0\" cellpadding=\"0\" cellspacing=\"0\" style=\"border-collapse: collapse\" bordercolor=\"#111111\" id=\"AutoNumber2\">\n";
$link_id = mysql_connect($dbhost,$dbusername,$dbuserpw)or die("Cannot connect to the database.<br>" . mysql_error());
$selected = mysql_select_db($dbname,$link_id)or die("Cannot select the database.<br>" . mysql_error());
$query = "SELECT * FROM categories_description WHERE categories_name ='$searched'";
$sql = mysql_query($newquery) or die("Cannot query (sql) the database.<br>" . mysql_error());
while($result = mysql_fetch_array($sql)) {
$gamename = $result["categories_name"] ;
$game_cat = $result["categories_id"];
$numgame = mysql_num_rows($sql);
$query2 = " SELECT `products_id` , `categories_id` FROM `products_to_categories` WHERE `categories_id` ='$game_cat'";
$sql2 =mysql_query($query2) or die ("Cannot query (sql2) the database.<br>" . mysql_error());
$numgames = mysql_num_rows($sql2); // counts how many records match 28
//echo "Number of games found, $numgames<br>\n";
while($result1 = mysql_fetch_array($sql2)){
$prodid = $result1["products_id"];
$query3 = "SELECT * FROM `products` where `products_id`='$prodid'";
$sql2= mysql_query($query3)or die("could not qury with sql2");
while ($result2 = mysql_fetch_array($sql2)){
$price = $result2["products_price"];
$query4="SELECT * FROM `products_description` WHERE `products_id`='$prodid'";
$sql4=mysql_query($query4)or die("could not qury with sql4");
while ($result4 = mysql_fetch_array($sql4)){
$productname=$result4["products_name"];
$productdescription=$result4["products_description"];
}
$query5="SELECT * FROM `categories` WHERE `categories_id`='$game_cat'";
$sql5=mysql_query($query5)or die("could not qury with sql5");
while ($result5 = mysql_fetch_array($sql5)){
$parent=$result5["parent_id"];
}
//echo "$productname $productdescription $price <br>\n";
echo "<tr>\n";
echo "<td rowspan=\"2\">\n";
echo "<a href=\"cat/index.php?cPath=$parent".."$game_cat\">\n";
echo "<img src=\"newimages/jointopsbannersm.jpg\" border=\"0\" style=\"border: 3px double #8A8A8A\" width=\"216\" height=\"67\"></a></td>\n";
echo "<td bgcolor=\"#BDBDBE\" class=\"style1\" height=\"29\">$productname<div style=\"padding-left:9px; padding-top:7px\"><a href=\"cat/index.php?cPath=$parent".."$game_cat\">\n";
echo "<font size =\"1\">\n";
echo "<img src=\"images/point_1.gif\" border=\"0\" width=\"9\" height=\"5\"></a></td>\n";
echo "</tr>\n";
echo "<tr>\n";
echo "<td valign=\"top\" class=\"style2\" height=\"7\"><div style=\"padding-left:12px; padding-top:15px\">\n";
echo "<href=".$root."\"cat/index.php?cPath=$parent"._."$game_cat\">Details and Purchase</a><br>\n";
echo " </font></div></td>\n";
}
}
}
echo "</tr>\n";
echo "</table>\n";
echo "</body>\n";
echo "</html>\n";
?>
Anyways, thats my orginal code not cleaned up at all, so you can see what I was doing, my newly created sql that wont work
$newquery=" SELECT cd.categories_name,
cd.categories_id,
ptc.products_id,
ptc.categories_id,
p.price,
pd.products_name,
pd.products_description,
c.parent_id
FROM categories_description cd,
products_to_categories ptc,
products p, products_description pd,
WHERE ptc.categories_id=cd.categories_id
AND p.products_id=ptc.products_id
AND c.categories_id = cd.categories_id
AND cd.categories_name = "$searched"
ORDER BY p.price DESC LIMIT 0 ,30" ;
is this doable or should I just populate arrays and sort them ?
Glenn H
