Search the Community

I have this stored procedure that has 2 parameters, the first one is the cartId(which is the IN) and I have the newOrderId(which is the OUT). Now, I know that the inCartId is the cart id which will come from the form or whatever means the cart is been access but I don't understand the OUT newOrderId since that is what MySQL will return. Now, in the PHP calling this Stored procedure, it will look like this. $sql = 'CALL create_order(:cart_id, "What will be here")// The cart_id is what PHP will pass to MySQL, but what will be passed to the 2nd argument since does not before hand what MySQL will return. CREATE PROCEDURE create_order( IN inCartId int, OUT newOrderId int ) BEGIN -- Insert a new record into orders and obtain the new order ID INSERT INTO orders (created_on) VALUES (NOW()); -- Obtain the new Order ID SELECT LAST_INSERT_ID() INTO newOrderId; -- Insert order details in order_detail table INSERT INTO order_detail ( order_id, product_id, attributes, product_name, quantity, unit_cost ) SELECT orderId, p.id, sc.attributes, p.name, sc.quantity, COALESCE( NULLIF( p.discounted_price, 0 ), p.price ) AS unit_cost FROM shopping_cart sc INNER JOIN products p ON sc.product_id = p.id WHERE sc.cart_id = inCartId AND sc.buy_now; -- Save the order's total amount UPDATE orders SET total_amount = ( SELECT SUM( unit_cost * quantity ) FROM order_detail WHERE order_id = orderId ) WHERE id = orderId; -- Clear the shopping cart CALL shopping_cart_empty(inCartId); END

Been studying your book for a while and noticed it is not too much different from the 4th edition. The book's using mysqli_ for database connection, and the php is still version 5.some. Meanwhile, php 7 has come to life and the pdo is ubiquitous nowadays. As well as MVC and more or less object oriented approach in general. Some books i know of (i'm not sure if i can mention them here) went through this and even their hands-on practice evolved from developing a simple CMS to an almost framework-like application. So, my question is: will you update your PHP & MySQL series?

Hi Larry, Just FYI I modified the db_sessions code (pp 82..) for PDO - after a few pitfalls it works beautifully. Thanks for the base code to work with. Cheers, Necuima.

I created various websites (based on Larry's code) that worked fine for years, but after my web hosting company upgraded to PHP Version 5.6.27, I repeatedly receive this error message: "An error occurred in script '.../mysql_connect.php' on line 8: mysql_connect(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead" I was able to suppress that error message on one website by prepending the "@" symbol to the expression, but strangely it did not work on another site. I'd like to know why (as a quick fix), and whether I can do something to suppress error messages for an entire site instead of using "@" multiple times. I'd also love to know how to change my code to work with PDO (preferably) or MySQLi; my web host supports both. It would be great to see an example of PHP handling data with old MySQL code and PHP working with PDO or MySQLi, to see how to "translate" it, so to speak. However, I don't want to use PDO if that requires me to learn Object-Oriented PHP; all my sites are based on procedural PHP. The above error message is generated in response to this mysql_connect.php code: <?php error_reporting(0); // didn't suppress error message ini_set('display_errors',0); // didn't suppress error message DEFINE ('DB_USER', '***'); DEFINE ('DB_PASSWORD', '***'); DEFINE ('DB_HOST', '***'); DEFINE ('DB_NAME', '***'); if ($dbc = @mysql_connect (DB_HOST, DB_USER, DB_PASSWORD)) { // this is the line generating the error message if (@!mysql_select_db (DB_NAME)) { trigger_error("Could not select the database!\n<br />MySQL Error: " . mysql_error()); exit(); } } else { trigger_error("Could not connect to MySQL!\n<br />MySQL Error: " . mysql_error()); exit(); } function escape_data ($data) { if (ini_get('magic_quotes_gpc')) { $data = stripslashes($data); } if (function_exists('mysql_real_escape_string')) { global $dbc; $data = @mysql_real_escape_string (trim($data), $dbc); } else { $data = @mysql_escape_string (trim($data)); } return $data; } ?> Thank you!

I am a OOP-newbie - and I like this book a lot :-) None of the the two classes in the chapter, User and Page, have a $pdo in the constructer. This means that all db-handling(select, insert) is in the controller-pages and not in the classes. There could be a getUserName-method in the User-class, but no. I found another userclass on the internet where the $pdo is in the constructor, and then I can make a getUserName-method. BUT - if I then want to make some of the db-handling in the controller-page and not inside the class, the $pdo is required in the constructor, if I want to fetch data into my object. So I can't do that? Error: Missing argument 1 for User::__construct() It seems like that if I have the $pdo in the constructor, I MUST do all db-handling inside the class. And if I dont have the $pdo in the constructor I MUST do all db-handling outside the class(in the controller page). My question is: when should I have the $pdo in my constructor? Kim K

