PHP search and filter problem

[saleh.keshko.09]

Here is my PHP code:

$fields=array('eventID','eventTitle','te_events.venueID','te_events.catID ',
             'eventStartDate','eventEndDate','eventPrice');


// initialize empty array for WHERE clauses
$wheres=array();

// loop through field names, get POSTed values,
// and build array of WHERE clauses, excluding false values
foreach ($fields as $field) {

  // get existing field value from POST, mark missing or empty value as FALSE
  ${$field} = isset($_POST[$field]) && trim($_POST[$field])!=''
      ? trim($_POST[$field]) : false;

  // add to array of WHERE clauses only if value is not FALSE
  if (${$field}) { $wheres[]="$field='".${$field}."'"; }

}

// build SELECT statement from WHERE clauses
$sql="SELECT eventID, eventTitle, te_events.catID, te_events.venueID, te_category.catDesc, te_venue.venueName, te_venue.location,  eventStartDate, eventEndDate, eventPrice FROM te_events INNER JOIN te_category ON te_category.catID=te_events.catID INNER JOIN te_venue ON te_venue.venueID=te_events.venueID 
 WHERE ".
     (!empty($wheres) ? implode(" AND ",$wheres) : '1=1').
     " 
     ORDER BY eventTitle";

Here is html code :

<p><label class="label1">Event Title:</label><input type='text' class="input_personal" name='eventTitle' id ='eventTitle'/></p>
<p><label class="label1">Venue Name:</label></p> 
        <p><select class="select" name="venueID" id ='venueID' >
          <option>Select</option>
          <option  value='v1'>Theatre Royal</option>
          <option  value='v2'>BALTIC Centre for Contemporary Art</option>
          <option  value='v3'>Laing Art Gallery</option>
          <option  value='v4'>The Biscuit Factory</option>
          <option  value='v5'>Discovery Museum</option>
          <option  value='v6'>HMS Calliope</option>
          <option  value='v7'>Metro Radio Arena</option>
          <option  value='v8'>Mill Volvo Tyne Theatre</option>
          <option  value='v9'>PLAYHOUSE Whitley Bay</option>
          <option  value='v10'>Shipley Art Gallery</option>
        </select></p>
<p><label class="label1">Cetogry:</label></p> 
        <p><select class="select" name="catID" id ='catID'>
          <option>Select</option>
          <option  value='c1'>Carnival</option>
          <option  value='c2'>Theatre</option>
          <option  value='c3'>Comedy</option>
          <option  value='c4'>Exhibition</option>
          <option  value='c5'>Festival</option>
          <option  value='c6'>Family</option>
          <option  value='c7'>Music</option>
          <option  value='c8'>Sport</option>
        </select></p>

I wrote a code to search and filter the entered data and display the results back. In my data base I have 3 tables te_events , te_category and te_venue. So the problem with my code is that it doesn't display the catID and the venueID but all the other fields are fine. So can anyone help my to solve this problem.

Thanks

[broj1]

Select element names are venueID and catID but the field names in the array are te_events.venueID and te_events.catID so you do not get a match here (isset($_POST[$field]) returns false).

[saleh.keshko.09]

But when I keep it like taht venueID and catID I get an error message saying "Column 'venueID' in where clause is ambiguous" and the other one saying "Column 'catID' in where clause is ambiguous", so how can I solve the problem

[broj1]

Depends on the situation. So the venueID and catID are from another table. You could code it this way:

...

  // add to array of WHERE clauses only if value is not FALSE
  if (${$field}) { 
      $wheres[] $field = venueID || $field = catID ? "$field='" . 'te_events.' . ${$field}."'" : "$field='" . ${$field}."'"; 
  }
}
...

The above code checks if the fields need to be prepended with the table name. But this code is not very flexible. Other approach would be to have select names contain table names (with dots) but I am not sure if this is valid in HTML:

<select class="select" name="te_events.catID" id ='catID'>

[broj1]

Yet another approach. Do not use table name in the array:

$fields=array('eventID','eventTitle','venueID','catID ',
'eventStartDate','eventEndDate','eventPrice');

Prepend table name to all $wheres:

// add to array of WHERE clauses only if value is not FALSE
if (${$field}) { $wheres[]="$field='" . 'te_events.' . ${$field}."'"; }

Again, not very flexible (and I am not sure if this is correct since I do not know all the logic).

[saleh.keshko.09]

Both ways didn't work

[broj1]

My previous post were not tested, only conceptual. Tonight I will simulate/test the code in real environment and let you know how I went.

[broj1]

I tested both my suggestions in my environment and they work. But please note, you have an extra space in one of the elements of the $fields array. I also simplified the code since there is no need to have intermediate $wheres array. Also see how I changed the name attributes of the select elements. So this is the code (see also the comments in the code):

<?php
// first check if $_POST is set and not empty
if(isset($_POST) && !empty($_POST)) {

    $fields = array('eventID','eventTitle', 'te_events.venueID', 'te_events.catID', 'eventStartDate', 'eventEndDate', 'eventPrice');

    // start building the query
    $sql = "SELECT eventID, eventTitle, te_events.catID, te_events.venueID, te_category.catDesc, te_venue.venueName, te_venue.location,  eventStartDate, eventEndDate, eventPrice 
    FROM te_events INNER JOIN te_category ON te_category.catID=te_events.catID 
    INNER JOIN te_venue ON te_venue.venueID=te_events.venueID
    WHERE 1";

    // loop through the POSTed array,
    // and add WHERE clauses to the query
    foreach ($_POST as $key => $val) {

        // check if the $_POST key (form field name) exists in the $fields array
        // and has allowed value
        if(in_array($key, $fields) && ($val != '' && $val != 'Select')) {

            $sql .= " AND $key='$val'";
        }
    }

    // add sorting to the query
    $sql .=  " ORDER BY eventTitle";

    // DEBUG: display the query
    echo $sql;
}

?>
<form action="#" method="post">

<p><label class="label1">Event Title:</label><input type='text' class="input_personal" name='eventTitle' id ='eventTitle'/></p>
<p><label class="label1">Venue Name:</label></p> 
    <p><select class="select" name="te_events.venueID" id ='te_events.venueID' >
      <option>Select</option>
      <option  value='v1'>Theatre Royal</option>
      <option  value='v2'>BALTIC Centre for Contemporary Art</option>
      <option  value='v3'>Laing Art Gallery</option>
      <option  value='v4'>The Biscuit Factory</option>
      <option  value='v5'>Discovery Museum</option>
      <option  value='v6'>HMS Calliope</option>
      <option  value='v7'>Metro Radio Arena</option>
      <option  value='v8'>Mill Volvo Tyne Theatre</option>
      <option  value='v9'>PLAYHOUSE Whitley Bay</option>
      <option  value='v10'>Shipley Art Gallery</option>
    </select></p>
<p><label class="label1">Cetogry:</label></p> 
    <p><select class="select" name="te_events.catID" id ='te_events.catID'>
      <option>Select</option>
      <option  value='c1'>Carnival</option>
      <option  value='c2'>Theatre</option>
      <option  value='c3'>Comedy</option>
      <option  value='c4'>Exhibition</option>
      <option  value='c5'>Festival</option>
      <option  value='c6'>Family</option>
      <option  value='c7'>Music</option>
      <option  value='c8'>Sport</option>
    </select></p>

    <input type="submit" name="submit" value="Submit">        
</form>   

[Dean_1]

Northumbria dynamic technology ;)