Jump to content
Larry Ullman's Book Forums

Search the Community

Showing results for tags 'database'.

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


Forums

  • Single Editions
    • Modern Javascript: Develop and Design
    • The Yii Book
    • Effortless Flex 4 Development
    • Building a Web Site with Ajax: Visual QuickProject
    • Ruby: Visual QuickStart Guide
    • C++ Programming: Visual QuickStart Guide
    • C Programming: Visual QuickStart Guide
    • Adobe AIR: Visual QuickPro Guide
  • PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide
    • PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide (5th Edition)
    • PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide (4th Edition)
    • PHP 6 and MySQL 5 for Dynamic Web Sites: Visual QuickPro Guide (3rd Edition)
    • PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide (2nd Edition)
    • PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide (1st Edition)
  • PHP for the Web: Visual QuickStart Guide
    • PHP for the Web: Visual QuickStart Guide (5th Edition)
    • PHP for the Web: Visual QuickStart Guide (4th Edition)
    • PHP for the Web: Visual QuickStart Guide (3rd Edition)
    • PHP for the World Wide Web: Visual QuickStart Guide (2nd Edition)
    • PHP for the World Wide Web: Visual QuickStart Guide (1st Edition)
  • Effortless E-commerce with PHP and MySQL
    • Effortless E-Commerce with PHP and MySQL (2nd Edition)
    • Effortless E-Commerce with PHP and MySQL
  • PHP Advanced: Visual QuickPro Guide
    • PHP Advanced and Object-Oriented Programming: Visual QuickPro Guide (3rd Edition)
    • PHP 5 Advanced: Visual QuickPro Guide (2nd Edition)
    • PHP Advanced: Visual QuickPro Guide
  • MySQL: Visual QuickStart Guide
    • MySQL: Visual QuickStart Guide (2nd Edition)
    • MySQL: Visual QuickStart Guide (1st Edition)
  • Other
    • Announcements
    • Newsletter, Blog, and Other Topics
    • Forum Issues
    • Social

Find results in...

Find results that contain...


Date Created

  • Start

    End


Last Updated

  • Start

    End


Filter by number of...

Joined

  • Start

    End


Group


