Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Page 2 of 3 FirstFirst 1 2 3 LastLast
Results 11 to 20 of 22
  1. #11
    Senior Member filburt1's Avatar
    Join Date
    Jul 2002
    Location
    Maryland, US
    Posts
    11,774
    Member #
    3
    Liked
    21 times
    Oracle 9i at least definitely supports it because I wrote a query for it last week that used it.
    filburt1, Web Design Forums.net founder
    Site of the Month contest: submit your site or vote for the winner!

  2.  

  3. #12
    Senior Member rosland's Avatar
    Join Date
    Jul 2003
    Location
    Norway
    Posts
    1,944
    Member #
    2096
    Without getting involved in the original question, but as a comment to the other MySQL statements:

    I think "IS NULL" is supported in MySQL as well as UDF's (User Defined Functions) and stored procedures which can be invoked in a general query.
    Functions can be compiled C or C++ snippets as well as stored complex SQL structures.

    From the manual:
    How MySQL Optimizes IS NULL

    MySQL can do the same optimization on col_name IS NULL that it can do with col_name = constant_value. For example, MySQL can use indexes and ranges to search for NULL with IS NULL.......

    ....SELECT * FROM t1 WHERE t1.a=expr OR t1.a IS NULL;
    With regard to UDF's (again from the manual):
    For the UDF mechanism to work, functions must be written in C or C++ and your operating system must support dynamic loading. The MySQL source distribution includes a file `sql/udf_example.cc' that defines 5 new functions. Consult this file to see how UDF calling conventions work......
    When using aggregate UDFs, MySQL works the following way:

    Call xxx_init() to let the aggregate function allocate the memory it needs to store results.
    Sort the table according to the GROUP BY expression.
    For the first row in a new group, call the xxx_reset() function.
    For each new row that belongs in the same group, call the xxx_add() function.
    When the group changes or after the last row has been processed, call xxx() to get the result for the aggregate.
    Repeat 3-5 until all rows has been processed
    Call xxx_deinit() to let the UDF free any memory it has allocated.
    All functions must be thread-safe (not just the main function, but the initialization and deinitialization functions as well). This means that you are not allowed to allocate any global or static variables that change! If you need memory, you should allocate it in xxx_init() and free it in xxx_deinit()....
    And finally:
    Stored procedures and functions are a new feature in MySQL version 5.0....

    ...Stored procedures also allow you to have libraries of functions in the database server. This is a feature shared by modern application languages that allow such design internally, for example, by using classes. Using these client application language features is beneficial for the programmer even outside the scope of database use.....

    MySQL follows the SQL:2003 syntax for stored procedures, which is also used by IBM's DB2....
    S. Rosland

  4. #13
    Senior Member rosland's Avatar
    Join Date
    Jul 2003
    Location
    Norway
    Posts
    1,944
    Member #
    2096
    So...?
    S. Rosland

  5. #14
    Senior Member Steax's Avatar
    Join Date
    Dec 2006
    Location
    Bandung, Indonesia
    Posts
    1,207
    Member #
    14572
    Err,, can someone actually explain to me what all those SQL commands do? I'm retarded in MySQL and usually am limited to simple queries - I'm much better at manipulating them in PHP.
    Note on code: If I give code, please note that it is simply sample code to demonstrate an effect. It is not meant to be used as-is; that is the programmer's job. I am not responsible to give you support or be held liable for anything that happens when using my code.

  6. #15
    Senior Member
    Join Date
    Jun 2005
    Location
    Atlanta, GA
    Posts
    4,146
    Member #
    10263
    Liked
    1 times
    Code:
    $sql = "SELECT * FROM pages WHERE parent_id " . ($parentId == 0 ? " IS NULL" : " = $parentId");
    So basically if parentId is 0, you have the query:
    Code:
    SELECT * FROM pages WHERE parent_id IS NULL
    This will return pages with a null parent id; i.e., pages that have no parent or are top-level.

    If, on the other hand, parentId is not null (which happens in a recursive call to displayPages), then we check for:
    Code:
    SELECT * FROM pages WHERE parent_id = 5
    Where `5' will be the value of the id passed in. In this case, we'll only fetch direct children.

    Personally, I think things would probably be faster if you did a full-table fetch and then did manipulation in PHP. Something like:
    PHP Code:
    $data mysql_fetch_result'SELECT * FROM pages ORDER BY parent_id' );

    // Shove all the data in an associative array indexed by parent id.
    $pages = Array();
    $curParent 0$curArr = Array();
    while ( 
    $record mysql_fetch_assoc$data ) ) {
        if ( 
    $curParent != $data['id'] && ! ( $curParent == && $data['id'] == null ) )
        {
            
    $pages$curParent ] = $curArr;
            
    $curArr = Array();
        }

        
    array_push$curArr$record );
    }

    displayPages$pages );

    // Transio's function, but slightly modified. Obviously it'd have to be declared
    // above, but whatever.
    function displayPages($pages$parentId=0$level=0) {
        
    // Grab the records from the main variable.
        
    $lines $pages$parentId ];
        foreach ( 
    $lines as $line )
            echo 
    strpad(""$level"-") . $line["title"] . "<br />";
            
    displayPages($line["id"], $level+1);
        }

    As for the while loop:
    Code:
    while ( $record = mysql_fetch_assoc( $data ) )
    mysql_fetch_assoc nabs the next record off of the data and returns it. Here we store it into a $record variable. The while loop runs until the stuff inside the condition evaluates to false, which will happen when mysql_fetch_assoc hits the end of the data and returns NULL. Make sense?

  7. #16
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Quote Originally Posted by Shadowfiend
    Personally, I think things would probably be faster if you did a full-table fetch and then did manipulation in PHP.
    Most definitely. I was just too lazy to actually code it out. :-D (see below)
    Quote Originally Posted by transio
    PPS - the other option, of course would be to query the entire table and order by parent_id, then loop through the resultset multiple times for display purposes... that would probably be quicker than what I posted above, but the code would be a little more complex.

  8. #17
    Senior Member
    Join Date
    Jun 2005
    Location
    Atlanta, GA
    Posts
    4,146
    Member #
    10263
    Liked
    1 times
    Yeah, I'd noticed. I just decided to implement it to show the `good' way :-P

  9. #18
    Senior Member Steax's Avatar
    Join Date
    Dec 2006
    Location
    Bandung, Indonesia
    Posts
    1,207
    Member #
    14572
    Sorry, I ment the table configuration transio mentioned at the start - I've never used foreign keys or anything aside from the basics. I'm starting to get to grips at the moment, I think it's starting to work out. So I should just grab the entire table and arrange it in PHP?

    Now, to aid my stupidity, can someone explain why this:

    PHP Code:
     while ($line mysql_fetch_array($resultMYSQL_ASSOC)) { 
            echo 
    strpad(""$level"-") . $line["title"] . "<br />"
            
    displayPages($line["id"], $level+1); 
        } 
    Works? I've never seen a while loop been used in this way, nor have I ever seen the method of obtaining variables. I'm using the rather, err, legacy method of a for loop and mysql_result...

    Thanks so much for the help!
    Note on code: If I give code, please note that it is simply sample code to demonstrate an effect. It is not meant to be used as-is; that is the programmer's job. I am not responsible to give you support or be held liable for anything that happens when using my code.

  10. #19
    Senior Member
    Join Date
    Jun 2005
    Location
    Atlanta, GA
    Posts
    4,146
    Member #
    10263
    Liked
    1 times
    I explained the while loop, too :-)

    SQL:
    Code:
        PRIMARY KEY (id), -- basically the identifying key on this table
        UNIQUE (name), -- stays unique, inserts fail if there's a duplicate
        FOREIGN KEY (parent_id) REFERENCES pages(id) ON UPDATE CASCADE ON DELETE CASCADE, -- parent_id refers to an id
            -- in this table, inserts and updates will fail if parent_id is set to something nonexistent in this table,
            -- when you change the id of someting it will go through and update the parent_id references,
            -- when you delete something, it will go through and delete all items referencing it.
        FOREIGN KEY (page_type_id) REFERENCES page_types(id) ON UPDATE CASCADE ON DELETE SET NULL -- page_type_id refers to an id in the page_types table,
           -- inserts and updates will fail if page_type_id is set to something nonexistent in that table,
            -- when you change an id in page_types it will go through and update the page_type_id references,
            -- when you delete something, it will go through and make page_type_id null for all items referencing it.

  11. #20
    Senior Member Steax's Avatar
    Join Date
    Dec 2006
    Location
    Bandung, Indonesia
    Posts
    1,207
    Member #
    14572
    Thanks a ton, shadowfiend!

    My problem with the while loop is that I don't get the syntax. Isn't a while loop's condition supposed to be "the condition in which the loop should be run"? I don't see any incrementing happening...

    And out of curiosity, would the PHP manipulation still work with a very simple table (just some basic rows and fields, no foreign key stuff because I'm working with an existing table here)?
    Note on code: If I give code, please note that it is simply sample code to demonstrate an effect. It is not meant to be used as-is; that is the programmer's job. I am not responsible to give you support or be held liable for anything that happens when using my code.


Page 2 of 3 FirstFirst 1 2 3 LastLast

Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 07:14 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com