Newbie coding help... PLEASE!!

Kevb

Hello.

I am a relative newbie to PHP and have created a form that loads data to a MySQL database. I am now working on simple reporting from that database. I have a form the user can fill out with 5 fields. StartDate, EndDate, Crit1, Crit2, Crit3.

Since the user can put in any combination of criteria I am having problems dynamically building the WHERE clause of the SELECT statement.

The StartDate can be populated singularly without an EndDate so the user is asking just for one day of information or they can ask for a date range by populating both fields. They can also populate any of the other criteria.

Does anyone have any ideas how I can dynamically cycle thru all the fields and build a WHERE clause that incorporates all the fields as applicable?

The form has fields startdate, enddate, IDNumber, Name.

I have assigned all the fields in this manner:

$sdate = $POST['startdate'];
$edate = $POST['enddate'];
$idnum = $POST['IDNumber'];
$name = $POST['Name'];

For example if the user populates startdate and IDNumber then the WHERE clause would only reference those two fields..

I hope I am explaining it correctly, I am quite new to this

ANY HELP would be greatly appreciated!!!

patsfans

Kevb;10892808 wrote:
Hello.

I am a relative newbie to PHP and have created a form that loads data to a MySQL database. I am now working on simple reporting from that database. I have a form the user can fill out with 5 fields. StartDate, EndDate, Crit1, Crit2, Crit3.

Since the user can put in any combination of criteria I am having problems dynamically building the WHERE clause of the SELECT statement.

The StartDate can be populated singularly without an EndDate so the user is asking just for one day of information or they can ask for a date range by populating both fields. They can also populate any of the other criteria.

Does anyone have any ideas how I can dynamically cycle thru all the fields and build a WHERE clause that incorporates all the fields as applicable?

The form has fields startdate, enddate, IDNumber, Name.

I have assigned all the fields in this manner:

$sdate = $POST['startdate'];
$edate = $POST['enddate'];
$idnum = $POST['IDNumber'];
$name = $POST['Name'];

For example if the user populates startdate and IDNumber then the WHERE clause would only reference those two fields..

I hope I am explaining it correctly, I am quite new to this

ANY HELP would be greatly appreciated!!!

So you're looking for a query that more or less says you're looking for information between the start and end dates, but if you only have four fields, what are the Crit1, Crit2, Crit3? Are they in another table? If you can give an example of what you're looking for I can probably help you.

djjjozsi

<?
//error_reporting(E_ERROR | E_PARSE);
error_reporting(E_ALL);
ini_set("display_errors", 1);

$startdate  =$_POST['startdate'];
$enddate  =$_POST['enddate'];
$IDNumber =$_POST['IDNumber'];
$Name   =$_POST['Name'];

function check_($value,$if_date)
{
global $$value;   

/* a value name gets with a string/value...
$val="name"; 
$name="1"; 
print $$val; // 1
*/

  if(isset($$value))
  { 
  if($if_date=='>')
  return("AND $value>'".$$value."' ");

  if($if_date=='<')
  return("AND $value<'".$$value."' ");

  if($if_date=='=')
  return("AND $value='".$$value."' ");
  }
}

if(empty($_POST))
$sql="SELECT * FROM table";  //if there is no form has sent
else
{
$sql="SELECT * FROM table WHERE ";
$sql.=check_("startdate","<");
$sql.=check_("enddate",">");
$sql.=check_("IDNumber","=");
$sql.=check_("Name","=");
/*  You can set here fields to be checked.  */
$sql=str_replace("WHERE AND","WHERE ",$sql); 

/* lets imagine if the first field is only set, and an AND clause 
has left in the sql query. this clean that.*/

print $sql;  //here comes the mysql query.
}

?>

ask if something is not clear.

ixalmida

Here is a "for dummies" method that doesn't require passing variables:

// Assuming you have a "submit" button at the end of your form, remove it...
array_pop($_POST);

// Create a new array of the remaining keys...
$keys = array_keys($_POST);

// Get the key count for use later...
$last_key = count($keys);

// Begin your query string...
$query = "SELECT * FROM table WHERE ";

// Add your query conditions...
$i = 1;
foreach($keys as $key)
  {
  // Get the original POST value...
  $value = $_POST[$key];

  // Determine the correct condition to add to your query string...
  select($key)
    {
    case "startdate":
      $query .= "$key > $value";
      break;
    case "enddate":
      $query .= "$key < $value";
      break;
    default:
      $query .= "$key = $value";
    }

  // Include an "AND" if we are not at the end of the string...
  if($i !== $last_key) $query .= " AND ";

  // Increment your counter...
  $i++;
  }

// Add any other query stuff (optional)...
$query .= " ORDER BY name ASC";

// Admire your finished query string...
echo $query;