NewbieAmi Posted January 15, 2018 Share Posted January 15, 2018 (edited) Hello everyone, i'm starting learning php by doing a small project (database) and i have some issues while working with array namely filling the tables with my arrays. ps: i didn't write some part of the code (database parameters etc) because its already working, so please don't pay attention to that <?php $tab= array ( 0 => array ( 'name' => 'rocky', 'eigenschaft' => 'stark|mutig|ruhig|liebevoll', 'verhalten' => 'ist immer in Bewegung. spielt gerne', ), 1 => array ( 'name' => 'mike', 'eigenschaft' => 'ängstlich|nervös|alt|langsam', 'verhalten' => 'will nur fleisch essen. spielt nicht gerne',), ) foreach ($tab as $key => $i){ for($i=0; $i<count($tab[]); $i++){ $query = "INSERT INTO tab VALUES ('" .$tab["name"][$i]."','" .$tab["eigenschaft"][$i]."','" .$tab["verhalten"][$i] ."');" ; } mysqli_query($link,$query); } ?> Thanks for your help Edited January 15, 2018 by NewbieAmi Quote Link to comment https://forums.phpfreaks.com/topic/306208-working-with-array/ Share on other sites More sharing options...
Barand Posted January 15, 2018 Share Posted January 15, 2018 foreach ($tab as $key => $i){In each loop, $i is an array containing the record data, so you don't need that for() loop. foreach ($tab as $key => $i){ $query = "INSERT INTO tab VALUES ( '{$i['name']}', '{$i['eigenschaft']}', '{$i[''verhalten']}' )"; mysqli_query($link, $query); } Instead of putting values into the query string, learn how to use prepared statements to protect from SQL injection attacks. It is easier with PDO library than with mysqli. Quote Link to comment https://forums.phpfreaks.com/topic/306208-working-with-array/#findComment-1555374 Share on other sites More sharing options...
Barand Posted January 15, 2018 Share Posted January 15, 2018 PS Same code but using prepared statements // // mysqli version // $query = "INSERT INTO tab VALUES (?,?,?)"; $stmt = $link->prepare($query); $link->bind_param('sss', $i['name'], $i['eigenschaft'], $i['verhalten']); foreach ($tab as $key => $i){ $stmt->execute(); } // // PDO version // $query = "INSERT INTO tab VALUES (?,?,?)"; $stmt = $link->prepare($query); foreach ($tab as $key => $i){ $stmt->execute($i); } Quote Link to comment https://forums.phpfreaks.com/topic/306208-working-with-array/#findComment-1555376 Share on other sites More sharing options...
NewbieAmi Posted January 16, 2018 Author Share Posted January 16, 2018 PS Same code but using prepared statements // // mysqli version // $query = "INSERT INTO tab VALUES (?,?,?)"; $stmt = $link->prepare($query); $link->bind_param('sss', $i['name'], $i['eigenschaft'], $i['verhalten']); foreach ($tab as $key => $i){ $stmt->execute(); } // // PDO version // $query = "INSERT INTO tab VALUES (?,?,?)"; $stmt = $link->prepare($query); foreach ($tab as $key => $i){ $stmt->execute($i); } Hello Barand, thank you. let me try this and come to you later. (i'm not so ggod with oop but i think u r right concerning sql injections. thks) Quote Link to comment https://forums.phpfreaks.com/topic/306208-working-with-array/#findComment-1555387 Share on other sites More sharing options...
NewbieAmi Posted January 16, 2018 Author Share Posted January 16, 2018 Hello Barand, here is my entire code but doesnt do anything and i dont receive any warnings or errors (blank page). thks <?php <html> <head> <title>Initializing the database</title> <meta charset="utf-8" /> </head> <body> <?php $tab= array ( 0 => array ( 'name' => 'rocky', 'eigenschaft' => 'stark|mutig|ruhig|liebevoll', 'verhalten' => 'ist immer in Bewegung. spielt gerne', ), 1 => array ( 'name' => 'mike', 'eigenschaft' => 'ängstlich|nervös|alt|langsam', 'verhalten' => 'will nur fleisch essen. spielt nicht gerne',), ); /*$zutaten = array( 'zutaten_kategorie' => array ( 'allgemeines' => 'organisch| anorganisch', 'proteine' => 'Rind | Lahm | Huhn | Fisch', 'kohlenhydrate' => 'Mais | Reis | Weizen | Hafer', 'fette' => ' Rindertalg | Schweineschmalz | Geflügelfett | Fischöl',); ); */ $kategorie = array( 'allgemeines' => 'organisch | anorganisch', 'proteine' => 'Rind | Lahm | Huhn | Fisch', 'kohlenhydrate' => 'Mais | Reis | Weizen | Hafer', 'fette' => 'Rindertalg | Schweineschmalz | Geflügelfett Fischöl',); //host, user, pass, base include("Parameters.php"); //function query($link,$query) ... include("Fonctions.inc.php"); // Connecting to the MySQL server $mysqli=mysqli_connect($host,$user,$pass) or die("Problem of connection to the MySQL server :".mysqli_error()); //delete if exists // Creating the database query($mysqli, 'CREATE DATABASE IF NOT EXISTS ' .$base); // Selecting the database mysqli_select_db($mysqli, $base) or die("Base selection failed! : $base"); /*function ExecSqlFile($sqlFileToExecute, $link) { if (!file_exists($sqlFileToExecute)) { echo "File not found!"; return false; } $querys = explode("\n", file_get_contents($sqlFileToExecute)); foreach ($querys as $q) { $q = trim($q); if (strlen($q)) { mysqli_query($link, $q) or die(mysqli_error()); } } return true; } */ //Creation of tables in the database query($mysqli, "CREATE TABLE IF NOT EXISTS `besitzer` ( `besitzer_id` INT PRIMARY KEY AUTO_INCREMENT, `besitzer_name` VARCHAR(50) NOT NULL, `besitzer_email` VARCHAR (50) UNIQUE NOT NULL, `besitzer_password` VARCHAR(50) NOT NULL, `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP )" ); query($mysqli, "CREATE TABLE IF NOT EXISTS `dogs` ( `dog_id` INT PRIMARY KEY AUTO_INCREMENT, `dog_name` VARCHAR(255) UNIQUE NOT NULL, `dog_eigenschaft` VARCHAR(500) UNIQUE NOT NULL, `dog_verhalten` VARCHAR(500) DEFAULT NULL, `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (dog_author) REFERENCES besitzer(besitzer_name) )" ); query($mysqli, "CREATE TABLE IF NOT EXISTS `zutaten` ( `zutaten_id` INT AUTO_INCREMENT, `dog_id` INT(11) NOT NULL, `zutaten_kategorie` VARCHAR(50) NOT NULL, `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`zutaten_id`,`zutaten_kategorie`, `dog_id`), FOREIGN KEY (dog_id) REFERENCES dogs(dog_id) )" ); query($mysqli, "CREATE TABLE IF NOT EXISTS `kategorie` ( `kategorie_id` INT AUTO_INCREMENT, `dog_id` INT(11) NOT NULL, `allgemeines` VARCHAR(50) NOT NULL, `proteine` VARCHAR(50) NOT NULL `kohlenhydrate` VARCHAR(50) NOT NULL `fette` VARCHAR(50) NOT NULL `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`zutaten_id`,`zutaten_kategorie`, `dog_id`), FOREIGN KEY (dog_id) REFERENCES dogs(dog_id) )" ); mysqli_close($mysqli); // inserting in the tables $query = "INSERT INTO tab VALUES (?,?,?)"; $stmt = $link->prepare($query); $link->bind_param('sss', $i['name'], $i['eigenschaft'], $i['verhalten']); foreach ($tab as $key => $i){ $stmt->execute(); } $query2 = "INSERT INTO kategorie VALUES (?,?,?,?)"; $stmt2 = $link->prepare($query2); $link->bind_param('sss', $i['allgemeines'], $i['proteine'], $i['kohlenhydrate'], $i['fette']); foreach ($tab as $key => $i){ $stmt2->execute(); } mysqli_query($link, $query); ?> <p> Database created with success </p> </body> </html> ?> Quote Link to comment https://forums.phpfreaks.com/topic/306208-working-with-array/#findComment-1555388 Share on other sites More sharing options...
Barand Posted January 16, 2018 Share Posted January 16, 2018 Where do I start? The code I gave you was for the scenario that you posted. This time you have created a database with four tables, none of which is called "tab". Why would you expect an insert into table tab to work? Your second insert is to the "kategorie" table yet you are looping through the same $tab array even though the required data is going to be completely different (eg there are now four columns). Also the structure of your $kategorie array is completely different from that of the $cat array and so needs to be processed differently. More general, when you insert data the way you have, you have to provide data for every column in the table in the order they were specified in the table. In you kategorie insert you only provide four values so you must specify which columns those values are for. EG INSERT INTO kategorie (allgemines,proteine, kohlenhydrate, fette) VALUES (?,?,?,?)When using bind_param the 'sss' defines the data type of the following parameters (in this case strings). Your second query has four values so you need 'ssss'. mysqli_query() is for executing unbound queries. When using prepared queries the execute() excutes it (no surprise there). Remove the mysqli_query(). Quote Link to comment https://forums.phpfreaks.com/topic/306208-working-with-array/#findComment-1555398 Share on other sites More sharing options...
NewbieAmi Posted January 16, 2018 Author Share Posted January 16, 2018 (edited) Where do I start? The code I gave you was for the scenario that you posted. This time you have created a database with four tables, none of which is called "tab". Why would you expect an insert into table tab to work? Your second insert is to the "kategorie" table yet you are looping through the same $tab array even though the required data is going to be completely different (eg there are now four columns). Also the structure of your $kategorie array is completely different from that of the $cat array and so needs to be processed differently. More general, when you insert data the way you have, you have to provide data for every column in the table in the order they were specified in the table. In you kategorie insert you only provide four values so you must specify which columns those values are for. EG INSERT INTO kategorie (allgemines,proteine, kohlenhydrate, fette) VALUES (?,?,?,?)When using bind_param the 'sss' defines the data type of the following parameters (in this case strings). Your second query has four values so you need 'ssss'. mysqli_query() is for executing unbound queries. When using prepared queries the execute() excutes it (no surprise there). Remove the mysqli_query(). Thank you! i correct my mistakes. here we go ... but same result <?php <html> <head> <title>Initializing the database</title> <meta charset="utf-8" /> </head> <body> <?php $dogs= array ( 0 => array ( 'name' => 'rocky', 'eigenschaft' => 'stark|mutig|ruhig|liebevoll', 'verhalten' => 'ist immer in Bewegung. spielt gerne', ), 1 => array ( 'name' => 'mike', 'eigenschaft' => 'ängstlich|nervös|alt|langsam', 'verhalten' => 'will nur fleisch essen. spielt nicht gerne',), ); /*$zutaten = array( 'zutaten_kategorie' => array ( 'allgemeines' => 'organisch| anorganisch', 'proteine' => 'Rind | Lahm | Huhn | Fisch', 'kohlenhydrate' => 'Mais | Reis | Weizen | Hafer', 'fette' => ' Rindertalg | Schweineschmalz | Geflügelfett | Fischöl',); ); */ $kategorie = array( 'allgemeines' => 'organisch | anorganisch', 'proteine' => 'Rind | Lahm | Huhn | Fisch', 'kohlenhydrate' => 'Mais | Reis | Weizen | Hafer', 'fette' => 'Rindertalg | Schweineschmalz | Geflügelfett Fischöl',); //host, user, pass, base include("Parameters.php"); //function query($link,$query) ... include("Fonctions.inc.php"); // Connecting to the MySQL server $mysqli=mysqli_connect($host,$user,$pass) or die("Problem of connection to the MySQL server :".mysqli_error()); //delete if exists // Creating the database query($mysqli, 'CREATE DATABASE IF NOT EXISTS ' .$base); // Selecting the database mysqli_select_db($mysqli, $base) or die("Base selection failed! : $base"); /*function ExecSqlFile($sqlFileToExecute, $link) { if (!file_exists($sqlFileToExecute)) { echo "File not found!"; return false; } $querys = explode("\n", file_get_contents($sqlFileToExecute)); foreach ($querys as $q) { $q = trim($q); if (strlen($q)) { mysqli_query($link, $q) or die(mysqli_error()); } } return true; } */ //Creation of tables in the database query($mysqli, "CREATE TABLE IF NOT EXISTS `besitzer` ( `besitzer_id` INT PRIMARY KEY AUTO_INCREMENT, `besitzer_name` VARCHAR(50) NOT NULL, `besitzer_email` VARCHAR (50) UNIQUE NOT NULL, `besitzer_password` VARCHAR(50) NOT NULL, `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP )" ); query($mysqli, "CREATE TABLE IF NOT EXISTS `dogs` ( `dog_id` INT PRIMARY KEY AUTO_INCREMENT, `dog_name` VARCHAR(255) UNIQUE NOT NULL, `dog_eigenschaft` VARCHAR(500) UNIQUE NOT NULL, `dog_verhalten` VARCHAR(500) DEFAULT NULL, `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (dog_author) REFERENCES besitzer(besitzer_name) )" ); query($mysqli, "CREATE TABLE IF NOT EXISTS `zutaten` ( `zutaten_id` INT AUTO_INCREMENT, `dog_id` INT(11) NOT NULL, `zutaten_kategorie` VARCHAR(50) NOT NULL, `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`zutaten_id`,`zutaten_kategorie`, `dog_id`), FOREIGN KEY (dog_id) REFERENCES dogs(dog_id) )" ); query($mysqli, "CREATE TABLE IF NOT EXISTS `kategorie` ( `kategorie_id` INT AUTO_INCREMENT, `dog_id` INT(11) NOT NULL, `allgemeines` VARCHAR(50) NOT NULL, `proteine` VARCHAR(50) NOT NULL `kohlenhydrate` VARCHAR(50) NOT NULL `fette` VARCHAR(50) NOT NULL `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`zutaten_id`,`zutaten_kategorie`, `dog_id`), FOREIGN KEY (dog_id) REFERENCES dogs(dog_id) )" ); mysqli_close($mysqli); // inserting in the tables $query = "INSERT INTO dogs VALUES (?,?,?)"; $stmt = $link->prepare($query); $link->bind_param('sss', $i['name'], $i['eigenschaft'], $i['verhalten']); foreach ($dogs as $key => $i){ $stmt->execute(); } $query2 = "INSERT INTO kategorie (allgemeines, proteine, kohlenhydrate, fette) VALUES (?,?,?,?)"; $stmt2 = $link->prepare($query2); $link->bind_param('ssss', $i['allgemeines'], $i['proteine'], $i['kohlenhydrate'], $i['fette']); foreach ($kategorie as $key => $i){ $stmt2->execute(); } //mysqli_query($link, $query); ?> <p> Database created with success </p> </body> </html> ?> thank you for your time Edited January 16, 2018 by NewbieAmi Quote Link to comment https://forums.phpfreaks.com/topic/306208-working-with-array/#findComment-1555399 Share on other sites More sharing options...
mac_gyver Posted January 16, 2018 Share Posted January 16, 2018 (edited) the posted code contains at least two php syntax errors and would be throwing php fatal parse errors. do you have php's error_reporting set to E_ALL and display_errors set to ON, in the php.ini on your development system, so that php would report and display all the errors it detects? you cannot put these settings into your code and have them report parse errors in the same file because your code never runs when there is type of error. next, once you get the code to run, your database connection is being assigned to the variable $mysqli, you are closing the database connection after creating the database tables, then using a nonexistent variable $link in your original code and the copied code, which would be throwing more php errors. you would want to use the same variable name in all the code and don't close the database connection when you still have code that needs the connection, or better yet, let php automatically close the connection for you when your script ends. lastly, your code needs to have error handling for all the database statements. you do have some error handling for the connect and select statements, but not for the prepare/execute statements (the bind statement doesn't cause a database action and would only throw php error, which are handled by the error_reporting/display_errors settings), and possibly your custom query() function. the easiest way of handling database statement errors is to use exceptions, by simply setting the error mode to exceptions before you make the mysqli connection, and let php catch the exception, where it will use its error_reporting, display_errors, and log_errors settings to control what happens with the actual error information. this will also let you remove any existing database error handling logic, thereby simplifying your code. to set the mysqli error mode to exceptions, add the following line of code before you make the mysqli connection - mysqli_report(MYSQLI_REPORT_ALL); if you have php's error_reporting/display_errors set up properly and you always have error handling for statements that can fail, your code will either work or php/your code will tell you why it isn't working. Edited January 16, 2018 by mac_gyver Quote Link to comment https://forums.phpfreaks.com/topic/306208-working-with-array/#findComment-1555400 Share on other sites More sharing options...
NewbieAmi Posted January 16, 2018 Author Share Posted January 16, 2018 the posted code contains at least two php syntax errors and would be throwing php fatal parse errors. "do you have php's error_reporting set to E_ALL and display_errors set to ON, in the php.ini on your development system, so that php would report and display all the errors it detects? you cannot put these settings into your code and have them report parse errors in the same file because your code never runs when there is type of error. " => Yes it has already done before and i got some reports with another test code "next, once you get the code to run, your database connection is being assigned to the variable $mysqli, you are closing the database connection after creating the database tables, then using a nonexistent variable $link in your original code and the copied code, which would be throwing more php errors. you would want to use the same variable name in all the code and don't close the database connection when you still have code that needs the connection, or better yet, let php automatically close the connection for you when your script ends." => ok i put it at the end of my code (or inserting part) $link is existent into a script calls "Fonctions.inc.php" "lastly, your code needs to have error handling for all the database statements. you do have some error handling for the connect and select statements, but not for the prepare/execute statements (the bind statement doesn't cause a database action and would only throw php error, which are handled by the error_reporting/display_errors settings), and possibly your custom query() function. the easiest way of handling database statement errors is to use exceptions, by simply setting the error mode to exceptions before you make the mysqli connection, and let php catch the exception, where it will use its error_reporting, display_errors, and log_errors settings to control what happens with the actual error information. this will also let you remove any existing database error handling logic, thereby simplifying your code. to set the mysqli error mode to exceptions, add the following line of code before you make the mysqli connection -" => ok thanks mysqli_report(MYSQLI_REPORT_ALL); if you have php's error_reporting/display_errors set up properly and you always have error handling for statements that can fail, your code will either work or php/your code will tell you why it isn't working. Quote Link to comment https://forums.phpfreaks.com/topic/306208-working-with-array/#findComment-1555405 Share on other sites More sharing options...
NewbieAmi Posted January 16, 2018 Author Share Posted January 16, 2018 the posted code contains at least two php syntax errors and would be throwing php fatal parse errors. "do you have php's error_reporting set to E_ALL and display_errors set to ON, in the php.ini on your development system, so that php would report and display all the errors it detects? you cannot put these settings into your code and have them report parse errors in the same file because your code never runs when there is type of error. " => Yes it has already done before and i got some reports with another test code "next, once you get the code to run, your database connection is being assigned to the variable $mysqli, you are closing the database connection after creating the database tables, then using a nonexistent variable $link in your original code and the copied code, which would be throwing more php errors. you would want to use the same variable name in all the code and don't close the database connection when you still have code that needs the connection, or better yet, let php automatically close the connection for you when your script ends." => ok i put it at the end of my code (or inserting part) $link is existent into a script calls "Fonctions.inc.php" "lastly, your code needs to have error handling for all the database statements. you do have some error handling for the connect and select statements, but not for the prepare/execute statements (the bind statement doesn't cause a database action and would only throw php error, which are handled by the error_reporting/display_errors settings), and possibly your custom query() function. the easiest way of handling database statement errors is to use exceptions, by simply setting the error mode to exceptions before you make the mysqli connection, and let php catch the exception, where it will use its error_reporting, display_errors, and log_errors settings to control what happens with the actual error information. this will also let you remove any existing database error handling logic, thereby simplifying your code. to set the mysqli error mode to exceptions, add the following line of code before you make the mysqli connection -" => ok thanks mysqli_report(MYSQLI_REPORT_ALL); if you have php's error_reporting/display_errors set up properly and you always have error handling for statements that can fail, your code will either work or php/your code will tell you why it isn't working. No change Quote Link to comment https://forums.phpfreaks.com/topic/306208-working-with-array/#findComment-1555406 Share on other sites More sharing options...
mac_gyver Posted January 16, 2018 Share Posted January 16, 2018 if the php settings are set where and to the values i suggested, you should be getting the following error - Parse error: syntax error, unexpected '<', expecting end of file in your_file.php on line 2 Quote Link to comment https://forums.phpfreaks.com/topic/306208-working-with-array/#findComment-1555407 Share on other sites More sharing options...
Barand Posted January 16, 2018 Share Posted January 16, 2018 The advice I gave you about the column names applies to the dog table insert too. You are inserting three values into a four-column table, so specify which three. Quote Link to comment https://forums.phpfreaks.com/topic/306208-working-with-array/#findComment-1555409 Share on other sites More sharing options...
NewbieAmi Posted January 22, 2018 Author Share Posted January 22, 2018 thank to all of you Quote Link to comment https://forums.phpfreaks.com/topic/306208-working-with-array/#findComment-1555650 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.