User Function Shop System

Question

Before I begin I must state I am very new to both php/sql so apologies if my understanding is a little limited.

Ok so basically I have got a site with a user login system where the users can earn points doing various things then exchange them for new functions, for this example I will call the function "user_images".

The transaction is stored in "function_users" which has..

ID
function_id
user_id
cost
date

I have have got the system to the point where the user can visit the shop page, purchase the function and it deducts the appropriate points or currency, my question is to experienced coders, what would the most efficient way to determine if the function is purchased by the user?

Rough example(yes i know it's not msql injection proof it's just an example:

$logged = $_SESSION['user_id'];

$check = mysql_query("SELECT * FROM feature_users WHERE `user_id`='$logged' AND feature_id='1'");
$check = mysql_num_rows($check);

if ($check < 1) {
include/features/user_images.php
?>

I am not asking for someone to write the code for me, I am simply asking the most efficient way to communicate if the user has purchased the feature so it can then display it. Thanks in advance

It is not only not injection save -- you should not use the `mysql_` family at all: http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — kratenko, Sep 06 '13 at 10:20
like i said, just a rough example of my thoughts on how it could be coded, but i wanted some opinions on if there was a more efficient way to do it. — user2571547, Sep 06 '13 at 10:23
What you have is already the most efficient way.. but you're including the wrong way. — Ali, Sep 06 '13 at 10:24

score 0 · Accepted Answer · answered Sep 06 '13 at 10:45

Your query:

SELECT *
FROM feature_users
WHERE `user_id` = '$logged' AND feature_id = '1';

is pretty simple. To make it as efficient as possible, you want a composite index on the table:

create index feature_users_user_feature on feature_users(user_id, feature_id);

Three additional comments. First, you don't need to return all the columns. If the query returns anything, the user has the feature. Second, id columns are typically integers (so the constant values should not be surrounded by single quotes for readability purposes). Third, If someone is inputting a user, they would typically be inputting a name. You might really mean:

SELECT 1
FROM feature_users fu
     users u
     on fu.user_id = u.user_id
WHERE `user_name` = '$logged' AND feature_id = 1;

User Function Shop System

1 Answers1