wastedthelight Posted March 6, 2008 Share Posted March 6, 2008 I use to know how to do this but it's been way to long. I would like to make a new table based on State pulled. Michigan ---------- company 1 company 2 company 2 Ohio ----------- company 1 company 2 company 2 and so forth. any help would be appreciated. Thanks. Link to comment https://forums.phpfreaks.com/topic/94796-phpmysql-new-table-for-every-state-pulled/ Share on other sites More sharing options...
wastedthelight Posted March 6, 2008 Author Share Posted March 6, 2008 oh yeah, that's a new HTML table, not mysql Link to comment https://forums.phpfreaks.com/topic/94796-phpmysql-new-table-for-every-state-pulled/#findComment-485481 Share on other sites More sharing options...
Barand Posted March 6, 2008 Share Posted March 6, 2008 <?php $sql = "SELECT state, company FROM tablename ORDER BY state, company"; $res = mysql_query($sql) or die(mysql_error()); $lastState = ''; while (list($state, $company) = mysql_fetch_row($res)) { if ($lastState != $state) // new state ? { if ($lastState != '') { echo '</table><br/>'; // if there was a previous table, close it } echo '<table>'; // start new table echo '<tr><th>$state</th></tr>'; // output new state header $lastState = $state; // reset last state value } echo '<tr><td>$company</td></tr>'; // output company name } echo '</table><br/>'; // cloase final table ?> Link to comment https://forums.phpfreaks.com/topic/94796-phpmysql-new-table-for-every-state-pulled/#findComment-485511 Share on other sites More sharing options...
wastedthelight Posted March 6, 2008 Author Share Posted March 6, 2008 <?php $sql = "SELECT state, company FROM tablename ORDER BY state, company"; $res = mysql_query($sql) or die(mysql_error()); $lastState = ''; while (list($state, $company) = mysql_fetch_row($res)) { if ($lastState != $state) // new state ? { if ($lastState != '') { echo '</table><br/>'; // if there was a previous table, close it } echo '<table>'; // start new table echo '<tr><th>$state</th></tr>'; // output new state header $lastState = $state; // reset last state value } echo '<tr><td>$company</td></tr>'; // output company name } echo '</table><br/>'; // cloase final table ?> Thanks! Just had to do a little editing because it was actually showing $state instead of the value state is. but yeah, works perfect thanks for your help Barand! Link to comment https://forums.phpfreaks.com/topic/94796-phpmysql-new-table-for-every-state-pulled/#findComment-485545 Share on other sites More sharing options...
wastedthelight Posted March 6, 2008 Author Share Posted March 6, 2008 i spoke to soon. When adding the rest of the fields (address, zip, phone...) it screwed everything up. Even taking when it works to changing Select company FROM to Select * From screws it up. This is what I have. It's not out putting anything except autoID and country (id for state adn country for company). <?php $sql = "SELECT * FROM locations ORDER BY state, company"; $res = mysql_query($sql) or die(mysql_error()); $lastState = ''; while (list($state, $company) = mysql_fetch_row($res)) { if ($lastState != $state) // new state ? { if ($lastState != '') { echo '</table><br/>'; // if there was a previous table, close it } echo '<table width="100%" align="left">'; // start new table echo '<tr bgcolor="#c0c0c0"><td><b>';echo $state; echo '</b></td></tr>'; // output new state header $lastState = $state; // reset last state value } echo ' <tr><td>';echo $company; echo '</td> <td>';echo $address1; echo '</td> <td>';echo $address2; echo '</td> <td>';echo $city; echo '</td> <td>';echo $zip; echo '</td> <td>';echo $phone; echo '</td> <td>';echo $cell; echo '</td> <td>';echo $fax; echo '</td> <td>';echo $website; echo '</td></tr>'; // output company name } echo '</table><br/>'; // cloase final table ?> Link to comment https://forums.phpfreaks.com/topic/94796-phpmysql-new-table-for-every-state-pulled/#findComment-485566 Share on other sites More sharing options...
Barand Posted March 6, 2008 Share Posted March 6, 2008 try <?php $sql = "SELECT state, company, address1, address2, city, zip, phone, cell, fax, website FROM locations ORDER BY state, company"; $res = mysql_query($sql) or die(mysql_error()); $lastState = ''; while (list($state, $company, $address1, $address2, $city, $zip, $phone, $cell, $fax, $website) = mysql_fetch_row($res)) { if ($lastState != $state) // new state ? { if ($lastState != '') { echo '</table><br/>'; // if there was a previous table, close it } echo '<table>'; // start new table echo "<tr><th colspan='9'>$state</th></tr>"; // output new state header $lastState = $state; // reset last state value } echo "<tr><td>$company</td> <td>$address1</td> <td>$address2</td> <td>$city</td> <td>$zip</td> <td>$phone</td> <td>$cell</td> <td>$fax</td> <td>$website</td> </tr>"; // output company data } echo '</table><br/>'; // close final table ?> Link to comment https://forums.phpfreaks.com/topic/94796-phpmysql-new-table-for-every-state-pulled/#findComment-485580 Share on other sites More sharing options...
wastedthelight Posted March 6, 2008 Author Share Posted March 6, 2008 Thanks that worked fully this time. So why can't I use Select * instead of listing all the fields out? Why does it seem to screw up everything? Link to comment https://forums.phpfreaks.com/topic/94796-phpmysql-new-table-for-every-state-pulled/#findComment-485594 Share on other sites More sharing options...
Barand Posted March 7, 2008 Share Posted March 7, 2008 list($state, $company, $address1, $address2, $city, $zip, $phone, $cell, $fax, $website) needs to be in the same order as the columns in the rows returned. SELECT * selects all columns. and for all I knew there could be 100 of them, in any order. Specifying the fields guarantees the content of the row. Unless you really do need ALL columns, don't use *. It obscures what the query is doing and results in redundant data being fetched, which adds to the inefficiency of the query Link to comment https://forums.phpfreaks.com/topic/94796-phpmysql-new-table-for-every-state-pulled/#findComment-485638 Share on other sites More sharing options...
wastedthelight Posted March 7, 2008 Author Share Posted March 7, 2008 Wow, okay didn't know that. Thanks for all your help! list($state, $company, $address1, $address2, $city, $zip, $phone, $cell, $fax, $website) needs to be in the same order as the columns in the rows returned. SELECT * selects all columns. and for all I knew there could be 100 of them, in any order. Specifying the fields guarantees the content of the row. Unless you really do need ALL columns, don't use *. It obscures what the query is doing and results in redundant data being fetched, which adds to the inefficiency of the query Link to comment https://forums.phpfreaks.com/topic/94796-phpmysql-new-table-for-every-state-pulled/#findComment-485911 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.