Found 25 results

  1. Hi everyone, I'm building my own shopping website. I haven't figured out how to populate two dropdown lists with available sizes and colors of a product_id from database. Here is the result I've got from querying database. id product_name style size color price image sku stock 1 Neck Top 104 S White $30.00 104.jpg 104-1 5 2 Neck Top 104 S Black $30.00 104.jpg 104-2 5 3 Neck Top 104 M Red $30.00 104.jpg 104-3 10 4 Neck Top 104 L Blue $30.00 104.jpg 104-4 10 5 Neck Top 104 XL Black $30.00 104.jpg 104-5 5 And here are my tables: tblProduct tblSizes tblColors tblSpecific_Products id id id id product_name size color product_id style size_id price color_id image stock category (tops,dresses,bottoms,...) subcategory (bodycon,work,evening,cocktail) Is there any way I can have it done without duplicating the values in each dropdown list? Also for each product_id, how can I store 3 different images (front view,back view and side view) for each color? Does it mean I have to create another attribute table for those images and store their names in database? I'm confused.
  2. When I execute Script 12.8 (delete_entry.php) by clicking the Delete button for an entry on the view_entries.php page, the same row comes up in the Delete an Entry page, no matter from which entry the Delete button was selected. Even if I download Script 12.8 from your website and use that, the results are the same. How do I modify the delete_entry.php script to target the entry from which the Delete button was selected?
  3. So...I'm just getting really frustrated now. I've been reading through several of these chapters several times. My school always tends to not choose books well. On to the issue though. In several sections I've noted it stating "Connect to MySQL and select the forum database." This is where the issue lies. I have MySQL and PHP installed in both operating systems of my laptop (Linux and Windows) and in neither does the referenced database exist. The book does not even state WHERE or HOW to have this included. How is a person supposed to follow the examples if some crucial components were left out? It's like giving directions by saying "Just follow me" while in a car to a person on a bicycle. Saying follow me would be completely worthless. If someone can at least point me in the right direction to actually have these referenced databases...or to a quality book. Either way.
  4. Hey when I import your SQL file into my database I get this error CREATE TABLE `users` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `type` ENUM('member','admin') NOT NULL DEFAULT 'member', `username` VARCHAR(45) NOT NULL, `email` VARCHAR(80) NOT NULL, `pass` VARCHAR(255) NOT NULL, `first_name` VARCHAR(45) NOT NULL, `last_name` VARCHAR(45) NOT NULL, `date_created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `date_expires` DATE NOT NULL, `date_modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE INDEX `username_UNIQUE` (`username` ASC), UNIQUE INDEX `email_UNIQUE` (`email` ASC), INDEX `login` (`email` ASC, `pass` ASC) ) ENGINE = InnoDB DEFAULT CHARSET=utf8; MySQL said: #1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause I can't continue in your book because I can't get pass this Also I believe the same occurs for the Orders table. Please help
  5. Good to be here people. I am not being able to insert my first tasks. Is this a suprise? The error in my logs says: mysqli_fetch_array() /Applications/MAMP/htdocs/myphp/databases/add_tasks.php:64 I have really looked everywhere that I can; no joy. Can someone please help me
  6. I have an images table that has 5 foreign keys that reference 5 other tables, each of those tables uses the images table to store images, when an image gets uploaded, the foreign key in question gets a value associated with it in the images table but the other 4 foreign keys will get NULL values in that row. Is that acceptable, or should I rethink my database design? What would be the best way to go?
  7. Hi Larry, I'm looking at the customers tables design, page 188, and there is something I don't understand. For me it looks like the order_contents table can store only one item per order, with its product_id, quantity and price. But if an order contains several items, how can it be stored in the order_contents table with each product_id, quantity and price? Thank you Francois
  8. I have followed the book closely and looked over the code to make sure it matches Larry's. However, no matter what I do, I am not able to load the session into the database when I run the sessions.php file as per the book. I was hoping on some help to figure out what the issue is. I am running PHP 5.4.10 and Mysql 5.5.29. Thanks for the help in advance! ******This is my db_sessions.inc.php file:********* <?php # Script 3.1 - db_sessions.inc.php /* * This page creates the functional interface for * storing session data in a database. * This page also starts the session. */ // Global variable used for the database // connections in all session functions: $sdbc = NULL; //diff than DBC bc this is meant for sessions... make global for session connections (does not have to be and in fact would only use one in general so change code accordingly) function open_session() { global $sdbc; $sdbc = mysqli_connect( 'localhost', 'root', 'pass', 'advPHP' ) ; return true; //always return somethign except for read function (indicate success Boolean rather than just true) } function close_session() { global $sdbc; return mysqli_close( $sdbc ); } function read_session( $sid ) { global $sdbc; $q = sprintf( 'SELECT data FROM sessions WHERE id="%s"', mysqli_real_escape_string($sdbc, $sid) ); $r = mysqli_query( $sdbc, $q ); if( mysqli_num_rows($r) == 1) { list($data) = mysqli_fetch_array($r, MYSQLI_NUM); return $data; } else { return ''; } } function write_session($sid, $data) { global $sdbc; $q = sprintf( 'REPLACE INTO sessions (id, data) VALUES("%s", %s")', mysqli_real_escape_string($sdbc, $sid), mysqli_real_escape_string($sdbc, $data) ); $r = mysqli_query($sdbc, $q); return true; } function delete_session( $sid ) { global $sdbc; $q = sprintf( 'DELETE FROM sessions WHERE id="%s"', mysqli_real_escape_string($sdbc, $sid) ); $r = mysqli_query( $sdbc, $q ); $_SESSION = []; // return mysqli_affected_rows($sdbc) ; return true; } function clean_session($expire) { global $sdbc; $q = sprintf('DELETE FROM sessions WHERE DATE_ADD (last_accessed, INTERVAL %d SECOND) < NOW()', (int) $expire); $r = mysqli_query($sdbc, $q); return true; } session_set_save_handler( 'open_session', 'close_session', 'read_session', 'write_session', 'delete_session', 'clean_session' ); session_start(); ******And this is my sessions.php file******** <?php # Script 3.2 - sessions.php /* This page does some silly things with sessions. * It includes the db_sessions.inc.php script * so that the session data will be stored in a database. */ // Include the sessions file: // The file already starts the session. require('db_sessions.inc.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 print_r($_SESSION); // 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'; echo '<p> Session data stored. </p>'; } else { echo '<p>Curren session contains <pre>' . print_r($_SESSION, 1) . '</pre> </p>'; } if( isset($_GET['logout']) ) { session_destroy(); echo '<p>Session destroyed.</p>'; } else { echo '<a href="sessions.php?logout=true"> Log Out </a>'; } echo '<p> Session data: <pre>' . print_r($_SESSION, 1) . '</pre></p>'; echo '</body> </html>'; session_write_close(); ?>
  9. 2013 was a banner year for me, I decided to focus my attention on taking my web development know-how to the next level (I happen to live in an area brimming with prospects for knowledgeable web developers). Towards that end I have spent much of my free time in 2013 reading almost everything that Larry Ullman has recently written on PHP, MySQL, JavaScript, Ajax, XML, JSON, and OOP, as well as "The Definitive Guide to HTML5" by Adam Freeman, "Beginning jQuery" by Jack Franklin, "Programming The Mobile Web," by Maximiliano Firtman and others (when, basically, Larry Ullman hadn't written anything dedicated to those subjects...) However, I understood that shifting from one subject to the next at this pace would dampen my long-term retention and protract my course of progress. So, at the same time, I took meticulous notes, creating supplementary documentation, excel spreadsheets, charts, and anything else that I thought would help me to refresh my memory quickly. I finished my jQuery book just before the new year, and having looked around for the next logical topic to research, I have come to the conclusion that I need to shift gears and take a deeper-dive into what I've covered so far. That is, I would like to utilize all of the supplemental materials that I've created and, using Mr. Ullman's suggestions and review and pursue notes to work on the comprehensive working examples, with the goal of finishing them as launch-able sites (in this book a message board and user registration application). Hopefully, people who have read the books will also find value in my charts (they often assume you own the books and reference page numbers), will be able to work alongside me as I (attempt to) finish off the working examples and maybe even be willing to offer some tips and advice on my code. I will be creating new threads for each example I begin in order to cut-down on clutter, following a naming convention of "Ch. 17 Review and Pursue - Message Board Example" For a taste of what is to come, here is my chart of Chapter 6, "Database Design." The starting point is the box with the thick border and bold font in the middle, titled "Database Design aka Data Modeling." From there, you follow the arrows, however supplemental information is represented by dashed lines within the arrows. You will have to find your way back to the main item and continue with the solid arrows when you follow supplementary information. Groupings are contained within dashed boxes. Headings, or big ideas, are represented with solid boxes. Thanks for reading...
  10. I have a question about the database design for my project. Originally I was going to have separate tables for Managers, Owners, Players, etc... since they all require different information. This design created a problem on how to add phone numbers. In order to do this, I was going to have to create separate tables for Managers_phone, Owner_phone, Player_phone since you couldn't have a foreign key in one phone table relate to different tables. Could you just create one table for People, with all of the information that they have in common such as first name, last name, address, etc..., and link phone table to this table, and then have seperate Manager, Owner, player tables to store the information that is unique to each? Also, I am planning on just storing the zip code on the people table, and then have a separate table that matches each zip code to the proper city. Is this fairly common, or do you usually store City, State, and zip in the same table?
  11. in What Was Larry Thinking? #70 it is stated that "You should not index colums that: Allow NULL values" Could you please explain the reason for this?
  12. Just as a short note, the SQL commands to create the three tables for the real-world site project in chapter 15, pg. 556 caused errors in phpMyAdmin with MAMP. I removed all of the single quotation marks throughout the code and everything ran smoothly. I guess this is a phpMyAdmin issue or maybe I'm using a different version than that used in the book. Still love Mr. Ullman's books and have nearly all of them now! Thanks
  13. In one sentence: I've had a read around and if I'm not mistaken this issue is called "object relational impedance mismatch" and I would love to know a good solution to it within Yii. I'm planning to build an application that goes beyond having the typical "user", "comment", "page", "file" database where you have comparatively straight forward models for each table (like in the book). One of the problems I am trying to solve looks something like this: I have several levels of inheritance and I have no idea what the best way is to models that work with Yii. There is http://www.yiiframework.com/wiki/198/single-table-inheritance/ but that doesn't seem particularly clean especially once you have more than two tables (then again, I really can't judge). My head is spinning. If my question isn't clear please tell me what to expand on. Cheers
  14. Hi Larry, When I test the DAO in your book: (page 186) $q = 'SELECT * FROM page WHERE id=1'; $cmd = Yii::app()->db->createCommand($q); $cmd->setFetchMode(PDO::FETCH_CLASS, 'Page'); $model = $cmd->queryRow(); // Use $model->title et al. I can not retrieve $model->title which is null. Also, I use $model-> attributes, the result is : Array ( [live] => 0 [id] => [user_id] => [title] => [content] => [date_updated] => [date_published] => ) Strangely, only 'live' is not null. The expected result is: Array ( [id] => 1 [user_id] => 1 [live] => 0 [title] => ewwq [content] => wsds [date_updated] => 2013-04-04 23:48:50 [date_published] => 2013-04-04 ) I use other tables and corresponding class to test, it also dose not work. I really hope you can explain it. By the way, I really appreciate your work. Thank You Chun
  15. Hi Larry, and all other experts, I am following your guide on setting up yii for my site, and I am at the stage where I want to design all the database tables before I run Gii for the CRUD/model/view/controllers setups, now I wanted to save the session data onto a database table and have as little possible info in cookies (i'd rather not use cookies unless the user specifically wants to, or i may need to to validate whether the user is using the same machine). anyway, i got the session thing to work with the mysql db and it created the table fine... now i want to try to modify the table structure and create more fields in this table to save for instance, ip, referrer, isp, http headers, screen resolution, os, browser, etc, if i pre make these fields and call functions to read this data upon user visits, can i keep the session in the db for even if it is expired, (and then i hope to copy expired session to gather some metrics, or even for legal purposes, who was on when and where they came from (in case of criminal activity, I assume the authorities may request social networking sites to gather such statistics because it is a requirement that such sites have a report button)). so if i sort this session table out i can then run gii, would gii include this session table too? any tips or advice? thanks a million Larry (or others), your book has probably sold more than u think already, Yii is so nice to use, thanks to you, Kind Regards, Deian
  16. Barely installed Yii and already ran into trouble. I'm running Windows 7 and use Wamp and MySQL for my database. After installation, I went to the url http://localhost/myproject/index.php?r=gii and selected Model Generator. I've edited both main.php and console.php in C:\wamp\www\myproject\protected\config folder to include the following: 'db'=>array( 'connectionString' => 'mysql:host=localhost;dbname=myproject', 'emulatePrepare' => true, 'username' => 'myproject', 'password' => 'thepassword', 'charset' => 'utf8', ) However, when trying to preview the model, the first field "Database Connection" gives me the following error: "A valid database connection is required to run this generator." I had the previous line in comments, and now tried changing into: 'db'=>array( 'connectionString' => 'mysql', ), Although it did not help. I've tried googling around and people have had similar problems, but editing both of the files solved their problems. For some reason it won't work for me. Is 'localhost' all I need to point it to wamp and phpMyAdmin? Can you help?
  17. I have gone into the database and explored what might happen after PayPal has been activated and is live. So I have manually changed some of the expiry dates of my users. This is my code when the registrant originally joins: $q = "INSERT INTO users (username, email, pass, first_name, mid_initial, last_name, agree, date_expires) VALUES ('" . stripslashes($u) . "', '$e', '" . get_password_hash($p) . "', '" . stripslashes($fn) . "', '" . stripslashes($mi) . "', '" . stripslashes($ln) . "', '$agree', SUBDATE(NOW(), INTERVAL 1 DAY) )"; Thus their expiry date is one day less than when they joined. The whole time I have been testing my site I have left these dates and my registrants have always been allowed into the site as long as their passwords and email are correct, even though the login form is asking for a date that is greater than or equal to NOW so that doesn't make sense to me. I am assuming NOW means right this minute. $q = "SELECT id, username, type, IF(date_expires >= NOW(), true, false) FROM users WHERE (email='$e' AND pass='" . get_password_hash($p) . "')"; SO I went in and changed some of the years from the current year to a year in the future. THESE registrants are not being allowed into the site. These registrants are getting the following error message when they try to login. $login_errors['login'] = 'The email address and password do not match those on file. '; This doesn't make any sense to me and I am wondering if this alone would keep the registrant from entering the site once paypal is integrated. I have found that one can only do so much testing when the site is "live" with PayPal because if you try to use a credit card too many times, PayPal blocks you. So I want to try to figure out as many bugs as I can beforehand. Marie
  18. So I am looking at developing a jobsboard, which has three tables: Company - Store details of companies looking to advertise jobs, build profile. Jobs - Store details of Jobs being advertised for a certain period of time. Orders - Store details of the transactions for the jobs being posted. The way it works is the company completes registration form, then has the option of posting jobs which they will be charged a fix fee. Now what foreign key would I store in the Orders table? since I am using Paypal and don't know how much information you can carry over. Would I store the CompanyID or the JobsID or both? PHP5 MySQL 5 Chrome
  19. I have created Rbac with superadmin,admin1,normaluser. I want to check weather the user is logged in as superadmin, admin1,normaluser depending on the database column "access" value in tbl_user table, if "access"=0 then user should be superadmin, if "access"=1 then user should be admin1 and if "access"=2 then user should be normaluser. RBAC authorization hierarchy was created by yiic shell, all the database schema has been defined. I cannot figure out how to proceed further than creating authorization hierarchy and how to write code to check a user according to roles. Here is RbacCommand.php http://pastebin.com/BKdYM4eg
  20. I have entered username and password directly in the database, now when i try to login with the data values in the default login page it shows UNKNOWN_IDENTITY error. I have printed out the error code in the log file, it shows error code is 100 which is i think is unknown identity error, the application is showing the default error which is unknown identity. Here is UserIdentity.php http://pastebin.com/emWFEwZc Here is LoginForm.php http://pastebin.com/r8WxW0CQ Here is SiteController.php http://pastebin.com/VYL3DcXV Here is login.php http://pastebin.com/0BhbhbmU
  21. A database design example commonly available pressumes that one wants to organize a collection of books, and depicts the usual author, title, isbn, etc., breakdown, and may even normalize to several tables. Generally not seen, however, is an example that includes the books contents, i.e., chapters, headings, footnotes, and body copy Any thoughs on why this is? Would MYSQL even be capable of managing the task? ~ David
  22. I am using what I have learned from the Intro to Databases chapter of the book to create a table that will hold user credentials: id, firstname, lastname, username, email, hash, timestamp are the columns. For the registration page, I want the user to submit the data firstname, lastname, username, email, and the password which will become hash. (hash being the md5($_POST['password']) string). More to the point, I want a mechanism to check whether or not the user's desired username is already taken or not. I initally went to page 361 (Retrieving Data from a Database). I found this code, thinking I could use it as a solution: SELECT * FROM users WHERE name='Larry' I figure that I can alter it to read: SELECT * FROM credentials WHERE username="{$_POST['username']}" (credentials being the table containing the relevant data). That code would of course be stored in a $query variable. Would I have to place the code in a loop? I do not think I would have to, since there could only be one username in the table that could match the desired username. How would I go about checking whether the query (or loop) returned results or not? Are there any things I can do to make my current system better? Thank you in advance, Lingolatz
  23. You provide several tips for database optimization (pag 193): 1. chose the best storage engine. If I understand correctly, we basically have to chose between MyISAM and InnoDB. If it is necessary or better to use transactions we have no choice than InnoDB. If it is necessary to use FULLTEXT indexing than we have no choice than MyISAM. Other factors, as you presented, are speed - but in some other thread you said that speed depends of other factors as well, for example the OS. So I guess FULLTEXT indexing and transactions are the dealbrakers, is this correct? 2. Use the smallest datatype possible for each column. This one I do not find it explained in your book. What do you mean? Something along the idea that bit is faster than integer and integer faster than varchar? Let's say we have a table where one of the columns is "language". Let's say we have two values: "English" and "Spanish". Is it correct to assume (from 2 above) that it is better to have this column defined in the database as integer, where 0 will be default and will stay for English and 1 will stay for Spanish and new numbers will stay for new languages. If we have to display the name of the language we will use a conditional or a switch (later on when we have more languages). Is this way of thinking correct? When do you consider this approach innapropriate, for example when the number of languages will be too big so the PHP code will be way longer than MySQL or there are other factors involved? Let's say we would like to create a small application that allows users to submit their preferences about a subject, like what programming language do they love and we want to allow them to select more than one languages. Let's assume we have to index these columns. Considering (1) above I may think as follow: because fulltext index not work with InnoDB i have to go with MyISAM, but MyISAM do not let me use transactions. Indexing is the most important thing about this table, considering that I cannot use transactions I may be willing to make a trade-off and instead to allow users to select any number of programming languages, I will chose a number that may sound reasonable to me, let's say 3, and I will create 3 columns in my table "language1", "language2" and "language3" (instead of a new table to hold languages linked to the first one). I encountered this exemple in all "jobs" sites I visit. I assume because they need FULLTEXT indexing for that particular table they made the trade-off I was talking about. Is this a good example of what you call "overrulling normalization" (pag 167)?.
  24. Hi. I'm creating a site where users can create different "groups". We're at the point where we're designing the database, and we're not sure how to proceed to maintain security, while not compromising performance. Our current design uses one database with three tables per group: settings, members, and message board/forum. Our concern is that if this site becomes what we hope, we may have hundreds or thousands of "groups" each with three tables in the database, the database would be massive. We were also considering having one table for all groups containing the group settings and then two tables per group (members and message board). Or, do we make each group its own separate database? Our site is not going to sell anything or keep personal information but we want to give our end users the best experience possible.
  25. I have a MySQL database model where I have different types of users, each having common field names, like 'username', 'password', 'first_name', 'last_name', etc. etc. I've designed it so that these common fields are stored in a 'base' table called 'users' and specific fields that apply to each type of user are stored in derived 'sub-tables' for each type of user. Here is what it looks like: I know that this is a better design than having the 'common' fields stored in every 'user' table. The problem I'm having is I'm trying to figure out how I'm going insert a new user. What would be the best way to handle this? Initially, I'm thinking I could do an insert like this (let's say I wanted to insert a student): <?php // Begin Transaction // Insert the common data into the 'users' table // Get the last inserted ID // Insert the student-specific data into the 'students' table along with the last inserted ID // Commit // If problem occured // Rollback ?> But that just seems like a very crappy way to do it. I would like to have it done in one swift statement. So I haven't had much luck finding clear solutions online, but I recall one person mentioning the use of updatable views for each subtype, where the view would perform an inner join on the subtype table and the base table, and you could insert and update using the single view. I have tried to create a view but keep getting the error: #1356 - View '[view name]' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them. Where [view name] is the name of my view. I get this error even when just selecting one column from one table and I know the names are right. Would the 'view' approach be the best way to go, assuming I can figure out how to get it to work? Is there a better way than what I've mentioned so far? Much thanks, Zane EDIT: Oh, and the PK 'user_id' is an auto-incremented INT in the users table. EDIT 2: Got my view to work. Turns out I had to specify the SQL SECURITY line as INVOKER instead of the default DEFINER. Going to try to see if I can perform inserts and updates on this view...
×
×
  • Create New...