php get data from csv and again write them to the same csv

Question

I have a csv like this

product_id,stock_unit
1, ,
2, ,
3, ,
4, ,
5, ,
6, ,

Now in my php code I will get the product id and run a query to the database to get the stock unit from the database. After getting the stock unit for the product the script should update that to the csv without changing any names.

So for all that I have made my code like this

<?php
$mysqli = new mysqli("localhost", "username", "password", "database");

/* check connection */
if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

$file = "update_pd.csv";
$csv = array_map("str_getcsv", file($file,FILE_SKIP_EMPTY_LINES));
$keys = array_shift($csv);

$stock_array = array();
if (($handle = fopen($file, "r")) !== FALSE) {
    fgetcsv($handle);
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $stock_array[] = get_data($data[0]);
    }
    fclose($handle);
}
//print_r($stock_array);
$handle = fopen("update_pd.csv", "a");
fputcsv($handle, $stock_array);


function get_data($id) {
    if( !empty($id) ) {
        $result = $mysqli->query("SELECT `stock_unit` FROM `products` WHERE `product_id` = ".$id." ");
        if( $result ) {
            while($row = $result->fetch_assoc()) {
                return $row['stock_unit'];
            }
        }
    }
}

Here it is over writing the csv I mean the data is writing at the last line with all the older data present. The script should write the stock quantity according to the product id in the csv file without any changes in the csv header and file name. So can someone tell me how to do this? Any help and suggestions will be really appreciable.

Update

As the stock unit will be fetch from the database .The output should be like

product_id,stock_unit
1, 24,
2, 22,
3, 37,
4, 46,
5, 89,
6, 96,

Why on earth are you using CSV? mysql does the same thing but heaps better. — e4c5, Sep 30 '16 at 10:00
I dont understand what you are trying to do. Please provide an output sample. Also, please clarify whether you want to append or overwrite. — Gordon, Sep 30 '16 at 10:12
@Gordon I just want to get the stock unit of those products whose product id has been mentioned in the csv then again write the stock unit to the same csv. The stock units are currently in a database table — NewUser, Sep 30 '16 at 10:15
@Gordon Yes..I want to overwrite csv but without changing file name. The product id will be in same sequence. — NewUser, Sep 30 '16 at 10:17

score 0 · Answer 1 · edited May 23 '17 at 12:33

If I get you correctly, your code is working but appends the data instead of overwriting the file. This is because you are doing:

$handle = fopen("update_pd.csv", "a");

which will open the file in append mode. To overwrite it, you'd need to open it in write mode, e.g.

$handle = fopen("update_pd.csv", "w");

See the description for the mode parameter in the PHP Manual for fopen

'w' - Open for writing only; place the file pointer at the beginning of the file and truncate the file to zero length. If the file does not exist, attempt to create it.

'a' - Open for writing only; place the file pointer at the end of the file. If the file does not exist, attempt to create it. In this mode, fseek() has no effect, writes are always appended.

However, you are using the $mysqli variable in your get_data function, but the variable is not defined in that scope, so it should not work at all. Make sure to pass the variable to the function, e.g.

function get_data($mysqli, $id) {

and then call it like this:

get_data($mysqli, $data[0]);

But if you have a query that's basically the same and only uses different values in the WHERE clause, you will get faster query executions by using Prepared Statements.

Define this in the global scope after you created your mysqli instance:

$query = $mysqli->prepare(
    "SELECT `stock_unit` FROM `products` WHERE `product_id` = ?
);

Then instead of passing the $mysqli variable to get_data, pass the $queryvariable instead. Change the get_data function to this code:

function get_data($query, $id) {
    if( !empty($id) ) {
        $query->bind_param("i", $id);
        $result = $query->execute($query);
        …

As an alternative, instead of fetching the stock units individually, consider doing:

$file = "file.csv";
$data = array_map('str_getcsv', file($file, FILE_SKIP_EMPTY_LINES));
$ids = array_filter(array_column($data, 0), 'is_numeric');
$ids = implode(',', $ids);

And then do it all in one single query by using WHERE … IN and INTO OUTFILE:

SELECT "product_id", "stock_unit"
UNION ALL
SELECT product_id, stock_unit
FROM products
WHERE product_id IN ($ids)
INTO OUTFILE '/path/to/update_pd.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY ',\n';

Note that it's bad practise to interpolate variables into queries due the risk of SQL Injection attacks. But since we make sure the ids are numeric, it's okay in this case. Also, mysqli does not allow easy binding of values in WHERE … IN queries.

Is there any other option without making the query like this I mean without OUTFILE kind of thing? — NewUser, Sep 30 '16 at 10:53
@NewUser yes, you can certainly keep doing it, like you are doing it and just change the fopen mode. See update. However, in that case, I suggest to change your SELECT query to use a prepared statement, since that will improve execution speed of your db calls. — Gordon, Sep 30 '16 at 11:02
Can you show some sample code how that can be done. I just don't want to write file from the query as you are doing from OUTFILE — NewUser, Sep 30 '16 at 11:04
@NewUser any reason why you dont want to do it in one go? its much more efficient this way — Gordon, Sep 30 '16 at 11:16

php get data from csv and again write them to the same csv

1 Answers1