I am trying: 1) to display all records from a mysql wpdb table, but without the trashed products, and 2) to paginate the html table.
I've searched in other questions and tried those answered methods, but none worked so far.
Here's my code (with a simplified version of the html table, for exemplification purposes)
<?php
$page = (isset($_GET['page'])) ? $_GET['page'] : 1;
$startrow = $page - 1;
$records = $wpdb->get_results( "
SELECT * FROM {$wpdb->prefix}table_name LIMIT $startrow, 20
WHERE product_id IS NOT NULL AND product_id <> ''
ORDER BY time DESC
" );
if ( sizeof( $records) > 0 ) {
<table>
<thead>
<tr>
<th><?php echo 'Date' ?></th>
<th><?php echo 'Product' ?></th>
<th><?php echo 'Order' ?></th>
</tr>
</thead>
<tbody>
<?php $i = 1;
foreach ( $records as $row ) :
$i++; ?>
<tr>
<td><?php echo $date ?></td>
<td><?php echo $product ?></td>
<td><?php echo $order ?></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
<?php echo '<a href="'.$_SERVER['PHP_SELF'].'?startrow=' ($startrow+20).'">Next</a>';
$prev = $startrow - 20;
if ($prev >= 0) {
echo '<a href="'.$_SERVER['PHP_SELF'].'?startrow='.$prev.'">Previous</a>';}?>
Questions:
1) How do I skip the rows for products that have been previously deleted permanently from wp dashboard and thus they don't have a product_id any more?
product_id IS NOT NULL AND product_id <>
or just
product_id IS NOT NULL
don't seem to work. It still displays blank rows for products that have been previously deleted.
2) As for the pagination of the table, I want to display 20 rows per page, with Next/Previous buttons, but my code doesn't seem to work as well and I am out of ideas.
Why don't you try using WordPress' built in query functions/Classes like get_posts() or WP_Query()?
For example, instead of:
$records = $wpdb->get_results( "
SELECT * FROM {$wpdb->prefix}table_name LIMIT $startrow, 20
WHERE product_id IS NOT NULL AND product_id <> ''
ORDER BY time DESC
" );
Do this:
$args = array(
'post_type' => 'product',
'posts_per_page' => 20,
'status' => 'publish',
'paged' => $paged
);
$loop = new WP_Query( $args );
This will give you all the active products without the delete ones.
For pagination make sure to declare the $paged variable before the arguments section above:
$paged = ( get_query_var('page') ) ? get_query_var('page') : 1;
And also add this code below the table:
<nav>
<ul>
<li><?php previous_posts_link( '« PREV', $loop->max_num_pages) ?></li>
<li><?php next_posts_link( 'NEXT »', $loop->max_num_pages) ?></li>
</ul>
</nav>
The completed code should look like this:
$paged = ( get_query_var('page') ) ? get_query_var('page') : 1;
$args = array(
'post_type' => 'product',
'posts_per_page' => 20,
'status' => 'publish',
'paged' => $paged
);
$loop = new WP_Query( $args );
if ( $loop->have_posts() ) {
<table>
<thead>
<tr>
<th><?php echo 'Date' ?></th>
<th><?php echo 'Product' ?></th>
<th><?php echo 'Order' ?></th>
</tr>
</thead>
<tbody>
<?php while ( $loop->have_posts() ) : $loop->the_post();?>
<tr>
<td><?php echo $date ?></td>
<td><?php echo $product ?></td>
<td><?php echo $order ?></td>
</tr>
<?php endwhile; ?>
</tbody>
</table>
}
<nav>
<ul>
<li><?php previous_posts_link( '« PREV', $loop->max_num_pages) ?></li>
<li><?php next_posts_link( 'NEXT »', $loop->max_num_pages) ?></li>
</ul>
</nav>