banacan

Hi Barand, I'm getting an error and I can't figure it out. $var1_chooseProducts = "-1"; if (isset($_GET['div_id'])) { $var1_chooseProducts = $_GET['div_id']; $where[]=ProductsID='$var1_chooseProducts'; } The error says: Pause error: syntax error, unexpected "=" in... and it points to the row that contains the $where array. It seems to be choking on the = after the [] square brackets. I'm afraid I still don't understand that line, what does it mean and what does it do? Thanks for your help. Brett

Barand, Thanks for your quick reply. I've got a meeting to go to now so I won't be able to test this until tomorrow, but I'm pleased that I'm getting a handle on this. Thanks again for your help and Laffin's, and don't be surprised if I come back with more questions. Cheers.

Hi Barand and Laffin, Thanks for your continued help. I'm making progress but I still do not fully understanding how Laffin's code is implemented. I see how each variable – if it is set – is included in the $where array. But I need this line explained more so I understand how the array is built: $where[] = "ProductsID = '$var1_chooseProducts'; What is ProductsID is it the array key, is it my auto-incremented primary key? $var1_chooseProducts = "something"; if (isset($_GET['div_id'])) { $var1_chooseProducts = $_GET['div_id']; $where[]="ProductsID='$var1_chooseProducts'; } So using the preceding code on each variable adds that variables value to the $where array, right? But if the variable was not set, it is not included in the array. Those that are set are included in the WHERE clause and separated by AND using implode. Is this right so far? The next part is what I really don't get. $Query = "SELECT * FROM mytable $where;"; Am I understanding this correctly, you are using a PHP variable in an SQL statement, is that allowed? if(isset($where)) // do we got any $where conditionals $where = "WHERE " . implode(' AND ',$where); // Yes, Build a Where Clause else $where = ""; // just leave it blank $Query="SELECT * FROM mytable $where;"; $res=mysql_query($Query); If that is the case it means the recordset is created on-the-fly and is custom for each search criteria, rather than being one recordset that is filtered. Wow, I didn't know that could be done. But how do I get my table rows out of this query for display? Dreamweaver uses the following method: $chooseProducts = mysql_query($query_chooseProducts, $belleuser) or die(mysql_error()); $row_chooseProducts = mysql_fetch_assoc($chooseProducts); Using your code would it be: $res=mysql_query($Query, $belleuser) or die(mysql_error()); $row_res = mysql_fetch_assoc($Query); I hope I'm getting this correctly.

I'm sorry to say I get lost in that code. I don't know where it goes in my code and how the values get extracted for use in my display. I'm still quite new to this and I think this one is a bit over my head. Can you explain it to me?

Barand, Boy do I feel STUPID! It is SO obvious, so obvious that I even used that way of writing in my previous post to describe what I meant. Thanks for helping me out. OK, I have one final (I hope) issue. Below is how my database is structured. I'm giving simplistic examples which are hypothetical because the actual database would have divisions, groups, segments, and categories that wouldn't make any sense to you or anyone else unless you knew the products and how they relate. These are the tables. As you can see PRODUCTS is the main table and contains foreign keys. PRODUCTS null DIVISIONS GROUP SEGMENT CATEGORY prod_id n div_id group_id seg_id cat_id mod_num n div_name group_name seg_name cat_name div_id n group_id n seg_id y cat_id y title y desc y thick y width y height y project y image n Staying with the food hypothetical: FOOD Fresh | Frozen Meats | Fruits & Vegetable | Fruits Melons Berries Citrus Beef Chicken Lamb Oranges Grapefruit As you can see, (I have abbreviated the concept) I have Divisions: Fresh and Frozen. I have Groups: Meat and Fruits & Vegetables. I have Segments: Fruits and Vegetables, and I have Categories: Melons, Berries, Citrus. I want to be able to select based on only two or three criteria sometimes, but the logic fails if I don't include all 4 arguments in my URL. For example, I want to see all fresh meats so I would select Fresh AND Meats which should then display Beef, Chicken, Lamb. But if I don't have seg_id or cat_id in the URL, it returns nothing. How do I get around this? Thanks.

Barand, I do have IS NULL in my WHERE, it follows OR. SELECT * FROM products WHERE products.div_id = %s AND products.grp_id = %s AND products.cat_id = %s OR products.cat_id IS NULL AND products.seg_id = %s OR products.seg_id IS NULL ORDER BY mod_num ASC In a nutshell, what I'm trying to do is select records in my table based on certain criteria. In my table Products I have four foreign keys two of which permit NULL value. It is based on the value of these four fields that I determine which products to show. The following URL shows one of my criteria: <li><a href="products_02.php?div_id=2&grp_id=3&seg_id='NULL'&cat_id=6">Miscellaneous</a></li> You will notice that I have ...AND products.seg_id = %s OR products.seg_id IS NULL... This however results in selecting all records with a NULL value. What I don't understand is why it is selecting all records with a NULL value when the two previous arguments (products.div_id and products.grp_id) are included with AND. Shouldn't this WHERE statement return only records with products.div_id = $_GET['div_id'] AND products.grp_id = $_GET['grp_id'] AND products.seg_id = $_GET['seg_id'] OR products.seg_id IS NULL. I'm trying for something like: (div_id AND grp_id AND (seg_id = something OR null)), but I'm getting ((div_id AND grp_id AND seg_id) OR null). Sorry for my ignorance, I'm new to PHP/MySQL but I'm trying to learn, so your patience is appreciated. Gee, wouldn't it be easier to change the Product table so that the fields that currently allow NULL, require something else like 0? Thanks for your help.

Barand, Thanks for your reply. Is this what you meant? $var1_chooseProducts = "none"; if (isset($_GET['div_id'])) { $var1_chooseProducts = $_GET['div_id']; } $var3_chooseProducts = "IS NULL"; if (isset($_GET['seg_id'])) { $var3_chooseProducts = $_GET['seg_id']; } $var4_chooseProducts = "IS NULL"; if (isset($_GET['cat_id'])) { $var4_chooseProducts = $_GET['cat_id']; } $var2_chooseProducts = "this"; if (isset($_GET['grp_id'])) { $var2_chooseProducts = $_GET['grp_id']; } mysql_select_db($database_belleuser, $belleuser); $query_chooseProducts = sprintf("SELECT * FROM products WHERE products.div_id = %s AND products.grp_id = %s AND products.cat_id = %s OR products.cat_id IS NULL AND products.seg_id = %s OR products.seg_id IS NULL ORDER BY mod_num ASC", GetSQLValueString($var1_chooseProducts, "text"),GetSQLValueString($var2_chooseProducts, "text"),GetSQLValueString($var4_chooseProducts, "text"),GetSQLValueString($var3_chooseProducts, "text")); $chooseProducts = mysql_query($query_chooseProducts, $belleuser) or die(mysql_error()); $row_chooseProducts = mysql_fetch_assoc($chooseProducts); $totalRows_chooseProducts = mysql_num_rows($chooseProducts); In the WHERE clause I have added OR to the product_id's that can have a NULL value so that they will accept either a value from the URL or a NULL value. The problem is it shows ALL products with a seg_id or cat_id of NULL regardless of the values of the first two arguments for div_id and grp_id. Why is that? The WHERE clause has div_id AND grp_id AND seg_id value or NULL so wouldn't that force the seg_id NULL value to have the correct div_id and grp_id values? Thanks for your help.

Thanks Laffin, I'm still new to php/mysql and I'd like to better understand what is happening in the code you suggested and where it fits in my current code. My first question is, should it be if(isset($_POST['var1_chooseProducts'])) or if(isset($_GET['var1_chooseProducts'])) since this is initiated by a URL and not a form. As I read your code, I'm assuming that if(isset($_POST['var1_chooseProducts'])) { $var1= mysql_real_esc( $_POST['var1_chooseProducts']); $where[]="var_id='$var1'"; } should follow my code $var1_chooseProducts = "something"; if (isset($_GET['div_id'])) { $var1_chooseProducts = $_GET['div_id']; } so that your code picks up the value of my var1_chooseProducts and then sends its value to the $where array. Is that right? Then I assume that I would repeat your code after each of my variables to continue to build the $where array. Do I have that right so far? I'm really not clear on where I place your next piece of code. if(isset($where)) $where = "WHERE " . implode(' AND ',$where); else $where = ""; How does this get integrated into the SQL? I appreciate your help.

