Tee Famakin Tee Famakin - 1 year ago 74
MySQL Question

Pass values from for each loop to mysql where clause

I am creating a search form where I am supposed to be able to select names of various users using a check list and then get their information.

Basically I have started by collating and posting the names of this users using

<?php do { ?>
<label style="color:#000; " for="input Address"> <?php echo $row_marketer['Username']; ?> <input type="checkbox" name="multiagent[]" value="<?php echo $row_marketer['Username']; ?>" /></label>
<?php } while ($row_marketer = mysql_fetch_assoc($marketer)); ?>

This is to populate my checklist with the names I can select from

Then I am trying to pass the names to my where clause in my sql statement using a for each loop.

foreach($_POST['multiagent'] as $idx => $studentname) {

$query_certs = sprintf("SELECT * FROM transactions WHERE Username IN ('". $studentname ."')", GetSQLValueString($colname3_certs, "text"),GetSQLValueString($colname_certs, "text"),GetSQLValueString($colname2_certs, "text"));

$query_limit_certs = sprintf("%s LIMIT %d, %d", $query_certs, $startRow_certs, $maxRows_certs);
$certs = mysql_query($query_limit_certs, $kbl) or die(mysql_error());
$row_certs = mysql_fetch_assoc($certs);

Having done this, my problem is it only passes the last name on the for each loop.

Answer Source

As you have an array of value in post field you can use implode function instead of looping them.

 if ( isset($_POST['multiagent'] ) && is_array ( $_POST['multiagent']  ) ) {

    //implode user name in string 
    $studentname = implode("','",$_POST['multiagent']);

    //Write query string
    $query_certs = "SELECT * FROM transactions WHERE Username IN ('". $studentname ."')";

    //Add limit in query
    $query_limit_certs = sprintf("%s LIMIT %d, %d", $query_certs, $startRow_certs, $maxRows_certs);

    //Execute query
    $certs = mysql_query($query_limit_certs, $kbl) or die(mysql_error());

    //Get row
    $row_certs = mysql_fetch_assoc($certs);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download