Shorten this query?

[ale1981]

Hi, just wondering, this code works great at the moment, but I am just wondering if it would be possible in just one db query?

	$result = $db->sql_query("SELECT * FROM games WHERE id=$id",false,__FILE__,__LINE__);
	$row = $db->sql_fetchrow($result);
	$homelist = $db->sql_query("SELECT id,name FROM teams ORDER BY name");
	$ddhome = array();
	while (list($home_id, $home_name) = $db->sql_fetchrow($homelist)) {
    		if(isset($row['home']) && !empty($row['home']))
    			{
        				$sel = ($row['home'] == $home_id) ? 'selected' : '';
			} else {
        				$sel = ($row['home'] == $home_name) ? 'selected' : '';
    			}
		$ddhome[] = "echo '<option $sel value=$home_id>$home_name</option>\n'";
	}
	$awaylist = $db->sql_query("SELECT id,name FROM teams ORDER BY name");
	$ddaway = array();
	while (list($away_id, $away_name) = $db->sql_fetchrow($awaylist)) {
    		if(isset($row['away']) && !empty($row['away']))
    			{
        				$sel = ($row['away'] == $away_id) ? 'selected' : '';
			} else {
        				$sel = ($row['away'] == $away_name) ? 'selected' : '';
    			}
		$ddaway[] = "echo '<option $sel value=$away_id>$away_name</option>\n'";
	}

... if not, I guess having 2 queries isnt to bad.

[mmilano]

i see 3 queries, and i'm confused. you have no where condition in your team queries. each of those would return all teams for both home and away.

[mmilano]

i looked at it a bit longer. you need to describe the schema of your games and teams tables if you want a good answer from anyone.

[ale1981]

Hi,

Teams
ID
NAME

Games
ID
SHEET_ID
KICKOFF
HOME
HOMESCORE
AWAY
AWAYSCORE

They need to both be filled with all the teams in the db as they are populated to create fixtures. What that bit of code does is when I am editing the fixture, selects the home and away teams in the dropdowns. I was just wondering if there was a shorter or easier way to do that code?

[Jack_McSlay]

first off, why acquiring the team data from the database twice? I think you're better off loading the entire data in an array and then process it.

    while (list($home_id, $home_name) = $db->sql_fetchrow($homelist)) {
            if(isset($row['home']) && !empty($row['home']))
                {
                        $sel = ($row['home'] == $home_id) ? 'selected' : '';
            } else {
                        $sel = ($row['home'] == $home_name) ? 'selected' : '';
                }
        $ddhome[] = "echo '<option $sel value=$home_id>$home_name</option>\n'";
        $team_data[] = $row; // added line
    }

and the next should be replaced with

    reset ($team_data);
    do {
            list($away_id, $away_name) = current ($team_data)
            if(isset($row['away']) && !empty($row['away']))
                {
                        $sel = ($row['away'] == $away_id) ? 'selected' : '';
            } else {
                        $sel = ($row['away'] == $away_name) ? 'selected' : '';
                }
        $ddaway[] = "echo '<option $sel value=$away_id>$away_name</option>\n'";
    } while (next ($team_data));

now if you want to execute only one query, what you need to look at is mysqli_multi_query(). I can't suggest a code because I don't know it too well, tho

[ale1981]

Thanks for your reply. I will take a look at mysqli_multi_query() although I doubt it will increase the speed by much for just 2 queries?