phpBB Smith: Assistance with sql

Author	Message
RMcGirr83 Member
Sat Jul 22, 2006 9:22 pm Post subject: Assistance with sql
Hey Thoul, I have the following setup in the ban section of sessions.php Code: $sql = "SELECT b.ban_ip, b.ban_userid, b.ban_email, b.reason, b.ban_until, s.inventory_user, s.inventory_item, s.inventory_store, s.inventory_amount FROM " . BANLIST_TABLE . " b," . STORE_INVENTORY . " s WHERE b.ban_ip IN ('" . $user_ip_parts[1] . $user_ip_parts[2] . $user_ip_parts[3] . $user_ip_parts[4] . "', '" . $user_ip_parts[1] . $user_ip_parts[2] . $user_ip_parts[3] . "ff', '" . $user_ip_parts[1] . $user_ip_parts[2] . "ffff', '" . $user_ip_parts[1] . "ffffff') OR b.ban_userid = $user_id"; if ( $user_id != ANONYMOUS ) { $sql .= " OR b.ban_email LIKE '" . str_replace("\'", "''", $userdata['user_email']) . "' OR b.ban_email LIKE '" . substr(str_replace("\'", "''", $userdata['user_email']), strpos(str_replace("\'", "''", $userdata['user_email']), "@")) . "'"; } if ( !($result = $db->sql_query($sql)) ) { message_die(CRITICAL_ERROR, 'Could not obtain ban information', '', __LINE__, __FILE__, $sql); } if ( $ban_info = $db->sql_fetchrow($result) ) { if ( $ban_info['ban_ip'] \|\| $ban_info['ban_userid'] \|\| $ban_info['ban_email'] ) { if (isset ($ban_info['reason']) AND ($ban_info['reason'] != '') AND ($ban_info['ban_userid'])) { if( $ban_info['ban_until'] != '') { if ( $ban_info['inventory_user'] = $ban_info['ban_userid'] AND $ban_info['inventory_item'] = "20" AND $ban_info['inventory_store'] = "Null" ) { $sql = "DELETE FROM " . BANLIST_TABLE . " WHERE ban_userid = $user_id"; if (!$db->sql_query($sql)) { message_die(GENERAL_ERROR, 'Error Accessing the Banlist Table', '', __LINE__, __FILE__, $sql); } $amount = $ban_info['inventory_amount']; $amount2 = $amount - 1; $sql = "UPDATE " . STORE_INVENTORY . " SET inventory_amount = $amount2 WHERE inventory_item = '20' AND inventory_user = $user_id AND inventory_store = 'Null' LIMIT 1"; if ( !$result = $db->sql_query($sql) ) { message_die(GENERAL_ERROR, "Error updating item count", "", __LINE__, __FILE__, $sql); } // Delete the item space if item count is 0 or less if ( $amount2 <= 0 ) { $sql = "DELETE FROM " . STORE_INVENTORY . " WHERE inventory_item = '20' AND inventory_user = $user_id"; if ( !$result = $db->sql_query($sql) ) { message_die(GENERAL_ERROR, "Error updating inventory", "", __LINE__, __FILE__, $sql); } // } message_die(GENERAL_MESSAGE, 'You have used your UnDan Ticket'); } message_die(CRITICAL_MESSAGE, 'You have been Betrayed (aka banned) from this forum'.'<br /><br />' . $ban_info['reason'].'<br /><br />' .' Expires:'.' '. create_date('d M Y g:i a', $ban_info['ban_until'], $board_config['board_timezone'])); } message_die(CRITICAL_MESSAGE, 'You have been Betrayed (aka banned) from this forum'.'<br /><br />' . $ban_info['reason']); } message_die(CRITICAL_MESSAGE, 'You have been Betrayed (aka banned) from this forum'); } } I'm trying to reduce the amount of item in the store_inventory particular to that member. What's happening instead is it appears to be taking the amount from the board store (first listing in the table) and updating that number into the members amount. So if a member starts with 1 and there are 500 in the store then the new amount for the member will be 499. Any ideas, I'm sure you have one or two, as to what I am doing incorrectly? TIA. PS what exactly does the LIMIT 1 part of the query do?

Thoul Administrator
Sat Jul 22, 2006 9:52 pm Post subject: re: Assistance with sql
The problem with the amounts is that the first query doesn't match the ban info to the member's specific store info - it just takes whatever it finds file in the inventory table (which happens to be the board store) and uses that. Usually I would say a section needs to be added to the WHERE part of the query to match up the right inventory info with the user. But of course it can't be that simple. To make sure the IP and email ban features still work, the query has to be tweaked to use a LEFT JOIN clause instead. It should work better like this: Code: $sql = "SELECT b.ban_ip, b.ban_userid, b.ban_email, b.reason, b.ban_until, s.inventory_user, s.inventory_item, s.inventory_store, s.inventory_amount FROM " . BANLIST_TABLE . " b LEFT JOIN " . STORE_INVENTORY . " s ON b.ban_userid = s.inventory_user WHERE b.ban_ip IN ('" . $user_ip_parts[1] . $user_ip_parts[2] . $user_ip_parts[3] . $user_ip_parts[4] . "', '" . $user_ip_parts[1] . $user_ip_parts[2] . $user_ip_parts[3] . "ff', '" . $user_ip_parts[1] . $user_ip_parts[2] . "ffff', '" . $user_ip_parts[1] . "ffffff') OR b.ban_userid = $user_id"; if ( $user_id != ANONYMOUS ) { $sql .= " OR b.ban_email LIKE '" . str_replace("\'", "''", $userdata['user_email']) . "' OR b.ban_email LIKE '" . substr(str_replace("\'", "''", $userdata['user_email']), strpos(str_replace("\'", "''", $userdata['user_email']), "@")) . "'"; } The LIMIT 1 part of the query tells the SQL engine to stop after the first row. In a SELECT query, LIMIT 1 would mean that once the first request for a query is found, the script stops looking for others. When used in an UPDATE query as it is here, it means that only the first row matching all the conditions in the WHERE clause of the query will be updated.

RMcGirr83 Member
Sun Jul 23, 2006 6:56 am Post subject: re: Assistance with sql
Thanks a ton Thoul....I'll test it out. BTW, I still am not getting emails from here.

RMcGirr83 Member
Sun Jul 23, 2006 9:43 am Post subject: re: Assistance with sql
Works to a tee!! Thanks again Thoul.

Thoul Administrator
Sun Jul 23, 2006 2:13 pm Post subject: re: Assistance with sql
Glad I could help. The e-mails seem to be working fine on my tests - you might want to check your junk mail filters, if you have any, to see if the e-mails are getting stopped by them. phpBB generated e-mails get caught in those sometimes.

RMcGirr83 Member
Sun Jul 23, 2006 8:23 pm Post subject: re: Assistance with sql
Ignore me...I'm a nOOb. Apparently you won't get notifications from forums if you don't have the correct email address input into your profile. lol

Sat Jul 22, 2006 9:22 pm Post subject: Assistance with sql

Hey Thoul,

I have the following setup in the ban section of sessions.php

Code:

$sql = "SELECT b.ban_ip, b.ban_userid, b.ban_email, b.reason, b.ban_until, s.inventory_user, s.inventory_item, s.inventory_store, s.inventory_amount
 FROM " . BANLIST_TABLE . " b," . STORE_INVENTORY . " s
 WHERE b.ban_ip IN ('" . $user_ip_parts[1] . $user_ip_parts[2] . $user_ip_parts[3] . $user_ip_parts[4] . "', '" . $user_ip_parts[1] . $user_ip_parts[2] . $user_ip_parts[3] . "ff', '" . $user_ip_parts[1] . $user_ip_parts[2] . "ffff', '" . $user_ip_parts[1] . "ffffff')
 OR b.ban_userid = $user_id";
if ( $user_id != ANONYMOUS )
{
 $sql .= " OR b.ban_email LIKE '" . str_replace("\'", "''", $userdata['user_email']) . "'
 OR b.ban_email LIKE '" . substr(str_replace("\'", "''", $userdata['user_email']), strpos(str_replace("\'", "''", $userdata['user_email']), "@")) . "'";
}
if ( !($result = $db->sql_query($sql)) )
{
 message_die(CRITICAL_ERROR, 'Could not obtain ban information', '', __LINE__, __FILE__, $sql);
}

if ( $ban_info = $db->sql_fetchrow($result) )
{
 if ( $ban_info['ban_ip'] || $ban_info['ban_userid'] || $ban_info['ban_email'] )
 {

 if (isset ($ban_info['reason']) AND ($ban_info['reason'] != '') AND ($ban_info['ban_userid']))
{
if( $ban_info['ban_until'] != '')
{
if ( $ban_info['inventory_user'] = $ban_info['ban_userid'] AND $ban_info['inventory_item'] = "20" AND $ban_info['inventory_store'] = "Null" )
{
$sql = "DELETE FROM " . BANLIST_TABLE . "
WHERE ban_userid = $user_id";
if (!$db->sql_query($sql))
{
 message_die(GENERAL_ERROR, 'Error Accessing the Banlist Table', '', __LINE__, __FILE__, $sql);
}

 $amount = $ban_info['inventory_amount'];
 $amount2 = $amount - 1;

 $sql = "UPDATE " . STORE_INVENTORY . "
SET inventory_amount = $amount2
WHERE inventory_item = '20' AND inventory_user = $user_id AND inventory_store = 'Null' LIMIT 1";
 if ( !$result = $db->sql_query($sql) )
 {
 message_die(GENERAL_ERROR, "Error updating item count", "", __LINE__, __FILE__, $sql);
 }

 // Delete the item space if item count is 0 or less
 if ( $amount2 <= 0 )
 {
 $sql = "DELETE FROM " . STORE_INVENTORY . "
WHERE inventory_item = '20' AND inventory_user = $user_id";
if ( !$result = $db->sql_query($sql) )
{
 message_die(GENERAL_ERROR, "Error updating inventory", "", __LINE__, __FILE__, $sql);
}
// }
message_die(GENERAL_MESSAGE, 'You have used your UnDan Ticket');
}
message_die(CRITICAL_MESSAGE, 'You have been Betrayed (aka banned) from this forum'.' ' . $ban_info['reason'].' ' .' Expires:'.' '. create_date('d M Y g:i a', $ban_info['ban_until'], $board_config['board_timezone']));
}
message_die(CRITICAL_MESSAGE, 'You have been Betrayed (aka banned) from this forum'.' ' . $ban_info['reason']);
 }

 message_die(CRITICAL_MESSAGE, 'You have been Betrayed (aka banned) from this forum');

 }
}

I'm trying to reduce the amount of item in the store_inventory particular to that member. What's happening instead is it appears to be taking the amount from the board store (first listing in the table) and updating that number into the members amount. So if a member starts with 1 and there are 500 in the store then the new amount for the member will be 499.

Any ideas, I'm sure you have one or two, as to what I am doing incorrectly?

TIA.

PS what exactly does the LIMIT 1 part of the query do?

Sat Jul 22, 2006 9:52 pm Post subject: re: Assistance with sql

The problem with the amounts is that the first query doesn't match the ban info to the member's specific store info - it just takes whatever it finds file in the inventory table (which happens to be the board store) and uses that. Usually I would say a section needs to be added to the WHERE part of the query to match up the right inventory info with the user.

But of course it can't be that simple. Wink

To make sure the IP and email ban features still work, the query has to be tweaked to use a LEFT JOIN clause instead. It should work better like this:

Code:

$sql = "SELECT b.ban_ip, b.ban_userid, b.ban_email, b.reason, b.ban_until, s.inventory_user, s.inventory_item, s.inventory_store, s.inventory_amount
FROM " . BANLIST_TABLE . " b
LEFT JOIN " . STORE_INVENTORY . " s ON b.ban_userid = s.inventory_user
WHERE b.ban_ip IN ('" . $user_ip_parts[1] . $user_ip_parts[2] . $user_ip_parts[3] . $user_ip_parts[4] . "', '" . $user_ip_parts[1] . $user_ip_parts[2] . $user_ip_parts[3] . "ff', '" . $user_ip_parts[1] . $user_ip_parts[2] . "ffff', '" . $user_ip_parts[1] . "ffffff')
OR b.ban_userid = $user_id";
if ( $user_id != ANONYMOUS )
{
$sql .= " OR b.ban_email LIKE '" . str_replace("\'", "''", $userdata['user_email']) . "'
OR b.ban_email LIKE '" . substr(str_replace("\'", "''", $userdata['user_email']), strpos(str_replace("\'", "''", $userdata['user_email']), "@")) . "'";
}

The LIMIT 1 part of the query tells the SQL engine to stop after the first row. In a SELECT query, LIMIT 1 would mean that once the first request for a query is found, the script stops looking for others. When used in an UPDATE query as it is here, it means that only the first row matching all the conditions in the WHERE clause of the query will be updated.

Sun Jul 23, 2006 6:56 am Post subject: re: Assistance with sql

Thanks a ton Thoul....I'll test it out.

BTW, I still am not getting emails from here. Sad

Sun Jul 23, 2006 9:43 am Post subject: re: Assistance with sql

Works to a tee!!

Thanks again Thoul.

Sun Jul 23, 2006 2:13 pm Post subject: re: Assistance with sql

Glad I could help. Smile

The e-mails seem to be working fine on my tests - you might want to check your junk mail filters, if you have any, to see if the e-mails are getting stopped by them. phpBB generated e-mails get caught in those sometimes.

Sun Jul 23, 2006 8:23 pm Post subject: re: Assistance with sql

Ignore me...I'm a nOOb.

Apparently you won't get notifications from forums if you don't have the correct email address input into your profile. Rolling Eyes

lol