Eduards Šķērsts Eduards Šķērsts - 3 months ago 9
MySQL Question

Mysql selection from database

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'"));

Answer

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.