Btukfyl

I have a custom table in my wordpress database. In the database, I'm showing photo galleries with their own gallery ID's. Each gallery has multiple photos in it, so I might have:

image_link: img1.png | upload_id: 47372

image_link: img2.png | upload_id: 47372

image_link: img3.png | upload_id: 47372

image_link: img4.png | upload_id: 373h3

image_link: img5.png | upload_id: 373h3

I have code that returns the img id's and links the user to a URL where they can view the gallery:

          $results = $wpdb->get_results( "SELECT * FROM wp_prefix_photos WHERE user_email='" . $email . "' AND created_date > $sevenDays ORDER BY created_date ASC" );



          ob_start();

          $arr = array();

          foreach ( $results as $result )

          {

            $arr = "<div style='border: 1px solid black'><a href='https://mywebsite.com/?photoID=" . $result->upload_id . "'>https://mywebsite.com/?photoID=" . $result->upload_id . "</a></div>";



          }

          $unique_data = array_unique($arr);

          // now use foreach loop on unique data

          foreach($unique_data as $val)

          {

            echo $val;;

          }



          $output = ob_get_clean(); // set the buffer data to variable and clean the buffer

          return $output;

Note that it's wrapped in a buffer, because it's inside a Wordpress shortcode, and the foreach stops return on the first result. I also use array_unique() to ensure that all results are unique (there's no need to show the same upload_id twice).

Now, here's the problem: I want to get the first and only the first image_link for each upload_id. When you stick echo "<br>" . $result->image_link; inside foreach ( $results as $result ), it prints every url that matches the id, and I can't use array_unique($arr); again because each img url is unique.

I think I can execute another DB query inside of foreach($unique_data as $val), using $val as the upload_id and grabbing the first result, but is there any way to adjust this so I don't have to? And if I do do that, will it be taxing on the server to run as many DB queries as I have foreach results?

Please change your query to

"SELECT max(image_link) as image_link,upload_id FROM wp_prefix_photos WHERE user_email='" . $email . "' AND created_date > $sevenDays group by upload_id ORDER BY created_date ASC"

I solved this only 30 minutes after asking the question, but I worked on it for hours before asking, so for anyone else who comes across:

$results = $wpdb->get_results( "SELECT * FROM wp_prefix_photos WHERE user_email='" . $email . "' AND created_date > $sevenDays GROUP BY upload_id" );



          ob_start();

          $arr = array();

          foreach ( $results as $result )

          {

            echo "<div style='border: 1px solid black'><a href='https://mywebsite.com/?photoID=" . $result->upload_id . "'>https://mywebsite.com/?photoID=" . $result->upload_id . "</a></div>";

            echo $result->image_link;

          }



          $output = ob_get_clean(); // set the buffer data to variable and clean the buffer

          return $output;

I got rid of array_unique($arr); and replaced it with GROUP BY upload_id in the query, which appears to ensure unique upload_id's and when used with echo $result->image_link;, only shows 1 link per unique upload_id.

Reference to this questions: wp get_results - filter unique records