Help with arrays 1

mattiep · Mar 15, 2005

Hi,

I am writing a code to take the data from 2 text files and output it to a table. I need to take only one of the columns from the second file only if the value in the first column matches the value in the first file.

e.g.

Text file 1:
data1, data2, data3
123, abc, def
456, ghi, jkl
789, mno, pqr

Text file 2:
data4, data5, data6
987, mno, jkl
654, abc, pqr
321, ghi, def

output all of text file 1, and the data from data6 if data2=data5.

Here is the code that jpadie already helped me out with, but I cant get it to work!!

<?
$fh1 = fopen("fileone.txt", 'r'); //the 'r' argument specifies that the file will be opened for read only
$fh2= fopen("filetwo.txt", 'r');

//parse the first file
while ($var1[] = fgetcsv($fh1,1024,"\t")) //assume this is the lookup file
{
// bad coding but dont need to do anything
}

//now have an array or arrays which need cleaning up
foreach ($var1 as $key=>$val)
{
$datafromfileone[$val[0]]=array("data2"=>$val[1],"data3"=>$val[2]);
}
unset($var1);
//parse the second file

while ($var2[] = fgetcsv($fh2,1024,"\t")) //assume this is the lookup file
{
// ditto
}

//transform $var2 into an easy lookup
foreach ($var2 as $key=>$val)
{
$datafromfiletwo[$val[1]]["datafromfileone"] = $val[1]; //this takes the second col and assigns it to the earlier variable with the ssame key as in the second col.
}
unset ($var2); //destroy the variable to free memory

//close the csv files
fclose($fh1);
fclose($fh2);

//assuming this is a standard vlookup with a single input column
// you can now lookup each item in the first file and find its corresponding value using something like this:
//you could also choose just to read the first file now and perform the lookup on the incoming data
echo "<table border=\"1\">";
echo "<tr>";
echo "<td>data1</td>";
echo "<td>data2</td>";
echo "<td>data5</td>";
echo"</tr>";

foreach ($datafromfileone as $key=>$val)
{
extract($val);
echo "<tr>";
echo "<td>$key</td>";
echo "<td>$data1</td>";
echo "<td>$data2</td>";
echo "<td>$data5</td>";
echo"</tr>";
}
echo "<table>";
?>

If you have read this far then thanks!! I know its long, but theres a lot to explain.

TIA
Mattie P

kenrbnsn · Mar 15, 2005

I think I got the code to do what I think you want.

I assumed that when you say:

the data from data6 if data2=data5

you mean that if any value in data2 is also in data5 print the values in data6.

Note: I left in all of the debugging print_r statements.

Code:

<?
$fh1 = fopen("fileone.txt", 'r');  //the 'r' argument specifies that the file will be opened for read only
$fh2= fopen("filetwo.txt", 'r');

//parse the first file
while ($var1[] = fgetcsv($fh1,1024,",")); //assume this is the lookup file
// notice you don't need the brackets containing no code
echo '<pre>';print_r($var1);echo '</pre>';
//now have an array or arrays which need cleaning up
for ($i=1;$i<count($var1);$i++) {
if (is_array($var1[$i])) foreach ($var1[$i] as $key=>$val)
{
    echo '<pre>$key:'.$key. ' ';print_r($val);echo '</pre>';
    $datafromfileone[$var1[0][$key]][] = $val; //Turn into an associative array
}
}
echo '<pre>';print_r($datafromfileone);echo '</pre>';
unset($var1);
//parse the second file

while ($var2[] = fgetcsv($fh2,1024,",")); //assume this is the lookup file
echo '<pre>';print_r($var2);echo '</pre>';

//transform $var2 into an easy lookup 
for ($i=1;$i<count($var2);$i++) {
if (is_array($var2[$i])) foreach ($var2[$i] as $key=>$val)
{
     $datafromfiletwo[$var2[0][$key]][] = $val;    //Turn into an associative array
}
}
echo '<pre> $datafromfiletwo: ';print_r($datafromfiletwo);echo '</pre>';
unset ($var2); //destroy the variable to free memory

//close the csv files
fclose($fh1);
fclose($fh2);

foreach ($datafromfileone as $key=>$val) // print data from file1
   echo $key . ': ' . implode(', ',$val). "<br>\n";

$data2_in_data5 = FALSE;
foreach ($datafromfileone['data2'] as $v) // see if a value from data2 is in data5
   if (!$data2_in_data5) $data2_in_data5 = in_array($v,$datafromfiletwo['data5']);

if ($data2_in_data5) // if set print data6
   echo '<br>data6: ' . implode(', ',$datafromfiletwo['data6']) . "<br>\n";
?>

