Support » Developing with WordPress » Using while and foreach with $wpdb

  • Hi

    I’m using ACF (Advanced Custom Fields) on my backend. I have a post_type named “rundowns” to make some lists of posts from another post_type named “elements”. On even rundown post i am using ACF to make an array with the ID’s of the posts from the post_type “elements”.

    Now I want to display all the posts on each rundown-post-page (single-rundown.php) – but I want to change the order of the list with drag-and-drop.

    I got these codes, but I can’t get the results from the MySQL.

    In the top of single-rundown.php:

    get_header();
    function get_records()
    {
    	//$con=connectsql();
    	$records=$wpdb->query("SELECT * FROM $wpdb->posts WHERE post_type = 'elements' AND post_status = 'publish'");
    	$all=array();
    	while($data=$records->fetch_assoc())
    	{
    		$all[]=$data;
    	}
    	return $all;
    }

    And here the code for displaying:

    <ul id="sortable">
    <?php $data=get_records(); ?> 
    <?php foreach($data as $record): ?>
    <li data-id="<?php echo $record['id'];  ?>" class="ui-state-default"><span class="ui-icon ui-icon-arrowthick-2-n-s"></span><?php echo $record['id'];  ?><?php echo $record['name']; ?></li>
    <?php endforeach; ?>
    </ul>
    <button id="save-reorder">Save</button>
Viewing 6 replies - 1 through 6 (of 6 total)
  • WordPress uses procedural-style access in its DBAL because it supports both mysql and mysqli extensions. That means you must first determine which extension $wpdb is using, and then use the appropriate procedural command. Make sure to free the result set afterwards!

    FYI, your code would run faster if you eliminated the get_records() function and added it directly into your display code. That would eliminate one of the two loops.

    Moderator bcworkz

    (@bcworkz)

    You need to declare global $wpdb; within scope of its use. Within the function declaration if you stay with that, or on the template if you take Dion’s advice.

    @diondesigns
    But how can I determine the mysql extension?

    Now I have tried to combine the two loops – but I get no results from the database.

    <?
    get_header();
    global $wpdb;
    ?>

    …..

    <?php 
    $records=$wpdb->query("SELECT * FROM $wpdb->posts WHERE post_type = 'elements' AND post_status = 'publish'");
    $all = array();
    foreach($data as $records): 
      $all[]=$data; ?>
    
    <li data-id="<?php echo $data['id'];  ?>" class="ui-state-default">
      <span class="ui-icon ui-icon-arrowthick-2-n-s"></span>
      <?php echo $data['id'];  ?>
      <?php echo $data['name']; ?>
      <?php echo $data['post_title']; ?>
    </li>
    
    <?php 
    endforeach; 
    return $all; 
    ?>
    • This reply was modified 4 months, 1 week ago by  wamslers.
    Moderator bcworkz

    (@bcworkz)

    $wpdb->query() merely returns the rows found. The actual results are in $wpdb->results.

    Your foreach parameters are reversed. Assuming $records would have results and not rows found, you want foreach( $records as $data ):

    I’m creazy – yes, it make sense to change the parameters in the foreach – but it still dosen’t work 🙁

    <?php 
    $records=$wpdb->results("SELECT * FROM $wpdb->posts WHERE post_type = 'elements' AND post_status = 'publish'");
    $all = array();
    foreach($records as $data): 
    $all[]=$data; 
    // doing stuff
    endforeach; 
    return $all; 
    ?>

    I think my SELECT statement is wrong. When I use a manual mysqli connection it works with a non-wordpress-tabel. I have the global $wpdb; just behind the get_header();

    It works now when I use get_results instead of results

Viewing 6 replies - 1 through 6 (of 6 total)
  • You must be logged in to reply to this topic.