Working with array

NewbieAmi · January 15, 2018

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

Barand · 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.

Barand · 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);        
}

NewbieAmi · 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)

NewbieAmi · 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>
?>

Barand · 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().

NewbieAmi · 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().

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

mac_gyver · 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.

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.

NewbieAmi · 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.

NewbieAmi · 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

mac_gyver · 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

Barand · 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.

NewbieAmi · January 22, 2018

thank to all of you

Sign In

Working with array

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Archived

Important Information