Jump to content
Larry Ullman's Book Forums

Antonio Conte

  • Content Count

  • Joined

  • Last visited

  • Days Won


Everything posted by Antonio Conte

  1. Stored functions seems awesome! Will check it out, as I have a query I REALLY need to improve on. The logic is not that hard in these calculation, but the query is not easily understandable. Btw: Congratulation on finishing your new book for print, Larry. Checked for the amazon widget in the article, but I couldn't find one. Adblock. Sorry.
  2. When should you use them? I've read how Larry uses stored procedures to calculate distances to shops. But when should you really use them? What needs should you have to take them to use? I started this thread to share tips and tricks here. Anyone else got something good to share?
  3. SELECT users.user_id AS user_id, users.username AS username, account.account_id AS accound_id, account.user_id AS account_user_id, account.last_name AS lastname, account.first_name AS firstname, DATE_FORMAT(account.registration_date, '%M %d, %Y') AS registration_date FROM users AS users INNER JOIN account AS account ON ( users.user_id = account.user_id ) ORDER BY $order_by LIMIT $start, $display This should be working. Try to understand what Hartley wrote. It's an easy and good explanation.
  4. Thanks for feedback, corrections and additions, Larry. Views does also update automatically. I can confirm that. Anyone have something to share?
  5. You need to join data to the members-table to. I don't don't know all your table names, but this should about do it. You could maybe edit the messages.* to spesific data you want to display. SELECT users.user_id, users.last_name AS lastname, users.first_name AS firstname, DATE_FORMAT(users.registration_date, '%M %d, %Y') AS user_date, messages.* FROM users as users INNER JOIN messages AS messages ON ( users.user_id = messages.user_id ) INNER JOIN forums as forums ON ( messages.forum_id = forums.forum_id ) WHERE forums.name = 'kindling'
  6. 1. Always name your tables the same way: There are best practice rules for naming tables. They should always be lowercase, split by underscore ( _ ) and named in plural. If you need to build tables for several purposes, (forums, shops, fruits) prefix them so the appear next to each other. Examples: forums_users forums_posts shops_products shops_prices shops_availability 2. Use normalization rules(!!!) when creating a structure for tables Larry explains the different forms of normalization very good in this book. Read it thoroughly, UNDERSTAND it, and plan your tables well. The rules are really not that hard to understand, and will allow you to cross-reference tables in an easy way later on. It will make you understand how the data you are using are working. It will make your systems a lot easier to build on and to introduce new features. I promise you, THIS is how you become a wizard when it comes to working with data in several tables. 3. Use white space(!) and use UPPERCASE for mysql functions (See tip #4!) When your queries becomes increasingly more complex, you should really follow these tips. To demonstrate, I will give you two versions of the exact same code: CREATE VIEW view_goals_per_game AS SELECT league.season AS season, league.id AS league_id, league.name AS league_name, goals.match_id AS match_id, clubs1.name AS hometeam, clubs2.name AS awayteam, players.id AS player_id, CONCAT( players.fornavn, ' ', players.etternavn ) AS player_name, goals.goal_time AS goal_time, games.kickoff_time AS kickoff_time FROM abc_players_goals AS goals INNER JOIN cnk_soccer_games AS games ON ( goals.match_id = games.id ) INNER JOIN cnk_soccer_clubs AS clubs1 ON ( games.club1_id = clubs1.id ) INNER JOIN cnk_soccer_clubs AS clubs2 ON ( games.club2_id = clubs2.id ) INNER JOIN abc_players AS players ON ( goals.player_id = players.id ) INNER JOIN cnk_soccer_league AS league ON ( league.id = games.league_id ) LIMIT 0, 1000 create view view_goals_per_game as select league.season as season, league.id as league_id, league.name as league_name, goals.match_id as match_id, clubs1.name as hometeam, clubs2.name as awayteam, players.id as player_id, concat( players.fornavn, ' ', players.etternavn ) as player_name, goals.goal_time, games.kickoff_time from abc_players_goals as goals inner join cnk_soccer_games as games on ( goals.match_id = games.id ) inner join cnk_soccer_clubs as clubs1 on ( games.club1_id = clubs1.id ) inner join cnk_soccer_clubs as clubs2 on ( games.club2_id = clubs2.id ) inner join abc_players as players on ( goals.player_id = players.id ) inner join cnk_soccer_league as league on ( league.id = games.league_id ) limit 0 , 1000 Whick one would you like to maintain? 4: Save your queries in a text editor Yes! It sound idiotic, right? It's not. Think of this happening: You accidentally delete, modify or overwrite a query you've used a lot of time on. It will save you a lot of time, tears and the life of a few keyboards! This is also leading up tip number 5. 5. Use views instead of customizing your dataset in PHP(!) This is a real life-saver. Think of it as including ONE central PHP file instead on chancing 10 documents every time you make a change. The views should be written to display AS MUCH information as possible. Try to think of every scenario you may want to use the data. Views are, with a few exceptions, for displaying data; hence it's name. NOTE: After the creation of a view, you CANNOT modify it. This is why you should follow step 4. - Views are really easy to create: [u][i]CREATE VIEW the_name_of_the_view AS[/i][/u] SELECT * FROM table1 The query used in tip #3 is a view. It would allow you to sort by a specific league, match_id, player_id or by kickoff_time. The view is used to display statistic about the goal scorer in a football match. (soccer for americans) The table for saving goal statistic has three rows(!). Match_id, player_id and goal_time. This is good data normalization, and minimizes redundancy and make for consistent data. That means the table players_goals need to be linked with other tables that holds data about the specific match and the specific player. This is the reason for create a view. Don't see the point of this? There really is one - I promise! By writing a view that is general and display a lot of data, I can write SIMPLE queries to get different results: 1. Last five goal scorers with name of both teams, player name and goal time SELECT hometeam, awayteam, player_name, goal_time FROM view_goals_per_game ORDER BY kickoff_time DESC 2. Display players with most goals in descending order SELECT player_name, COUNT(*) as goals FROM view_goals_per_game GROUP BY player_id ORDER BY goals DESC 3. Display all goals by a specific player SELECT hometeam, awayteam, player_name, goal_time FROM view_goals_per_game WHERE player_id = 10 ORDER BY kickoff_time DESC 4. Find all goals in a specific match SELECT hometeam, awayteam, player_name, goal_time FROM view_goals_per_game WHERE match_id = 837 Does it start to make some sense? Instead of repeating and modifying the same hard-to-grasp code over and over again (see tip #3), use a view to make your life simpler. Hope this has helped someone. I have been thinking about writing a lot of different tips when it comes to MySQL. I've been thinking about writing a guide to joins for example. I really don't know if this interests someone or not. Hoping others will also share tips and experiences when it comes to MySQL.
  7. The question is how your structure are looking at the moment. Did you need categories, or do you only want to alphabetize? Are you planning on having several authors in the feature? Do you need to save the time published on the poems? These are questions you need to think about for the feature. It can always be altered later on, so I think we can start adding data. --------------------------- Yes. Insert is the right place. - Look after: "Continue insertion with X rows" in the bottom. Set it according to number of poems you want to add. Enter data: 1. Leave id blank. This is automatic 2. Add title 3. Add poem content 4. The question is whether you need categories. Just add 1 or something here in the meantime. Than Mysql like function? I'm not 100% sure about that, but if you say so.
  8. One tip. Instead of having a.php, b.php, etc. Make one general page called poems.php (or something) and base the query of poems on a $_POST og $_GET-variable. It would make more sense to develope ONE version than 26.
  9. This is just thinking. You add all letters into <button>-tags inside a form. This information will be used to query the correct poems. <form action="" method="post"> <input type="submit" name="alphabetical" value="a" /> ... <input type="submit" name="alphabetical" value="z" /> </form> You then use this information in your query: // make sure data is good $letter = $_POST['alphabetical']; $query = " SELECT * FROM poems WHERE title LIKE '{$letter}%' "; $result = mysqli_query($query, $connect); // display info Something like that. There are always several ways to solve a problem.
  10. And yes, i forgot the actuall poem. 1. Click on the table poems in phpMyAdmin 2. Select "structure" 3. Look after "Add X column(s) below this structure 4. Select after "title" or what you called it. 5. Click add, call it "poem/text/whatever" 6. Select "Longtext" as type from the drop down list One question: Is the letters only for sorting alphabetically? Then you could add the Letter directly into the poems table. You could also drop the whole categorization and select only queries that begins with the letter accoring to a variable etc. It depends on what you want. With categories, you could sort the poems accoring to categories AND letters. It would give you some more choices. I dunno what your looking for. This is how the data MAY look like: poems(id, title, poem, category_id) 1 | my title........... | Poem Text | 1 2 | my best friend | Poem Text | 4 3 | my title........... | Poem Text | 2 4 | my title........... | Poem Text | 1 5 | my friend........ | Poem Text | 4 poems_categories (category_id, category_name) 1 | Dreams 2 | Nature 3 | Religious 4 | Frendship 5 | Whatever
  11. Then this would be a good structure: poems (id, title, category_id*) - id (primary_key, auto_increment INT) - title (varchar 255) - category_id (INT 3) - Foreign key to table poems_category's category_id poems_category (category_id, category_name) - categeory_id (primary_key, auto_increment INT 3) - category_name (varchar 255) Do you need help generating the code for the structure? I have to go now. Sure someone can help you in the mean tine.
  12. Do you need any more information regarding these poems? Author, date submitted, poem category, etc? We can help you build the database structure, but we would need some more information about this first.
  13. Did not know that. Larry: Are you completly sure? Xampp can be installed on portable devices/etc. Could this be a possibility, or would it still be an OS issue here?
  14. Yes. Just by adding some style like background: url(); this is absolutly perfect. *Adding to toolbox*
  15. As Larry said, I start by planning the database. This way, I keep track of what needs to be stored and retrieved from the database. I start by listing all information I want to display, and begin the normalization process to find primaries and foreign keys that needs to be used. The first part of the planning is on how to store data. Then I begin to write different views for displaying different pieces of information. After this, I start with the basics like connection to the DB and how to insert, modify and delete these data. Here I focus on the best way to design the user interface for this. How to make it easy to work with these data. When that is done, I start by implementing site design and layout to make it look better. I know most PHP developers with gain on learning some basic normalization to keep that integrity, remove most of redundancy and to plan the flow of the data in and out.
  16. When comparing a variable to info, you should always use double equals. ( == ) // I'm a string $something = "Compare me"; if ($something == "Compare me") { echo "Yes, we're alike"; } else { echo "I'm different"; } // I'm Integer $oceans = 11; if ($oceans == 11) { echo "Occeans Eleven rocked!"; } elseif ($oceans == 12) { echo "I was not as good"; } else { echo "Crap..."; } // BOOLEAN $sure == TRUE; // if ($sure) is really enough. Just for the example if ($sure == TRUE) { echo "Of course I'm sure!"; } else { echo "Ok. I might've been wrong"; }
  17. The way Larry does this is to check whether the array are empty of not. If it's not empty, that would mean an error has occurred. My thinking is that a textual description of the error is what's important. That is also how Larry does it. (No wonder I concur, right) // Create the array. It's now empty $errors = array(); if (isset($something)) { // it does exist } else { // it does NOT exist $errors[] = "variable Something is empty"; } // Check array and foreach it to get possible errors if (empty($errors) { // Errors array are empty. Rock on with anything... } else { foreach ($errors as $error_message) { echo $message; // looks like someone fucked up } }
  18. 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.
  19. 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 :/
  20. 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.
  21. 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. :/
  • Create New...