ludo1960 Posted January 16, 2019 Share Posted January 16, 2019 Hi folks, If I have an array where the database table columns match the array keys, how can I insert just the array values into the mysql database using a mysqli connection? Link to comment Share on other sites More sharing options...
Barand Posted January 16, 2019 Share Posted January 16, 2019 Where does the array come from? Link to comment Share on other sites More sharing options...
ludo1960 Posted January 16, 2019 Author Share Posted January 16, 2019 $myarray = array( 'ID' => 1, 'ID2 => 2, 'ID3' => 3 ); Link to comment Share on other sites More sharing options...
benanamen Posted January 16, 2019 Share Posted January 16, 2019 When you when you start using incrementing numbers for your column names it is a big red flag to a design problem. Take a minute and learn about database normalization. Link to comment Share on other sites More sharing options...
ludo1960 Posted January 16, 2019 Author Share Posted January 16, 2019 Ok how about: $myarray ( 'state' => Texas, 'country => USA ); Link to comment Share on other sites More sharing options...
Barand Posted January 16, 2019 Share Posted January 16, 2019 4 hours ago, Barand said: Where does the array come from? I'll ask again. The solution would differ depending on the array's origin. (For example, if it's from a query fetch() or posted form data). So what are you trying to accomplish here? (If you won't answer my questions, why should I bother to answer yours?) Link to comment Share on other sites More sharing options...
ludo1960 Posted January 16, 2019 Author Share Posted January 16, 2019 The arrays origin is from a call to a JSON source, $response = json_decode(file_get_contents($url), true); the array is then created from the data received: $myarray = array( 'County' => $response['state'], 'Country' => $response['country'] ); Link to comment Share on other sites More sharing options...
Barand Posted January 16, 2019 Share Posted January 16, 2019 Is this what you are looking for $db->query("DROP TABLE ludo"); $db->query("CREATE TABLE ludo ( id int auto_increment not null primary key, county varchar(20), country varchar(20) )"); $data = [ 'county' => 'Cheshire', 'country' => 'England']; $fields = array_keys($data); $placeholders = array_fill(0, count($data), '?'); $res = $db->prepare("INSERT INTO ludo (" . join(',', $fields) . ") VALUES (" . join(',', $placeholders) . ")"); $res->bind_param('ss', ...array_values($data)); $res->execute(); Link to comment Share on other sites More sharing options...
ludo1960 Posted January 16, 2019 Author Share Posted January 16, 2019 Not quite, your code uses the array keys and the array values, I just want to pass the array values to the mysql table. Link to comment Share on other sites More sharing options...
Barand Posted January 16, 2019 Share Posted January 16, 2019 Silly me!. When you stated that the keys were the same names as the columns I thought there was some relevance to that. Link to comment Share on other sites More sharing options...
ludo1960 Posted January 16, 2019 Author Share Posted January 16, 2019 lol, the keys are irrelevant, just need to insert the array values. Link to comment Share on other sites More sharing options...
Barand Posted January 16, 2019 Share Posted January 16, 2019 https://dev.mysql.com/doc/refman/5.7/en/insert.html Link to comment Share on other sites More sharing options...
ludo1960 Posted January 17, 2019 Author Share Posted January 17, 2019 Aye very good, anybody else got an answer to just insert array values without using array keys? Link to comment Share on other sites More sharing options...
Barand Posted January 17, 2019 Share Posted January 17, 2019 You could always engage brain, leave out the "array_keys" bit and provide the column names/placeholders yourself EG $data = [ 'county' => 'Cheshire', 'country' => 'England']; $res = $db->prepare("INSERT INTO ludo (county, country) VALUES (?,?)"); $res->bind_param('ss', ...array_values($data)); $res->execute(); Link to comment Share on other sites More sharing options...
ludo1960 Posted January 17, 2019 Author Share Posted January 17, 2019 Yes that makes sense, was wondering what the bind_param('ss', meant, seems https://www.w3schools.com/php/php_mysql_prepared_statements.asp explains it rather well. Nice answer Barand, thanks!! Link to comment Share on other sites More sharing options...
Barand Posted January 17, 2019 Share Posted January 17, 2019 PDO makes it even easier. I would strongly advise you switch from mysqli. The PDO solution is $res = $db->prepare("INSERT INTO ludo (county, country) VALUES (?,?)"); $res->execute(array_values($data)); Link to comment Share on other sites More sharing options...
ludo1960 Posted January 17, 2019 Author Share Posted January 17, 2019 PDO it is then! Does that mean I don't need the: $res->bind_param('ss', ...array_values($data)); line then? Thanks again. Link to comment Share on other sites More sharing options...
Barand Posted January 17, 2019 Share Posted January 17, 2019 Binding is optional with PDO or you can pass an array of values (as I did) To get the most out of, set the right options when connecting. This my connection code (The constants and function definition are in an include file) const HOST = 'localhost'; const USERNAME = '????'; const PASSWORD = '????'; const DBNAME = "????"; function pdoConnect() { $dsn = "mysql:dbname=".DBNAME."; host=".HOST."; charset=utf8"; $db = new pdo($dsn, USERNAME, PASSWORD, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, ]); return $db; } $db = pdoConnect(); [EDIT] It also lets you used name placeholders which can be helpful if you have a large number in your query. So I could have used $res = $db->prepare("INSERT INTO ludo (county, country) VALUES (:county, :country)"); $res->execute($data); Link to comment Share on other sites More sharing options...
ludo1960 Posted January 17, 2019 Author Share Posted January 17, 2019 Perfect! As I understand it PDO stands for PHP Data Object, so instead of firing an array at the database, is it not better to fire an object at the database? Link to comment Share on other sites More sharing options...
Barand Posted January 17, 2019 Share Posted January 17, 2019 That isn't what it means. The PDO connection is and object. A result from a query is an object. A statement returned by preparing a query is an object. (This is also true for mysqli but, unlike mysqli, the PDO result and statement objects have identical methods. With mysqli you have two different sets of methods to learn). As for firing objects at the database, there are object databases out there but MySQL isn't one of them. Link to comment Share on other sites More sharing options...
ludo1960 Posted January 17, 2019 Author Share Posted January 17, 2019 All working nicely, thanks for taking the time to walk me through it, Cheers!!! Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.