Using the Rand() function

[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.

[syntax=php]$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]";[/syntax]

Thanks in advance.

[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

[syntax=php]echo mysql_error();[/syntax]
after the query to see if that shows up any problems.

[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.

[syntax=php]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>&nbsp;";
}
else
{
$pagelinks.="<li><span class='prev'>previous page</span></li>&nbsp;";
}
$counter=0;
$lowercount = $currentpage-5;
if ($lowercount <= 0) $lowercount = 1;
while ($lowercount < $currentpage)
{
$pagelinks.="<li><a href='$thebaseurl/?page=$lowercount$adds'>$lowercount</a></li>&nbsp;";
$lowercount++;
$counter++;
}
$pagelinks.="<li><span class='active'>$currentpage</span></li>&nbsp;";
$uppercounter = $currentpage+1;
while (($uppercounter < $currentpage+10-$counter) && ($uppercounter<=$toppage))
{
$pagelinks.="<li><a href='$thebaseurl/?page=$uppercounter$adds'>$uppercounter</a></li>&nbsp;";
$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
?>[/syntax]

[jacek]

okay...

Well where is $conn defined ?

And what is this cleanit() function that is used all over ?

[sycodelic]

$conn is defined in the config file.

[syntax=php]$conn = &ADONewConnection($DBTYPE);[/syntax]

[syntax=php]function cleanit($text)
{
return htmlentities(strip_tags(stripslashes($text)), ENT_COMPAT, "UTF-8");
}[/syntax]

[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

[sycodelic]

These are the extra error thrown.

[syntax=php]Fatal error: Call to a member function getrows() on a non-object in /home/public_html/index.php on line 161[/syntax]

[jacek]

Ah perfect

I'm not sure what your sql object is, but does it have a getrows() method, it could be get_rows() maybe ?

[sycodelic]

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