Fetching item id's

[blackdragonsdg]

Need a bit of help with this script. I want to take a list of item names from a text file then fetch there associated item id's from the database then output both the item names and item id's into a specific format as indicated below.


use DBI;
my $db = "peq";
my $user = "root";
my $pass = "password";
my $host = "localhost";
my $source = "DBI:mysql:database=$db;host=$host";
my $dbh = DBI->connect($source, $user, $pass) || die "Could not create db handle\n";

sub get_item {
my $item_name;
my $item_id;
my $sth = $dbh->prepare("SELECT * FROM items WHERE `name`=$item_name and `id`=$item_id");
$sth->execute();

########Something along the lines of fetch() is suppose to go here
########not sure what though
}

open (FILE, 'data.txt');
while (<FILE>) {
chomp;

########take item name from file pass it through get_item() retrieving item id
########then take the item name and the fetched item id
########outputting it into the desired format

open (MYFILE2, '>>data2.txt');
print MYFILE2 "INSERT INTO `lootdrop_entries` (`lootdrop_id`, `item_id`, `item_charges`, `equip_item`, `chance`) VALUES (x, $id, 1, 0, z); /* $name */\n";
}
close (MYFILE);
close (MYFILE2);

[trevius]

Your request isn't really a quest related question. It is more of a general perl or scripting question. I am sure it can be done easy enough how you are wanting to do it.

You could probably just save yourself some time and use notepad++ to edit your item name list into queries for the item id SELECT. Then, do the same to make the results into queries to insert into the loot tables. You can hold ALT then click and drag up or down to select a column to type in for all rows selected. This means you can select the row in front of your column of item names, and type:

SELECT * FROM items WHERE `name`="

Then put your cursor at the left of the first line and click Macro->Start Recording. Then hit the END key on your keyboard. Then type the following:

");

Then hit the DOWN arrow on your keyboard. Then click Macro->Stop Recording. Then Macro->Run a Macro Multiple Times....

Next, select the radio button for "Run until the end of file" and click Run. That should make all lines look like a query. Sometimes you might get a weird character when you run macros, but you can just do a find/replace on that character to remove it if needed.

At that point, you can just run your queries from that list and do the same again with the query results. Once you get used to using notepad++, stuff like that gets really quick and easy to do.

Otherwise, if you are set on getting the script working, here is an example plugin I use on my server to query the DB. Should be able to use it to figure out what you are wanting:

# Usage: plugin::SetCharVarByName(CharName, VarName, VarValue);
sub SetCharVarByName {

my $CharName = $_[0];
my $VarName = $_[1];
my $VarValue = $_[2];

use DBI;
use DBD::mysql;

$tablename = "charvars";

# Set Database Connection
my $connect = DBConnect();

if (plugin::GetCharVarByName($CharName, $VarName))
{
# Create the Query
$query = "UPDATE $tablename SET value = '$VarValue' WHERE name = '$CharName' AND variable = '$VarName'";
}
else
{
# Create the Query
$query = "INSERT INTO $tablename (name, variable, value) VALUES('$CharName', '$VarName', '$VarValue')";
}
# Prepare and Execute the Query
$query_handle = $connect->prepare($query);
$query_handle->execute();
$connect->disconnect();

return 1;

}

sub DBConnect {

# Configuration Settings
my $database = "dbname";
my $host = "192.168.1.100";
my $port = "3306";

# Set Connect String
my $dsn = "dbi:mysql:$database:$host:$port";
my $user = "dbusername";
my $pw = "password";

# Set Database Connection
my $connect = DBI->connect($dsn, $user, $pw);
return $connect;

}

[blackdragonsdg]

Didn't know where exactly to post this question as it doesn't really fit anywhere. I'll give the notepad++ thing a try. Looking up one hundred plus item id numbers manually is not exactly fun so I was attempting to automate it.

[blackdragonsdg]

Well notepad++ is an interesting program with lots of potential uses but for what I am doing I think i can create loottables just as fast without it.

I took the script segment trevius posted and started trying to adapt it to what I was working on. While I have made some progress I am still unable to get the id to transfer through. Here is where I am at now

#Item id look up by item name

$tablename = "items";
$columnname = "Name";

use DBI;
use DBD::mysql;

# Set Database Connection
my $connect = DBConnect();

open (FILE, 'data.txt');
while (<FILE>) {
chomp;

if ($_)
{
$query = "Select id from $tablename where $columnname = '$_'";

}

# Prepare and Execute the Query
$query_handle = $connect->prepare($query);
$query_handle->execute();

open (MYFILE2, '>>data2.txt');
print MYFILE2 "INSERT INTO `lootdrop_entries` (`lootdrop_id`, `item_id`, `item_charges`, `equip_item`, `chance`) VALUES (x, $id, 1, 0, z); /* $_ */\n";
}
close (MYFILE);
close (MYFILE2);

sub DBConnect {

# Configuration Settings
my $database = "peq";
my $host = "localhost";
my $port = "3306";

# Set Connect String
my $dsn = "dbi:mysql:$database:$host:$port";
my $user = "root";
my $pw = "password";

# Set Database Connection
my $connect = DBI->connect($dsn, $user, $pw);
return $connect;
}

I am not sure how to pull the id values out of the select statement. Only reason I got the name to go through was because of a default to $_ when reading the file text. Any hints would be welcomed.

[lerxst2112]

You should probably read the docs for DBI. http://www.perl.com/pub/1999/10/DBI.html

[blackdragonsdg]

Heh naturally I figured most of it out not long after my last post. Thanks for the help trevius and thanks for the link lerxst2112 maybe that will help in finding out why the script doesn't like ' in item names. Easy one to work around though.