Using session variables to build mysql update string

[Systkinabarn]

I'm not sure if this is the proper forum but it seems to be code and not msql. First off I am a newbee to PHP with little experience. Here's my question. I have a set of scripts that I coded that queries, filters and inserts data into a mysql data base. I am creating a set of scripts to overwrite only "selected" fields from a csv file. In my scripts I use a form with checkboxes to select the field to overwrite (ex alt_email, company). I have these selected field names passed via sessions to the final script that will upload, read the csv sheet and UPDATE the selected fields only. My idea was to create a string to use to build an UPDATE mysql command. How do create the string not knowing which fields are chosen and what key needs to be used for that field. I may not be looking at this the right way. I am not asking for someone to write the code for me but I would like if someone could give me an idea of the logic and perhaps the functions needed to do this. Any help is appreciated. 

[mac_gyver]

// list of fields to keep

$_SESSION['fields'] = array(2=>'alt_email', 4=>'company'); // the key values are used to intersect with the actual array of data


// example data, actual data would be from the csv line (fgetcsv)

$data = array('field0','field1','field2','field3','field4','field5'); // for this example, want field2 and field4 values out of the array of data


$result = array_intersect_key($data,$_SESSION['fields']);


echo '<pre>'; print_r($result);

[Systkinabarn]

Thanks for the quick reply Mac_gyver. I will have to look into array_intersect_key. I didn't know it existed. Thanks!

[aysiu]

Maybe you have a really good reason for using a CSV file, but have you considered instead creating a PHP form that allows people to update the database directly, based on what checkboxes they put into the online form?

[Systkinabarn]

aysiu, that is what I am doing. The first form the user selects the fields to be update, the second form is where the file to upload and fget the field information to update is executed along with saving the selected fields to update. My issue is I have all the pieces: the field name to update, the list of fieldnames, and the data for each field. My trouble is I can't figure a way to put that into the UPDATE statement. Since each part of the UPDATE statement needs the "selected" field name and the corresponding $data from the fgetcsv loop I'm not sure how to create the string dynamically for the UPDATE statement. EX: UPDATE $table_main SET alt_email='$data[1]', g_name='$data[2]',........ I know alt_email needs to use $data[1] to update that field. I don't know how to write that string dynamically using only the fields selected. The reason for the csv is this will be updating tens of thousands of contacts at a time. For example if I suddenly found the state for a group of contacts or if they have been emailed a certain newsletter.

 

mac_gyver, I found the intersect function interesting. I really never got it to work probably because of syntax errors,  but I can see how handy they are. I really didn't need to define the array since I already had it from the session. I found my issue is what I just stated above. Writing the string variables.

[mac_gyver]

the session variable i showed was just an example defining what the code expected as an input. it shows the index values that would be kept in the csv data and the corresponding field names.

 

to produce your query statement, you could just loop over the session variable, getting the $key and $field name from each element. then use the $key to reference the data - $data[$key]. however, you can avoid an explicit loop by letting php do all the work using array functions -

// list of fields to keep
$_SESSION['fields'] = array(2=>'alt_email', 4=>'company'); // the key values are used to intersect with the actual array of data

// callback function to produce the field = 'value' term for each set of fields/values
function _set_terms($arr_k,$arr_v){
    return "`$arr_k` = '$arr_v'"; // you could expand this to address the actual field type (not all of type string)
}

// example data, actual data would be from the csv line (fgetcsv)
$data = array('field0','field1','field2','field3','field4','field5'); // for this example, want field2 and field4 values out of the array of data

$result = array_intersect_key($data,$_SESSION['fields']); // keep only the csv fields you want

// form the query
$query = "UPDATE your_table SET ";

$result = array_map('_set_terms',$_SESSION['fields'],$result); // produce the field = 'value' for each set of array entries

$query .= implode(', ',$result); // combine into the query statement

$query .= " WHERE some_condition_here..."; // finish the query statement

echo $query; // display/run your query here....

lastly, if you are updating a large number of rows, you would actually want to use a multi-value REPLACE query, where you update/replace as many rows in one query statement as possible (2K - 5K.) each complete query you run (or each prepared query you send values for and run) requires a round-trip from php to the database server. for simple queries, each of these communications with the database server takes much longer than the actual query takes to run on the database server, so you should avoid running queries inside of any sort of loop.

[aysiu]

This doesn't sound like the proper approach. If I'm understanding what you've described correctly, you basically don't have a database... you have a whole bunch of different databases that you try to merge together periodically.

 

Instead of having people work with CSVs that they update manually and then have to upload to merge with the real MySQL database, create PHP forms to have them update the MySQL database directly.

 

Again, perhaps I'm not understanding you correctly, but it sounds as if there are multiple people working with a bunch of contacts. Each person then sends out emails and marks "Oh, this person has been sent a newsletter... that person moved to a different state" but marks these on a CSV file, which she then has to upload, and then that CSV gets parsed... this is not ideal and creates a lot of overhead (you have to upload and process the entire CSV, even if there are only a few changes, and if there is any shared data, it isn't being updated in real time). Instead, each person should have a PHP form to edit contacts and mark address changes or newsletter sends, which would then immediately be directly changed in the MySQL database.

[mac_gyver]

@aysiu,

 

the OP is trying to let a user selectively map/filter which csv columns correspond to which database table columns to provide a general purpose script for importing data from any arbitrary csv file into a database table.

[Systkinabarn]

aysiu, I find myself in a position that happens in the real world often. I am the IT administrator for several small companies. I am really a network and A+ guy but have to on many occasions leave my comfort zone to address problems. I have created a MySQL database to "filter" bad email addresses, opt outs etc. Only I and the office manager from that company work the back end of the database. Input comes from several sources but is mapped into a csv sheet for insert and updates. Currently the fields number around 34 but the contacts near 100K.  When building the database and the interface there we a small set of fields needed, but as usual now that company wants it to include and filter by more fields and as the information is available I need to update the database to meet the new standards. (this never happens in the real world) I was careful to make the fields expandable and other variables with "include" files but now this issue has me deep in the weeds to fix. mac_gyver has given me great insight on how to approach this problem. I am grateful for it. I will need some time to digest it and hopefully with limited understanding make it work. I hope this helps you understand what I'm doing.