variable queries

[nickelus]

Ive been wondering how this is done. Suppose you have three possible POST fields but you don't want to use all three in your query if not set. i would guess set a variable for each of the partial statements to be printed in the query if POST field isset but i cant get it to work

[Maq]

Can you post your relevant code?

[nickelus]

something like this...

if(isset($_POST['submit'])&&($_POST['submit']=="Search")){
$driver=$_POST['driver'];$driver.="`driver` LIKE '%$driver%'";
$sDate=$_POST['sDate'];$sDate.="`pu_date` >= '$sDate%'";
$eDate=$_POST['eDate'];$eDate.="AND `pu_date` <= '$eDate%' ";

$pu_qry="SELECT *
FROM `pu_sched`
WHERE '$sDate','$eDate','$driver'";
mysql_select_db($database_bio);
$pu_rpt=mysql_query($pu_qry);

[nickelus]

i am very interested in this theory as it involves combining functions

[TeNDoLLA]

Might not be the best way, and I didnt test it but probably it gives you some ideas unless it doesn't work straight away..

<?php
$where = '';
$flag = false; // Indicates if there was values in where already.
if(isset($_POST['submit'])&&($_POST['submit']=="Search"))
{
if (!empty($_POST['driver']))
{
	$driver = $_POST['driver'];
	$where = "WHERE `driver` LIKE '%$driver%'"
	$flag = true;
}
if (!empty($_POST['sDate']))
{
	if ($flag)
	{
		$sDate = $_POST['sDate'];
		$where = " AND `pu_date` >= '%$sDate%'"
	}
	else
	{
		$sDate = $_POST['sDate'];
		$where = "WHERE `pu_date` >= '%$sDate%'"
		$flag = true;
	}
}
if (!empty($_POST['eDate']))
{
	if ($flag)
	{
		$eDate = $_POST['eDate'];
		$where = " AND `pu_date` <= '%$eDate%'"
	}
	else
	{
		$eDate = $_POST['eDate'];
		$where = "WHERE `driver` <= '%$eDate%'"
	}
}
}
$pu_qry="SELECT * FROM `pu_sched` $where";
mysql_select_db($database_bio);
$pu_rpt=mysql_query($pu_qry);

[nickelus]

i had to look at this a few times to get it, its great concept i am trying it out with combos of two out of three POST fields and it should work fine

[roopurt18]

I usually use something like this:

 

<?php
$wheres = array();  // Assume we have no wheres
$driver = getPostValue( 'driver' );
$sdate = getPostValue( 'sdate' );
$edate = getPostValue( 'edate' );
if( $driver !== null ) {
  $driver = mysql_real_escape_string( $driver );
  $wheres[] = "`driver`='{$driver}'";
}
if( $sdate !== null ) {
  $sdate = strtotime( $sdate ); // Using strtotime gives the user more flexibility in their date formats
  if( $sdate !== false ) {
    $sdate = mysql_real_escape_string( date( 'Y-m-d H:i:s', $sdate ) );
    $wheres[] = "`pu_date`>='{$sdate}'";
  }
}
if( $edate !== null ) {
  $edate = strtotime( $edate );
  if( $edate !== false ) {
    $edate = mysql_real_escape_string( date( 'Y-m-d H:i:s', $edate ) );
    $wheres[] = "`pu_date`<='{$edate}'";
  }
}
$wheres = count( $wheres ) > 0 ? (' where ' . implode( ' and ', $wheres ) . ' ') : ' ';
$sql = "select * from `your_table` {$wheres}";
$q = mysql_query( $sql );
// you get the idea

function getPostValue( $name, $default = null ) {
  return isset( $_POST[$name] ) ? $_POST[$name] : $default;
}
?>

 

And combining functions isn't any type of theory.  8P

[TeNDoLLA]

Indeed using implode and functions will get rid of that flag and make it more readable. Still you should not use isset but empty instead if you don't want to search for empty values which is same as no search at all.

[roopurt18]

Could always change:

function getPostValue( $name, $default = null ) {
  return isset( $_POST[$name] ) ? $_POST[$name] : $default;
}

// TO
function getPostValue( $name, $default = null ) {
  return isset( $_POST[$name] ) && strlen( $_POST[$name] ) ? $_POST[$name] : $default;
}

 

Depends on your purpose.

[TeNDoLLA]

Yes but, empty() already checks if the variable is set also. So that would be useless extra code.

 

Edit:

Just noticed this actually was about search. So it might be better off checking together with strlen&isset instead empty because if you want to search with value of '0' empty will consider it also as empty.

[roopurt18]

I knew I had a good reason for doing it that way; I had forgotten what it was though.

[nickelus]

correct i was getting back results for null rows in the column