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)
  • Dion

    (@diondesigns)

    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.

    Thread Starter wamslers

    (@wamslers)

    @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 6 years, 10 months 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 ):

    Thread Starter wamslers

    (@wamslers)

    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();

    Thread Starter wamslers

    (@wamslers)

    It works now when I use get_results instead of results

Viewing 6 replies - 1 through 6 (of 6 total)
  • The topic ‘Using while and foreach with $wpdb’ is closed to new replies.