Oran223

Hi ... I have a database that hold the images in a strange way and to import them I need to clear the extra data sitting around them. The meta_value is as follows : [{"id":"31768","imageurl":"https://sampledomain.com/wp-content/uploads/2021/10/904002d0f93c473a98f586302ccbd67d_ful.jpg"},{"id":"31766","imageurl":"https://sampledomain.com/wp-content/uploads/2021/10/5720254985644cb9a6679e258b74a202_ful.jpg"},{"id":"31767","imageurl":"https://sampledomain.com/wp-content/uploads/2021/10/155aa9070ad6404586a50e865be43a17_ful.jpg"},{"id":"31765","imageurl":"https://sampledomain.com/wp-content/uploads/2021/10/10ba1f392a7b401087e6d1ede995057b_ful.jpg"}] but I need it as this (simply the image url separated by a comma) .... https://sampledomain.com/wp-content/uploads/2021/10/904002d0f93c473a98f586302ccbd67d_ful.jpg,https://sampledomain.com/wp-content/uploads/2021/10/5720254985644cb9a6679e258b74a202_ful.jpg,https://sampledomain.com/wp-content/uploads/2021/10/155aa9070ad6404586a50e865be43a17_ful.jpg, https://sampledomain.com/wp-content/uploads/2021/10/10ba1f392a7b401087e6d1ede995057b_ful.jpg Using wildcards is there a way to be able clean/remove the unwanted data??? I have had a go but my knowledge of MySQL is not that advanced. Many thanks in advance.

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.

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

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.

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

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

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

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

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>"; } } ?>

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

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

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.

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

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>'; } ?>

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>'; } ?>

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.

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

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.

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.

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

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

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.