Making some code more compact

arbitter · November 7, 2010

SO I have this database, 'members', containg all the members. This database has 14 columns.

In one column I have the group of which the people are leaders. For each group I want to display all the people in it with some data. Currently, it is done like this: (the database is 'leden', in dutch)

echo'<div class=\'inhoud\'>';
      mysql_select_db('database')or die(mysql_error());
      $result = mysql_query("SELECT * FROM leden WHERE groep='minis' AND type='leiding' ORDER BY voornaam ASC")or die(mysql_error());
      echo "<font color='purple'><span style='font-size:large;'><br />Mini's:</span><br /><table>";
      while($row = mysql_fetch_array($result, MYSQL_ASSOC))
      {
        echo '<tr valign=\'middle\'><td height=\'20px\' width=\'30px\'> </td><td width=\'200px\'>' . $row['voornaam'] . ' ' . $row['achternaam'] . '</td><td>' . $row['telefoonnummer'] . '</td><td>' . $row['email'] . '</td></tr>';
      }
      echo'</table></font>';
      $result = mysql_query("SELECT * FROM leden WHERE groep='speelclubs' AND type='leiding' ORDER BY voornaam ASC")or die(mysql_error());
      echo "<font color='#ffcc33'><span style='font-size:large;'><br />Speelclubs:</span><br /><table>";
      while($row = mysql_fetch_array($result, MYSQL_ASSOC))
      {
        echo '<tr valign=\'middle\'><td height=\'20px\' width=\'30px\'> </td><td width=\'200px\'>' . $row['voornaam'] . ' ' . $row['achternaam'] . '</td><td>' . $row['telefoonnummer'] . '</td></tr>';
      }
      echo'</table></font>';
      $result = mysql_query("SELECT * FROM leden WHERE groep='rakwis' AND type='leiding' ORDER BY voornaam ASC")or die(mysql_error());
      echo "<font color='green'><span style='font-size:large;'><br />Rakwi's:</span><br /><table>";
      while($row = mysql_fetch_array($result, MYSQL_ASSOC))
      {
        echo '<tr valign=\'middle\'><td height=\'20px\' width=\'30px\'> </td><td width=\'200px\'>' . $row['voornaam'] . ' ' . $row['achternaam'] . '</td><td>' . $row['telefoonnummer'] . '</td></tr>';
      }
      echo'</table></font>';
      $result = mysql_query("SELECT * FROM leden WHERE groep='titos' AND type='leiding' ORDER BY voornaam ASC")or die(mysql_error());
      echo "<font color='red'><span style='font-size:large;'><br />Tito's:</span><br /><table>";
      while($row = mysql_fetch_array($result, MYSQL_ASSOC))
      {
        echo '<tr valign=\'middle\'><td height=\'20px\' width=\'30px\'> </td><td width=\'200px\'>' . $row['voornaam'] . ' ' . $row['achternaam'] . '</td><td>' . $row['telefoonnummer'] . '</td></tr>';
      }
      echo'</table></font>';
      $result = mysql_query("SELECT * FROM leden WHERE groep='ketis' AND type='leiding' ORDER BY voornaam ASC")or die(mysql_error());
      echo "<font color='blue'><span style='font-size:large;'><br />Keti's:</span><br /><table>";
      while($row = mysql_fetch_array($result, MYSQL_ASSOC))
      {
        echo '<tr valign=\'middle\'><td height=\'20px\' width=\'30px\'> </td><td width=\'200px\'>' . $row['voornaam'] . ' ' . $row['achternaam'] . '</td><td>' . $row['telefoonnummer'] . '</td></tr>';
      }
      echo'</table></font>';
      echo '</div>';

So as you can see I have every group done in a seperate query.

How can I do this in 2 querys within a while?

So foreach group it has to write the group name, and then a table with all the people that lead that group and some other data. Also, each group needs it seperate color.

mikosiko · November 7, 2010

for you to play with and have a goal

just 1 SELECT and 1 WHILE should do what you have posted ...

hint: you can control IN the WHILE IF the group changed, and proceed accordingly

arbitter · November 8, 2010

Oh boy, a challenge!

1 Select and 1 While... So the select must be

mysql_query("SELECT * FROM leden WHERE type='leiding' ORDER BY voornaam ASC")

Then i guess the

while($row = mysql_fetch_array($result,MYSQL_ASSOC))

Did two miserably failed attempts:

1)

