SitePoint Sponsor

User Tag List

Page 1 of 8 12345 ... LastLast
Results 1 to 25 of 186
  1. #1
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    PHP and MySQL coding tips

    Hi all,

    I thought it would be good to create a thread with a lot of my common PHP and MySQL coding tips that I mention in other topics on here all the time. These are valuable things that I've learned during 1 year with PHP and MySQL. I believe these are good do's and don'ts, that most experienced coders would agree with, and not just my opinions.

    Note: in my examples, I'm using the new arrays ($_GET/$_POST/$_COOKIE/$_SERVER) because I'm assuming that you're using at least PHP 4.1.0. If you have an older version, then use the old, longer arrays ($HTTP_GET_VARS/$HTTP_POST_VARS/$HTTP_COOKIE_VARS/$HTTP_SERVER_VARS). But you should upgrade because of the file-upload vulnerability in unpatched versions prior to 4.1.2. That's a tip by itself!

    PHP

    register_globals
    First and foremost, I believe, is the use of register_globals. For those of you who don't know, register_globals allows you to access variables from forms and URLs (such as file.php?var=foo) as $var in your script -- "magically" created global variables. Unfortunately, this old method continues to be used in most tutorials/examples. The PHP developers seem to have realized that register_globals was a bad idea, though. They discuss Using Register Globals in the PHP manual and recommend turning them off in php.ini:

    Note that register_globals is going to be deprecated (i.e., turned off by default) in the next version of PHP, because it often leads to security bugs.
    ...
    You should do your best to write your scripts so that they do not require register_globals to be on
    In fact, as of PHP 4.2.0, register_globals is now off by default on new PHP installations. By writing code that relies on register_globals being on, you risk having that code not work on some systems! Wouldn't you rather use the preferred method of accessing variables and have your code work on all PHP installations?

    The proper way to access these variables is via their respective arrays. So instead of $var, in the above example, you should use $_GET['var']. Similarly, use $_SERVER['HTTP_USER_AGENT'] instead of just $HTTP_USER_AGENT.

    The main arrays are $_GET, $_POST, $_COOKIE, and $_SERVER, depending on where the variable came from, obviously. You can read more about them, and a couple of others, here and here in the manual.

    Please access your variables via these arrays!


    magic_quotes, addslashes(), and stripslashes()
    magic_quotes_gpc, when on, automatically adds slashes to all GET/POST/COOKIE data so that you don't need to use addslashes() before using GET/POST/COOKIE data in MySQL queries, etc. (e.g. with magic_quotes_gpc OR addslashes(), I'm becomes I\\'m). Well, magic_quotes_gpc is no convenience and just complicates things! Forum member HarryF has written an article entitled Slash 'em: The War Against Magic Quotes.

    Since magic_quotes_gpc can be on or off, you don't know whether to use addslashes() or not. You don't want to use addslashes() when magic_quotes_gpc is on because you'll add too many slashes (e.g. I'm becomes I\\\\'m), which is bad. Use addslashes() if magic_quotes_gpc is off, and don't if it's on (you can find out its setting with get_magic_quotes_gpc()). But you can't use the same code all the time. One workaround is something such as:

    PHP Code:
    if (!get_magic_quotes_gpc()) { $txt addslashes($txt); } 
    Things are further complicated if you want to first manipulate text that has had magic_quotes_gpc applied. You then have some text that has slashes added and some that doesn't. The effect of this is: some text will be wrong whether you use addslashes() or not.

    It's easiest to turn off magic_quotes_gpc, which I recommend, and use addslashes() manually all the time and not worry about the wrong amount of slashes. This is what it says in the recommended php.ini:

    magic_quotes_gpc = Off
    Input data is no longer escaped with slashes so that it can be sent into SQL databases without further manipulation. Instead, you should use the function addslashes() on each input element you wish to send to a database.
    As I said above, if you use addslashes() when magic_quotes_gpc is on, too many slashes will be added. For inserting I'm into MySQL, you want it to be I\\'m (and it will come out as I'm). Using addslashes() with magic_quotes_gpc, however, will give you I\\\\'m. THAT will come out of MySQL as I\\'m, which is not the original text. Most people assume that you are supposed to use stripslashes() when retrieving data from MySQL because otherwise they have slashes in their text. But that's fixing a problem that should never have occurred. If you have to use stripslashes() on text from your database, it's because you added too many slashes when you inserted it. You should never have to use stripslashes() on text from your database. If you do, you need to fix the problem at the source, rather than after the fact.

    You can turn off magic_quotes_gpc in php.ini or like this in a .htaccess file:

    Code:
    <IfModule mod_php4.c>
    	php_flag magic_quotes_gpc off
    </IfModule>
    If that's not possible, you can put the following code at the top of all your files (in a require or include). It will strip the slashes that magic_quotes_gpc added, virtually turning it off.

    Code:
    function strip_magic_quotes($arr)
    {
    	foreach ($arr as $k => $v)
    	{
    		if (is_array($v))
    			{ $arr[$k] = strip_magic_quotes($v); }
    		else
    			{ $arr[$k] = stripslashes($v); }
    	}
    
    	return $arr;
    }
    
    if (get_magic_quotes_gpc())
    {
    	if (!empty($_GET))    { $_GET    = strip_magic_quotes($_GET);    }
    	if (!empty($_POST))   { $_POST   = strip_magic_quotes($_POST);   }
    	if (!empty($_COOKIE)) { $_COOKIE = strip_magic_quotes($_COOKIE); }
    }

    ereg vs preg
    When it comes to the regular expression functions, ereg* and preg*, the preg functions are the clear choice. The preg functions are generally twice as fast as their ereg counterpart. They also support more advanced regular expression operations. I can't think of any reason why you would need to use the ereg functions.

    preg manual page and pattern syntax (long and confusing but pretty good).


    PHP tags
    I recommend always using the full PHP open tag, <?php, rather than the short one, <?. This means also that instead of <?=$var?>, it's better to use the full <?php echo $var?>. Using the full open tag ensures that your code will work on all PHP installations, regardless of the short_open_tag setting.


    Alternative control structure syntax
    I highly recommend staying away from the if (): ... endif; style syntax and sticking with curly braces, if () { ... }. This is the preferred syntax and it makes your code easier to read.


    error_reporting
    You should probably test your code with error_reporting set to at least E_ALL & ~E_NOTICE (all errors except notices) so that you aren't suppressing errors that should be fixed. error_reporting can be set in php.ini or with the error_reporting() function.


    Line breaks
    People want to know how they can retain textarea line breaks in HTML. You should store text in the database in its original format (e.g. with just newlines) and then use nl2br() to convert newlines to HTML <br /> tags on display (thanks to the people here for teaching me that ). That's all good, except for one problem with nl2br(): it doesn't seem to convert \r newlines (edit: this has now been fixed in PHP 4.2.0).

    Windows uses \r\n newlines; *nix uses \n; Mac uses \r.

    nl2br() works correctly on text from Windows/*nix because they contain \n. However, if you get text from a Mac, nl2br() will not convert its newlines (again, fixed in PHP 4.2.0). To remedy this, I use the following bit of code to convert \r\n or \r to \n before inserting it into the database. It won't hurt anything and ensures that nl2br() will work on the \n only newlines on display. Also, it has the side effect of saving 1 byte in the database per newline from Windows (by storing only \n instead of \r\n).

    PHP Code:
    $txt preg_replace('/\r\n|\r/'"\n"$txt); 

    Strings
    1) As a finesse thing, I use single quotes around strings whenever possible (e.g. strings that don't contain variables, single quotes, \n, etc.). This is supposed to make less work for the PHP parser.

    2) When an array variable isn't in a string, put quotes around string-literal keys so they are not regarded as constants:

    PHP Code:
    // OK
    echo $row[$key];

    // Wrong, unless key is a constant
    echo $row[key];

    // Right
    echo $row['key'];

    // OK, since it's in a string
    echo "Text: $row[key]"
    3) Remember, you can break out of PHP mode for large sections of HTML. This is faster than echo'ing and you don't need to escape quotes.


    Quotes around numeric data in queries
    For numeric columns in MySQL, you shouldn't put quotes around any of their values in queries. As our resident database guru, MattR, says, "that is very non-standard and will only work on MySQL." But if it's unknown data, how do you know that it's numeric and not letters that will cause an error? You can make sure that only a number is used in the query by first type-casting the data as int (or float for decimal numbers):

    PHP Code:
    // If id is being passed in the URL
    $id = (int) $_GET['id'];

    $r mysql_query("SELECT * FROM table WHERE id=$id"); 
    Then even if id is set to "abc," the worst that can happen is a 0 will be used in the query. No quotes; no error.



    MySQL

    Column types and attributes
    1) Be familiar with MySQL's column types along with their ranges/lengths. You should use the smallest column type that will hold the data that you expect to store. Most of the time, you probably don't need INT, because MEDIUMINT, SMALLINT, or TINYINT have enough range. Using a smaller type saves space and speeds things up.

    2) Declare all of your columns NOT NULL unless you need to store NULL values (NULL is not the same as 0 or the empty string). If you need to store NULL, you'll know. Again, NOT NULL saves space and speeds things up.

    3) Making INT-family columns UNSIGNED will effectively double the positive range with the same storage requirement. For example, TINYINT's highest value is 127. TINYINT UNSIGNED's highest value is 255.


    Indexes and optimization
    1) First, I sometimes see people create tables like this:

    Code:
    CREATE TABLE table (
    	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    	.....
    	KEY (id),
    	UNIQUE (id),
    	PRIMARY KEY (id)
    	)
    Do not make a column KEY, UNIQUE, and PRIMARY KEY. In this case, KEY and UNIQUE don't help anything. The PRIMARY KEY is a key and it is unique. By specifying 3 indexes, it wastes space and slows down write operations.

    2) Indexes/keys are the key (no pun intended) to fast queries (especially joins). The 3 types of indexes are PRIMARY, UNIQUE, and KEY/INDEX (same thing). It's hard to explain when and how to use indexes, so I'll just point you to some relevant pages in the MySQL manual: Speed of SELECT queries and How MySQL uses indexes.

    3) When you have your indexes setup, use EXPLAIN on SELECT queries to make sure your indexes are actually being used.
    Last edited by DR_LaRRY_PEpPeR; Feb 17, 2003 at 18:43.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  2. #2
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    excellent! this is the reason they made you a mentor ... i'm gonna print out this thread thanks doc

    some mini-tips:
    - instead of thinking of using the regexp functions immediately when you come across a text replacement or pattern-matching task, think thru whether it can be accomplished using sting handling functions like strstr() and str_replace()
    - the if: endif: syntax is not neccessarily difficult to read if you do indent your code properly. it's probably not recommended because most users are assumed to be unfamiliar (previous Pascal users should be familiar with this syntax) with it but i believe it can come in useful when you mix html and php within the if-conditional... something like this:
    PHP Code:
    if (condition)
    {
      // php statements
    ?>
      <html>
      <!-- html code -->
    <?php
    }
    would be just as readable if written like this... plus you don't run the risk of inadvertently leaving out a closing brace... jumping in and out of php sometimes leaves me quite befuddled and having to match the braces is no joke
    PHP Code:
    if (condition):
      //php statements
      ?>
      <html>
      <!-- html code -->
      <?php
    endif;
    all said it's a matter of personal taste as well...

  3. #3
    SitePoint Evangelist
    Join Date
    Oct 2001
    Posts
    592
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow, I must say this is a good piece of text! (Not that I didn't know any of the tips in
    it of course :-) )

    I do have some additions that might or might not be useful. See for yourself:

    ereg vs preg

    Indeed it's much better to use the preg* functions instead of the ereg* functions. But
    more often than not a regular expression isn't needed to replace some text. Most of the
    time you can do a simple str_replace to achieve the same result. This function is
    much, much quicker than preg_replace, and uses less memory. Think twice before using a
    regular expression!

    Casting

    A variable can be cast to some type by putting the type in front of it in braces, like
    (int)$var. This is perfectly legal, but I find it a bit counterintuitive. PHP is
    supposed to be a typeless language, so how could you possibly cast something? You can
    achieve the same result by using the intval and doubleval. They do exactly
    the same thing as the casting operators, but at least to me they make more sense, as they
    are normal functions.

    MySQL column types

    It is not true that using the smallest column type possible leads to query results. The
    reason is that, internally, MySQL uses 32-bit integers for indexes. (At least, last time I
    checked it did.) So when you build an index on an integer-type like SMALLINT or TINYINT,
    it will still become a 32-bit integer in the accompanying index (possibly even resulting
    in worse performance when running queries!). There even was a time MySQL messed up indexes
    on integer columns that weren't 32-bit, but this probably isn't true anymore. (I don't use
    MySQL myself.) Of course it makes good sense to use smaller integer types that aren't
    indexed and have a limited range, like someone's age.

    Another reason for not using anything other than the default INT32 (INT == INT32) is that
    other software might not understand it. If, for example, you use an ODBC connection to a
    MySQL database, you should only use simple data types. Microsoft Access in particular,
    which is used a lot for these kind of things, will freak out otherwise.

    Declaring columns NULL or NOT NULL has nothing to do with optimization! It has to
    do with the design of the database. If some table can have empty fields (as specified in
    the design), it should be NULL, and not 0 or the empty string. This way, when some field
    holds the value 0 (or the empty string), you'll know it's not an empty field. It's a field
    that has an empty value, which is something quite different.

    Indexes and optimizations

    Of course you shouldn't define a single field as KEY, UNIQUE and PRIMARY KEY at the same
    time. But frankly I'm surprised that MySQL doesn't optimize this away. As soon as some
    field is already a PRIMARY KEY, it needn't create two additional indexes. Are you sure
    MySQL doesn't notice this? (If so, it's even more stupid than I thought...)


    Vincent

  4. #4
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by voostind
    Casting

    A variable can be cast to some type by putting the type in front of it in braces, like
    (int)$var. This is perfectly legal, but I find it a bit counterintuitive. PHP is
    supposed to be a typeless language, so how could you possibly cast something? You can
    achieve the same result by using the intval and doubleval. They do exactly
    the same thing as the casting operators, but at least to me they make more sense, as they
    are normal functions.

    <snip>

    Declaring columns NULL or NOT NULL has nothing to do with optimization! It has to
    do with the design of the database. If some table can have empty fields (as specified in
    the design), it should be NULL, and not 0 or the empty string. This way, when some field
    holds the value 0 (or the empty string), you'll know it's not an empty field. It's a field
    that has an empty value, which is something quite different.
    eh? PHP being typeless simply means that you don't have to declare a type for variables... the type of the variable is dynamically 'set' as the variable is declared... saying "This is perfectly legal, but I find it a bit counterintuitive. PHP is
    supposed to be a typeless language, so how could you possibly cast something?"
    is well wrong imho (no offense... my intuition is different from yours vincent )... why? because there is every variable does have a type... using casting operators achieves much the same effect as intval() and probably works like it should like most languages like Java or C++...

    as for declaring fields NOT NULL when you know the field is never gonna be NULL, i think you missed Doctor_Larry_Pepper's point...
    posted by the doc
    Declare all of your columns NOT NULL unless you need to store NULL values (NULL is not the same as 0 or the empty string). If you need to store NULL, you'll know.
    declaring a column NOT NULL saves 1 byte (or 1 bit, i forget... set me right on this)... that optimizes use of the memory used to store the fields... it makes a difference when you've a thousand record table

  5. #5
    SitePoint Evangelist
    Join Date
    Oct 2001
    Posts
    592
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    eh? PHP being typeless simply means that you don't have to declare a type for variables...
    the type of the variable is dynamically 'set' as the variable is declared...
    This is not completely correct, although I do see your point. (So no offence taken :-))

    How about this:

    PHP Code:
    $var1 'foo'
    $var2 8.3
    $var3 
    $var1 $var2 
    You could of course say that $var3 is 'dynamically set' from the result of $var1 and
    $var2, but $var1 used to be a string, and is now evaluated as a number... This is a tricky
    subject, and it gets even trickier when we start talking about class instances.

    Anyway, the PHP manual serves you right, so I won't say anything stupid about it anymore.
    (But I still prefer 'intval($var)' above '(int)$var' :-) ).

    as for declaring fields NOT NULL when you know the field is never gonna be NULL, i think
    you missed Doctor_Larry_Pepper's point...
    Yep. I competely overlooked. Sorry!

    declaring a column NOT NULL saves 1 byte (or 1 bit, i forget... set me right on this)...
    that optimizes use of the memory used to store the fields... it makes a difference when
    you've a thousand record table
    LOL! Even when it's one byte, it wouldn't matter! Thousands times 1 byte is 1,000 bytes is
    (officially) not even 1 kB! On one million records it would save less than 1 MB. Who
    cares? Only in really, really large databases it matters, but then storage is not likely
    to be much of a problem...

    Vincent

  6. #6
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by voostind

    LOL! Even when it's one byte, it wouldn't matter! Thousands times 1 byte is 1,000 bytes is
    (officially) not even 1 kB! On one million records it would save less than 1 MB. Who
    cares? Only in really, really large databases it matters, but then storage is not likely
    to be much of a problem...
    yeah but programmers usually like to optimize everything... the doc is one of them and so am i (most times... when i'm not lazy)... and well actually it saves 1 byte or 1 bit (??) for each field... there're bound to be plenty of records and thus almost exponentially more fields...

    there's a better explanation for this NOT NULL thing... i read it somewhere but i forget... oh well, let the doc defend himself when he comes back

    cheers

    - Joel

  7. #7
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This thread needs a PHP equivalent to an Oscar! Someone make it sticky quick!

    Another section to look at;

    User defined functions
    User defined functions allow you to re-use chunks of your code. Say for example you have an script where you are FTPing a files in a few places. You might declared a function like;
    PHP Code:
    function ftpcopy ($ftpserver,$ftppath,$ftpuser,$ftppassword,$filename) {
        
    $fp 0;    
        
    $fp ftp_connect($ftpserver);
        
    $login ftp_login ($fp$ftpuser$ftppassword);
        
    ftp_chdir($fp$ftppath);
        
    ftp_put($fp$filename$filenameFTP_BINARY);  
        
    ftp_quit($fp);

    You then call this function elsewhere in your code, passing it the variable is needs, e.g.
    PHP Code:
    ftpcopy ("ftp.yourdomain.com","/pub/","anonymous","php@yourdomain.com","myfile.tar.gz"); 
    Globals
    Normally when you declare a function you define the variables it can take and when you call it, you pass it the variables it need.
    There's a bypass to this though: globals. E.g.
    PHP Code:
    function ftpcopy () {
        global 
    $ftpserver,$ftppath,$ftpuser,$ftppassword,$filename;
        
    $fp 0;    
        
    $fp ftp_connect($ftpserver);
        
    $login ftp_login ($fp$ftpuser$ftppassword);
        
    ftp_chdir($fp$ftppath);
        
    ftp_put($fp$filename$filenameFTP_BINARY);  
        
    ftp_quit($fp);

    Later on in your script you can now do this;
    PHP Code:
    $ftpserver "ftp.yourdomain.com";
    $ftppath "/pub/"
    $ftpuser "anonymous";
    $ftppassword "php@yourdomain.com";
    $filename "myfile.tar.gz";

    ftpcopy(); 
    Globals appear to make your life alot easier. But do not succumb to them!

    The downside of globals is they make code extremely hard to read and understand, particularily with large scripts or where you're including files or using classes. One day you're going to declare the wrong variable as global, and be condemned to purgatory, destined to spend the rest of your days debugging the same piece of code.

    In general, it's a good habit to start early, and only use globabls for things like a select few enviroment variables (such as $PHP_SELF and $_SERVER), if you're going to use them at all.

    Of course the real PHP Monks say:

    NEVER USE GLOBALS!

    Remember, globals, like magic_quotes ARE EVIL!

    Some other things it would be nice to hear about on User Defined Functions (but I'm not expert enough to answer well);

    - Functions + Arrays (i.e. passing / returns arrays to functions)
    - Error handling in user defined functions

    And another subject - includes vs. classes
    Last edited by HarryF; Mar 25, 2002 at 10:26.

  8. #8
    SitePoint Evangelist
    Join Date
    Oct 2001
    Posts
    592
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Normally when you declare a function you define the variables it can take and when you call it, you pass it the variables it need.
    There's a bypass to this though: globals.
    Please, please, PLEASE drop this piece of 'advice'!

    Reading the word 'global' instantly makes my heart beat twice as fast, and little wafts of smoke come out of my ears. I can rant and rant and rant for months about these so-called 'globals'.

    But to put it simply: NEVER USE GLOBALS!

    I feel better already.... :-)

    Oh, and to answer this one:

    yeah but programmers usually like to optimize everything...
    Well, I do too. But I think my time is better spent rewriting an algorithm to work in O(N log N) instead of O(N^2) than to save a couple of bytes of harddisk-space. It's much more worth the while...

    Vincent

  9. #9
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Please, please, PLEASE drop this piece of 'advice'!

    Reading the word 'global' instantly makes my heart beat twice as fast, and little wafts of smoke come out of my ears. I can rant and rant and rant for months about these so-called 'globals'.

    But to put it simply: NEVER USE GLOBALS!
    I was trying to illustrate why they're wrong without making them a taboo subject.

    But I've now emphased the part about globals are evil in case anyone wasn't sure.

  10. #10
    SitePoint Enthusiast
    Join Date
    Jun 2001
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    //comments

    doc...thanks for the great tips! they helped a lot.

    my tiny bit of advice...its probably a good idea to add comments to your code (as seen above);
    PHP Code:
    //single line comment

    # another single line comment

    /* multi line comment
       multi line comment */ 
    http://www.php.net/manual/ro/languag...x.comments.php

    k
    Last edited by kamstar; Mar 25, 2002 at 18:23.

  11. #11
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    wow, thanks for the sticky weird!

    first, in the ereg vs preg section, i thought about mentioning that a simple str_replace() will work most of the time and you should only use regex when necessary. but i was hoping people would know that , and just wanted to compare the 2 types of regular expression functions.

    second, type casting: for a short time i did use the intval() function to accomplish the same thing. then i tested it and discovered that simply casting as int is something like 2-3 times faster than using intval(). that makes sense though, since the value doesn't have to be passed to a function, but is simply evaluated as an int.

    third, most of the time you don't need to store NULL values in MySQL. 0 or the empty string works fine. for example, with an email column, if someone doesn't enter an email address, just store the empty string. i can't see any need for NULL.

    one column i do have NULL is an age column. if an age isn't entered, i store NULL, rather than 0. that way, in case i want to do something like SELECT AVG(age) FROM ..., only the people who have an age will be averaged because the grouping functions (except COUNT(*)) ignore NULL rows. if i had instead stored 0's, those rows would lower the average.

    don't use NOT NULL *IF* you need to tell the difference between an empty value (0 or empty string) and no value (NULL).


    Originally posted by voostind
    MySQL column types

    It is not true that using the smallest column type possible leads to query results. The
    reason is that, internally, MySQL uses 32-bit integers for indexes. (At least, last time I
    checked it did.) So when you build an index on an integer-type like SMALLINT or TINYINT,
    it will still become a 32-bit integer in the accompanying index (possibly even resulting
    in worse performance when running queries!). There even was a time MySQL messed up indexes
    on integer columns that weren't 32-bit, but this probably isn't true anymore. (I don't use
    MySQL myself.) Of course it makes good sense to use smaller integer types that aren't
    indexed and have a limited range, like someone's age.

    <snip>

    Declaring columns NULL or NOT NULL has nothing to do with optimization! It has to
    do with the design of the database. If some table can have empty fields (as specified in
    the design), it should be NULL, and not 0 or the empty string. This way, when some field
    holds the value 0 (or the empty string), you'll know it's not an empty field. It's a field
    that has an empty value, which is something quite different.
    i got my information from the MySQL manual. from Get your data as small as possible:

    One of the most basic optimisation is to get your data (and indexes) to take as little space on the disk (and in memory) as possible. This can give huge improvements because disk reads are faster and normally less main memory will be used. Indexing also takes less resources if done on smaller columns.

    • Use the most efficient (smallest) types possible. MySQL has many specialised types that save disk space and memory.
    • Use the smaller integer types if possible to get smaller tables. For example, MEDIUMINT is often better than INT.
    • Declare columns to be NOT NULL if possible. It makes everything faster and you save one bit per column. Note that if you really need NULL in your application you should definitely use it. Just avoid having it on all columns by default.
    only the index pointers (is that what they're called?) are 32-bit. i just checked and changing a SMALLINT column to INT on an 880 row table increased the size of the index file. so obviously SMALLINT takes up less space in the index.

    it's even more important to use smaller types, when possible, on indexed columns. they can be compared faster when doing joins and other searches. and by being smaller, they not only save disk space, but require less disk reads/writes and allow more of the index to be kept in the key_buffer (if it's a large table and assuming the table stays in the table_cache, otherwise its index blocks are released from the key_buffer). the more things you can keep in memory, the less disk access you have. but you know that.

    declaring columns NOT NULL does have something to to with optimization. i'm not sure what the manual means by "you save one bit per column." i just checked on the 880 row table. removing NOT NULL (from one column) made the data file 840 bytes larger. so that looks more like 1 byte per row. of course the savings are multiplied for each column you make NOT NULL.

    when columns are NOT NULL, that's one less value to check for. some examples of where NOT NULL helps:

    Code:
    mysql> EXPLAIN SELECT * FROM table WHERE not_null_col IS NULL;
    +-----------------------------------------------------+
    | Comment                                             |
    +-----------------------------------------------------+
    | Impossible WHERE noticed after reading const tables |
    +-----------------------------------------------------+
    MySQL doesn't need to check the table because not_null_col can't possibly contain a NULL row.

    also, MySQL can do optimizations on things like ... LEFT JOIN t2 ON t1.col=t2.col WHERE t2.col IS NULL. in the manual, for EXPLAIN, it says this for a value in the Extra column:

    Extra
    ...
    Not exists
    MySQL was able to do a LEFT JOIN optimisation on the query and will not examine more rows in this table for the previous row combination after it finds one row that matches the LEFT JOIN criteria. Here is an example for this:

    SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;

    Assume that t2.id is defined with NOT NULL. In this case MySQL will scan t1 and look up the rows in t2 through t1.id. If MySQL finds a matching row in t2, it knows that t2.id can never be NULL, and will not scan through the rest of the rows in t2 that has the same id. In other words, for each row in t1, MySQL only needs to do a single lookup in t2, independent of how many matching rows there are in t2.

    Another reason for not using anything other than the default INT32 (INT == INT32) is that
    other software might not understand it. If, for example, you use an ODBC connection to a
    MySQL database, you should only use simple data types. Microsoft Access in particular,
    which is used a lot for these kind of things, will freak out otherwise.
    i'm aware of that. i don't know if it's an issue for most people, though. if it is, they'll probably know.


    Of course you shouldn't define a single field as KEY, UNIQUE and PRIMARY KEY at the same
    time. But frankly I'm surprised that MySQL doesn't optimize this away. As soon as some
    field is already a PRIMARY KEY, it needn't create two additional indexes. Are you sure
    MySQL doesn't notice this? (If so, it's even more stupid than I thought...)
    it seems to be pretty stupid. when you do a SHOW INDEX FROM table, it shows 3 indexes. i too thought it might ignore the KEY and UNIQUE.
    Last edited by DR_LaRRY_PEpPeR; Mar 25, 2002 at 18:48.

  12. #12
    SitePoint Enthusiast z00om's Avatar
    Join Date
    Dec 2001
    Location
    California
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'll probably be flamed for this one...

    In some cases, people are programming across different platforms, or using various editors. This is a definate hazard! Because different operating systems use different newlines, different spaces sometimes, and things can get really ugly when a file you wrote in windows is saved in unix then you try to edit it! So, I always keep these two practices in mind!

    At the end of every line, add a space. So if you lose all your new lines, most of your code is likely to be in tact and repairable... for example:
    PHP Code:
    <?php 
    if ($action == "whatever") { 
    echo 
    "You told us to do whatever!"

    ?>
    now say you lost all those new lines..
    PHP Code:
    <?php 
    if ($action == "whatever") { echo "You told us to do whatever!"; } ?>
    the code still functions 'cause you put the spaces in! And it's readable. This one goes along with my next one,

    I NEVER USE single line comments. I always use "multi-line" comments, because...

    if you type something like above, and you lose all your new lines again, your comments causes trouble because now everything is ONE LINE, and that ONE LINE is commented out! see:

    PHP Code:
    <?php // whatever check if ($action == "whatever") { echo "You told us to do whatever!"; } ?>
    oops! but, had you used this so-called "multi-line" comment, it'd be okay...

    PHP Code:
    <?php /* whatever check */ if ($action == "whatever") { echo "You told us to do whatever!"; } ?>
    (or atleast I think so?)

    And I know adding spaces adds bytes, but I'm willing to take on those extra bytes if it saves me 3 hours of recovering from new line problems!
    and think: a 1000 line script only takes on about .99 KB... thats not much and usually a script is 100-300 lines, thats only 0.3k!
    [z00om : home]
    [z00om@hotmail.com]
    [z00om : aim]
    [77968493 : icq]

  13. #13
    purple monkey dishwasher scoates's Avatar
    Join Date
    Nov 2001
    Location
    Montreal
    Posts
    794
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    1) if you follow the PEAR standards (or something similar), you'll never write one-line ifs. (EDIT: oops; except when switching platforms.. but still you should translate accordingly)

    2) single-line comments are important. Consider this:
    PHP Code:
    /* whatever check */
    if ($action == "whatever") {
        echo 
    "You told us to do whatever!";

    That works, right? Now consider this:
    PHP Code:
    /* I'm debugging this template, and I want to temporarily ignore this code...
    echo "here's a bunch of code I'll need to comment out when debugging...";
    /* whatever check */
    if ($action == "whatever") {
        echo 
    "You told us to do whatever!";
    }
    */ 
    See what happens? You'll get a parse error on the last line. If you didn't get a parse error, the if would run anyway (because the comment would end after "check ".

    Better (in my opinion):
    PHP Code:
    /*
     I'm debugging this template, and I want to temporarily ignore this code...
    echo "here's a bunch of code I'll need to comment out when debugging...";
    // whatever check
    if ($action == "whatever") {
        echo "You told us to do whatever!";
    }
    */ 
    That works as it's intended. PHP doesn't like nested comments. (well, 4.0.6 is what I'm testing on right now.. maybe 4.1 does it correctly?).

    S

  14. #14
    SitePoint Enthusiast z00om's Avatar
    Join Date
    Dec 2001
    Location
    California
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I must inquire about your comment on one-line ifs? I never told people to write one-line ifs, but if you lose newlines and your code has spaces, it will keep it working until you can repair it. I was not suggesting you write one-line ifs, I was saying thats what'll happen if you don't add a space.

    And as for the other part, well, I think I would just take out the /* */ of the "whatever check" line personally, I'm never in such a hurry I couldn't just take it out. They're just safer IMO.
    [z00om : home]
    [z00om@hotmail.com]
    [z00om : aim]
    [77968493 : icq]

  15. #15
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by z00om
    In some cases, people are programming across different platforms, or using various editors. This is a definate hazard! Because different operating systems use different newlines, different spaces sometimes, and things can get really ugly when a file you wrote in windows is saved in unix then you try to edit it! So, I always keep these two practices in mind!

    At the end of every line, add a space. So if you lose all your new lines, most of your code is likely to be in tact and repairable... for example:

    <snip>
    now say you lost all those new lines..
    <snip>
    the code still functions 'cause you put the spaces in! And it's readable.
    i'm not sure about this. newlines are not "lost" between OS's, they're merely different (which you said). depending on what your editor recognizes, you may not SEE the newlines (e.g. *nix lines in Notepad), but they're there in some form, and PHP sees them. (unless PHP itself would have problems with Mac (\r only) newlines? i may have read something to that effect. maybe it only regards \n as a newline, which *nix and Win have.)

    about spaces: i believe your code will work ok, even without spaces?:

    PHP Code:
    <?php 
    if($action=="whatever"){echo "You told us to do whatever!";}?>
    Last edited by DR_LaRRY_PEpPeR; Mar 26, 2002 at 17:57.

  16. #16
    SitePoint Wizard Aes's Avatar
    Join Date
    Jun 2001
    Location
    Oklahoma
    Posts
    3,392
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Spaces make no difference in PHP (and most other programming languages as well [when not being referenced in strings and such, of course]). PHP will simply ignore any whitespace in a script -- tabs and spaces are only used to make for better understanding by the coders -- it's all the same by the PHP interpreter!

    As a side note
    PHP Code:
    <?php 
    if($action=="whatever"){echo "You told us to do whatever!";}
    ?>
    will work equally well as
    PHP Code:
    <?php
    if($action=="whatever") echo "You told us to do whatever!";
    ?>
    because if-elseif-else statements are interpreted by line -- meaning so long as the conditions are on one line, brakets ({}) are not needed.

    -Colin
    Last edited by Aes; Mar 26, 2002 at 18:56.
    Colin Anderson
    Ambition is a poor excuse for those without
    sense enough to be lazy.

  17. #17
    purple monkey dishwasher scoates's Avatar
    Join Date
    Nov 2001
    Location
    Montreal
    Posts
    794
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP doesn't interpret mac line endings.

    Sorry, I take it back.
    PHP on unix (Linux specifically) doesn't interpret \r as a line ending. PHP on Win32 DOES.

    \r line endings are probably gone with OSX anyway (I certainly HOPE so).

    \n or \r\n are both interpreted as line endings.
    Spaces are moot, as was mentioned.

    S

  18. #18
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by Aes
    because if-elseif-else statements are interpreted by line -- meaning so long as the conditions are on one line, brakets ({}) are not needed.
    braces are not needed for single statements. a second statement on the same line will not be part of the if (). just wanted to clear up any confusion. example:

    PHP Code:
    // a is printed even though it's on a new *line*
    // as it's a single statement
    if (1)
        echo 
    'a';

    // c is printed since it's a second *statement*
    // even though it's on the same line
    if (0) echo 'b'; echo 'c'
    Last edited by DR_LaRRY_PEpPeR; Mar 26, 2002 at 19:55.

  19. #19
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In some cases, people are programming across different platforms, or using various editors.
    One thing that no doubt drives the UNIX VI users (and others) crazy is length of lines.

    If you're developing on Windows and feeling considerate, keep your lines within 80 characters. This generally applies to variables and comments. If you have a long variable, you might want to do something like this;

    PHP Code:
    $query "SELECT";
    $query .= "table1.column2 as name1, ";
    $query .= "table2.column3 as name2 ";
    $query .= "FROM table1, table2 ";
    $query .= "WHERE ";
    $query .= "table1.column1 = table2.column2 ";
    $query .= "ORDER BY ";
    $query .= "table1.column4 DESC"
    80 characters per line is a magic numbers. More than 80 characters does wierd things to editors like VI, and makes code near impossible to read. Believe the 80 char thing goes back to the days of mainframes.

  20. #20
    SitePoint Enthusiast z00om's Avatar
    Join Date
    Dec 2001
    Location
    California
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, you said your code didn't need spaces, but you put...

    PHP Code:
    echo "You told us to do whatever"
    can you do...?
    PHP Code:
    echo"You told us to do whatever"
    or would you have to switch to echo("You told us to do whatever"); ?

    (thats really just a question, its not being critiscized)

    I see your guys point, but there have been incidents where I got a file off my server, opened it up in notepad, and I had not one single new line, they all got deleted, not even that little black square where an unrecognised char was (a unix new line, for instance). They were all gone, and it took less time to re-design the whole site and re-do all those files that to recover it, of course.... it would've been easier if I'd had those spaces... It's just a precaution, and once you have the habbit you don't even notice it.

    HarryF
    you may want to be careful doing that, would your query be correct reading...

    SELECTtable1.column2 as name1...

    you have to remember to add spaces before/after or doing that can be killer!

    Also,

    if you use Edit+ or something, generally you won't have any new line issues or anything... but I use notepad, its plain and simple and with the Notepad in XP I don't need to use anything else 'cause it has all those fancy Line Column readouts and everything...

    also, Dr... thx for the $_GET/$_POST..etc advice, I put that to use immediatly... I just gotta remember to keep notes about when I'm using post or get! heh...
    [z00om : home]
    [z00om@hotmail.com]
    [z00om : aim]
    [77968493 : icq]

  21. #21
    What? Maelstrom's Avatar
    Join Date
    Oct 2001
    Location
    Whistler BC originally from Guelph Ontario
    Posts
    2,175
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by scoates
    1) if you follow the PEAR standards (or something similar), you'll never write one-line ifs. (EDIT: oops; except when switching platforms.. but still you should translate accordingly)
    Does this include the use of

    ($name=="bob")?"Hello Bob":"You aren't bob";

    Just curious because I use this format often. VERY often and if doesn't meet the standards then I might be upset. Probably not but I am curious.
    Maelstrom Personal - Apparition Visions
    Development - PhP || Mysql || Zend || Devshed
    Unix - FreeBSD || FreeBsdForums || Man Pages
    They made me a sitepoint Mentor - Feel free to PM me or Email me and I will see if I can help.

  22. #22
    SitePoint Evangelist
    Join Date
    Oct 2001
    Posts
    592
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The ternary operator is nowhere mentioned in the 'PEAR Coding Standards', so I can't so if you should use it or not. (I do, anyway).

    Also, I wouldn't care much about PEAR. It's not worth it. (*Especially* the database connectivity!)

    Vincent

  23. #23
    Grumpy Mole Man Skunk's Avatar
    Join Date
    Jan 2001
    Location
    Lawrence, Kansas
    Posts
    2,067
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Personally I avoid any code that looks like this:
    Code:
    $something = 'this';
    $something .= 'and this';
    $something .= 'and this';
    $something .= 'and this';
    $something .= 'and this';
    or this:
    Code:
    $something = 'this'
    . 'and this'
    . 'and this'
    . 'and this'
    . 'and this'
    . 'and this'
    . 'and this';
    I much prefer the following:
    Code:
    $something = 'this
    and this
    and this
    and this';
    Why? Because in the first two examples PHP is having to allocate memory for multiple strings and then go through the (relatively) expensive process of "sticking" them together again. If you just declare a string over multiple lines you are avoiding that overhead. You have to be careful to make code readable if you do this but I've never had any problems with it.

    Here's a handy (relatively undocumented) tip. PHP supports the following method of assigning strings (borrowed from Perl):
    Code:
    $string = <<<ENDOFSTRING
    This is a string
    
    It can include both 'single' and "double" quotes 
    without needing to escape them. However, $variables
    will still be interpolated as they are in double 
    quoted strings. Complex variable expressions such as
    {$array['element']} or {$object->property} can also
    be included and will be evaluated if they are included
    in curly braces (they may work without curly braces
    but I tend to include them for added clarity). The
    string will terminate with whatever you specified
    at the start like this:
    ENDOFSTRING;
    Excellent thread btw

  24. #24
    eigo hanasemasu ka? Yes. =) ZuulJin's Avatar
    Join Date
    Dec 2001
    Location
    Japan
    Posts
    655
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I got one...

    When trying to loop through the global arrays (I've only tested the $_POST array) you have to access it by the key name not by number.

    For example:
    Code:
    for ($i = '0'; $i < count($_POST); $i++)
    {
      if ($_POST[$i] == 'on')
      {
        echo $_POST[$i] . ' is on!';
      }
    }
    Would not find the value "on" in the $_POST array. But this bit of code will:

    Code:
    for ($i = '0'; $i < count($_POST); $i++)
    {
      $key = array_search('on', $_POST);
      echo $_POST[$key] . ' is on!';
    }
    Just my contribution to the thread.

    [Z]
    U.S. DoD Member in Japan?
    Choose your base. Buy|Sell. Easy
    @ APO Ads.



  25. #25
    SitePoint Evangelist
    Join Date
    Oct 2001
    Posts
    592
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    for ($i '0'$i count($_POST); $i++)
    {
      
    $key array_search('on'$_POST);
      echo 
    $_POST[$key] . ' is on!';

    Are you sure this is correct? I mean: aren't you finding the same variable count($_POST) times? The above code doesn't work if there are multiple variables in $_POST with the same value 'on', and the loop isn't necessary at all... Why not do this:

    PHP Code:
    foreach ($_POST as $key => $value)
    {
      if (
    $value == 'on')
      {
        echo 
    "$key is on!';
      }

    Vincent


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •