Using the Rand() function

Post here if you need help with SQL.
Post Reply
sycodelic
Posts: 5
Joined: Thu May 03, 2012 8:28 pm

Using the Rand() function

Post by sycodelic »

Hi,

I am slowly learning a lot from the tutorials here about php/sql. I am listing certain columns from my database in order, one of which I require to be limited and taking random rows.

I have tried for the last couple of days and so far, I seem to only be able to get them all random or just a blank page. The following code is what I presume should work, but it does not, can anyone enlighten me? A.feat is which I require random.
$query2 = "SELECT A.*, B.seo, C.username, C.country, C.toprated, C.level from posts A, categories B, members C where A.active='1' AND A.category=B.CATID AND A.USERID=C.USERID $addsqlb $scriptolution_addprice order by A.feat rand() limit 3, $dby limit $pagingstart, $config[items_per_page]";
Thanks in advance.
User avatar
jacek
Site Admin
Posts: 3262
Joined: Thu May 05, 2011 1:45 pm
Location: UK
Contact:

Re: Using the Rand() function

Post by jacek »

It looks like is should work, it might be a PHP problem rather than one with the SQL so can you post the full code for the page and of any functions that are used on the page ?

Also it would be worth adding
echo mysql_error();
after the query to see if that shows up any problems.
Image
sycodelic
Posts: 5
Joined: Thu May 03, 2012 8:28 pm

Re: Using the Rand() function

Post by sycodelic »

Hi, I have added the whole index page. If I add the code you gave, I still see no errors. Just a blank page.
include("include/config.php");
include("include/functions/import.php");
$thebaseurl = $config['baseurl'];

$s = cleanit($_REQUEST['s']);
STemplate::assign('s',$s);

$page = intval($_REQUEST['page']);

if($page=="")
{
	$page = "1";
}
$currentpage = $page;

if ($page >=2)
{
	$pagingstart = ($page-1)*$config['items_per_page'];
}
else
{
	$pagingstart = "0";
}

if($s == "r")
{
	$dby = "A.rating desc";	
}
elseif($s == "rz")
{
	$dby = "A.rating asc";	
}
elseif($s == "p")
{
	$dby = "A.viewcount desc";	
}
elseif($s == "pz")
{
	$dby = "A.viewcount asc";	
}
elseif($s == "c")
{
	$dby = "A.price asc";	
}
elseif($s == "cz")
{
	$dby = "A.price desc";	
}
elseif($s == "dz")
{
	$dby = "A.PID asc";	
}
else
{
	$dby = "A.PID desc";	
}

if($s == "ez")
{
	$dby = "A.PID asc";	
	$addsql = "AND days='1'";
	$addsqlb = "AND A.days='1'";
}
elseif($s == "e")
{
	$dby = "A.PID desc";	
	$addsql = "AND days='1'";
	$addsqlb = "AND A.days='1'";
}

if($s == "iz")
{
	$dby = "A.PID asc";	
	$addsql = "AND days='0'";
	$addsqlb = "AND A.days='0'";
}
elseif($s == "i")
{
	$dby = "A.PID desc";	
	$addsql = "AND days='0'";
	$addsqlb = "AND A.days='0'";
}
if($s == "vz")
{
	$dby = "A.youtube asc";
	$addsql = "AND youtube<>''";
	$addsqlb = "AND A.youtube<>''";
}
elseif($s == "v")
{
	$dby = "A.youtube desc";
	$addsql = "AND youtube<>''";
	$addsqlb = "AND A.youtube<>''";	
}
if($s == "sz")
{
	$dby = "A.scriptolutionship1 asc";
	$addsql = "AND scriptolutionship1<>''";
	$addsqlb = "AND A.scriptolutionship1<>''";
}
elseif($s == "s")
{
	$dby = "A.scriptolutionship1 desc";
	$addsql = "AND scriptolutionship1<>''";
	$addsqlb = "AND A.scriptolutionship1<>''";	
}
$p = intval(cleanit($_REQUEST['p']));
if($p > 0)
{
	$scriptolution_addprice = " AND A.price='".mysql_real_escape_string($p)."'";
	$scriptolution_addpriced = " AND price='".mysql_real_escape_string($p)."'";
	STemplate::assign('p',$p);
	$addp = "&p=$p";
}


$query1 = "SELECT count(*) as total from posts where active='1' $addsql $scriptolution_addpriced order by PID desc limit $config[maximum_results]";

