Search the Community
Showing results for tags 'sql'.
-
Chapter 7: Advanced SQL and MySQL - Fifth Edition of PHP and MySQL for Dynamic Web Sites Visual QuickPro Guide Page 229 - Table 7.3 It says I don't understand the difference between lowering importance and lowering ranking. Doesn't decreasing the importance decrease the ranking? Can someone please explain to me the difference between the 2? Thanks, Varun
-
Hello, Hope you are doing great. I belong to a web development community and recently got hired in a logo design company to manage their site development issues. Basically, the website is designed quite well without any front-end issues but it has some backend issues that needs to be fixed. As they have given me a new task to add up a CMS into the backend of their website. I have already worked on Word press so I know how to configure it but as they want it on Drupal platform, I don’t know much about it. Although I have used Drupal and other CMS sites too but never got a chance to configure it in my previous experiences. So, here is what the error looks like: (see image below) An AJAX HTTP error occurred. HTTP Result Code: 200 Debugging information follows. Path: /drupal/core/install.? rewrite=ok&langcode=en&profile=standard &continue=1&id=1&op=do_nojs&op=do StatusText: OK ResponseText: Fatal error: Maximum execution time of 30 seconds exceeded in C:\xampp\htdocs\drupal\core\lib\Drupal\Core\ Database\Statement.php on line 59 Now, I would really appreciate if you could let me know how to get this issue fixed. Because the website is already built on using PHP so I am sure it won’t be that difficult to handle this error. Looking forward towards your valuable comments.
-
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
-
When I tried to run the downloaded SQL commands in phpMyAdmin, various error messages appeared, stating that no author table exists. So I searched for all occurrences of the word "author" and replaced them with "user", except for this line: `type` enum('public','author','admin') NOT NULL, Is this correct? The file I downloaded is: yiibook2_cms.sql.zip Thanks.
-
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
-
Hi, I hope everyone is well. When running the stored procedure for creating select_products below in phpMYADMIN I get the error: "#1064 - 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 '' at line 10". When I look at the line, the syntax seems to be correct. Line 10 is where "ORDER BY name ASC;" lies, and where the error message is stating the problem resides. Can anyone point me in the right direction? Many Thanks, CREATE PROCEDURE select_products(type VARCHAR(6), cat TINYINT) BEGIN IF type = 'coffee' THEN SELECT gc.description, gc.image, CONCAT("C", sc.id) AS sku, CONCAT_WS(" - ", s.size, sc.caf_decaf, sc.ground_whole, sc.price) AS name, sc.stock FROM specific_coffees AS sc INNER JOIN sizes AS s ON s.id=sc.size_id INNER JOIN general_coffees AS gc ON gc.id=sc.general_coffee_id WHERE general_coffee_id=cat AND stock>0 ORDER by name ASC; ELSEIF type = 'other' THEN SELECT ncc.description AS g_description, ncc.image AS g_image, CONCAT("O", ncp.id) AS sku, ncp.name, ncp.description, ncp.image, ncp.price, ncp.stock FROM non_coffee_products AS ncp INNER JOIN non_coffee_categories AS ncc ON ncc.id=ncp.non_coffee_category_id WHERE non_coffee_category_id=cat ORDER by date_created DESC; END IF; END$$
-
Dear Larry, I am the customer who purchase your book PHP and MySql for Dynamic Website 4th edition. Now I read chapter 9 and reach the section of Ensure Secure SQL. I found that we will use function mysqli_real_escape_string for a security reason. If you have a book in your hand please open to page 287 and read the line 40. $p = mysqli_real_escape_string($dbc, trim($_POST['pass1'])); From here I have a question. We use the mysqli_real_escape_string function to secure sql, so it mean some character might now allow to enter because of security reason. But in form password, user can choose any character that they need (alphabet, number, symbol...) in order to make password difficult to crack. If we use this function, what happend if user need ot use the character that they need and will impact to sql security? How many character will not be allow to use when using mysqli_real_escape_string function? Thank, Kanel
-
Does Anybody know how do do this from end of 1st Q to the rest, Especially Creating the APis and Idexes and Bash code etc? 1. DB Schema Design - Design a basic database to store the following information + A fleet of Vehicles - Include details such as mileage, vin numbers etc + A pool of drivers - Include relevant documentation, e.g. Drivesr license, ID Book + A group of routes - Marking out GPS coords for directions etc. is not necessary, just include a start & end address as well as any other info you believe to be pertinent + A drivers schedule, tying the above 3 tables together 2. SQL skill test CREATE TABLE ¿tUSER¿ ( ¿id¿ bigint(20) NOT NULL AUTO§INCREMENT, ¿id§number¿ varchar(20) NOT NULL, ¿first§names¿ varchar(100) NOT NULL, ¿last§name¿ varchar(100) NOT NULL PRIMARY KEY (¿id¿), <<INDEXES>> ) CREATE TABLE ¿tPROFILE¿ ( ¿id¿ bigint(20) NOT NULL AUTO§INCREMENT, ¿tUSER§id¿ bigint(20) DEFAULT NULL, ¿tTYPES§id¿ bigint(20) DEFAULT NULL, ¿value¿ varchar(100) NOT NULL, PRIMARY KEY (¿id¿), <<INDEXES>> ) CREATE TABLE ¿tTYPES¿ ( ¿id¿ bigint(20) NOT NULL AUTO§INCREMENT, ¿type¿ varchar(100) NOT NULL DEFAULT '', ¿description¿ varchar(255) NOT NULL, ¿deleted¿ tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (¿id¿), <<INDEXES>> ) Apply indexes to the above tables, and design a SINGLE query to retrieve a full list of user information based on: 2.A) an ID number 2. a cellphone number (¿Cellphone¿ is a record in the ¿tTYPES¿ table) 3. Write a re-usable script for importing information into the above 3 tables, from the example dsv file below: - NB - All data of the same type MUST be stored in the same format - NB2- You may assume that if a column doesn't seem to exist for a piece of data, that data type is listed in the tTYPES table <<BOF>> record§numberöid§numberöfirst nameölast nameömsisdnönetworköpointsöcard numberögender 312ö9101011234011öTest JunioröSmithö071 123 4321öMTNö73ö1241551413214444öM 313ö9012023213011öBoböSmithö27743334321öVodacomö3ö1231233232323244öM 314ö8706055678011öFranköFrankinsonö2771 156 1567ö8taö0ö1231123453214444öM 315ö9102078765011öMaryöVan Niekerkö+27(0)711236677öCellCö2ö1278933213214444öF 316ö9005074545011öSusanöWilsonö0821121124öCellCö705ö1231233216544444öF 317ö9101013232011öKatherineöJeevesö+271233214ö8taö112ö1231233678214444öF 318ö9101011234011öMatthewöMatthiasö0711111111öMTNöö1231555213214444öM 319ö9103126666011öMichaelöBayö085-6122-161ö8taö63ö1231244413214444öM 320ö7506023232300öTyroneöOlivierö711234322öCellCö89ö1234563213214444öM 321ö8901020304055öBurtöJacksonö071 4566544öVodacomö1ö4567233213214444öM <<EOF>> 4. After the data has been imported, Write a basic API to wrap the Database. Include the following function points: - Add a new User - Update a User's details - Delete a User - Search users 5. Describe what the following bash statement does: grep ¿date +%Y-%m-%d --date='1 day ago'¿ /path/to/file/FILE§PREFIX§Ö¿date +%Y%m%d --date='1 day ago'¿.dsv ö grep -v 'ERROR' ö cut -d "ö" -f 2 ö sed 's/Ü0/27/' 1. If a resturaunt serves: - 3 types of starters - 5 types of main - N types of drinks - 3 types of desserts + How many different meals are available, if you can order 1 item of each type? + How many different meals are available, if you can order 1 item of each type, BUT you can order 2 drinks as long as you do not order the same drink twice? + How many different meals are available, if you can only order a dessert OR a starter? 2. If you need to profile 1000 users, each with 3 different attributes, and each attribute has 4 possible values - before parsing any of the date: + What can we guarantee about the resultset? 3. A wild director appears. He uses "I want to profile my user database using an additional attribute!" - Describe a ¿super-effective¿ method which we can implement, which will allow us to handle an indeterminate number of this type of request. 4. If we have two seperate tables, the first detailing a list of registered club members, and the second detailing a list of competition entrants (assuming we have a key we can join on), what do/could the following resultsets represent: - The INTERSECT of the tables - The MINUS of the tables - The UNION of the tables
-
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
-
Can anyone help me get the sample data and SQL commands into Workbench? The book says "You can download the complete SQL commands, along with some sample data, from the account page on the book’s Web site." (Page 64) Are these files for use in MySQL Workbench? If so, how can I get the commands and sample data into Workbench? I have already opened the yii_cms.mwb fil in Workbench, and I can see the visual of the table relationships. But I don't know how to import the data and SQL commands. This is not obvious to me, since Workbench is new for me. I have already downloaded and unzipped these files: SQL commands MySQL WorkBench file for the CMS site Edited Code for the CMS Example I am using version 0.5 of Larry Ullman's Yii book. I'm on Win XP, with XAMPP and Workbench running. Any help would be much appreciated. Thanks in advance!
-
Chapter 3 of the 3rd Edition of this book includes some work with a database of US zipcodes borrowed from US census bureau or some similar organization. The database is located here (the smaller database): http://www.federalgo...mentzipcodes.us Some of the latitude and longitude values in the database are empty, which can be checked by running the following query: SELECT * FROM zip_codes WHERE latitude=''; Which outputs a number of results. (Showing rows 0 - 29 (648 total, Query took 0.0042 sec)) Larry offers to replace the empty values with NULL, by running this query: UPDATE zip_codes SET latitude=NULL, longitude=NULL WHERE latitude=''; But nothing happens! (0 row(s) affected. ( Query took 0.0993 sec )) Is there an error in the UPDATE query? What am I missing, why does it fail to update? Would be grateful for your help, as always!
-
Hi everybody. I have used the following code from http://www.yiiframework.com/wiki/361/simple-way-to-use-autocomplete-using-different-id-and-display-value/ . My table name is dog and I'm interested in three attributes: id, name and size. So far I have gotten an autocomplete field working which is populated with the name-attribute values as typed in by the end user. The following code does that: Under extensions: <?php Yii::import("zii.widgets.jui.CJuiAutoComplete"); class myAutoComplete extends CJuiAutoComplete { /** * Run this widget. * This method registers necessary javascript and renders the needed HTML code. */ public function run() { list($name,$id)=$this->resolveNameID(); // Get ID Attribute of actual hidden field containing selected value $attr_id = get_class($this->model).'_'.$this->attribute; if(isset($this->htmlOptions['id'])) $id=$this->htmlOptions['id']; else $this->htmlOptions['id']=$id; if(isset($this->htmlOptions['name'])) $name=$this->htmlOptions['name']; if($this->hasModel()) { echo CHtml::textField($name,$this->value,$this->htmlOptions); echo CHtml::activeHiddenField($this->model, $this->attribute); }else { echo CHtml::textField($name,$this->value,$this->htmlOptions); CHtml::hiddenField($name,$this->value,$this->htmlOptions); } if($this->sourceUrl!==null) $this->options['source']=CHtml::normalizeUrl($this->sourceUrl); else $this->options['source']=$this->source; // Modify Focus Event to show label in text field instead of value if (!isset($this->options['focus'])) { $this->options['focus'] = 'js:function(event, ui) { $("#'.$id.'").val(ui.item.label); return false; }'; } if (!isset($this->options['select'])) { $this->options['select'] = 'js:function(event, ui) { $("#'.$id.'").val(ui.item.label); $("#'.$attr_id.'").val(ui.item.id); }'; } $options=CJavascript::encode($this->options); //$options = $this->options; $js = "jQuery('#{$id}').autocomplete($options);"; $cs = Yii::app()->getClientScript(); $cs->registerScript(__CLASS__.'#'.$id, $js); } } Under models: public static function usersAutoComplete($name='') { $sql= 'SELECT id ,name AS label FROM dog WHERE name LIKE :name'; $name = $name.'%'; return Yii::app()->db->createCommand($sql)->queryAll(true,array(':name'=>$name)); } Under Controllers: public function actionUsersAutocomplete() { $term = trim($_GET['term']) ; if($term !='') { $users = Users::usersAutoComplete($term); echo CJSON::encode($users); Yii::app()->end(); } } And finally under views: $this->widget('ext.myAutoComplete', array( 'model'=>$model, 'attribute'=>'user_id', 'name'=>'user_autocomplete', 'source'=>$this->createUrl('dog/usersAutoComplete'), 'options'=>array( 'minLength'=>'0', ), 'htmlOptions'=>array( 'style'=>'height:20px;', ), )); What I Want to achieve still is have a dropdown list with sizes (small, medium or large) which would be added to my SQL statement. I.e. if the dropdownlist is set to large then something like "....WHERE size='large'...." should be included in the SQL query. Im really not sure how to achieve this. If no records are found then I need to display a "Sorry, no results" message in the autocomplete list and finally if a record is found and selected by the user a button needs to be included to do a database search based upon the 'id' found in the above code. Thank you very much for your help!
-
I have a form with 30+ checkboxes which are named sf1, sf2 etc. and they are to be inserted into a table which has a column for each checkbox to store whether it was checked or not (type enum yes or no). I'll be doing various processing with this list of checkboxes and obviously want to use loops. As part of the validation process, I've created an array $sf, which holds the checked (or not) value of each checkbox and looks similar to $sf = array(1=>'yes','no','yes','no','yes','no','yes','no','no','yes','no','yes','no','yes','no','yes','no','yes','no','no','yes','no','yes','no','yes','no','yes','no','yes','no','no','yes','no'); What is the best way to insert this data into the table? I've been playing around with prepared statements in a for loop. $numFields=count($sf); $q="INSERT INTO survey_fields field1 VALUES '$sf[1]'"; $sf_id = mysqli_insert_id($dbc); //for each sf item except the 1st one for (i=2; i <= $numFields; i++) { $q="UPDATE survey_fields SET field$i VALUES ? WHERE id=$sf_id"; $stmt = mysqli_prepare($dbc,$q); mysqli_stmt_bind_param($stmt, 's', $sf[$i]); I"m getting a 1064 error , check the syntax to use near 'field1 VALUES 'yes'' - on the first insert. Is this being rejected because only 1 field is being inserted when there are 33? All the fields except id are set as type enum('yes','no'), NOT NULL - I have also tried setting all the fields (except id) to NULL but that didn't change the result. A couple of other questions: This method whilst it looks neater involves 33 inserts - I could do just one insert, would that be more efficient (though the query looks horrible)? Is a prepared statement useful here, or would a regular statement do just as well?
-
I have the following database scenario: companies ------- companyID (PK) CompanyName jobs ------- jobID (PK) companyID (FK) description domains ----- domainsID (PK) jobID (FK) domains ('retail', 'it, 'management'...etc.) benefits ------ benefitsID (PK) jobID (FK) benefits ('free transport', 'health insurance'...etc.) Each job in jobs is posted by 1 company only, here I would like to limit the result to 1. Each job may have multiple domains. Each job may have multiple benefits. What would be the easiest way to retrieve the information for a specific job in jobs table? The name of the company from companies table, all the domains associated with the jobID in jobs and all benefits associated with the same jobID in benefits table? What I would like to obtain (using variable named as columns in the tables) is a multidimensional array like: [jobID] array => 0 => string ['description'] 1 => string ['companyName'] 2 => array [benefits] => 0 => string 'some benefits' 1 => string 'another benefit' ... so on for all benefits associated to that jobID 3 => array [domains] => 0 => string 'some domain' 1 => string 'another domain' ... so on for all domains associated to that jobID What gives me problems is the fact that one table has 1 record only and I would like to see it enforced, where the domains and benefits table have a variable number of rows. If I join all I will have a number of records equal to 1 + number of domains with that jobID + number of benefits with that jobID. I can extract the information the way I want it, still I guess I am making a mistake and I have the feeling that it may be a better way to achieve the same result. What query is best fitted to this situation? Is there a better solution (maybe using views) for this complex example?
-
An error occurred during a connection to www.mydomain.co.uk SSL received a record that exceeded the maximum permissible length. (Error code: ssl_error_rx_record_too_long) I am stuck on chapter 6. After signing into paypal sandbox, using the buyer account I created & then clicking the 'go back to my site button,' I got the following error above. How do I solve this please?