Jump to content
Larry Ullman's Book Forums
Bill

Stored Procedure Ch 9 Rewritten As Query In Cart.Php

Recommended Posts

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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.

  • Upvote 1

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

  • Upvote 1

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

  • Upvote 1

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 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] => 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 $

Share this post


Link to post
Share on other sites

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'");

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...