Jump to content
Larry Ullman's Book Forums

masterlayouts

Members
  • Posts

    64
  • Joined

  • Last visited

  • Days Won

    1

Posts posted by masterlayouts

  1. I asked myself the same question the other days and because when updating a record it probably make no difference if the user updates or not the record (unless it is a mandatory update, so the user must update the record), I guess it will be easier to write:

     

     

    if (mysqli_affected_rows ($dbc) >= 0) {
      // updated or canceled
    }
    elseif {
      // system error
    }

     

    I guess checking for the error works as well:

     

    if (mysqli_affected_rows ($dbc) < 0) {
      // error
    }
    

  2. $f = strip_tags($_POST['textareaField'], '<h1><h2><p><pre><ul><ol><li><div><font><span><strong><br>');

    $r = htmlspecialchars($c);

     

    Is this too much or it should work?

     

    If it is not safe enough I am thinking of replacing each of these tags with a placeholder (like @@<h1>@@ for '<h1>' and so on...) than strip everything before store the string to database. Than when I want to display it I replace the placeholders with their respective tags.

     

    I wouldn't like to do this if not necessary. What do you think?

  3. I would like to replace one of textarea form elements with a wysiwyg editor such as http://nicedit.com/ or http://aloha-editor.org/ so the users may format their posts (pretty much like in this forum). This probably means that the information will be stored in the database as HTML. My question is what is the best way to deal with this things from the security point of view. Should I use strip_tags() and specify what is allowed and probably slim down the editors to something reasonable like eliminating things like inline style for colors, divs for indenting the content? Or maybe it is a better solutions that I am not aware of it so I can safely implement such an editor without (major) changes dealing with html as a whole? Does such an implementation rise security concerns?

  4. Hi Larry, thank you for your reply. Now I am confused as talking with a database designer about my database he said that it is a bad idea to have the meaning in the PHP and I should make all these linked tables in my database. I was very much inclined to do as you suggested here and hardcoded in the PHP (not to mention it is easier). I understand the theoretical advantages of both approaches, but I lack experience to figure it out on myself. My main issue is that I have way too many linked tables (10) not to be concerned with performance.

     

    Also I guess that to populate a dropdown from database I will have to use a query. If I have ten dropdowns/select to populate from database from ten different linked tables, does this mean I will have to make 10 queries? I don't see how and no logic reason to link the "education" table of "country" table for example, nor how this may help to speed up the system.

     

    If I should make 10 queries I guess it is a terrible idea, isn't any way around? Please advise and it will be really helpful if you can explain why it is a bad idea, I would like to learn how you're thinking when your taking such decisions.

  5. I have an application that has quite a few dropdown menus in the UI form that are very good candidates for an "include" page because they appear on several pages and the values from dropdown rarely or never change.

    My problem is that these dropdowns I want to be populated from the database. Is there any way I can create a page and run a query to create these dropdowns and than include this newly created page with dropdowns already populated as result of the query everywhere I need avoiding this to run the same query again and again for every user that is using the application? Thank you.

  6. Thank you for your reply. I understand your point of view and I agree that it makes sense and it is better. The only problem I see with this approach with the "error" array is that you cannot place the error messages next to the form field that did not passed the validation. Any thoughts? I guess a javascript/ajax validation before the server validation done as "error" array model is the best solution. Will you go for something else?

     

    Regarding another post I begin to understand the advantages of OOP. I have a question regarding the creation of an add function that will add to database using prepared statements and having variable bind parameters. I believe you already worked this out and you probably have a CRUD class, will you be so kind to answer that thread? Thank you again.

  7. I've seen this on nettuts, but similar functions are on PHP site. The function is used to display/view/read the results of a query using prepared statements by binding the result dynamically.

     

    <?php

     

    function read()

    {

    $parameters = array();

    $results = array();

     

    $mysql = new mysqli('localhost', 'root', 'root', 'db') or die('There was a problem connecting to the database');

     

    $stmt = $mysql->prepare('SELECT product, price, category FROM products') or die('Problem preparing query');

    $stmt->execute();

     

    $meta = $stmt->result_metadata();

     

    while ( $field = $meta->fetch_field() ) {

     

    $parameters[] = &$row[$field->name];

    }

     

    call_user_func_array(array($stmt, 'bind_result'), $parameters);

     

    while ( $stmt->fetch() ) {

    $x = array();

    foreach( $row as $key => $val ) {

    $x[$key] = $val;

    }

    $results[] = $x;

    }

     

    return $results;

     

    }

     

    $results = read();

    ?>

     

    I am trying for days to make something similar to add to databse and I fail mainly because when I bound the parameters I have to specify the type of data. Also I am not sure how or where should a MySQL function go - like SHA1(?) in values. Is it possible to create such a function that adds to database with parameters specified dynamically? I am interested to know at least if it's possible to make such a thing, maybe there it is impossible and I am trying for nothing. Some directions will be nice (or even a piece of code?).

  8. You suggest CHAR(1) for gender as M/F.

     

    (1)

    If I want to display the information as "Male" / "Female" do I still have to make a linked table to maintain "the logic in MySQL" as you suggested? Or it is ok to make the adjustments in PHP and print "Male" instead of "M" (I guess this means separating the meaning between PHP and MySQL). I take your advise in his full meaning as not making a related table and not separating the meaning between PHP MySQL, so I guess my options will be: (a) I have to print "M" where the application should print "gender" or (b ) create a related table if I want to print "Male". Any of those is better than (c ) printing "Male" instead of "M" with the help of PHP. Is this the correct reading?

     

    (2)

    In a related table should (or it is recommended) the foreign key to be the primary key? I can anticipate the disadvantages as if you delete a record and add a new one the primary key will change/increase so the queries will have to be modified to reflect the change. I also guess that using the primary key of the related table as foreign key of the main table has its advantages as probably the database treat preferential these keys and it is probably faster than having another column as primary key (that probably should be indexed). What do you advise here?

     

    products

    -----------

    id (PK)

    product

    manufacturer

     

    manufacturer

    ----------------

    id (PK) (FK)

    manufacturer

     

    instead of:

     

    manufacturer

    ----------------

    id (PK)

    manufacturer

    productid (FK)

     

    (3)

    Does it make sense here is to use in the lookout table the "productid" as fk and give up to the "id" as primary key? Does all tables need to have a primary key?

     

    (4)

    Related to (1) above, let say we have a table "employees" where users can chose either "m" or "f" (per "male" and "female"). Now let say we have a "recrutingAgent" table where the recruting agent have in fact three options: "male", "female" and "both". If the recruting agent search on the "emplyees" table than the solution is simple. But how about the recruting agen has to post a job, so now you have to put in the "recrutingAgent" table the selection and you have three. What will be the solution here? Char(1) where we put "m/f" and we add some other letter like "b" for "both"?

     

    (5)

    Regarding the (1) above there are other columns that may be defined as char and they are quite frequent, like "countries" and "states". Both can be abbreviated with 2 characters, countries have a standard with 3 characters as well. What is the best approach here? A lookout table where the foreign key is the abbreviation from the main table so we can print the full country name or state name? Or we chose not to print the full name of the country and give up to the lookout table?

     

    If a main table contain several such lookout tables we will have to do several queries with the sole purpose, like in the example with states and countries, to print a dropdown with options where the label will be the country names and state names and the values will be country and state values respectively? Isn't this a waste? I have a main table with 6 such lookout tables, it is still true that the meaning will have to remain in MySQL with the result of making 6 queries for the sake of displaying the drop-downs?

     

    What is the best practice?

     

    main table (anything like a registration form....)

    ------------

    ...

    CountryAbbr

    StateAbbr

    ...

     

    lookout table1

    ---------------

    CountryAbbr

    CountryName

     

    lookout table2

    ---------------

    StateAbbr

    StateName

     

    Thank you for your time. I'm delighted I can learn from your books and get answer to my questions in this forum. I regret that I'm not in college anymore to take your classes.

  9. I kind of need an ajax script to view/add/edit/delete from the database. Your second example display results from database (view), I guess I can work upon it to make it run with delete, but I am not sure how to make it work with edit. Do you have any suggestions or maybe you have such an view/add/edit/delete example at hand?

     

    I am also wondering if the use of eval() in the script does not present a security risk? At least in PHP everywhere I turn says "eval is evil".

     

    Regarding the security issue, from what I've learned from your books the input data must be sanitized and validated. I haven't seen anythink like that in the JavaScript example you offer. Isn't any security precautions to be take or it was skipped for brevity?

     

    I am also wondering if the example offered is still actual considering the book is relatively old and I've seen that you have written a new book "Modern Javascript: Develop and Design". At this time I would probably be satisfied with an example that is doing view/add/edit/delete as this is pretty much what I need. I consider buying this book, is there any chance I will find inside what I am looking for?

     

    Another questions is what happens when the javascript is disabled? In the first example it creates no difficult as the validations occurs in PHP. What happens in the second example? It feels like something is missing...

  10. Having so many related tables to a main table will not decrease performance? If I have things columns like "countries", "states", "gender", "language" and several others in separate tables related to a main table, I will have to use joins and will be quite a few to display the information with meaning (1 is English, 2 is Spanish...etc., I want displayed the label from the related table, not the numeric value from the main table). Having 5-6 joins of this kind isn't too much? Is there a point where it will be better if instead of having this arrangement with related tables and joins to actually write the information in the main table as varchar (i.e. "English" or "Spanish" instead of '1' and '2')?

     

    If i have 3 options it sounds good ENUM, but how about if I have 5 or 10 options (for example a column keeping the level of education: no education, primary school, high school, professional school, college not finished, college finished, masterate, doctorate). These will not change, but there are quite a few. Will still make sense to use ENUM? Is the number that counts or there are other factors?

     

    What would be the rule of thumb here? Thank you.

  11. 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)?.

  12. The information is relevant only to that particular table, this is why I use a new table. I can work with each individual table at a time or with all, in which case I create a view where I join all the tables together (performing some other tasks as well, as computing the "age" from "date of born" and similar). The information it is not redundant, nor an amalgamation. First table is the user, second is profile, third is education, fourth is experience - it is a (part of a) database for a job web site. The question is if I have to take out the "level" column from "users" table and create a new table only to keep track of the progress.

     

    Another question may relate to use of views, but I guess I will start a new thread for relevance.

     

    Otherwise I agree with you and I may say that no stone was left unturn. Thank you for your time.

  13. In the book I've seen that after we check if the form is submitted we assume all values FALSE. Than we perform validation and if validation is passed we take the value. (pag 500, PHP6 and MySQL5). Than it comes a conditional to check if all required variables have a value. What I did was simply to take out the first statement where we assume all values as being FALSE and write it in shortly manner with the ternary operator.

     

    What you are saying it makes sense and I understand your reasons, but PHP is not a strongly typed language and readability it's not a problem in this case as in fact I found it more readable this way.

     

    The examples are something like this:

     

     

    $fn = $ln = (bool) FALSE;

     

    // validation ommited

     

    $fn = $_POST['fn'];

    $ln = $_POST['ln'];

     

    if ($fn & $ln) {

    // do something

    }

     

    How would you re-write it following your advise to keep the datatype of the same type?

    Thank you for your reply.

  14. I will reply backwards. I am not aware of what server factors are important, I guess this goes beyond what your book covers. If you can offer some general guidance will be appreciated, otherwise I understand that your answer should be bind to specifics and it is not that I am unwilling to offer them (because I am), but I do not want to abuse your kindess and get into so much detail that the thread will be of no use for other users.

     

    Regarding the example I gave I put fruits and vegetables as it makes no difference what the rest of the columns contain. The user should fill several tables with information and we need a column to keep track where he/she is: "level". Now you said this design may potential be wrong. Is it because the level column is in the users column? Should I make a table only to keep track the progress?

     

    x862inwg326mv4km4bsx.png

  15. Thank you, I think I understand: "transactions should be used when the failure of a subsequent query would lead to inaccurate data if previous queries were not revoked".

     

    You said the particular example I gave "sound like a potential for a bad database design." I basically have the following design: an user table that holds the login information with a column ("level") that records the progress of the completion of all the other tables. So the application starts with level = 0 (default) for unregister and will increase to 1 if the second table is filled, 2 if the third table is filled and so on.

     

    If the design is wrong, how should I improved it?

     

    On completion of every step (filling a new table) the "level" field will have to be updated in the first table. Is this the case of using transactions?

     

    tablesh.png

     

     

    Regarding the version of MySQL in use and the OS I do not have one selected, I will buy hosting services after the application is ready and I can accommodate any suggestion. I guess the question becomes what version of MySQL and OS is recommended when using InnoDB tables and what is recommended when using MyISAM? Does the fact that it will use utf-8 influence the decision?

     

    Thank you for your help.

  16. Sorry, it is PHP5 Advanced the book that I had in mind. Is it possible to move the post to that forum? The question is still valid and probably applies to any of your books as it is about initializing the variables. If I do not use them until the form is submitted, isn't better not to initialize them until than? Basically I would like to initialize the variables only when the form is submitted and I found this syntax short.

  17. I was wondering in what sense is the information secure by using this linear/symetric encryption methods? Even if you hide the password or the salt they are there somewhere, in the database or in the PHP code.

     

    Asymetric encryption (with public key) suffer of the same problem. As far as you have to encrypt and decrypt the information on the same site it doesn't make any difference what method you are using as far as the keys or passwords and/or salts must pe present on the same machine.

     

    I am a little puzzled by this situation and I was wondering what is expected from a programmer to do in order to safely encrypt the information?

     

    I guess it has something to do with the server settings. At the PHP level I am not sure, but having the code with the key either obsfurcated and encrypted by something like Zend or compiled with bcompiler... will be enough to store the key(s) on the same server? Does anybody knows ways to bind the key to specific hardware or other methods that may work? Or is ths too much?

     

    I would really appreciate your input on this one. Thank you.

  18. If I would like to make a website bilingual, I guess I can follow the example from "internationalization" chapter with the multilanguage forum. However, because I want only two languages isn't faster that instead of having a table with the dictionary and running a query on every page to get the words for a particular language, to have a multidimensional array on every page with the words related only to that page? Certainly is not as flexible as your implementation, but is this a bad approach?

  19. I am working on a job site and there are thousands of records that will be searched. When I chosed the table type I had this in mind. I tend to keep the records as you said, however in some circumstances it will not make sense. In my example when an user is creating his/her cv one will have to add a series of information like "from this period of time to this period of time I worked here and did this". If one of this lines is wrong it makes sense to allow the user to delete it. I will not delete the user account or other things, but in this particular case the information is of no use (it is simply wrong) and it makes sense to delete it. I am sorry I had to enter in details, I try to keep the posts as general as I can to be of use for anybody else. So question become something like: in cases like this one where the delete action is the reasonable approach, but it has just a minor role at the application level, should I change to using transactions and InnoDB instead myISAM and "if... else..."? From what you are saying - "code around the possibilities" - I should maintain MyISAM tables as the application will operate better/faster with them. Code around is an "if... else...". May I generalize and say that when we are having lots of SELECT queries is generally better to use MyISAM tables and "code around" the sections that may make use of transactions?

     

    I would like to understand better his issue. How should we think when decide pro or against transactions in a particular case. I have in mind the following: on the registration process as per your book, we check to see if the email is in the database, if it's not in the database than we enter the details collected from form in the database. Why not using transactions here?

     

    Another example: a table with USERS and a table with USER DETAILS. I add information to USER DETAILS table and I need to update a column in the USER tables that keep track of something like "user status" (it may be "user level" or "user progress" for a registration that require several steps). I can use transactions here, but I don't see in which way it is different than the registration example. There are two tables that I am working upon, not one as in the registration, but the logic is the same. How are these different? What is your thinking process when you decide what to use?

     

    Generally speaking, how do you decide if you need transactions instead of of an "if... else" (as in the registration process: if one row is affected we know the information was added to the database so we move to run another query, in the registration process we add information to database, in my example we updated the status of the user in another table).

     

    Las thing: how do we check if a transaction was succesfull or not and why don't we check the success of the second query in something like the registration process? In the registration example we know the first action happend by checking affected rows, we don't know for sure the second action happened but we assume that it was performed with success. Shouldn't be checked if the second operation happened? If we don't check for the second operation, why do we check for the first operation? Obviously in the registration process it should be checked and it depends upon the result of the first query, but in my example there are two input queries, why don't I simply assume that both queries performed succesfully? As in an delete situation. If I delete everything related to this user from this table I delete everything related to this user from this other table as well. Why and when to check if one, both or none of the queries happened?

  20. Explaining classes you're saying that it is not necessary a better approach or superior approach, but a different approach. Depending of the situation it may be better to use classes or not. From a developer point of view makes sense to use classes as chances are will diminuish the amount of code to write in the future, but from the client point of view it seems to me that most of the time it is better to avoid using classes. It makes little sense to create a blueprint than instantiate your application as a particular case of the class. For example when using the database connector, it is known that the mysql connector is faster than the abstracted PDO driver. If you know from beginning that you will use mysql what is the point of using PDO. Isn't this a diservice for the client? If we're not talking about large projects or even if they are large, approaching class style doesn't constitues a disservice to the client? I understand the advantages for the programmer, however if you were the clients what would you say?

  21. Chapter 4 - the basics - initialize variables prior using them.

     

    When using a form I tend not to initialize the variables before the "if form submitted" condition. Instead after the form is submitted when comes the validation section i do something like this:

     

    $var = (!empty($_POST['var'])) ? $_POST['var'] : (bool) FALSE;

     

    This way I avoid to initiate variables as far as the form is not submitted and I do not need them. Also I found the code easier or at least shorter. Is this wrong or dangerous?

  22. You said that the main drawback would be that you cannot use MyISAM tables.

    From what I understand from your book these are faster than innoDB.

    I am thinking that for an update I would probably prefer transactions from the way you explain it (the example with the money transaction). But for a straight forward delete it looks easier to simply run the queries. Not elegant, maybe a little more time consumin as it has to run multiple queries...

     

    In my particular situation in my application deleting records is not a frequent operation, I will say very rare in the form of deleting everything from multiple tables with reference to an id. (Like on this blog, chances are people will not delete their profile with posts and everything.) Instead a search is a very often operation and there are thousands of records. Under these circumstances will MyISAM table compensate for the brute use of several queries to delete records instead of a single transaction? The downside will be to change the table type to slower innoDB. What do you advise? Thank you.

  23. "The parameters to prepared statements don't need to be quoted; the driver automatically handles this. If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible)."

     

    If this is true, why would anybody ever use something else than prepared statements to handle the SQL queries?

     

    1. Can I have an example when prepared statements are used and SQL injection will occur?

    2. Can I have a reasonable technical reason why NOT to use prepared statements every time? When and why not to use prepared statements?

×
×
  • Create New...