passing variables thru to SQL Server using an array

[cerebrus189]

Hi all! I'm a total noob but I'm trying here...

 

I got a script off the net and it works just fine except now I have to write it to the db for retrieval later. This is what I have so far.

 

 

<style>
table { text-align: left; border-collapse: collapse; }
tr:hover { background: blue; color: white }
th, td { padding: 7px }
</style>
<?php



echo "<table>\n";

$row = 0;
$handle = fopen("Ricoh/RICOH_MP9000_print.csv", "r");

while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    if ($row == 0) {
        // this is the first line of the csv file
        // it usually contains titles of columns
        $num = count($data);
        echo "<thead>\n<tr>";
        $row++;
        for ($c=0; $c < $num; $c++) {
            echo "<th>" . $data[$c] . "</th>";
        }
        echo "</tr>\n</thead>\n\n<tbody>";
    } else {
        // this handles the rest of the lines of the csv file
        $num = count($data);
        echo "<tr>";
        $row++;
        for ($c=0; $c < $num; $c++) {
               
            echo "<td>" . $data[$c] . "</td>";
        }
        echo "</tr>\n";
    }
} echo "</tbody>\n</table>";

    //pull last month and year
//$tablename = "Ricoh" + date("FY",mktime(0, 0, 0, date("m")-1, date("d"),   date("Y")));
$tablename = "Testing2010";




//Create SQL Credentials
$SQLUser = "...";
$SQLPass = "...";
$DSNName = "PrintReports";

//connect to the Database
$SQLConn = odbc_connect($DSNName, $SQLUser, $SQLPass) or die ("The Database is unavailable");

//if the table already exists, delete it
$query = "IF OBJECT_ID ('$tablename') IS NOT NULL DROP TABLE $tablename";
odbc_exec($SQLConn, $query);
$query = "CREATE TABLE $tablename (recordId int IDENTITY(1,1), DepartmentCode varchar(50), DepartmentName varchar(50), BWCopy int, BWPrint int, PRIMARY KEY (recordId))";
odbc_exec($SQLConn, $query);


echo "...Writing to DB"
$query = "INSERT INTO $tablename (DepartmentCode, DepartmentName, BWCopy, BWPrint) VALUES ($data[1],[2],[3],[4])";
echo "...done"


odbc_close($SQLConn);
echo "To View the Report, click <a href=pullReport.php?report=$tablename>here</a>";
?>

 

 

I would appreciate any thoughts on how to make it write to the db table properly. I have a limited amount of experience in PHP, more experience in multimedia and HTML/XHTML.

 

Thanks!

[RussellReal]

$query = "INSERT INTO $tablename (DepartmentCode, DepartmentName, BWCopy, BWPrint) VALUES ($data[1],[2],[3],[4])";

 

should probably be:

 

$query = "INSERT INTO $tablename (DepartmentCode, DepartmentName, BWCopy, BWPrint) VALUES ($data[1],$data[2],$data[3],$data[4])";

[cerebrus189]

That's a start but it didn't fix the issue. I tried it in the browser and I'm getting a blank screen.

[RussellReal]

you never execute the new query..

[cerebrus189]

Yes, I executed it with the newest code you gave me. And i'm getting a blank screen as far as the echo'd csv file contents (table in html). And when I check the db, the table is created but there's no data in the table.

 

 

[cerebrus189]

Okay, I just reread your post and it says I'm not supposed to execute the new query. So then how do I know if it works? I just checked the db and its still not writing to the table.

[RussellReal]

no no, you're supposed to execute it

 

put the insert query into your loop thats the only thing I can see that could be mucking it up

[taquitosensei]

It's possible there's a typo in your code and you don't have error reporting on. That could be the reason for the white screen.  Try putting this

ini_set('display_errors',1);
error_reporting(E_ALL);

 

at the top of your code.  That should tell you if that's the case or not.

 

 

[cerebrus189]

I'm debugging in PhpED so I'm getting the debugging errors (and lines where they exist in that program).

 

But I'll try that

[cerebrus189]

How would I create the insert statement within the loop?

 

 

Like this...

// this handles the rest of the lines of the csv file
        $num = count($data);
        echo "<tr>";
        $row++;
        for ($c=0; $c < $num; $c++) {
          
       
     
            echo "<td>" . $data[$c] . "</td>";
        }      
  $query = "INSERT INTO $tablename (DepartmentCode, DepartmentName, BWCopy, BWPrint) VALUES ($data[1],$data[2],$data[3],$data[4])";              

        echo "</tr>\n";

[cerebrus189]

As far as I can tell, my insert statement should be within the loop statement but I'm not quite sure how this fits into my table rendering.

 

