webbiegurrl Posted August 4, 2017 Share Posted August 4, 2017 First, please don't blast me for the ugly code, as I am still learning php and altering existing code is the easiest place to start. I hope to fix it up a bit once it's working properly. I want to display graphics in groups by size, but within that size also grouped by the donator. GRAPHIC SIZE Donated by Bob image image image Donated by Sue image image GRAPHIC SIZE The following code does successfully group by size, but just adds the new donator's codes in with the previous. <?php $urlParts = explode('.', $_SERVER['HTTP_HOST']); $str = $urlParts[0]; $subdomain = preg_replace('#^https?://#', '', rtrim($str,'/')); $stmt = $pdo->prepare('SELECT * FROM images_tbl WHERE subdomain=? ORDER BY width ASC, height ASC, email'); $stmt->execute([$subdomain]); if($stmt->rowCount() != 0) { echo "<h3>Donated</h3>"; } $current_size = null; $current_donator = null; while ($row = $stmt->fetch()) { $size = $row["width"].'x'.$row["height"]; $email = $row["email"]; if ($size != $current_size) { echo "<h6>{$size}</h6>\n"; $url = $row["url"]; $name = $row["name"]; $text = $row["url"] !== "your url" ? '<a href="' . $url . '" target="' . $target . '">' . $name . '</a>' : $name; if ($email != $current_donator) { echo "<p>Donated by $text</p>"; $current_donator = $email; } $current_size = $size; } if ($row["url"] !== "your url") { ?> <a href="<?=$row["url"]?>" target="<?=$target?>"><img src="DOMAIN/<?=$row["images_path"]?>" width="<?=$row["width"]?>" height="<?=$row["height"]?>" title="donated by <?=$row["name"]?>"></a> <?php } else { ?> <img src="DOMAIN/<?=$row["images_path"]?>" width="<?=$row["width"]?>" height="<?=$row["height"]?>" title="donated by <?=$row["name"]?>"> <?php } } ?> Quote Link to comment Share on other sites More sharing options...
Solution Jacques1 Posted August 4, 2017 Solution Share Posted August 4, 2017 You'll make your life a lot easier if you first turn the result set into a more convenient format and then render that custom format. For example: [ '100x100' => [ 'bob@example.com' => [ $bob_donation_1, $bob_donation_2, // ... ], 'sue@example.com' => [ $sue_donation_1, $sue_donation_2, // ... ] ], '150x200' => [ // ... ], ] This can be rendered with a simple nested foreach loop rather than a complex state machine. Pre-processing the result set is also trivial. donations = [] for each row in result_set: donations[row["width"] + "x" + row["height"]]["email"][] = all_donation_related_data_you_need Quote Link to comment Share on other sites More sharing options...
webbiegurrl Posted August 6, 2017 Author Share Posted August 6, 2017 Thanks for answering Jacques1, but truthfully, I have no idea what you're talking about or where/how to implement it. Quote Link to comment Share on other sites More sharing options...
Jacques1 Posted August 6, 2017 Share Posted August 6, 2017 So, do you want help or not? If the task isn't really important, I'm OK with that. I have more than enough to do, so we can just forget about the thread and move on. If you do want a solution, then I expect you to turn your brain on and actively work on the problem. You have no idea what I'm talking about? That's strange, because this is your task. I didn't come up with it. You did. Your initial approach is horrendeously complex, because you're trying to render the result set directly, and that forces you to have all those if checks. I'm proposing a simplification: First turn the result set into a more convient data structure. Then render that new data structure. No if statements, no $current_* variables. Just a loop. Which part of this do you not understand? Quote Link to comment Share on other sites More sharing options...
kon Posted August 6, 2017 Share Posted August 6, 2017 One way would be what Jacques1 suggested. If you don't know how to turn the results array into an array that has a more convenient format , you can write what you tried and we will help you from there (but please , first try something). The other approach is using prepared statements (that from your code I can tell you know the basics) to select first the different “GRAPHIC SIZE”. You can do it in many ways and GROUP BY is one of those. Then having those and using each “GRAPHIC SIZE” you can select different donors that correspond in this (again you can do it with GROUP BY). Finally you have the “GRAPHIC SIZE” and the donator , so you can perform a simple select to get the images (the priorities in each select is up to you). Using prepared statements (and the PDO objects of those) will make the performance really great IF you don't have a great amount of “GRAPHIC SIZE” and a great amount of “donators”. In any case you should consider creating another table of “GRAPHIC SIZES” and another for donators and have in this table only the id of those records. That would be the reasonable way but also would allow you could scale your app easy if records are more than you anticipated. Quote Link to comment Share on other sites More sharing options...
Jacques1 Posted August 6, 2017 Share Posted August 6, 2017 Your GROUP BY doesn't make any sense in this context (apparently you confuse this with something else like SELECT DISCTINCT), and multiple nested queries are going to be extremely inefficient and make the code much more complex than necessary. This is a simple task with a simple solution. Quote Link to comment Share on other sites More sharing options...
kon Posted August 6, 2017 Share Posted August 6, 2017 Hello Jacques1, As I wrote there are many ways depending on schema . One of those is “SELECT DISTINCT” but the “GROUP BY” would allow us to list them in an order of how many records are there in each case. We don't have any clue of the specs so let's give all options. We also don't know the table. I still agree with your first reply that can be done only with PHP, but it can be done other ways also. We don't have to disagree only for making it interest Quote Link to comment Share on other sites More sharing options...
Jacques1 Posted August 6, 2017 Share Posted August 6, 2017 When your suggestion doesn't make sense, then it's pretty much my job to disagree with you. The specs are absolutely clear, and once again, this is a very, very simple task. There's no point in turning this into advanced SQL gymnastics when the OP already struggles with a single query. Quote Link to comment Share on other sites More sharing options...
kon Posted August 6, 2017 Share Posted August 6, 2017 Is there any specs how the first select “GRAPHIC SIZE” is selected ? I would bet that anyone would want it to be ordered by how many times are there. But I could bet wrong , simple WE DONT KNOW. First Jacques1 you proposed an all PHP solutions with arrays and building them without any clue to the creator of this post of how to do it. I am fine with that approach , its great but there are other approaches also. Then in the second approach that I PROPOSED you have a disagreement of the use of “SELECT DISTINCT” vs “GROUP BY” this wasn't what I proposed at all … Copying from my first reply “You can do it in many ways and GROUP BY is one of those” it all depends of what really needs to be done . If how many records are there go with the “SELECT DISTINCT” if how many records affect the order go with “GROUP BY”. No need to disagree where there isn't a issue to. (let's keep that for when we really disagree) Quote Link to comment Share on other sites More sharing options...
Jacques1 Posted August 6, 2017 Share Posted August 6, 2017 Did you even read the original post? The OP is ordering the graphics by dimension. The specifcations are absolutely clear. What the OP wants makes perfect sense, and I see no reason whatsoever for claiming ambiguity when there is none and inventing features nobody asked for. Even if she somehow changed her mind and wanted a different ordering scheme, you would still only run one query. Not nested queries within nested queries within nested queries. What you're proposing is insanely inefficient, bloats the code beyond any reasonable limit and is even more complicated than what the OP currently has. I'm all for new ideas, and I wouldn't even have commented on your approach if I thought it was OK. It's quite common that a thread ends with multiple valid solutions. But when the proposed solution is worse than the code which came in, there's definitely something wrong. Quote Link to comment Share on other sites More sharing options...
webbiegurrl Posted August 9, 2017 Author Share Posted August 9, 2017 Jacques1, I must admit that when I first read your second submission, I was pretty ticked off by your bluntness. I mean I did state that I am just learning. I really felt like I was one simple tweak away with my existing script, but then you wanted me to put something else together entirely. I have been over and over your suggestion since reading it and have googled and really tried to figure it out how to setup the array - pretty sure I'm over complicating it. I want so desperately to learn php, you have no idea. I read once that you're either a coder and it all makes sense to you, or you're not. I really don't want that to be true and have persevered through the fog. The books I've read made sense to me while reading, but I haven't been able to put it into practice. Maybe this script will never behave the way I want. Can you point me in the direction of useful (up-to-date) tutorials for those who struggle to put all the pieces together? Sorry if you think I've wasted your time, as I can imagine that you have much more important things to do, but I really do want to learn. Thanks Quote Link to comment Share on other sites More sharing options...
Jacques1 Posted August 9, 2017 Share Posted August 9, 2017 (edited) I think you're missing the forest for the trees. The pseudo-code I posted is exactly what you need to write down in PHP – well, plus a few dollar signs and semicolons. $donations = []; foreach ($result_set as $row) $donations[$row['width'].'x'.$row['height']]['email'][] = ['name' => $row['name'], 'url' => $row['url']]; Yes, that's all. I'm sure you agree this is much, much simpler than the complex logic you currently have. Once you've built the array, you can iterate over the various groups with a nested foreach loop. foreach ($donations as $dimensions => $dim_donations) { // show heading with dimensions foreach ($dim_donations as $email => $images) { // show donor's name or email address // list images foreach ($images as $image) { } } } Edited August 9, 2017 by Jacques1 Quote Link to comment Share on other sites More sharing options...
webbiegurrl Posted August 10, 2017 Author Share Posted August 10, 2017 Absolutely! I completely agree that this is much simpler (and definitely cleaner) than what I had. Thank you. I am stuck though with the script repeating the last value in every instance and I really can't see the problem. Here's what I have: <?php $urlParts = explode('.', $_SERVER['HTTP_HOST']); $str = $urlParts[0]; $subdomain = preg_replace('#^https?://#', '', rtrim($str,'/')); $stmt = $pdo->prepare('SELECT * FROM images_tbl WHERE subdomain=? ORDER BY width ASC, height ASC, email'); $stmt->execute([$subdomain]); if($stmt->rowCount() != 0) { echo "<h3>Donated</h3>"; } $donations = []; foreach ($stmt as $row) $donations[$row['width'].'x'.$row['height']][$row['email']][] = ['name' => $row['name'], 'url' => $row['url'], 'img' => $row['images_path']]; foreach ($donations as $dimensions => $dim_donations) { // show heading with dimensions echo "<br><br><h6>{$dimensions}</h6>\n"; foreach ($dim_donations as $email => $images) { $text = $row['url'] !== "your url" ? '<a href="' . $row['url'] . '" target="' . $target . '">' . $row[name] . '</a>' : ' . $row[name] . '; // show donor's name or email address echo "<p>Donated by $text</p>"; foreach ($images as $image) { // list images echo '<img src="http://mydomain.com/common/' . $row['images_path'] . '" width="' . $row['width'] . '" height="' . $row['height'] . '">'; } } } ?> I have been using print_r() to see what my array looks like (really trying to understand each step). So my $donations array looks like this Array ( [50x50] => Array ( [email@gmail.com] => Array ( [0] => Array ( [name] => Andrea [url] => http://domain.com [img] => donatedimages/subdomain/email@gmail.com/50x50/50x50a.jpg ) [1] => Array ( [name] => Andrea [url] => http://domain.com [img] => donatedimages/subdomain/email@gmail.com/50x50/50x50b.jpg ) ) ) [75x50] => Array ( [email@gmail.com] => Array ( [0] => Array ( [name] => Andrea [url] => http://domain.com [img] => donatedimages/subdomain/email@gmail.com/75x50/75x50a.jpg ) [1] => Array ( [name] => Andrea [url] => http://domain.com [img] => donatedimages/subdomain/email@gmail.com/75x50/75x50b.jpg ) ) ) etc... This all looks good to me. But under each dimension heading (which iterates properly) I'm getting the Donated by link followed by 3 graphics that are all 100x50c.jpg (the very last graphic), and if there is a different donator within the same dimension, it is the same exact link as the previous donator, not the new one. I'm really at a loss as print_r($images) shows me exactly what should be displayed but isn't. ... [1] => Array ( [name] => Andrea [url] => http://domain.com [img] => donatedimages/subdomain/email@gmail.com/50x50/50x50b.jpg ) [2] => Array ( [name] => Andrea [url] => http://domain.com [img] => donatedimages/subdomain/email@gmail.com/50x50/50x50c.jpg ) ) Donated by Andrea <---- this is the previous donator not the current one Array ( [0] => Array ( [name] => Barbara [url] => http://differentdomain.com [img] => donatedimages/subdomain/differentemail@gmail.com/50x50/5050-1.png ) ) I hope what I've explained makes sense. Quote Link to comment Share on other sites More sharing options...
Jacques1 Posted August 10, 2017 Share Posted August 10, 2017 In your $donations loop, you're trying to pull data out of a $row variable, but $row only exists as a leftover from the previous query. It's the wrong variable. You need to use the acual key and value variables from this loop ($email, $images etc.). If you need more information about each donor, then adjust the data structure accordingly. $donations = []; foreach ($result_set as $row) { // if the donor doesn't exist yet, add their data if (!isset($donations[$row['width'].'x'.$row['height']][$row['email']])) $donations[$row['width'].'x'.$row['height']][$row['email']] = [ 'name' => $row['name'], 'url' => $row['url'], 'images' => [], ]; // append image $donations[$row['width'].'x'.$row['height']][$row['email']]['images'][] = $row['images_path']; } var_dump($donations); Don't just randomly combine code. Make sure you actually understand the context and what data is where. Quote Link to comment Share on other sites More sharing options...
webbiegurrl Posted August 10, 2017 Author Share Posted August 10, 2017 I'm sure this kind of thing you can do in your sleep, but sadly I can't, but still want to get there. I really appreciate all your help and it pushed me to get this done. I almost gave up (& cried), but got it done and learned a lot along the way. Here is my final code - still had some snags that I had to sort out, but I did it. <?php $urlParts = explode('.', $_SERVER['HTTP_HOST']); $str = $urlParts[0]; $subdomain = preg_replace('#^https?://#', '', rtrim($str,'/')); $stmt = $pdo->prepare('SELECT * FROM images_tbl WHERE subdomain=? ORDER BY width ASC, height ASC, email'); $stmt->execute([$subdomain]); if($stmt->rowCount() != 0) { echo "<h3>Donated</h3>\n"; } $donations = []; foreach ($stmt as $row) { // if the donor doesn't exist yet, add their data if (!isset($donations[$row['width'].'x'.$row['height']][$row['email']])) $donations[$row['width'].'x'.$row['height']][$row['email']] = [ 'name' => $row['name'], 'url' => $row['url'], 'images' => [], ]; // append image $donations[$row['width'].'x'.$row['height']][$row['email']]['images'][] = $row['images_path']; } foreach ($donations as $dimensions => $dim_donations) { // show heading with dimensions echo "<br><br><h6>{$dimensions}</h6>\n"; foreach ($dim_donations as $email => $data) { $text = $data['url'] !== "your url" ? '<a href="' . $data['url'] . '" target="' . $target . '">' . $data[name] . '</a>' : "$data[name]"; // show donor's name or email address echo "<p>Donated by $text</p>\n"; foreach ($data as $images => $image) { if (is_array($image)) { foreach ($image as $code) { // list images echo '<img src="http://fan-a-tic.ca/common/' . $code . '" width="' . $dim_donations['width'] . '" height="' . $dim_donations['height'] . '">'; echo " \n"; } } } } } ?> Makes me feel like a million bucks when I can get things working the way I envision (even if it's with A LOT of help). Thanks again Jacques1!!!! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.