$query2 = "SELECT A.*, B.seo, C.username, C.country, C.toprated, C.level from posts A, categories B, members C where A.active='1' AND A.category=B.CATID AND A.USERID=C.USERID $addsqlb $scriptolution_addprice order by A.feat rand() limit 3, $dby limit $pagingstart, $config[items_per_page]";
$executequery1 = $conn->Execute($query1);
$scriptolution = $executequery1->fields['total'];


			
if ($scriptolution > 0)
{
	if($executequery1->fields['total']<=$config[maximum_results])
	{
		$total = $executequery1->fields['total'];
	}
	else
	{
		$total = $config[maximum_results];
	}
	$toppage = ceil($total/$config[items_per_page]);
	if($toppage==0)
	{
		$xpage=$toppage+1;
	}
	else
	{
		$xpage = $toppage;
	}
	$executequery2 = $conn->Execute($query2);
	$posts = $executequery2->getrows();
	$beginning=$pagingstart+1;
	$ending=$pagingstart+$executequery2->recordcount();
	$pagelinks="";
	$k=1;
	$theprevpage=$currentpage-1;
	$thenextpage=$currentpage+1;
	if($s != "")
	{
		$adds = "&s=$s".$addp;
	}
	if ($currentpage > 0)
	{
		if($currentpage > 1) 
		{
			$pagelinks.="<li class='prev'><a href='$thebaseurl/?page=$theprevpage$adds'>$theprevpage</a></li> ";
		}
		else
		{
			$pagelinks.="<li><span class='prev'>previous page</span></li> ";
		}
		$counter=0;
		$lowercount = $currentpage-5;
		if ($lowercount <= 0) $lowercount = 1;
		while ($lowercount < $currentpage)
		{
			$pagelinks.="<li><a href='$thebaseurl/?page=$lowercount$adds'>$lowercount</a></li> ";
			$lowercount++;
			$counter++;
		}
		$pagelinks.="<li><span class='active'>$currentpage</span></li> ";
		$uppercounter = $currentpage+1;
		while (($uppercounter < $currentpage+10-$counter) && ($uppercounter<=$toppage))
		{
			$pagelinks.="<li><a href='$thebaseurl/?page=$uppercounter$adds'>$uppercounter</a></li> ";
			$uppercounter++;
		}
		if($currentpage < $toppage) 
		{
			$pagelinks.="<li class='next'><a href='$thebaseurl/?page=$thenextpage$adds'>$thenextpage</a></li>";
		}
		else
		{
			$pagelinks.="<li><span class='next'>next page</span></li>";
		}
	}
}
if($_POST['sugsub'] == "1")
{
	if(intval($_SESSION['USERID']) > "0")
	{
		$sugcontent = cleanit($_REQUEST['sugcontent']);	
		$sugcat = intval(cleanit($_REQUEST['sugcat']));
		if($sugcontent != "" && $sugcat != "")
		{
			$approve_suggests = $config['approve_suggests'];
			if($approve_suggests == "1")
			{
				$active = "0";
			}
			else
			{
				$active = "1";
			}
			$query="INSERT INTO wants SET USERID='".mysql_real_escape_string($_SESSION['USERID'])."', want='".mysql_real_escape_string($sugcontent)."', category='".mysql_real_escape_string($sugcat)."', time_added='".time()."', date_added='".date("Y-m-d")."', active='$active'";
			$result=$conn->execute($query);
			$message = $lang['121'];	
		}
	}
}

		
$templateselect = "index.tpl";
//TEMPLATES BEGIN
STemplate::assign('pagetitle',stripslashes($config['site_slogan']));
STemplate::assign('message',$message);
STemplate::assign('beginning',$beginning);
STemplate::assign('ending',$ending);
STemplate::assign('pagelinks',$pagelinks);
STemplate::assign('total',$total);
STemplate::assign('posts',$posts);
STemplate::display('header.tpl');
STemplate::display($templateselect);
STemplate::display('footer.tpl');
//TEMPLATES END
?>
User avatar
jacek
Site Admin
Posts: 3262
Joined: Thu May 05, 2011 1:45 pm
Location: UK
Contact:

Re: Using the Rand() function

Post by jacek »

:shock: okay...

Well where is $conn defined ?

And what is this cleanit() function that is used all over ?
Image
sycodelic
Posts: 5
Joined: Thu May 03, 2012 8:28 pm

Re: Using the Rand() function

Post by sycodelic »

$conn is defined in the config file.
$conn = &ADONewConnection($DBTYPE);
function cleanit($text)
{
	return htmlentities(strip_tags(stripslashes($text)), ENT_COMPAT, "UTF-8");
}
User avatar
jacek
Site Admin
Posts: 3262
Joined: Thu May 05, 2011 1:45 pm
Location: UK
Contact:

Re: Using the Rand() function

Post by jacek »

Right...

Well if you are getting no output at all that probably means there is a syntax error that is being hidden, so make sure you have your error_reporting value set to E_ALL and display_errors set to On. Google can tell you how to do both of those :)
Image
sycodelic
Posts: 5
Joined: Thu May 03, 2012 8:28 pm

Re: Using the Rand() function

Post by sycodelic »

These are the extra error thrown.
Fatal error: Call to a member function getrows() on a non-object in /home/public_html/index.php on line 161
User avatar
jacek
Site Admin
Posts: 3262
Joined: Thu May 05, 2011 1:45 pm
Location: UK
Contact:

Re: Using the Rand() function

Post by jacek »

Ah perfect :D

I'm not sure what your sql object is, but does it have a getrows() method, it could be get_rows() maybe ?
Image
sycodelic
Posts: 5
Joined: Thu May 03, 2012 8:28 pm

Re: Using the Rand() function

Post by sycodelic »

Thanks, I manage to work it out by adding another query and merging them together.
Post Reply