英文:
Implement Blacklist with values in two columns in same row
问题
I wanted to block checkout and display an error if the firstname and street address are found together in the same row in the "firstname" column and "address" column, respectively, in the "blacklist" table.
I used the following database query and other code:
$firstn = $this->db->query("SELECT firstname FROM blacklist");
if ($firstn->num_rows) {
foreach ($firstn->rows as $result) {
$blacklistedfirstname[] = $result['firstname'];
}
}
$addrs = $this->db->query("SELECT address FROM blacklist");
if ($addrs->num_rows) {
foreach ($addrs->rows as $result) {
$blacklistedaddress[] = $result['address'];
}
}
$is_f_bl = in_array(ucwords($post['firstname']), $blacklistedfirstname);
$is_a_bl = in_array(ucwords($post['address']), $blacklistedaddress);
if ($is_f_bl && $is_a_bl) {
$error = "Oops! Something went wrong. Contact the administrator";
}
The following is the database table named "blacklist":
| id | phone | firstname | address | |
|---|---|---|---|---|
| 1 | e@e.com | 999999 | Raj | Street2 |
| 2 | b@c.com | 222222 | Ben | Street1 |
| 3 | g@m.com | 333333 | Ken | Street3 |
The following SQL query is used in PHP:
The issue is when the firstname "Raj," which is in the row with ID 1, and the address "Street3," which is in the row with ID 3, are entered in checkout, it blocks with an error as blacklisted. It should only block if the address "Street2" is entered, as in ID 1. If "Ben" and "Street1" are entered, it should block as blacklisted. Please help. Thank you.
英文:
I wanted to block checkout and display error if firstname and Street address are together found in same row in firstname column and address column respectively in the table "blacklist".
I used the following database query and other codes: I did not use WHERE in the query.
$firstn= $this->db->query("SELECT firstname FROM blacklist");
if ($firstn->num_rows) {
foreach ($firstn->rows as $result) {
$blacklistedfirstname[] = $result['firstname'];
}
}
$addrs= $this->db->query("SELECT address FROM blacklist");
if ($addrs->num_rows) {
foreach ($addrs->rows as $result) {
$blacklistedaddress[] = $result['address'];
}
}
$is_f_bl = in_array(ucwords($post['firstname']), $blacklistedfirstname);
$is_a_bl = in_array(ucwords($post['address']), $blacklistedaddress);
if($is_f_bl && $is_a_bl){
$error = "Oops ! Something went wrong. Contact the administrator";
}
The following is the database table name "blacklist"
| id | email | phone | firstname | address |
|:---- |:------:| -----:|:---- |:------:|
| 1 | e@e.com | 999999 | Raj | Street2|
| 2 | b@c.com | 222222 | Ben | Street1|
| 3 | g@m.com | 333333 | Ken | Street3|
The following sql query is used in PHP:
The issue is When firstname "Raj" which is in the row having ID 1 and address "Street3" which is in row with ID 3 if entered in checkout, blocks with error as blacklisted. It must block only if address Street2 is entered as in ID 1. If Ben and Street1 is entered it must block as blacklisted.
Please help
Thank you
答案1
得分: 1
你可以这样组合和优化它：
$fname = ucwords($post['firstname']?? '');
$addr = ucwords($post['address']?? '');
$stmt= $this->db->prepare("SELECT id FROM blacklist WHERE firstname = ? AND address = ?");
$stmt->execute([$fname,$addr]);
if ($stmt->num_rows) {
$error = "Oops ! Something went wrong. Contact the administrator";
}
请注意，搜索是数据库的任务。所以，将查找被列入黑名单的项目留给MySQL。
永远不要使用带有输入的 query() 函数，这是危险且非常不安全的！学会使用 prepared statements。 在此了解更多信息
英文:
You can combine and optimize it this way:
$fname = ucwords($post['firstname']?? '');
$addr = ucwords($post['address']?? '');
$stmt= $this->db->prepare("SELECT id FROM blacklist WHERE firstname = ? AND address = ?");
$stmt->execute([$fname,$addr]);
if ($stmt->num_rows) {
$error = "Oops ! Something went wrong. Contact the administrator";
}
Note that searching is DB's task. So leave finding the black listed items to MySQL.
Never use query() function with inputs, it's risky and very unsafe! Learn to use prepared statements. Learn more here
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论