Will changing Column Order break my Queries/Code?

[DoubleDee]

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

[guelphdad]

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.

[r937]

... 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

[DoubleDee]

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...

    // ****************
    // 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

[r937]

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

[DoubleDee]

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?

[guido2004]

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

[cpradio]

What about in a Prepared Statement like this...

    // ****************
    // 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.

[DoubleDee]

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

[DoubleDee]

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