Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009
    Posts
    32

    Unanswered: Return 4 Random and 1 specific record

    In my PHP project I would like to return 4 random records and 1 specific record and display them in a drop down.

    The idea being only 1 row is the correct answer.

    Here's what i have so far

    PHP Code:
    SELECT CONCAT(nok1fname' 'nok1sname) AS NOK FROM customers WHERE id=" . $_POST["username"] . "
    UNION ALL 
    (SELECT CONCAT(nok1fname' 'nok1sname) AS NOK FROM customers WHERE id!=" . $_POST["username"] . "ORDER BY RAND() LIMIT 5"); 
    I get 5 random records but whether or not I get the specific record is random.

    What am I doing wrong?

    Is there a better way?

    Many Thanks

    Steve

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Have you run the query directly against your database (i.e. not in PHP)?

    Your logic seems relatively sound, however you realise that you will be returning 6 records each time, right?
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2009
    Posts
    32
    Yes so without the PHP and purely through mysql the code looks like this:
    Code:
    SELECT CONCAT( nok1fname,  ' ', nok1sname ) AS NOK
    FROM customers
    WHERE id =12127
    UNION ALL (
    
    SELECT CONCAT( nok1fname,  ' ', nok1sname ) AS NOK
    FROM customers
    WHERE id !=12127
    )
    ORDER BY RAND( ) 
    LIMIT 5
    and the same result it doesnt always include the first SELECT WHERE id=12127.


  4. #4
    Join Date
    Jul 2009
    Posts
    32
    Managed to resolve this after several hours of head scratching so for the benefit of anyone trying the same thing here is the amended query:

    Code:
    SELECT CONCAT( nok1fname,  ' ', nok1sname ) AS NOK
    FROM customers
    WHERE id =12127
    UNION ALL (
    
    SELECT CONCAT( nok1fname,  ' ', nok1sname ) AS NOK
    FROM customers
    WHERE id !=12127 
    LIMIT 0, 5
    )
    ORDER BY RAND()
    It was a as simple as moving the LIMIT to the non matching query (second half of queried joined).

    Steve

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •