Jump to content

PHP+MySQL: New Table for every State pulled


wastedthelight

Recommended Posts

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.

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

<?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!

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

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

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

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

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.