Selecting "ALL" from a WHERE sql statement in PHP

GoMeZ

I have been trying to do a simple query of a mysql database from a HTML page, that would then have it’s results shown via a php page. Anyway this works OK (but it isn’t pretty) but the problem I have is when I try to select “ALL” fields in a column. If I select values it works fine but when I just want all it finds nothing. I have use “” and “*” in my html page but both fail.

The PHP part.

<?php

$db = mysql_connect ("localhost", "user", "pw");

mysql_select_db("DB",$db);

$result = mysql_query("SELECT * FROM products WHERE Designer='$Designer' and Product_Type='$Product_Type'" ,$db);

if ($myrow = mysql_fetch_array($result)) {

echo "<CENTER><TABLE WIDTH=680 table border=0 CELLPADDING=6 CELLSPACING=6>\n";

echo "<tr><td><CENTER>Product</CENTER></td> <td><CENTER>Designer</CENTER></td>
<td><CENTER>Description</CENTER></td> <td><CENTER>Price</CENTER></td> <td><CENTER>Sizes</CENTER></td>
<td><CENTER>Picture</CENTER> </tr>\n";

do {
printf("<tr><td VALIGN=\"MIDDLE\" ALIGN=\"CENTER\"> %s </td>
<td VALIGN=\"MIDDLE\" ALIGN=\"CENTER\"> %s </td>
<td VALIGN=\"MIDDLE\" ALIGN=\"CENTER\"> %s </td>
<td VALIGN=\"MIDDLE\" ALIGN=\"CENTER\"> %s </td>
<td VALIGN=\"MIDDLE\" ALIGN=\"CENTER\"> %s </td>
<td VALIGN=\"MIDDLE\" ALIGN=\"CENTER\"> %s </td>
</tr>\n",$myrow["Product_Type"],
$myrow["Designer"], $myrow["Description"],$myrow["Price"], $myrow["Sizes"],$myrow["Picture"]);

} while ($myrow = mysql_fetch_array($result));

echo "</table></CENTER>\n";

}

else {
echo "Sorry, no records were found!";
}
?>

I am new to all this - I would just like to to know what is wrong in the where bit - or what values I should be passing?

Thanks in advance.

Lars_Berg

If you want all rows, omit the where clause.

$query = "select * from products ";
$where = "";
if ( $designer != "" ) $where .= "designer ='" . $designer . "'";
if ( $prouct_Type != "" ) {
   if ( $where != "" )  $where .= "and ";
  $where .= "designer ='" . $designer . "'";
}
if ( $where != "" ) $query .= "where " . $where;
$result = mysql_query($query ,$db) or die("Error in " . $query . " " . mysql_error());

GoMeZ

Thanks very much

GoMeZ

I added this code in and now I can select all or none of the designers.

The trouble is the the Product_Type selection doesn't work. I always get all products of a designer (or all designers). I am sorry maybe I didn't make it clear in what I said - I would like to be able to chose one or all Designers for one or all Product_types.

I have tried building on what you did in the forst place following the same idea but I think I have just screwed it up even more!

Thanks for getting me most of the way there anyway.

GoMeZ

by adding the statement:

if ( $Designer != "" and $Product_Type != "" )   $result = mysql_query("SELECT * FROM products WHERE Designer='$Designer' and Product_Type='$Product_Type'" ,$db);

at the end of what you gave me I am now able to work my searches EXCEPT for the circumstance when

Designer is ALL and the product_Type is named