[RESOLVED] Alternate Display of Content from Two Queries

[bronzino]

I am using this script to do a vertical scroller on my site. For content, I want to take "new" material from one MySQL table and intersperse it with "new" material from another, so it displays in an alternating fashion like this:

Table 1 Item 1
Table 2 Item 1
Table 1 Item 2
Table 2 Item 2
Table 1 Item 3
Table 2 Item 3
Table 1 Item 4
Table 2 Item 4

I'll paste the queries below in case they're of any help - each query is designed to produce only four items from each table, so the scroller will only have 8 total items. Can someone advise what kind of loop I should be using for this situation? I've only done simple "do while" loops so far, and that is not working here. When I try to put one inside the other, I get no content from the inner loop.

Thank you in advance for any help.

// NEW ITEMS 
$query_newitems = "SELECT *, (select max(PubDate) from PubData where PubID=Items.ID group by PubID) as TheDate FROM Items WHERE Items.Publish='Yes' ORDER BY TheDate DESC LIMIT 0,4";
$newitems = mysql_query($query_newitems, $CR) or die(mysql_error());
$row_newitems = mysql_fetch_assoc($newitems);
$totalRows_newitems = mysql_num_rows($newitems);

// NEW RESULTS 
$query_newresults = "SELECT * FROM Show_Records, Items WHERE Show_Records.Publish = 'Yes' and Items.Publish = 'Yes' and Show_Records.ID = Items.ID ORDER BY Show_Records.`Date` DESC LIMIT 0, 4";
$newresults = mysql_query($query_newresults, $CR) or die(mysql_error());
$row_newresults = mysql_fetch_assoc($newresults);
$totalRows_newresults = mysql_num_rows($newresults);

[Weedpacket]

Loop through the two result sets separately, with each table's results going in their own array: $pubdata_results and $showrecords_results, say.

You'd need to do something if one of the arrays ends up shorter than the other.

$interleaved_results = array()
while(!empty($pubdata_results) && !empty($showrecords_results))
{
    $interleaved_results[] = array_shift($pubdata_results);
    $interleaved_results[] = array_shift($showrecords_results);
}

[bronzino]

I appreciate your response. I was afraid you were going to say arrays were required, because while I vaguely understand them, I have never really used them and when I do, I tend to make a mess. Case in point. Here is what I did:

$results1 = array();
$results1[] = $row_newitems;
sort($results1);foreach ($results1 as $key1 => $val1)

$results2 = array();
$results2[] = $row_newresults;
sort($results2);foreach ($results2 as $key2 => $val2)

$interleaved_results = array();
while(!empty($results1) && !empty($results2));
{
    $interleaved_results[] = array_shift($results1);
    $interleaved_results[] = array_shift($results2);
     print_r($interleaved_results);
}

I am getting millions of results, so obviously I'm doing something wrong here, but I'll be darned if I can figure out what that is. The structure you suggested makes perfect sense to me.

[Weedpacket]

Well, you're going to have to get the hang of them or it will be like never being able to count past one 🙂

$results1 = array();
$results1[] = $row_newitems;
sort($results1);
foreach ($results1 as $key1 => $val1)

These lines don't really make much sense:

1. Create an empty array ($results1)

2. Add one element to it ($row_newitems)

3. Sort all $results1's elements (all one of them)

4. Start a loop........

What you want (using the database) is something more like

$query_newitems = "SELECT *, (select max(PubDate) from PubData where PubID=Items.ID group by PubID) as TheDate FROM Items WHERE Items.Publish='Yes' ORDER BY TheDate DESC LIMIT 0,4";
$newitems = mysql_query($query_newitems, $CR) or die(mysql_error());
$pubdata_results = array();
while($row = mymysql_fetch_assoc($newitems))
{
    $pubdata_results[] = $row;
}

[bronzino]

Boy, I really appreciate the fact that you're continuing to stick with me. Also lol'ed re: sorting one element. Sad, sad, sad.

Above the header, I have this:

// HOME PAGE NEW ITEMS 
$query_newitems = "SELECT *, (select max(PubDate) from PubData where PubID=Items.ID group by PubID) as TheDate FROM Items WHERE Items.Publish='Yes' ORDER BY TheDate DESC LIMIT 0,4";
$newitems = mysql_query($query_newitems, $CR) or die(mysql_error());
$pubdata_results = array();
while($rowP = mysql_fetch_assoc($newitems))
{
    $pubdata_results[] = $rowP;
} 

// HOME PAGE NEW SHOW RESULTS 
$query_newresults = "SELECT * FROM Show_Records, Items WHERE Show_Records.Publish = 'Yes' and Items.Publish = 'Yes' ORDER BY Show_Records.`Date` DESC LIMIT 0, 2";
$newresults = mysql_query($query_newresults, $CR) or die(mysql_error());
$showrecords_results = array();
while($rowS = mysql_fetch_assoc($newresults))
{
    $showrecords_results[] = $rowS;
} 

And then in the body of the page I did your interleaved_results array combo:

