flexfanatic 0 Posted February 19, 2014 Report Share Posted February 19, 2014 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)}\');") } Quote Link to post Share on other sites
blumi40 222 Posted February 19, 2014 Report Share Posted February 19, 2014 as mysql column u only give em (title) but if u have 5 columns u should fill 5 columnsINSERT INTO mytable(col1,col2,col3,col4,col5)value ("val1","val2","val3","val4","val5") Quote Link to post Share on other sites
flexfanatic 0 Posted February 19, 2014 Author Report Share Posted February 19, 2014 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.pnghttp://s12.postimg.org/vzhzx2er1/mysqlerror2.png as mysql column u only give em (title) but if u have 5 columns u should fill 5 columnsINSERT INTO mytable(col1,col2,col3,col4,col5)value ("val1","val2","val3","val4","val5") Quote Link to post Share on other sites
blumi40 222 Posted February 20, 2014 Report Share Posted February 20, 2014 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 Quote Link to post Share on other sites
blumi40 222 Posted February 20, 2014 Report Share Posted February 20, 2014 ah one thing i forgotu 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 Quote Link to post Share on other sites
flexfanatic 0 Posted February 20, 2014 Author Report Share Posted February 20, 2014 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 tagsthat 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 directlyto 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 Quote Link to post Share on other sites
blumi40 222 Posted February 20, 2014 Report Share Posted February 20, 2014 cmon i told u i didnt check the syntax go and search in ubot for table cell and use it on the rigth wayby 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 Quote Link to post Share on other sites
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.