UPDATE after if statement

[lukep11a]

Hi, I have a form where a user submits a name and passcode, and I want it to check my table called 'privateleague' and if that private league name and passcode exists then UPDATE the 'privateleagueid' column from the 'login' table with the 'privateleagueid' from the 'privateleague' table, hope that makes sense! This is the code I currently have that doesn't give any errors but doesn't update the 'login' table either. Any help would be very much appreciated.

 

<?php

$privateleaguename = $_POST['privateleaguename'];
$privateleaguepasscode = $_POST['privateleaguepasscode'];

$query = "SELECT privateleagueid, privateleaguename, privateleaguepasscode FROM privateleague WHERE privateleaguename = $privateleaguename AND privateleaguepasscode = $privateleaguepasscode";

if($query){
    mysql_query("UPDATE login SET privateleagueid = privateleague.privateleagueid WHERE userid = '{$_SESSION['userid']}'");
} else{
    echo "Private League Name or Passcode incorrect";
}  

?>

[AyKay47]

try debugging the query to display any errors that are occuring..

 

mysql_query("UPDATE login SET privateleagueid = privateleague.privateleagueid WHERE userid = '{$_SESSION['userid']}'") or exit(mysql_error());

[lukep11a]

thanks, it says "Unknown column 'privateleague.privateleagueid' in 'field list'". Though it might work if I changed the query to

$query = "SELECT privateleague.privateleagueid, privateleague.privateleaguename, privateleague.privateleaguepasscode login.userid FROM privateleague, login WHERE privateleague.privateleaguename = $privateleaguename AND privateleague.privateleaguepasscode = $privateleaguepasscode";

but it still comes up with the same error

[AyKay47]

here you go, should work for you

 

mysql_query("UPDATE login, privateleague SET login.privateleagueid = privateleague.privateleagueid WHERE login.userid = '{$_SESSION['userid']}'") or exit(mysql_error());

[lukep11a]

thanks, it's updating the login table now but it's updating it with a '1' rather that the correct privateleagueid, do you know why this could be?

[lukep11a]

it's also not displaying the message "Private League Name or Passcode incorrect" if I enter the details in the form incorrectly

[AyKay47]

I have just noticed that you are not actually running your first query to check for the input data in your db

 

<?php

$privateleaguename = $_POST['privateleaguename'];
$privateleaguepasscode = $_POST['privateleaguepasscode'];

$query = mysql_query("SELECT privateleagueid, privateleaguename, privateleaguepasscode FROM privateleague WHERE privateleaguename = $privateleaguename AND privateleaguepasscode = $privateleaguepasscode"); //added mysql_query()

if($query){
    mysql_query("UPDATE login SET privateleagueid = privateleague.privateleagueid WHERE userid = '{$_SESSION['userid']}'");
} else{
    echo "Private League Name or Passcode incorrect";
}  

?>

[Psycho]

1. You are not enclosing the values in quote marks

2. You only need ONE query.

3. You need to be validating the user input to prevent errors and SQL Injection

 

//Parse user input
$privateleaguename     = mysql_real_escape_string(trim($_POST['privateleaguename']));
$privateleaguepasscode = mysql_real_escape_string(trim($_POST['privateleaguepasscode']));

//Create/run single query
$query = "UPDATE `login`
          SET `privateleagueid` =
              (SELECT privateleagueid
               FROM privateleague
               WHERE privateleaguename = '$privateleaguename'
                 AND privateleaguepasscode = '$privateleaguepasscode')
          WHERE userid = '{$_SESSION['userid']}'"
$result = mysql_query($query);

//Check results
if(!$result)
{
    echo "Error running query " . mysql_error();
}
elseif(mysql_affected_rows()==0)
{
    echo "Private League Name or Passcode incorrect";
}
else
{
    echo "Update successful.";
}

[lukep11a]

thankyou both, it now works, I have another question, is it possible to change the if statement, to only update privateleagueid if it is equal to '0' or else update privateleagueid1 if it is equal to '0' and if its not update privateleagueid2 if it is equal to '0' and if not display an error message?

[Psycho]

Seriously? Why would you hold back on that requirement? I enjoy helping people, but it is disheartening when you build a solution based upon the requirements submitted only to have the OP then change the requirements. Your question doesn't event apply

is it possible to change the if statement, to only update privateleagueid if it is equal to '0' ...

 

By the time the if() statement is run, the update has already been performed. All that logic could be stuffed into a single query, but it would be overcomplicated, so the solution is two queries with some logic in-between them

 

//Parse user input
$privateleaguename     = mysql_real_escape_string(trim($_POST['privateleaguename']));
$privateleaguepasscode = mysql_real_escape_string(trim($_POST['privateleaguepasscode']));

//Check current values in login table for user
$query = "SELECT `privateleagueid`, `privateleagueid1`, `privateleagueid2`
          FROM `login`
          WHERE `userid` = '{$_SESSION['userid']}'"
$result = mysql_query($query);

