0

I have tried both the links but I don't get the expected variables content in $fields and $newdata

** This question has developed in a new one here ** PHP how to extract keys names and values from associative array for mysql query (as explained I'm newbie and it took some work to get the question asked in the right way)

so I kindly print here some var_dump and cast to kindly ask your support.

For simplicity in my learning experiment, I'm working with a dummy table of just 5 fields, as you see they are: selected, user_id, user_name, user_company and user_email. Finally I have inserted just 2 rows of values.

Using this code

$Arr = (array)$data;
print_r ( $Arr );

I can see this output

Array ( 
[0] => Array ( [selected] => [user_id] => 3 [user_name] => nome3 [user_company] => azien3 [user_email] => email3 ) 
[1] => Array ( [selected] => 1 [user_id] => 6 [user_name] => nome6 [user_company] => azien6 [user_email] => email6 ) 
)

next I try to apply the code of your links

24  $fields = implode(",", array_keys($Arr));
25  $newdata = "'" . implode("','", $Arr) . "'";
26  
27  var_dump($fields);
28  echo "<br><br>";
29  var_dump($newdata);

But something is wrong in my interpreteation or in my code , because the output is

Notice: Array to string conversion in D:\xampp\htdocs\ajax-json\post.php on line 25

Notice: Array to string conversion in D:\xampp\htdocs\ajax-json\post.php on line 25
string(3) "0,1"

string(15) "'Array','Array'"

can you kindly point out what's wrong? e.g. is my array properly formed?

ORIGINAL QUESTION

I'm newbie and after further readings I got the point and hopefully now my question could result more simple. Thank you for any hint.

I have a bidimensional array since its records are coming from an html table.

The collection made on the html side generates an associative array so each row is like in the following example where, also, you can see, the keys are many

[item] => Array ( 
    [0] => Array ( 
        [unit] => "car"
        [qty] => 3 
        [description] => "Description 1"
        ....
        [key47] => "thin"
    ) 
    [1] => Array ( 
        [unit] => "bike"
        [qty] => 74 
        [description] => "Description 2"
       ....
        [key47] => "large"
    ) 
)

The mysql table name where I want to insert the items is named items_table

"unit" "qty" and "description" and all the remaining keys (total 47) are exactly the same names of the items_table columns

When on the php side I'd like to have a sort of automatic query generation maybe with PDO that would pick the columns names from the inner array keys names and will insert the corresponding key value in the correct column.

The goal is to avoid creating such a awkward and extremely long query.

Preferred method would be with PDO, avoid binding 47 keys.

This sort of automatism is also useful because in the future columns in the mysql table may being added and or removed and or renamed, so I'd like to forget about the query thinking only about properly editing the html table (on that side the array is already automatically generated with some javascript)

Do you think is possible to do this?

If yes how to?

Does this still need some foreach loop?

Thank you for helping

Robert
  • 490
  • 1
  • 5
  • 17
  • P.S. As you may wonder, I'm a newbie. I've read a lot of theory and watched a lot of questions about these themes, but I have not found this situation/combination. Eventually thank you for the right link. – Robert Jul 27 '17 at 11:41
  • A table of many columns is likely to be a poorly designed table. – Strawberry Jul 27 '17 at 12:21
  • @Strawberry unfortunately that is a must since there is a matrix of shoes numbers with quantity for each shoes size. Though I have totally reviewed the question. Hope now it makes more sense. Can you kindly help with it? Thank you – Robert Jul 27 '17 at 15:34
  • Your logic is flawed. A database table is NOT a spreadsheet. Your model requires 3 columns, not n columns. – Strawberry Jul 27 '17 at 15:46
  • @Strawberry this is a simplified example, the real case has some 31 columns. But if someone can help with the required implementation (see new text of the question, now is totally rewritten) it won't matter if columns are 3 or 30 or 300 :-) – Robert Jul 27 '17 at 15:52

1 Answers1

0

E.g.:

core_table
id* unit quantity description
 1  car         3 Description 1
 2  bike       74 Description 2

adjunct_table
core_id* meta_key* meta_value
      1        47  Description of key n.47 first row
      2        47  Description of key n.47 second row

* = (component of) PRIMARY KEY
Strawberry
  • 33,750
  • 13
  • 40
  • 57
  • Thank you for the code but these fields are rows of an order entry. Each row is the order detail. Each row is so many columns. As order detail this row is a row and will be hold by the items_table. Saving 3 columns having doubling the table with the second table again of so many fields is a slight no sense :-). I think that now the point of the question is pretty sharp. I'm wondering of how to reuse array keys to build the query. – Robert Jul 27 '17 at 16:17
  • More precisely order_items_table – Robert Jul 27 '17 at 16:20
  • Yeah, you're not really getting it are you. The 'so many columns' part is the fundamental flaw in your design - which is why I propose an alternative model. – Strawberry Jul 27 '17 at 17:35
  • Ok, that is an alternative, but the point of the question is another: associative arrays are made of pairs of key names and key values. I'm searching (if it does exist) the solution to take advantage of those pairs to build the query taking advantage of the self array keys names as db columns names and the keys values to insert them in the corresponding column (since as stated, they do have the same name as the array key name). – Robert Jul 27 '17 at 18:14
  • This question has developed in a new question here https://stackoverflow.com/q/45368084/344628 with very good replies. – Robert Jul 28 '17 at 12:46