[RESOLVED] Working With A File (List) For Database Insertion

scriderpsd

I have a text file that looks like this:

In this file, the lines that begin with "MA" are room numbers and the numbers underneath each are computer id numbers. I need to read this file and update my database setting the room number for each computer id number.

The update query needs to go like this...

UPDATE table SET room_number = 'MA145' WHERE computer_id_number='2660';

My questions is how should I go through the file and get this data ready for the UPDATE? Should I be putting it in an array? Then how do I get it out of the array for the UPDATE?

Any help is very much appreciated.

Thanks,

Scotty

laserlight

scriderpsd wrote:
The update query needs to go like this...

Consider this subrange:

A suitable UPDATE statement would be:

UPDATE table SET room_number = 'MA104' WHERE computer_id_number IN ('2703', '2716', '2717', '1139');

My questions is how should I go through the file and get this data ready for the UPDATE? Should I be putting it in an array? Then how do I get it out of the array for the UPDATE?

You could use [man]file/man to read the data into an array. Then, iterate over the array. Everytime you encounter a line beginning with 'MA', set that to be the current key. Otherwise, add the line's contents to the array with the current key (i.e., you have an array of arrays). Remember to use [man]rtrim/man to remove the newline sequence from each line.

When you are done with this processing, iterate over the new array and then construct the UPDATE statements corresponding to each sub-array. You may find [man]implode[/man] useful to construct the IN clause. Remember to use [man]mysql_real_escape_string/man to escape the string input.

scriderpsd

Thank you very much for your reply, laserlight.

You could use file() to read the data into an array.

I read the file into an array like so...

$file = file('school_inv/MA12-02-08.txt') or die('could not read file'); 
$data = array();

foreach($file as $line) 
{ 
   $myArray= array();
}

Okay, here is where I am having trouble...

Then, iterate over the array. Everytime you encounter a line beginning with 'MA', set that to be the current key. Otherwise, add the line's contents to the array with the current key (i.e., you have an array of arrays). Remember to use rtrim() to remove the newline sequence from each line.

I appreciate your help.

Scotty

laserlight

scriderpsd wrote:
Okay, here is where I am having trouble...

You could do something like this:

$lines = file('school_inv/MA12-02-08.txt') or die('could not read file');

$entries = array();
$room_number = '';
foreach ($lines as $line)
{
    $line = trim($line);
    if (strpos($line, 'MA') === 0)
    {
        $room_number = $line;
    }
    else
    {
        $entries[$room_number][] = mysql_real_escape_string($line);
    }
}

foreach ($entries as $room_number => $computer_ids)
{
    if (count($computer_ids) > 0)
    {
        $computer_ids = "'" . implode("','", $computer_ids) . "'";
        $room_number = mysql_real_escape_string($room_number);
        $sql = "UPDATE table SET room_number = '$room_number' WHERE computer_id_number IN ($computer_ids)";
        // ...
    }
}

Note that this assumes that the input is valid.

scriderpsd

Laserlight, thank you so much for your help. This is exactly what I'm looking for.

Unfortunately, my output is close but not quite there...It appears $room_number and $computer_ids is coming out null. Here's what the output looks like.

UPDATE table SET room_number = '' WHERE computer_id_number IN ('','','','','','','','','','','','','','','')
UPDATE table SET room_number = '' WHERE computer_id_number IN ('','','','')UPDATE table SET room_number = '' WHERE computer_id_number IN ('')
UPDATE table SET room_number = '' WHERE computer_id_number IN ('')
UPDATE table SET room_number = '' WHERE computer_id_number IN ('')
UPDATE table SET room_number = '' WHERE computer_id_number IN ('')
UPDATE table SET room_number = '' WHERE computer_id_number IN ('','','','','','','','')

is the problem in this block?

else
    {
        $entries[$room_number][] = mysql_real_escape_string($line);
        //print_r ($entries[$room_number]);
    }

laserlight

scriderpsd wrote:
Unfortunately, my output is close but not quite there...It appears $room_number and $computer_ids is coming out null.

I checked my example with your sample input (but omitting the functions of the MySQL extension) and it worked as expected, so there should be no problem.

scriderpsd

Thank you. I removed mysql_real_escape_string and it worked fine. I appreciate your help.

Scotty

laserlight

scriderpsd wrote:
Thank you. I removed mysql_real_escape_string and it worked fine.

Oh wait, now I understand. The 'MA' made me think of the MySQL extension for some strange reason, which is why I suggested mysql_real_escape_string(). However, you never actually stated what database interface you were using. As such, instead of simply doing away with mysql_real_escape_string(), replace it with the appropriate string escaping mechanism from the database extension that you are using. If you do not do so, your script may be vulnerable to SQL injection.