Problem with RAND() 1

[leeboycymru]

This is the site:

http://www.checksafetyfirst.com/en/...ectRegion=0&ratings=0&hotname=&absolutepage=1

Everything is working properly, but my boss asked if I could make all these hotels display randomly - so i said no problem and changed this:

$buildquery .= " order by t1.Rate_Num_Hot DESC";

to this:

$buildquery .= " order by RAND()

He was happy, so it was left, then we started having complaints about in some cases you can have the same hotels on the second page as teh first page, so basically it was calling a random selection each time.

So is it possible to say display randomly but once the array has been formed in this case 494 hotels, the hotels stay put on the their pages, and the random display only does it once to display all 494 hotels, so when you click through and back the same hotels are displayed.

This this is going to be a tuffy for me.

cheers

Lee

 

[feherke]

Hi

No, not without a temporary storage. While you already use sessions, I suggest to first get an array of random Id_Hot or whatever the unique identifier is, then you page over that list and get the related information from the database.

If you have no gaps, you can create the random array in PHP, without MySQL query. But probably you have or will have gaps, so you can not simplify it with this.

Feherke.

http://rootshell.be/~feherke/

 

[leeboycymru]

Hi feherke,

Good to speak to you again. I spoke with my boss and said something like you said to him, and it made sense to pull it all down first then page over.

Is it difficult to do this feherke?

Or are you saying its right in thought, but not doable in practice

 

[feherke]

Hi

Is doable and should not be difficult. But this depends on the code which does the paging.

Feherke.

http://rootshell.be/~feherke/

 

[leeboycymru]

This is the buildquery building up to it.

if($hlist==3){
$buildquery.=" select distinct(t1.Nom_Hot ), t1.Id_Hot hotel_id , t1.Nom_Hot hotel_name , t2.Nom_Cat star_rating , t3.Nom_Cntry country, t1.Foto1_Hot foto1 ";

$buildquery.=" ,t1.Foto2_Hot foto2, t1.Foto3_Hot foto3, t1.Foto4_Hot foto4 ";

if($selectRegion>0){ $buildquery.=" , t4.Nom_Rsrt region ";	}						$buildquery.=" from tbl_hotels t1, tbl_categories t2, tbl_countries t3  ";

if($selectRegion>0){ $buildquery.=" , tbl_resorts t4 ";	}
$buildquery.=" where ";$buildquery.= "
t1.IdCat_Hot=t2.Id_Cat ";
$buildquery.= " and t1.IdType_Hot=2 ";

if($ratings>0){ $buildquery.=" and t2.Id_Cat=$ratings ";}

if($selectCountry>0){ 	$buildquery.=" and t1.IdCntry_Hot=$selectCountry ";	}
$buildquery .=" and t1.IdCntry_Hot=t3.Id_Cntry  ";

if($selectRegion>0){  $buildquery.=" and t1.IdRsrt_Hot=$selectRegion and t1.IdRsrt_Hot=t4.Id_Rsrt ";	}

if(strlen(trim($hotname))>0){ $buildquery.="  and t1.Nom_Hot like '%$hotname%' "; 	}
$buildquery.=" AND t1.Act_Hot=1";

/*$buildquery.="  FROM t1,t2,t3 where t1.IdType_Hot=1 ";*/
/*Original static desc order - */$buildquery .= " order by t1.Rate_Num_Hot DESC";
/*New Random order display - $buildquery .= " order by RAND()"; End Random */
}

 

[leeboycymru]

and this does the paging:

