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