$result = mysql_query("SELECT * FROM leden WHERE type='leiding' ORDER BY voornaam ASC")or die(mysql_error());
		$row = mysql_fetch_array($result,MYSQL_ASSOC);
		foreach($row['groep'] as $groep)
		{
			echo "<span style='font-size:large;'>$groep</span><table>";
			while($row2 = mysql_fetch_array($result,MYSQL_ASSOC))
			{
				echo '<tr valign=\'middle\'><td height=\'20px\' width=\'30px\'> </td><td width=\'200px\'>' . $row2['voornaam'] . ' ' . $row2['achternaam'] . '</td><td>' . $row2['telefoonnummer'] . '</td><td>' . $row2['email'] . '</td></tr>';
			}
			echo "</table>";
		}

2)

$result = mysql_query("SELECT * FROM leden WHERE type='leiding' ORDER BY groep ASC")or die(mysql_error());
		while($row = mysql_fetch_array($result,MYSQL_ASSOC))
		{
			if($row['groep'] == 'minis'){echo "<font color='purple'><span style='font-size:large;'><br />Mini's:</span><br /><table>";}
			if($row['groep'] == 'speelclubs'){echo "<font color='#ffcc33'><span style='font-size:large;'><br />Speelclub's</span><br /><table>";}
			if($row['groep'] == 'rakwis'){echo "<font color='green'><span style='font-size:large;'><br />Rakwi's:</span><br /><table>";}
			if($row['groep'] == 'titos'){echo "<font color='red'><span style='font-size:large;'><br />Tito's:</span><br /><table>";}
			if($row['groep'] == 'ketis'){echo "<font color='blue'><span style='font-size:large;'><br />Keti's:</span><br /><table>";}
			echo '<tr valign=\'middle\'><td height=\'20px\' width=\'30px\'> </td><td width=\'200px\'>' . $row['voornaam'] . ' ' . $row['achternaam'] . '</td><td>' . $row['telefoonnummer'] . '</td><td>' . $row['email'] . '</td></tr>';
		}
		echo "</table>";			
		echo "<hr /><hr />";

I am really unexperienced with loops and stuff, as you can see.

mikosiko · November 8, 2010

quickly written ... no tested.... but you can test it ... the color change is for you to figure out how.... HINT: a possible solution ... use an array

      echo'<div class="inhoud">';

      mysql_select_db($dbname)or die(mysql_error());
            
      $query = "SELECT * FROM leden 
                 WHERE type='leiding'
                 ORDER BY groep, voornaam ASC";

      $oldgroep = "";
                       
      $result = mysql_query($query) or die(mysql_error());
      
      while ($row = mysql_fetch_assoc($result)) {
         // Here we validate if the groep changed
         if ($row['groep'] != $oldgroep) {
           if ( $oldgroep != "") {
              // Close Table... except first time
              echo'</table></font>';
           }

           // Print the Groep header everytime that it is different
           echo '<font color="purple"><span style="font-size:large;"><br />'.$row['groep'].'</span><br /><table>';
           
           $oldgroep = $row['groep'];
         }
         
         // Print the table rows
         echo '<tr valign="middle"><td height="20px" width="30px"> </td><td width="200px">' . $row['voornaam'] . ' ' . $row['achternaam'] . '</td><td>' . $row['telefoonnummer'] . '</td><td>' . $row['email'] . '</td></tr>';
      
      } 
      
      // Close Table only if it was created
      if ($oldgroep != "") {
         echo'</table></font>';
      }
      echo '</div>';

arbitter · November 8, 2010

Actually I did test them, and i know that none of them gave any good results; the first one didn't even give any results!

You're code is genious by the way; I would've never come up with that! As far as the colors go; that was no difficulty. Only problem is some regex; eg the group name in the db is "minis", but on the site it must be "Mini's". The capital letter is no problem, using

ucwords()

. And then I need something to get a ' between the last and the before-last letter, though this mustn't be executed on one group but I can filter that out with an

if($row['groep'] != 'VB'){
     //The thing that needs to be done to get eg Mini's ;
}

As far as the current code goes; it's what you wrote except for adding the color.

