[eluser]Sorry[/eluser]
I need some help for a MySQL Query.
I've got five tables and I want to get data from all of them (if possible with one query).
At the moment I've got this query but it only connects two tables.
Code: $sql = "SELECT
name, tag, members, villages, points, rank
FROM
{$world}_ally
INNER JOIN
{$world}_ally_name ON {$world}_ally_name.ally_id = {$world}_ally.ally_id
WHERE
{$world}_ally.ally_id = ?
ORDER BY date DESC
LIMIT 0, 1;";
$sql_params = array(
$id
);
$query = $this->db->query($sql, $sql_params);
I've got three more tables:
{$world}_ally_bash_all
{$world}_ally_bash_off
{$world}_ally_bash_def
[$world} is a String wich builds a part of the table, it's filtered with in_array().
these three tables all have the following columns:
id, ally_id, rank, kills, date
I want to always get the newest entry where the ally_id = $id and I need the columns rank and kills. As i already said I want to do this within the query on top if possible.
[eluser]Nisha S.[/eluser]
Code: $sql = "SELECT name, tag, members, villages, points, rank
FROM
{$world}_ally,
{$world}_ally_name,
{$world}_ally_bash_all,
{$world}_ally_bash_off,
{$world}_ally_bash_def
WHERE
{$world}_ally_name.ally_id = {$world}_ally.ally_id
AND {$world}_ally_bash_all.ally_id = {$world}_ally.ally_id
AND {$world}_ally_bash_off.ally_id = {$world}_ally.ally_id
AND {$world}_ally_bash_def.ally_id = {$world}_ally.ally_id
AND {$world}_ally.ally_id = ?
ORDER BY date DESC
LIMIT 0, 1;";
This will work for you if all the tables have records for all ally_id. If some table have records optionally please do outer join instead of the above query.
[eluser]Sorry[/eluser]
now i have this Query, but the Problem isn't fully solved.
Code: $this->db->query("SET SQL_BIG_SELECTS=1;");
$sql = "SELECT
name, tag, members, villages, points,
{$world}_ally.rank AS rank,
{$world}_ally_bash_all.rank AS bash_all_rank,
{$world}_ally_bash_all.kills AS bash_all_kills,
{$world}_ally_bash_off.rank AS bash_off_rank,
{$world}_ally_bash_off.kills AS bash_off_kills,
{$world}_ally_bash_def.rank AS bash_def_rank,
{$world}_ally_bash_def.kills AS bash_def_kills
FROM
{$world}_ally,
{$world}_ally_name,
{$world}_ally_bash_all,
{$world}_ally_bash_off,
{$world}_ally_bash_def
WHERE
{$world}_ally_name.ally_id = {$world}_ally.ally_id AND
{$world}_ally_bash_all.ally_id = {$world}_ally.ally_id AND
{$world}_ally_bash_off.ally_id = {$world}_ally.ally_id AND
{$world}_ally_bash_def.ally_id = {$world}_ally.ally_id AND
{$world}_ally.ally_id = ?
ORDER BY
{$world}_ally.date DESC
LIMIT 0, 1;";
What I need is something like this:
Code: $this->db->query("SET SQL_BIG_SELECTS=1;");
$sql = "SELECT
name, tag, members, villages, points,
{$world}_ally.rank AS rank,
{$world}_ally_bash_all.rank AS bash_all_rank,
{$world}_ally_bash_all.kills AS bash_all_kills,
{$world}_ally_bash_off.rank AS bash_off_rank,
{$world}_ally_bash_off.kills AS bash_off_kills,
{$world}_ally_bash_def.rank AS bash_def_rank,
{$world}_ally_bash_def.kills AS bash_def_kills
FROM
{$world}_ally,
{$world}_ally_name,
{$world}_ally_bash_all,
{$world}_ally_bash_off,
{$world}_ally_bash_def
WHERE
{$world}_ally_name.ally_id = {$world}_ally.ally_id AND
{$world}_ally_bash_all.ally_id = {$world}_ally.ally_id AND
{$world}_ally_bash_off.ally_id = {$world}_ally.ally_id AND
{$world}_ally_bash_def.ally_id = {$world}_ally.ally_id AND
{$world}_ally.ally_id = ?
ORDER BY
{$world}_ally.date DESC,
{$world}_ally_bash_all.date DESC,
{$world}_ally_bash_off.date DESC,
{$world}_ally_bash_def.date DESC
LIMIT 0, 1;";
i want to get the newest entry from every table where ally_id = $id but the second query doesn't work.
[eluser]Nisha S.[/eluser]
What is the error in your second query?
[eluser]Sorry[/eluser]
Code: 504 Gateway Time-out
The server didn't respond in time.
Could an index on the date column help to solve this problem?
|
