CODEWITHSUNDEEP
phpmysql
Daniel Jensen
Daniel Jensen

Reputation: 141

Sort columns automatically

I have a database-table with some football-matches, 3 per week, and I wrote them in a table like this example:

week  |team1   |team2   |date1   |time1   |team3  |team4  |date2  |time2  |
46    |Chelsea |Man Utd |18/11   |13:30   |Arsenal|Stoke  |19/11  |18:00  |

Then I have this code to call them:

"SELECT * FROM table_name WHERE week = '$week'";

...where $week = Date('W');

echo $row['team1'].' - '.$row['team2'].'<br />
'.$row['team3'].' - '.$row['team4'].';

and so on. But sometimes the date or time changes, so I have to make the whole row again, because else it will still show the first match first, even though the other match will be played first. Let say Chelea - Man Utd is moved to the 20/11 at 18:00. Can I somehow just change the date and time in the row and make the matches rearrange on the front-end?

Sorry if it's a bit difficult to understand what I'm talking about :)

Upvotes: 0

Views: 67

Answers (2)

Doc Roms
Doc Roms

Reputation: 3308

First, I think your tables has not correctly formatted, you shouldn't have fields like field1, field2, field3... If you require that : you should be create another table :D Like that: Or you have a logical problem. I think an architecture like that is better addapted to your database : 

# table team
id  | name
1   | Chelsea
2   | Man Utd 
3   | Arsenal
4   | Stoke

If a team change this name, is more efficient, if value is once in other table.

# table match
id  | local_team_id | external_team_id | dateTime             | week | score ^_^
1   | 1             | 2                | 2017-11-14 19:30:06  | 46   | 2-0
2   | 3             | 4                | 2017-11-15 18:30:21  | 46   | 2-0

Also, if possible, never use the SELECT * FROM your_table is not effective, and very not good for performance. You must just return in your request the values you need in your PHP. Not more.

From your sample you can write: 

"SELECT team1, team2, team3, team4 FROM table_name WHERE week='$week'"

But, even with that, you can have a problem of SQL injection. You can fix it with PHP method, or with using PDO and prepare query. A sample of your request with PDO : 

$query = "SELECT team1, team2, team3, team4 FROM table_name WHERE week= :week"
$dbh->prepare($query, , array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$dbh->execute([':week' => $week]);

So, for answer to your question, you can order the result column, with define fields in your request : 

SELECT first, second, third FROM table_name

And you can order the rows results with the ORDER BY clause (see the doc here ) like that : 

SELECT team1, team2, team3, team4, week FROM table_name ORDER_BY week DESC

Hope I help you.

Upvotes: 1

symcbean
symcbean

Reputation: 48357

When you are asking about how to redesign or query an existing data structure, please include the definition of the data structure (in your case, the CREATE TABLE statement) as well as examples of the data it contains.

The title of your question talks about sorting - but the body of your question is about how the date/time of a match changes. If you want to control the order in which rows are retrieved from an SQL DBMS, then SQL has a solution for that - specify a ORDER BY clause in your SELECT statement.

But I suspect that your problem is the fact that you are putting details of multiple matches into a single row in your table. This is very wrong.

A table describing matches should only contain one match per row. Arguably, it shouldn't contain teams either (since both teams have the same physical relationship to a match, using different columns to hold them creates an artificial difference) but this is probably overkill for most purposes.

I would implement this as:

  CREATE TABLE match (
    ondate DATETIME,
    home_team VARCHAR(20),
    away_team VARCHAR(20)
    PRIMARY KEY (ondate, home_team)
  );

(it may require other indices depending on usage).

I am assuming that the week can be derived from the datetime (e.g.if a match scheduled for week 40 is postponed by 7 days, it is then considered to be in week 41).

If you want to display all the matches occurring in one week as a row at the front end then, although its possible to format the data this way in the output of a SELECT statement, you should handle this in your PHP code.

Upvotes: 0

Related Questions