Ravens PHP Scripts: Forums
 

 

View next topic
View previous topic
Post new topic   Reply to topic    Ravens PHP Scripts And Web Hosting Forum Index -> How To's
Author Message
Donovan
Client


Joined: Oct 07, 2003
Posts: 735
Location: Ohio

PostPosted: Tue Dec 11, 2007 8:17 am Reply with quote

I downloaded this from PHPClasses. I need a good CSV import feature to import testing scantrons we get from the university.

In the example page they have the following:
Code:


<?php
include "Quick_CSV_import.php";

//connect to database
mysql_connect("localhost", "root", "1");
mysql_select_db("test"); //your database

$csv = new Quick_CSV_import();


Which I need to change to use the $db connection variable in Nuke.

Could I not just add

require_once("db/db.php");

Then make $db global?

What is the proper way to "port" standalone code for use with Nuke?

The Quick_CSV_Import.php page has the following:

Code:
function import()

  {
    if($this->table_name=="")
      $this->table_name = "temp_".date("d_m_Y_H_i_s");
   
    $this->table_exists = false;
    $this->create_import_table();
   
    if(empty($this->arr_csv_columns))
      $this->get_csv_header_fields();
   
    /* change start. Added in 1.5 version */
    if("" != $this->encoding && "default" != $this->encoding)
      $this->set_encoding();
    /* change end */
   
    if($this->table_exists)
    {
      $sql = "LOAD DATA INFILE '".@mysql_escape_string($this->file_name).
             "' INTO TABLE `".$this->table_name.
             "` FIELDS TERMINATED BY '".@mysql_escape_string($this->field_separate_char).
             "' OPTIONALLY ENCLOSED BY '".@mysql_escape_string($this->field_enclose_char).
             "' ESCAPED BY '".@mysql_escape_string($this->field_escape_char).
             "' ".
             ($this->use_csv_header ? " IGNORE 1 LINES " : "")
             ."(`".implode("`,`", $this->arr_csv_columns)."`)";
      $res = @mysql_query($sql);
      $this->error = mysql_error();
    }
  }


$this I take it is a large array that is filled with all the variables needed throughout the code.

All I would need to do is change each instance of

Code:
@mysql_query


to

Code:
$db->sql_query


or

$db-> whatever the mysql equivalent is.

Correct?

Again is there any preferred way of "porting" standalone code to use with Nuke.

Why is the author using @mysql_query? Are they trying to suppress errors?
 
View user's profile Send private message Visit poster's website ICQ Number
Donovan
PostPosted: Tue Dec 11, 2007 1:22 pm Reply with quote

I've been playing with this. Trying to make it simpler.

I have a cvs file:

Quote:
1,000077193 ,"ABOUSOUD, OMAR ",9,90,35,9,90
1,000390918 ,"ABRAHAM, HEIDI ",9,90,35,9,90
1,000385334 ,"ADAMSHARRISON, ANGELA ",8,80,14,8,80
1,000545516 ,"AGIN, ANNIE ",8,80,14,8,80
1,000388419 ,"AMPONSAH, EUGEN ",10,100,75,10,100
1,000553939 ,"ANKROM, SEAN T",8,80,14,8,80
1,000034194 ,"BALCIK, BRENDE ",9,90,35,9,90
1,000065368 ,"BARNES, NATHAN ",10,100,75,10,100
1,000345478 ,"BARTON, LUCAS ",10,100,75,10,100


And trying to import it with the following:

Code:


if(!defined('TL_ADMIN')) { die("Illegal Access Detected!!!"); }
$modname = "Team_Learning";
include("header.php");
TLAdminMenu();
global $admin_file, $db, $prefix;
$table_name = "_tl_session_grade_import";

if(isset($_POST["Go"]) && ""!=$_POST["Go"]) //form was submitted
{
$file_name = $HTTP_POST_FILES['file_source']['tmp_name'];        

$emptytable = "TRUNCATE TABLE ".$prefix."$table_name";
$emptyresult = $db->sql_query($emptytable);

$sql = "LOAD DATA LOCAL INFILE '".mysql_real_escape_string($file_name)."'
    INTO TABLE ".$prefix."$table_name
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '\"'
    LINES TERMINATED BY '\r\n'";
if (!$sql) {echo("<p>Error performing query: " . mysql_error() . "</p>");}
   $result = $db->sql_query($sql);   
}   


