PHP MySQL multiple queries into one

Question

I'm trying to find a way to display my entity relations in a better way. My DB consists of 3 tables: members, fees and fees_has_members.

One member can have multiple fees and vice versa. The problem is when I want to display all members and their corresponding fees, my while loop runs through all entities and displays all information every time it loops.

The loop displays it like this:

Member A
Fee B
200 $

Member A
Fee C
230 $

Member A
Fee B
200 $

I want to display the Member once and all the related fees.

The code:

 <?php 
mysql_connect("#######", "########", "######") or die(mysql_error());
mysql_select_db("#####") or die(mysql_error());
?>

<h3>Members:</h3>
<?php
 $result = mysql_query("SELECT boder_boder_id, medlemmer_medlemmer_id, medlemmer_navn, medlemmer_id, boder_navn, boder_id, boder_pris FROM boder_has_medlemmer, medlemmer, boder WHERE boder_boder_id = boder_id AND medlemmer_medlemmer_id = medlemmer_id");
 ?>

 <?php
 if (mysql_num_rows($result) == 0)
 echo "Query returned 0 rows";
 ?>

 <?php
 $samlet = 0;
 while($row = mysql_fetch_array($result))
 { ?>

 <p>
 <?php
  echo $row[medlemmer_navn].'<br>'; 
  echo $row[boder_navn].'<br>';
  echo $row[boder_pris].' kr.';
  $samlet += $row[boder_pris];
 ?>
 </p>

I know i could change the MySQL query to include the specific member name, but then I would have to run 30 queries to display all members and their fees.

I hope I made myself clear.

Any suggestions?

PatrickB · Accepted Answer · 2014-09-10 09:30:06Z

Your idea with a query per member is silly :D

Try this.

<?php
mysql_connect('#######', '########', '#######') or die(mysql_error());
mysql_select_db('#####') or die(mysql_error());

echo '<h3>Members:</h3>';

$result = mysql_query('SELECT `boder_boder_id`, `medlemmer_medlemmer_id`, `medlemmer_navn`, `medlemmer_id`, `boder_navn`, `boder_id`, `boder_pris` FROM `boder_has_medlemmer`, `medlemmer`, `boder` WHERE `boder_boder_id` = `boder_id` AND `medlemmer_medlemmer_id` = `medlemmer_id`;');

if (!mysql_num_rows($result))
{
    echo 'Query returned 0 rows';
}

$samlet = 0;
$medlemmers = array();
while($row = mysql_fetch_array($result))
{
    $medlemmers[$row['medlemmer_navn']][] = $row;
}

foreach($medlemmers as $navn => $medlemmer)
{
    echo '<p>';
    echo $navn . '<br />';
    foreach($medlemmer as $row)
    {
        echo $row['boder_navn'] . '<br />';
        echo $row['boder_pris'] . ' kr.';
        $samlet += $row['boder_pris'];
    }
    echo '</p>';
}

It's not tested but should go in right direction ^^

Yeah I know the idea is silly, as I pointed out :) I will test your suggestion right away.

Kickstart · Accepted Answer · 2014-09-10 14:16:16Z

A slightly different alternative using an object. This would save memory compared to reading all the data into an array.

Each time the name changes the object is unset (triggering the __DESTRUCT function to output the details) and a new object created

 <?php 
mysql_connect("#######", "########", "######") or die(mysql_error());
mysql_select_db("#####") or die(mysql_error());

echo "<h3>Members:</h3>";

$result = mysql_query("SELECT boder_boder_id, medlemmer_medlemmer_id, medlemmer_navn, medlemmer_id, boder_navn, boder_id, boder_pris 
                        FROM boder_has_medlemmer,
                            medlemmer,
                            boder 
                        WHERE boder_boder_id = boder_id 
                        AND medlemmer_medlemmer_id = medlemmer_id");

if (mysql_num_rows($result) == 0)
{
    echo "Query returned 0 rows";
}
else
{
    $prev_medlemmer_navn = '';
    while($row = mysql_fetch_array($result))
    {
        if ($prev_medlemmer_navn != $row['medlemmer_navn'])
        {
            unset($member_output);
            $member_output = new member_output($row['medlemmer_navn']);
            $prev_medlemmer_navn = $row['medlemmer_navn'];
        }
        $member_output->add_row($row['boder_navn'], $row['boder_pris'])
    }
    unset($member_output);
}

class member_output
{
    private $medlemmer_navn = '';
    private $details = array();
    private $samlet = 0;

    public function __CONSTRUCT($medlemmer_navn)
    {
        $this->medlemmer_navn = $medlemmer_navn;
    }

    public function __DESTRUCT()
    {
        echo "<p>";
        echo $this->medlemmer_navn.'<br />'; 
        foreach ($this->details as $detail)
        {
            echo $detail['boder_navn'].'<br />';
            echo $detail['boder_pris'].' kr.<br />';
        }
        echo $this->samlet.'<br />';
        echo "</p>";
    }

    public function add_row($boder_navn, $boder_pris)
    {
        $this->details[] = array('boder_navn'=>$boder_navn, 'boder_pris'=>$boder_pris);
        $this->samlet += $boder_pris;
    }
}

 ?>

Or you could push much of the work onto MySQL, grouping the details together so one row per name, then exploding out the rest of the fields.

 <?php 
mysql_connect("#######", "########", "######") or die(mysql_error());
mysql_select_db("#####") or die(mysql_error());

echo "<h3>Members:</h3>";

$result = mysql_query("SELECT medlemmer_navn, medlemmer_id, GROUP_CONCAT(CONCAT_WS('~~', boder_navn, boder_pris) SEPARATOR '##') AS details, SUM(boder_pris) AS samlet
                        FROM boder_has_medlemmer,
                            medlemmer,
                            boder 
                        WHERE boder_boder_id = boder_id 
                        AND medlemmer_medlemmer_id = medlemmer_id
                        GROUP BY medlemmer_navn, medlemmer_id");

if (mysql_num_rows($result) == 0)
{
    echo "Query returned 0 rows";
}
else
{
    while($row = mysql_fetch_array($result))
    {
        echo "<p>";
        echo $row['medlemmer_navn'].'<br />'; 
        $details = explode('##', $row['details']);
        foreach ($details as $detail)
        {
            list($boder_navn, $boder_pris) = explode('~~', $detail);
            echo $boder_navn.'<br />';
            echo $boder_pris.' kr.<br />';
        }
        echo $row['samlet'].'<br />';
        echo "</p>";        
    }
}

 ?>

Or a little dirty, building up the html within the sql.

 <?php 
mysql_connect("#######", "########", "######") or die(mysql_error());
mysql_select_db("#####") or die(mysql_error());

echo "<h3>Members:</h3>";

$result = mysql_query("SELECT medlemmer_navn, medlemmer_id, GROUP_CONCAT(CONCAT(boder_navn, '<br />', boder_pris, 'kr.', '<br />') ) AS details, SUM(boder_pris) AS samlet
                        FROM boder_has_medlemmer,
                            medlemmer,
                            boder 
                        WHERE boder_boder_id = boder_id 
                        AND medlemmer_medlemmer_id = medlemmer_id
                        GROUP BY medlemmer_navn, medlemmer_id");

if (mysql_num_rows($result) == 0)
{
    echo "Query returned 0 rows";
}
else
{
    while($row = mysql_fetch_array($result))
    {
        echo "<p>";
        echo $row['medlemmer_navn'].'<br />'; 
        echo $row['details']; 
        echo $row['samlet'].'<br />';
        echo "</p>";        
    }
}

 ?>

Collectives™ on Stack Overflow

PHP MySQL multiple queries into one

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related