$query = "SELECT * FROM leden 
                 WHERE type='leiding'
                 ORDER BY groep, voornaam ASC";
      $oldgroep = "";  
      $result = mysql_query($query) or die(mysql_error());
      
      while ($row = mysql_fetch_assoc($result)) {
         // Here we validate if the groep changed
         if ($row['groep'] != $oldgroep) 
	 {
           if ( $oldgroep != "") 
	   {
              // Close Table... except first time
              echo'</table></font>';
           }
           // Print the Groep header everytime that it is different
	   if($row['groep'] == 'minis'){$color='purple';}elseif($row['groep'] == 'rakwis'){$color='green';}elseif($row['groep'] == 'titos'){$color='red';}elseif($row['groep'] == 'speelclubs'){$color='#ffcc33';}elseif($row['groep'] == 'ketis'){$color='blue';}else{$color='black';}
           echo "<font color='$color'><span style='font-size:large;'><br />".ucfirst($row['groep'])."</span><br /><table>";
           
           $oldgroep = $row['groep'];
         }
         
         // Print the table rows
         echo "<tr valign='middle'><td height='20px' width='30px'> </td><td width='200px'>" . $row['voornaam'] . ' ' . $row['achternaam'] . '</td><td>' . $row['telefoonnummer'] . '</td><td>' . $row['email'] . '</td></tr>';
      
      } 
      
      // Close Table only if it was created
      if ($oldgroep != "") {
         echo'</table></font>';
      }
      echo '</div>';

Another question; I need the groups in another order. So say I add a table in my database with 1-6, called 'order'. What should my query be? Now it is

$query = "SELECT * FROM leden 
                 WHERE type='leiding'
                 ORDER BY groep, voornaam ASC";

So it first orders all the results by group, and within that group it orders them by name?

So presumably then I'd only need

ORDER BY order, name ASC

, am I right?

By the way, thanks for all the help and willingness to learn me something!

mikosiko · November 8, 2010

Actually I did test them, and i know that none of them gave any good results; the first one didn't even give any results!

my line : "quickly written ... no tested.... but you can test it"

was in reference to my code not yours

Only problem is some regex; eg the group name in the db is "minis", but on the site it must be "Mini's". The capital letter is no problem, using
ucwords()
. And then I need something to get a ' between the last and the before-last letter, though this mustn't be executed on one group but I can filter that out with an
if($row['groep'] != 'VB'){
     //The thing that needs to be done to get eg Mini's ;
}

too much of a hassle... an easy alternative is use an array... per example:

  $names = array(
                  'minis' => 'Mini\'s',
                  'rakwi' => 'Rawki\'s',
                  'tito' => 'Tito\'s',
                  'ketis' => 'Keti\'s'
                );

and then use the array in this line:

           echo "<font color='$color'><span style='font-size:large;'><br />".$names[$row['groep']]."</span><br /><table>";

Another question; I need the groups in another order. So say I add a table in my database with 1-6, called 'order'.

Humm... maybe you have some semantic confusion here.. (if I understood correctly your goal)... you mean that you need a new field called "order" in your table leaden...

then yes... but don't call that field "order" (order is a reserved mysql word)

$query = "SELECT * FROM leden
WHERE type='leiding'

ORDER BY groep, voornaam ASC";

So it first orders all the results by group, and within that group it orders them by name?

yes

So presumably then I'd only need
ORDER BY order, name ASC
, am I right?

yes... considering that you need to assign the same order number to an specific group.

arbitter · November 9, 2010

That array method thing you used there is very handy! But it does not maintain values that are not changed, does it? All of my 'groep' veriables need to be in that array or they'll be erased from the array I presume.

Now everything is perfect, except for the order-problem.

Implementing a new order requires me to change a lot of other codes, so I'm guessing there must be another way. A way to assign the 'minis' a value of 1, 'speelcubs' 2, and so on.

Though, giving it another thought, it probably won't be possible since you can't change the order of the groups inside the while(). So the only method is to insert another field?

I guess I can get that fixed without any issues, considering I don't have to touch any of the code inside the while(), so I can't mess things up!

Sir mikosiko, you have been a great help and you've given me the knowledge of new methods that I can now (hopefully) integrate in my future-php-scripts! Thanks a bunch! :-*

stephenworks · November 11, 2010

Just as a quick note - you can add a column called `order`, just make sure you encase it in backticks.

For example:

SELECT `id` FROM `users` ORDER BY `order` ASC

arbitter · November 11, 2010

I used another name for it just in case, works like a charm now

Though I was wondering; is there a way to order something by letter/number but tell it to begin at number 9 instead of 1?

Sign In

Making some code more compact

Recommended Posts

arbitter

Link to comment

Share on other sites

mikosiko

Link to comment

Share on other sites

arbitter

Link to comment

Share on other sites

mikosiko

Link to comment

Share on other sites

arbitter

Link to comment

Share on other sites

mikosiko

Link to comment

Share on other sites

arbitter

Link to comment

Share on other sites

stephenworks

Link to comment

Share on other sites

arbitter

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information