I'm only getting the first record imported to the table, and I don't know why.

Here is the structure of my table:

Code:
#

# Table structure for table `atlas_tl_session_grade_import`
#

  `ReportNumber` int(3) NOT NULL default '0',
  `StudentID` varchar(9) character set latin1 default '0',
  `StudentName` varchar(50) character set latin1 default '0',
  `Total_RS` tinyint(4) NOT NULL default '0',
  `Total_Percent` tinyint(3) NOT NULL default '0',
  `Percentile` tinyint(3) NOT NULL default '0',
  `1st_Subtest_RS` tinyint(3) NOT NULL default '0',
  `1st_Subtest_Percent` tinyint(3) NOT NULL default '0'


The CHARSET is = utf8

Any help is appreciated.
 
montego
Site Admin


Joined: Aug 29, 2004
Posts: 9453
Location: Arizona

PostPosted: Tue Dec 11, 2007 6:22 pm Reply with quote

BTW, take a look at your IF statement again... the "query" hasn't happened yet and $sql really is just a string, not a db connection handle.

But, the sql_query($sql) should still execute.

Wrap the if around the actual $result line so you can see the error that is being produced (at least I suspect there is an error, but you are not trapping for it).

_________________
Only registered users can see links on this board! Get registered or login!
Only registered users can see links on this board! Get registered or login! 
View user's profile Send private message Visit poster's website
Donovan
PostPosted: Wed Dec 12, 2007 9:24 am Reply with quote

Well I kinda did this my own way, which of course is a way to learn. Smile

My issue now is in two parts.

I first create the import table, import the csv file, alter the table and add a field then try to update the table and add a Session_ID to the table.

I can't get the Session_ID to write to the table even through it is available thru a _GET as it is passed in the url.

The second part is a field called StudentID varchar(9) which is a University ID (UID) used on campus. The students enter this on the scantron when they take a test. It is highly likely they will enter the wrong number or maybe only the last 4 of their UID. The scantron is fed into a machine that records the test and spits out a grade.

The format of the csv looks like this.

Quote:

1,000077193 ,"ABOUSOUD, OMAR ",9,90,35,9,90
1,000390918 ,"ABRAHAM, HEIDI ",9,90,35,9,90
1,000385334 ,"ADAMSHARRISON, ANGELA ",8,80,14,8,80
1,000545516 ,"AGIN, ANNIE ",8,80,14,8,80
1,000388419 ,"AMPONSAH, EUGEN ",10,100,75,10,100


The 9 character number is the StudentID or UID. Sometimes I will see a StudentID in this file as

Quote:
xxxx75481


The UID stored in my students table looks like this

Quote:

U00025856
U00020784
U00518132
etc


In the imported table from the scantron I need to trim leading x's from the variable.

Code:
SELECT TRIM(LEADING 'x' FROM 'StudentID');


...and replace with zeros.

I need to update the UID in the imported table and replace the first character with a U

I think I can isolate the first character with a substring
Code:


SELECT SUBSTRING('StudentID',1);


Or something like that. Then somehow append a capital U to the beginning.

Then do a match for StudentID from the imported table to my student table UID and if not found set a flag in that record for a needed correction. Then try to match on `StudentName` varchar(50) to my student table Last_Name with a LIKE to see if I get a match.
Code:


