[SOLVED] Array and SELECT query

[Beanbee]

I have a list on my database which I have exploded into an array. The list on is of an undetermined length so it could have 1 value or over 100. I'm trying to use this array to search multiple databases and then order the results by date. Currently, I'm only able to get the results to be ordered first by the order of the array and then by date. Could anyone provide me with some help? Here is my current code.

 

// Grab the Subscription list and create the array.
$slist = mysql_fetch_array(mysql_query("SELECT * FROM user WHERE uid=" .$_SESSION['user']. ""));
$sray=explode(",",$slist['sub']);
// For each array value, search for tables
foreach ($sray as $n)
{
$nlist = mysql_query("SELECT * FROM News".$n." ORDER BY date DESC");
  while($row = mysql_fetch_array($nlist))
{
// Find the poster's identity
$ucheck = mysql_query("SELECT * FROM People WHERE uid=". $row['uid']. "");
while($use = mysql_fetch_array($ucheck))
 {
 // Create the post
  $dcheck = mysql_query("SELECT * FROM Discuss".$n." WHERE nid=". $row['nid'] ."");
	echo "<div id='post'><div id='Ptitle'>". $row['title'] ."<div id='Pdate'>". $row['date'] ."</div></div>
		  <div id='Pinfo'>by: <b><a href='user.php?u=". $row['uid'] ."'>". $use['fname'] ." ". $use['sname'] ."</a></b>&nbsp tags: ". $row['tags'] ."</div>
	      <div id='Ptxt'>". $row['text'] ."</div>
	      <div id='Pdiss'>discuss ( ". mysql_num_rows($dcheck) . " )</div><div id='Pmore'>more</div>
	      <div id='Pline'></div><p><p>
	      </div>";
 }
}
}

P.S. If it would be easier to edit the tables into one table with a field for surname (to search for) let me know

[GKWelding]

The section where you create the post, what you need to do there instead is INSERT the data obtained into a TEMPORARY TABLE (Google it if you don't know how). Then you can query the temporary table after you've inserted all of the records obtained from your array searches and then order them by date... I did something very similar recently. I'll try and find an example of the code I wrote and I'll post it here.

[GKWelding]

Found it...

 

function getEntries(){
//fetch user id of current user
$userId = um()->getCurrentUser()->getUserId();
// fetch entry from database
$subscriptions = edb()->getAll("SELECT * FROM BlogSubscriptions WHERE subscriptionUserId='$userId'");

$tempTable = edb()->execute("CREATE TEMPORARY TABLE `TempEntries` (
  `entryId` int(11),
  `entryBlogId` int(11),
  `entryTitle` varchar(128),
  `entryText` text,
  `entryDate` date);");

foreach($subscriptions as $subscription){
	//get the blog id of the subscription selected
	$blogId = $subscription["subscriptionBlogId"];
	//get the client name of the current user
	$clientName = um()->getCurrentUserForClient()->getClient()->getClientName();
	//find out if the blog is public or private, 0 = public, 1 = private
	$blogCheck = edb()->getOne("SELECT blogPermissions FROM Blog WHERE blogId=$blogId");

	if($blogCheck == 0){
		$entries = edb()->getAll("SELECT * FROM BlogEntry WHERE entryBlogId = $blogId");

		foreach ($entries as $entry){
			//get entry data into a fit state for ADODB
			$record["entryId"] = $entry['entryId'];
			$record["entryBlogId"] = $entry['entryBlogId'];
			$record["entryTitle"] = $entry['entryTitle'];
			$record["entryText"] = $entry['entryText'];
			$record["entryDate"] = $entry['entryDate'];

			$popTemp = edb()->autoExecute("TempEntries", $record, "INSERT"); // insert entry data into temp table
		} //end foreach
	} //end blogcheck if
} //end foreach

//query the temporary table for data
$objects = edb()->getAll("SELECT * FROM TempEntries ORDER BY entryId DESC LIMIT 3");

foreach ($objects as $object){
	//create objects for each entry
	$entrySort[] = new BlogEntry($object);
}

return $entrySort;
}

[Beanbee]

Thankyou very much for your code and guidence.

[Beanbee]

Completed my code, all works as needed. Here it is if you too need help.

 

<?php
// Grab the Subscription list and create the array.
$slist = mysql_fetch_array(mysql_query("SELECT * FROM user WHERE uid=" .$_SESSION['user']. ""));
$sray=explode(",",$slist['sub']);
// Create the temp table.
mysql_query("CREATE TEMPORARY TABLE Temp (
		tid INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
		nid int NOT NULL, 
		uid int NOT NULL, 
		title varchar(100) NOT NULL, 
		sname varchar(50) NOT NULL, 
		date date NOT NULL, 
		text text NOT NULL, 
		tags varchar (100)
		)");

// For each array value, search for tables
foreach ($sray as $n)
{
$nlist = mysql_query("SELECT * FROM News".$n." ORDER BY date DESC");
  while($trow = mysql_fetch_array($nlist))
{
// Correlate with userdb for additional info
$urow = mysql_fetch_array(mysql_query("SELECT * FROM user WHERE uid=".$trow['uid'].""));
// Insert into Temp table
$intemp = "INSERT INTO Temp (nid, uid, title, sname, date, text, tags)
VALUES ('$trow[nid]', '$trow[uid]', '$trow[title]', '$urow[sname]', '$trow[date]', '$trow[text]', '$trow[tags]')";
mysql_query ($intemp);
}
}
// Now create the posts
$nlist2 = mysql_query("SELECT * FROM Temp ORDER BY date DESC");
  while($row = mysql_fetch_array($nlist2))
{
  // Find the poster's identity
$ucheck = mysql_query("SELECT * FROM People WHERE uid=". $row['uid']. "");
while($use = mysql_fetch_array($ucheck))
 {
 // Create a post
  $dcheck = mysql_query("SELECT * FROM Discuss".$row['sname']." WHERE nid=". $row['nid'] ."");
	echo "<div id='post'><div id='Ptitle'>". $row['title'] ."<div id='Pdate'>". $row['date'] ."</div></div>
		  <div id='Pinfo'>by: <b><a href='user.php?u=". $row['uid'] ."'>". $use['fname'] ." ". $use['sname'] ."</a></b>&nbsp tags: ". $row['tags'] ."</div>
	      <div id='Ptxt'>". $row['text'] ."</div>
	      <div id='Pdiss'>discuss ( ". mysql_num_rows($dcheck) . " )</div><div id='Pmore'>more</div>
	      <div id='Pline'></div><p><p>
	      </div>";
 }
}
?>

Thanks again to GKWelding, you placed me in the right direction