if(!$result)
{
    echo "Error running query " . mysql_error();
}
elseif(mysql_affected_rows()==0)
{
    echo "Error: User ID incorrect";
}
else
{
    //Got user record - proceed
    $user = mysql_fetch_assoc($result);

    //Detemine the field to update
    $updateField = false;
    if($row['privateleagueid']=='0')
    {
        $updateField = 'privateleagueid';
    }
    elseif($row['privateleagueid1']=='0')
    {
        $updateField = 'privateleagueid1';
    }
    elseif($row['privateleagueid2']=='0')
    {
        $updateField = 'privateleagueid2';
    }

    if(!$updateField)
    {
        echo "Error: User private league id fields are already set";
    }
    else
    {
        //Perform the update
        $query = "UPDATE `login`
                  SET `{$updateField}` =
                      (SELECT privateleagueid
                       FROM privateleague
                       WHERE privateleaguename = '$privateleaguename'
                         AND privateleaguepasscode = '$privateleaguepasscode')
                  WHERE userid = '{$_SESSION['userid']}'"
        $result = mysql_query($query);

        //Check results
        if(!$result)
        {
            echo "Error running query " . mysql_error();
        }
        elseif(mysql_affected_rows()==0)
        {
            echo "Private League Name or Passcode incorrect";
        }
        else
        {
            echo "Update successful.";
        }
    }
}

[lukep11a]

I am really sorry about that, I am just trying to build my site up one step at a time, and every time I get over one hurdle I feel like it is an acheivement, you shouldn't feel disheartened because I was over the moon last night when that code you provided worked with no problems whatsoever. Originally that is all I had wanted that form to do but then I thought in an ideal world it would be good if each user could sign up to more than one private league. Thankyou so much for your help I really do appreciate it, I will give the code you most recently provided a go tonight when I get chance to work on it some more.

[Psycho]

... then I thought in an ideal world it would be good if each user could sign up to more than one private league.

 

Then you should really consider creating an associative table to store the selected league's for each user. You could, potentially, allow users to sign up to an unlimited number of leagues. Or, if you thought that three leagues is enough you could enforce that as well, but if you then decided to increase it in the future you could easily change it without having to rewrite any code. Besides, it isn't good format storing those three values in there separate columns in the login table.

 

 

[Psycho]

After reviewing the last code, I see that not only would separating the selected leagues into their own table would be a more proper structure, it would simplify the code. Revised code is below. You would need to create a new table, called `user_leagues`, with two columns: `userid` and `privateleagueid`. You could then remove the three league id columns from the login table. Of course you would need to update any other code that pulls user/league information accordingly. But, the benefit is that you can easily change the number of leagues a user can select by changing the value of the $MAX_LEAGUES variable and everything else takes care of itself. NO recoding for all those if/else statements.

 

(Note: there was a couple of errors in the code I provided previously that are corrected below as well. 1) I used mysql_affected_rows() as an error check after the first query and it should have been mysql_num_rows(). 2) I put the results for the first query into the variable $user, but then referenced it using $row)

 

<?php

//Config var to set max number of leagues for a user
$MAX_LEAGUES = 3;

//Parse user input
$privateleaguename     = mysql_real_escape_string(trim($_POST['privateleaguename']));
$privateleaguepasscode = mysql_real_escape_string(trim($_POST['privateleaguepasscode']));

//Get count of currently selected leagues for user
$query = "SELECT COUNT(`privateleagueid`) as `league_count`
          FROM `user_leagues`
          WHERE `userid` = '{$_SESSION['userid']}'
          GROUP BY `userid`"
$result = mysql_query($query);

if(!$result)
{
    echo "Error running query " . mysql_error();
}
elseif(mysql_num_rows($result)==0)
{
    echo "Error: User ID incorrect";
}
else
{
    //Got user record - proceed
    $user = mysql_fetch_assoc($result);

    //Detemine the current count of selected leagues
    $updateField = false;
    if($user['league_count']>=$MAX_LEAGUES)
    {
        echo "Error: You have already reached the max league count of {$MAX_LEAGUES}.";
    }
    else
    {
        //Perform the update
        $query = "INSERT INTO `user_leagues`
                      (`userid`, `privateleagueid`)
                  SELECT '{$_SESSION['userid']}', privateleagueid
                  FROM `privateleague`
                  WHERE `privateleaguename` = '$privateleaguename'
                    AND `privateleaguepasscode` = '$privateleaguepasscode'";

        $result = mysql_query($query);

        //Check results
        if(!$result)
        {
            echo "Error running query " . mysql_error();
        }
        elseif(mysql_affected_rows()==0)
        {
            echo "No match for Private League Name and Passcode.";
        }
        else
        {
            echo "Update successful.";
        }
    }
}


?>

[lukep11a]

Thanks so much, that works brilliantly, and you right it is so much more efficient to have an extra table and to have a variable with the number of leagues they can join!

[Psycho]

To allow unlimited leagues just change the validation to

if($user['league_count']>=$MAX_LEAGUES && $MAX_LEAGUES!=0)

 

Then you can allow unlimited leagues by setting the $MAX_LEAGUES variable to 0.