News:

Bored?  Looking to kill some time?  Want to chat with other SMF users?  Join us in IRC chat or Discord

Main Menu

Need a query to pull users name corresponding in a custom table

Started by hungarianguy, September 21, 2020, 02:00:37 PM

Previous topic - Next topic

hungarianguy

I am working on a plugin and I am in need of some help. I have created a custom script that adds a table to my smf forum, called smf_log_customers. The table has 3 fields: id, id_board and customer_name.

I can add users to the table assigning them a customer name. So far so good. What I want to do is to deny access to a forum based on the customer name. I want them to see the forum, but not enter.

I need some help with pulling out the info. The code to add the customers name and the board id in my custom table works fine btw. Looking at the Smf code this is what I have tried so far but it is not working.

$result = $smcFunc['db_query']('', '
SELECT customer_name
FROM {db_prefix}smf_log_customers
WHERE id_board = {int:board}',
array(
board' => $board,
)
);

list ($customers) = $smcFunc['db_fetch_row']($result);
$smcFunc['db_free_result']($result);

       if ($user_info['username'] == $customers)
    {
            fatal_lang_error('noboard_entering_error', false);
}


How can I make so the user whose username matches the value of customer_name field in the custom table can not enter the board id that I do not want him to. Some help will be greatly appreciated.

SychO

a cheaper query would be


$request = $smcFunc['db_query']('', '
SELECT customer_name
FROM {db_prefix}smf_log_customers
WHERE customer_name = {string:username}
AND id_board = {int:board_id}',
array(
'username' => $user_info['username'],
'board_id' => $board,
),
);
list ($customer_name) = $smcFunc['db_fetch_row']($request);
$smcFunc['db_free_result']($request);

if (!empty($customer_name))
fatal_lang_error('noboard_entering_error', false);


however, why are you using usernames instead of user ids ? also make sure $board exists before you run the query.

Oh and it would probably make more sense to name the table just log_customers
Checkout My Themes:
-

Potato  •  Ackerman  •  SunRise  •  NightBreeze

hungarianguy

Quote from: SychO on September 21, 2020, 03:51:59 PM
a cheaper query would be


$request = $smcFunc['db_query']('', '
SELECT customer_name
FROM {db_prefix}smf_log_customers
WHERE customer_name = {string:username}
AND id_board = {int:board_id}',
array(
'username' => $user_info['username'],
'board_id' => $board,
),
);
list ($customer_name) = $smcFunc['db_fetch_row']($request);
$smcFunc['db_free_result']($request);

if (!empty($customer_name))
fatal_lang_error('noboard_entering_error', false);


however, why are you using usernames instead of user ids ? also make sure $board exists before you run the query.

Oh and it would probably make more sense to name the table just log_customers

Thank you SychO. I tried it but it is not working. Customers are still able to view the forum that I do not want them to.

I am using usernames instead of user ids, because somehow ids are not working when I added them to the custom table.

SychO

well, I'm sure it didn't work with ids like it is not working now because of something else.

I don't know exactly where you're putting the code or how you're executing it, or if you got any specific errors or not.
Checkout My Themes:
-

Potato  •  Ackerman  •  SunRise  •  NightBreeze

Doug Heffernan

The issue here from what I could see at the whole mod that the OP sent me, is that the code used to add the data to the custom table is not done right. The customer_name field is empty, hence why it was not working.

Edit. I have taken this on.

Doug Heffernan

Quote from: SychO on September 21, 2020, 05:53:39 PM
He didn't use user ids though, also I didn't test the code either

The problem lies elsewhere. It is very difficult to troubleshoot issues when you do not have the whole picture, but only bits and parts.

hungarianguy


live627

{db_prefix}smf_log_customerswill becomesmf_smf_log_customers

Quote from: SychO on September 21, 2020, 03:51:59 PM
a cheaper query would be


$request = $smcFunc['db_query']('', '
SELECT customer_name
FROM {db_prefix}smf_log_customers
WHERE customer_name = {string:username}
AND id_board = {int:board_id}',
array(
'username' => $user_info['username'],
'board_id' => $board,
),
);
list ($customer_name) = $smcFunc['db_fetch_row']($request);
$smcFunc['db_free_result']($request);

if (!empty($customer_name))
fatal_lang_error('noboard_entering_error', false);

[....]
Depends on the indexes present. Might be slower without one.

I'm assuming  the only index is the primary key on id which might be auto increment.

Quote from: hungarianguy on September 21, 2020, 02:00:37 PM[...]

$result = $smcFunc['db_query']('', '
SELECT customer_name
FROM {db_prefix}smf_log_customers
WHERE id_board = {int:board}',
array(
board' => $board,
)
);

list ($customers) = $smcFunc['db_fetch_row']($result);
$smcFunc['db_free_result']($result);

       if ($user_info['username'] == $customers)
    {
            fatal_lang_error('noboard_entering_error', false);
}

