Picking a random row in my sql

  • (2 Pages)
  • +
  • 1
  • 2

25 Replies - 1874 Views - Last Post: 20 March 2010 - 07:28 AM Rate Topic: -----

#1 nick1200  Icon User is offline

  • Php Coder
  • member icon

Reputation: -19
  • View blog
  • Posts: 922
  • Joined: 21-March 09

Picking a random row in my sql

Posted 20 March 2010 - 04:49 AM

i mean row and not tow lol


i have tried to google what im trying to do but there ent no info any were ( i love googeling )

i am trying to make my sql pick a random result from the table pokemon were map = 1

the problem is it is showing all the results and not just a random 1

here's my script



<?php
$con = mysql_connect("localhost","_","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("_", $con);

$result = mysql_query("SELECT * FROM pokemon
WHERE pokemon_map='1'");

while($row = mysql_fetch_array($result))
  {
  echo $row['pokemon_name'] . " " . $row['pokemon_type'];
  echo "<br />";
  }

mysql_close($con);
?> 



				    	<img src="http://<?php echo $result['pokemon_pic'] ?>" width="90" height="80" />






another problem has well it ent showing the image ether
any ideas how i can make it pick a random result and get the image showing

This post has been edited by nick1200: 20 March 2010 - 04:58 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Picking a random row in my sql

#2 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2951
  • View blog
  • Posts: 10,172
  • Joined: 08-August 08

Re: Picking a random row in my sql

Posted 20 March 2010 - 05:17 AM

I was going to suggest using MySQL's rand() function, but see this link about fast random results on large tables:
http://www.greggdev....ticles.php?id=6
I would have used mysql_num_rows instead of selecting "as max_id", but that's just nitpicking. ;)
Was This Post Helpful? 0
  • +
  • -

#3 nick1200  Icon User is offline

  • Php Coder
  • member icon

Reputation: -19
  • View blog
  • Posts: 922
  • Joined: 21-March 09

Re: Picking a random row in my sql

Posted 20 March 2010 - 05:22 AM

View PostCTphpnwb, on 20 March 2010 - 04:17 AM, said:

I was going to suggest using MySQL's rand() function, but see this link about fast random results on large tables:
http://www.greggdev....ticles.php?id=6
I would have used mysql_num_rows instead of selecting "as max_id", but that's just nitpicking. ;)

i don't get what hes doing on there

i would use MySQL's rand() but i don't know how to use it
Was This Post Helpful? 0
  • +
  • -

#4 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2951
  • View blog
  • Posts: 10,172
  • Joined: 08-August 08

Re: Picking a random row in my sql

Posted 20 March 2010 - 05:47 AM

First he gets the largest id number from the table, then he chooses a random number from 1 to that number and uses the result in his query. In your case, I'd use (untested):
$max = mysql__num_rows("SELECT * FROM pokemon
WHERE pokemon_map='1'");
$random_number = mt_rand(0, $max);
$result = mysql_query("SELECT * FROM pokemon
WHERE pokemon_map='1' LIMIT '".$random_number.", 1'");


or something similar.

This post has been edited by CTphpnwb: 20 March 2010 - 05:51 AM
Reason for edit:: Limit was missing a parameter!

Was This Post Helpful? 0
  • +
  • -

#5 nick1200  Icon User is offline

  • Php Coder
  • member icon

Reputation: -19
  • View blog
  • Posts: 922
  • Joined: 21-March 09

Re: Picking a random row in my sql

Posted 20 March 2010 - 05:51 AM

View PostCTphpnwb, on 20 March 2010 - 04:47 AM, said:

First he gets the largest id number from the table, then he chooses a random number from 1 to that number and uses the result in his query. In your case, I'd use (untested):
$max = mysql__num_rows("SELECT * FROM pokemon
WHERE pokemon_map='1'");
$random_number = mt_rand(0, $max);
$result = mysql_query("SELECT * FROM pokemon
WHERE pokemon_map='1' LIMIT '".$random_number."'");


or something similar.

http://www.pokemonto...net/testmap.php

white page lol


this is what i have now


<?php
$con = mysql_connect("localhost","_","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("_", $con);

$max = mysql__num_rows("SELECT * FROM pokemon
WHERE pokemon_map='1'");
$random_number = mt_rand(0, $max);
$result = mysql_query("SELECT * FROM pokemon
WHERE pokemon_map='1' LIMIT '".$random_number."'");

mysql_close($con);
?> 




Was This Post Helpful? 0
  • +
  • -

#6 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2951
  • View blog
  • Posts: 10,172
  • Joined: 08-August 08

Re: Picking a random row in my sql

Posted 20 March 2010 - 05:53 AM

I had a mistake in LIMIT. See my previous, now edited post.
Was This Post Helpful? 0
  • +
  • -

#7 nick1200  Icon User is offline

  • Php Coder
  • member icon

Reputation: -19
  • View blog
  • Posts: 922
  • Joined: 21-March 09

Re: Picking a random row in my sql

Posted 20 March 2010 - 05:56 AM

View PostCTphpnwb, on 20 March 2010 - 04:53 AM, said:

I had a mistake in LIMIT. See my previous, now edited post.

still white

i changed the connect to see if it was that but it ent that

<?php
	
mysql_connect("localhost", "_", "");
mysql_select_db("_");

$max = mysql__num_rows("SELECT * FROM pokemon
WHERE pokemon_map='1'");
$random_number = mt_rand(0, $max);
$result = mysql_query("SELECT * FROM pokemon
WHERE pokemon_map='1' LIMIT '".$random_number.", 1'");

mysql_close($con);
?> 






Was This Post Helpful? 0
  • +
  • -

#8 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2951
  • View blog
  • Posts: 10,172
  • Joined: 08-August 08

Re: Picking a random row in my sql

Posted 20 March 2010 - 06:07 AM

Oops! Try it without the single quotes around the LIMIT parameters.
<?php
	
mysql_connect("localhost", "_", "");
mysql_select_db("_");

$max = mysql_num_rows("SELECT * FROM pokemon
WHERE pokemon_map='1'");
$random_number = mt_rand(0, $max);
$result = mysql_query("SELECT * FROM pokemon
WHERE pokemon_map='1' LIMIT ".$random_number.", 1");

mysql_close($con);
?> 


Oh, and mysql_num_rows only has one underscore in two places, not two.

This post has been edited by CTphpnwb: 20 March 2010 - 06:09 AM
Reason for edit:: removed two underscores __

Was This Post Helpful? 0
  • +
  • -

#9 nick1200  Icon User is offline

  • Php Coder
  • member icon

Reputation: -19
  • View blog
  • Posts: 922
  • Joined: 21-March 09

Re: Picking a random row in my sql

Posted 20 March 2010 - 06:09 AM

View PostCTphpnwb, on 20 March 2010 - 05:07 AM, said:

Oops! Try it without the single quotes around the LIMIT parameters.
<?php
	
mysql_connect("localhost", "_", "");
mysql_select_db("_");

$max = mysql__num_rows("SELECT * FROM pokemon
WHERE pokemon_map='1'");
$random_number = mt_rand(0, $max);
$result = mysql_query("SELECT * FROM pokemon
WHERE pokemon_map='1' LIMIT ".$random_number.", 1");

mysql_close($con);
?> 




the page is showing now but no results :whatsthat:

http://www.pokemonto...net/testmap.php

This post has been edited by nick1200: 20 March 2010 - 06:10 AM

Was This Post Helpful? 0
  • +
  • -

#10 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2951
  • View blog
  • Posts: 10,172
  • Joined: 08-August 08

Re: Picking a random row in my sql

Posted 20 March 2010 - 06:11 AM

Did you fix the underscores? mysql__num_rows should be mysql_num_rows
Was This Post Helpful? 0
  • +
  • -

#11 nick1200  Icon User is offline

  • Php Coder
  • member icon

Reputation: -19
  • View blog
  • Posts: 922
  • Joined: 21-March 09

Re: Picking a random row in my sql

Posted 20 March 2010 - 06:13 AM

View PostCTphpnwb, on 20 March 2010 - 05:11 AM, said:

Did you fix the underscores? mysql__num_rows should be mysql_num_rows

yep

$max = mysql_num_rows("SELECT * FROM pokemon
WHERE pokemon_map='1'");
$random_number = mt_rand(0, $max);
$result = mysql_query("SELECT * FROM pokemon
WHERE pokemon_map='1' LIMIT ".$random_number.", 1");

mysql_close($con);



Was This Post Helpful? 0
  • +
  • -

#12 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2951
  • View blog
  • Posts: 10,172
  • Joined: 08-August 08

Re: Picking a random row in my sql

Posted 20 March 2010 - 06:30 AM

Oh, missing the query:
$qry = mysql_query("SELECT * FROM pokemon
WHERE pokemon_map='1'");
$max = mysql_num_rows($qry);


Was This Post Helpful? 0
  • +
  • -

#13 nick1200  Icon User is offline

  • Php Coder
  • member icon

Reputation: -19
  • View blog
  • Posts: 922
  • Joined: 21-March 09

Re: Picking a random row in my sql

Posted 20 March 2010 - 06:33 AM

View PostCTphpnwb, on 20 March 2010 - 05:30 AM, said:

Oh, missing the query:
$qry = mysql_query("SELECT * FROM pokemon
WHERE pokemon_map='1'");
$max = mysql_num_rows($qry);



so like this ?


<?php
	
mysql_connect("localhost", "_", "");
mysql_select_db("_");

$max = mysql_num_rows("SELECT * FROM pokemon
WHERE pokemon_map='1'");
$random_number = mt_rand(0, $max);
$result = mysql_query("SELECT * FROM pokemon
WHERE pokemon_map='1' LIMIT ".$random_number.", 1");

$qry = mysql_query("SELECT * FROM pokemon
WHERE pokemon_map='1'");
$max = mysql_num_rows($qry);

mysql_close($con);

?> 





still white
Was This Post Helpful? 0
  • +
  • -

#14 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2951
  • View blog
  • Posts: 10,172
  • Joined: 08-August 08

Re: Picking a random row in my sql

Posted 20 March 2010 - 06:36 AM

Try:
<?php
        
mysql_connect("localhost", "_", "");
mysql_select_db("_");

$qry = mysql_query("SELECT * FROM pokemon
WHERE pokemon_map='1'");
$max = mysql_num_rows($qry) - 1; // i.e.: 5 rows would be 0,1,2,3,4
$random_number = mt_rand(0, $max);
$result = mysql_query("SELECT * FROM pokemon
WHERE pokemon_map='1' LIMIT ".$random_number.", 1")

mysql_close($con); // unnecessary since connections are closed when the script ends.

?> 


Was This Post Helpful? 0
  • +
  • -

#15 nick1200  Icon User is offline

  • Php Coder
  • member icon

Reputation: -19
  • View blog
  • Posts: 922
  • Joined: 21-March 09

Re: Picking a random row in my sql

Posted 20 March 2010 - 06:39 AM

View PostCTphpnwb, on 20 March 2010 - 05:36 AM, said:

Try:
<?php
        
mysql_connect("localhost", "_", "");
mysql_select_db("_");

$qry = mysql_query("SELECT * FROM pokemon
WHERE pokemon_map='1'");
$max = mysql_num_rows($qry) - 1; // i.e.: 5 rows would be 0,1,2,3,4
$random_number = mt_rand(0, $max);
$result = mysql_query("SELECT * FROM pokemon
WHERE pokemon_map='1' LIMIT ".$random_number.", 1")

mysql_close($con); // unnecessary since connections are closed when the script ends.

?> 





white page again
http://www.pokemonto...net/testmap.php

thought this would be easier than this lol
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2