Bill Posted November 6, 2012 Share Posted November 6, 2012 The stored procedure from chapter 9 get_shopping_cart_contents does not appear to work in a non-stored procedure environment. It works as a stored procedure but not when the whole query is included in the code of the cart.php script. I done a search but I've not found anything. Here is the procedure that I have placed in the cart script: $r = mysqli_query($dbc, 'SELECT CONCAT("O", ncp.id) AS sku, c.quantity, ncc.category, ncp.name, ncp.price, ncp.stock, ncp.energy, sales.price AS sale_price FROM carts AS c INNER JOIN non_coffee_products AS ncp ON c.product_id=ncp.id INNER JOIN non_coffee_categories AS ncc ON ncc.id=ncp.non_coffee_category_id LEFT OUTER JOIN sales ON (sales.product_id=ncp.id AND sales.product_type="other" AND ((NOW() BETWEEN sales.start_date AND sales.end_date) OR (NOW() > sales.start_date AND sales.end_date IS NULL)) ) WHERE c.product_type="other" AND c.user_session_id="$uid" UNION SELECT CONCAT("C", sc.id), c.quantity, gc.category, CONCAT_WS(" - ", s.size), sc.price, sc.stock, sc.energy, sales.price FROM carts AS c INNER JOIN specific_coffees AS sc ON c.product_id=sc.id INNER JOIN sizes AS s ON s.id=sc.size_id INNER JOIN general_coffees AS gc ON gc.id=sc.general_coffee_id LEFT OUTER JOIN sales ON (sales.product_id=sc.id AND sales.product_type="coffee" AND ((NOW() BETWEEN sales.start_date AND sales.end_date) OR (NOW() > sales.start_date AND sales.end_date IS NULL)) ) WHERE c.product_type="coffee" AND c.user_session_id="$uid"'); The error message states that no rows have been returned. Does anyone konw why? System Information: MYSQL 5.5.16, PHP Version 5.3.8, Windows NT, XAMPP 1.7.7 Link to comment Share on other sites More sharing options...
Edward Posted November 6, 2012 Share Posted November 6, 2012 Really wish I could help you on this but unfortunately I can't. I have done the first half of the book but still have to work through Coffee shop, will be starting on it within the next two weeks. That's one hell of a query, it would be hard for someone to understand without having worked through the site for them-self i think but hey may be i will be proven wrong. Link to comment Share on other sites More sharing options...
Antonio Conte Posted November 7, 2012 Share Posted November 7, 2012 Have you tried pasting the query into something like PHPMyAdmin? It will reveal possible errors to you. The only thing I notice is that the variable might be interpreted as plain text. Try replacing this: AND c.user_session_id="$uid" With: AND c.user_session_id="{$uid}" You need to do this with both the first BETWEEN and the last WHERE clause. Other than that, I have no idea. It shouldn't be problem running this as a normal query from what I see. Link to comment Share on other sites More sharing options...
Larry Posted November 7, 2012 Share Posted November 7, 2012 On my blog I've rewritten all of those scripts that use stored procedures to work without stored procedures. You may want to check those posts out. Link to comment Share on other sites More sharing options...
Bill Posted November 7, 2012 Author Share Posted November 7, 2012 I should have express myself more clearly but I did read "Rewriting the E-Commerce Stored Procedures with Standard PHP-MySQL #2, Chapter 9". Here is the link: http://www.larryullm...ql-2-chapter-9/ After putting the get_shopping_cart query from the above article in cart.php and removing the stored procedure call the program returns an error message in relation to "O", "C", "Other", and "Coffee". Once these double quotes are replaced with single quotes, Coffee works and says, "Your Shopping Cart is currently empty!" I then commented out the stored procedure and included the code from rewriting stored procedure chaper 9 in the above link. Nevertheless, the program produces an error message: "An error occurred in script 'C:\xampp\htdocs\larryullman\Sites\ecom_book\ex2\html\cart.php' on line 93: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given Array In other word no rows have been returned! Here is the exact query rewritten for a non-stored procedural evironment from the above article Rewriting stored procedures ch.9: $r = mysqli_query($dbc, "SELECT CONCAT("O", ncp.id) AS sku, c.quantity, ncc.category, ncp.name, ncp.price, ncp.stock, sales.price AS sale_price FROM carts AS c INNER JOIN non_coffee_products AS ncp ON c.product_id=ncp.id INNER JOIN non_coffee_categories AS ncc ON ncc.id=ncp.non_coffee_category_id LEFT OUTER JOIN sales ON (sales.product_id=ncp.id AND sales.product_type='other'AND ((NOW() BETWEEN sales.start_date AND sales.end_date) OR (NOW() > sales.start_date AND sales.end_date IS NULL)) ) WHERE c.product_type="other" AND c.user_session_id='$uid' UNION SELECT CONCAT("C", sc.id), c.quantity, gc.category, CONCAT_WS(" - ", s.size, sc.caf_decaf, sc.ground_whole), sc.price, sc.stock, sales.price FROM carts AS c INNER JOIN specific_coffees AS sc ON c.product_id=sc.id INNER JOIN sizes AS s ON s.id=sc.size_id INNER JOIN general_coffees AS gc ON gc.id=sc.general_coffee_id LEFT OUTER JOIN sales ON (sales.product_id=sc.id AND sales.product_type='coffee' AND ((NOW() BETWEEN sales.start_date AND sales.end_date) OR (NOW() > sales.start_date AND sales.end_date IS NULL)) ) WHERE c.product_type="coffee" AND c.user_session_id='$uid'"); Hopefully this explnation is more concise! Link to comment Share on other sites More sharing options...
Larry Posted November 7, 2012 Share Posted November 7, 2012 I'm surprised you're not also getting parse errors. If you look at your query, it's surround by double quotes but then you also use double quotes within the query. That won't work at all. 1 Link to comment Share on other sites More sharing options...
Bill Posted November 12, 2012 Author Share Posted November 12, 2012 Here is the query with double quotes removed but still generates parse errors: $r = mysqli_query($dbc, 'SELECT CONCAT("O", ncp.id) AS sku, c.quantity, ncc.category, ncp.name, ncp.price, ncp.stock, ncp.energy, ncp.protein, ncp.fat, sales.price AS sale_price FROM carts AS c INNER JOIN non_coffee_products AS ncp ON c.product_id=ncp.id INNER JOIN non_coffee_categories AS ncc ON ncc.id=ncp.non_coffee_category_id LEFT OUTER JOIN sales ON (sales.product_id=ncp.id AND sales.product_type="other" AND ((NOW() BETWEEN sales.start_date AND sales.end_date) OR (NOW() > sales.start_date AND sales.end_date IS NULL)) ) WHERE c.product_type="other" AND c.user_session_id="$uid" UNION SELECT CONCAT("C", sc.id), c.quantity, gc.category, CONCAT_WS(" - ", s.size), sc.price, sc.stock, sc.energy, sc.protein, sc.fat, sales.price FROM carts AS c INNER JOIN specific_coffees AS sc ON c.product_id=sc.id INNER JOIN sizes AS s ON s.id=sc.size_id INNER JOIN general_coffees AS gc ON gc.id=sc.general_coffee_id LEFT OUTER JOIN sales ON (sales.product_id=sc.id AND sales.product_type="coffee" AND ((NOW() BETWEEN sales.start_date AND sales.end_date) OR (NOW() > sales.start_date AND sales.end_date IS NULL)) ) WHERE c.product_type="coffee" AND c.user_session_id="$uid"'); I don't know why it works as a stored procedure but not as a non-procedural statement. Perhaps it's because of syntax errors! Cheers Link to comment Share on other sites More sharing options...
Larry Posted November 12, 2012 Share Posted November 12, 2012 Well, that query won't work because you've got a variable in there and its value won't be inserted when you're using single quotes for the whole thing. 1 Link to comment Share on other sites More sharing options...
Antonio Conte Posted November 12, 2012 Share Posted November 12, 2012 Instead of ="$var", you need to concat the variable on like: ="'.$var.'". This is very basis stuff. Had you tried to echo out the query, like suggested, you would see that quite easily. Link to comment Share on other sites More sharing options...
Edward Posted November 13, 2012 Share Posted November 13, 2012 This is worth checking out if you are stuck with queries. http://www.myquerybuilder.com/codebuilder.php 1 Link to comment Share on other sites More sharing options...
Bill Posted December 11, 2012 Author Share Posted December 11, 2012 Well, here are my latest findings. The query below works in phpmyadmin when the user_session_id is used instead of the $uid variable:- SELECT CONCAT("O", ncp.id) AS sku, c.quantity, ncc.category, ncp.name, ncp.price, ncp.stock, ncp.energy, ncp.protein, ncp.fat, sales.price AS sale_price FROM carts AS c INNER JOIN non_coffee_products AS ncp ON c.product_id=ncp.id INNER JOIN non_coffee_categories AS ncc ON ncc.id=ncp.non_coffee_category_id LEFT OUTER JOIN sales ON (sales.product_id=ncp.id AND sales.product_type="other" AND ((NOW() BETWEEN sales.start_date AND sales.end_date) OR (NOW() > sales.start_date AND sales.end_date IS NULL)) ) WHERE c.product_type="other" AND c.user_session_id="871a5d394995b479efd5a2e9bfce249c" After trying virtually every variation for the variable "$uid" such as ".$uid." the query run using phpmyadmin doesn't show any rows listed. I have noticed that the stored procedure get_shopping_cart_contents defines uid as a char so maybe it has to be defined either in the query or the code of cart.php. Link to comment Share on other sites More sharing options...
Larry Posted December 12, 2012 Share Posted December 12, 2012 Have you confirmed the value of your $uid variable? Because, just to be clear, you're saying that if you take the session ID value and hardcode that into the query and run it in phpMyAdmin, it returns rows, but the exact same query in your PHP script using the variable instead of the hardcoded session ID does not work. Is that correct? 1 Link to comment Share on other sites More sharing options...
Bill Posted December 14, 2012 Author Share Posted December 14, 2012 Yes, that's correct...It returns rows if the cart session_id is hardcoded in phpmyadmin but not with the variable. Link to comment Share on other sites More sharing options...
Larry Posted December 14, 2012 Share Posted December 14, 2012 Okay, and have you confirmed the value of the $uid variable in your PHP script? Link to comment Share on other sites More sharing options...
Bill Posted December 16, 2012 Author Share Posted December 16, 2012 I have been able to echo the $uid variable in the coffee cart.php script when the stored procedure is being called. However, if the mysql query get_shopping_cart_contents is running in a non-stored procedure environment, an error message saying "your shopping cart is empty!" occurs. This error message appears to suggest that the syntax of the mysql query in a non-stored procedure environment is at fault but I am not sure that is the case. Here is the error message: An error occurred in script 'C:\xampp\htdocs\larryullman\Sites\ecom_book\ex2\html\cart.php' on line 117: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given Array ( [0] => Array ( [function] => my_error_handler [args] => Array ( [0] => 2 [1] => mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given [2] => C:\xampp\htdocs\larryullman\Sites\ecom_book\ex2\html\cart.php [3] => 117 [4] => Array ( [GLOBALS] => Array *RECURSION* [_POST] => Array ( ) [_GET] => Array ( ) [_COOKIE] => Array ( [sESSION] => 762d4f78b298d60a1d5279e67a5db64e [__atuvc] => 1|27 ) [_FILES] => Array ( ) [_ENV] => Array ( ) [_REQUEST] => Array ( ) [_SERVER] => Array ( [MIBDIRS] => C:/xampp/php/extras/mibs [MYSQL_HOME] => \xampp\mysql\bin [OPENSSL_CONF] => C:/xampp/apache/bin/openssl.cnf [php_PEAR_SYSCONF_DIR] => \xampp\php [phpRC] => \xampp\php [TMP] => \xampp\tmp [HTTP_ACCEPT] => text/html, application/xhtml+xml, */* [HTTP_REFERER] => http://localhost/larryullman/Sites/ecom_book/ex2/html/cart.php [HTTP_ACCEPT_LANGUAGE] => en-AU [HTTP_USER_AGENT] => Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Win64; x64; Trident/5.0) [HTTP_UA_CPU] => AMD64 [HTTP_ACCEPT_ENCODING] => gzip, deflate [HTTP_HOST] => localhost [HTTP_CONNECTION] => Keep-Alive [HTTP_COOKIE] => SESSION=762d4f78b298d60a1d5279e67a5db64e; __atuvc=1%7C27 [PATH] => C:\Program Files\Common Files\Microsoft Shared\Windows Live;C:\Program Files (x86)\Common Files\Microsoft Shared\Windows Live;C:\windows\system32;C:\windows;C:\windows\System32\Wbem;C:\windows\System32\WindowsPowerShell\v1.0\;C:\Program Files (x86)\ATI Technologies\ATI.ACE\Core-Static;c:\Program Files (x86)\Common Files\Roxio Shared\DLLShared\;c:\Program Files (x86)\Common Files\Roxio Shared\OEM\DLLShared\;c:\Program Files (x86)\Common Files\Roxio Shared\OEM\DLLShared\;c:\Program Files (x86)\Common Files\Roxio Shared\OEM\12.0\DLLShared\;c:\Program Files (x86)\Roxio\OEM\AudioCore\;C:\Program Files (x86)\Windows Live\Shared; [systemRoot] => C:\windows [COMSPEC] => C:\windows\system32\cmd.exe [PATHEXT] => .COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.MSC [WINDIR] => C:\windows [sERVER_SIGNATURE] => Apache/2.2.21 (Win32) mod_ssl/2.2.21 OpenSSL/1.0.0e PHP/5.3.8 mod_perl/2.0.4 Perl/v5.10.1 Server at localhost Port 80 [sERVER_SOFTWARE] => Apache/2.2.21 (Win32) mod_ssl/2.2.21 OpenSSL/1.0.0e PHP/5.3.8 mod_perl/2.0.4 Perl/v5.10.1 [sERVER_NAME] => localhost [sERVER_ADDR] => ::1 [sERVER_PORT] => 80 [REMOTE_ADDR] => ::1 [DOCUMENT_ROOT] => C:/xampp/htdocs [sERVER_ADMIN] => postmaster@localhost [sCRIPT_FILENAME] => C:/xampp/htdocs/larryullman/Sites/ecom_book/ex2/html/cart.php [REMOTE_PORT] => 50470 [GATEWAY_INTERFACE] => CGI/1.1 [sERVER_PROTOCOL] => HTTP/1.1 [REQUEST_METHOD] => GET [QUERY_STRING] => [REQUEST_URI] => /larryullman/Sites/ecom_book/ex2/html/cart.php [sCRIPT_NAME] => /larryullman/Sites/ecom_book/ex2/html/cart.php [php_SELF] => /larryullman/Sites/ecom_book/ex2/html/cart.php [REQUEST_TIME] => 1355634766 ) [live] => [contact_email] => [uid] => 762d4f78b298d60a1d5279e67a5db64e [page_title] => Coffee - Your Shopping Cart [dbc] => mysqli Object ( [affected_rows] => -1 [client_info] => mysqlnd 5.0.8-dev - 20102224 - $Revision: 310735 $ [client_version] => 50008 [connect_errno] => 0 [connect_error] => [errno] => 1064 [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 '0' at line 1 [field_count] => 0 [host_info] => localhost via TCP/IP [info] => [insert_id] => 0 [server_info] => 5.5.16 [server_version] => 50516 [sqlstate] => 42000 [protocol_version] => 10 [thread_id] => 70 [warning_count] => 0 ) [r] => ) ) ) [1] => Array ( [file] => C:\xampp\htdocs\larryullman\Sites\ecom_book\ex2\html\cart.php [line] => 117 [function] => mysqli_num_rows [args] => Array ( [0] => ) ) ) Your Shopping Cart Your shopping cart is currently empty. Link to comment Share on other sites More sharing options...
Larry Posted December 18, 2012 Share Posted December 18, 2012 Actually the code you've posted there suggests that MySQL is reporting an error in the query, too. Link to comment Share on other sites More sharing options...
benster Posted February 8, 2013 Share Posted February 8, 2013 Hi, What is the answer to this thread? I have copied and pasted the code exactly (adjusting for the " or ' as above), $r = mysqli_query($dbc, "SELECT CONCAT('O', ncp.id) AS sku, c.quantity, ncc.category, ncp.name, ncp.price, ncp.stock, sales.price AS sale_price FROM carts AS c INNER JOIN non_coffee_products AS ncp ON c.product_id=ncp.id INNER JOIN non_coffee_categories AS ncc ON ncc.id=ncp.non_coffee_category_id LEFT OUTER JOIN sales ON (sales.product_id=ncp.id AND sales.product_type='other' AND ((NOW() BETWEEN sales.start_date AND sales.end_date) OR (NOW() > sales.start_date AND sales.end_date IS NULL)) ) WHERE c.product_type='other' AND c.user_session_id='$uid' UNION SELECT CONCAT('C', sc.id), c.quantity, gc.category, CONCAT_WS(" - ", s.size, sc.caf_decaf, sc.ground_whole), sc.price, sc.stock, sales.price FROM carts AS c INNER JOIN specific_coffees AS sc ON c.product_id=sc.id INNER JOIN sizes AS s ON s.id=sc.size_id INNER JOIN general_coffees AS gc ON gc.id=sc.general_coffee_id LEFT OUTER JOIN sales ON (sales.product_id=sc.id AND sales.product_type='coffee' AND ((NOW() BETWEEN sales.start_date AND sales.end_date) OR (NOW() > sales.start_date AND sales.end_date IS NULL)) ) WHERE c.product_type='coffee' AND c.user_session_id='$uid'"); (also trying '.$uid.' in the code above) but getting this error message: An error occurred in script 'C:\xampp\htdocs\ecommerce\ex2\html\cart.php' on line 113:mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given Array ([0] => Array([function] => my_error_handler[args] => Array([0] => 2[1] => mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given[2] => C:\xampp\htdocs\ecommerce\ex2\html\cart.php[3] => 113[4] => Array([GLOBALS] => Array*RECURSION*[_POST] => Array()[_GET] => Array()[_COOKIE] => Array([sESSION] => 630116313b7de1e3fd32bc6393e9ca2c)[_FILES] => Array()[live] =>[contact_email] => you@example.com[uid] => 630116313b7de1e3fd32bc6393e9ca2c[page_title] => Coffee - Your Shopping Cart[dbc] => mysqli Object([affected_rows] => -1[client_info] => mysqlnd 5.0.8-dev - 20102224 - $Revision: 310735 $[client_version] => 50008[connect_errno] => 0[connect_error] =>[errno] => 1064[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 '0' at line 1[field_count] => 0[host_info] => localhost via TCP/IP[info] =>[insert_id] => 0[server_info] => 5.5.16[server_version] => 50516[sqlstate] => 42000[protocol_version] => 10[thread_id] => 150[warning_count] => 0)[r] =>)))[1] => Array([file] => C:\xampp\htdocs\ecommerce\ex2\html\cart.php[line] => 113[function] => mysqli_num_rows[args] => Array([0] =>))) An error occurred in script 'C:\xampp\htdocs\ecommerce\ex2\html\cart.php' on line 115:mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean givenArray([0] => Array([function] => my_error_handler[args] => Array([0] => 2[1] => mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given[2] => C:\xampp\htdocs\ecommerce\ex2\html\cart.php[3] => 115[4] => Array([GLOBALS] => Array*RECURSION*[_POST] => Array()[_GET] => Array()[_COOKIE] => Array([sESSION] => 630116313b7de1e3fd32bc6393e9ca2c)[_FILES] => Array()[live] =>[contact_email] => you@example.com[uid] => 630116313b7de1e3fd32bc6393e9ca2c[page_title] => Coffee - Your Shopping Cart[dbc] => mysqli Object([affected_rows] => -1[client_info] => mysqlnd 5.0.8-dev - 20102224 - $Revision: 310735 $[client_version] => 50008[connect_errno] => 0[connect_error] =>[errno] => 1064[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 '0' at line 1[field_count] => 0[host_info] => localhost via TCP/IP[info] =>[insert_id] => 0[server_info] => 5.5.16[server_version] => 50516[sqlstate] => 42000[protocol_version] => 10[thread_id] => 150[warning_count] => 0)[r] =>)))[1] => Array([file] => C:\xampp\htdocs\ecommerce\ex2\html\cart.php[line] => 115[function] => mysqli_num_rows[args] => Array([0] =>))) I have echo the $uid and it does print the user_session_id. If I use the stored procedure, the page works fine. I have managed to convert the other stored procedures fine. Any help would be greatly appreciated. Thanks Apache/2.2.21 (Win32) mod_ssl/2.2.21 OpenSSL/1.0.0e PHP/5.3.8 mod_perl/2.0.4 Perl/v5.10.1 MySQL client version: mysqlnd 5.0.8-dev - 20102224 - $Revision: 310735 $ Link to comment Share on other sites More sharing options...
Larry Posted February 9, 2013 Share Posted February 9, 2013 You've got double quotes in your query string, which is delimited by double quotes. Link to comment Share on other sites More sharing options...
benster Posted February 9, 2013 Share Posted February 9, 2013 Ah yes, Thank you Larry! It now works! To clarify for others, my final code is $r = mysqli_query($dbc, "SELECT CONCAT('O', ncp.id) AS sku, c.quantity, ncc.category, ncp.name, ncp.price, ncp.stock, sales.price AS sale_price FROM carts AS c INNER JOIN non_coffee_products AS ncp ON c.product_id=ncp.id INNER JOIN non_coffee_categories AS ncc ON ncc.id=ncp.non_coffee_category_id LEFT OUTER JOIN sales ON (sales.product_id=ncp.id AND sales.product_type='other' AND ((NOW() BETWEEN sales.start_date AND sales.end_date) OR (NOW() > sales.start_date AND sales.end_date IS NULL)) ) WHERE c.product_type='other' AND c.user_session_id='$uid' UNION SELECT CONCAT('C', sc.id), c.quantity, gc.category, CONCAT_WS(' - ', s.size, sc.caf_decaf, sc.ground_whole), sc.price, sc.stock, sales.price FROM carts AS c INNER JOIN specific_coffees AS sc ON c.product_id=sc.id INNER JOIN sizes AS s ON s.id=sc.size_id INNER JOIN general_coffees AS gc ON gc.id=sc.general_coffee_id LEFT OUTER JOIN sales ON (sales.product_id=sc.id AND sales.product_type='coffee' AND ((NOW() BETWEEN sales.start_date AND sales.end_date) OR (NOW() > sales.start_date AND sales.end_date IS NULL)) ) WHERE c.product_type='coffee' AND c.user_session_id='$uid'"); Link to comment Share on other sites More sharing options...
Recommended Posts