Mysql selection from database

Question

I am newbie in this stuff and I am just learning. I have made a page for NHL tournaments and now I want to shorten my php code, but actually I dont know how.

My problem is with selections from database. It is made very complicated, but maybe there is a way how to make it shorter?

<?php
 if( IS_USER ){
$team = mysql::fetch(mysql::query( 1 , "SELECT * FROM league_nhl_standings WHERE league_id = '".get_seg('ceturtais')."' AND player_id = '" . get_cookie('user_id') . "'"));
$center = mysql::rows(mysql::query(1,"SELECT * FROM player_list WHERE team = '$team[team_small]' AND position = 'C'")); 
$defenseman = mysql::rows(mysql::query(1,"SELECT * FROM player_list WHERE team = '$team[team_small]' AND position = 'D'")); 
$goalies = mysql::rows(mysql::query(1,"SELECT * FROM player_list WHERE team = '$team[team_small]' AND position = 'GK'")); 
$leftw = mysql::rows(mysql::query(1,"SELECT * FROM player_list WHERE team = '$team[team_small]' AND position = 'LW'")); 
$rightw = mysql::rows(mysql::query(1,"SELECT * FROM player_list WHERE team = '$team[team_small]' AND position = 'RW'")); 

• Riad: use join and get the result in a single query. better use procedure and call from php.

Answer 1

You can combine following queries in one query.

$center = mysql::rows(mysql::query(1,"SELECT * FROM player_list WHERE team = '$team[team_small]' AND position = 'C'")); 
$defenseman = mysql::rows(mysql::query(1,"SELECT * FROM player_list WHERE team = '$team[team_small]' AND position = 'D'")); 
$goalies = mysql::rows(mysql::query(1,"SELECT * FROM player_list WHERE team = '$team[team_small]' AND position = 'GK'")); 
$leftw = mysql::rows(mysql::query(1,"SELECT * FROM player_list WHERE team = '$team[team_small]' AND position = 'LW'")); 
$rightw = mysql::rows(mysql::query(1,"SELECT * FROM player_list WHERE team = '$team[team_small]' AND position = 'RW'")); 

to like this,

$allplayers=mysql::rows(mysql::query(1,"SELECT * FROM player_list WHERE team = '$team[team_small]' AND position IN ('C', 'D', 'GK', 'LW', 'RW')")); 

Now you can iterate over $allplayers and in loop filter the result-set by comparing position column with simple PHP if condition.

This will reduce the amount of queries you make to database and reply on PHP script to filter/get appropriate data.

• Eduards Šķērsts: Thanks! This is great! Already did all and works really fine!
• Alok Patel: Awesome:) Please accept it as correct answer if it helped you.
• eggyal: WHERE team = ... AND position IN ('C', 'D', 'GK', 'LW', 'RW') would be a little clearer, and more concise.
• Alok Patel: @eggyal Indeed! Updated the answer. Thanks.