Warm tip: This article is reproduced from stackoverflow.com, please click

sql, insert random data from selected data

发布于 2020-03-27 10:16:55

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.

Questioner
roottoor
Viewed
105
reembank 2019-07-03 21:49

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.