[RESOLVED] screen scraping and submitting info to sql database

[funnylookin]

hey, i'm trying to screenscrape this webpage http://www.huds.harvard.edu/foodpro/center_frame.asp?naFlag=1&sName=HARVARD+UNIVERSITY+DINING+SERVICES&locationNum=05&locationName=Hot+Entrees%2C+Starches%2C+Bean%2FGrain+and+Vegetables%3C%2Ffont%3E%3C%2Fa%3E%3Cbr%3E%3Cfont%3E%26nbsp%3C%2Ffont%3E%3Ca%3E%3Cfont%3E and i want to store the text for the meals in a sql database

so several questions:
1) i have split up each line into an array, but i'm pretty sure all the html is still in there - say a particular element of an array has html in it, and some others have the text i want. while its not 100% critical to get rid of it, i would prefer to in order to save space and make life easier in the long run. any ideas?

2) i'm getting a Parse error: syntax error, unexpected T_STRING on line 27 which is

        $insert = sprintf("INSERT INTO 'menu' ('%d') VALUES ('%s')", $i,
        $contents[$i]);

my guess is that it has something to do with %d for the row name, but i'm not sure. i have it set up this way so i can put each element of the array in a different row - i don't think this is really the best set-up efficiency-wise, but i just want the easiest way to do it that will be easily searchable afterwards.

here is my whole code, any help would be much appreciated!

<?php
    //require common code (this connects me to database)
    require_once("inc/common.inc");

//open HUDS website for reading
$url =
"http://www.huds.harvard.edu/foodpro/center_frame.asp?naFlag=1&sName=HARVARD+UNI$
$handle = fopen($url, "r");
//grab contents from webpage
$contents = file_get_contents($url);
echo "$contents";
fclose($handle);

//split into array
$contents = explode("\n", $contents);