Hi guys, i read about PDO in the book, then i read some PDO articles.. Most of the articles say that we should turn off PDO::ATTR_EMULATE_PREPARES if using MYSQL latest driver.. is this true? i searched some explanation why with recent mysql i should turn of PDO::ATTR_EMULATE_PREPARES, but unfortunately my newbie brain doesn't understand their explanation... all i know is PDO will send the query to the MYSQL when we use : $stmt = $pdo->prepare('query'); then PDO will send the data(placeholder's value) : $stmt->bindValue(':val', $value, PDO::PARAM_STR); $stmt->execute(); my mysql version : 5.x php version : 5.4 please guide me here , if possible with example codes

SOLVED for solution see the last part of my first post _______________________________________________________________________ hi , i read chapter 3 and try to pass session_set_save_handler with an object, here's the code for my session handler class: <?php class MySessionHandler implements SessionHandlerInterface { protected $conn = NULL; public function open($savePath, $sessionName) { if(is_null($this->conn)) { $dsn = 'mysql:host=localhost;dbname=php_advanced'; $username = 'root'; $password = 'password'; try { $this->conn = new PDO($dsn, $username, $password); $this->conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION); } catch(PDOException $e) { $this->conn = NULL; die('error in open function ' . $e->getMessage()); } } return TRUE; } public function close() { echo '<p>close</p>'; $this->conn = NULL; return TRUE; } public function read($id) { echo '<p>read</p>'; $query = 'SELECT data FROM session_table WHERE session_id = :id'; try { $pdo = $this->conn->prepare($query); $pdo->bindValue(':id', $id); $pdo->execute(); // Kalo query berhasil nemuin id.. if($pdo->rowCount() == 1) { list($sessionData) = $pdo->fetch(); return $sessionData; } return FALSE; } catch(PDOException $e) { $this->conn = NULL; die('error in read function => ' . $e->getMessage()); } } public function write($id, $data) { echo '<p>write</p>'; $query = 'REPLACE INTO session_table(session_id, data) VALUES(:id, :data)'; try { $pdo = $this->conn->prepare($query); $pdo->bindValue(':id', $id); $pdo->bindValue(':data', $data); $pdo->execute(); // return the value whether its success or not return (bool)$pdo->rowCount(); } catch(PDOException $e) { $this->conn = NULL; die('error in write function => ' . $e->getMessage()); } } public function destroy($id) { echo '<p>destroy</p>'; $query = 'DELETE FROM session_table WHERE session_id = :id LIMIT 1'; try { $pdo = $this->conn->prepare($query); $pdo->bindValue(':id', $id); $pdo->execute(); $_SESSION = array(); return (bool)$pdo->rowCount(); } catch(PDOException $e) { $this->conn = NULL; die('error in destroy function => ' . $e->getMessage()); } } public function gc($maxLifeTime) { echo '<p>garbage collection</p>'; $query = 'DELETE FROM session_table WHERE DATE_ADD(last_accessed INTERVAL :time SECOND) < NOW()'; try { $pdo = $this->conn->prepare($query); $pdo->bindValue(':time', $maxLifeTime); $pdo->execute(); return TRUE; } catch(PDOException $e) { $this->conn = NULL; die('error in gc function => ' . $e->getMessage()); } } } $SessionHandler = new MySessionHandler(); session_set_save_handler($SessionHandler); session_name('my_session'); session_start(); i remove the session_write_close on purpose. This probably sounds stupid, but i want to get the session error to learn more.. here's session script(using Larry's code): <?php require('session_class.php'); ?><!doctype html> <html lang="en"> <head> <meta charset="utf-8"> <title>DB Session Test</title> <link rel="stylesheet" href="style.css"> </head> <body> <?php // Store some dummy data in the session, if no data is present: if (empty($_SESSION)) { $_SESSION['blah'] = 'umlaut'; $_SESSION['this'] = 3615684.45; $_SESSION['that'] = 'blue'; // Print a message indicating what's going on: echo '<p>Session data stored.</p>'; } else { // Print the already-stored data: echo '<p>Session Data Exists:<pre>' . print_r($_SESSION, 1) . '</pre></p>'; } // Log the user out, if applicable: if (isset($_GET['logout'])) { session_destroy(); echo '<p>Session destroyed.</p>'; } else { // Otherwise, print the "Log Out" link: echo '<a href="session_link.php?logout=true">Log Out</a>'; } // Reprint the session data: echo '<p>Session Data:<pre>' . print_r($_SESSION, 1) . '</pre></p>'; // Complete the page: echo '</body> </html>'; // Write and close the session: // session_write_close <<<<<--- I REMOVE THIS ON PURPOSE TO GET ERROR ?> but i dont get any error, then i try to use Larry's mysqli script to connect db and it produces error.. can anyone explain why if im using PDO it doesn't generate error? i'm even dont use register_shutdown_function('session_write_close'); in my session class destructor (on purpose) NOTE : I'm doing this on purpose because i want to learn more. the error im expecting is like when im using mysqli connection(connection closed by php at the end of script then session try to write and close but no connection available) : Warning: mysqli_real_escape_string() expects parameter 1 to be mysqli, null given in /var/www/ullman_advance/ch3/ullman_db.php on line 66 Warning: mysqli_real_escape_string() expects parameter 1 to be mysqli, null given in /var/www/ullman_advance/ch3/ullman_db.php on line 66 Warning: mysqli_query() expects parameter 1 to be mysqli, null given in /var/www/ullman_advance/ch3/ullman_db.php on line 67 Warning: mysqli_close() expects parameter 1 to be mysqli, null given in /var/www/ullman_advance/ch3/ullman_db.php on line 33 UPDATE i just tried to pass session_set_save_handler with function callback(using pdo as connection) and it produces error i expected, but still if using MySessionHandler class it doesn't produce any error. The codes in the MySessionHandler class is similar with my new session handler functions. here's the script <?php $conn = NULL; function open_session() { echo '<p>open session</p>'; global $conn; $_dsn = 'mysql:host=localhost;dbname=php_advanced'; $_username = 'root'; $_password = 'monyetsahur'; $conn = new PDO($_dsn, $_username, $_password); $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); return TRUE; } function close_session() { echo '<p>close session</p>'; global $conn; $conn = NULL; return TRUE; } function read_session($sid) { echo '<p>read session</p>'; global $conn; $query = 'SELECT data FROM session_table WHERE session_id = :sid'; $pdo = $conn->prepare($query); $pdo->bindValue(':sid', $sid, PDO::PARAM_STR); $pdo->execute(); if($pdo->rowCount() == 1) { list($session_data) = $pdo->fetch(); echo '<pre>'; print_r($session_data); echo '</pre>'; return $session_data; } else { return ''; } } function write_session($sid, $data) { echo '<p>write session</p>'; global $conn; $query = 'REPLACE INTO session_table(session_id, data) VALUES(:sid, :data)'; $pdo = $conn->prepare($query); $pdo->bindValue(':sid', $sid, PDO::PARAM_STR); $pdo->bindValue(':data', $data, PDO::PARAM_STR); $pdo->execute(); return (bool)$pdo->rowCount(); } function destroy_session($sid) { echo '<p>destroy session </p>'; global $conn; $query = 'DELETE FROM session_table WHERE session_id = :sid'; $pdo = $conn->prepare($query); $pdo->bindValue(':sid', $sid, PDO::PARAM_STR); $pdo->execute(); // clean the session array; $_SESSION = array(); return TRUE } function clean_session($expire) { echo '<p>clean session</p>'; global $conn; $query = 'DELETE FROM session_table WHERE DATE_ADD(last_accessed, INTERVAL :expire SECOND) < NOW()'; $pdo = $conn->prepare($query); $pdo->bindValue(':expire', $expire, PDO::PARAM_INT); $pdo->execute(); return $pdo->rowCount(); } session_set_save_handler('open_session', 'close_session', 'read_session', 'write_session', 'destroy_session', 'clean_session'); session_name('my_session'); session_start(); SOLVED: SOLVED sorry guys my mistake actually its a pretty easy answer why MySessionHandler class doesnt produce error wihtout session_write_close() in the end of script, session_set_save_handler() by default will register session_write_close() to register_shutdown_function() so if u want to make your own shutdown function for session then use : session_set_save_handler($SessionClass, FALSE) if u do this then u must provide session_write_close() in your class destructor source : http://php.net/manual/en/function.session-set-save-handler.php thanks for the tips and your attention

Hi Larry I found your book in my pursue for learning more about PHP. I learn procedural programming with PHP and PDO for Database related operations from the first book I bought. With your book I see you are using mysqli and you cover PDO in chapter 8. I know that: 1. Mysqli provides database specific functions. 2. PDO is an abstraction layer, which means you can easily use your database functions no matter which database you are using. 3. If you go to the OOP rute then PDO is the standard for database functions. I know that there are more things, but to make a long story short, for a beginner...who's making his way into PHP and as a starting point with 5.3 flavor and up . * Should I stick to PDO? * Is mandatory to learn mysqli too? I wish you would have used PDO from a very begining in your book. Still have a lot to learn and assimilate. Apart from that...thanks and you got a new reader Víctor