foreach Array inside foreach Array?

[brady123]

I'm not sure if the title explains it very well, but here is an image of it. I am trying to figure out how to access the option selected (equal, not equal, etc.), along with its input box, for each column (the checkboxes on the right) selected. The purpose of this is to allow a non-programming administrator user to create a custom query to access information. I can find out which checkboxes are selected no problem, but finding the accompanying drop-down option and input box is difficult for me. How do I do this?

 

If you have a suggestion on how to change it, I'm very open. Thank you in advance!

 

 

if(!isset($_POST['submit']))
{
echo "
<form method='post'>
<table>
<tr>
  <td valign='top'>SELECT</td>
  <td valign='top'>
   <table>";
  
// LIST ALL COLUMNS IN A TABLE
$result = mysql_query("SELECT * FROM table_name") or die(mysql_error());
$rowcount=mysql_num_rows($result);
$y=mysql_num_fields($result);
for ($x=0; $x<$y; $x++)
{ echo "
    <tr>
 <td>
  <input type='checkbox' name='fields[]' value='".mysql_field_name($result, $x)."'>".mysql_field_name($result, $x)."
     </td>
</tr>"; }
  
   echo "
   </table>
  </td>
  <td valign='top'>FROM allocations</td>
  <td valign='top'>WHERE</td>
  <td>
   <table>";
   
// LIST ALL COLUMNS IN A TABLE
$result = mysql_query("SELECT * FROM table_name") or die(mysql_error());
$rowcount=mysql_num_rows($result);
$y=mysql_num_fields($result);
for ($x=0; $x<$y; $x++)
{ echo "
    <tr>
 <td>
  <input type='checkbox' name='column[]' value='".mysql_field_name($result, $x)."'>".mysql_field_name($result, $x)."
 </td>
 <td>
  <select name='operator[]'>
          <option value='='>equals</option>
          <option value='<>'>does not equal</option>
          <option value='>'>greater than</option>
          <option value='<'>less than</option>
          <option value='>='>greater than or equal to</option>
          <option value='<='>less than or equal to</option>
          <option value='LIKE'>is like</option>
         </select>
 </td>
     <td><input type='text' name='criteria[]'></td>
</tr>"; }

   echo "
   </table>
  </td>
  <td valign='top'><input type='submit' value='Process Query' name='submit' class='submit'></td>
</tr>
</table>
</form>";
}
else
{
echo "<b>Fields to edit:</b> ";
foreach ($_POST['fields'] as $fields) {
	echo $fields,", ";
}

echo "<br><br>";
echo "<b>Columns to query:</b> ";
foreach ($_POST['column'] as $columns) {
	echo $columns," HERE IS THE SPOT, ";
}
}

[phpJoeMo]

Cool idea bro!

 

Give this a shot.  It should do what you want, as well as output a query that you can execute.

if(!isset($_POST['submit']))
{
echo "
<form method='post'>
<table>
<tr>
  <td valign='top'>SELECT</td>
  <td valign='top'>
   <table>";
  
// LIST ALL COLUMNS IN A TABLE
$result = mysql_query("SELECT * FROM table_name") or die(mysql_error());
$rowcount=mysql_num_rows($result);
$y=mysql_num_fields($result);
for ($x=0; $x<$y; $x++)
{ echo "
    <tr>
 <td>
  <input type='checkbox' name='fields[]' value='".mysql_field_name($result, $x)."'>".mysql_field_name($result, $x)."
     </td>
</tr>"; }
  
   echo "
   </table>
  </td>
  <td valign='top'>FROM allocations</td>
  <td valign='top'>WHERE</td>
  <td>
   <table>";
   
// LIST ALL COLUMNS IN A TABLE
$result = mysql_query("SELECT * FROM table_name") or die(mysql_error());
$rowcount=mysql_num_rows($result);
$y=mysql_num_fields($result);
for ($x=0; $x<$y; $x++)
{ echo "
    <tr>
 <td>
  <input type='checkbox' name='column[]' value='".mysql_field_name($result, $x)."'>".mysql_field_name($result, $x)."
 </td>
 <td>
  <select name='operator[]'>
          <option value='='>equals</option>
          <option value='<>'>does not equal</option>
          <option value='>'>greater than</option>
          <option value='<'>less than</option>
          <option value='>='>greater than or equal to</option>
          <option value='<='>less than or equal to</option>
          <option value='LIKE'>is like</option>
         </select>
 </td>
     <td><input type='text' name='criteria[]'></td>
</tr>"; }

   echo "
   </table>
  </td>
  <td valign='top'><input type='submit' value='Process Query' name='submit' class='submit'></td>
</tr>
</table>
</form>";
}
else
{
$fields = '';
function build_fields(&$field, $name)
{
$field .= $name;
}
$conditions = '';
function build_conditions(&$condition, $where, $opt, $value)
{
$condition .= "$where $opt '$value'";
}

$numFields = count($_POST['fields']);
for ($i=0; $i <= $numFields; $i++) {
	$x = ($i == $numFields ? null : ', ');
	build_fields( $fields, $_POST['fields'][$i].$x );
}
echo "<b>Fields to edit:</b> ".$fields;

echo "<br><br>";


$numColumns = count($_POST['columns']);
for ($i=0; $i <= $numColumns; $i++) {
	$x = ($i == $numFields ? null : ' and ');
	build_conditions( $conditions, $_POST['column'][$i], $_POST['operator'][$i], $_POST['criteria'][$i].$x );
}
echo "<b>Columns to query:</b> ".$conditions;
echo "<br/><b>Query to Execute (optional)</b><br/>
SELECT $fields WHERE $conditions";
}

 

[Psycho]

The problem you have is that checkboxes are only passed in the POST data if they are checked. So, if user checks boxes 0 and 5, the post data has checkbox fields with ids of 0 and 1.

 

The solution is very easy though. I assume you have a loop that created all the form inputs. In that loop, I also assume you have a variable to keep track of the loops (i.e. $x).

 

In the loop that creates the fields, set the value of the checkboxes using the $x value (i.e. 0 through 9). Also, for the field names of the fields that correspond to those checkboxes, set the index in the field name using the $x.

 

Now, when you receive the form post, you check the values passed for the "field" checkboxes and use those values to reference the fields associated with them.

[jonsjava]

Posted to wrong thread. sry.

[brady123]

First off, thank you phpJoeMo for the help! It worked, with the little snag that mjdamato brought up. I'm not totally sure that I understand what you're getting at, mjdamato. You see my code there, are you suggesting something like assign a numeric value, based on the $x counter in my "for" loop, then once the data has been submitted, check each number (based on $x), and see if the checkbox has been checked. Off of that, I would grab the information to the corresponding operator drop-down list and input box? Meaning that the names would be something to the ffect of "operator0, operator 1, etc." and "input1, input2, etc." Thus, once I find that checkbox1 has been checked, grab the information from operator1 and input1?

 

I don't know if my reasoning makes sense here. To do this, would I just rename my input from name='column[]' to name='column[$x]' ? I could be way off here, but the help is much appreciated!

 

The problem you have is that checkboxes are only passed in the POST data if they are checked. So, if user checks boxes 0 and 5, the post data has checkbox fields with ids of 0 and 1.

 

The solution is very easy though. I assume you have a loop that created all the form inputs. In that loop, I also assume you have a variable to keep track of the loops (i.e. $x).

 

In the loop that creates the fields, set the value of the checkboxes using the $x value (i.e. 0 through 9). Also, for the field names of the fields that correspond to those checkboxes, set the index in the field name using the $x.

 

Now, when you receive the form post, you check the values passed for the "field" checkboxes and use those values to reference the fields associated with them.

[phpJoeMo]

To: brady123

 

There really isn't a need to add a number in the form.  The field name is in the input value.  Note that your are simply creating an array by having name="column[]".  When you post this data again to your script, php combines all the inputs with name "column[]" into an enumerated array.  All you are simply trying to do is get the fields for the query.  It basically looks like this:

 

$_POST['column'] = array('fieldName1','fieldName2',etc...);

 

Can you explain further what you are trying to accomplish?

 

It sounds like you want to give users the ability to query tables in your db based on conditions that they choose.  If this is the case, I think what you have + the code I sent should do the trick.  Your flow should be as follows:

 

User chooses tables.

Field options are displayed accordingly (to table.  IE First Name, Last Name for Customers)

User chooses Fields to display (which can vary)

User chooses conditions whereby to narrow the query (which can also vary)

Form is submitted.

 

At this point, there can be only 1-2 fields that the user wishes to display, with an optional amount of conditions.

 

Therefore, the data I see being key for the submission is:

 

Fields (varies on user selection)

Table (based on user selection)

Conditions (optional | varies on user selection)

 

Formulate Query.

 

Is this the jist of what you are trying to accomplish?

 

 

[phpJoeMo]

You'll probably want to add another select box to the left of the condition select box, that will give users the ability to choose from any of the fields in the table.

 

You'll also need to make sure that there are the same number of condition fields, options, and values as part of your validation.  ID

 

$_POST['column'] = array(    'columnName1','columnName2','columnName3');
$_POST['operator'] = array( 'operator1',       'operator2',      'operator3');
$_POST['criteria'] = array(    'ccriteria1',         'criteria2',        'criteria3');

 

[Psycho]

Below is a working solution - albeit a rough one. You just need to provide the appropriate information in the five variables at the top of the script. I was going to extend the functionality to first let the user select any table, but I'll let you do that if you so choose.

 

Here are some notes about the script:

 

1. I use the query "DESCRIBE [TABLE_NAME]" to get the field information for the table. Currently, the script is only using the field names, but the results contain much more information such as the field type (i.e. int, varchar, etc.). This script can be further expanded to provide only the appropriate comparison operators for each field. For example, it doesn't make sense to have a "greater than" operator for a text field, but it would be handy to have a "starts with".

 

2. I like phpJoeMo's suggestion above about having a select box to determine the fields to be used in the comparisons. That allows you to have the same field used twice in the WHERE clause (but that would necessitate the ability to use OR's - see #4 below)

 

3. After running the script, I think it would make sense to have the input fields repopulated with the submitted values when the results are displayed. That way the user can easily make minor modifications to get the results they are looking for.

 

4. Lastly, all of the comparisons in the WHERE clause are considered as AND comparisons. It would be beneficial to allow for OR'd conditions as well. But, then you would also need to add the ability to have parenthesized comparisons.

 

EDIT #5: Very important! I didn't take the time to escape the user input. You will want to do that to prevent errors and possible database corruption.

 

I'll let you read through the code to see what I did and where.

 

<?php

$__DB_SERVER__ = "localhost";
$__DB_USER__   = "XXXXX";
$__DB_PASS__   = "XXXXX";
$__DB_NAME__   = "test";
$__DB_TABLE__  = "lu_products";

mysql_connect($__DB_SERVER__, $__DB_USER__, $__DB_PASS__) or die(mysql_error());
mysql_select_db($__DB_NAME__) or die(mysql_error());


if(isset($_POST['select']))
{
    $selectClause = '`' . implode('`, `', $_POST['select']) . '`';

    $whereParts = array();
    foreach($_POST['where'] as $field)
    {
        $whereParts[] = "`{$field}` {$_POST['compare'][$field]} '{$_POST['value'][$field]}'";
    }
    $whereClause = implode("\n  AND ", $whereParts);

    $query = "SELECT $selectClause
              FROM $__DB_TABLE__
              WHERE $whereClause";
    echo $query;
    $result = mysql_query($query) or die(mysql_error());

    if(mysql_num_rows($result)==0)
    {
        $output = "There were no matching records.";
    }
    else
    {
        $header = true;
        $output = "<table border='1'>\n";
        while($row = mysql_fetch_assoc($result))
        {
            if($header)
            {
                $output .= "  <tr>\n";
                foreach($row as $label => $value)
                {
                    $output .= "    <th>$label</th>\n";
                }
                $output .= "  </tr>\n";
                $header = false;
            }
            $output .= "  <tr>\n";
            foreach($row as $value)
            {
                $output .= "    <td>$value</td>\n";
            }
            $output .= "  </tr>\n";
        }
        $output .= "</table>\n";
    }
}

//Get fields for table
$query = "DESCRIBE {$__DB_TABLE__}";
$result = mysql_query($query) or die(mysql_error());

if($result)
{
    while($row = mysql_fetch_assoc($result))
    {
        $fieldNames[] = $row['Field'];
    }
}

$selectFields = '';
$whereFields = '';
$compareOptions  = "<option value='='>equals</option>\n";
$compareOptions .= "<option value='<>'>does not equal</option>\n";
$compareOptions .= "<option value='>'>greater than</option>\n";
$compareOptions .= "<option value='<'>less than</option>\n";
$compareOptions .= "<option value='>='>greater than or equal to</option>\n";
$compareOptions .= "<option value='<='>less than or equal to</option>\n";
$compareOptions .= "<option value='LIKE'>is like</option>";

foreach($fieldNames as $field)
{
    $selectFields .= "<tr>\n";
    $selectFields .= "  <td colspan=\"4\" style=\"padding-left:25px;\">\n";
    $selectFields .= "    <input type=\"checkbox\" name=\"select[]\" value=\"{$field}\" /> {$field}\n";
    $selectFields .= "  </td>\n";
    $selectFields .= "</tr>\n";
    $whereFields .= "<tr>\n";
    $whereFields .= "  <td style=\"padding-left:25px;\"><input type=\"checkbox\" name=\"where[]\" value=\"{$field}\" /></td>\n";
    $whereFields .= "  <td>{$field}</td>\n";
    $whereFields .= "  <td><select name=\"compare[$field]\">\n";
    $whereFields .= $compareOptions;
    $whereFields .= "  </select></td>\n";
    $whereFields .= "  <td><input type=\"text\" name=\"value[$field]\"></td>\n";
    $whereFields .= "</tr>\n";
}

?>
<html>
<head>
<style>
th { text-align: left; padding-top:10px;}

</style>
</head>

<body>

<form action="" method="post">
<table border=0>
    <tr>
        <th colspan="4">SELECT</th>
    </tr>
    <?php echo $selectFields; ?>
    <tr>
        <th colspan="4">FROM <?php echo $__DB_TABLE__; ?></th>
    </tr>
    <tr>
        <th colspan="4">WHERE</th>
    </tr>
    <?php echo $whereFields; ?>
</table>
<button type="submit">SUBMIT</button>
<br><br>
<?php echo $output; ?>
</form>
</body>
</html>

[brady123]

Wow, HUGE thank you to everyone for your help, especially mjdamato for taking the time to completely write the script! I really, really appreciate your help! It's working like a charm, and makes sense so that I can customize it. If I end up expanding it to use OR operators and other options, based on field type, I'll be sure to post on the forum for others to use.

 

Thanks again!