Jump to content

Newbie MySQL help ....


Oran223
 Share

Recommended Posts

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

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

<div class="col-xs-12 col-md-3 2 DL 3 NE 4 BA ">

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

oran_1.thumb.PNG.07b85f5989dc664a38dd97bc9039d61f.PNG

or show Peter like this

image.png.bdca72bb013d49a5ce2788bb0c2d1e02.png

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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>

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.