$interleaved_results = array();
while(!empty($pubdata_results) && !empty($showrecords_results));
{
    $interleaved_results[] = array_shift($pubdata_results);
    $interleaved_results[] = array_shift($showrecords_results);
     print_r($interleaved_results);
} 

And I'm still getting millions of results. Alas.

[Weedpacket]

I'm guessing your millions of results look like

Table 1 Item 1
Table 2 Item 1
Table 1 Item 1
Table 2 Item 1
Table 1 Item 2
Table 2 Item 2
Table 1 Item 1
Table 2 Item 1
Table 1 Item 2
Table 2 Item 2
Table 1 Item 3
Table 2 Item 3
Table 1 Item 1
Table 2 Item 1
Table 1 Item 2
Table 2 Item 2
Table 1 Item 3
Table 2 Item 3
Table 1 Item 4
Table 2 Item 4

The problem is that

     print_r($interleaved_results); 

is inside the loop, so every time you add two elements to the array, you redisplay the entire array. Try moving it to just after the loop.

[bronzino]

Thank you again for continuing to try to help. I appreciate it! 🙂

Did that, same result - although I may have been mistaken about what the "result" was, originally. When I use the "print_r" command with either "interleaved_results," "pubdata_results," or "showrecords_results," I get over a minute of "waiting for localhost" from my browser. This is still occurring even when I place "print_r" outside of any loop. Generally that "waiting for localhost" displaying for any more than a few seconds means that the PC is running through a loop a million times and not going to resolve, so I've been stopping it.

This time, I let "print_r" run its course and tried it with each of the three arrays separately. What I got in each instance was a page that terminates right before the "interleaved_results" array is defined (e.g., if placed after the "body" tag, we get as far as the "body" tag and the remainder of the page is empty). Any guesses on why the heck this is happening?

I'm so excited to get this going that I will keep trying as long as you have time to keep trying to help. .

[Weedpacket]

Sounds like something else is going on somewhere. I'll need to see more of your page.

[bronzino]

Sure. Not sure about which BB tags to use, going with PHP since that's the bulk of it.

<?php require_once('../../Connections/CR.php');

// ESCAPE STRINGS
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    

    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

// DATABASE SELECTION
mysql_select_db($database_CR, $CR);


// HOME PAGE NEW ITEMS
$query_newitems = "SELECT Color, (select max(PubDate) from PubData where PubID=Items.ID group by PubID) as TheDate FROM Items WHERE Items.Publish='Yes' ORDER BY TheDate DESC LIMIT 0,2";
$newitems = mysql_query($query_newitems, $CR) or die(mysql_error());
$pubdata_results = array();
while($rowP = mysql_fetch_assoc($newitems))
{
    $pubdata_results[] = $rowP;
}

// HOME PAGE NEW SHOW RESULTS
$query_newresults = "SELECT `Show Name` FROM Show_Records, Items WHERE Show_Records.Publish = 'Yes' and Items.Publish = 'Yes' ORDER BY Show_Records.`Date` DESC LIMIT 0, 2";
$newresults = mysql_query($query_newresults, $CR) or die(mysql_error());
$showrecords_results = array();
while($rowS = mysql_fetch_assoc($newresults))
{
    $showrecords_results[] = $rowS;
} 

 ?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
</head>

<body>

<?php

$interleaved_results = array();
while(!empty($pubdata_results) && !empty($showrecords_results));
{
    $interleaved_results[] = array_shift($pubdata_results);
    $interleaved_results[] = array_shift($showrecords_results);
} 

print_r($interleaved_results);
?>

</body>
</html>

I have numerous pages online and functional using this same database, so I'm sure it's not a connection issue or anything of that nature.

[Weedpacket]

Hmmm.... I'm not seeing the problem there 🙁. You're not getting any error messages?

bronzino wrote:

Generally that "waiting for localhost" displaying for any more than a few seconds means that the PC is running through a loop a million times and not going to resolve, so I've been stopping it.

By default PHP would typically do that itself after thirty seconds.

At this point I'd probably start doing other tests, just to see what happens, like:

$interleaved_results = array();
/*
while(!empty($pubdata_results) && !empty($showrecords_results));
{
    $interleaved_results[] = array_shift($pubdata_results);
    $interleaved_results[] = array_shift($showrecords_results);
}
*/
print_r($interleaved_results); 

echo count($pubdata_results);
echo count($showrecords_results);
// Should both be no more than 2
// Incidentally, since you're using "items" and "results" elsewhere to refer
// to the two sets of data, you might as well rename these to be consistent.

/*
$interleaved_results = array();

while(!empty($pubdata_results) && !empty($showrecords_results));
{
    $interleaved_results[] = array_shift($pubdata_results);
    $interleaved_results[] = array_shift($showrecords_results);
}
print_r($interleaved_results); 
*/

$interleaved_results = array();

while(!empty($pubdata_results) && !empty($showrecords_results));
{
    $interleaved_results[] = array_shift($pubdata_results);
    $interleaved_results[] = array_shift($showrecords_results);
}

echo count($interleaved_results); // Should be no more than 4