Newbie MySQL help ....

Barand · July 12, 2021

There is an alternative approach given the type of output you require this time. This time the query will return a single row for each member which contains a list of the postcodes covered by each. The processing then becomes a lot more simple.

<?php 
require '../db_inc.php';
$db = myConnect('josen');

$res = $db->query("SELECT concat(m.first_name, ' ', m.last_name) as name
                         , m.email
                         , m.phone
                         , c.value as phone2
                         , GROUP_CONCAT(DISTINCT  t.region_id, ' ', t.postcode
                                         ORDER BY region_id, postcode
                                         SEPARATOR ', '
                                         ) as postcodes
                    FROM hfwji_swpm_members_tbl m 
                         JOIN hfwji_members_towns mt USING (member_id)
                         JOIN hfwji_towns t ON t.id = mt.town_id
                         LEFT JOIN hfwji_swpm_form_builder_custom c
                                        ON m.member_id = c.user_id
                                        AND c.field_id = 33
                    GROUP BY m.member_id
                    ORDER BY m.last_name;
                    ");
//
//  PROCESS THE QUERY RESULTS
//
$prev_id = ''; $prev_name = ''; 
$output = '';

foreach ($res as $r) { 
   
    $output .= <<<MEMBER
          <div class="w3-col m4">
          <div class="w3-card-4 w3-light-gray w3-margin w3-padding member">
               <i class="fas fa-user"></i><b>{$r['name']}</b><br>
               <i class="fas fa-envelope"></i>{$r['email']}<br>
               <i class="fas fa-phone"></i>{$r['phone']}&emsp;{$r['phone2']}
               
               <div class="w3-panel w3-padding w3-blue pcodes">
                   {$r['postcodes']}
               </div>
          </div>
          </div>
MEMBER;
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="generator" content="PhpED 19.5 (Build 19515, 64bit)">
<title>Example</title>
<meta name="author" content="Barand">
<meta name="creation-date" content="07/12/2021">
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
<link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.15.1/css/all.css">
<style type='text/css'>
    .member {
        line-height: 20px;
    }
    i {
        color: #2DABE1;
        margin-right: 16px;
    }
    .pcodes {
        font-size: 12pt;
        font-weight: 600;
    }
</style>
</head>
<body>
<div class="w3-row">
    <?=$output?>
</div>
</body>
</html>

image.png.c015023bf9fe2ac1dc57305e980d83c6.png

Oran223 · July 12, 2021

Superb - many thanks once again! I guessed that there may have been a better way ...... still have much to learn!

I learn so much more deconstructing than reading - so the above gives me a lot to play with.

Have just sent you some money to buy yourself a few tipples! 😉

Barand · July 12, 2021

Just now, Oran223 said:

Have just sent you some money to buy yourself a few tipples!

Thanks. I was just scratching my head wondering who that came from.

Oran223 · July 12, 2021

Hah! Sorry it is not much but this is a personal project I am helping some friends on which is now getting in the way fo the day job so your assistance has been very much appreciated.

Most of the coding is logical and I get/understand .... but there are some elements that I cannot even find it books such as '$output .= <<<MEMBER' ..... why the period and why the <<<MEMBER bit too! 😉 (not asking for a response - just venting at my inabilities!)

Barand · July 12, 2021

37 minutes ago, Oran223 said:

Hi Barand ...

Many thanks - below is my go at interpretation of that although I cannot get that working??

prev_town = ""
foreach ($res as $r) {    
    if ($r['town'] != $prev_town) { 
        if ($prev_town != "") {           
            echo '">';
        }
        echo '<div class="col-xs-12 col-md-3';         
        echo ' ' . $r['id'] . ' ' . $r['postcode'] . '';
        $prev_town = $town;
        }
        echo "<h3>" . $r['name'] . '</h3>';  
        echo "<p>" . $r['email'] . '<br/>'; 
        echo "" . $r['phone'] . '</p>'; 
    }
echo '</div>'

You got the last line correct to close the final div group so why did you think echo '">' would do it earlier on?

Barand · July 12, 2021

5 minutes ago, Oran223 said:

Most of the coding is logical and I get/understand .... but there are some elements that I cannot even find it books such as '$output .= <<<MEMBER' ..... why the period and why the <<<MEMBER bit too!

period is the PHP string concatenation operator,

$a = 'oran';
$b = '223'
echo $a.$b;    // oran223

.= is a shorcut...

$name = $a;
$name = $name . $b;  // oran223

can also be written as

$name = $a
$name .= $b;

Just as with numeric operators

$x = 50;
$x += 20;
$x += 10;
echo $x;    // 80

As for the <<<MEMBER, read up on heredoc syntax

Oran223 · July 12, 2021

11 minutes ago, Barand said:

You got the last line correct to close the final div group so why did you think echo '">' would do it earlier on?

I was following your guidance of - close previous div ?? As needed to close the div once all the postcodes were in.

Oran223 · July 12, 2021

Last Question - Promise 😉

Is there a way to regulate the t.region_id to not duplicate - currently I get 3 GU 3 RH 4 SW 5 CR 5 KT 5 SM 5 TW - but ideally don't need the duplicated 3's & 5's? I have tried looking it up but cannot find anything easily.

, GROUP_CONCAT(DISTINCT t.region_id, ' ', t.postcode
ORDER BY region_id, postcode
SEPARATOR ' '
) as postcodes

Thanks in advance.

Barand · July 12, 2021

2 hours ago, Oran223 said:

Is there a way to regulate the t.region_id to not duplicate

Yes.

How you do it depends on how you want to handle those members who cover postcodes in more than one region (if there are any)

Like this

or show Peter like this

image.png.bdca72bb013d49a5ce2788bb0c2d1e02.png

Oran223 · July 12, 2021

Postcodes will only ever fall in one regions - so the former.

Barand · July 12, 2021

12 minutes ago, Oran223 said:

Postcodes will only ever fall in one regions

That wasn't the issue. The postcodes in my data all have have only one region. The point is that Peter has several postcodes but not all in the same region.

Barand · July 12, 2021

This is the code for the first

<?php 
require '../db_inc.php';
$db = myConnect('josen');

$res = $db->query("SELECT concat(m.first_name, ' ', m.last_name) as name
                         , m.email
                         , m.phone
                         , c.value as phone2
                         , t.region_id
                         , GROUP_CONCAT(DISTINCT t.postcode
                                         ORDER BY region_id, postcode
                                         SEPARATOR ', '
                                         ) as postcodes
                    FROM hfwji_swpm_members_tbl m 
                         JOIN hfwji_members_towns mt USING (member_id)
                         JOIN hfwji_towns t ON t.id = mt.town_id
                         LEFT JOIN hfwji_swpm_form_builder_custom c
                                        ON m.member_id = c.user_id
                                        AND c.field_id = 33
                    GROUP BY region_id, m.member_id
                    ORDER BY region_id, m.last_name;
                    ");
//
//  PROCESS THE QUERY RESULTS
//
$prev_id = ''; $prev_name = ''; 
$output = '';

$prev = '';

foreach ($res as $r) { 
    if ($r['region_id'] != $prev) {
        if ($prev != '') {
            $output .= "</div></div>\n";
        }
        $prev = $r['region_id'];
        $output .= <<<HEAD
            <div class='w3-container w3-padding w3-margin w3-border'>
                <div class="w3-panel w3-blue-gray">
                    <h3>Region {$r['region_id']}</h3>
                </div>
                <div class="w3-row">
HEAD;
    }
    $output .= <<<MEMBER
          <div class="w3-col m4">
          <div class="w3-card-4 w3-light-gray w3-margin w3-padding member">
               <i class="fas fa-user"></i><b>{$r['name']}</b><br>
               
               <i class="fas fa-envelope"></i>{$r['email']}<br>
               <i class="fas fa-phone"></i>{$r['phone']}&emsp;{$r['phone2']}
               
               <div class="w3-panel w3-padding w3-blue pcodes">
                    {$r['postcodes']}
               </div>
          </div>
          </div>\n
MEMBER;
}
$output .= "</div>\n";
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="generator" content="PhpED 19.5 (Build 19515, 64bit)">
<title>Example</title>
<meta name="author" content="Barand">
<meta name="creation-date" content="07/12/2021">
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
<link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.15.1/css/all.css">
<style type='text/css'>
    .member {
        line-height: 20px;
    }
    i {
        color: #2DABE1;
        margin-right: 16px;
    }
    .pcodes {
        font-size: 12pt;
        font-weight: 600;
    }
</style>
</head>
<body>

    <?=$output?>

</body>
</html>

Oran223 · July 12, 2021

Oh sorry - less haste more speed ... was running out the door when I read it.

Don't worry - you have helped me out more than enough so far I will see if I can work it out for myself from this and the last.

Sign In

Newbie MySQL help ....

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information