Newbie MySQL help ....

[Oran223]

Hi .... 

Trying to learn but am stuck now .... have the following :

$query = "SELECT * FROM hfwji_towns where region_id = 12";
$result = $mysqli->query($query);
$towns = $result->fetch_all(MYSQLI_ASSOC);

foreach ($towns as $town) {
    $town_id = $town['id'];

    $query = "SELECT * FROM hfwji_members_towns where town_id = $town_id";
    $result = $mysqli->query($query);
    $town_members = $result->fetch_all(MYSQLI_ASSOC);

    if (!empty($town_members)) {
        echo '<h3 style="margin-bottom:0">' . $town['town'] . ' (' . $town['postcode'] . ')</h3>';

        foreach ($town_members as $tm) {
            $member_id = $tm['member_id'];
            $query = "SELECT * FROM hfwji_swpm_members_tbl where member_id = $member_id";
            $result = $mysqli->query($query);
            $member = $result->fetch_assoc();

            if(empty($member)) continue;
            
            $query = "SELECT * FROM hfwji_swpm_form_builder_custom where field_id = 33 AND user_id = $member_id";
            $result = $mysqli->query($query);
            $custom_field = $result->fetch_assoc();
            $second_phone = !empty($custom_field) ? $custom_field['value'] : '';

            echo "<strong>" . $member['first_name'] . " " . $member['last_name'] . '</strong><br>';
            echo "" . $member['email'] . '<br>';
            echo "" . $member['phone'] . '<br><br>';

        } 

        echo '';
    } 
}

This works but I get a list of the towns as follows : 

Bedlington (NE)
name
email address
mobile number

Bishop Auckland (DL)

Blaydon-on-Tyne (NE)
name
email address
mobile number

However, where Bishop Auckland does not have any members I dont want to output the Town name which I think this has something to do with if (!empty($town_members)). How do I output to only echo the H3 (the town and postcode) only when it has $town_members - as not all towns have members in the database??

Appreciate any help I can get.  Thanks in advance.

 

 

[Barand]

1 ) never run queries inside loops like that. Use joins to connect related records from tables.

2) Don't use "SELECT * , list the column names you need. Then people like me (and any one else who needs to maintain the code later) can see what data you are retrieving from each table and help you construct a better query.

Post your table structures.

Edited July 8, 2021 by Barand

[Oran223]

Hi Barand ...

Thanks for the response - seems I have much to learn.  I am a creative designer at heart my small brain is not good when it comes to databases etc.  It seems it may not be as simple a fix as I thought.  

I'll see if I can work out the table structures and post them shortly. Wish me luck! 😉

[Oran223]

Not sure if this helps or hinders ... these are the tables structures.

 

[requinix]

Question: are there going to be people who are "members" of multiple towns? Not the sort of thing I would expect, but then again I can't tell what it means to be a "member" of a town.

[Oran223]

Yes - A member may have multiple towns.  Essentially what I am trying to list is all the towns covered, by whom and listed as one single region_id.  Hope the below helps.

hfwji_members_towns - For each member listed on his profile he selects from a long list of towns that he/she covers.
hfwji_towns - Each town has a postcode, name and a region - ie Andover (SP) - South West - A user can have any number of multiple towns.
hfwji_swpm_form_builder_custom - Part of the membership sign-up form has some custom fields which contain the telephone number.

 

[Oran223]

Have started looking at 'JOINS' as Barand suggests and sort of understand those - except I don't where the user will have a number of towns listed.

[Barand]

DATA

hfwji_towns;                                           hfwji_members_towns;                 hfwji_swpm_form_builder_custom;
+----+-----------------+----------+-----------+        +----+-----------+---------+         +----------+----------+---------+------------+
| id | town            | postcode | region_id |        | id | member_id | town_id |         | value_id | field_id | user_id | value      |
+----+-----------------+----------+-----------+        +----+-----------+---------+         +----------+----------+---------+------------+
|  1 | Bedlington      | NE       |         2 |        |  1 |         1 |       1 |         |        1 |       33 |       2 | 0123456789 |
|  2 | Bishop Aukland  | DL       |         2 |        |  2 |         1 |       3 |         +----------+----------+---------+------------+
|  3 | Blaydon-on-Tyne | NE       |         2 |        |  3 |         2 |       4 |
|  4 | Barnard Castle  | BA       |         2 |        |  4 |         3 |       1 |
+----+-----------------+----------+-----------+        |  6 |         4 |       3 |
                                                       +----+-----------+---------+




