Author Topic: Database Query Problem (Read 2059 times)

The Wizard · « **on:** July 25, 2012, 10:42:35 AM »

Hello:

I'm working on a SMF Shop mod and I need to update the bit of code to work on SMF 2.0.2
I'm still learning php and trying to understand the changes in the code from SMF 1.x to SMF 2.0.2
Could someone fix this code and explane why it won't work with SMF 2.0.2? I really want to understand the changes.

Thanks

Wiz

Quote

//BEGIN SMFShop Profile Images Item 0.1 Beta
global $db_prefix, $boardurl;
$result_flag = db_query("SELECT shop_Flag
FROM {$db_prefix}members
WHERE ID_MEMBER = {$message['member']['id']}
LIMIT 1", __FILE__, __LINE__);
$row_flag = mysql_fetch_array($result_flag, MYSQL_ASSOC);

if($row_flag['shop_Flag'] !== "") {
echo "Flag: <img src='{$boardurl}/Sources/shop/flag_images/{$row_flag['shop_Flag']}'> ";
}

Suki · « **Reply #1 on:** July 25, 2012, 11:28:02 AM »

Hi, SMF 2.0 uses a framework/set of functions to perform queries: http://www.simplemachines.org/community/index.php?topic=224166.0

on you specific case:

Code: [Select]

//BEGIN SMFShop Profile Images Item 0.1 Beta
	global $smcFunc;

	$flag = '';

	$request = $smcFunc['db_query']('', '
		SELECT shop_Flag
		FROM {db_prefix}members
		WHERE id_member = {int:id_member}
		LIMIT {int:limit}',
		array(
			'id_member' => $message['member']['id'],
			'limit' => 1,
		)
	);

	while ($row = $smcFunc['db_fetch_row']($request))
		$flag = $row['shop_Flag'];

	if (!empty($flag))
		$context['my_mod']['flag'] = $flag;

Do note how the casting array is used to pass the values instead of hardcoding them on the query itself.

The Wizard · « **Reply #2 on:** July 25, 2012, 12:01:36 PM »

Thank you Suki

The Wizard · « **Reply #3 on:** July 25, 2012, 12:22:12 PM »

@Suki - Could you check this code and tell me if I did it right?

Thanks

Wiz

Orginal Code:

Quote

function onUse() {
global $db_prefix, $ID_MEMBER;

$result = db_query("UPDATE {$db_prefix}members
SET shop_Flag = '{$_POST['flag']}'
WHERE ID_MEMBER = {$ID_MEMBER}",
__FILE__, __LINE__);

return "Successfully set your flag image to {$_POST['flag']}!";
}

Wizards Try:

Quote

function onUse() {
global $smcFunc;

$result = $smcFunc['db_query']('', '
      UPDATE {$db_prefix}members
      SET shop_Flag = '{$_POST['flag']}'
       WHERE id_member = {int:id_member}
       LIMIT {int:limit}',
       array(
          'id_member' => $message['member']['id'],
          'limit' => 1,
       )
    );
     return "Successfully set your flag image to {$_POST['flag']}!";
}

Suki · « **Reply #4 on:** July 25, 2012, 01:25:07 PM »

Not quite like that.

Just like the other cases, you need to cast that var on the casting array:

SET shop_Flag = {string:flag}

array(
'id_member' => $message['member']['id'],
'limit' => 1,
'flag' => $_POST['flag']
)

Of course you need to properly sanitize all your $_POST superglobals and make sure they contain exactly what you want them to contain. On this case the LIMIT is redundant since the WHERE argument pretty much narrows the query down to just 1 entry.

The Wizard · « **Reply #5 on:** July 25, 2012, 01:45:53 PM »

@Suki

So your saying the code should look like this -

Quote

function onUse() {
global $smcFunc;

$result = $smcFunc['db_query']('', '
UPDATE {$db_prefix}members
SET shop_Flag = {string:flag}
array(
'id_member' => $message['member']['id'],
'limit' => 1,
'flag' => $_POST['flag']
)
}

Suki · « **Reply #6 on:** July 25, 2012, 01:53:34 PM »

No, you removed the WHERE part, so now the query will be executed on every row, not a good thing.

Code: [Select]

unction onUse() {
        global $smcFunc;

        $result = $smcFunc['db_query']('', '
      UPDATE {$db_prefix}members 
      SET shop_Flag = {string:flag}
      WHERE id_member = {int:id_member}
        array(
          'id_member' => $message['member']['id'],
          'flag' =>  $_POST['flag']
        )
    }

This will only modify the flag column in the member's specific row. Take the old 1.1.x query as an example, the only thing you need to change is how you will perform the query but the query itself is still the same.

K@ · « **Reply #7 on:** July 25, 2012, 02:02:10 PM »

I removed your double-topic, Wiz.

Pretty pointless, doing that.

The Wizard · « **Reply #8 on:** July 25, 2012, 02:05:41 PM »

Thanks K

The Wizard · « **Reply #9 on:** July 25, 2012, 02:07:08 PM »

Ok so here is the final product - I hope fingers crossed

Quote

function onUse() {
global $smcFunc;

$result = $smcFunc['db_query']('', '
      UPDATE {$db_prefix}members
      SET shop_Flag = {string:flag}
      WHERE id_member = {int:id_member}
array(
'id_member' => $message['member']['id'],
'limit' => 1,
'flag' => $_POST['flag'],
)
      );
}

K@ · « **Reply #10 on:** July 25, 2012, 02:09:43 PM »

Quote from: The Wizard on July 25, 2012, 02:05:41 PM

Thanks K

Suki · « **Reply #11 on:** July 25, 2012, 02:20:20 PM »

Quote from: The Wizard on July 25, 2012, 02:07:08 PM

Ok so here is the final product - I hope fingers crossed

Quote
function onUse() {
global $smcFunc;

$result = $smcFunc['db_query']('', '
      UPDATE {$db_prefix}members
      SET shop_Flag = {string:flag}
      WHERE id_member = {int:id_member}
array(
'id_member' => $message['member']['id'],
'limit' => 1,
'flag' => $_POST['flag'],
)
      );
}

OK, the query looks good but, there are some flaws, for example, you don't check your $_POST var, as it is I can enter whatever I want on the text field:

flag: 1234567

and your function will store that on your database, this is not a good thing, first thing you need to do is check if the var is actually full:

Code: [Select]


if (!empty($_POST['flag']))
{
 $result = $smcFunc['db_query']('', '
      UPDATE {$db_prefix}members 
      SET shop_Flag = {string:flag}
      WHERE id_member = {int:id_member}
        array(
          'id_member' => $message['member']['id'],
          'limit' => 1,
          'flag' =>  $_POST['flag'],
        )
      );
}

or since you are actually using a function, you can also pass a var to it:

onUse($_POST['flag']);

Code: [Select]

function onuse($flag)
{
if (!empty($flag))
{
 $result = $smcFunc['db_query']('', '
      UPDATE {$db_prefix}members 
      SET shop_Flag = {string:flag}
      WHERE id_member = {int:id_member}
        array(
          'id_member' => $message['member']['id'],
          'limit' => 1,
          'flag' =>  $flag,
        )
      );
}

else
    return false;
}

Best way to learn is to check other mods and see how they perform their queries.

The Wizard · « **Reply #12 on:** July 25, 2012, 02:23:17 PM »

I'm taking that big thumbs up to mean I got it right.

So now here is the last of my changes -

Orginal code:

Quote

if (file_exists(dirname(__FILE__) . '/SSI.php') && !defined('SMF'))
 require_once(dirname(__FILE__) . '/SSI.php');
// Hmm... no SSI.php and no SMF?
elseif (!defined('SMF'))
 die('Error: Cannot install - please verify you put this in the same place as SMF\'s index.php.');

 //The Flag field in the member table
 db_query("ALTER TABLE `{$db_prefix}members` ADD `shop_Flag` TEXT NOT NULL", __FILE__, __LINE__);

 //Add the item
 db_query("INSERT INTO `{$db_prefix}shop_items` (`name` , `desc` , `price` , `module` , `stock`, `input_needed`, `can_use_item`) VALUES ('Profile Image', 'Show an image next to your posts', '100.00', 'Flag', '50', 1, 1)", __FILE__, __LINE__);

My Code:

Quote

if (file_exists(dirname(__FILE__) . '/SSI.php') && !defined('SMF'))
 require_once(dirname(__FILE__) . '/SSI.php');
// Hmm... no SSI.php and no SMF?
elseif (!defined('SMF'))
 die('Error: Cannot install - please verify you put this in the same place as SMF\'s index.php.');

 //The Flag field in the member table
 $smcFunc['db_query']('', 'ALTER TABLE `{$db_prefix}members` ADD `shop_Flag` TEXT NOT NULL', __FILE__, __LINE__);

 //Add the item
 $smcFunc['db_query']('', 'INSERT INTO `{$db_prefix}shop_items` (`name` , `desc` , `price` , `module` , `stock`, `input_needed`, `can_use_item`) VALUES ('Profile Image', 'Show an image next to your posts', '100.00', 'Flag', '50', 1, 1), __FILE__, __LINE__);

Please tell me I'm starting to understand this?

The Wizard · « **Reply #13 on:** July 25, 2012, 03:21:23 PM »

Ok I have tryed to run this and I'm getting a problem form this part of the code -

Quote

//Add the item
$smcFunc['db_query']('', 'INSERT INTO `{$db_prefix}shop_items` (`name` , `desc` , `price` , `module` , `stock`, `input_needed`, `can_use_item`) VALUES ('Profile Image', 'Show an image next to your posts', '100.00', 'Flag', '50', 1, 1)'', __FILE__, __LINE__);

But I just don't see where the problem is does anybody?

The Wizard · « **Reply #14 on:** July 25, 2012, 03:40:32 PM »

Forgot This is the error I'm getting:

Quote

Parse error: syntax error, unexpected T_STRING in /Packages/temp/ProfileImage_Item_0.1/installScript.php on line 12

News:

Author Topic: Database Query Problem (Read 2059 times)

The Wizard

Database Query Problem

Suki

Re: Database Query Problem

The Wizard

Re: Database Query Problem

The Wizard

Re: Database Query Problem

Suki

Re: Database Query Problem

The Wizard

Re: Database Query Problem

Suki

Re: Database Query Problem

K@

Re: Database Query Problem

The Wizard

Re: Database Query Problem

The Wizard

Re: Database Query Problem

K@

Re: Database Query Problem

Suki

Re: Database Query Problem

The Wizard

Re: Database Query Problem

The Wizard

Re: Database Query Problem

The Wizard

Re: Database Query Problem