Jump to content
Larry Ullman's Book Forums

Antonio Conte

  • Content Count

  • Joined

  • Last visited

  • Days Won


Everything posted by Antonio Conte

  1. 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.
  2. 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.
  3. Download an IDE like Netbeans or Eclipse. They're both free and will help you with syntax errors like these.
  4. In some other languages, like Java, you have to declare a variable before you use it. What declaring them really does, it allocating a place in the memory for the information you save in a variable. It might be a bit technical, but you should not really care why it's like this. Just understand how to use variables. This is Java with predeclared variables: String firstName; String lastName; String adress; Int phone; Int age; Int credit_card_number = null; // now we can use these variables below in the script. firstname = "Thomas"; lastname = "Lastname"; String fullname = (firstname . ' ' . lastname); // echo statement PHP also is a loosly typed language, unlike Java. That means you don't have to set the type of information the variable should save (numbers, text, booleans, etc). It's done automaticly, like declaration is. $firstname = "Thomas"; $lastname = "Lastname"; $fullname = $firstname . ' ' . $lastname; echo $lastname
  5. You could of course set the flag AFTER all the $_POST-variables are right $flag = null; isset($_POST['first'], $_POST['second'], $_POST['third'], $_POST['fourth'], $_POST['fifth'], $_POST['sixth']) { echo 'This is actually allowed! All must be set! USE IT'; $flag = TRUE; } if ($flag == TRUE) { header(); // send them to next page }
  6. Are you using utf8_unicode_ci or utf8_general_ci? unicode_ci should support at lot additional characters.
  7. UTF-8 SHOULD support Indian characters. I'm thinking it could be your php program that saves that file as another charset. Have your tried to manually insert Indian characters into your database? I struggle alot with the same problem as you. Please tell me if you solve this.
  8. Glad it worked out for you. And good you are determent to get it done "your way". I've also learned something about this. Remember this: If a time function returns null for some unexplainable reason (like with CONVERT_TZ(), ADDTIME(), etc) you should add IF_NULL() inside it. I learned this just this week.
  9. Shoun. I tried to sort on datetime column now. Max(date_column) is returning the most recent date. Min(date_column) is returning the oldest date. Date_format is for displaying dates different. You could use European form like (dd.mm.yyyy) or anything you want. It's only for display. CONVERT_TZ() is for timezone conversions. It will allow you to use a GMT datetime and add/subract automaticly to your timezone. Do you store the user's timezone? Else, it's useless for you. This will do what you need: SELECT DATE_FORMAT(MAX(posted_on), '%e-%b-%y %l:%i %p') AS last, DATE_FORMAT(MIN(posted_on), '%e-%b-%y %l:%i %p') AS first FROM table_with_your_posts I have tested it myself, and it works perfectly with my data. (32 football players with birthdates saved) The problem was that MAX() and MIN() calculates the dates wrong if it's formated BEFORE finding the MAX and MIN. Because the query starts with the most inner function, this would be the right way to do it. If you really need timezone conversions, add it inside the MAX() and MIN()-functions to get it correct.
  10. You only need the most recent post? Forget about that code, and do it all in the query SELECT * FROM table_with_posts ORDER BY posted_on DESC LIMIT 1 That should produce the latest post in the table according to post date. Same for first post. use ASC instead of DESC in the order by-clause. Replace the astrix (*) with the columns you would like to display, and alter the table name. Then you are ready to go. If you need usernames, etc, a simple join is enough. SELECT posts.*, users.* FROM table_with_posts AS posts INNER JOIN table_with_users AS users ON (posts.user_id = users.users_id) ORDER BY posted_on DESC LIMIT 1 Edit: 100 posts. Enjoying the moment. Moment's passed. Life goes on.
  11. What he probably mean, Larry is that within classes, you cannot use $array[]. You have to use self::$array[] or classname::$array[] I know these things seem obvious for you, but a slow mind like mine took some time to remember to use this style.
  12. SELECT * FROM tableA, tableB .... A tips thought; User more descriptive names for your tables and columns.
  13. Hey everyone First off; I have NO knowlage of Ajax. I do not intend to learn it as this point neither. I'm working of saving huge amount of football statistics, but I still need the powers of Ajax! I hope you will let this one slide. I'm displaying information about the players this way: <div style="width: 100%; float: left;"> <p><input type="checkbox" name="player[0][id]" value="1" />Gianluigi Buffon</p> <input type="text" name="player[0][time]" value="" /> <select name="player[0][sub]"> <option value="0">Full match</option> <option value="1">Subbed Inn</option> <option value="2">Subbed Out</option> </select> </div> <div style="width: 100%; float: left;"> <p><input type="checkbox" name="player[1][id]" value="13" />Alexander Manninger</p> <input type="text" name="player[1][time]" value="" /> <select name="player[1][sub]"> <option value="0">Full match</option> <option value="1">Subbed Inn</option> <option value="2">Subbed Out</option> </select> </div> ......... ......... What I want to achive: - Every clicked checkbox gets a BLUE icon to the right - Players subbed inn gets a GREEN icon to the right - Players subbed out gets a RED icon to the right. Something like this: Is this possible with Ajax? Anyone willing to help a forum member in need out with this?
  14. Varbinary should be varchar(32). I actually have a Varbinary in my setup. It looks like it is like a number of binaries (zeroes and ones) You don't need to change your databas type. You can do this be clicking the table, selection "Operations", and select a different DB type. There are some benefits and some drawbacks regarding both, so it really doesn't matter else your told to use a spesific type. Unique is kind of like Primary key. It means it cannot exist similar data. You can do a combined unique with several columns. A good suggestion is often an auto_increment Primary key and a combined Unique to make sure data cannot be saved twice. (If you need to make sure of this) Key could be several things. Primary keys, foreign key and possibly some others. A foreign key means a refferance to another tables Primary key. If you ever wanted to use user_id in another table, say sales, the user_id would be a foreign key to the users table. Index and Full Text is used for database optimalization. If a number (lets say user_id, user_name rows is often used in queries, an index would speed up queries when both these coulumns are used. Full Text is used to speed up long columns of text. That is good if you need to perform searches on text. A good example is you need to search in forum post. Full text is made for this. Hope this answeers some of your questions.
  15. If your SQL is bloated, that would probably call for some inner joins and creation of views to make your job easier. The syntax for Inner joins are pretty easy, CREATE VIEW main_view AS SELECT main.*, (other rows) FROM main_table as main INNER JOIN second_tale ON (main.id = second.id) ..... When you have written the views you like, SELECT * FROM main_view is enough. You cannot delete/edit or add to every view. It's for display, as the name suggests.
  16. If you know the position in the array, it is. $array[5] is the 6th position in the array. (Counts from zero). I guess #array[5][0] would be the 6th array with first position in that array.
  17. ".......'Title' = '2011-07-06', 'Date' = 'Hello World - Post 1', at line 1 Hope you can spot your error. Strings don't work inside datetime datatypes, you know
  18. I LOVE MYSQL! It's always a challenge to do new things, but you can really see the benefit of learning how to do things. I use the following query to find the WHOLE number of weeks and days before the injury is finished: SELECT player_id, ROUND((datediff( IFNULL(date_add(`recovery_date`, INTERVAL `variable` DAY ) , `recovery_date`) , `injury_date`) / 7 )) AS weeks, ROUND((datediff( IFNULL(date_add(`recovery_date`, INTERVAL `variable` DAY ) , `recovery_date`) , `injury_date`))) - (ROUND((datediff(IFNULL(date_add(`recovery_date`, INTERVAL `variable` DAY ) , `recovery_date` ) , `injury_date` ) /7 )) *7) AS days FROM `abc_injuries Edit: This would be right SELECT player_id, ROUND( (datediff( IFNULL( date_add( `recovery_date` , INTERVAL `variable` DAY ) , `recovery_date` ) , `injury_date` ) /7 ) - 0.5) AS weeks, (ROUND( (datediff( IFNULL( date_add( `recovery_date` , INTERVAL `variable` DAY ) , `recovery_date` ) , `injury_date` ) ) ) - ( ROUND( (datediff( IFNULL( date_add( `recovery_date` , INTERVAL `variable` DAY ) , `recovery_date` ) , `injury_date` ) ) ) - ROUND( datediff( IFNULL( date_add( `recovery_date` , INTERVAL `variable` DAY ) , `recovery_date` ) , `injury_date` ) %7 ) )) AS days FROM `abc_injuries` LIMIT 0 , 100 I tried to explain how this works. I just realized I can't. Edit 2: Had to subtract the number of weeks with 0.5 to prevent 6 extra days to become for example 3.8 days and rounded up to 4 weeks. This is in the first part of the query (the AS weeks part)
  19. Hey Paul It would solve you problems, yes, but using quotes is probably easier. I understand what you say about threes and the forest, and I agree with you. Database systems need to work, that's the most important. If you get lost by the structure, it's probably a little too tricky for you. Integers don't need quotes in queries. Text does. The name DROP is likely the problem, but might work with quotes. try curtain_drop our something instead if it does not work.
  20. May I make some suggestions about your structure? I know very little about curtains, but I see you use a lot of "hard coded values". Have you thought about making additional tables to keep data integrity in place? regular_lining, three_inch, etc is possible to misspell (and not so easy to work with) Lets say you add a table lining: lining_id (auto_increment, primary key) lining_type (Varchar) It would allow you to use lining_id as the value to save in your database. It would also make number functions easyer. Like to show how many curtains with regular lining you have. This can be extended to most of your other things to. SELECT lining_type, count(lining_id) FROM lining WHERE lining_id = 8 GROUP BY lining_type asc, lining_id asc Example output: Regular lining (15 available) Silk lining (3 available) Ragged lining (7 available) Just a suggestion.
  21. Hello everyone. Time for another challenge. This time, I need to save stats about player injuries. I need some help with the thought process here. I'm thinking abot the following structure: - Players ID (INT) - injury date (Datetime) - ready to play date (Datetime) - Day fixer (INT, default null) - Type of injury (String (OR foreign keys to injury_type table)) ------------------------------------------- What I need with the data: - Calculate the number of weeks before the injury is over. I also think about using the field additional days for the possibility of adding/subtract a number of days for when the injury is over. ------------------------------------------- Hope some of you know mysql date functions. I'm want to calculate the number of weeks and days between INJURY DATE and (FIT DATE, plus/minus a number of days). How it will be displayed: - Player name - Player injury type - Ready in (about) X number of weeks/(days if less than a week) Thank you in advanced. I will use some time to think about how to solve this.
  22. I have some school background when it comes to database normalization. I can tell you there is a few more possibilities, but those might not be so easy to work with. Send that PM anytime. I have to few people to talk php with.
  23. That's 100% right and the way I've done things, abigail. Don't know if you tried to help our just explained it, but thanks anyway. I've actually solved this issue, but I and HartleySan are just discussing table structures. Larry could label this is [sOLVED] if he cares. Thanks everyone for your help. I could explain more over PM or even MSN/Skype if you want HartleySan.
  24. Genius, Paul. Probably gonna do something like this sooner or later. Thanks.
  • Create New...