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:
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.
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).
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.
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.