hfwji_swpm_members_tbl;
+-----------+-----------+------------+-----------+------------------+------------+
| member_id | user_name | first_name | last_name | email            | phone      |
+-----------+-----------+------------+-----------+------------------+------------+
|         1 | cheggs    | Scott      | Chegg     | cheggs@gmail.com | 134567890  |
|         2 | norderl   | Laura      | Norder    | lauran@gmail.com | 2345678901 |
|         3 | canrit    | Tom        | DiCanari  | tomdc@gmail.com  | 6543219878 |
|         4 | peted     | Peter      | Dowt      | pete@gmail.com   | 9876543210 |
+-----------+-----------+------------+-----------+------------------+------------+

QUERY

SELECT t.town
     , t.postcode
     , concat(m.first_name, ' ', m.last_name) as name
     , m.email
     , m.phone
     , c.value as phone2
FROM hfwji_towns t 
     LEFT JOIN hfwji_members_towns mt ON t.id = mt.town_id
     LEFT JOIN hfwji_swpm_members_tbl m USING (member_id)
     LEFT JOIN hfwji_swpm_form_builder_custom c
                    ON m.member_id = c.user_id
                    AND c.field_id = 33
ORDER BY town, name;

RESULTS

+-----------------+----------+--------------+------------------+------------+------------+
| town            | postcode | name         | email            | phone      | phone2     |
+-----------------+----------+--------------+------------------+------------+------------+
| Barnard Castle  | BA       | Laura Norder | lauran@gmail.com | 2345678901 | 0123456789 |
| Bedlington      | NE       | Scott Chegg  | cheggs@gmail.com | 134567890  | NULL       |
| Bedlington      | NE       | Tom DiCanari | tomdc@gmail.com  | 6543219878 | NULL       |
| Bishop Aukland  | DL       | NULL         | NULL             | NULL       | NULL       |
| Blaydon-on-Tyne | NE       | Peter Dowt   | pete@gmail.com   | 9876543210 | NULL       |
| Blaydon-on-Tyne | NE       | Scott Chegg  | cheggs@gmail.com | 134567890  | NULL       |
+-----------------+----------+--------------+------------------+------------+------------+

To process

prev_town = ""
foreach results
    if town != prev_town 
        echo town heading
        prev_town = town
    end if
    echo member data    
end foreach

 

[Barand]

Oops! Forgot to omit towns with no members

SELECT t.town
     , t.postcode
     , concat(m.first_name, ' ', m.last_name) as name
     , m.email
     , m.phone
     , c.value as phone2
FROM hfwji_towns t 
     LEFT JOIN hfwji_members_towns mt ON t.id = mt.town_id
     LEFT JOIN hfwji_swpm_members_tbl m USING (member_id)
     LEFT JOIN hfwji_swpm_form_builder_custom c
                    ON m.member_id = c.user_id
                    AND c.field_id = 33
WHERE m.first_name IS NOT NULL                                    -- ADD THIS LINE
ORDER BY town, name;

Edited July 8, 2021 by Barand

[Oran223]

Wowsers - superb many thanks.  Will take a look tomorrow and see if I can get that working. 

Thanks a million!

[Oran223]

Morning Barand ...

Cannot believe your comprehensive response, to which I am VERY grateful and as I have already said, my mind is not good with databases the basics of SQL is now much much clearer and nowhere near as daunting to a newbie. However, I am sort of lost as to how this all pulls together on the physical page - clearly I still have some work to do and will order a book and spend some more time reading when I get some.  I tried to look through your tutorials and the sample code (namely the student table at the base of your JOINS page) for some pointers but have to admit I am lost.  

I think I MAY (?) be able to get the basic process working but I am not sure how the actual query needs to be wrapped/properly coded on the page??  

Therefore - not wishing to waste your excellent work are there any further pointers you can provide please to help me learn.

 

[Oran223]

Am I close ..... ???

<?php

$servername = "xxxxx";
$username = "xxxx";
$password = "xxxxxx";
$database = "xxxxxx";