for($i = 0; $i < strlen($contents); $i ++)
{
    $empty = "TRUNCATE TABLE  `menu`";
    //execute query
    $query = mysql_query($empty);
    if (!query)
        apologize("Couldn't empty table);

    $insert = sprintf("INSERT INTO 'menu' ('%d') VALUES ('%s')", $i,
    $contents[$i]);

    if(!$insert)
        apologize("Could not insert %s into table", $contents[$i]);

    //execute query
    $result = mysql_query($insert);
        if(!result)
            apologize("Error! Check primary key.");

?>

[funnylookin]

Ok i figured out the parse error, and a few other syntax errors. Now however, it won't submit into the database and i get the apology message "Error, check primary key" meaning that $result is null and didn't go through. i can't seem to figure out why this is happening. sorry i'm can't remember how to fix all these newbie problems, i guess i should have posted this there.

[bradgrafelman]

First thing you should do is post a fresh copy of your code since you've made changes to it.

Next, the problem is that the INSERT query failed. When a query fails, you should always output the query as well as the MySQL error message. For example, this is how I executed SQL queries when developing:

$query = 'SELECT ...';
$exec = mysql_query($query) or die('MySQL error: ' . mysql_error() . "<hr>Query: $query");

You should implement something similar into your script and let us know both the error message and the query sent.

[funnylookin]

i just changed the method of exploding $contents to attempt to get only the string of text for the meals that i want, but i'm not sure it works - when i echo $contents I only get "Array". This returns an error when trying to insert to mysql database: MySQL error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0') VALUES ('Array')' at line 1.

"

<?php
    //require common code (this connects me to database)
    require_once("inc/common.inc");

//open HUDS website for reading
$url =
"http://www.huds.harvard.edu/foodpro/center_frame.asp?naFlag=1&sName=HARVARD+UNIVERSITY+DINING+SERVICES&locationNum=05&locat$
$handle = fopen($url, "r");
//grab contents from webpage
$contents = file_get_contents($url);
echo "$contents";
fclose($handle);

/*
    //split into array
    $contents = explode("\n", $contents);


for($i = 0; $i < strlen($contents); $i ++)
{
    $contents = explode("<li>", $contents);
    $contents = explode ("<img src", $contents);
    echo "$contents";

    $empty = "TRUNCATE TABLE  `menu`";
    //execute query
    $query = mysql_query($empty);
    if (!$query)
        apologize("Couldn't truncate!");
    echo "$i";

    $insert = sprintf("INSERT INTO `menu` ('%d') VALUES ('%s')", $i,
    $contents[$i]);

    if(!$insert)
        apologize("Could not insert %s into table", $contents[$i]);

    //execute query
    $result = mysql_query($insert) or die('MySQL error: ' . mysql_error() .
    "<hrQuery: $result") ;
    if(!$result)
        apologize("Error! Check primary key.");

}
?>

alternatively i was thinking about using a real expressions pattern. here is my attempt at writing one: when i echo it is prints ([a-zA-Z0-9,()\s]*) -- so am i using echo wrong, or does this not work? i'm typing echo "$temp_pattern";

$temp_pattern = "/<b>BREAKFAST ENTREES</b> <ul>
<li>([a-zA-Z0-9,\(\)\s]*)</li>/";

the site i'm scraping is http://www.huds.harvard.edu/foodpro/center_frame.asp?naFlag=1&sName=HARVARD+UNIVERSITY+DINING+SERVICES&locationNum=05&locationName=Hot+Entrees%2C+Starches%2C+Bean%2FGrain+and+Vegetables%3C%2Ffont%3E%3C%2Fa%3E%3Cbr%3E%3Cfont%3E%26nbsp%3C%2Ffont%3E%3Ca%3E%3Cfont%3E

thanks!

[bradgrafelman]

1. I think your script got cutoff there; as you'll notice by the way the syntax highlighting goes haywire, the $url string is never ended.

2. The error in your SQL statement is that you have '0' in the place of a column list. For one thing, a column list can't include a string value, and for another, I highly doubt you named your column 0.

3. The $temp_pattern variable is simply a string, so of course echo'ing the contents of a string will simply show you the string as it is. If you're trying to match a string using regular expressions, then you'll have to pass this pattern onto a function that actually does something with it, such as [man]preg_match/man.

[funnylookin]

1. I think your script got cutoff there; as you'll notice by the way the syntax highlighting goes haywire, the $url string is never ended

The url works in the code, though you can't see the whole thing when I copy it. The whole url is: http://www.huds.harvard.edu/foodpro/center_frame.asp?naFlag=1&sName=HARVARD+UNIVERSITY+DINING+SERVICES&locationNum=05&locationName=Hot+Entrees%2C+Starches%2C+Bean%2FGrain+and+Vegetables%3C%2Ffont%3E%3C%2Fa%3E%3Cbr%3E%3Cfont%3E%26nbsp%3C%2Ffont%3E%3Ca%3E%3Cfont%3E

I've rearranged the SQL table to have two columns, one uid which auto-increments, and one varchar "food" I fixed the syntax and it will now submit. My problem, however is what I am submitting.

I can't seem to correctly split the information from the website into arrays. I first grab the contents from the webpage. When I echo it, I get the entire webpage back, except for the images.

I am using the explode function to try to isolate the food titles that I want to store. However, what is being saved is "A0" then "A1" etc. What am I doing wrong here?

Here is a copy of my entire code

<?php
    //require common code (this connects me to database)
    require_once("inc/common.inc");

//open HUDS website for reading
$url =
"http://www.huds.harvard.edu/foodpro/center_frame.asp?naFlag=1&sName=HARVARD+UNIVERSITY+DINING+SERVICES&locati$
$handle = fopen($url, "r");
//grab contents from webpage
$contents = file_get_contents($url);
echo "$contents";
fclose($handle);

$empty = "TRUNCATE TABLE  `menu2`";
//execute query
$query = mysql_query($empty);
if (!$query)
    apologize("Couldn't truncate!");

for($i = 0; $i < strlen($contents); $i ++)
{
    $contents[$i] = explode("<li>", $contents);
    $contents[$i] = explode ("/n", $contents);

    echo "$contents[$i]";

    echo "$i";


    $insert = "INSERT INTO `menu2` (`food`) VALUES ('$contents[i]')";


    if(!$insert)
        apologize("Could not insert %s into table", $contents[$i]);

    //execute query
    $result = mysql_query($insert) or die('MySQL error: ' . mysql_error() .
    "<hrQuery: $result") ;
    if(!$result)
        apologize("Error! Check primary key.");

}
//redirect home
redirect("index.php");



?>

[bradgrafelman]

On that webpage, what exactly are you trying to grab? Just all of the items listed, i.e. Bacon, Eggs Cooked to Order, etc. ? If so, just use [man]preg_match_all/man with a pattern such as:

$pattern = '/(?<=<li>).*?(?=[\r\n])/i';

This would give you an array that looks like:

Array ( [0] => Array ( [0] => Bacon [1] => Eggs Cooked to Order [2] => Hard Cooked Eggs [3] => Oatmeal Steel Cut [4] => Scrambled Eggs [5] => The Breakfast Sandwich [6] => Assorted Bagels [7] => Cranbrry Orange Nut Muffin [8] => Multi Grain Waffles [9] => Strawberry Sauce [10] => Warmed Pancake Syrup [11] => Whipped Topping [12] => Chicken Florentine Soup [13] => Indian Corn Soup [14] => Fusilli Primavera [15] => Philly Chicken & Cheese [16] => French Fries Straight Cut [17] => Fresh Spinach w/ Garlic [18] => Blueberry Squares [19] => Sugar Cookie [20] => Tuscan Chickpeas [21] => Naan Bread [22] => Cheese Ravioli w/ Sauce [23] => Fresh Beef Brisket [24] => Roasted Red Bliss Potatoes [25] => Peppered Turnips [26] => Yellow Cake w/ Straw Frosting [27] => White and Wheat Rolls [28] => Alu Chole [29] => Brown Basmati Rice [30] => Chopped Scallions [31] => Chopped Tomatoes [32] => Mint & Cilantro Raita [33] => Moonsoons Tamarind Sauce [34] => Palak Paneer [35] => Shredded Carrots [36] => Yellow Split Pea Dal ) )

Thus, you could INSERT the values into your database using a single query like so:

$foods = array_map('mysql_real_escape_string', $matched_array[0]);
$query = 'INSERT INTO menu2 (food) VALUES (\'' . implode('\'), (\'', $foods) . '\')';

[funnylookin]

thank you so much for your help!