My PDO query isn't work when parameter is null

Discussion in 'PHP' started by ketting00, Oct 18, 2014.

  1. #1
    Hi guys,
    I'm trying to display users randomly along with data pull from the database. The code below used to work great with mysqli method. It's not working now:
    
    if( logIn() ) {
        $id = $_GET['id'];
    } else {
        $id = null;
    }
    $sql = "
        SELECT
            user.id, user.name, user.cat, user.privacy, user.banned, snapshot.uid, snapshot.img as img, snapshot.avt as avt
        FROM
            user
        LEFT JOIN
            (SELECT snapshot.uid, snapshot.img, snapshot.avt FROM snapshot) snapshot
        ON
            user.id = snapshot.uid AND snapshot.avt = '1'
        WHERE
            user.id != :id AND user.banned != :banned AND RAND()<(SELECT ((15/COUNT(*))*10) FROM user)
        GROUP BY
            user.id
        ORDER BY RAND()
        LIMIT 15
    ";
    $banned = 1;
    try {
        $stmt = $conn->prepare($sql);   
        $stmt->execute(array(':id' => $id, ':banned' => $banned));
        $users = array();
        $users = $stmt->fetchAll(PDO::FETCH_ASSOC);
        if (count($users) <> 0 ) {
            foreach ( $users AS $user ) {
                echo $user['name'];
            }
        } else {
            echo "No user!";
        }
    } catch(PDOException $e) {
        echo $e->getMessage();
    }
    
    Code (markup):
    Please help and thank you,
     
    ketting00, Oct 18, 2014 IP
  2. Anveto

    Anveto Well-Known Member

    Messages:
    697
    Likes Received:
    40
    Best Answers:
    19
    Trophy Points:
    195
    #2
    Why do you have this?

    
        AND RAND()<(SELECT ((15/COUNT(*))*10) FROM user)
    
    Code (markup):
    Anyways, are you getting an error? Could you try this?

    
        $stmt = $conn->prepare($sql);
        $stmt->bindValue('id',$id, PDO::PARAM_INT);
        $stmt->bindValue('banned',$banned, PDO::PARAM_INT);
        $stmt->execute();
    
    Code (markup):
     
    Anveto, Oct 18, 2014 IP
  3. ketting00

    ketting00 Well-Known Member

    Messages:
    782
    Likes Received:
    28
    Best Answers:
    3
    Trophy Points:
    128
    #3
    Thanks for help. It doesn't work. It looks pretty pattern anyway. I may consider using it.

    Base on my research, it's the most efficient way (in term of fastest) to random large database. If I do not misunderstand, it pick up amount of data in range and ignore the others. It may not be very efficient in term of coverage, but I focus on speed.
     
    ketting00, Oct 18, 2014 IP