Lists sql and variables

Question

PHP + HTML:

I have three list boxes the contents of which(when clicked) are placed into a variable in a sql SELECT string.

Something like this:

SELECT * FROM table where field1= '" & variable1 & "' AND field2 = '" & Variable2 &"' AND field3 = '" & Variable3 & "'"

This works well if all the list boxes are clicked BUT if I only click 2 or 1 of the list boxes nothing happens and I get a blank table.

I tried it with check boxes and get the same thing. The thing is I will be expanding the number of Lists to 12 but this will produce the same problem.

Very new to all of this so any suggestion much appreciated. Kind Regards jim

Can you post the PHP code that generates the query, and the HTML of the form? — Halcyon
– Halcyon, Commented Mar 31, 2014 at 12:46

blue · Accepted Answer · 2014-03-31 12:48:35Z

1

When a checkbox is not checked it is not submitted to the server! Your PHP code probably depends on that while it shouldn't.

answered Mar 31, 2014 at 12:48

blue

1,9471 gold badge11 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mark · Accepted Answer · 2014-03-31 12:49:50Z

1

You can use this query with your variable names.

$query = "SELECT * FROM TABLENAME WHERE field1 LIKE '".$variable1."' AND field2 LIKE '".$variable2."' AND field3 LIKE '".$variable3."'";

answered Mar 31, 2014 at 12:49

Mark

971 silver badge15 bronze badges

Comments

Samy Massoud · Accepted Answer · 2014-03-31 12:52:12Z

1

Just check if variable exist before adding it to your sql
Your code should be something like that

if(isset($variable1)){
  $sql .= " AND field1 = '$variable1'";
}

answered Mar 31, 2014 at 12:52

Samy Massoud

4,3852 gold badges37 silver badges50 bronze badges

Comments

sunny · Accepted Answer · 2014-03-31 12:53:26Z

only the checked checkboxes get posted. so it needs slightly different appraoch. You can acheive it like this- put a hidden input with the same name as the checkbox that might not be checked. I think it works so that if the checkbox isn't checked, the hidden input is still successful and sent to the server but if the checkbox is checked it will override the hidden input before it. This way you don't have to keep track of which values in the posted data were expected to come from checkboxes.

<form>
  <input type='hidden' id='testName' value='0' name='carga1'>
  <input type='checkbox' id='testNameHidden' value='1' name='carga1'>

</form>

Before submitting the form , disabled the hidden field based on the checked condition.

<script>
if(document.getElementById("testName").checked){
  document.getElementById('testNameHidden').disabled = true;
}
</script>

I personally think its the easiest approach for this.

user2417483 · Accepted Answer · 2014-03-31 12:55:39Z

0

The listbox should have the first entry as empty so the use doesn't have to select anything. Your php code should use

$fld1 = isset($_POST['field1']) ? $_POST['field1'] : "";

Then build your sql statement.

$sql = "SELECT * FROM table ";
$where ="";
if( $fld1 != "" ) $whr .= 'field1 = '.$fld1;
if( $fld2 != "" {
  if( $whr != "" ) $whr += " AND ";
  $whr .= 'field2='.$fld2;
}

answered Mar 31, 2014 at 12:55

user2417483

Comments

Callidior · Accepted Answer · 2014-03-31 13:06:21Z

0

I suggest building an array of SQL conditions, while only adding a condition if something in the respective list box has been selected. That would result in something like this:

$conds = array(1);
for ($i = 1; $i <= 3; $i++)
    if (isset($_POST['Variable' . $i]))
        $conds[] = "field" . $i . " = '" . $_POST['Variable' . $i] . "'";
$query = "SELECT * FROM table WHERE " . implode(" AND ", $conds);

Of course I don't know the exact identifiers of your variables, so, take this as an orientation.

Edit: Changed code slightly as proposed by Doge in the comments.

edited Mar 31, 2014 at 13:06

answered Mar 31, 2014 at 12:53

Callidior

2,9042 gold badges20 silver badges28 bronze badges

6 Comments

blue Over a year ago

In fact, there is a case where a variable might be empty ;) Use isset().

Halcyon Over a year ago

Your WHERE trick still fails if $conds is empty. You need to initialize $conds as array("1").

Callidior Over a year ago

@blue: empty($var) is a shortcut for (!isset($var) || $var == null)

Halcyon Over a year ago

@Callidior I think blue means the case where $var has an empty value like "". I think isset or array_key_exists is correct here.

Callidior Over a year ago

@Doge: Ah, okay. I assumed that the question owner would also like to exclude ignore empty variables; but I can't know that. Sorry, blue, for that misunderstanding. I will change that code again to use isset().

|

Collectives™ on Stack Overflow

Lists sql and variables

6 Answers 6

Comments

Comments

Comments

Comments

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related