Input please on if this is right...

 

// this handles the rest of the lines of the csv file  
        
        $num = count($data);
        echo "<tr>";
        $row++;
        for ($c=0; $c < $num; $c++)

{      
          echo "<td>" . $data[$c] . "</td>";
          
          $query = "INSERT INTO $tablename (DepartmentCode, DepartmentName, BWCopy, BWPrint) VALUES ('" . $data[1] . "','" . $data[2] . "', '" . $data[3] . "','" . $data[4] . "')"; 
        }      
        echo "</tr>\n";
    }
} echo "</tbody>\n</table>";

 

Earlier in my code, I'm able to create the table in SQL Server but all info is null when I run the parse.php file.

 

I've attached the code in its entirety as a file. If somebody is willing to look at it, I'd be very grateful.

 

 

 

[attachment deleted by admin]

[cerebrus189]

There's an echo in here...and I'm not talking PHP.

 

Anybody have any input?

[cerebrus189]

Solved this issue myself. Here's the final code in case anybody else has the problem. It was an issue with the loop.

<style>
table { text-align: left; border-collapse: collapse; }
tr:hover { background: blue; color: white }
th, td { padding: 7px }
</style>
<?php

function pullLDAP($name){
  /*
   * This function pulls the OU data from LDAP based on the user name
   * It will return only the first OU that the user is in
   */

  //LDAP Connection information
  $host='...';
  $password='...';
  $bindDN='...\serverconsole';

  //Get LDAP configuration settings.
  $ds = ldap_connect($host) or die ('Could not connect!');
  ldap_set_option($ds, LDAP_OPT_PROTOCOL_VERSION, 3);
  $r = ldap_bind($ds, $bindDN, $password);



  //sAMAccountName is the name of the field in LDAP for the username
  $attributes = array('sAMAccountName');
  //we will query LDAP, using the binding from outside the function
  $result = ldap_search($ds, "ou=Arca,dc=int,dc=arc-a,dc=org", "(sAMAccountName=$name)", $attributes);
  $entries = ldap_get_entries($ds, $result);

  //we now have the dn and need to filter it
  $dn = $entries[0]['dn'];
  //we only want to grab the first OU, so we filter that
  $splitvalue = split(",",$dn);
  //Remove the =CN= from the beginning, so it is just the OU
  return substr($splitvalue[1],3);
}

//get last month for the filenames
$lastMonth = sprintf("%02d", date("m") - 1);
$thisYear = date("Y");
$fileLastMonth = $thisYear . $lastMonth;
//$fileLastMonth = "201001";

//pull last month and year
//$tablename = "Ricoh_" + date("FY",mktime(0, 0, 0, date("m")-1, date("d"),   date("Y")));
$tablename = "Testing2010";


echo "<table>\n";

$row = 0;
$handle = fopen("Ricoh/RICOH_MP9000_print.csv", "r");

        //Create SQL Credentials
            $SQLUser = "..";
            $SQLPass = "...";
            $DSNName = "PrintReports";

            //connect to the Database
            $SQLConn = odbc_connect($DSNName, $SQLUser, $SQLPass) or die ("The Database is unavailable");

            //if the table already exists, delete it
            $query = "IF OBJECT_ID ('$tablename') IS NOT NULL DROP TABLE $tablename";
            odbc_exec($SQLConn, $query);
            $query = "CREATE TABLE $tablename (recordId int IDENTITY(1,1),DepartmentCode varchar(50), DepartmentName varchar(50), BWCopy int, BWPrint int, PRIMARY KEY (recordId))";
            odbc_exec($SQLConn, $query);


while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {


    if ($row == 0) {
        // this is the first line of the csv file
        // it usually contains titles of columns
        $num = count($data);
        echo "<thead>\n<tr>";
        $row++;
        for ($c=0; $c < $num; $c++) {
            echo "<th>" . $data[$c] . "</th>";
        }
        echo "</tr>\n</thead>\n\n<tbody>";
} else {


        // this handles the rest of the lines of the csv file

        $num = count($data);
        echo "<tr>";
        $row++;
        for ($c=0; $c < $num; $c++)
		{
		echo "<td>" . $data[$c] . "</td>";
        }
        $query = "INSERT INTO ".$tablename." (DepartmentCode, DepartmentName, BWCopy, BWPrint) VALUES ('" . $data[1] . "','" . $data[2] . "', '" . $data[3] . "','" . $data[4] . "') where DepartmentCode != null";
        odbc_exec($SQLConn, $query);
        echo "</tr>\n";
    }
} echo "</tbody>\n</table>";


//Write to DB

odbc_close($SQLConn);

?>

[RussellReal]

gratz