// Create connection
$mysqli = new mysqli($servername, $username, $password, $database);

// Check connection
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

$sql = "SELECT t.town
    , t.postcode
    , concat(m.first_name, ' ', m.last_name) as name
    , m.email
    , m.phone
    , c.value as phone2
FROM hfwji_towns t 
     LEFT JOIN hfwji_members_towns mt ON t.id = mt.town_id
     LEFT JOIN hfwji_swpm_members_tbl m USING (member_id)
     LEFT JOIN hfwji_swpm_form_builder_custom c
                    ON m.member_id = c.user_id
                    AND c.field_id = 33
WHERE m.first_name IS NOT NULL  
ORDER BY town, name"; 

$results = $query($sql); ?>

<?php 

    $prev_town = "";
    foreach ($results) {
        if ($town != $prev_town) { 
            echo "<h3>" .$town. ' (' .$postcode. ')</h3>';
            $prev_town = $town;
        }
        echo '<p><b>' .$name. '<br>';
        echo '' .$email. '<br>';
        echo '' .$phone. '<br>';
        echo '' .$phone2. '<br></p>';
    }
?>

 

[Oran223]

Updated ...   I fear I am missing something large at the for each results line??? 

<?php

$servername = "xxxxx";
$username = "xxxx";
$password = "xxxxxx";
$database = "xxxxxx";

// Create connection
$mysqli = new mysqli($servername, $username, $password, $database);

// Check connection
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

$query = "SELECT t.town
    , t.postcode
    , concat(m.first_name, ' ', m.last_name) as name
    , m.email
    , m.phone
    , c.value as phone2
FROM hfwji_towns t 
     LEFT JOIN hfwji_members_towns mt ON t.id = mt.town_id
     LEFT JOIN hfwji_swpm_members_tbl m USING (member_id)
     LEFT JOIN hfwji_swpm_form_builder_custom c
                    ON m.member_id = c.user_id
                    AND c.field_id = 33
WHERE m.first_name IS NOT NULL  
ORDER BY town, name"; 

$result = $mysqli->query($query); ?>



<?php 

    $prev_town = "";
    foreach ($result) {
        if ($town != $prev_town) { 
            echo '<b>' .$town. ' (' .$postcode. ')</b><br>';
            $prev_town = $town;
        }
        echo '<p><b>' .$name. '<br>';
        echo '' .$email. '<br>';
        echo '' .$phone. '<br>';
        echo '' .$phone2. '<br></p>';
    }
?>

 

[Barand]

Very close, though that should have generated a few error messages.

Your query calling and foreach() syntax are wrong. It should be something like

$results = $mysqli->query($sql);

foreach ($results as $row) {

then reference $row['town'], $row['email'] etc.

 

My solution was (note I use and recommend PDO)

(Included file - db_inc.php)

const HOST     = 'localhost';                                                           
const USERNAME = '????';                                                                
const PASSWORD = '????';                                                                
const DATABASE = 'test');               // default db                                   
                                                                                        
function pdoConnect($dbname=DATABASE)                                                   
{                                                                                       
    $db = new PDO("mysql:host=".HOST.";dbname=$dbname;charset=utf8",USERNAME,PASSWORD); 
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);                       
    $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);                  
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);                               
    return $db;                                                                         
}                                                                                       

Code

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

