Insert INTO MySQL Table

[flexfanatic 0]

I wrote a bot that scrapes product details from a website and adds the information into a Ubot table (five columns). 

 

I'm trying to save this data to a table in MySQL (again five columns).

 

I can connect and run a insert query but it's concatenating all five columns into one for my insert and I'm not sure what I'm doing wrong.

ui text box("URL To Scrape:", #url)
ui text box("Pages To Scrape:", #pages)
ui stat monitor("Product Titles:", $list total(%titles))
ui stat monitor("Product Thumbnails:", $list total(%thumbnails))
ui stat monitor("Product Images:", $list total(%images))
ui stat monitor("Product Description:", $list total(%desc))
ui stat monitor("Price:", $list total(%price))
ui stat monitor("Product URL:", $list total(%produrl))
define Scrape Data {
    clear table(&products)
    clear list(%titles)
    clear list(%thumbnails)
    clear list(%images)
    clear list(%desc)
    clear list(%price)
    clear list(%produrl)
    set user agent("Firefox 6")
    navigate(#url, "Wait")
    wait(5)
    loop(#pages) {
        add list to list(%titles, $scrape attribute(<itemprop="name">, "title"), "Don\'t Delete", "Global")
        add list to table as column(&products, 0, 0, %titles)
        add list to list(%thumbnails, $scrape attribute(<class="productImage">, "src"), "Don\'t Delete", "Global")
        add list to table as column(&products, 0, 1, %thumbnails)
        add list to list(%price, $scrape attribute(<class="qv-price">, "outertext"), "Don\'t Delete", "Global")
        add list to table as column(&products, 0, 2, %price)
        add list to list(%produrl, $scrape attribute(<itemprop="name">, "href"), "Don\'t Delete", "Global")
        add list to table as column(&products, 0, 3, %produrl)
        click(<innertext="NEXT »">, "Left Click", "No")
        wait(6)
    }
    loop($list total(%produrl)) {
        navigate($next list item(%produrl), "Wait")
        add item to list(%images, $scrape attribute(<itemprop="image">, "fullsrc"), "Don\'t Delete", "Global")
        add item to list(%desc, $scrape attribute(<((tagname="div" AND itemprop="description") AND class="box pdp-unit pdp-producttypedesc")>, "innerhtml"), "Don\'t Delete", "Global")
    }
    add list to table as column(&products, 0, 4, %images)
    add list to table as column(&products, 0, 5, %desc)
}
Scrape Data()
plugin command("DatabaseCommands.dll", "connect to database", "server=******;uid=*****; pwd=*****; database=******; port=3306; pooling=false") {
    plugin command("DatabaseCommands.dll", "query", "insert INTO cafepressdata (title) VALUES (\'{$plugin function("TableCommands.dll", "$list from table", &products, "Row", 0)}\');")
}

[blumi40 222]

as mysql column  u only  give em (title) but if u have 5 columns u should fill 5 columns

INSERT INTO mytable
(col1,col2,col3,col4,col5)
value ("val1","val2","val3","val4","val5")

[flexfanatic 0]

That was the first way I had it but it says the row count does not match. The issue is not the amount of columns I'm trying to insert it seems Ubot table is outputting as one cell.

 

http://s12.postimg.org/50e51wsa5/mysqlerror1.png

http://s12.postimg.org/vzhzx2er1/mysqlerror2.png

 

as mysql column  u only  give em (title) but if u have 5 columns u should fill 5 columns

INSERT INTO mytable
(col1,col2,col3,col4,col5)
value ("val1","val2","val3","val4","val5")

 

 

[blumi40 222]

INSERT INTO `yourtable`

(`title`, thumbnail, price, producturl, image, description)

VALUE

(

"table cell(your table,#row,0)",

"table cell(your table,#row,1)",

"table cell(your table,#row,2)",

"table cell(your table,#row,3)",

"table cell(your table,#row,4)",

"table cell(your table,#row,5)"

)

 

should do what u want but syntax not testet on my side.

 

but before u insert it have a look at column 5  and replace the html tags

that can be an issue why the query not works

 

if u dont find the error  do follow....

before u trigger the sql  set it in a var  like

 

#myvar('INSERT INTO `yourtable`

(`title`, thumbnail, price, producturl, image, description)

VALUE

(

"table cell(your table,#row,0)",

"table cell(your table,#row,1)",

"table cell(your table,#row,2)",

"table cell(your table,#row,3)",

"table cell(your table,#row,4)",

"table cell(your table,#row,5)"

)', Global)

 

so u can use the debugwindows to copy the result and run it in phpmyadmin directly

to see whats wrong.  because ubot itself dont gives a errormessage back

 

hope that helps

[blumi40 222]

ah one thing i forgot
u also can use  query with results and give the sql query at the end 
; select last_insert_id()  which returns u the last id mysql was givem to the new dataset

[flexfanatic 0]

 

INSERT INTO `yourtable`

(`title`, thumbnail, price, producturl, image, description)

VALUE

(

"table cell(your table,#row,0)",

"table cell(your table,#row,1)",

"table cell(your table,#row,2)",

"table cell(your table,#row,3)",

"table cell(your table,#row,4)",

"table cell(your table,#row,5)"

)

 

should do what u want but syntax not testet on my side.

 

but before u insert it have a look at column 5  and replace the html tags

that can be an issue why the query not works

 

if u dont find the error  do follow....

before u trigger the sql  set it in a var  like

 

#myvar('INSERT INTO `yourtable`

(`title`, thumbnail, price, producturl, image, description)

VALUE

(

"table cell(your table,#row,0)",

"table cell(your table,#row,1)",

"table cell(your table,#row,2)",

"table cell(your table,#row,3)",

"table cell(your table,#row,4)",

"table cell(your table,#row,5)"

)', Global)

 

so u can use the debugwindows to copy the result and run it in phpmyadmin directly

to see whats wrong.  because ubot itself dont gives a errormessage back

 

hope that helps

 

 

Okay so this has definitely put me in the right direction . I decided to take out the description for now until I get the first 4 columns inserted correctly. With your query I'm able to insert without a error now but it's appending text to the front of the data.

 

http://s7.postimg.org/vbshtw8yz/query1.png

 

http://s7.postimg.org/x4vei7u5n/query2.png

[blumi40 222]

cmon  i told u i didnt check the syntax 
go and search in ubot for table cell and use it on the rigth way

by the way  the row  is the the next dataset  so u need a loop with total table rows and inside a increment...

im willing to help but u have to do your part....what means rtfm and learn