PHP+MySQL: New Table for every State pulled

[wastedthelight]

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.

[wastedthelight]

oh yeah, that's a new HTML table, not mysql 

[Barand]

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

[wastedthelight]

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

[wastedthelight]

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

[Barand]

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

[wastedthelight]

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?

[Barand]

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

[wastedthelight]

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