Simplest Mod

[Arantor]

When using replace, it will be done to replace based on the primary key. If you're not certain of that you can attempt an update, and if that fails, an insert.

[RustyBarnacle]

OK, I opened up an ads mod and got something like this but I don't think I got it right:

Code Select Expand


$t = "NumChoices = $nnc,
Wager1 = $nw1,
Wager2 = $nw2,
Wager3 = $nw3,
HouseCut1 = $hc1,
HouseCut2 = $hc2,
HouseCut3 = $hc3,
TheRules = '$tr',
Enabled = '$enabled',
PaypalEmail = '$pp'";

$smcFunc['db_query']('',"
UPDATE {db_prefix}sportspool_events
SET ", $t ,"
WHERE {db_prefix}sportspool_events.EventID = $eve",
);

[Arantor]

... I have no idea what that code is supposed to do :/

[RustyBarnacle]

Hehe, that can't be a good sign. 

The objective is to replace all of the variables in that table where the primary key is $eve.

[Arantor]

In that case, you could either do a replace (which will take out the entire row) or an update (which updates only the columns in question, which sounds more like it's supposed to be)

The one thing, the method of coding there is flat out wrong. It's not supposed to be done like that in 2.0, and if you found that from a mod, I'd be asking questions of the author, especially if it is who I think it is

For EVERY parameter, you're supposed to use a parameterised component.

      $smcFunc['db_query']('','
            UPDATE {db_prefix}sportspool_events
            SET
               NumChoices = {int:num_choices},
               Wager1 = {int:wager1},
               ...etc...
            WHERE {db_prefix}sportspool_events.EventID = {int:event_id}",
            array(
               'event_id' => $eve,
               'num_choices' => $nnc,
...and so on...
            )
         );

The whole point of doing it is to ensure things can't get into the database when they're not supposed to. I don't know whether the columns are numbers (int) or text (string) injections, though.

[RustyBarnacle]

Thank you!  I think thats simple enough that it just penetrated!  I am a little sick and tired today so I took 2 ideas, one from the example and another from a mod I didn't fully understand and pasted them together into a monstrosity that likely wouldn't have worked.

[RustyBarnacle]

If I wanted to make a second mod, whose only purpose was to insert several rows into a table of sample data which would be called from the package manager, what would that query look like?  The tables already exist so they don't need to be remade, its just inserting sav 3 rows of sample data, but from the package manager which I know made a difference last time.

[Arantor]

Actually makes no real difference at all. You still have package-info.xml, it still calls the file you name in <database>. All you have to do is $smcFunc['db_insert'], with type as 'insert', and everything else as you would normally.

[RustyBarnacle]

OK, so if I take the example above, does it look something like this?:

$smcFunc['db_query']('','
            Insert {db_prefix}sportspool_events
            SET
               NumChoices = {int:num_choices},
               Wager1 = {int:wager1},
               ...etc...
            array(
               array('event_id' => 'Event1', 'event_id' => 'Event2'),
               array('num_choices' => '3', 'num_choices' = '5'),
...and so on...
            )
         );

Sorry about the number of questions.  When I was making the first version for SMF1 I googled a lot but this thread and some mods that I don't always understand are my only sources of info and my programming knowledge is really Rusty.

[Arantor]

Not for an insert of new rows, no. UPDATE does what it says on the tin, it updates existing rows, not inserting new ones...

[RustyBarnacle]

Yeah as soon as I posted I realized my mistake and edited.  I guess you caught me before I saved again.

[Arantor]

You're really not supposed to use INSERT into tables with db_query, that's what db_insert is for.

[RustyBarnacle]

Right, so looking at the example you had before, is this getting closer?

$smcFunc['db_insert']('','
            '{db_prefix}sportspool_events',
            array(
               'NumChoices' => 'int', 'Wager1' => 'int',
            ),
             
            array(
               array('event_id' => 'Event1', 'event_id' => 'Event2'),
               array('num_choices' => '3', 'num_choices' = '5'),
...and so on...
            )
         );

[RustyBarnacle]

Assuming I was using the same variable names. 

[Arantor]

Not exactly, no.

Code Select Expand

            array(
               'NumChoices' => 'int', 'Wager1' => 'int',
            ),

This is the array of all the columns you want to fill, columnname => type pairs. You don't need to include this for a field that has an auto-increment value.

Code Select Expand

            array(
               array('event_id' => 'Event1', 'event_id' => 'Event2'),
               array('num_choices' => '3', 'num_choices' = '5'),
...and so on...
            )

This array contains an array of arrays, the inner array represents one row. The values in this sub-array map 1:1 with the list of columns you specified in the previous option. So the first array would be the first row's (NumChoices, Wager1), the second array would be (NumChoices, Wager2) for the second inserted row. If you do decide to map key=>value, the key will be ignored from what I remember, because it's the order of values that's important, and it's the same order as the ones listed in the first main array.

[RustyBarnacle]

Awesome, thank you.

[RustyBarnacle]

I got everything as close as I thought I was going to get and did an install on a test forum.  I've figured out a few small things but this one is stumping me, probly because I don't understand the documentation at the Mod Authors site.  I get this error:

Fatal error: Function name must be a string in /home/me/public_html/sptest2/Sources/sportspool.php on line 545

This is the code in that area.  Line 545 is the $request line:

Code Select Expand


$eve = $sportspool['eve'];

// Load the choices requirement
$request = $smcFunc['db_query']('', '
SELECT
NumChoices, PaypalEmail
FROM
{db_prefix}sportspool_events
WHERE
EventID = {int:EventID}',
array(
'EventID' => $eve,
)
);


Now, EventID is the actual name of the column in the table which happens to be the primary key.  Should I be using a temporary variable and somehow coming back to it?

[Arantor]

Just means you didn't declare $smcFunc as global.

[RustyBarnacle]

DOH!  Thanks.

[RustyBarnacle]

How do you do joins?

original code:

Code Select Expand


$request = db_query("
SELECT
Name, Points
FROM
{$db_prefix}sportspool_athlete
LEFT JOIN {$db_prefix}spo