Exploding and checking against DB

[Xtremer360]

$characterIDs = explode(',', $_POST['characterIDList']);

 

After the explode I want to take all those values and do a select statement in a database table for any of the values that match any of the values inside that variable variable. Not sure how to do this since there is no limit to how may there could be.

[Zurev]

$characterIDs = explode(',', $_POST['characterIDList']);

 

After the explode I want to take all those values and do a select statement in a database table for any of the values that match any of the values inside that variable variable. Not sure how to do this since there is no limit to how may there could be.

 

Not going to lie, it's a bit difficult to understand, but I'll give it a go.

 

foreach ($characterIDs as $characterID)
{
mysql_query("SELECT * FROM `tablename` WHERE `fieldname` = '$characterID'");
}

 

Foreach loop takes each value of an array and runs through it what is in the loop, the as $characterID refers to what each individual array value will be equal to, you can also use the array key by doing as $key => $characterID.

[litebearer]

Might look into find_in_set

http://www.bitbybit.dk/carsten/blog/?p=162

[Pikachu2000]

It would be infinitely better not to put a query in a loop. Assuming the values are numeric, this should work, if they're string values you'd need to account for that by adding appropriate single-quotes.

 

$query = "SELECT * FROM `tablename` WHERE `fieldname` IN ( " . implode(', ', $characterIDs) . " )";
if ( $result = mysql_query($query) ) {
     // do your mysql_fetch_whatever
}

[Xtremer360]

Thank you all.

[Xtremer360]

Basically after form submission here its going to establish the variables from the POST method of the form. Its going to run the first select query and then the second select query. Can I add an additional argument to the Qresult variable to accept the second Select query.

 

if (isset($_POST['submithandler'])) {
    $username = mysqli_real_escape_string($dbc, $_POST['username']);
    $password = sha1($_POST['password']);
    $firstname = mysqli_real_escape_string($dbc, $_POST['firstname']);
    $lastname = mysqli_real_escape_string($dbc, $_POST['lastname']);
    $email = mysqli_real_escape_string($dbc, $_POST['email']);
    $status = (int)$_POST['status'];
    $admin = mysqli_real_escape_string($dbc, $_POST['admin']);
    $characterIDs = explode(',', $_POST['characterIDList']);
    $defaultcharid = (int) $characterIDs[0];

    $query = "SELECT * FROM `handlers` WHERE (`username` = '".$username."') OR (`email` = '".$email."') OR (`default_character_id` = '".$defaultcharid."')";
    $query2 = "SELECT * FROM `handler_characters` WHERE `character_id` IN ( " . implode(', ', $characterIDs) . " )";
    
    $Qresult = mysqli_query ( $dbc, $query ); // Run The Query  
    
    if (mysqli_num_rows($Qresult) == 0) {
    
        $query = "INSERT INTO `handlers` 
                (username,    password,   firstname,   lastname,   email, status_id, isadmin, default_character_id, creator_id, datecreated) 
            VALUES 
                ('".$username."','".$password."','".$firstname."','".$lastname."','".$email."','".$status."','".$admin."', '".$defaultcharid."', 1, NOW())";
        mysqli_query($dbc, $query);
        
        $result = "good";
    
    } else {
        
        $result = '';
        while ($row = mysqli_fetch_array($Qresult)) {
            if (($username  && $row['username'] == $username) AND ($email  && $row['email']  == $email) AND ($defaultcharid  && $row['default_character_id']  == $defaultcharid) )  {$result .= 'bad7';} else
            if (($email  && $row['email'] == $email) AND ($defaultcharid  && $row['default_character_id']  == $defaultcharid))  {$result .= 'bad6';} else
            if (($username  && $row['username'] == $username) AND ($defaultcharid  && $row['default_character_id']  == $defaultcharid))  {$result .= 'bad5';} else  
            if (($username  && $row['username'] == $username) AND ($email  && $row['email']  == $email))  {$result .= 'bad4';} else  
            if ($defaultcharid        && $row['default_character_id']         == $defaultcharid)      {$result .= 'bad3';} else  
            if ($email        && $row['email']         == $email)      {$result .= 'bad2';} else
            if ($username        && $row['username']         == $username)      {$result .= 'bad1';}  
        }
    }
}

[Xtremer360]

I take that back after some looking I came up with this:

 

What its supposed to do is do the first select query and then the second select query and if both pass then run the insert however I'm hoping that my all my syntax is correct here because when it gets to the insert in the second table its supposed to make a new row for each of the characters that were exploded with that same handler id value.

 

 

$query = "SELECT * FROM `handlers` WHERE (`username` = '".$username."') OR (`email` = '".$email."') OR (`default_character_id` = '".$defaultcharid."')";
    $query .= "SELECT * FROM `handler_characters` WHERE `character_id` IN ( " . implode(', ', $characterIDs) . " )";
    
    $Qresult = mysqli_query ( $dbc, $query ); // Run The Query  
    
    if (mysqli_num_rows($Qresult) == 0) {
    
        $query = "INSERT INTO `handlers` 
                (username,    password,   firstname,   lastname,   email, status_id, isadmin, default_character_id, creator_id, datecreated) 
            VALUES 
                ('".$username."','".$password."','".$firstname."','".$lastname."','".$email."','".$status."','".$admin."', '".$defaultcharid."', 1, NOW())";
        mysqli_query($dbc, $query);
        $query_id = mysqli_insert_id($dbc); 
        $query2 = "INSERT INTO `handler_characters` (`handler_id`, `bio_id`) VALUES ('".$query_id."', '" . implode(', ', $characterIDs) . "')";
        mysqli_query($dbc, $query2);
        
        $result = "good";
    
    }

[ngreenwood6]

Just to go off of what you are working with...You are just making your server work a little harder than it needs to lol. Your characterIds are already comma separated but you are exploding them to put them in an array and then you are imploding them to put them back into the comma separated values. Why not just use the comma separated values you originally had .

[ignace]

$query = "SELECT * FROM `tablename` WHERE `fieldname` IN ( " . implode(', ', $characterIDs) . " )";
if ( $result = mysql_query($query) ) {
     // do your mysql_fetch_whatever
}

 

If you are going to pass it through a query you should validate/filter it:

 

$query = "SELECT * FROM `tablename` WHERE `fieldname` IN (" . implode(',', array_filter(array_map('intval', explode(',', $_POST['characterIDList'])))) . " )";
if ( $result = mysql_query($query) ) {
     // do your mysql_fetch_whatever
}

[Pikachu2000]

Quite true. I was thinking the OP would do that when exploding the original string from the form, but yes it does need to be done at some point before the DB sees it.