relational DB inserting

[nshack31]

I'm trying something I've never tried before! inserting data into a relational database. My realationships are as follows (* = primay key)

tblusers
ID*
Username
pword
etc

tbllink
ID*
LeagueID*

tblleague
LeagueID*
Leaguename
etc

ok my question is this! i want the username to be associated with a leagueID so a user can chose to be in leagues 1,2 or/and 3. Lets take username joe as an example, i want joe to be a member of league id's 1 & 2. Where do I need to insert the data?! i can select * from table users but i need to insert the league ID's for the user. Is it tbllnk where i need to insert the data? if so, do i need a while loop because there could be more than one of the same id? thanks in advance!

[jatar_k]

once the user exists and the league exists

you grad the ID of the user and the single league they want to be part of

you then insert the user id and the league id into the link table

repeat as necessary ;)

[nshack31]

ok this is what ive tried.. this is for the READ the vaules and display them part

$sql="SELECT * FROM link WHERE id = $id";

Then ive created a while loop, I would like to have this while loop search through the link table and the psudocode is.....

if the user id contains leagueID 1 then check box1, if the next row has the same user id and the leagueID is 2 then check box 2 and so on. I am using this code but it is only checking the final box when all three should be checked because the user actually has leagueID 1 2 and 3 on serparate rows eg...

tbllink
userID leagueID
1--------1
1--------2
1--------3

MY CODE........

