Antonio Conte Posted July 24, 2011 Share Posted July 24, 2011 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 More sharing options...
Jonathon Posted July 24, 2011 Share Posted July 24, 2011 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 More sharing options...
Antonio Conte Posted July 24, 2011 Author Share Posted July 24, 2011 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 More sharing options...
Jonathon Posted July 24, 2011 Share Posted July 24, 2011 And do all your values turn out ok if you echo them out in the script? (Although i'm sure with your knowledge of programming, you've already tried this) Link to comment Share on other sites More sharing options...
Antonio Conte Posted July 24, 2011 Author Share Posted July 24, 2011 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 More sharing options...
Jonathon Posted July 24, 2011 Share Posted July 24, 2011 :/ 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 More sharing options...
Antonio Conte Posted July 24, 2011 Author Share Posted July 24, 2011 The for-loops are only creating a string that is later inserting rows using mysqli_query($insert_info); I would think this is perfectly fine as the loops are done before mysqli_query() i executed. Link to comment Share on other sites More sharing options...
Jonathon Posted July 24, 2011 Share Posted July 24, 2011 I'm not doubting you, I just feel like i've seen something similar somewhere. But I couldn't tell you where, when, what the problem was at all i'm afraid. Link to comment Share on other sites More sharing options...
Antonio Conte Posted July 24, 2011 Author Share Posted July 24, 2011 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 More sharing options...
HartleySan Posted July 24, 2011 Share Posted July 24, 2011 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. 1 Link to comment Share on other sites More sharing options...
Antonio Conte Posted July 24, 2011 Author Share Posted July 24, 2011 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. Thanks again. Link to comment Share on other sites More sharing options...
Recommended Posts