[....]
   list ($customers) = $smcFunc['db_fetch_row']($result); only fetches the first username.

hungarianguy

Quote from: live627 on September 23, 2020, 03:36:44 AM
{db_prefix}smf_log_customerswill becomesmf_smf_log_customers

Quote from: SychO on September 21, 2020, 03:51:59 PM
a cheaper query would be


$request = $smcFunc['db_query']('', '
SELECT customer_name
FROM {db_prefix}smf_log_customers
WHERE customer_name = {string:username}
AND id_board = {int:board_id}',
array(
'username' => $user_info['username'],
'board_id' => $board,
),
);
list ($customer_name) = $smcFunc['db_fetch_row']($request);
$smcFunc['db_free_result']($request);

if (!empty($customer_name))
fatal_lang_error('noboard_entering_error', false);

[....]
Depends on the indexes present. Might be slower without one.

I'm assuming  the only index is the primary key on id which might be auto increment.

Quote from: hungarianguy on September 21, 2020, 02:00:37 PM[...]

$result = $smcFunc['db_query']('', '
SELECT customer_name
FROM {db_prefix}smf_log_customers
WHERE id_board = {int:board}',
array(
board' => $board,
)
);

list ($customers) = $smcFunc['db_fetch_row']($result);
$smcFunc['db_free_result']($result);

       if ($user_info['username'] == $customers)
    {
            fatal_lang_error('noboard_entering_error', false);
}

[....]
   list ($customers) = $smcFunc['db_fetch_row']($result); only fetches the first username.

Thank you. Yes, the prefix is {db_prefix}log_customers in my code. I wrote it {db_prefix}smf_log_customers by mistake. And you are right again, the only index is the primary key on id which is auto increment.

How can I pull out the data the correct way?

live627

Thee code provided y SychO should work. Did you declare globals? I'll need to see more of your code to  find the problem.

hungarianguy

Quote from: live627 on September 23, 2020, 10:54:45 AM
Thee code provided y SychO should work. Did you declare globals? I'll need to see more of your code to  find the problem.

Yes, I declaired the globals. That is the whole code. I am putting it at Load.php file after this code:

if (count(array_intersect($user_info['groups'], $board_info['groups'])) == 0 && !$user_info['is_admin'])
$board_info['error'] = 'access';


I used $board_info too and still it did not work.

What is the difference between $board_info and $board btw?

live627

$board_info is an array of information about the board; see the 2.0 source on GitHub
$board is the board id

Can you upload your Load.php somewhere so that I see the entire context of your code snippet?

And have you checked the database to see if the desired username exists with the board id? (probably a dumb question

hungarianguy

Here is the Load.php file. The info is present in the custom table. Each row contains the id, the board id and the customer name. Thank you again for looking into this.


live627

when you say it doesn't work, it  just produces a blank page?

$request = $smcFunc['db_query']('', '
SELECT customer_name
FROM {db_prefix}log_customers
WHERE customer_name = {string:username}
AND id_board = {int:board_id}',
array(
'username' => $user_info['username'],
'board_id' => $board,
)//there was an extra comma here resulting in a parse error
);
list ($customer_name) = $smcFunc['db_fetch_row']($request);
$smcFunc['db_free_result']($request);

if (!empty($customer_name))
fatal_lang_error('noboard_entering_error', false);

hungarianguy

Quote from: live627 on September 24, 2020, 05:11:49 AM
when you say it doesn't work, it  just produces a blank page?

$request = $smcFunc['db_query']('', '
SELECT customer_name
FROM {db_prefix}log_customers
WHERE customer_name = {string:username}
AND id_board = {int:board_id}',
array(
'username' => $user_info['username'],
'board_id' => $board,
)//there was an extra comma here resulting in a parse error
);
list ($customer_name) = $smcFunc['db_fetch_row']($request);
$smcFunc['db_free_result']($request);

if (!empty($customer_name))
fatal_lang_error('noboard_entering_error', false);


I dont get any errors. Customers whose names are in the custom table still get access to forums where I dont want them to.

Doug said that it could be due to my table not having an user id field for the customers. How can I can get the member id of the customers whose name is at my custom table? Maybe that would help.

Illori

Why can't you use the built-in subscription system or group permissions?

live627

Something isn't lining up. It worked when I tried it.

CREATE TABLE `smf_log_customers` (
  `customer_name` varchar(50) NOT NULL,
  `id_board` smallint(6) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `smf_log_customers` (`customer_name`, `id_board`) VALUES
('live627', 2);


ALTER TABLE `smf_log_customers`
  ADD PRIMARY KEY (`customer_name`,`id_board`);
And then the new code added to Load.php blocked me.

$user_info['username'] maps to user_name from smf_members; this is the name provided upon registration which is used to login; only admins can change it
$user_info['name'] maps to real_name from smf_members; this is the displayed name which can be set by the user
$user_info['id'] maps to member_id  from smf_members

Advertisement: