2

i have a question in regards to mysql mainly. i have the following:

mysql_query("INSERT INTO {$game}_ships (location,login_id
         ) values(
    '250','44')");

i would like to make the location a random number from 2 to 300.

my table structure includes among others a column for location and login id, i dont need to mention other columns as they are static and do not change. the above is suposed to make a ship look like its moving around the systems.

location   int(4)

can anyone help with this?

user631756
  • 55
  • 2
  • 9
  • 1
    See: http://stackoverflow.com/questions/984396/how-to-get-mysql-random-integer-range - `(FLOOR( 2 + RAND( ) * 300 ))` – Justin Ethier Feb 03 '12 at 21:19
  • possible duplicate of [MySQL get a random value between two values](http://stackoverflow.com/questions/6550155/mysql-get-a-random-value-between-two-values) – Mchl Feb 03 '12 at 21:21
  • This will generate a number between 2 and 302. You have to subtract the min from max on the multiplication, like I've posted in my answer. – Ricardo Souza Feb 03 '12 at 21:22

3 Answers3

5
mysql_query("INSERT INTO {$game}_ships (location,login_id
         ) values(
    FLOOR(RAND() * 298 + 2),'44')");

See: http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand

Mchl
  • 61,444
  • 9
  • 118
  • 120
  • the above is giving me a 0 location. – user631756 Feb 03 '12 at 21:24
  • 1
    There might be something wrong with your column datatype then. Could you edit you question to show us table structure? – Mchl Feb 03 '12 at 21:27
  • i have updated the question and tested it again. the result is 0. i have a problem with location 0, it does not exist. lol – user631756 Feb 03 '12 at 21:45
  • The actual datatype of this column is what is of onterest here. Do `SHOW CREATE TABLE yourTableName` and paste the row where it describes `location` column – Mchl Feb 03 '12 at 21:48
  • In such case, double check if you copied my answer correctly. Don't add any quotes to it (you have quotes around `250` in your original query - they are not needed. If you copied it exactly, and it still inserts 0, then some magick is going on, and I've no idea what kind. – Mchl Feb 03 '12 at 22:07
  • 1
    lol, removed the quotes and it worked. bingo. lol, thank you very much. :) – user631756 Feb 03 '12 at 22:21
  • For the record. you don't need quotes around `44` either (assuming `login_id` is also an `int()` – Mchl Feb 03 '12 at 22:42
2

You can use something like this:

INSERT INTO TABLE_NAME(rand_col) VALUES(FLOOR($MIN_VAL + (RAND() * ($MAX_VAL - $MIN_VAL))));

Where MIN_VAL is your lower number (2) and MAX_VAL is your higher number(300)

Ricardo Souza
  • 16,030
  • 6
  • 37
  • 69
  • its not a random column, im trying to randomize an insert to make it look like a ship is moving around. – user631756 Feb 03 '12 at 21:33
  • My example does exactly this. It's an insert and the MIN_VAL and MAX_VAL are only placeholders: `mysql_query("INSERT INTO {$game}_ships (location,login_id ) values( FLOOR(2 + (RAND() * (300 - 2))),'44')");` That does the same as your acepted answer. – Ricardo Souza Feb 04 '12 at 20:57
1

Can you use PHP's rand instead?

mt_rand(2,300)
  • my guess both answers the random didnt work because i still get a 0 system. – user631756 Feb 03 '12 at 21:48
  • Can you output the result of mt_rand to make sure it is working? –  Feb 03 '12 at 21:54
  • mt_rand works but its not inserting a valid integer in the database and its reverting back to default, which is 0. – user631756 Feb 03 '12 at 22:13
  • 1
    according to your original query (at least) you'd be trying to insert a string into an integer type? Lose the quotes and maybe that will fix it. –  Feb 06 '12 at 18:25