Jump to content
Larry Ullman's Book Forums

Recommended Posts

Hello,

 

I'm struggling to post data into the database. As the VAR_DUMP() explains, I suspect is has something to do with values beeing sent as STRINGS instead of INT.

 

The script is generating a text query that WORK in PhpMyAdmin. (I echo the variable $insert_info after mysql_query($insert_info)

 

-------------------------------------------

 

The PHP code for producing the SQL-query looks like this:

 

/* BUILDS QUERY FROM $_POST[]
* - The first if is looking for a submit with value "Lag Statistikk"
* 
*/
if ($_POST['querygen'] == "Lag statistikk") {

	/* Create a string for the Query. 
		* The string is buildt with concatenation to this string ( .= )
		* Each multi-dimensional $_POST-array starts from Zero....
		* and the for-loops are based on an earlier set "number_of_X" - $_POST-values"
		*/

	// Insert for table abc_players_captain
	$_POST[giornata]   	= (int) $_POST[giornata];
	$_POST[players_captain] = (int) $_POST[players_captain];
	$info_insert = "INSERT INTO abc_players_captain (match_id, captain_id) VALUES ({$_POST[giornata]}, {$_POST[players_captain]});";

	// Insert for table abc_players_goals
	$_POST['number_of_goals']= (int) $_POST['number_of_goals'];
	for ($i = 0; $i < $_POST['number_of_goals']; $i++) {
		if (isset($_POST[goal][$i])) {
			$_POST[goal][$i][goal_id]   = (int) $_POST[goal][$i][goal_id];
			$_POST[goal][$i][goal_time] = (int) $_POST[goal][$i][goal_time];
			$info_insert .= "INSERT INTO abc_players_goals (match_id, player_id, goal_time) VALUES ({$_POST[giornata]}, {$_POST[goal][$i][goal_id]}, {$_POST[goal][$i][goal_time]});";
		}
	}

	// Insert for table abc_players_assists
	$_POST['number_of_assists'] = (int) $_POST['number_of_assists'];
	for ($i = 0; $i < $_POST['number_of_assists']; $i++) {
		if (isset($_POST[assist][$i])) {
			$_POST[assist][$i][assist_id] 	= (int) $_POST[assist][$i][assist_id];
			$_POST[assist][$i][assist_time] = (int) $_POST[assist][$i][assist_time];
			$info_insert .= "INSERT INTO abc_players_assists (match_id, player_id, assist_time) VALUES ({$_POST[giornata]}, {$_POST[assist][$i][assist_id]}, {$_POST[assist][$i][assist_time]});";
		}
	}

	// Insert for table abc_players_cards
	$_POST['number_of_cards'] = (int) $_POST['number_of_cards'];
	for ($i = 0; $i < $_POST['number_of_cards']; $i++) {
		if (isset($_POST[card][$i])) {
			$_POST[card][$i][card_id]   = (int) $_POST[card][$i][card_id];
			$_POST[card][$i][card_time] = (int) $_POST[card][$i][card_time];
			$_POST[card][$i][card_type] = (int) $_POST[card][$i][card_type];
			$info_insert .= "INSERT INTO abc_players_cards (match_id, player_id, card_time, card_type) VALUES ({$_POST[giornata]}, {$_POST[card][$i][card_id]}, {$_POST[card][$i][card_time]}, {$_POST[card][$i][card_type]});";
		}
	}

$query_result = mysqli_query($connect, $info_insert);

// Display success/error-message depending on TRUE/FALSE of $query_result
if ($query_result) {
	echo '<h2>Sucess</h2>';
}
else {
	echo '<h2>Query Failure</h2>
			'.mysqli_error($connect).' <br /> <br />
			<pre>'.$info_insert.'</pre>
	';	
}

// CLOSE connection
mysqli_close($connect);

} // IF ISSET($_POST[]) END

 

-------------------------------------------

 

Echo $insert_info and Mysql-error:

 

FAILURE:
You have an error in your SQL syntax; check the manual that corresponds  to your MySQL server version for the right syntax to use near '
INSERT INTO abc_players_goals (match_id, player_id, goal_time) VALUES (868' at line 1 

THE QUERY FOLLOWS:
INSERT INTO abc_players_captain (match_id, captain_id) VALUES (868, 10); 
INSERT INTO abc_players_goals (match_id, player_id, goal_time) VALUES (868, 10, 30); 
INSERT INTO abc_players_goals (match_id, player_id, goal_time) VALUES (868, 9, 78); 
INSERT INTO abc_players_goals (match_id, player_id, goal_time) VALUES (868, 27, 85); 
INSERT INTO abc_players_assists (match_id, player_id, assist_time) VALUES (868, 3, 30); 
INSERT INTO abc_players_assists (match_id, player_id, assist_time) VALUES (868, 23, 85); 
INSERT INTO abc_players_cards (match_id, player_id, card_time, card_type) VALUES (868, 4, 4, 1); 
INSERT INTO abc_players_cards (match_id, player_id, card_time, card_type) VALUES (868, 9, 67, 1);

 

-------------------------------------------

 

The table structures looks like this:

abc_players_captain (match_id, player_id*)

abc_players_goals (match_id, player_id*, goal_time)

abc_players_assists (match_id, player_id*, assist_time)

abc_players_cards (match_id, player_id*, card_type, card_time)

 

ALL columns are INT of different lengths. I've tripple-checked both the names and lengths of the columns to assure thats not the problem.

The query inserts data correctly when using phpMyAdmin as said earlier

Underlined: Primary key

Asterix (*): Foreign key

 

-------------------------------------------

This is the var_dump of the $_POST-array:

 

The for-loops in this script is based on $_POST-values number_of_goals, number_of_assists and number_of_cards. All these loops are working, as you can see by the query. (see below)

 

array(9) {
["giornata"]=>
int(868)
["players_captain"]=>
int(10)
["number_of_goals"]=>
int(3)
["number_of_assists"]=>
int(2)
["number_of_cards"]=>
int(2)
["goal"]=>
array(3) {
	[0]=>
	array(2) {
		["goal_id"]=>
		int(10)
		["goal_time"]=>
		int(30)
	}
	[1]=>
	array(2) {
		["goal_id"]=>
		int(9)
		["goal_time"]=>
		int(78)
	}
	[2]=>
	array(2) {
		["goal_id"]=>
		int(27)
		["goal_time"]=>
		int(85)
	}
}
["assist"]=>
array(2) {
	[0]=>
	array(2) {
		["assist_id"]=>
		int(3)
		["assist_time"]=>
		int(30)
	}
	[1]=>
	array(2) {
		["assist_id"]=>
		int(23)
		["assist_time"]=>
		int(85)
	}
}
["card"]=>
array(2) {
	[0]=>
	array(3) {
		["card_id"]=>
		int(4)
		["card_time"]=>
		int(4)
		["card_type"]=>
		int(1)
	}
	[1]=>
	array(3) {
		["card_id"]=>
		int(9)
		["card_time"]=>
		int(67)
		["card_type"]=>
		int(1)
	}
}
["querygen"]=>
string(14) "Lag statistikk"
}

 

-------------------------------------------

 

Hope you can help me solve this weird error. I just don't get it. <_<

 

Btw: I'm well aware of the dangerous of using $_POST[] directly in a script. This is just used by a few people behind a htaccess-restricted directory. The security is not as important at the moment as getting this to work. Please feel free to comment on that to if you'd like, but It's not the main focus right now.

Link to comment
Share on other sites

So it works if you just type the SQL into PHPMyAdmin?

But it doesn't work in the actual script?

 

I'd have to guess that the (int) is maybe failing it that regard. What happens if you use braces here. If that is where it fails.

 

 (int) {$_POST[goal][$i][goal_id]}

Link to comment
Share on other sites

I have tried to type cast directly in the query. It does not seem to work.

 

I've edited the post and added the type casting. Better not to confuse anybody on how it's done.

 

I also changed from single to double quotes in $insert_query .= " ", but no changes on that neighter. :(

Link to comment
Share on other sites

Yes. I tried to explain this in my first post. Sorry for not beeing clear enough. :)

 

This is both the error message and an echo of $insert_info

 

Echo $insert_info and Mysql-error:

 

FAILURE:
You have an error in your SQL syntax; check the manual that corresponds  to your MySQL server version for the right syntax to use near '
INSERT INTO abc_players_goals (match_id, player_id, goal_time) VALUES (868' at line 1 

THE QUERY FOLLOWS:
INSERT INTO abc_players_captain (match_id, captain_id) VALUES (868, 10); 
INSERT INTO abc_players_goals (match_id, player_id, goal_time) VALUES (868, 10, 30); 
INSERT INTO abc_players_goals (match_id, player_id, goal_time) VALUES (868, 9, 78); 
INSERT INTO abc_players_goals (match_id, player_id, goal_time) VALUES (868, 27, 85); 
INSERT INTO abc_players_assists (match_id, player_id, assist_time) VALUES (868, 3, 30); 
INSERT INTO abc_players_assists (match_id, player_id, assist_time) VALUES (868, 23, 85); 
INSERT INTO abc_players_cards (match_id, player_id, card_time, card_type) VALUES (868, 4, 4, 1); 
INSERT INTO abc_players_cards (match_id, player_id, card_time, card_type) VALUES (868, 9, 67, 1);

 

I have compared both table names, column names, data type and length of the fields. That is not the error.

 

This is also confirmed by the fact that phpMyAdmin inserts this data perfectly without errors. :/

Link to comment
Share on other sites

:/ This is strange, but I have no idea why i think this. But something (quite possibly / probably wrong) wants to say that you can't update MySQL using the for loop like this. I have no idea why I think this :/ (Which I realise is of little help to you - sorry :( )

Link to comment
Share on other sites

Just to be sure, I did this after the for-loops:

 

	// Copy final string
$final_query = $info_insert;
// RUN QUERY
$query_result = mysqli_query($connect, $final_query);

// Display success/error-message depending on TRUE/FALSE of $query_result
if ($query_result)
  	...
...
...

 

Same result :/

Link to comment
Share on other sites

Is it just me, or can you not execute multiple INSERT queries at the same time? What about executing the mysqli_query function after each time the $info_insert string is created? Also, it might be best to not append the query strings together in the $info_insert variable.

 

Other than that, I'm at a bit of a loss, unless of course, the $connect variable is the issue.

  • Upvote 1
Link to comment
Share on other sites

Stupid me. Thank you very much, Jonathan. I just changed mysqli_query to mysqli_multi_query, and everything works perfectly. :)

 

The manual says it can execute several queries from a concatenated string devided by a semicolon. In other words: Just what I'm doing here.

 

This is embarrassing. :rolleyes:

 

Thanks again. :P

Link to comment
Share on other sites

 Share

×
×
  • Create New...