query question

[Anguz]

I'm releasing v2 of my Smart URLs mod, but since the modsettings are different, there's a change in the dbmod file, which is ok with new users, but may bring up an error for users of v1

should I create an upgrade script? or they can just run the new dbmod file and if the setting or column already exists, it'll just ignore it?

anyway, how should I write the queries to remove these entries from the db?

Code Select Expand

$result1 = mysql_query("INSERT INTO {$db_prefix}settings VALUES ('smartUrl', '1');");
$result2 = mysql_query("INSERT INTO {$db_prefix}settings VALUES ('smartUrl_friendlyUrl', '1');");
$result3 = mysql_query("INSERT INTO {$db_prefix}settings VALUES ('friendlyUrl_kwBoard', '0');");
$result4 = mysql_query("INSERT INTO {$db_prefix}settings VALUES ('friendlyUrl_kwCateg', '0');");
$result5 = mysql_query("INSERT INTO {$db_prefix}settings VALUES ('friendlyUrl_fileExt', 'php');");
$result6 = mysql_query("INSERT INTO {$db_prefix}settings VALUES ('smartUrl_relativeUrl', '1');");
$result7 = mysql_query("INSERT INTO {$db_prefix}settings VALUES ('smartUrl_urlCounter', '1');");

thanks

[Shoeb Omar]

maybe I'm missing something, but can't you combine that into one query?

[Gobalopper]

For deleting something along the lines of:
DELETE FROM yabbse_settings WHERE variable = 'somename';

That is unless you are just updating the values of those variables. Then it would be:
UPDATE yabbse_settings SET value = 'x' WHERE variable = 'y';

[Anguz]

thank you guys

maybe I'm missing something, but can't you combine that into one query?

I don't know how to do it yet though, this is how it was done in the script I used to write mine

For deleting something along the lines of:
DELETE FROM yabbse_settings WHERE variable = 'somename';

That is unless you are just updating the values of those variables. Then it would be:
UPDATE yabbse_settings SET value = 'x' WHERE variable = 'y';

cool... I already wrote a version of the script to just add the new stuff in version 2 of the mod and leave the old stuff there just in case... but when v2 is working fine and I move it out of beta, I'll use this for the upgrade script to remove the old settings

[pulpitfire]

one easy way for me to find the correct format for queries, is by doing the action directly from the mysql manager.  every time you do something there, it displays the query and asks you to confirm if this is what you want to do (e.g. Do you really want to  :
DELETE FROM `rnglog_boards` WHERE `memberName` = 'rng' AND `ID_BOARD` = '1' AND `logTime` = '1041965836' LIMIT 1 ?)

[[Unknown]]

I would use INSERT IGNORE.  This is the same as INSERT but it *ignores* unique rows that already exist.

-[Unknown]

[Anguz]

I would use INSERT IGNORE.  This is the same as INSERT but it *ignores* unique rows that already exist.

-[Unknown]

great tip! I'll change the script right away

thank you 

[Anguz]

is there a similar query for these?

Code Select Expand

$result1 = mysql_query("ALTER TABLE {$db_prefix}boards ADD urlKeyword TINYTEXT;");
$result2 = mysql_query("ALTER TABLE {$db_prefix}categories ADD urlKeyword TINYTEXT;");

[[Unknown]]

No... you'll just have to not output anything if they fail.

-[Unknown]

[Anguz]

or maybe if I can check first if they exist and add them or not based on that... how would I check that?

[[Unknown]]

You can only check it by testing for error, for example:

SELECT column_that_might_exist
FROM table_that_has_column
LIMIT 1;

If that query bites the dust, you have to add the column.

-[Unknown]

[Anguz]

cool... thank you