SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,530
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Will changing Column Order break my Queries/Code?

    I have a table with 15-20 columns that is getting a little unwieldy because the columns no longer match what I feel is a "logical" order.

    If I change the order of the columns - either by creating a new table or by using ALTER - will this break any of my existing queries or code?

    That is, does either SQL or PHP care that the column order in my code/queries does not match the physical order of my tables?!

    Thanks,


    Debbie

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    how do you view your table other than with a SELECT clause? Why would it matter the physical order when you can determine what shows up with a SELECT clause?

    I think you worry FAR TOO MUCH about minute things in your database(s), table(s), column name(s) etc. Focus on your work and don't sweat the small stuff.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    ... will this break any of my existing queries or code?
    unless you use (deprecated) ordinal position numbers in your ORDER BY clause, it will have no effect on sql

    if you use arrays in handling your query results in php, with offset positions instead of column names in those arrays, you could mess up big time
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,530
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    unless you use (deprecated) ordinal position numbers in your ORDER BY clause, it will have no effect on sql

    if you use arrays in handling your query results in php, with offset positions instead of column names in those arrays, you could mess up big time
    What about in a Prepared Statement like this...

    PHP Code:
        // ****************
        // Find Articles.    *
        // ****************

        // Build query.
        
    $q1 'SELECT section, heading, image, published_on, summary
                FROM article
                WHERE section=?'
    ;

        
    // Prepare statement.
        
    $stmt1 mysqli_prepare($dbc$q1);

        
    // Bind variable to query.
        
    mysqli_stmt_bind_param($stmt1's'$_GET['section']);

        
    // Execute query.
        
    mysqli_stmt_execute($stmt1);

        
    // Store results.
        
    mysqli_stmt_store_result($stmt1);

        
    // Check # of Records Returned.
        
    if (mysqli_stmt_num_rows($stmt1)>0){
            
    // Articles Found.

            // Bind result-set to variables.
            
    mysqli_stmt_bind_result($stmt1$section$heading$image$publishedOn$summary);

            
    // Fetch below in loop... 
    Thanks,


    Debbie

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i don't do php so i can't say with certainty, but it looks okay, you're referencing the result using the same column names and not positions
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,530
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i don't do php so i can't say with certainty, but it looks okay, you're referencing the result using the same column names and not positions
    Okay, thanks!


    Debbie

    P.S. Is it bad for me wanting to "pretty up" the order of my table columns?

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Is it bad for me wanting to "pretty up" the order of my table columns?
    Bad? Not if you're doing it in your own time. If you were working for a boss, he might not be too pleased.
    Lot of extra, useless work? IMO yes

  8. #8
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    4,811
    Mentioned
    141 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    What about in a Prepared Statement like this...

    PHP Code:
        // ****************
        // Find Articles.    *
        // ****************

        // Build query.
        
    $q1 'SELECT section, heading, image, published_on, summary
                FROM article
                WHERE section=?'
    ;

        
    // Prepare statement.
        
    $stmt1 mysqli_prepare($dbc$q1);

        
    // Bind variable to query.
        
    mysqli_stmt_bind_param($stmt1's'$_GET['section']);

        
    // Execute query.
        
    mysqli_stmt_execute($stmt1);

        
    // Store results.
        
    mysqli_stmt_store_result($stmt1);

        
    // Check # of Records Returned.
        
    if (mysqli_stmt_num_rows($stmt1)>0){
            
    // Articles Found.

            // Bind result-set to variables.
            
    mysqli_stmt_bind_result($stmt1$section$heading$image$publishedOn$summary);

            
    // Fetch below in loop... 
    Thanks,


    Debbie
    Changing the order of the columns in your table will not break this code because you are defining the order of the columns you want in the SELECT statement, which still match your bind_result statement. If you had used SELECT * instead of listing your column names, OR if you change the order of your column names in your SELECT statement, yes your code will break.
    Be sure to congratulate xMog on earning April's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  9. #9
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,530
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Bad? Not if you're doing it in your own time. If you were working for a boss, he might not be too pleased.
    Lot of extra, useless work? IMO yes
    Yep, it's my system.


    Debbie

  10. #10
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,530
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    Changing the order of the columns in your table will not break this code because you are defining the order of the columns you want in the SELECT statement, which still match your bind_result statement.
    Okay.


    If you had used SELECT * instead of listing your column names, OR if you change the order of your column names in your SELECT statement, yes your code will break.
    Okay.

    Thanks,


    Debbie


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
  •