Reducing long SELECT SQL statements?

ale1981

Hi.

How do I shorten an SQL statement like this;

$teamlist = $db->sql_query("SELECT id,name FROM teams WHERE id = $row['hometeam1'], $row['hometeam2'], $row['hometeam3'], $row['hometeam4'], $row['hometeam5'], $row['awayteam1'], $row['awayteam2'], $row['awayteam3'],  $row['awayteam4'],  $row['awayteam5'],  ORDER BY name");

Thanks for any help, still learning! :rolleyes:

MrPotatoes

first off i would do this:

$teamlist = $db->sql_query(
                "SELECT id, name FROM teams
                WHERE id =
                    $row['hometeam1'],
                    $row['hometeam2'],
                    $row['hometeam3'],
                    $row['hometeam4'],
                    $row['hometeam5'],
                    $row['awayteam1'],
                    $row['awayteam2'],
                    $row['awayteam3'],
                    $row['awayteam4'],
                    $row['awayteam5'],
                ORDER BY name");

secondly i doubt that you need all of that in order to find out what you are looking for. it seems way over the top. almost brute force. but i don't know what you are trying to search for or how your database is set up so i can't help past this point.

although i am still in the basis of coding as well (hih beginner to rough intermediate)

Sxooter

Is that a legal, working query? I mean, what are those other terms there for? They're not compared to anything. What are you trying to do, maybe that will help.

ale1981

Hi guys, sorry, let me explain better!

In my db I have a list of teams (hometeam1 - 10 and awayteam1 - 10). I need to get the value of each of the fields so I can display the relevant teams in a dropdown list.

In english it is suppose to SELECT the id, name from teams WHERE the ID of the teams are equal to the values of $row['hometeam1'] through to $row['awayteam10']. 😕

Roger_Ramjet

Don't understand your table structure - and I'm sure most others don't either.

Please post the table structure together with specimen data so we can understand what you are on about. Maybe then you'll get an answer that helps as opposed to the ones you've got so far.

PS my guess is that the answers are then going to be to normalise your data properly.

ale1981

Sorry. Ok;

The teams table has 2 fields, id(primary, int), name(varchar)
The 4casts table has the following fields

id(primary, int),hometeam1(tinyint),hometeam2,hometeam3 .... hometeam10, awayteam1,awayteam2,awayteam3 .... awayteam10

Ok before I do this select I pick everything from the 4casts table;

$db->sql_query("SELECT * FROM 4casts WHERE id=$id");
$row = sql_fetchrow($result);

Now what I need to do is pick the id and name of all the teams that corresponds with each of the hometeam and awayteam fields, which are now $row[hometeam1] etc.

Explain any better?

Roger_Ramjet

OK, well as you have it set up that is going to require 20 joins to the 20 team columns - if you want to do that in sql. I'm sure that the table layout is wrong - just the fact that you could require 20 joins convinces me of that. Not going to get into alternative storage strategies as you still have not given enough info.

Easier to do it in php with an array of the team names indexed by their id

$team_array = array();
$teams = mysql_query("SELECT * FROM teams");
while ($row=mysql_fetch_array($teams)) {
   $id = $row['id'];
   $name = $row['name'];
   $team_array[$id] = $name;
}

$4casts = mysql_query("SELECT * FROM 4casts");
while ($row=mysql_fetch_array($4casts)) {
    $i = $row['hometeam1'];
   echo 'home team 1 = ' . $team_array[$i];
}

I'm sure you can do the rest from there.

Still not the ideal solution.
Even if your analysis determined that this table layout was indeed the 3rd Normal Form, this is an ideal case for modifying the design to make processing easier.
I would break the 20 columns in 4casts out into multiple rows thus

4casts table
week | type_id | team_id

types table
type_id int
type char

data like this
1 | hometeam1
2 | hometeam2
3 | hometeam3

...
11 | awayteam1

etc

Now you can join to teams table on 1 column and to types table on another column to get your data in human readable form

Select week, type, team FROM 4casts INNER JOIN team_type ON(4casts.type_id=types.type_id) INNER JOIN teams ON(4casts.team_id=teams.team_id)

ale1981

Hi Roger and thanks for your input so far.

I am new to php and MySQL and thought that having a table with 20 columns in it would be the simplest way to go. I will post my entire table structure later, am at work at the moment.

ale1981

Here is the 4cast table;

Field Type Null Default
id mediumint(9) No
kickoff1 varchar(5) No
hometeam1 tinyint(4) No 0
awayteam1 tinyint(4) No 0
kickoff2 varchar(5) No
hometeam2 tinyint(4) No 0
awayteam2 tinyint(4) No 0
kickoff3 varchar(5) No
hometeam3 tinyint(4) No 0
awayteam3 tinyint(4) No 0
kickoff4 varchar(5) No
hometeam4 tinyint(4) No 0
awayteam4 tinyint(4) No 0
kickoff5 varchar(5) No
hometeam5 tinyint(4) No 0
awayteam5 tinyint(4) No 0
kickoff6 varchar(5) No
hometeam6 tinyint(4) No 0
awayteam6 tinyint(4) No 0
kickoff7 varchar(5) No
hometeam7 tinyint(4) No 0
awayteam7 tinyint(4) No 0
kickoff8 varchar(5) No
hometeam8 tinyint(4) No 0
awayteam8 tinyint(4) No 0
kickoff9 varchar(5) No
hometeam9 tinyint(4) No 0
awayteam9 tinyint(4) No 0
kickoff10 varchar(5) No
hometeam10 tinyint(4) No 0
awayteam10 tinyint(4) No 0

Team Table;

Field Type Null Default
id smallint(6) No
name varchar(30) No

I hope this helps to understand what my problem is a bit better! 😕

sfullman

Hi There,

This is really a respectful post but I'm sure anyone else helping you, based on your table structure, would agree you need to do some research on good optimization and normalization methods. You try to normalize a table so that you don't need to add columns, but typically index numbers in ONE column, as this makes your queries easier. Otherwise, how do wyou know which column something is in for sure?

It's a new world and new way of thinking and takes some time but it will come fast once you start thinking through it. There are times where something is so unchanging that it could use columns of Widget1, Widget2, and maybe Widget 3, but much more than that and you need to think "hey, this is not the normalized way of doing things".

A few hours of study here may seem tough but you'll be doing this stuff for years :-)

Samuel

Roger_Ramjet

So you need a fixtures table and a teams table - whether this is to schedule matches, bet on the outcome, or just record the results

Something like this will do it

fixtures
fix_id int autoinc primary key not null
week int not null
matchdate date not null
kickoff time not null
hometeam int not null foreign key
awayteam int not null foreign key
homescore int null - to indicate if the match has been played yet
awayscore int null - ditto

I would store things like league position, goals for and against, win/loss in the team table. You may also want to extend the fixtures table to include things like venue, officials, etc.
You might also want to add some unique multi-column indexes to week-hometeam and week-awayteam just to trap data entry errors.

Simple then to join teams to fixtures and get all the data you need.
eg get fixtures for week 5

$sql = "SELECT * FROM fixtures f INNER JOIN teams h ON(f.hometeam=h.team_id) INNER JOIN teams a ON(f.awayteam=a.team_id) WHERE f.week=5";

get fixtures for team 2

$SQL = "SELECT * FROM fixtures f INNER JOIN teams h ON(f.hometeam=h.team_id) INNER JOIN teams a ON(f.awayteam=a.team_id) WHERE f.hometeam=2 OR f.awayteam=2 ORDER BY f.week";

ale1981

INNER JOINS?!

Arg, well this is confusing me now!

The project is for predicting results, there are actually 10 GAMES per sheet, so I store the info per sheet?

Doing it that way would mean I would have 10 entries PER sheet, instead of 1 sheet for 10 GAMES?

Still with me?

Roger_Ramjet

Stop thinking flat-file and start thinking relational database.

ale1981

Sorry, I dont mean to be rude, I just dont understand as I am very new! I am glad of ANY help that I receive.

sfullman

I think everyone is trying to tactfully hint you need to study some. Believe me, I get lots of pompous responses from people (not on this forum of course) when I ask a simple question, but the point being made is that you're asking for help on something that requires understanding of relationships.

There may be 10 games per physical sheet, but that's not how it works in relational databases - USUALLY. The reason is that each game has (from your table) 3 parameters, and each game is really equal in many senses, and you want to be able to search all games. so instead of creating a table with these fields:

Table: Sheets
game1
param1A
param1B
game2
param2A
param2B
game3
param3A
param3B
.. etc .. etc ..
[..please say you understand that :-]

you create TWO tables:

Table: Sheets
ID INT(9) auto_increment primary key
Name CHAR(30)

Table: Sheets_Games
ID INT(9) auto_increment primary key
Sheets_ID INT(9)
Idx TINYINT(2) unsigned
Description CHAR(30)
ParamA CHAR(30)
ParamB CHAR(30)

Each sheet has an autoinc ID assigned.
Then each game goes in Sheets_Games, and Sheets_Games.ID is a new number for THAT game. Sheets_ID refrences Sheets.ID. Idx is the game sequence number.

You see now all games are equal? They can be searched on 3 columns, Description, ParamA, and ParamB. (vs. 3x10=30) They just have a Sheets_ID pointing back to the sheet and that structure can be rebuilt by a joining query if need be. Not only that, you can have 10 games per sheet, OR only 3, OR 3,000,000,000 if you want - so you save space.

I've just given a lesson in normalization - which you could have learned by study. Hope that helps, this is what ramjet was getting at I think. Point is, there are more things to learn in this new way of thinking, and if you're going to run with the big dogs you need to learn how to eat meat :-)

HTH,
Samuel

ale1981

Thanks for your broken down reply Samuel, I understand now!

So basically I should have a sheet table and a games table, the games table will be attached to the sheets table via the game ID.

I will go away and study some more on normalization.

Thanks for all replies.

jansky

here try this

select * from yourtable where id in (team1id,team2id,team3id,....team10id)