MYSQL ordering columns in my query

Question

I have been working on trying to get a bunch of data to work in a table structure displayed neatly using PHP, and I must say I am having a difficult time. I have finally been able to call the columns so that in case I add a field it will always add it in my table, and I hope for this to be very easily managed. However, when I initially set up the table, I put it in a random order. Now when I come use the DESCRIBE table it gives them to me in exact order, and I cannot find a way to organize them better. It would not make sense to say have month/year at the end of the database for my form that I have.

<?php 
require 'connect.php';
?>
<h3>Monthly Finances | <a href="new.php">Add New Month</a> </h3>
<table border="1" bordercolor ="#000000">
<tr>
<?php
$columns = "DESCRIBE  `month` ";
if($query_run_columns = mysql_query($columns)){
    $columnNames = array();
        while($column = mysql_fetch_assoc($query_run_columns)){
        echo '<th>'.$column['Field'].'</th>';
    }
}else{
    echo'sql error';
}

?>


<?php
$gatherdata = "SELECT `month`, `year`,`rent`,`electric`,`cable`,`cellphone`,`renters` FROM `month` ";
if($query_run = mysql_query($gatherdata)){

    while($row = mysql_fetch_assoc($query_run)){
        $rent = $row['rent'];
        $electric = $row['electric'];
        $cable = $row['cable'];
        $cellphone = $row['cellphone'];
        $renters = $row['renters'];
        $month = $row['month'];
        $year = $row['year'];

        echo '<tr>';
        echo '<td align="center">'.$month.'</td>';
        echo '<td algin="center">'.$year.'</td>';
        echo '<td align="center">'.$rent.'</td>';
        echo '<td align="center">'.$electric.'</td>';
        echo '<td align="center">'.$cable.'</td>';
        echo '<td align="center">'.$cellphone.'</td>';
        echo '<td align="center">'.$renters.'</td>';
        echo '</tr>';
    }
}else{
    echo 'query error';
}
?>
<br>View by month
</table>
<form action="index.php" method="GET">
<select name="months" value="all">
<?php
$gathermonths = "SELECT `month`, `year` FROM `month";

if($query_month_selector = mysql_query($gathermonths)){
    while($month_row = mysql_fetch_assoc($query_month_selector)){
        $month = $month_row['month'];
        $year = $month_row['year'];

        echo '<option value="'.$month.' '.$year.'">' .$month.' '.$year.'</option>';
    }
}
echo $_GET['months'];
?>
<input type="submit" value="View Selected Date"></input>
</select>
</form>

My code is very far from complete, or orderly but I have been slacking on this project and I will clean it up more when I have more time. But if you could please give me a hand on an efficient organizational method that would be appreciated.

why do you care what order they're in? Your SQL statements shouldn't depend upon it. — Alnitak
– Alnitak, Commented Apr 28, 2012 at 20:18
Well when I use the describe statement, it orders them automatically, and I use the while loop to put them directly into my table headers. — Joshua Lown
– Joshua Lown, Commented Apr 28, 2012 at 20:39
The mysql extension is outdated and on its way to deprecation. New code should use mysqli or PDO, both of which have important advantages, such as support for prepared statements. — outis
– outis, Commented Apr 28, 2012 at 21:10

Kyle · Accepted Answer · 2012-04-28 20:49:13Z

1

Do your tables contain an index/id?

You can easily order them in ascending/descending by "ORDER BY"

ALTER TABLE `table_name` ORDER BY `id`

Note default ORDER BY is in ascending order. Put DESC at the end if you want descending.

If you want your mysql query to output results in a nice ordered fashion, you can also use ORDER BY in the query:

$gathermonths = "SELECT `month`, `year` FROM `month` ORDER BY `month` DESC";

There is also GROUP BY functions in case you wanted to group your results by month:

$gathermonths = "SELECT `month`, `year` FROM `month` GROUP BY `month`";

answered Apr 28, 2012 at 20:49

Kyle

3,04619 gold badges54 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

bfavaretto · Accepted Answer · 2012-04-28 21:06:14Z

1

Instead of the DESCRIBE query, you could use mysql_fetch_field. Then columns will always be in the same order you have on your SELECT:

<?php
$gatherdata = "SELECT `month`, `year`,`rent`,`electric`,`cable`,`cellphone`,`renters` FROM `month` ";
if($query_run = mysql_query($gatherdata)){

    // Loop the columns to build the table headers
    echo '<table>';
    echo '<tr>';
    while ($i < mysql_num_fields($query_run)) {
        $field = mysql_fetch_field($query_run, $i);
        echo '<th>' . $field->name . '</th>';
        $i++;
    }
    echo '</tr>';

    // Your current data loop
    while($row = mysql_fetch_assoc($query_run)){
        // (...)
    }
    echo '</table>'
}
?>

answered Apr 28, 2012 at 21:06

bfavaretto

72k18 gold badges118 silver badges162 bronze badges

Comments

outis · Accepted Answer · 2012-04-28 21:20:26Z

Since you're already specifying which columns you're fetching, simply output the column headers explicitly rather than querying the database.

Contrariwise, if you want to abstract the HTML table generation code so that it works with arbitrary SQL tables (thus separating data access from display), record the column names as you generate the column headers in an array, then iterate over this array when outputting rows. You can also do away with the $rent = $row['rent']; assignments, as they gain you nothing. Using PDO:

/* data access */
$finances = $db->query('SELECT `month`, `year`,`rent`,`electric`,`cable`,`cellphone`,`renters` FROM `month`');

$columns = array();
for ($i = 0; $i < $finances->columnCount(); ++$i) {
    $meta = $finances->getColumnMeta($i);
    $columns[$meta['name']] = ucwords($meta['name']);
}

/* data display. Note this is completely independent of the type used to store 
   the colum & row data. All that matters are that $columns and $finances are 
   Traversable
 */
?>
<table>
  <thead>
    <tr>
      <?php foreach ($columns as $name => $label): ?>
        <th><?php echo $label ?></th>
      <?php endforeach ?>
    </tr>
  </thead>
  <tbody>
    <?php foreach ($finances as $monthly): ?>
      <tr>
        <?php foreach ($columns as $name => $label): ?>
          <td><?php echo $monthly[$name]; ?></td>
        <?php endforeach ?>
      </tr>
    <?php endforeach; ?>
  </tbody>
</table>

Error handling and abstraction into modules left as an exercise.

Collectives™ on Stack Overflow

MYSQL ordering columns in my query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related