PHP / MySQL issue

maZtah · 10-18-2007, 05:37 PM

Here's another - in my opinion - interesting issue.

I have the following table: 'Contracts', with a field 'Contract' (varchar(20)).

I'm trying to put all Contracts in groups, one group per different Contract, like this:

Id Contract1
1 SampleContract
4 SampleContract
5 SampleContract

Id Contract2
2 SampleContract2
3 SampleContract2

How do I achieve this? (Per 'Contract-group' it has to output a new <table>)

Thanks in advance!

bluesaga · 10-24-2007, 09:52 AM

You are doing this in an un-normalised approach:

Instead of creating a new table for each group, add an extra column to the table like this:
id, contract, group

Then in your mysql queries whenever you want to define a specific group:
SELECT * FROM contracts WHERE group=1

That would pull all contacts from group 1.

You can do an auto table if you want but this is far better, to do an auto table you would do a php loop with a mysql insert query the query would look somewhat like:

Code:

INSERT INTO contract1 (id, contract) VALUES (SELECT id, contract FROM contract WHERE group = 1)

maZtah · 10-24-2007, 10:27 AM

I'm sorry for the bad explanation.

What I'm trying to achieve is to output it per group. So I have to ORDER it BY the field 'Contract'. But then, per contract I want to output a new <table> with the rows of the same 'Contract'.

Let me try to clearify.

This is my database:

[Id] [Contract] [OtherFields]
[ 1] [Head] [......]
[ 2] [Platina] [......]
[ 3] [Platina] [......]
[ 4] [Bronze] [......]
[ 5] [Bronze] [......]
[ 6] [Bronze] [......]

Then I want to output it as follows (html):

<table>
<tr>
<th>Contract</th>
<th>OtherFields......</th>
</tr><tr>
<td>Head</td>
<td>OtherFields.......</td>
</tr>
</table>

<table>
<tr>
<th>Contract</th>
<th>OtherFields......</th>
</tr><tr>
<td>Platina</td>
<td>OtherFields.......</td>
</tr><tr>
<td>Platina</td>
<td>OtherFields.......</td>
</tr>
</table>

<table>
<tr>
<th>Contract</th>
<th>OtherFields......</th>
</tr><tr>
<td>Bronze</td>
<td>OtherFields.......</td>
</tr><tr>
<td>Bronze</td>
<td>OtherFields.......</td>
</tr><tr>
<td>Bronze</td>
<td>OtherFields.......</td>
</tr>
</table>

I hope it's somewhat more clearyfied now.
Thanks.

bluesaga · 10-24-2007, 12:08 PM

OHH now i see what you mean :) html <table> not a mysql one ;)

Ok basically something like:

PHP Code:


			
$pQuery = mysql_query("SELECT * FROM contracts ORDER BY contract ASC");

while($aRow = mysql_fetch_array($pQUERY, MYSQL_ASSOC))
{
   if($aRow['contract'] != $szTempContract)
   {
      //Start of script? If so no need to close a table that isnt open!
      if($szTempContract != "")
          $szEcho .= "</table>";

      //make new table
      $szEcho .= "<table>";
      $szEcho .= "<tr><th>Contract</th>";
      $szEcho .= "<th>Other Fields....</th></tr>";

      $szTempContract = $aRow['contract'];
   }

   $szEcho .= "<tr><th>".$aRow['contract']."</th>";
   $szEcho .= "<th>".$aRow['field1']."</th></tr>";
}

//Won't of closed the table, so close it
$szEcho .= "</table>";

//Output results
echo $szEcho;

maZtah · 10-24-2007, 03:38 PM

Thanks alot!

bluesaga · 10-24-2007, 04:00 PM

No prob, lemme know if you need any more help regarding this.