Monday, April 30, 2012

MYSQL ordering columns in my query

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.





No comments:

Post a Comment