How to access drops of mobs via query_sql?

[Louis T Steinhil]

This is my code but i always get an error:
 

		set @string$, "( `name_aegis` IN ( SELECT DISTINCT `drop1_item` FROM `mob_db_re` ) " +
					"OR `name_aegis` IN ( SELECT DISTINCT `drop2_item` FROM `mob_db_re` ) " +
					"OR `name_aegis` IN ( SELECT DISTINCT `drop3_item` FROM `mob_db_re` ) " +
					"OR `name_aegis` IN ( SELECT DISTINCT `drop4_item` FROM `mob_db_re` ) " +
					"OR `name_aegis` IN ( SELECT DISTINCT `drop5_item` FROM `mob_db_re` ) " +
					"OR `name_aegis` IN ( SELECT DISTINCT `drop6_item` FROM `mob_db_re` ) " +
					"OR `name_aegis` IN ( SELECT DISTINCT `drop7_item` FROM `mob_db_re` ) " +
					"OR `name_aegis` IN ( SELECT DISTINCT `drop8_item` FROM `mob_db_re` ) " +
					"OR `name_aegis` IN ( SELECT DISTINCT `drop9_item` FROM `mob_db_re` ) " +
					"OR `name_aegis` IN ( SELECT DISTINCT `drop10_item` FROM `mob_db_re` ) )";

				set @sql_query$, "SELECT `item_db_re`.`id` FROM `item_db_re` WHERE `type` = 'Card' AND " + .@string$;
				dispbottom "Generated SQL query: " + @sql_query$;
				.@nb = query_sql(@sql_query$, .@id);

Error:

[SQL]: DB error - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
[Debug]: at D:\Games\Ragnarok\01_emulator\renewal\src\map\script.cpp:17797 - SELECT `item_db_re`.`id` FROM `item_db_re` WHERE `type` = 'Card' AND
[Warning]: Script command 'query_sql' returned failure.
[Debug]: Source (NPC): Card_Quest_NPC at moc_para01 (16,13)
[Debug]: Source (NPC): Card_Quest_NPC is located in: npc/custom/Card_Collector.txt

I even tried this in query tab of database
 

SELECT COUNT(*) FROM `mob_db_re` WHERE `drop8_item` IS NOT NULL;

it returned 859. So meaning it has value.

 

I also tried this
 

set @query$, "SELECT `mob_db_re`.`drop8_item` FROM `mob_db_re`";
dispbottom "Generated SQL query: " + .@query$;
.@nb = query_sql(.@query$, .@id);
dispbottom "Number of rows returned: " + .@nb;

it didn't return anything.

[Poring King]

Get some idea with this . I just write it simple 

prontera,150,150,5	script	QueryMobDrops	1_M_MERCHANT,{
    // NPC Dialogue
    mes "Enter the Mob ID:";
    input .@mob_id;

    // SQL Query
    query_sql("SELECT m.id, m.name_japanese, i.id, i.name_english, md.rate " +
              "FROM mob_db m " +
              "JOIN mob_drop md ON m.id = md.mob_id " +
              "JOIN item_db i ON md.item_id = i.id " +
              "WHERE m.id = " + .@mob_id + ";", 
              .@mob_id, .@mob_name$, .@item_id, .@item_name$, .@drop_rate);

    if (getarraysize(.@mob_id) > 0) {
        mes "Mob: " + .@mob_name$;
        for (.@i = 0; .@i < getarraysize(.@mob_id); .@i++) {
            mes "Item: " + .@item_name$[.@i] + " (ID: " + .@item_id[.@i] + "), Drop Rate: " + .@drop_rate[.@i] + "%";
        }
    } else {
        mes "No drops found for this mob ID.";
    }
    close;
}

 

[Louis T Steinhil]

On 6/3/2024 at 2:15 PM, Outlook said:

Like this.

share the query_sql pls?

Edited June 4, 2024 by Louis T Steinhil
additional

[Louis T Steinhil]

Please share query_sql? and also how did you manage to add vip status icon? Haven't managed to do that even tho i search all the previous ones that have reported they can't add it also.

[Louis T Steinhil]

ah okay no worries. sorry for what happened tho

[Louis T Steinhil]

On 6/3/2024 at 3:14 AM, Poring King said:

Get some idea with this . I just write it simple 

prontera,150,150,5	script	QueryMobDrops	1_M_MERCHANT,{
    // NPC Dialogue
    mes "Enter the Mob ID:";
    input .@mob_id;

    // SQL Query
    query_sql("SELECT m.id, m.name_japanese, i.id, i.name_english, md.rate " +
              "FROM mob_db m " +
              "JOIN mob_drop md ON m.id = md.mob_id " +
              "JOIN item_db i ON md.item_id = i.id " +
              "WHERE m.id = " + .@mob_id + ";", 
              .@mob_id, .@mob_name$, .@item_id, .@item_name$, .@drop_rate);

    if (getarraysize(.@mob_id) > 0) {
        mes "Mob: " + .@mob_name$;
        for (.@i = 0; .@i < getarraysize(.@mob_id); .@i++) {
            mes "Item: " + .@item_name$[.@i] + " (ID: " + .@item_id[.@i] + "), Drop Rate: " + .@drop_rate[.@i] + "%";
        }
    } else {
        mes "No drops found for this mob ID.";
    }
    close;
}

 

It kinda work if it's like quering for both informations but if I add it into saving the cards into characters and making it a quest it somehow not saving at all. So i just access the item_db_re for now.

				.@sql_query$ = "SELECT `id` FROM `item_db_re` WHERE `type` = 'Card' AND `name_english` LIKE '%Card%' ORDER BY RAND() LIMIT 5";
				.@nb = query_sql(.@sql_query$, .@id[.@i]);

 

Edited June 5, 2024 by Louis T Steinhil