Database tutorial

[xindexer 4]

I spent the last couple of days figuring out how to scrape articles and drop them directly into my database and I thought that I would share what I have learned.

 

I'm going to assume that you have an instance of mysql up and running either locally or on a server somewhere and that you can connect to it.

 

I have a very basic setup with two tables, "articles" and "keywords"

 

The articles table has "ID, keyword, title, article" as columns and the keywords table has "ID, keyword'

 

I have prepopulated the keywords table with several thousand keywords to pull from

 

First up - query the database for the keyword and put it into a variable

 

plugin command("DatabaseCommands.dll", "connect to database", "server=localhost;uid=****; pwd=****; database=articles; port=3306; pooling=false") {
 plugin command("DatabaseCommands.dll", "query with results", "SELECT keyword from keywords order by rand() limit 1", #keyword)
}

 

 

The trick to getting data back into the database is that you need to escape your input. In other words you need to change ' to \' and " to \"

 

This took me a bit to figure out the syntax so here it is:

 

set(#article, $replace(#article, "'", "\\\'"), "Global")
set(#article, $replace(#article, "\"", "\\\""), "Global")

 

notice that it takes three \\\ in order to get one \ on the output - this has to do with special characters (you also need to escape the " in the replace line in order for the system to be able to find the " because both " and \ are special characters)

 

Finally, to insert into the database - you need to use this format {#keyword}

 

plugin command("DatabaseCommands.dll", "connect to database", "server=65.39.148.178;uid=writer; pwd=owgn2wby;database=articles; port=3306; pooling=false") {
 plugin command("DatabaseCommands.dll", "query","INSERT INTO articles (keywordID,title,article) VALUES ('{#keyword}','{#title}','{#article}');")
}

 

hope this helps somebody out in the future

[Legend 181]

thanks!

[Lauren 5]

Welcome to the forum xindexer

[VaultBoss 310]

First up - query the database for the keyword and put it into a variable

 

plugin command("DatabaseCommands.dll", "connect to database", "server=localhost;uid=****; pwd=****; database=articles; port=3306; pooling=false") {
 plugin command("DatabaseCommands.dll", "query with results", "SELECT keyword from keywords order by rand() limit 1", #keyword)
}

 

I'm a bit puzzled... My UBot Studio only accepts tables as results containers in the "query with results" node... Looks like you used a variable there (#keyword)

I'm wondering how have you done that?

 

Personally, I am using a temporary table with a single cell, but that is only an artifice, to be able to have things done quickly, w/o asking Eddie to code extra features yet (although it would be nice, Eddie...)

 

As a side note, in the code you put above, I would edit out the password if I were you... just for the sake of being over-cautious, but still...

 

--------

 

In regards to the escaping of characters, for people using ANY SQL editor... once they built their query, they could drop it in Notepad++ and have everything escaped automatically (under the TestFX Characters TAB/Option there)

 

HTH...

[Ubot_guy 13]

Hey VB, I'm doing the same thing "Row 0 Column 0" AKA single result.

[UBotBuddy 331]

I agree VB. He is using that variable in error. Will not work in the manner that he is using it. If it does then it is a fluke. I requested that they change this back during our Beta testing but it as not been changed yet.

[VaultBoss 310]

I requested that they change this back during our Beta testing but it as not been changed yet.

 

I vote for that too!

 

+1