PDO query not working when variable is null

PHP
1

Hi Guys,
I’ve tried to pull user data randomly from 2 tables. The code below doesn’t work with PDO query. It’s working great with mysqli:

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();
}

I’ve already tried this, it isn’t worked for me: http://stackoverflow.com/questions/14078744/php-mysql-pdo-binding-null-parameter-doesnt-work

How to get it work?
Thank you

1 Like
2

You said the code snippet doesn’t work with PDO - what errors are you receiving? Have you tried replacing:

user.id != :id

with:

(user.id <=> :id) = false

I personally think that changing the default value of $id would be the more sensible approach though than trying to prepare a query with a value that is potentially. A user’s ID is typically unsigned and auto incrementing (which starts from 1 and onwards), so setting the default ID value to 0 or any negative number would simplify the complexity required in your SQL to compare a null value.

Your SQL query also seems unnecessarily complicated. You’re aliasing attributes when they needn’t be:

snapshot.img as img, snapshot.avt as avt

and generating a temporary table to perform a left join to, which seems pretty redundant to me:

LEFT JOIN (SELECT snapshot.uid, snapshot.img, snapshot.avt FROM snapshot) snapshot

Plus you’re mixing logic from your WHERE clause into your ON clause:

 AND snapshot.avt = '1'
3

Thanks for reply,

It doesn’t report any error. Just fail silently.

I works as expected if $id is not null.

The query works well with mysqli. So I query with mysqli if a user is not logged in and with PDO if a user logged in.

4

That… is a mess. What are you trying to do with it? RAND() is < 1. So if you have less than 150 entries in the user table, you’ll get no result. (if X < 150, 15/X > 0.1, *10 means > 1, which means F(0…149) > RAND() ), and as X grows, the average number of results grows, but is a random evaluation.

5

And if you’re looking to avoid a problem of setting the variable to null… set it to -1 instead of null. The value doesnt have to make sense (you should never have a userid of -1), but it has to be valid (an integer).

1 Like
6

You’re selecting these fields in addition to user.name but in the code snippet posted you only use user.name are the rest of them used elsewhere?

Have you considered doing one query to get a list of all potential user ids that your interested in, having PHP then either pick a number of them at random with array_rand() or shuffling the order of them with shuffle and palcing the first x mumber of user ids into a second array.

Then use a second query with an IN clause to grab the required data for each of them.

7

That was I hate about MySQL. I don’t really under stand this. I just picked it from the internet. The author shows this method against others with performance graph. I believe him.

Should I switch database to MongoDB since have much more skill in JavaScript than in PHP.

I already have considerable amount of users in the database.

8

Thanks for advice,
I use every row and column data I picked. It’s just for the sake of design :frowning:

I’ll further study your suggestion.

9

What do you want the query to DO, is the question?

(There is no way that adding complexity to the query by adding unnecessary function calls improves performance)