[Some Tips When It Comes To Mysql Queries]

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.

[Some Tips When It Comes To Mysql Queries]

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?

[Changing View_Users.Php To Query Data From A Single Table To Two Tables]

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.

[Some Tips When It Comes To Mysql Queries]

Thanks for feedback, corrections and additions, Larry.

 

Views does also update automatically. I can confirm that.

 

Anyone have something to share?

[Changing View_Users.Php To Query Data From A Single Table To Two Tables]

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'

 

[Some Tips When It Comes To Mysql Queries]

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.

[Need Help With Multidimensional Array]

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.

 

Indeed that's what you do. Also I'm pretty sure:

 

$query = "SELECT * FROM poems WHERE substring(title, 1, 1) = '$letter'";

 

Is better practice/more efficient.

 

Than Mysql like function? I'm not 100% sure about that, but if you say so.

[Need Help With Multidimensional Array]

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.

[Need Help With Multidimensional Array]

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.

[Need Help With Multidimensional Array]

doublepost

[Need Help With Multidimensional Array]

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

[Need Help With Multidimensional Array]

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.

[Need Help With Multidimensional Array]

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.

[Mysql (And Php) For Mac 10.2.8]

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?

[Mysql (And Php) For Mac 10.2.8]

Second that. Xampp would be the best choice.

[Icons/Colors After Checkbox/Option I Choosen]

Yes. Just by adding some style like background: url(); this is absolutly perfect.

 

*Adding to toolbox*

[Development Cycl]

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.

[Icons/Colors After Checkbox/Option I Choosen]

Very good. The technique really does not matter as long as it works.

[Symbolic Logic]

To start, what's symbolic logic?

[Chapter 6: Review Question]

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";
}

[Chapter 7; Validation Question]

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
  }
} 

[Mysqli_Query() Error: Syntax Error]

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.

[Mysqli_Query() Error: Syntax Error]

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 :/

[Mysqli_Query() Error: Syntax Error]

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.

[Mysqli_Query() Error: Syntax Error]

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. :/