<?php
if(!$mRecordCount == 0) {?>
<div align="left"><span class="tahoma11bBold"> 
&nbsp;&nbsp;&nbsp;<?=PagNav($mPageSize, $mPageCount, $mAbsolutePage, $mRecordCount, $mQry, $mUrl) ;?>
</div>
<div align="right">Records 
<?=$mPag1?>
to 
<?=$mPag2?>
(of 
<?=$mRecordCount?>)</div>
<?php } ?>
<? 
if($records>0)
{ 
$cnt=$records+(3-$records%5);
/*Original - $cnt=$records+(3-$records%3);*/
/*echo $records;*/
/*echo $cnt;*/
/*echo $mRecordCount;*/
$i=1;
for($i=1;$i<$cnt;$i++) { ?>
<? 
if(($i%3)==0 || $i==1 ){ ?>
                                             
<? for($j=1;$j<=3;$j++) { 
							$morevalue=true;
							$rows=mysql_fetch_assoc($result) or $morevalue=false;
?>

 

[jpadie]

the way I deal with removing repetitions is to store the displayed hotels in a session variable.

then when each page is loaded, i either exclude the hotels in the session variable (within sql) or just skip the row if I am loading the whole recordset.

then as each new and "clean" hotel is loaded, i reset the session var.

it ends up, usually, with only a few lines of extra code

 

[leeboycymru]

Hi jpadie, what i need to say is that this isnt my code. I came to this business as a web designer with good asp knowledge, but over time I have been quite good at RE-engineering php and mysql code, so basically in terms of writing clean code above, im going to have to take them up on courses they have offered me.

So what I'm saying is that I need a bit of hand holding when it comes to stuff like you say above.

cheers

Lee

 

[jpadie]

Lee

I posted code that was quite a bit cleaner that the above in a recent post. Have you not had any success with it? It would be easier to work with clean code.

I am assuming that you do not want randomisation for each page click. this would make paging a nonsense.

So I assume that you want the recordset randomised once per session. This has and impact on dataset "freshness" as a database updates/edits might have been made during a session. if your dataset is relatively static, this is not so much of a problem.

the process is as follows (as feherke implies):

build a query with all the where conditions you require.
save the where conditions in a session variable
for the first query just return the id's and load them into an array in a session variable
then replace your paging logic based on the array rather than a limit/offset the where clause will create the limit inherently.

some basic code to get you started is as follows. I've not checked it for parse errors

<?
if (session_name() == ''){
	session_start();
}

$numHpP = 3; //number of hotels per page
$page = isset($_GET['page']) ? trim ($_GET['page']) : 1;


function displayHotels($page){
	if (!isset($_SESSION['hotelIDs'])){
		loadHotelID();
	}
	$cnt = 0;
	$i = ($page-1) * $numHpP;
	while ($cnt < $numHpP){
		$hotelIDs[] = $_SESSION['hotelIDs'][$i+$cnt];
		$cnt++;
	}
	
	$where = $_SESSION['where'] ' AND hotel_id IN ('.implode(',', $hotelIDs).')';
	
	//run your query with the new $where clause 
}

function constructWhereClause(){
	$where = '';
	//add in the conditional provisions
	
	if (empty($where)){
		$_SESSION['where'] = $where;
	} else {
		$_SESSION['where'] = " WHERE $where";
	}
}

function loadHotelID(){
	if (!isset($_SESSION['where'])){
		constructWhereClase();
	}
	$sql = "select hotel_id from hoteltable $_SESSION[where] ORDER BY rand()";
	$result = mysql_query($sql) or die (mysql_error());
	while ($row = mysql_fetch_assoc($result)){
	  $_SESSION['hotelIDs'][]=$row['hotel_id'];
	}
}

 

[leeboycymru]

Hi jpadie, my theory was the get everything working on each page and then go back to the help I have had to clean the code up.

I have printed it all off, and will get around to it once the boss is happy that it works for starters.

I dont get a lot of free time which is a shame to go back on things to clean it up, so what I planned was to get it finished then without them knowing go back and for my own pride and advancement, start looking back through the messages.

Please do not think that I have ignored all your help.

I will go back now and look at your post, before getting back to you. I just wanted to let you know this about your previous posts.

cheers

Lee

 

[jpadie]

hold on ! i'm not offended or anything, I was justr trying to point out that you will find the approach I outlined a lot easier to implement if you have a cleaner method of building your query.

 

[leeboycymru]

Good, im glad you weren't offended. I do appreciate everybodys help here, just that I'm basically a web designer that can re-engineer code to make it work differently, i do get lost a bit with trying to write new php.

I understand your last post in that you build up the query then save it to a session variable, but looking at your code i am confused how to use it and where to start it off.

cheers again

lee

 

[jpadie]

Lee

try this code

<?php
if (session_id() === ''){
	session_start();
}
?>
<head>
<style type="text/css">
.pages {width: 200px; border: 1px red solid; padding-bottom:20px;}

.page {border:1px black dotted; height: 20px; width: 15px; background-color:#CCFF99;  text-align:center; margin: 2px;}
.curDay {background-color:#CC3300;}
a {text-decoration:none; color:#0000FF;}
a:visited {text-decoration:none; color:#0000FF;}

</style>
<?
$host = "localhost";
$user = "root";
$pass = "root";
error_reporting(E_ALL);
mysql_connect($host, $user, $pass);



if (isset($_GET['install'])){
	mysql_query("create database if not exists TEKTIPSTEST") or die (mysql_error());
	mysql_select_db('tektipstest') or die (mysql_error());
	$tabledef = "
			CREATE TABLE  IF NOT EXISTS `tektipshotels` (
 			`hotelID` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
 			`hotelName` VARCHAR( 255 ) NOT NULL
			) TYPE = MYISAM";
	mysql_query($tabledef) or die (mysql_error()); //create table
	
	//dummy some data
	$iQ = '';
	if (mysql_result(mysql_query("select count(*) from tektipshotels"),0,0) == 0){
		$insertQuery = "Insert into tektipshotels (hotelID, hotelName) values "; 
		for($i=0; $i<20; $i++){
			$iQ []= "(null, 'hotel Number $i')";
		}
	
		$insertQuery .= implode (',', $iQ); //get rid of the trailing comma
		//insert the data
		mysql_query($insertQuery) or die(mysql_error());
	}
} else {
	mysql_select_db('tektipstest') or die (mysql_error());
}
if (isset($_GET['refresh'])){
	unset($_SESSION['hotelIDs']);
}

if (isset($_GET['DEBUG'])){
	$get = print_r($_GET, true);
	$post = print_r($_POST, true);
	$session = print_r($_SESSION, true);
	echo <<<HTML
<hr/>
Get variables
$get
<hr/>
Post variables
$post
<hr/>
Session variables
$session
<hr/>
</pre>
HTML;
}


$numHpP = 3; //number of hotels per page
$page = isset($_GET['page']) ? trim ($_GET['page']) : 1;


//display the hotels
displayHotels($page);

function displayHotels($page){
	$hotelIDs = array();
	global $numHpP;
	if (!isset($_SESSION['hotelIDs'])){
		loadHotelID();
	}
	$cnt = 0;
	$i = ($page-1) * $numHpP;
	while ($cnt < $numHpP){
		if (isset($_SESSION['hotelIDs'][$i+$cnt])){
			$hotelIDs[] = $_SESSION['hotelIDs'][$i+$cnt];
		}
		$cnt++;
	}
	
	if (!empty($_SESSION['where'])){
		$where = " WHERE (".$_SESSION['where'].") AND hotelID IN (".implode(',', $hotelIDs). ")";
	} else {
		$where = " WHERE hotelID IN (".implode(',', $hotelIDs). ")";
	}

	$query = "Select hotelID, hotelName from tektipshotels $where";
	
	$result = mysql_query($query) or die(mysql_error());
	echo "Currently selected hotels: <br/>";
	while ($row = mysql_fetch_assoc($result)){
		echo $row['hotelName'] . "<br/>";	
	}
	
	//do some paging
	$numRows = count ($_SESSION['hotelIDs']);
	$numPages = ceil($numRows/$numHpP);
	$pages='';
	$pref = isset($_GET['DEBUG']) ? '&DEBUG' : '';
	for ($p=1; $p<=$numPages; $p++){
		$class = ($p == $page) ? "page curDay" : "page";
		$pages .= "<span class=\"$class\"><a href=\"$_SERVER[PHP_SELF]?page=$p$pref\">$p</a></span>";
	}
	echo <<<HTML
<div class="pages">
	Pages
	<div class="pageNumbers">
		$pages
	</div>
</div>
HTML;
}

function constructWhereClause(){
	$where = '';
	//add in the conditional provisions
	
	if (empty($where)){
		$_SESSION['where'] = $where;
	} else {
		$_SESSION['where'] = " WHERE $where";
	}
}

function loadHotelID(){
	if (!isset($_SESSION['where'])){
		constructWhereClause();
	}
	$sql = "select hotelID from tektipshotels $_SESSION[where] ORDER BY rand()";
	$result = mysql_query($sql) or die (mysql_error());
	while ($row = mysql_fetch_assoc($result)){
	  $_SESSION['hotelIDs'][]=$row['hotelID'];
	}
}

echo "<br/>click <a href=\"$_SERVER[PHP_SELF]?install\">here</a> to install the dummy database";
echo "<br/>click <a href=\"$_SERVER[PHP_SELF]?refresh\">here</a> to regenerate the random list";

change the db parameters to your own.

to install the database and table click the link at the bottom
to debug (see the variables) add the query parameter DEBUG ie

localhost/somepage.php?DEBUG

hopefully this should be enough to get you going

 

[leeboycymru]

WOW, blimey thanks jpadie.

I will print that off now and give it a go.

thanks so much

lee