Support » Plugins » Hacks » List media files.

  • Resolved Martijn

    (@ximie90)


    Hi,

    we have a custom post type ‘Locations’ => with Advanced custom fields we want to add a ‘folder ID’* to each post. So we can show a list of all files/photo’s linked to a specific ‘folder’*

    *= we use ‘Real media library’ plugin => this can create folders in the media library.
    In the database we have a table, were the following columns ‘fid’ with the folder ID and ‘attachment’ with a post id of the file.

    We want a simple list, with clickable links to the files from that ‘folder’.

    How do I query this?
    I’m quite new to building WP queries.
    I build some stuf with mysqli queries, the its quite simple.
    SELECT ‘attachment’ FROM … WHERE fid = ‘*folder id*’
    and then a for each or while to query the file link (guid ?) from post table with the id you got from attachment. and echo/print this in a anchor tag within a li tag.

    But I do not know how to do this the wordpress way, and I would like to know how I can do this.

    Kind regards,

    Martijn

Viewing 11 replies - 1 through 11 (of 11 total)
  • Moderator bcworkz

    (@bcworkz)

    If you’re able to construct an SQL query that’ll do the job, you can run it using the global wpdb class object: $wpdb. Most devs tend to use the WP_Query class to construct custom queries. Its argument structure tends to be more accessible and forgiving. WP_Query doesn’t always work for all situations. While very flexible, its flexibility is not unlimited. There are a number of filters in WP_Query::get_posts() that allow us to alter various clauses of the resulting SQL query string. This greatly extends its flexibility, but it still has its limits. When all else fails, there’s always that global $wpdb. You can run any query that’s possible this way, so it’s the ultimate in flexibility, provided you are able to construct the desired query correctly.

    I will warn you to not rely on the GUID field for related file data. That is not the purpose of the field. There’s nothing saying the GUID cannot be some other unique identifier that is unrelated to the file. The actual field used to reference a file is in postmeta. Your queries should use this postmeta, not GUID.

    The one glaring weakness of WP_Query is it cannot access data of custom tables. In order to do that you need to build your own SQL queries and run them through global $wpdb. I’m rather weak with SQL myself. I’ve resorted to using WP_Query just to see what SQL it comes up with, then modifying it for my needs and using the result in $wpdb. The WP_Query part is not part of the final code, it’s just a learning technique I sometimes use when I’m stuck in my SQL query composition effort 🙂

    Thread Starter Martijn

    (@ximie90)

    Hi,

    I’ve looked at this, thanks.

    I now have a query that works with this:

    if ( $postids ) 
    {
    	echo "<ul>";
    	foreach ( $postids as $id ) 
    	{ 
    		$post = get_post( intval( $id ) );
    		setup_postdata( $post );
    		?>
    		<li>
    			<a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title_attribute(); ?>">
    				<?php the_title(); ?>
    			</a>
    		</li>
    		<?php
    	} 
    	echo "</ul>";
    }

    This gives the list, but the link is to the ‘page’ of that attachment, and then on that page is the link to the file. How would I get the link to the file instantly?

    Moderator bcworkz

    (@bcworkz)

    You want to query for “attachment” post types whose parent has the folder id of interest. Then the file path relative to uploads folder is in each attachment’s postmeta under the key “_wp_attached_file”

    Thread Starter Martijn

    (@ximie90)

    I solved it, thanks!

    Adding this $path = wp_get_attachment_url( $id );
    and echoing $path instead of the_permalink();

    Kind regards,

    Martijn

    Thread Starter Martijn

    (@ximie90)

    I just tried to Add this code to a widget, where the query is not working, should I use something special in a widget?

    Moderator bcworkz

    (@bcworkz)

    No, but the snippet you posted is missing the actual query that generates $postids, so I couldn’t speculate why it doesn’t work in a widget. Are you otherwise getting widget output, just no posts returned?

    Thread Starter Martijn

    (@ximie90)

    Oh sorry, here is the complete code:

    $postids=$wpdb->get_col( $wpdb->prepare( 
    	"
    	SELECT      asdr_medialibrary_posts.attachment
    	FROM        asdr_medialibrary_posts
    	WHERE       asdr_medialibrary_posts.fid = %s
    	
    	",
    	$meta_key2
    ) );
    
    if ( $postids ) 
    {
    	echo "<ul>";
    	foreach ( $postids as $ida ) 
    	{ 
    		
    		
    		$post = get_post( intval( $ida ) );
    		setup_postdata( $post );
    		$path = wp_get_attachment_url( $ida );
    		?>
    		<li>
    			<a href="<?php echo $path; ?> " rel="bookmark" title="<?php the_title_attribute(); ?>">
    				<?php the_title(); ?>  
    			</a>
    		</li>
    		<?php
    	
    	} 
    	echo "</ul>";
    }else{ echo 'Geen bestanden'; }

    I used the same query and code in a page template, only difference is $metakey2 but I can echo that just fine on both.

    When I comment out the query it the page loads normally with just an empty widget. But with the query there, the page stops loading on that widget, and all that comes after is not loaded. (i put the widget in between 2 other widgets, the one before it will load. the one after wont.

    • This reply was modified 7 years, 3 months ago by Martijn. Reason: Extra info
    Moderator bcworkz

    (@bcworkz)

    Thanks for the query. As it turns out, that part is fine, the problem is in the snippet you posted prior to that but I couldn’t see it then. Something about being out of context confused me I think 🙂

    Just add global $post; somewhere before the foreach loop. It’s a common omission. I forget it myself sometimes even though I know better. Use it anytime you use setup_postdata(). You didn’t need it on the main template because it was declared in scope as part of the_post() in the main loop.

    Thread Starter Martijn

    (@ximie90)

    Hi,

    It is not working still.

    if I comment out:

    $postids=$wpdb->get_col( $wpdb->prepare( 
    	"
    	SELECT      asdr_medialibrary_posts.attachment
    	FROM        asdr_medialibrary_posts
    	WHERE       asdr_medialibrary_posts.fid = %s
    	
    	",
    	$meta_key2
    ) );

    This bit, it ‘works’ as in it echos Geen bestanden from the ..else{ echo 'Geen bestanden'; }

    But with this query, which worked on the other page. it just doesn’t work.

    Moderator bcworkz

    (@bcworkz)

    Well, the global was necessary, but it apparently prevented me from seeking any other issues. The issue with the query is so closely related I’m embarrassed that I missed it.

    Just before the query declare global $wpdb; 🙂

    Sorry for being a bit blind, but that’s how it goes sometimes.

    Thread Starter Martijn

    (@ximie90)

    This worked, thanks.
    I have something working now.
    Thanks fro the help

Viewing 11 replies - 1 through 11 (of 11 total)
  • The topic ‘List media files.’ is closed to new replies.