I am using Opencart 3.0.3.8
Basically I created a code in php to block customers from account registration if email and telephone, first name and address is in blacklist. For that in the catalog/controller/account/register.php I added array that holds list of blacklists as below:
$blacklistedemail = array("email1@gmail.com", "email2@gmail.com","example@example.com");
$blacklistedtelephone = array("9999999999", "5555555555","2222222222");
$blacklistedfirstname = array("Raj", "ken", "ber");
$blacklistedaddress = array("street1", "street2","street3");
with the rest of codes in php, validation works and it blocks customers in blacklist. But, using arrays like the above is not standard and it is Ok for testing validation code and small data. But using database will be good choice .
Instead of using php array list , I needed to change it into database query and result. For that I created a table called " oc_blacklist". In this table there are four columns 'email' , 'telephone', 'address', 'firstname' which contains blacklist. I tried some sql queries but it breaks and registration page becomes blank or give internal server error 500.
How to query DB and get email, telephone, address and firstname blacklist in arrays set in respective variables, $blacklistedemail, $blacklistedtelephone, $blacklistedaddress and $blacklistedfirstname. Please help me.
Thank you
AVSRThe
Dedication and passion goes to those who are able to push and merge a project.
Regards,
Straightlight
Programmer / Opencart Tester
Code: Select all
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
Reach out to us at hello@softmonke.com for your OpenCart web development needs.
There is no model or any other module in this. I add blacklist data manually using phpmyadmin.
The php in_array see the blacklist and throw error if user submits the form by the code:
If I use php array for ex. : $blacklistedemail = ["email1@gmail.com", "example2example.com" , "someone@somemail.com"]; , everything works.
But I wanted to deleted this php line and substitute with DB query and assign the result to the variable $blacklistedemailI. I don't know to query database and put that result in the variables like. $blacklistedemail.
Basically I tried for ex.
$query = $this->db->query("SELECT 'email 'FROM 'oc_blacklist' ");
and similar codes, but not perfect, as I don't have good knowledge in the syntax to be used in Opencart. Also stuck up on how to fetch all rows containing email addresses in the column. I need to Query and fetch results having all email addresses in the column 'email' (all in the rows) from the table 'oc_blacklist ' and assign the variable $blacklistedemail to it. Please help me with right syntax for one variable $blacklistedemail, I will follow it and do for the rest of columns like 'telephone' 'firstname' etc....
Thank you
Avsrthe
Code: Select all
$this->request->post
Dedication and passion goes to those who are able to push and merge a project.
Regards,
Straightlight
Programmer / Opencart Tester
Histraightlight wrote: ↑Tue May 23, 2023 7:46 amWhile what you have posted are partial codes, OC does not use $post but rather:
However, since you are requiring assistance with custom codes, you could always create a new service request in the Commercial Support section of the forum or contact me directly via the forum PM to get this done as a custom job.Code: Select all
$this->request->post
I had already assigned $post['email'] = $this->request->post['email'];
this works in php and throws error and blocks when a blacklisted user tries to register. I am not asking for full custom work.
I am asking help to query DB and syntax for this particular variable. Just substitute php array method line to DB query result.
If you can help me with Sql syntax, it will be helpful..
Thank you
I have figured it out and found which seems to be correct syntax for the DB query with two lines of code as below: I removed table prefix. Table name is 'blacklist' and the column is 'email'. In this column blacklisted email addresses are manually added in phpmyadmin.
$query = $this->db->query("SELECT email FROM blacklist ");
$blacklistedemail = $query->row;
At present the above queries 1st row of the column. Do we need to use foreach or something ? I tried, but page breaks because I dont know the syntax correctly.. It is done almost nearest to what is needed. Only thing is that all rows values must be retrieved and used in a variable to validate.
Please help me to fetch all rows from the the above query into $blacklistedemail. Then this $blacklistedemail variable will be used for validation. Please help.
Thank you for your replies
AVSRThe
That's the correct way to query from your database in OpenCart. However, $query->row will only return the first result from your query.avsrthe wrote: ↑Tue May 23, 2023 1:27 pm@ADD Creative and @Straighlight
I have figured it out and found which seems to be correct syntax for the DB query with two lines of code as below: I removed table prefix. Table name is 'blacklist' and the column is 'email'. In this column blacklisted email addresses are manually added in phpmyadmin.
$query = $this->db->query("SELECT email FROM blacklist ");
$blacklistedemail = $query->row;
At present the above queries 1st row of the column. Do we need to use foreach or something ? I tried, but page breaks because I dont know the syntax correctly.. It is done almost nearest to what is needed. Only thing is that all rows values must be retrieved and used in a variable to validate.
Please help me to fetch all rows from the the above query into $blacklistedemail. Then this $blacklistedemail variable will be used for validation. Please help.
Thank you for your replies
AVSRThe
Code: Select all
$this->db->query
Code: Select all
{
"num_rows" => number of rows returned from your query, 0 if none,
"row" => first result returned from your query as an array or an empty array if no results are returned,
"rows" => all rows returned from your query as an array of arrays or an empty array if no results are returned,
}
Code: Select all
array (
'email' => 'email1@gmail.com',
)
Code: Select all
array (
0 => array (
'email' => 'email1@gmail.com'
),
1 => array(
'email' => 'email2@gmail.com'
),
2 => array(
'email' => 'email3@gmail.com'
)
)
Code: Select all
foreach ($query->rows as $row) {
echo $row['email'];
}
Code: Select all
$query = $this->db->query("SELECT email FROM blacklist");
// trim and convert users' input to lowercase to be safe
$trimmed_email = trim(utf8_strtolower($this->request->post['email']));
foreach ($query->rows as $row) {
if ($row['email'] === $trimmed_email) {
// there's a match, do something
break;
}
}
Code: Select all
$blacklist_query = $this->db->query("SELECT * FROM blacklist WHERE email = '" . $this->db->escape(utf8_strtolower($this->request->post['email'])) . "'");
if ($blacklist_query->num_rows) {
// there's a match, do something
}
Reach out to us at hello@softmonke.com for your OpenCart web development needs.
Code: Select all
if ($query->num_rows) {
foreach ($query->rows as $result) {
$blacklistedemail[] = $result['email'];
}
}
Thank you very much. It works very well. You have made my day good. I tried to add WHERE clause initially, but unsure what to write next to it. The page broke or gives Undefined variable error. For example I tried: $query= $this->db->query("SELECT email FROM blacklist WHERE email = $email"); I know the variable is not define like this. It is wrong.ADD Creative wrote: ↑Tue May 23, 2023 5:40 pmYou would need num_rows.It will probably be more efficient to use a WHERE clause in you SQL to check if the email in the database.Code: Select all
if ($query->num_rows) { foreach ($query->rows as $result) { $blacklistedemail[] = $result['email']; } }
My table Name is "blacklist" and column is 'email' . email column contains all email addresses as blacklist. Can you please tell what should we add next to WHERE to make this more efficient? If I needed to add something in mytable ? If you suggest, I will do the same. Please help me further to fine tune this. Thank you
Code: Select all
$query= $this->db->query("SELECT email FROM blacklist WHERE email = '" . $this->db->escape($email) . "'");
HiADD Creative wrote: ↑Tue May 23, 2023 7:22 pmThe quoting and escaping is very important.Code: Select all
$query= $this->db->query("SELECT email FROM blacklist WHERE email = '" . $this->db->escape($email) . "'");
After using WHERE like above code, I get error:
Warning: Undefined variable $email in /xxxx/xxxxxx/public_html/xxxxxx/system/storage/modification/catalog/controller/account/register.php on line 271Warning: Undefined variable $blacklistedemail in /xxxx/xxxxxx/public_html/demo/system/storage/modification/catalog/controller/account/register.php on line 286
In my register.php there is no variable $email. Please help me further. Shall I use without WHERE or any solution possible? Thank you
Code: Select all
public function getBlacklistCustomerByEmail(string $email): array {
$query = $this->db->query("SELECT `email` FROM `blacklist` WHERE LCASE(`email`) = '" . $this->db->escape(oc_strtolower($email)) . "'");
return $query->row;
}
catalog/controller/account/register.php
Code: Select all
$blacklistemails = $this->model_account_customer->getBlacklistCustomerByEmail($this->request->post['email']);
foreach ($blacklistmails as $blacklistemail) {
$blacklistedemail[] = $blacklistemail['email'];
}
if (in_array($this->request->post['email'], $blacklistedemail) || (oc_strlen($this->request->post['email']) > 96) || !filter_var($this->request->post['email'], FILTER_VALIDATE_EMAIL)) {
$this->error['email'] = $this->language->get('error_email');
}
Business Web Development | Content Creation | Analytics and Reporting | SEO
It's hard to say as I don't know where you are adding the code and what other code you have added. But just replace $email with the variable which contains the email address you are searching for. Maybe it's $this->request->post['email'] you want.avsrthe wrote: ↑Tue May 23, 2023 7:37 pmHiADD Creative wrote: ↑Tue May 23, 2023 7:22 pmThe quoting and escaping is very important.Code: Select all
$query= $this->db->query("SELECT email FROM blacklist WHERE email = '" . $this->db->escape($email) . "'");
After using WHERE like above code, I get error:
Warning: Undefined variable $email in /xxxx/xxxxxx/public_html/xxxxxx/system/storage/modification/catalog/controller/account/register.php on line 271Warning: Undefined variable $blacklistedemail in /xxxx/xxxxxx/public_html/demo/system/storage/modification/catalog/controller/account/register.php on line 286
In my register.php there is no variable $email. Please help me further. Shall I use without WHERE or any solution possible? Thank you
HiADD Creative wrote: ↑Tue May 23, 2023 11:45 pmIt's hard to say as I don't know where you are adding the code and what other code you have added. But just replace $email with the variable which contains the email address you are searching for. Maybe it's $this->request->post['email'] you want.avsrthe wrote: ↑Tue May 23, 2023 7:37 pmHiADD Creative wrote: ↑Tue May 23, 2023 7:22 pmThe quoting and escaping is very important.Code: Select all
$query= $this->db->query("SELECT email FROM blacklist WHERE email = '" . $this->db->escape($email) . "'");
After using WHERE like above code, I get error:
Warning: Undefined variable $email in /xxxx/xxxxxx/public_html/xxxxxx/system/storage/modification/catalog/controller/account/register.php on line 271Warning: Undefined variable $blacklistedemail in /xxxx/xxxxxx/public_html/demo/system/storage/modification/catalog/controller/account/register.php on line 286
In my register.php there is no variable $email. Please help me further. Shall I use without WHERE or any solution possible? Thank you
It is a great help you did.
I think WHERE is very efficient as you said, when Sql query will target particular data of a particular row each and validate.
Because I am validation Firstname and Street address also. If Firstname and Street address together matches, it will block checkout. Now what happens is that it blocks if a name in one row and a different street address in another row matches. It must check name and street address together in a single row. Genuine customers should not be blocked
Shall I I insert "ID" column in phpmyadmin. Then query ID from database wiith name posted and ID from address posted. If both IDs match then block with error messsge. This logic, will it work? Please help me with ID target in the Sql query you mentioned: $query= $this->db->query("SELECT firstname FROM blacklist WHERE ID = '" . $this->db->escape($id) . "'");
$query2= $this->db->query("SELECT address FROM blacklist WHERE ID = '" . $this->db->escape($id) . "'");
something like this ? Or any better method ? But ID will not get posted from frontend checkout obviously. But how to target both firstname and particular Street to match in blacklist table?
But as of now your answer with the code works very well for email address, without WHERE clause at present, because email address is already unique.But First name can be same for many people. A better solution would be really helpful. Please help.
Thank you
AVSRThe
I would advise against trying to blacklist based on first name and address since you will have to blacklist all possible combinations and variations of first names and addresses - since there are no fixed format for first names and addresses. Furthermore, it is too easy for the blacklisted customers to bypass your blacklisting logic.avsrthe wrote: ↑Wed May 24, 2023 8:35 amHiADD Creative wrote: ↑Tue May 23, 2023 11:45 pmIt's hard to say as I don't know where you are adding the code and what other code you have added. But just replace $email with the variable which contains the email address you are searching for. Maybe it's $this->request->post['email'] you want.avsrthe wrote: ↑Tue May 23, 2023 7:37 pm
Hi
After using WHERE like above code, I get error:
Warning: Undefined variable $email in /xxxx/xxxxxx/public_html/xxxxxx/system/storage/modification/catalog/controller/account/register.php on line 271Warning: Undefined variable $blacklistedemail in /xxxx/xxxxxx/public_html/demo/system/storage/modification/catalog/controller/account/register.php on line 286
In my register.php there is no variable $email. Please help me further. Shall I use without WHERE or any solution possible? Thank you
It is a great help you did.
I think WHERE is very efficient as you said, when Sql query will target particular data of a particular row each and validate.
Because I am validation Firstname and Street address also. If Firstname and Street address together matches, it will block checkout. Now what happens is that it blocks if a name in one row and a different street address in another row matches. It must check name and street address together in a single row. Genuine customers should not be blocked
Shall I I insert "ID" column in phpmyadmin. Then query ID from database wiith name posted and ID from address posted. If both IDs match then block with error messsge. This logic, will it work? Please help me with ID target in the Sql query you mentioned: $query= $this->db->query("SELECT firstname FROM blacklist WHERE ID = '" . $this->db->escape($id) . "'");
$query2= $this->db->query("SELECT address FROM blacklist WHERE ID = '" . $this->db->escape($id) . "'");
something like this ? Or any better method ? But ID will not get posted from frontend checkout obviously. But how to target both firstname and particular Street to match in blacklist table?
But as of now your answer with the code works very well for email address, without WHERE clause at present, because email address is already unique.But First name can be same for many people. A better solution would be really helpful. Please help.
Thank you
AVSRThe
Reach out to us at hello@softmonke.com for your OpenCart web development needs.
Code: Select all
public function getBlacklistCustomerByEmail(string $email): array {
$query = $this->db->query("SELECT `email` FROM `blacklist` WHERE LCASE(`email`) = '" . $this->db->escape(oc_strtolower($email)) . "'");
return $query->row;
}
catalog/controller/account/register.php
Code: Select all
$blacklistedemail = $this->model_account_customer->getBlacklistCustomerByEmail($this->request->post['email']);
if (in_array($this->request->post['email'], $blacklistedemail) || (oc_strlen($this->request->post['email']) > 96) || !filter_var($this->request->post['email'], FILTER_VALIDATE_EMAIL)) {
$this->error['email'] = $this->language->get('error_email');
}
Business Web Development | Content Creation | Analytics and Reporting | SEO
Users browsing this forum: No registered users and 396 guests