I'm passing URL parameters to a recordset which uses the values to determine which thumbnail images to display. Here is an example of my link from my SPRY navigation: <li><a href="products_02.php?div_id=2&grp_id=3&seg_id=null&cat_id=4">Pork</a></li> Here is the SQL query that is mostly working: $var1_chooseProducts = "something"; if (isset($_GET['div_id'])) { $var1_chooseProducts = $_GET['div_id']; } $var4_chooseProducts = "NULL"; if (isset($_GET['cat_id'])) { $var4_chooseProducts = $_GET['cat_id']; } $var2_chooseProducts = "whatever"; if (isset($_GET['grp_id'])) { $var2_chooseProducts = $_GET['grp_id']; } mysql_select_db($database_belleuser, $belleuser); $query_chooseProducts = sprintf("SELECT * FROM products WHERE products.div_id = %s AND products.grp_id = %s AND products.cat_id = %s ORDER BY mod_num ASC", GetSQLValueString($var1_chooseProducts, "text"),GetSQLValueString($var2_chooseProducts, "text"),GetSQLValueString($var4_chooseProducts, "text")); $query_limit_chooseProducts = sprintf("%s LIMIT %d, %d", $query_chooseProducts, $startRow_chooseProducts, $maxRows_chooseProducts); $chooseProducts = mysql_query($query_limit_chooseProducts, $belleuser) or die(mysql_error()); $row_chooseProducts = mysql_fetch_assoc($chooseProducts); My problem is with NULL values in the WHERE clause. As you see above, the link for Pork contains seg_id=null, but I did not include it in the query because as soon as I do no thumbnail images will display (I get the broken image icon). When I don't have seg_id (var3) in the query, it works fine, all of the correct thumbnail images display properly – at least for those links that contain all of the arguments in the query. That's where I'm stumped. I have four foreign keys in this table two of which (seg_id and cat_id) permit NULL values. How do I deal with that? On a related issue. I'd like to be able to select based on just div_id which would give me every product in the Division, even though they all have a value for grp_id. But again, if I make my link like this: <li><a href="products_02.php?div_id=2">Fresh Foods</a></li> I get the broken image icon where the thumbnails should be. As the query is designed, I must have a value in each of the fields because of the AND. How can I make the query work when only one of the arguments has value? It seems to me that OR would not be suitable because it would return any record that contains a value in any of the arguments. I believe that if this issue is solved it would also solve the first issue. I'm getting the URL values passed to the query and in many cases it's working fine. Now I just need to understand how to design the query to allow for NULL or fewer values. Thanks for your help.

I'm giving simplistic examples which are hypothetical because the actual database would have divisions, groups, segments, and categories that wouldn't make any sense to you or anyone else unless you knew the products and how they relate. These are the tables. As you can see PRODUCTS is the main table and contains foreign keys. PRODUCTS NULL DIVISIONS GROUP SEGMENT CATEGORY prod_id n div_id group_id seg_id cat_id mod_num n div_name group_name seg_name cat_name div_id n group_id n seg_id y cat_id y title y desc y thick y width y height y project y image n Staying with the food hypothetical: FOOD Fresh | Frozen Meats | Fruits & Vegetable | Fruits Melons Berries Citrus Beef Chicken Lamb Oranges Grapefruit As you can see, (I have abbreviated the concept) I have Divisions: Fresh and Frozen. I have Groups: Meat and Fruits & Vegetables. I have Segments: Fruits and Vegetables, and I have Categories: Melons, Berries, Citrus. I need to be able to select by div_id: Fresh or Frozen, and cat_id: Citrus to be able to see Fresh Oranges. I hope this helps you understand where I'm coming from. Many thanks.

Thorpe, Now a new question. I want the same page to be used for any product selection. That means the selection will not always be based on the same id. For example, if I'm searching for Melons, I need the group_id = 3 (fruit), but if I'm searching for Chicken I need the category_id = 2 (meats). As it is now, my WHERE clause and $_GET[] both have group_id hard coded. How can I make this work where the $_GET and WHERE are determined by what is in the URL query? Thanks for starting me off.

Solved thanks to Thorpe in this post. http://www.phpfreaks.com/forums/index.php/topic,179142.0.html

Thorpe, If you were a GIRL I'd kiss you!!! Thank you for pointing the way. IT WORKED!!! Yay!!!!!

Hey, I stumbled across this post and it just happens that I need to do something similar, although maybe easier. I have a table with sizes saved in decimal format in a FLOAT, but I need to display them in fractional form on-screen. How can I accomplish that? And more specifically, how can I show the lowest denominator possible for a given number. I need precision down to 1/32" but I don't want 3.5" expressed as 3-16/32", I want it expressed as 3-1/2". Hope someone can help.

Matt, This is what I'm trying to understand in my post "Help, struggling to understand navigation based record selection". Can you more fully explain how this is done in Dreamweaver? Maybe answer my post since it contains the specifics of my question. I certainly would appreciate any help.