Untitled

<html>
    <head>
        <title>Update Star Trades Database</title>
    </head>
    <body>
        <div style="width:100%; text-align:center;">
        <?php
        if(!isset($_POST['submit'])){ ?>
                <form action="<?php echo $_SERVER['PHP_SELF'] ; ?>" method="post" enctype="multipart/form-data">
                    <label for="file">Filename:</label>
                    <input type="file" name="file" id="file" />
                    <input type="submit" name="submit" value="Submit" />
                </form>

        <? } else {
            echo "Processing File......";
            $file = $_FILES["file"]["tmp_name"];
            echo "$file";
        }
        ?>
        <hr>
        </div>
    </body>

</html>

<?php
/*
 * Used to update the startrades database
 *
 *  First we do some very basic data sanitization,
 *
 * NDM 28/06/2011
 *
 */

/* Only report simple running errors */
error_reporting(E_ERROR | E_WARNING | E_PARSE);


if(isset($_POST['submit'])){
    /* Connect to the SQL server */
    $db = mysql_connect("localhost","root","4LE0ia7roa");
    if (!$db){ die('Could not connect: ' . mysql_error()); }

    mysql_select_db("startrades", $db);

    /* Read the file */
    $lines = file($file);
    $count['count'] = 0;
    $count['skipped'] = 0;
    $count['success']['num'] = 0;

    /* foreach line in the file, assign it to $line, and keep a line number */
    foreach ($lines as $line_num => $line) {
        $line_num++; /* Incease the line num by 1, php is 0 based, excel is not. */
        $line = explode(",",$line); /* Split the file by a ',' into an array in $line */

        /* If we don't have a company name, its a blank line, continue to next loop */
        if ($line[2] == ""){ $count['empty']++ ; continue;}

        /* if we don't have a Publicaion, use the last one, else usee this line's one */
        if (trim($line[0]) == ""){
            $inserts[$line_num]['pub'] = $last_pub ;
        }else{
            $inserts[$line_num]['pub'] = $line[0];
            $last_pub = $line[0];
        }

        /* if we don't have a classification, use the last one, else usee this line's one */
        if ($line[1] == ""){
            $inserts[$line_num]['class'] = $last_class ;
        }else{
            $inserts[$line_num]['class'] = $line[1];
            $last_class = $line[1];
        }

        /* build an array to loop through of good data, this is for our insert later */
        $inserts[$line_num]['name']         = trim(mysql_real_escape_string($line[2]));
        $inserts[$line_num]['special_tag']  = trim(mysql_real_escape_string($line[3]));
        $inserts[$line_num]['features']     = trim(mysql_real_escape_string($line[4]));
        $inserts[$line_num]['contact']      = trim(mysql_real_escape_string($line[5]));
        $inserts[$line_num]['land_line']    = trim(mysql_real_escape_string($line[6]));
        $inserts[$line_num]['mobile']       = trim(mysql_real_escape_string($line[7]));
        $inserts[$line_num]['website']      = trim(mysql_real_escape_string($line[8]));
        $inserts[$line_num]['email']        = trim(mysql_real_escape_string($line[9]));
        $inserts[$line_num]['license']      = trim(mysql_real_escape_string($line[10]));
    }


    /*
     *  Loop through our insert array, and do the following.
     *
     *  1) Insert into goods table -- Returning ID
     *  2) Insert into Goods_cat table -- Including ID
     *  3) Insert/Update sng_goods_log
     *  4) Insert/Update sng_goods_properties
     *  5) Insert/Update sng_goods_suburb
     *  6) Insert/Update sng_good_suburb_range
     *
     */

     foreach ($inserts as $insert_num => $insert){
        /* Do the complicated and retarded insert into jim's fucked db scheme */

        $insert_goods = " INSERT INTO sng_goods ( `user_id`,`modify_datetime`,`cat_id`,`goods_sn`,`goods_name`,`subdomain_name`,`source_paper_id`,`promote_start`,`promote_end`,`goods_brief`,`is_real`,`is_on_sale`,`is_confirmed`,`is_alone_sale`,`is_linked`,`can_handsel`,`add_time`,`is_promote`,`last_update`,`duration_years`,`goods_desc`,`seller_note`,`payment_name`) VALUES ('0',current_timestamp(),'0','','$insert[name]','','0',current_timestamp(),date_add(current_date,INTERVAL 1 YEAR),'$insert[special_tag] $insert[features]','1','1','1','1','1','1',UNIX_TIMESTAMP(current_date),'1',UNIX_TIMESTAMP(current_date),'1','','','');";

        /* If we cannot insert into the goods table, we cannot continue with this line, report error and continue with next */
        if(!mysql_query($insert_goods)){
            $errors[$insert_num]['sql_error'] = mysql_error();
            $errors[$insert_num]['sql'] = $insert_goods;
            $count['skipped']++;
            continue;
        }

        /* Get the last insert id */
        $goods_id = mysql_insert_id();

        /* Get the category ID, and name, or go to next row */
        $select_goods_cat = "SELECT * from sng_category WHERE cat_name='$insert[class]'";
        $result_goods_cat = mysql_query($select_goods_cat);
        $row_goods_cat    = mysql_fetch_row($result_goods_cat);

        if(!$row_goods_cat){
            $errors[$insert_num]['message'] = "Could not find category '$insert[class]'";
            $errors[$insert_num]['sql'] = $select_goods_cat;
            $count['skipped']++;
            continue;
        }

        /* Insert into the goods_cat table */
        $insert_goods_cat = "INSERT INTO sng_goods_cat ( `goods_id`,`cat_id`,`cat_name` ) VALUES ( '$goods_id', '" .mysql_real_escape_string($row_goods_cat[0]) ."', '" .mysql_real_escape_string($row_goods_cat[1]) ."');";

        /* If we cannot insert goods_cat, error and continue */
        if(!mysql_query($insert_goods_cat)){
            $errors[$insert_num]['sql_error'] = mysql_error();
            $errors[$insert_num]['message'] = $row_goods_cat;
            $errors[$insert_num]['sql'] = $insert_goods_cat;
            $count['skipped']++;
            continue;
        }

        $insert_goods_log = "INSERT INTO sng_goods_log (`log_time`, `goods_id`, `admin_name`, `log_info`, `ip_address`) VALUES (UNIX_TIMESTAMP(current_date), $goods_id, 'Batch Uploaded', 'Batch Uploaded', '127.0.0.1')" ;

        /* If we cannot insert goods_log, error and continue */
        if(!mysql_query($insert_goods_log)){
            $errors[$insert_num]['sql_error'] = mysql_error();
            $errors[$insert_num]['sql'] = $insert_goods_log;
            $count['skipped']++;
            continue;
        }

        /* Explode the name into first_name and last_name */
        $exploded_name  = explode(" ", $inserts[$line_num]['contact']);
        $first_name     = $exploded_name[0];
        $last_name      = isset($exploded_name[1]) ? $exploded_name[1] : "";

        $insert_goods_properties = "INSERT INTO sng_goods_properties (`goods_id`, `user_id`, `first_name`, `last_name`, `phone_number`, `mobile_number`, `goods_email`, `goods_website`, `fax_number`, `hear_about_us`, `yeas_in_business`, `particular_type`, `goods_awards`, `goods_brands`, `Pensionser_Discounts`, `Free_Quotes`, `other_information`) VALUES    ('$goods_id', '0', '$first_name', '$last_number', '$insert[land_line]', '$insert[mobile]', '$insert[email]', '$insert[website]', '', '', '', '', '', '', '0', '0', '')" ;

        /* If we cannot insert goods_propertirs, error and continue */
        if(!mysql_query($insert_goods_properties)){
            $errors[$insert_num]['sql_error'] = mysql_error();
            $errors[$insert_num]['sql'] = $insert_goods_properties;
            $count['skipped']++;
            continue;
        }


        /* Lookup the publication, select the post code, and do something with it */
        $select_post_code = "SELECT * from sng_post_code_pub WHERE publication = '" .$insert['pub'] ."';";
        $result_post_code = mysql_query($select_post_code);
        $row_post_code    = mysql_fetch_array($result_post_code) ;
        if(mysql_num_rows($result_post_code) > 0){
                $select_post_code1  = "SELECT * FROM sng_post_code WHERE post_code = '$row_post_code[post_code]';";
                $result_post_code1  = mysql_query($select_post_code1);
                $row_post_code1     = mysql_fetch_row($result_post_code1);

                /* Make sure we got the post code, or continur next line */
                if(mysql_num_rows($result_post_code1) > 0){
                    $insert_goods_suburb = "INSERT INTO sng_goods_suburb (`goods_id`, `suburb_id`, `suburb_name`, `post_code`) VALUES ('$goods_id', '$row_post_code1[0]', '" .mysql_real_escape_string($row_post_code1[4]) ."','$row_post_code1[1]');";
                    if(!mysql_query($insert_goods_suburb)){
                        $errors[$insert_num]['error'] = mysql_error();
                        $errors[$insert_num]['sql'] = $insert_goods_suburb;
                        $count['skipped']++;
                        continue;
                    }
                }else{
                        $errors[$insert_num]['error'] = "Could not lookup Post Code";
                        $errors[$insert_num]['message'] = $select_post_code1;
                        $errors[$insert_num]['sql'] = $select_post_code;
                        $count['skipped']++;
                        continue;
                }
        }else{
            $errors[$insert_num]['error'] = "Could not look up publication: $insert[pub]";
            $errors[$insert_num]['sql'] = $select_post_code;
            $count['skipped']++;
            continue;
        }


        /* Increase the success count */
        $count['success']['num']++ ;
        $count['success']['rows'][$insert_num] = "Success" ;
     }
    /* Delete the file we uploaded, were done */
    unlink($file);

    /* Close the mysql connection, were done */
    mysql_close($db);

}

/* Do some reporting on what's happened */
if($errors){
    printf("The following %s lines imported OK: <br>", $count['success']['num']);
     foreach($count['success']['rows'] as $line_number => $success_row){
        echo "$line_number, ";
     }
     echo "<h2> Here are the errors:</h2>";
     echo "<h3> We have have skipped $count[empty] empty rows, and have $count[skipped] errors.";
     echo "<ul>";
     foreach ($errors as $error_num => $error){
        echo "<li>";
            $string = "<b>" .$error_num .":</b>&nbsp" ;
            $string .= isset($error['message']) ? $error['message'] ." ": "" ;
            $string .= isset($error['error']) ? $error['error'] ." ": "" ;
            //$string .= isset($error['sql']) ? $error['sql'] ." ": "" ;
            echo  $string;
        echo "</li>";
     }
     echo "</ul>";
}

?>