How to show SQL values in an NPC

[Panny]

Hello, i have a SQL table called event and in there is fields called id and name.

How do i implament it into an NPC so it shows all the names stored in the 'event' database? Also maybe in Alphabetical Order?.

Example;

Name 1

Name 2

Name 3

etc

Edited December 17, 2012 by Panny

[Emistry]

.@sql = query( "SELECT `id`,`name` FROM `yourtable` ",.@id,.@name$ );
for( .@i = 0; .@i < .@sql; .@i++ )
 mes "ID : "+.@id[.@i]+" & Name : "+.@name$[.@i];

refer query_sql

[Panny]

I dont want it to show the ID so would it be this?

prontera,150,83,4   script   Event Names   814{
set .@sql = query( "SELECT `name` FROM `event` ",.@name$ );
mes "[Event Names]"
for(set .@i = 0; .@i < .@sql; .@i++ )
mes "Name : "+.@name$[.@i];
close;
}

Edited December 17, 2012 by Panny

[Brian]

Something like this:

prontera,150,83,4	script	Event Names	814,{
query_sql "SELECT `name` FROM `event` ORDER BY `name` ASC", .@name$;
mes "[Event Names]";
for (set .@i,0; .@i<getarraysize(.@name$); set .@i,.@i+1)
	mes "Name : " + .@name$[.@i];
close;
}

I fixed the syntax of query_sql, added ORDER BY to sort the names, and added a missing semi-colon.

[Panny]

Something like this:

prontera,150,83,4    script    Event Names    814,{
   query_sql "SELECT `name` FROM `event` ORDER BY `name` ASC", .@name$;
   mes "[Event Names]";
   for (set .@i,0; .@i<getarraysize(.@name$); set .@i,.@i+1)
       mes "Name : " + .@name$[.@i];
   close;
}

I fixed the syntax of query_sql, added ORDER BY to sort the names, and added a missing semi-colon.

Thanks Brian, tried your script however the NPC loads but it isnt clickable.

What may cause this?

[AnnieRuru]

dunno why but if you couldn't understand Brian script ...

makes me feels like you need a little bit spoon feed already

/*
// creating table
create table event (
id int(11) primary key auto_increment,
name varchar(23),
key (name)
) engine = myisam;

// debugging script in test server
insert into event
select null, name from `char`;

// show the table
select * from event;
*/

prontera,152,179,4    script    Event Names    814,{
   query_sql "select count(1) from event", .@total;
   while (1) {
       mes "Congratulations to these players !";
       mes "Total "+ .@total +" players, Page no. "+( .@current_page +1 )+" =";
       .@nb = query_sql( "select name from event order by name limit "+ .page +" offset "+ .page * .@current_page, .@name$ );
       for ( .@i = 0; .@i < .@nb; .@i++ )
           mes .@name$[.@i];
       next;
       if ( select ( "Next Page", "Previous Page" ) == 1 ) {
           if ( .page * ( .@current_page +1 ) < .@total )
               .@current_page++;
           else {
               mes "the end of the page";
               close;
           }
       }
       else {
           if ( .@current_page )
               .@current_page--;
           else {
               mes "this is the 1st page";
               close;
           }
       }
   }
   close;
OnInit:
   .page = 2; // display 2 players per page
   end;
}

edit: forgot to index the `name` field

if you don't understand what are the commands that I've used in this script, always feel free to ask and criticize my methods

Edited December 23, 2012 by AnnieRuru