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.

I think you can Cloak the other files if you do not want to keep uploading them. Are you highlighting ONLY index.php when Putting?

This was answered in the MySQL forum. http://www.phpfreaks.com/forums/index.php/topic,178437.0.html

Hi All, I'm creating a dynamic website with a products table. Each record has fields to distinguish the product by category, group, type, etc. I want to create one products page to display the products, but I need to select them based on one or more of the distinguishing fields. I have created the site navigation (SPRY horizontal drop-down) for the purpose of selecting products based on their distinguishing features, e.g. Fruits & Vegetables > Fruits > Melons. What I need to know is how do I make the navigation link open the products.php page and show only the products that match those criteria, in the above example, Melons. Do I need to create a recordset (Melons, etc.) for each possible search criteria and reference that in the navigation link? Or can I use one general recordset, which contains all of the fields I may need to search, that can somehow be limited through the navigation link? It seems to me that having as many recordsets in a page as there are choices, would be unwieldy and needlessly complex. How is this normally handled? And how would it be done using DWCS3? Many many thanks.

revraz, Thank you so much for your help. Somehow this morning when I came in and tried the query again it worked! Maybe Apache was tired yesterday too, but all is now right with the world. Thanks again for teaching me the proper way to construct a LEFT JOIN. I can now use this with confidence.

Thanks revraz, I think I did not copy enough of the code to show you what was happening. Here is more of the code surrounding the SQL: $maxRows_listProducts = 20; $pageNum_listProducts = 0; if (isset($_GET['pageNum_listProducts'])) { $pageNum_listProducts = $_GET['pageNum_listProducts']; } $startRow_listProducts = $pageNum_listProducts * $maxRows_listProducts; mysql_select_db($database_belleadmin, $belleadmin); $query_listProducts = "SELECT products.mod_num, products.div_id, products.group_id, products.seg_id, products.cat_id, products.title, products.`desc`, products.thick, products.width, products.height, products.project, products.image, `group`.group_name, category.cat_name, divisions.div_name, segment.seg_name FROM products LEFT JOIN `group` ON (products.group_id = `group`.group_id) LEFT JOIN category ON (products.cat_id = category.cat_id) LEFT JOIN divisions ON (products.div_id = divisions.div_id) LEFT JOIN segment ON (products.seg_id = segment.seg_id) ORDER BY products.mod_num;"; $query_limit_listProducts = sprintf("%s LIMIT %d, %d", $query_listProducts, $startRow_listProducts, $maxRows_listProducts); $listProducts = mysql_query($query_limit_listProducts, $belleadmin) or die(mysql_error()); $row_listProducts = mysql_fetch_assoc($listProducts); if (isset($_GET['totalRows_listProducts'])) { $totalRows_listProducts = $_GET['totalRows_listProducts']; } else { $all_listProducts = mysql_query($query_listProducts); $totalRows_listProducts = mysql_num_rows($all_listProducts); } $totalPages_listProducts = ceil($totalRows_listProducts/$maxRows_listProducts)-1; The LIMIT 0, 20 is part of recordset paging, so I don't know why it is conflicting with the listProducts query. Isn't that a separate query? My brain is too fried after a long day. I think I'll sleep on it and give it another try tomorrow. Thanks for all of your help. At least I feel I have a better understanding of how to construct a proper LEFT JOIN, which I clearly didn't know before. I appreciate your help.

revraz, Thanks. Here's what I've got. mysql_select_db($database_belleadmin, $belleadmin); $query_listProducts = "SELECT products.mod_num, products.div_id, products.group_id, products.seg_id, products.cat_id, products.title, products.`desc`, products.thick, products.width, products.height, products.project, products.image, `group`.group_name, category.cat_name, divisions.div_name, segment.seg_name FROM products LEFT JOIN `group` ON (products.group_id = `group`.group_id) LEFT JOIN category ON (products.cat_id = category.cat_id) LEFT JOIN divisions ON (products.div_id = divisions.div_id) LEFT JOIN segment ON (products.seg_id = segment.seg_id) ORDER BY products.mod_num;"; $query_limit_listProducts = sprintf("%s LIMIT %d, %d", $query_listProducts, $startRow_listProducts, $maxRows_listProducts); $listProducts = mysql_query($query_limit_listProducts, $belleadmin) or die(mysql_error()); $row_listProducts = mysql_fetch_assoc($listProducts);

I'll change the name from GROUP to GRP. Thanks for the heads-up. That is exactly the SQL query, what else could it mean?

revraz, Thanks again for your quick response. First, I noticed that `group` and `desc` are the only field names that have the backtick. Why is that? Are they special in some way? Second, I tried the SQL as you suggested SELECT products.mod_num, products.div_id, products.group_id, products.seg_id, products.cat_id, products.title, products.`desc`, products.thick, products.width, products.height, products.project, products.image, `group`.group_name, category.cat_name, divisions.div_name, segment.seg_name FROM products LEFT JOIN `group` ON (products.group_id = `group`.group_id) LEFT JOIN category ON (products.cat_id = category.cat_id) LEFT JOIN divisions ON (products.div_id = divisions.div_id) LEFT JOIN segment ON (products.seg_id = segment.seg_id) ORDER BY products.mod_num; but I got the following error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; LIMIT 0, 20' at line 1 What does that mean?

Thanks for the quick reply. I did try a LEFT JOIN as seen in the last SQL statement, but it didn't work. Can you tell me what I need to do to make it work?

Hi All, Noobie here, and I really need help. I want to create a product_list page where all of the details of each record are visible so I can check that they were entered properly. The problem is that 4 of the 10 fields in the table are foreign keys and their values were set using drop-down lists. I can show the value of each field but the foreign keys are numeric and I want to show their text value from their respective tables. Below is my original SQL which shows the numeric values. > SELECT * > FROM products > ORDER BY products.mod_num I'm afraid this is way beyond my ability right now, therefore I hope someone will be able to help me create this seemingly impossible join. Below is the full SQL as I have it now. > SELECT products.mod_num, products.div_id, products.group_id, products.seg_id, products.cat_id, products.title, products.`desc`, products.thick, products.width, products.height, products.project, products.image, `group`.group_name, category.cat_name, divisions.div_name, segment.seg_name > FROM products, `group`, category, divisions, segment > WHERE products.group_id = `group`.group_id AND products.cat_id = category.cat_id AND products.div_id = divisions.div_id AND products.seg_id = segment.seg_id > ORDER BY products.mod_num This works except for the fields that allow null values. Plus, the above SQL has the AND in the WHERE clause which means it only shows records in which all are present: Following are the Tables and their fields: 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 As you can see, of the foreign keys, only div_id and group_id are not null, seg_id and cat_id allow null. I tried to create a left join but it didn't work because I need to join more than two tables: > SELECT products.mod_num, products.div_id, products.group_id, products.seg_id, products.cat_id, products.title, products.`desc`, products.thick, products.width, products.height, products.project, products.image, `group`.group_name, category.cat_name, divisions.div_name, segment.seg_name > FROM products left join `group`, category, divisions, segment > ON products.group_id = `group`.group_id AND products.cat_id = category.cat_id AND products.div_id = divisions.div_id AND products.seg_id = segment.seg_id > ORDER BY products.mod_num Any help would be greatly appreciated. NOTE: I don't know how to get the table definitions to align properly so it may require adjusting the browser window. Sorry.