SA-MP Forums Archive
[MySQL] SQL Query - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+--- Thread: [MySQL] SQL Query (/showthread.php?tid=660492)


[MySQL] SQL Query - Josevasqueez - 04.11.2018

Hi! I have a problem in my sintax sql. I want to add a random phone number between 10000 and 99999. I have this query so far, but I'm failing to run it in the PHPMyAdmin

Code:
SELECT round(RAND()*99999) AS `NUM` WHERE `NUM` NOT IN (SELECT `PHONE_NUMBER` FROM `PHONE`);
This is the error: #1064 - Something is wrong in its syntax near 'WHERE `NUM` NOT IN (SELECT` PHONE_NUMBER` FROM `PHONE`)' on line 1

Re: [MySQL] SQL Query - Calisthenics - 04.11.2018

`WHERE` clause takes action first so `NUM` is unknown to select expression. With user-variables, you might get the results you expect, but this is not guaranteed. RAND()*99999 will not have min range 10000 and you need to floor it so why not use the easiest way?

Set `PHONE_NUMBER` in table `PHONE` as UNIQUE KEY to indicate that there will be no duplicates. Execute your INSERT query as you would do with exception the `IGNORE` keyword. If an error was to occur (duplicate for instance), it would be silent and no rows would be affected whereas when a new record is inserted, affected rows are 1.

pawn Code:
"INSERT IGNORE INTO PHONE (..., PHONE_NUMBER) VALUES (..., %d)", ..., random(90000) + 10000);
after execution, check affected rows to send an error/success message.

Re: [MySQL] SQL Query - KinderClans - 04.11.2018

I guess it's to give a random number when a player register? Well, you're wasting a query for nothing. IMHO you can just do:

pawn Code:
Player[playerid][PhoneNumber] = random(90000) + 12345;
In your Player enum to generate a random phone number. Then save it as you would save other stats. But in case you want to use your method:

https://www.w3resource.com/mysql/mat...d-function.php

Re: [MySQL] SQL Query - Josevasqueez - 04.11.2018

(SPANISH)

Solucionado de la siguiente manera.

Code:
new NumeroRandom, strx[200], count;
do
{
	NumeroRandom = RandomEntre(10000,99999);
	format(strx, 200, "SELECT * FROM `PHONE` WHERE `PHONE_NUMBER` = %d", NumeroRandom);
	new Cache:result = mysql_query(SMF_DATABASE, strx, true);
	cache_set_active(result);
	cache_get_row_count(count);
}
while(count > 1);
WHERE

Code:
RandomEntre(min, max)
{     
    // CrŠ¹ditos a y_less     
    new rand =  random(max-min)+min;    
    return rand;
}