Custom database query

[viorelepuran]

I have created a query that retrieves 5 groups under the caegories ID 6649950,6649951,6649952,6649953

Right now, i have managed to display 5 groups from those categories only, and echo their permalink and title.

But now i need to sort the query to make it display 5 groups under those specific categories, and order them by the number of discussions.

Please let me know what i should add in the $sql
Here is my current query:

$link = mysqli_connect("localhost", "xxxx", "xxxx", "xxxxx");

// Check connection
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}

$sql = "SELECT n.*,nn.urlident purl FROM `node` n LEFT JOIN `node` nn ON (nn.nodeid = n.parentid) WHERE n.parentid IN (6649950,6649951,6649952,6649953) LIMIT 5";
if($result = mysqli_query($link, $sql)){
if(mysqli_num_rows($result) > 0){

while($row = mysqli_fetch_array($result)){
echo "<a href=WWW.SITE-URL.COM/social-groups/".$row['purl']."/" . $row["urlident"]. "' class='result_link'>";
echo $row['title'];
echo "</a>";
}
// Free result set
mysqli_free_result($result);
} else{
echo "No records matching your query were found.";
}
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}

mysqli_close($link);  

[delicjous]

Depends on what you want to order by. threads or posts:

posts:

SELECT n.*,nn.urlident purl FROM `node` n LEFT JOIN `node` nn ON (nn.nodeid = n.parentid) WHERE n.parentid IN (6649950,6649951,6649952,6649953) ORDER BY nn.textcount desc LIMIT 5

threads

SELECT n.*,nn.urlident purl FROM `node` n LEFT JOIN `node` nn ON (nn.nodeid = n.parentid) WHERE n.parentid IN (6649950,6649951,6649952,6649953) ORDER BY nn.totalcount desc LIMIT 5

Did not try but this should work.

BTW: You should use nn.urlident as purl

[viorelepuran]

Thank you verry much delicjous . I did not saw that column in the database. I applied your code, and it works flawless