|
Page 1 of 1
|
 KugeLSichA 
Joined: August 2006
Posts: 803
Location:  Dresden
|
 Some Help for SQL Needed
Hi everybody,
I try to port a script into my Icy Phoenix and now i need your help.
The script uses other sql statements, and this is my problem. this is the original sql statement:
- $sections_ref = dbQuery("SELECT * FROM `{$cfg['db_table_prefix']}sections` WHERE `deleted` = 0 ORDER BY `name` ASC");
- while ($sections_row = dbFetch($sections_ref))
- {
- $seasons_ref = dbQuery("SELECT * FROM `{$cfg['db_table_prefix']}seasons` " .
- "WHERE `id_section` = {$sections_row['id']} AND `deleted` = 0 " .
- "ORDER BY `submitted` DESC");
- while ($seasons_row = dbFetch($seasons_ref))
- {
- $main_tpl->set_var("I_ID_SEASON", $seasons_row['id']);
- $main_tpl->set_var("I_SECTION_NAME", $sections_row['name']);
- $main_tpl->set_var("I_SEASON_NAME", $seasons_row['name']);
- $main_tpl->parse("H_SEASON_DROPDOWN", "B_SEASON_DROPDOWN", true);
- }
- }
and this is what i have done yet
- $sql = "SELECT * FROM " . TOURNEY_SECTIONS_TABLE . "
- WHERE `deleted` = 0
- ORDER BY `name` ASC";
- $result = $db->sql_query($sql);
- $section_info = $db->sql_fetchrowset($result);
- for ($x = 0; $x < count($section_info); $x++)
- {
- $section_id = $section_info[$x]['name'];
- }
-
- $sql = "SELECT * FROM " . TOURNEY_SEASONS_TABLE . "
- WHERE `deleted` = 0
- ORDER BY `submitted` DESC";
- $result = $db->sql_query($sql);
- $season_info = $db->sql_fetchrowset($result);
- $season_select = '';
- $season_select .= '<form action="">';
- $season_select .= 'Select Season: <select name="season_id" size="1">';
- $season_select .= '<option value="0">-</option>';
- for ($x = 0; $x < count($season_info); $x++)
- {
- $season_select .= '<option value="' . $season_info[$x]['id'] . '">' . $section_info[$x]['name'] . ' ' . $season_info[$x]['name'] . '</option>';
- }
- $season_select .= '</select>';
- $season_select .= '<input type="submit" value="Go">';
- $season_select .= '</form>';
-
- $template->assign_vars(array(
- 'SEASON_SELECT' => $season_select,
- )
- );
the problem is, that my code, select all seasons, correct, but the section name is not shown by all created options.
can somebody help me with that?
|
#1 Thu 14 Feb, 2008 23:32 |
|
Sponsors
|
Icy Phoenix is an open source project, you can show your appreciation and support future development by donating to the project.
|
| |
KasLimon
Joined: November 2006
Posts: 494
Location:  Madrid (Spain)
|
Re: Some Help For SQL Needed
Hi, first of all, this lines are not doing anything: for ($x = 0; $x < count($section_info); $x++)
{
$section_id = $section_info[$x]['name'];
}
I don't understand exactly what's the error. Can you take a screenshot of the select box result?
Greets!
____________
Gabriel Anca
|
#2 Fri 15 Feb, 2008 13:52 |
|
KugeLSichA
Joined: August 2006
Posts: 803
Location: Dresden
|
Re: Some Help for SQL Needed
Hi KasLimon,
thx for your help
here is a screenshot:
der red underlined word is the "section_info name" and the blue underlined is the "season_info name"
the dropdown is sorted by "season_info id" the "carom abcdef" has id "3" and the last "Test season" has id "1"
|
#3 Fri 15 Feb, 2008 15:32 |
|
KasLimon
Joined: November 2006
Posts: 494
Location: Madrid (Spain)
|
Re: Some Help For SQL Needed
OK I see.
One more question.
Then season_info ID and section_info ID have to be the same to make it work?
I mean:
carom id 3
abcdef id 3
carom3d id 2
neuer test id 2
test season id 1
Is this correct?
Greets?
____________
Gabriel Anca
|
#4 Fri 15 Feb, 2008 16:00 |
|
KugeLSichA
Joined: August 2006
Posts: 803
Location: Dresden
|
Re: Some Help for SQL Needed
exactly 
greetings
|
#5 Fri 15 Feb, 2008 16:02 |
|
KasLimon
Joined: November 2006
Posts: 494
Location: Madrid (Spain)
|
Re: Some Help For SQL Needed
I've been testing and I got this:
- $sql = "SELECT sec.id AS section_id, sec.name AS section_name, sea.id AS season_id, sea.name AS season_name
- FROM (" . TOURNEY_SECTIONS_TABLE . " sec, " . TOURNEY_SEASONS_TABLE . " sea)
- WHERE sec.id = sea.id
- AND sea.deleted = 0
- AND sec.deleted = 0
- ORDER BY sec.name ASC";
- if (!$result = $db->sql_query($sql))
- {
- message_die(CRITICAL_ERROR, "Could not query season information", "", __LINE__, __FILE__, $sql);
- }
- $season_info = $db->sql_fetchrowset($result);
- $season_select = '<form action="">';
- $season_select .= 'Select Season: <select name="season_id" size="1">';
- $season_select .= '<option value="0">-</option>';
- for ($x = 0; $x < count($season_info); $x++)
- {
- $season_select .= '<option value="' . $season_info[$x]['section_id'] . '">' . $season_info[$x]['section_name'] . ' ' . $season_info[$x]['season_name'] . '</option>';
- }
- $season_select .= '</select>';
- $season_select .= '<input type="submit" value="Go">';
- $season_select .= '</form>';
-
- $template->assign_vars(array(
- 'SEASON_SELECT' => $season_select,
- )
- );
Try it. Greets!
____________
Gabriel Anca
|
#6 Fri 15 Feb, 2008 16:29 |
|
KugeLSichA
Joined: August 2006
Posts: 803
Location: Dresden
|
Re: Some Help for SQL Needed
many thanks KasLimon,
now i got it working with your help... with your code there was only showing 2 options, but it was my fault, because we checked the season table "id" but it must be the season table "id_section" (line 3)
i have it working with this code:
- $sql = "SELECT sec.id AS section_id, sec.name AS section_name, sea.id AS season_id, sea.name AS season_name
- FROM (" . TOURNEY_SECTIONS_TABLE . " sec, " . TOURNEY_SEASONS_TABLE . " sea)
- WHERE sec.id = sea.id_section
- AND sea.deleted = 0
- AND sec.deleted = 0
- ORDER BY sec.name ASC";
- if (!$result = $db->sql_query($sql))
- {
- message_die(CRITICAL_ERROR, 'Could not query season information', '', __LINE__, __FILE__, $sql);
- }
- $season_info = $db->sql_fetchrowset($result);
- $season_select = '<form action="tourney.' . $phpEx . '?sid=' . $userdata['session_id'] . '" method="post">';
- $season_select .= 'Select Season: <select name="season_id" size="1">';
- $season_select .= '<option value="0">-</option>';
- for ($x = 0; $x < count($season_info); $x++)
- {
- $season_select .= '<option value="' . $season_info[$x]['season_id'] . '">' . $season_info[$x]['section_name'] . ' ' . $season_info[$x]['season_name'] . '</option>';
- }
- $season_select .= '</select>';
- $season_select .= '<input type="submit" value="Go">';
- $season_select .= '</form>';
-
- $template->assign_vars(array(
- 'SEASON_SELECT' => $season_select,
- 'I_ID_SEASON' => $season['id'],
- )
- );
again thank you very much =)
|
#7 Fri 15 Feb, 2008 18:16 |
|
KasLimon
Joined: November 2006
Posts: 494
Location: Madrid (Spain)
|
Re: Some Help For SQL Needed
Nice to know it works 
Greets!
____________
Gabriel Anca
|
#8 Fri 15 Feb, 2008 22:05 |
|
KugeLSichA
Joined: August 2006
Posts: 803
Location: Dresden
|
Re: Some Help for SQL Needed
yes and i´m happy that you helped me with that... now i can change the other sql queries, and the whole script
if i finished it, i put it here online, maybe someone needs it... but later more infos...
anyway thanks KasLimon 
|
#9 Sun 17 Feb, 2008 11:22 |
|
|
|
Page 1 of 1
|
 Was this topic useful?
 Was this topic useful?
| Link this topic |
|---|
| URL |
|
| BBCode |
|
| HTML |
|
You cannot post new topics
You cannot reply to topics
You cannot edit your posts
You cannot delete your posts
You cannot vote in polls
You cannot attach files
You can download files
You cannot post calendar events
|
|
|
|
