Incorrect search result query

Good day,

Am trying to find a solution to the search result issue am facing…if I search for a particular state it will return all the rows in the database which some of the result is different from the state I search, even different countries…I want it to return only what I search for…if I search by artist_username it will return all rows in the database too…

below is the code am running…am trying to generate the result randomly

include_once $_SERVER['DOCUMENT_ROOT'] . '/inc/';
$sql= "SELECT artist_username
FROM artists INNER JOIN members_signup ON artists.artist_username = members_signup.user_name
WHERE artists_id >= (SELECT FLOOR( MAX(artists_id) * RAND()) FROM `artists` 
WHERE MATCH(artist_bizname) AGAINST(:phrase) OR
MATCH(artist_city_area) AGAINST(:phrase) OR
MATCH(artist_state) AGAINST(:phrase) OR
MATCH(artist_country) AGAINST(:phrase) OR
MATCH(artist_username) AGAINST(:phrase)) LIMIT 25";
$s = $pdo->prepare($sql);
$s->bindValue(":phrase", $_SESSION['srhVal']);

foreach ($s as $row){
$userD[] = array( 'artist_username' => $row['artist_username'], 
'artist_bizname' =>$row['artist_bizname'],
  'artist_city_area' =>$row['artist_city_area'],
  'artist_country' =>$row['artist_country'],
  'profile_photo' =>$row['profile_photo'],
  'artist_state' =>$row['artist_state']); }

kindly help with code or idea to solve the issue

your problem is here –

WHERE artists_id >= ( SELECT ...

the subquery goes to a lot of trouble pulling all of the artists which meet your search criteria, then randomly futzes the highest id, to come up with one number

but then your outer query returns all artists which have a higher id than that number

not sure you realize why this is wrong :slight_smile:


am not sure…pls can you kindly help me correct the query so that I can randomly select the results that match my search criteria

please can you explain why you thought you needed a subquery… did someone suggest this to you?


Yes…someone suggested it to make the sql rand() fast…am trying to make rand() method fast base on large database records…How can I generate the result randomly fast…code or idea all welcome…


I will be very happy if you can help with sample code…

not sure i’m going to be able to help

i will, though, share with you some advice from a guy called Donald Knuth – “premature optimization is the root of all evil”

i would ditch the subquery, and use ORDER BY RAND() LIMIT 25

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.