forumnz Posted May 8, 2009 Share Posted May 8, 2009 I wish to insert an array into a database. I am really new to this so please help me. Here is my array output: Array ( [0] => Array ( [qty] => 66 [price] => 5 [item] => ggg [des] => g [acc] => h [tax] => h ) [1] => Array ( [qty] => 66 [price] => 5 [item] => ggg [des] => g [acc] => h [tax] => h ) ) What can I do? Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted May 8, 2009 Share Posted May 8, 2009 It's just an array that has 2 other arrays inside it. You would just write 2 loops. Quote Link to comment Share on other sites More sharing options...
forumnz Posted May 8, 2009 Author Share Posted May 8, 2009 Ok thanks I'm starting to understand. I'm just taking it step by step at the moment, here is what I have: <?php $e = '[{"qty":66,"price":5,"item":"ggg","des":"g","acc":"h","tax":"h"},{"qty":66,"price":5,"item":"ggg","des":"g","acc":"h","tax":"h"}]'; $d = json_decode($e, true); $d = (print_r($d)); foreach ($d as $val) { echo $val . " "; } ?> I know the error has something to do with the print_r part, but how can I fix it? Thanks Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted May 8, 2009 Share Posted May 8, 2009 Why would you set $d to be the value of print_r($d)? Take out that line first. In your foreach, $val is an array, so you'll have to use another loop to go through it. You're almost there. Quote Link to comment Share on other sites More sharing options...
forumnz Posted May 8, 2009 Author Share Posted May 8, 2009 Oh I see - getting there! I have this at the moment (snippet): <?php foreach ($d as $val) { foreach ($val as $arr) { $sess_hash = (rand() . rand() . rand() . rand() . rand() . rand() . rand()); $sess_hash = (md5($sess_hash)); $lk = $sess_hash; mysql_query("INSERT INTO acc_rec_inv (hash_key, inv_key, line_key, item, des, qty, price, acc, tax) VALUES ('$hk', '$ik', '$lk', '$arr')")or die(mysql_error()); } } ?> But I get this error: Column count doesn't match value count at row 1 Should I have the query outside of the second array? Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted May 8, 2009 Share Posted May 8, 2009 Um... you have an INSERT that fills 9 columns, but you only gave it 4 values. Quote Link to comment Share on other sites More sharing options...
trq Posted May 8, 2009 Share Posted May 8, 2009 You define 9 identifiers in your query yet only 4 values. Quote Link to comment Share on other sites More sharing options...
forumnz Posted May 8, 2009 Author Share Posted May 8, 2009 Oops! This is better: <?php foreach ($d as $val) { foreach ($val as $arr) { $in .= "'$" . $arr . "', "; } $sess_hash = (rand() . rand() . rand() . rand() . rand() . rand() . rand()); $sess_hash = (md5($sess_hash)); $lk = $sess_hash; echo $in; mysql_query("INSERT INTO acc_rec_inv (hash_key, inv_key, line_key, item, des, qty, price, acc, tax) VALUES ('$hk', '$ik', '$lk', '$in')")or die(mysql_error()); } ?> That sort of works, but since I have 2 rows to insert, I have 2 times as much data, so I need to somehow split the $in between (). Is this easy? Thanks Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted May 8, 2009 Share Posted May 8, 2009 Edit: oops.. use explode Quote Link to comment Share on other sites More sharing options...
trq Posted May 8, 2009 Share Posted May 8, 2009 I can plainly see from here that your query won't work. Again, you still have 9 identifiers and only 4 values. Quote Link to comment Share on other sites More sharing options...
forumnz Posted May 9, 2009 Author Share Posted May 9, 2009 Thanks for the help. I am almost there. I have the right amount of columns etc.. Just when there is more than one set of data, in adds more fields within the VALUES (here) - that means that there isn't the right amount of columns... Here is what I have: <?php foreach ($d as $val) { foreach ($val as $arr) { $in .= "'" . $arr . "', "; $ina = "('$hk', '$ik', '$lk', " . $in . ")"; $l = array(", )"); $ina = str_replace($l, ")", $ina); } $sess_hash = (rand() . rand() . rand() . rand() . rand() . rand() . rand()); $sess_hash = (md5($sess_hash)); $lk = $sess_hash; echo $ina; mysql_query("INSERT INTO acc_rec_inv (hash_key, inv_key, line_key, item, des, qty, price, acc, tax) VALUES $ina")or die(mysql_error()); } ?> What do you think? Thanks Quote Link to comment Share on other sites More sharing options...
trq Posted May 9, 2009 Share Posted May 9, 2009 Given this.... ] <?php $data = '[{"qty":66,"price":5,"item":"ggg","des":"g","acc":"h","tax":"h"},{"qty":66,"price":5,"item":"ggg","des":"g","acc":"h","tax":"h"}]'; $array = json_decode($data, true); foreach ($array as $innerarray) { $sql = "INSERT INTO acc_rec_inv SET "; foreach ($innerarray as $k => $v) { $sql .= "$k = '$v', "; } $sql = sustr($sql, 0, -2); // at this point in time your query will look like "INSERT INTO acc_rec_inv SET qty = '66', price = '5', item = 'ggg', des = 'g', acc = 'h', tax = 'h'" // execute the query. } ?> Im not sure where your planning on getting 'hash_key, inv_key, line_key' from but you should be able to sort it from here. Quote Link to comment Share on other sites More sharing options...
trq Posted May 9, 2009 Share Posted May 9, 2009 Of course if your guaranteed that the data will be in the same order within the array at all times you could also have used implode. <?php $data = '[{"qty":66,"price":5,"item":"ggg","des":"g","acc":"h","tax":"h"},{"qty":66,"price":5,"item":"ggg","des":"g","acc":"h","tax":"h"}]'; $array = json_decode($data, true); foreach ($array as $innerarray) { $sql = "INSERT INTO acc_rec_inv (qty, price, item, des, acc, tax) VALUES ('" . implode("','", $innerarray) . "')"; // execute query. } ?> Which would be more efficient. Quote Link to comment Share on other sites More sharing options...
forumnz Posted May 9, 2009 Author Share Posted May 9, 2009 Hi thorpe Thanks, but for some reason that doesn't work (I fixed the substr), but it isn't inserting anything... WHy could this be? Quote Link to comment Share on other sites More sharing options...
trq Posted May 9, 2009 Share Posted May 9, 2009 Echo your query to see what it looks like. ps: I don't think there was anything wrong with my call to substr. Quote Link to comment Share on other sites More sharing options...
forumnz Posted May 9, 2009 Author Share Posted May 9, 2009 It looks like: INSERT INTO acc_rec_inv SET qty = '66', price = '5', item = 'ggg', des = 'g', acc = 'h', tax = 'h'INSERT INTO acc_rec_inv SET qty = '66', price = '5', item = 'ggg', des = 'g', acc = 'h', tax = 'h' I guess then I should just change it to complete each query separately? Quote Link to comment Share on other sites More sharing options...
forumnz Posted May 9, 2009 Author Share Posted May 9, 2009 Or not? That wouldn't work would it..? Quote Link to comment Share on other sites More sharing options...
trq Posted May 9, 2009 Share Posted May 9, 2009 Of course you need to execute each query separately, look at where I put the comment about executing the sql. Quote Link to comment Share on other sites More sharing options...
forumnz Posted May 9, 2009 Author Share Posted May 9, 2009 Hey thanks this is the query I have at the moment.. The $sql echoed: mysql_query("INSERT INTO acc_rec_inv SET qty = '66', price = '5', item = 'ggg', des = 'g', acc = 'h', tax = 'h'"); mysql_query("INSERT INTO acc_rec_inv SET qty = '66', price = '5', item = 'ggg', des = 'g', acc = 'h', tax = 'h'"); It looks right to me, but how can I actually 'run' the variable instead of echoing it? Quote Link to comment Share on other sites More sharing options...
trq Posted May 9, 2009 Share Posted May 9, 2009 <?php $data = '[{"qty":66,"price":5,"item":"ggg","des":"g","acc":"h","tax":"h"},{"qty":66,"price":5,"item":"ggg","des":"g","acc":"h","tax":"h"}]'; $array = json_decode($data, true); foreach ($array as $innerarray) { $sql = "INSERT INTO acc_rec_inv SET "; foreach ($innerarray as $k => $v) { $sql .= "$k = '$v', "; } $sql = sustr($sql, 0, -2); mysql_query($sql); } ?> Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.