//
//  QUERY THE DATABASE TO GET REQUIRED DATA
//
$res = $db->query("SELECT  t.town
                         , t.postcode
                         , concat(m.first_name, ' ', m.last_name) as name
                         , m.email
                         , m.phone
                         , c.value as phone2
                    FROM hfwji_towns t 
                         LEFT JOIN hfwji_members_towns mt ON t.id = mt.town_id
                         LEFT JOIN hfwji_swpm_members_tbl m USING (member_id)
                         LEFT JOIN hfwji_swpm_form_builder_custom c
                                        ON m.member_id = c.user_id
                                        AND c.field_id = 33
                    WHERE m.first_name IS NOT NULL
                    ORDER BY town, last_name
                    ");
//
//  PROCESS THE QUERY RESULTS
//
$prev_town = '';
$output = '';
foreach ($res as $r) {
    if ($r['town'] != $prev_town) {                               // new town value?
        $output .= "<h3>{$r['town']} ({$r['postcode']})</h3>\n";
        $prev_town = $r['town'];                                  // set new previous town name
    }
    $output .= <<<MEM
        <div class="member">
            {$r['name']}<br>
            {$r['email']}<br>
            {$r['phone']}<br>
            {$r['phone2']}
        </div>
MEM;

}
?>
<!DOCTYPE html>
<html lang="en">
<html>
<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>
<style type='text/css'>
    body {
        font-family: verdana, sans-serif;
        font-size: 10pt;
    }
    .member {
        width: 300px;
        border-bottom: 1px solid #CCC;
        margin: 10px 0 10px 50px;
        padding: 10px;
    }
</style>
</head>
<body>

    <?=$output?>
    
</body>
</html>

[Oran223]

Once again - Many thanks .... am enjoying the steep learning curve and hope I can get this working.

Quick question - Understand most of it .... but what is $db = pdoConnect('josen'); ... what is the Josen in brackets ????

[Barand]

As you can see from the definition of pdoConnect() function, the optional argument is the name of the database you want to use.

In this case, I put the test tables I used in a database called "josen".

[Oran223]

Thanks - I did think that was it and replaced it with my database name but it was not happy.

Once again - many thanks for you assistance.

[Barand]

Having had a second look at my query and data, the only table that requires a LEFT JOIN is the "form_builder_custom" one, giving

SELECT  t.town
      , t.postcode
      , concat(m.first_name, ' ', m.last_name) as name
      , m.email
      , m.phone
      , c.value as phone2
 FROM hfwji_towns t 
      JOIN hfwji_members_towns mt ON t.id = mt.town_id
      JOIN hfwji_swpm_members_tbl m USING (member_id)
      LEFT JOIN hfwji_swpm_form_builder_custom c
                     ON m.member_id = c.user_id
                     AND c.field_id = 33
 ORDER BY town, last_name

 

12 minutes ago, Oran223 said:

and replaced it with my database name but it was not happy.

what error message did you get?

[Oran223]

I did not get an error just a white page??

[Barand]

If you haven't got it displaying errors, and startup errors, check the error log.

[Oran223]

Will do.  Your knowledge of the final table in your workings out .... is that from experience or is there a piece of software/website that will help?  Although have ordered a book to help! 🙂

[Barand]

I don't understand your question.

[Oran223]

Hi ....

So my learning journey continues - Am working on a slightly different configuration to group the postcode towns to a member but as CSS classes to make it a sortable DIV - but I am wondering if there is a way to reference the content of the next row. For example:  In the last few lines you will see I am trying to close the div based upon if the name in the next record ($next_name) .... is it even possible to reference the next row or do I need to tackle this in another way?

Basically - trying to our something similar to the following for each person:

<div class="col-xs-12 col-md-3 **ID & Postcodes of all towns covered**> (ie <div class="col-xs-12 col-md-3 2 DL 3 NE 4 BA">
<h3>John Doe</h3>
<p>email & Telephone</p>
</div>

 

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

foreach ($res as $r) { 
   
    if ($r['name'] != $prev_name) {                       // new name value?
        $output .= '<div class="col-xs-12 col-md-3 ';
    }
    if ($r['id'] != $prev_id) {                           // different town id?
        $output .= "{$r['id']} {$r['postcode']} ";
        $prev_id = $r['id'];                             
    }
    if ($r['name'] != $prev_name) {                      
        $output .= '">';
    }
    if ($r['name'] != $prev_name) {                       
        $output .= "<h3>{$r['name']}</h3>
            <p>{$r['email']}<br>{$r['phone']}</p>";
    $prev_name = $r['name'];                             
    }
     if ($r['name'] != $next_name) {                     
        $output .= "</div>";
    }

}
?>

 

[Barand]

The processing remains basically the same with a couple of small additions

prev_town = ""
foreach results
    if town != prev_town 
        if prev_town != ""           // if not first group
            close previous div
        end if
        open new div group           // open new group 
        echo town heading
        prev_town = town
    end if
    echo member data    
end foreach
close previous div                   // close final div group

 

Edited July 12, 2021 by Barand
typos

[Oran223]

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>'