Display single post inside accordion based on form submission results

Question

*Note - for full background see previous question here: http://bit.ly/1gaF673

Ok, so after fixing the problem above and a couple of issues that followed. My form submission returns the results I want. The code that does this is as follows:

//THIS CODE CONNECTS TO THE NON-WP-TABLES WE MADE INSIDE THE WP DATABASE
global $wpdb;
//get list of localities
$localityRow = $wpdb->get_results( "SELECT Name, LocalityID FROM tblYBSLocality ORDER BY
Name ASC" );
//get list of schools
$schoolRow = $wpdb->get_results( "SELECT Name, SchoolID FROM tblYBSSchool ORDER BY Name 
ASC" );
//get school ID if form has been submitted
$SelectedSchoolID = $_GET['schools'];
//get location ID if form has been submitted
$SelectedLocationID = $_GET['localities'];
//get selected school name
$selectedSchoolName = $wpdb->get_var( "SELECT Name FROM tblYBSSchool WHERE 
SchoolID=".$SelectedSchoolID );
//get selected location name
$selectedLocationName = $wpdb->get_var( "SELECT Name FROM tblYBSLocality WHERE 
LocalityID=".$SelectedLocationID );
//get route ID
$getRouteID = $wpdb->get_results( "SELECT Name, RouteID, Description, RoutePageURI
FROM tblYBSRoute WHERE RouteID IN ( SELECT RouteID FROM tblYBSRouteSchool WHERE SchoolID = 
$SelectedSchoolID ) AND RouteID IN ( SELECT RouteID FROM tblYBSRouteLocality WHERE 
LocalityID = $SelectedLocationID ) " );

//THIS CODE DISPLAYS THE RESULTS BASED ON WHAT THE USER HAS SELECTED IN THE CODE OMITTED
if (isset($SelectedLocationID) && isset($SelectedSchoolID)) { ?>
<div id="resultsDiv">
<div id="resultsHead">
<h3>Search results for routes between:</h3>
<b><?php echo $selectedLocationName; ?></b> and <b><?php echo $selectedSchoolName; ?>
</b><br />
(Click on route to view timetable and map)
</div>
<div id="routeResult">
<?php
if ($getRouteID) {
foreach ($getRouteID as $route) {
$RouteName = $route->Name;
$RouteID = $route->RouteID;
$RouteDescription = $route->Description;
$RoutePageID = $route->RoutePageURI;
?>
<a href="../../<?php echo $RoutePageID;?>"><b>Route: </b><?php echo $RouteName;?></a> <br 
/> <?php echo $RouteDescription;?><br />
<?php
}
} else {
?>
<div id="routeRestult">
<h4>Results:</h4>
No Route found for your selection.<br />
</div><?php } ?> </div></div><?php } ?>

This works as is, but I want to change what happens. Currently, there's a link to the individual post ID where the results are - which is awesome. But I want to do some scorcery and put that post inside an accordion.

I found a starting point here: http://bit.ly/1nqT6Pw - but need to get my head around how to make it more specific to what I'm trying to do. The code from the link above is (as it is in that post):

<?php if (have_posts()): ?>
<div id="accordion">
<?php while (have_posts()) : the_post();?>
<div class="accordion-header">
<h1><?php the_title();?></h1>
<?php the_excerpt();?>
</div>
<div><?php the_content();?></div>
<?php endwhile; ?>
</div>
<?php else:?>
<p><?php _e('No posts'); ?></p>
<?php endif;?>

I know you can pull a post apart by title, excerpt, and content - but I can't seem to get this to work properly to put them into an accordion.

I've modified the above code to be the following:

<div id="routeResult"> <?php
if ($getRouteID) {
foreach ($getRouteID as $route) {
$RouteName = $route->Name;
$RouteID = $route->RouteID;
$RouteDescription = $route->Description;
$RoutePageID = $route->RoutePageURI; ?>
<div id="accordion"> <?php
while (is_single($RoutePageID));?>
<div class="accordion-header">
<h1><?php the_title();?></h1>
<?php the_excerpt();?>
</div>
<div><?php the_content();?></div>
</div><?php } } else { ?>
<div id="routeResult">
<h4>Results:</h4>
No Route found for your selection.<br />
</div> <?php } ?> </div>

This works better than you'd think. It creates the divs but with no content except for the title (wait for it....) but the WRONG title. It gives me the title of the page and not the title of the post.

So the while statement is detecting that yes $RoutePageID is valid and it is a single post - but then returns the current pages title.

Is it because I've connected to other tables and $wpdb needs to be told to go back to the original table? Or should I be doing something with get_post (can't link to codex as need more reputation but I know where to look).

As always, any and all help is greatly appreciated. Thanks yo!

Answer 1

Closing off as the issue is resolved. For anyone interested, the result is as follows (refer to the code detailed above for the initial DB-table connections, as the code below refers to the output only):

if (isset($SelectedLocationID) && isset($SelectedSchoolID)) { ?>
<div id="resultsDiv">
  <div id="resultsHead">
    <h3>Search results for routes between:</h3>
    <b><?php echo $selectedLocationName; ?></b>
    and <b><?php echo $selectedSchoolName; ?></b><br />

    (Click on route to view timetable and map)
  </div>

  <?php
  if ($getRouteID) { ?>
  <div id="routeResult">
    <div id="accordion">
    <?php
    foreach ($getRouteID as $route) {
      // Break the getRouteID results into separate variables
      $RouteName = $route->Name;
      $RouteID = $route->RouteID;
      $RouteDescription = $route->Description;
      $RoutePageID = $route->RoutePageURI;

      $getPost = $wpdb->get_results(
        $wpdb->prepare (
          "SELECT post_content, post_name
          FROM m6anby77we_posts
          WHERE post_name = '%s'", $RoutePageID
        )
      );

      foreach ($getPost as $post) {
        $pName = $post->post_name;
        $pContent = $post->post_content;

        ?>
        <div class="accordion-header">
          <h4>Route: <?php echo $RouteName;?> <?php echo $RouteDescription;?></h4>
        </div>
        <div><?php echo $pContent; ?></div>
        <?php
      }
    }
    ?>
    </div>
  </div>
<?php } else { ?>
  <div id="routeResult">
    <h4>Results:</h4>
    No Route found for your selection.<br />
  </div>
<?php } ?> </div> <?php } ?>

Only trick/ painful thing is that the post content is brought in as a string (to be expected) - so you'll have to go into the text editor within each post to ensure that you've formated it properly in there so that the string can then be printed as html.

Have fun y'all