CodingForums.com - Random function limited to the first 10 ID's of db, must take 10 random from entireDB

CodingForums.com (http://www.codingforums.com/index.php)

- MySQL (http://www.codingforums.com/forumdisplay.php?f=7)

- - Random function limited to the first 10 ID's of db, must take 10 random from entireDB (http://www.codingforums.com/showthread.php?t=285842)

Random function limited to the first 10 ID's of db, must take 10 random from entireDB

Hello, Coding Forums, i have problem with option in site.
User goes to the "10 Random Jokes" in the site. In the db there may be tens of thousands of Joke ID's, the user is seeing only the first 10 joke ID's cuz of the desc function of the script and they are mixed, but only the first ten. And if the user refreshes the page, he will see the first 10 jokes from DB again, only by different order again. What needs to happen is when the user goes to the "10 random jokes" he must see 10 random jokes from those tens of thousands Joke ID's and when refreshing the page again 10 random jokes from thousands, not the first 10 jokes only mixed differently, i beleave the function and the module is all here, but can anyone do this ?

Code:

<?

include ("config_file.php");

include(DIR_LNG.'top_random_jokes.php');

$display_nr = $display_nr_top_random_joke;

$type = TEXT_RANDOM_JOKES_ON." ";

$database_table_name1 = $bx_db_table_joke_categories;  

$database_table_name2 = $bx_db_table_jokes;            

$jtype = "random";



isset($HTTP_POST_VARS['cat_id']) ? $cat_id = $HTTP_POST_VARS['cat_id'] : (isset($HTTP_GET_VARS['cat_id']) ? $cat_id = $HTTP_GET_VARS['cat_id'] : "");



if(isset($cat_id) && $cat_id != '0')

        $condition = " where category_id='".$cat_id."' and validate='1' and slng='".$slng."' ORDER BY emailed_value desc, rating_value DESC limit 0, $display_nr";

else

        $condition = " where validate='1' and slng='".$slng."' ORDER BY emailed_value desc, rating_value DESC limit 0, $display_nr";



$SQL = "select * from $database_table_name2 ".$condition;



$result_array = generate_random_array($SQL, $display_nr, $mode);



$show_joke_categories="yes";

include (DIR_SERVER_ROOT."header.php");

include (DIR_FORMS."jokes_category_with_jokes_form.php");

include (DIR_SERVER_ROOT."footer.php");

?>

Code:

/**************************************************************

Random array for any $SQL, it'll return result array compatible step function

**************************************************************/

function generate_random_array($SQL, $nr_random=10 , &$mode)

{

        $random_row = 0;

        $array = null;

        $sel = bx_db_query($SQL); 

        SQL_CHECK(0,"SQL Error at ".__FILE__.":".(__LINE__-1));



        $count = bx_db_num_rows($sel);



        srand((double)microtime()*1000000); 

        if ($count!=0)

        {

                if ($count >= $nr_random)

                        $nr_random = $nr_random;                

                else

                        $nr_random = $count;

                

                for($i = 0; $i < $nr_random; $i++)

                {

                        $random_row = @rand(0, ($count - 1));

                        $exist = random_once($array, $random_row);

                        if($exist != 1)

                                $array[$i] = $random_row;

                        else

                                $i--;

                }

        }

        $i = $j = 0;   



        for ($i = 0; $i < sizeof($array) ; $i++)

        {

                $record=bx_db_data_seek($sel, $array[$i]);

                $result_ads=bx_db_fetch_array($sel);

                $result_array[$i] = $result_ads;

        }

        

        $mode = "random";

        return $result_array;

}





function string_break($text, $length, $symbol = "...")

{

        $length_text = strlen($text);

        $length_symbol = strlen($symbol);

        if ($length_text <= $length || $length_text <= $length_symbol || $length <= $length_symbol)

        {

                return($text);

        }

        else

        {

                if ((strrpos(substr($text, 0, $length - $length_symbol)," ") > strrpos(substr($text, 0, $length - $length_symbol),".")+25) && (strrpos(substr($text, 0, $length - $length_symbol)," ") < strrpos(substr($text, 0, $length - $length_symbol),",")+25)) {

                        return(substr($text, 0, strrpos(substr($text, 0, $length - $length_symbol)," ")). $symbol);

                }

                else if (strrpos(substr($text, 0, $length - $length_symbol)," ") < strrpos(substr($text, 0, $length - $length_symbol),".")+25) {

                        return(substr($text, 0, strrpos(substr($text, 0, $length - $length_symbol),".")). $symbol);

                }

                else if (strrpos(substr($text, 0, $length - $length_symbol)," ") < strrpos(substr($text, 0, $length - $length_symbol),",")+25) {

                        return(substr($text, 0, strrpos(substr($text, 0, $length - $length_symbol),".")). $symbol);

                }

                else{

                        return(substr($text, 0, strrpos(substr($text, 0, $length - $length_symbol)," ")). $symbol);

                }

        }

}

Ummm...well yeah, that's what your query asked for.

Code:

ORDER BY emailed_value desc, rating_value DESC limit 0, $display_nr";

Apparently, you want the more "important" jokes (by emailed_value, whatever that is, and then by rating_value). If you truly want random jokes, than you need to abandon the use of those "importance" qualifiers.

So just change it to

Code:

ORDER BY RAND() LIMIT 10

And then you will not need or want that ugly generate_random_array( ) function. The records *WILL* be in random order, already.

By the by, you could write the condition stuff much simpler:

Code:

$condition = " WHERE validate = 1 AND slng = " $slng ; // assuming slng is a number

if(isset($cat_id) && $cat_id != 0) 

{

    $condition .= " AND category_id=" . $cat_id ;

}

$SQL = "select * from $database_table_name2 " . $condition . " ORDER BY rand() LIMIT 10";

Though you should be sanitizing that $cat_id to make sure it really is a number, to protect against SQL injection.

Well no, see there are many options in the site and all working as far as i know and maybe some using the same function, but the "10 random jokes" option is not working. Here are all options:

- Top emaild jokes - the joke that has been email to friend the most
- Top jokes by vote - when the people vote for the joke, takes the rating and displays the highest rated joke at the top
- 10 random jokes - if the db has 5 000 jokes, the option must take 10 random jokes from those 5 000 jokes everytime, problem is that it's not taking 10 out of 5000 it's taking 10 from the first 10 out of 5000 .. so if the db has 5 000 jokes 4 990 jokes can never be showed, because it's taking random 10 jokes from the first 10

Ummm...NEITHER is your "Top jokes by vote" working, if the code you are showing there is all you have.

Your SQL query *ALWAYS* does

Code:

ORDER BY emailed_value desc, rating_value DESC

so the emailed_value is the first seen and will override the rating_value.

I think you will have to show your HTML page where the user requests one of those 3 selections. You are clearly not interacting between the PHP code and the HTML request from the user.

If you can show a live URL, that woudl be easiest.

PLEASE don't tell me that you have SEPARATE PHP pages for those 3 categories!

Why would you do that instead of using one page and conditional SQL?

Well, here is the link to the site, it's not english, but i have changed the menu to english so that you can know what you are seeing.
=== Link Hiden ===

Well, I can *almost* read the site (I read Russian, so it's familiar looking).

Okay, so the "trick" to the site is your jtype=xxx query string. As in:

Code:

http://www.djinchebg.eu/top_emailed_jokes.php?jtype=emailed

or

http://www.djinchebg.eu/top_emailed_jokes.php?cat_id=8&jtype=emailed

But nowhere in the code you showed in your first post are you doing anything like

Code:

$joketype = $_GET["jtype"];

Also, I was right.

You get the *SAME RESULTS* for

Code:

http://www.djinchebg.eu/top_ten_jokes.php?cat_id=8&jtype=ten

and

http://www.djinchebg.eu/top_ten_jokes.php?cat_id=8&jtype=emailed

!!! The jtype is *IGNORED*

Back in a while with all new code.

Okay...I do *NOT* code in PHP. So don't be surprised if I make some PHP typos in this.

But here's roughly how I would do it:

Code:

<?

include ("config_file.php");

include(DIR_LNG.'top_random_jokes.php');

$type = TEXT_RANDOM_JOKES_ON." ";  // no idea what this is for

           

$jtype = $_GET["jtype"];

if ( ! isset($jtype) ) { $jtype = "random"; }

if ( $jtype == "random" ) { $ordering = " RAND( )"; }

else if ( $jtype == "ten" ) { $ordering = " rating_value DESC"; }

else { $ordering = " emailed_value DESC"; }



$cat_id = $_REQUEST["cat_id"]; // why would this ever NOT be $_GET??  oh, well



$condition = " WHERE validate = 1 AND slng = '$slng' "; // where does $slng come from???

if(isset($cat_id) && 1 * $cat_id != 0) 

{

    $condition .= " AND category_id=" . (1 * $cat_id) ;

}

// You should NEVER use SELECT * but I have to use it here 

// because you didn't show what the fields in your DB table are

$SQL = "SELECT * FROM `$bx_db_table_jokes` " . $condition . " ORDER BY $ordering LIMIT 10";



$result = mysql_query($SQL) or die( mysql_error() );

while ( $row = mysql_fetch_array($result) )

{

    ... show one joke ...

}

Well, as far as you can, thank you for the time spend