Help with an array problem

[axsom1]

I'm spinning my wheels here... I'm having a problem with the code below.

I'm successfully breaking out the comma delimited fields into their own arrays for database inserts (the data is not exactly the same as the table structure). That all works fine and dandy, my problem is I wan't to do a lookup of the cost centers in the database and associate that unique ID with the cost center I am getting in my file.

Here is the first four lines of my csv file:

"YEAR","PAY PERIOD #","COST CENTER NUMBER","COST CENTER NAME","HOURS","UNITS",
"2004","11","6010","6010 ICU-CCU","716","30",
"2004","11","6170","6170 MEDICAL/SURGICAL","2053","248",
"2004","11","6380","6380 OB/GYN","700.75","54",

The third field is my cost center numbers which I would like to see like this:

2004,11,1,
2004,11,2,
2004,11,5,
etc...

Here is my code so far:

<?

$db = mysql_connect('db' , u' , 'p') or die ("Unable to connect to database server.");
mysql_select_db('prod') or die ("Unable to connect to database name.");

// open the file for importing
$f = fopen ("C:\\TEST.TXT", "r");

// read the file
$text = fread($f, filesize("C:\\TEST.TXT"));

fclose($f);


$query = "SELECT deptid, cost_center FROM depts";
$result = mysql_query($query, $db);
$num = mysql_num_rows($result);

// break the file down line by line
// each line represents one element in our array
$text = str_replace("\"", "", $text);
$text = explode("\n", $text);

for ($i=1; $i < count($text); $i++) {
	list($year[$i], $pay_period[$i], $dept[$i], $name[$i], $hour[$i], $unit[$i]) = explode(',', $text[$i]);
	for ($n=0; $n < $num; $n++) {
		$cost = mysql_fetch_object($result);
		if ($dept[$i] == $cost->cost_center) {
			$cost_cen[$i] = $cost->deptid;
			}
		}	
	echo $year[$i].", ".$pay_period[$i].", ".$cost_cen[$i]."<br>";
	}
	
for ($t=0; $t < $num; $t++) {
	$test = mysql_fetch_object($result);
	echo $test->deptid;
	}
echo $num;
?>

Currently this is what I would see:

2004,11,1
2004,11,
2004,11,
2004,11,
etc...

The 1 value above is correct, yet I will get a 1 if I include the first line ($i=0 in my first for loop) which isn't right since it is a header.

Thanks,
John

 

[sleipnir214]

Could you explain in a little more detail what it is you're trying to do to this data? Your script's logic is sufficiently flawed to prevent my figuring it out from the code you posted.


It looks to me like you're trying to transform one column in your text file using data from MySQL. But if that is true, your lines that replace the value seem to require that the data from the database match the order of the data from the datafile. But I don't see how that will work.

Here's what I would do:[ul][li]select all your department and cost-center data from MySQL and store it in an associative array, with the cost center id being the keys and the department id being the values[/li][li]Open the file in read mode.[/li][li]read the header line into a variable for safekeeping[/li][li]Use a while loop with fgetcsv() to read the datafile one one at a time and parse the CSV data into an array[/li][li]change whatever record from the CSV array that you need to using the data from the MySQL array[/li][li]Ouput the changed CSV array[/li][/ul]



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[axsom1]

Sure thing, as you can tell I'm a real newb with PHP.

Basically, I need to import the data from a text file into my database but I need to do some data transforms.

What I was trying to do was create some arrays for each field in my csv file so that I can match only cost center fields with my SQL array and if they match replacing that value with my table ID's.

I tried just reassigning the value like so:

$dept[$i] = $cost->deptid;

But this did the same exact thing as the code above.

I tried the fgetcsv() but was under the impression that you can't create an associative array with it. If I can't do that, what's the best way to make sure I'm replacing only the cost center numbers with my table ID's?

In the end this is going to be inserted into my database and dynamically graphed based on the user's input.

What advice could you give me for improving my code's logic?

Thanks,
John

 

[sleipnir214]

You don't need an associative array to modify values. The static value 2 works just as well as the static value 'cost_center_number' as an array reference, and you can modify the element's value either way. Or is your CSV file not really a CSV, but rather has a variable number of values per line?

Just read in one line at a time from the CSV file, modify it, and do what you need with it.


Actually, though, you could just upload the CSV data into a MySQL table, then perform a SELECT with a join to combine data from the existing MySQL table and the CSV table into a third temporary table.



Want the best answers? Ask the best questions!

TANSTAAFL!!