This code prints (after all of the debugging prints):

Code:

data1: 123, 456, 789
data2: abc, ghi, mno
data3: def, jkl, pqr

data6: jkl, pqr, def

Ken

sleipnir214 · Mar 15, 2005

Here's my take on the solution:

Code:

<?php
$filename1 = 'test_data1.txt';
$filename2 = 'test_data2.txt';

$data = array();
$fh =fopen ($filename1, 'r');
while ($line = fgets($fh))
{
	$line = rtrim($line);
	$line_array = split (', +', $line);
	$data[$line_array[1]] = array ('data1' => $line_array[0], 'data3' => $line_array[2]);
}
fclose ($fh);

$fh = fopen ($filename2, 'r');
while ($line = fgets ($fh))
{
	$line = rtrim($line);
	$line_array = split (', +', $line);
	if (array_key_exists ($line_array[1], $data))
	{
		$data[$line_array[1]]['data6'] = $line_array[2];
	}
}
fclose ($fh);

print '<html><body><table><tr><td>data1<td>data2<td>data3<td>data6';

foreach ($data as $key => $datum)
{
	print '<tr><td>' . $datum['data1'];
	print '<td>' . $key;
	print '<td>' . $datum['data3'];
	print '<td>' . $datum['data6'];
}

print '</table></body></html>';
?>

which outputs:

[tt]<html><body><table>
<tr>
<td>data1<td>data2<td>data3<td>data6
<tr>
<td>123
<td>abc
<td>def
<td>pqr
<tr>
<td>456
<td>ghi
<td>jkl
<td>def
<tr>
<td>789
<td>mno
<td>pqr
<td>jkl
</table></body></html>[/tt]

Want the best answers? Ask the best questions!

TANSTAAFL!!

mattiep · Mar 15, 2005

Hi,

Thanks for your help!!! Both were good, but... (sorry)

Ken, the output format was different to the table format that I need.
Sleipnir214, I couldnt get your report to run when I tried it.

Maybe if I put some of the test data I am using it will help???

File one:
Case Id Cell ID RNC
2582854 4 BSC215
2613054 4 RNC062
2614144 4 BSC215
2596041 7 BSC119
2610776 9 RNC061
2576357 11 BSC123
2578363 11 BSC123
2605472 11 BSC123
2606713 11 BSC123
2609431 11 BSC123
2578807 14 RNC067

File two:
CSR Name Area
0 840354 432
0 435456436 342
0 43543 342
1 4564356546 602
3 4354353 315
4 5654654 215
6 354436556 315
7 34543 119
8 435 324
9 435 603
10 43543 315
11 34543 123
13 435435 114
13 435435 114
14 435 209

and what I'd expect to see as the output (in table format):
Case Id Cell ID RNC Area
2582854 4 BSC215 215
2613054 4 RNC062 215
2614144 4 BSC215 215
2596041 7 BSC119 119
2610776 9 RNC061 603
2576357 11 BSC123 123
2578363 11 BSC123 123
2605472 11 BSC123 123
2606713 11 BSC123 123
2609431 11 BSC123 123
2578807 14 RNC067 209

Thank you for your help on this!!! I may also be adding data into file one at at later date, so is there an easy way of adding this into the code?

Mattie P

sleipnir214 · Mar 15, 2005

Which column from table2 must match which column of table 1?

Want the best answers? Ask the best questions!

TANSTAAFL!!

sleipnir214 · Mar 15, 2005

Nevermind. "CSR" of file 2 must match "Cell ID" of file 1.

Which file will be longer?
File 2 has duplicate entries. Which entry should be used?

Next time, give example files that better match your real data. The fact that values can be duplicated in your real data makes a difference in how to attack the problem.

Want the best answers? Ask the best questions!

TANSTAAFL!!

sleipnir214 · Mar 15, 2005

On my system, this produces the output you've specified:

Code:

<?php
$filename1 = 'test_data1.txt';  //contains the main data.
$filename2 = 'test_data2.txt';  //contains the data to be appended to each row

$lookup = array();
$fh =fopen ($filename2, 'r');
while ($line = fgets($fh))
{
	$line = rtrim($line);
	$line_array = split (' +', $line);
	$lookup[$line_array[0]] = $line_array[2];
}
fclose ($fh);

print '<html><body><table>
	<tr>
		<td>Case ID
		<td>Cell ID
		<td>RNC
		<td>Area';


$fh = fopen ($filename1, 'r');
while ($line = fgets ($fh))
{
	$line = rtrim($line);
	$line_array = split (' +', $line);

	print '
	<tr>
		<td>' . $line_array[0] . '
		<td>' . $line_array[1] . '
		<td>' . $line_array[2] . '
		<td>';

	if (array_key_exists ($line_array[1], $lookup))
	{
		print $lookup[$line_array[1]];
	}
	else
	{
		print 'N/A';
	}
}
fclose ($fh);


print '
</table></body></html>';
?>

Want the best answers? Ask the best questions!

TANSTAAFL!!

kenrbnsn · Mar 15, 2005

I think this would be much easier if the data were in a database, then you could do something like:

Code:

<?
echo '<table><tr><td>Case Id</td><td>Cell ID</td><td>RNC</td><td>Area</td></tr><tr>'."\n";
$q = "select * from data1";
$rs = @mysql_query($q) or die('Problem with query:'.$q.'<br>'.mysql_error());
while ($rw = @mysql_fetch_assoc($rs)) {
   echo implode('</td><td>',$rw);
   $q2 = "select area from data2 where csr = '" . $rw['cell_id'] . "'";
   $rs2 = @mysql_query($q2);
   $rw2 = @mysql_fetch_assoc($rs2);
   echo $rw2['area']."</td></tr><tr>\n"; }
echo "</tr></table>\n";
?>

Please note, I haven't tested this code, so there are probably some typing errors and (maybe) some logic errors.

Ken

mattiep · Mar 15, 2005

Hi guys,

Ken, I cant get it into mysql very easily, as the data is outputted for me by another system.

sleipnir214, here is the output I get...

Notice: Undefined offset: 2 in c:\program files\easyphp1-7\

http://www\vantivereports\new_advanced_report_grabber_v3.php

on line 12

lots of times, then I get:

1348022 8635 BSC603
Notice: Undefined offset: 1 in c:\program files\easyphp1-7\

http://www\vantivereports\new_advanced_report_grabber_v3.php

on line 37
N/A
Notice: Undefined offset: 1 in c:\program files\easyphp1-7\

http://www\vantivereports\new_advanced_report_grabber_v3.php

on line 33

Notice: Undefined offset: 2 in c:\program files\easyphp1-7\

http://www\vantivereports\new_advanced_report_grabber_v3.php

on line 34

Have I set up PHP / the server on this PC incorrectly??

TIA
Mattie P

sleipnir214 · Mar 15, 2005

I would bet that the data in your file is not formatted the same as the data you posted. What character or characters separates the values in each line of the files?

Want the best answers? Ask the best questions!

TANSTAAFL!!

mattiep · Mar 15, 2005

The values are seperated by tab.

sleipnir214 · Mar 15, 2005

<muttering darkly>
That's not what you posted.
</muttering>

Try this on for size:

Code:

<?php
$filename1 = 'test_data1.txt';
$filename2 = 'test_data2.txt';

$lookup = array();
$fh =fopen ($filename2, 'r');
while ($line = fgets($fh))
{
	$line = rtrim($line);
	$line_array = split ("\t", $line);
	$lookup[$line_array[0]] = $line_array[2];
}
fclose ($fh);

print '<html><body><table>
	<tr>
		<td>Case ID
		<td>Cell ID
		<td>RNC
		<td>Area';


$fh = fopen ($filename1, 'r');
while ($line = fgets ($fh))
{
	$line = rtrim($line);
	$line_array = split ("\t", $line);

	print '
	<tr>
		<td>' . $line_array[0] . '
		<td>' . $line_array[1] . '
		<td>' . $line_array[2] . '
		<td>';

	if (array_key_exists ($line_array[1], $lookup))
	{
		print $lookup[$line_array[1]];
	}
	else
	{
		print 'N/A';
	}
}
fclose ($fh);


print '
</table></body></html>';
?>

Want the best answers? Ask the best questions!

TANSTAAFL!!

mattiep · Mar 15, 2005

sleipnir214, you are a diamond! One last thing, if I need to add extra columns of data to input file one, how do i do this?

Am i right in thinking that the code will automatically add the data as $line_array[x] then all i will have to do is add the line "<td>' . $line_array[x] . '" in below the vlookup?

Thank you again for your help!!!

Mattie P

sleipnir214 · Mar 15, 2005

It should be all right, so long as you add the additional data at the end of the existing lines.

Want the best answers? Ask the best questions!

TANSTAAFL!!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Help with arrays 1

mattiep

Programmer

kenrbnsn

Technical User

sleipnir214

Programmer

mattiep

Programmer

sleipnir214

Programmer

sleipnir214

Programmer

sleipnir214

Programmer

kenrbnsn

Technical User

mattiep

Programmer

sleipnir214

Programmer

mattiep

Programmer

sleipnir214

Programmer

mattiep

Programmer

sleipnir214

Programmer

Similar threads

Part and Inventory Search

Sponsor