while (odbc_fetch_row($rs))
{
$userid=odbc_result($rs,"id");
$leagueid=odbc_result($rs,"leagueid");

if ($userid == $id && $leagueid == 1)
{
$box1="checked";
}
else
{
$box1="";
}
if ($userid == $id && $leagueid == 2)
{
$box2="checked";
}
else
{
$box2="";
}
if ($userid == $id && $leagueid == 3)
{
$box3="checked";
}

Hmm i know i shouldnt be using if 3 times, what should i be doing?! It is only executing the last "IF" statement! thanks

[jatar_k]

what about loading the leagues into an array

do you need to retrieve the id? we already have it
$sql="SELECT leagueid FROM link WHERE id = $id";

while (odbc_fetch_row($rs)) $leagues[]=odbc_result($rs,"leagueid");

//; initialize vars
$box1='';
$box2='';
$box3='';
if(in_array(1,$leagues)) $box1 = 'checked';
if(in_array(2,$leagues)) $box2 = 'checked';
if(in_array(3,$leagues)) $box3 = 'checked';

or if you want to be extendable, set $maxleagues to the number of leagues, which could also be a db query. Maybe, select count(leagueid) as maxleagues from tblleague.

$maxleagues = 3; 
for($i=1;$i<=$maxleagues;$i++) { 
 ${'box' . $i} = ''; 
 if(in_array($i,$leagues)) ${'box' . $i} = 'checked'; 
}

[nshack31]

You guys are brilliant!

[nshack31]

wait im stuck again! im having probs probably because ive not got to grips with arrays yet and i think this could be the prob! the code above is sent to a form to update the values in the database.

I now want to see if the checkboxes are checked and if so give them a numebr and insert them into link

so i check if its checked...

if (isset($_REQUEST['boxone']))
{
$leagueid1 = 1;
}
else
{
$leagueid1 = "";
}

if (isset($_REQUEST['boxtwo']))
{
$leagueid2 = 2;
}
else
{
$leagueid2 = "";
}

i then need to insert it into the DB..

$sql="UPDATE link SET leagueid WHERE id = $id";

but how do i update into the column league id the values $leagueid1 and $leagueid2. Its something to do with an array again isnt it?! lol please help! thanks guys

[nshack31]

is this the solution - I run more than one sql statement?

$sql="SELECT leagueid FROM link WHERE id = $id"; //select league id where id = id of current user logged on

and then run muliple sql's?...

$sql1="UPDATE link SET leagueid '$leagueid1' WHERE leagueid = 1"; //update the leagues that the current user is in

$sql2="UPDATE link SET leagueid '$leagueid2' WHERE leagueid = 2";

$sql3="UPDATE link SET leagueid '$leagueid3' WHERE leagueid = 3";

Or am i clutching at straws?!

[jatar_k]

you're in the right neighbourhood but...

I would not try to update the rows that are there, what if there aren't the same number as there were, trouble.

so we probably will need to remove the previous rows and then add new ones. We can also use an array to do it. We will assume you have gotten the proper id into the $id var.

// initialize the array 
$leagues = array(); 
if (isset($_REQUEST['boxone'])) $leagues[] = 1; 
if (isset($_REQUEST['boxtwo'])) $leagues[] = 2; 
if (isset($_REQUEST['boxthree'])) $leagues[] = 3; 
// if we didn't assign any values then $leagues[0] 
// will not be set 
if (isset($leagues[0])) { 
 $sql1 = "delete from link where id=$id"; 
 mysql_query($sql1) or die ("delete query died: " . mysql_error()); 
 foreach($leagues as $lid) { 
 $sql2 = "insert into link values ($id,$lid)"; 
 mysql_query($sql2) or die ("insert for $lid died: " . mysql_error()); 
 } 
}

totally untested, just off the top of my head

[nshack31]

I'll try that, thanks very much! :) may have to edit it slightly as im using odbc

[jatar_k]

oh yeah, forgot about that, hehe, sorry

sometimes you don't realize how ingrained things are ;)

[nshack31]

Ok i editted it to ODBC................

$leagues = array();
$leagues[] = 1; if (isset($_REQUEST['box1']))
$leagues[] = 2; if (isset($_REQUEST['box2']))
$leagues[] = 3; if (isset($_REQUEST['box3']))
if (isset($leagues[0]))
{
$sql1 = "delete from link where id=$id";
{
$sql2 = "insert into link values ($id,$id)";
}
$rs1=odbc_exec($conn,$sql1);
$rs2=odbc_exec($conn,$sql2);

}

the checkboxes remain empty until i check box 3. If i check box 3, then box one is always checked and the others stay empty. I'm confused!

[nshack31]

being useless at arrays I gave up on the array and used...

if (isset($_REQUEST['box1']))
{
$box1 = 1;
}
else
{
$box1 = "";
}
if (isset($_REQUEST['box2']))
{
$box 2= 2;
}
else
{
$box2 = "";
}
if (isset($_REQUEST['box3']))
{
$box3 = 3;
}
else
{
$box3 = "";
}

if ($box1 == 1)
{
$sql="Insert Into link (id, leagueid) Values ('$id', '$box1')";
$rs=odbc_exec($conn,$sql);
}

if ($box2 == 2)
{
$sql1="Insert Into link (id, leagueid) Values ('$id', '$box2')";
$rs1=odbc_exec($conn,$sql1);
}
if ($box3 == 3)
{
$sql2="Insert Into link (id, leagueid) Values ('$id', '$box3')";
$rs2=odbc_exec($conn,$sql2);
}

I just need to add the delete query and all should be well. Thanks for your help guys

[nshack31]

OK i got it working but the code is very long and im sure it can be shorter...

if (!$conn)
{
exit(
"Connection Failed: " . $conn);
}
if (isset($_REQUEST['tdm']))
{
$tdm = 1;
}
else
{
$tdm = "";
}
if (isset($_REQUEST['obj']))
{
$obj = 2;
}
else
{
$obj = "";
}
if (isset($_REQUEST['ctf']))
{
$ctf = 3;
}
else
{
$ctf = "";
}
$sql="Delete From link Where id = $id";
$rs=odbc_exec($conn,$sql);
if (!$rs)
{
exit("Error in SQL");
}
if ($tdm == 1)
{
$sql1="Insert Into link (id, leagueid) Values ('$id', '$tdm')";
$rs1=odbc_exec($conn,$sql1);
if (!$rs1)
{
exit("Error in SQL");
}
}

if ($obj == 2)
{
$sql2="Insert Into link (id, leagueid) Values ('$id', '$obj')";
$rs2=odbc_exec($conn,$sql2);
if (!$rs2)
{
exit("Error in SQL");
}
}
if ($ctf == 3)
{
$sql3="Insert Into link (id, leagueid) Values ('$id', '$ctf')";
$rs3=odbc_exec($conn,$sql3);
if (!$rs3)
{
exit("Error in SQL");
}

Does anybody know howto sorten this?! thanks!

[jatar_k]

well just changing the format of your if statements shortens it, otherwise you can use my variable variables with arrays from above ;)

this

if (isset($_REQUEST['tdm']))
{
$tdm = 1;
}
else
{
$tdm = "";
}

now you should actually initialize all variables before ever using them, though most of us don't use super strict error_reporting rules we all should. so, $tdm = ""; should be initialized above and not as your else result. There is also no need for the braces for a single line in the if, the above then becomes

$tdm = "";
if (isset($_REQUEST['tdm'])) $tdm = 1;

so I quickly reformatted your code below

if (!$conn) exit("Connection Failed: " . $conn); 
$tdm = ""; 
$obj = ""; 
$ctf = ""; 
if (isset($_REQUEST['tdm'])) $tdm = 1; 
if (isset($_REQUEST['obj'])) $obj = 2; 
if (isset($_REQUEST['ctf'])) $ctf = 3; 
$sql="Delete From link Where id = $id"; 
$rs=odbc_exec($conn,$sql); 
if (!$rs) exit("Error in SQL"); 
if ($tdm == 1) { 
 $sql1="Insert Into link (id, leagueid) Values ('$id', '$tdm')"; 
 $rs1=odbc_exec($conn,$sql1); 
 if (!$rs1) exit("Error in SQL"); 
} 
if ($obj == 2) { 
 $sql2="Insert Into link (id, leagueid) Values ('$id', '$obj')"; 
 $rs2=odbc_exec($conn,$sql2); 
 if (!$rs2) exit("Error in SQL"); 
} 
if ($ctf == 3) { 
 $sql3="Insert Into link (id, leagueid) Values ('$id', '$ctf')"; 
 $rs3=odbc_exec($conn,$sql3); 
 if (!$rs3) exit("Error in SQL"); 
}

[nshack31]

thanks!