<?php
/************************************************************************/
/* Academic Team Learning & Assessment System                           */
/* ==========================================                           
/************************************************************************/
global $admin_file, $db, $prefix;
if(!defined('TL_ADMIN')) { die("Illegal Access Detected!!!"); }
$modname = basename(str_replace("/admin", "", dirname(__FILE__)));
require_once("mainfile.php");
include("header.php");
TLAdminMenu();
$table_name = "_tl_session_grade_import";
$Session_ID = $_GET['Session_ID'];
if(isset($_POST["Go"]) && ""!=$_POST["Go"]) //form was submitted
{
$file_name = $HTTP_POST_FILES['file_source']['tmp_name'];
//Create the Import CSV table
$sql   = "CREATE TABLE IF NOT EXISTS ".$prefix."$table_name (
  `ReportNumber` int(3) NULL,
  `StudentID` varchar(9) NULL,
  `StudentName` varchar(50) NULL,
  `Total_RS` tinyint(4) NULL,
  `Total_Percent` tinyint(3) NULL,
  `Percentile` tinyint(3) NULL,
  `Subtest_RS` tinyint(3) NULL,
  `Subtest_Percent` tinyint(3) NULL
  )";   
$result = $db->sql_query($sql);   
if (!$result) {echo("<p>Error performing query: " . mysql_error() . "</p>");}
   if ( $result ) {
      echo "Table Created ".$prefix."$table_name Successful";
      echo "</p>\n";
   } else {
      echo "Table Created ".$prefix."$table_name was Unsuccessful";
      echo "</p>\n";
   }

echo "<p>\n";

//$emptytable = "TRUNCATE TABLE ".$prefix."$table_name";
//$emptyresult = $db->sql_query($emptytable);

$sql = "LOAD DATA LOCAL INFILE '".mysql_real_escape_string($file_name)."'
    INTO TABLE ".$prefix."$table_name
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '\"'
    LINES TERMINATED BY '\r'";
if (!$sql) {echo("<p>Error performing query: " . mysql_error() . "</p>");}
   $result = $db->sql_query($sql);   

$altertable = $db->sql_query("ALTER TABLE ".$prefix."$table_name ADD Session_ID INT(11) NOT NULL AFTER StudentID");
if (!$altertable) { echo "Alter ".$prefix."$table_name failed<br>\n"; } else { echo "Alter ".$prefix."$table_name succeeded<br>\n"; }

$updateSessionID = $db->sql_query("UPDATE ".$prefix."$table_name SET Session_ID = '$Session_ID'");
if (!$updateSessionID) {echo("<p>Error performing query: " . mysql_error() . "</p>");}
//if (!$updateSessionID) { echo "Updating Session_ID ".$prefix."$table_name failed<br>\n"; } else { echo "Updating Session_ID ".$prefix."$table_name succeeded<br>\n"; }

$import_total = $db->sql_numrows($db->sql_query("SELECT * FROM ".$prefix."$table_name"));
Opentable();
echo" $import_total records were imported \n";
echo "<p><a href=\"admin.php?op=TLImportIratView\">View Imported Irat Grades</a></p>";
Closetable();
}   
Opentable();
echo" <h2 align=\"center\">IRAT CSV import</h2>";
echo "<form method='post' action='".$admin_file.".php' enctype=\"multipart/form-data\">\n"
  . "   <table border=\"1\" align=\"center\">"
  . "     <tr>"
  . "        <td>Source CSV file to import:</td>"
  . "        <td rowspan=\"30\" width=\"10px\">&nbsp;</td>"
  . "        <td><input type=\"file\" name=\"file_source\" id=\"file_source\" class=\"edt\" value=\"$file_source\"></td>"
  . "     </tr>      "
  . "     <tr>"
  . "        <td colspan=\"3\" align=\"center\"><input type=\"Submit\" name=\"Go\" value=\"Import it\" onClick=\" var s = document.getElementById('file_source'); if(null != s && '' == s.value) {alert('Define file name'); s.focus(); return false;}\"></td>"
  ."<input type='hidden' name='op' value='TL_CSV_Import'>\n"
  . "     </tr>"
  . "   </table>"
  . " </form>"
 ."";
 Closetable();
?>





I didn't think cleansing these scantron records would be so difficult. Mad
 
Display posts from previous:       
Post new topic   Reply to topic    Ravens PHP Scripts And Web Hosting Forum Index -> How To's

View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You can attach files in this forum
You can download files in this forum


Powered by phpBB © 2001-2007 phpBB Group
All times are GMT - 6 Hours
 
Forums ©