sql, insert random data from selected data

Question

sql, insert random data from selected data,

I get data from another table in the insertion process, but I only need to select one of these data randomly, now I can only add the first one of those data.

MySql DB.


$sql="INSERT INTO dal (dal.tarih,dal.tarihsonuc,
dal.fiyat,dal.kullaniciadi,dal.isim,dal.fal_konusu,
dal.cinsiyet,dal.medeni_hal,dal.is_durumu,dal.giris_id)
SELECT '2019-07-03',
'2019-07-04',
5,
'user',
'user2',
10,
0,
0,
1,
yorumlar2.id 
FROM yorumlar2 
WHERE yorumlar2.fiyat=".$_GET['fiyat']." and 
yorumlar2.cinsiyet=".$_GET['cinsiyet']." and 
yorumlar2.fal_konusu=".$_GET['fal_konusu']." and 
yorumlar2.medeni_hal=".$_GET['medeni_hal']." and 
yorumlar2.is_durumu=".$_GET['is_durumu']." and 
yorumlar2.id NOT IN(SELECT dal.giris_id FROM dal 
WHERE dal.kullaniciadi='".$_GET['kullaniciadi']."' 
and dal.fiyat=".$_GET['fiyat']." and 
dal.cinsiyet=".$_GET['cinsiyet']." 
and dal.fal_konusu=".$_GET['fal_konusu']." 
and dal.medeni_hal=".$_GET['medeni_hal']." 
and dal.is_durumu=".$_GET['is_durumu'].") limit 1";

I want "yorumlar2.id" to appear randomly. Only 1 pcs should come at random.

Wow...this query is screaming very loud: Please hack my database! (sql injection) - take a look at [how-can-i-prevent-sql-injection-in-php](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php/60496#60496) — B001ᛦ, Jul 03 '19 at 13:25
*" Please hack my database!"* Give me the url/IP i will do it.... @B001ᛦ jokes aside.. Topicstarter you should be reading [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1) — Raymond Nijland, Jul 03 '19 at 13:29
I know it's sql injection, but I haven't edited it, thank you :) — roottoor, Jul 03 '19 at 13:29
_I know it's sql injection, but I haven't edited it..._ Should we cry or laugh? — B001ᛦ, Jul 03 '19 at 13:31
iam tempted this mark this question as a duplicate off [How to do a loop to fill a database table in MySQL?](https://stackoverflow.com/questions/46650817/how-to-do-a-loop-to-fill-a-database-table-in-mysql/46652915#46652915) as mine answer shows a way to get inserting random data — Raymond Nijland, Jul 03 '19 at 13:35

reembank · Accepted Answer · 2019-07-03T13:49:11.340

0

You can add the following to your query before your existing limit to randomise the order in which they'll be fetched, leaving you with one random row.

ORDER BY RAND()

If your table contains a high number of rows or you are more concerned with performance, then you might want to try this instead in your where clause:

AND yorumlar2.id >= RAND() * (SELECT MAX(id) FROM yorumlar2)

and this as your ORDER BY/LIMIT:

ORDER BY yorumlar2.id LIMIT 1

This will calculate a random number under your highest ID, limit your query to rows above that ID, and then give you the first one, leaving you with one random row.

You can see this explained in detail here.

edited Jul 03 '19 at 13:49

answered Jul 03 '19 at 13:31

reembank

106
1
5

unfortunately my limits are not regular ID as an example: 2,4,5,6,33,41,98 – roottoor Jul 03 '19 at 13:32
This will sort the whole set randomly whether your IDs match up to the random numbers or not. Try inserting it between the last bracket and "LIMIT 1" and see the results. – reembank Jul 03 '19 at 13:35

sql, insert random data from selected data

1 Answers1