multiple search/query options?

wmguk · March 15, 2008

hey,

Im trying to do a query on my db...

basically I have 4 fields

albumname, month, year, status

the options are generated in a dropdown from the entries in the database,

what i am trying to do is if someone selects albumname = album1 and month = Jan then display the results, however they might not select month,

basically there are 16 options for results, how would i make a query work like this?

also, in the WHERE is there a display all, ie like * ?

JD* · March 15, 2008

For this type of query, I would use some PHP to construct it, along the lines of:

$query = "SELECT * FROM database WHERE";
if($_POST['albumname'] != '') { $query.=" albumname=.'".$_POST['albumname']."'"; }
if($_POST['month'] != '') { $query.=" AND month=.'".$_POST['month']."'"; }
if($_POST['year'] != '') { $query.=" ABD year=.'".$_POST['year']."'"; }
if($_POST['status'] != '') { $query.=" AND status=.'".$_POST['status']."'"; }

$result = mysql_query($query) or die(mysql_error());

That should do it for you. You will have a problem with this if there is no album name (as you can't have "AND" as the first part of the query), but I don't have access to a server at the moment to construct and test a more dynamic query. If you're interested in that, send me a PM and I'll construct one when I have access again.

wmguk · March 15, 2008

hey, that looks fab!!

the only issue is that, yes I will have no album name more often than not, - for example I may want to see all items, Jan 2008, Delivered, - so i wouldnt specify an album name....

the database will always be fully populated, it is just going to be the search that allows different selections, I already have 5 queries running on this page, just to show some really basic things, but its the only way o could get it to work lol...

i love php

anyway, thanks for the script, if you do get a chance to have a play and see if you can make it work with no albumname, that would be brilliant

wmguk · March 15, 2008

how would i place your script in to my existing page of queries

im trying it out now

include "../scripts/connection.php"; 
if (!$conn) 
{ 
 die( 'Could not connect: ' . mysql_error() ); 
  }
mysql_select_db($db, $conn); 

$sql="SELECT DISTINCT (loginid), albumname FROM orders"; //GENERATES A DROPDOWN OF ALBUM NAMES
$sql2="SELECT DISTINCT DATE_FORMAT(orderdate, '%M') AS month, month(orderdate) AS monthnum FROM orders ORDER BY monthnum asc"; //GENERATES A DROPDOWN OF MONTHS IN USE
$sql3="SELECT DISTINCT YEAR(orderdate) AS year FROM orders ORDER BY orderdate asc"; //GENERATES A DROPDOWN OF YEARS IN USE
$sql4="SELECT DISTINCT (status) FROM orders ORDER BY status desc"; //GENERATES A DROPDOWN OF STATUS IN USE
$sql5="SELECT * FROM album WHERE login = '$albumname'";
$sql10="SELECT * FROM orders WHERE loginid = '$albumname' ORDER BY orderdate desc "; //THIS IS THE ONE I WAS TRYING TO WORK ON

$result=mysql_query($sql)or die( "<strong>Query Error</strong>: " . mysql_error() . "<br><strong>Query</strong>: $sql<br><br>" );
$result2=mysql_query($sql2)or die( "<strong>Query Error</strong>: " . mysql_error() . "<br><strong>Query</strong>: $sql2<br><br>" );
$result3=mysql_query($sql3)or die( "<strong>Query Error</strong>: " . mysql_error() . "<br><strong>Query</strong>: $sql3<br><br>" );
$result4=mysql_query($sql4)or die( "<strong>Query Error</strong>: " . mysql_error() . "<br><strong>Query</strong>: $sql4<br><br>" );
$result5=mysql_query($sql5)or die( "<strong>Query Error</strong>: " . mysql_error() . "<br><strong>Query</strong>: $sql5<br><br>" );
$result10=mysql_query($sql10)or die( "<strong>Query Error</strong>: " . mysql_error() . "<br><strong>Query</strong>: $sql10<br><br>" );

JD* · March 15, 2008

I'm assuming that you're going to be setting this up as a form, so it will take user input before showing the final query. I would wrap the whole page like this:

include "../scripts/connection.php"; 
if (!$conn) 
{ 
 die( 'Could not connect: ' . mysql_error() ); 
  }
mysql_select_db($db, $conn); 


if(isset($_POST['Submit'])){
$query = "SELECT * FROM database";
if(sizeof($_POST) > 1) { $query.=" WHERE"; }
for($i=0;$i<sizeof($_POST);$i++)
{
    if($_POST == "Submit") { continue(); }
    if(value($_POST[$i]) == '') { continue(); } else {
        $query.= " ".key($_POST[$i])." = '".value($_POST[$i])."'";
        if($i+1 < sizeof($_POST)) { $query.=" AND"; }
    }
}

echo $query;

// - Uncomment this after the query looks right: $result = mysql_query($query) or die(mysql_error());

} else {
$sql="SELECT DISTINCT (loginid), albumname FROM orders"; //GENERATES A DROPDOWN OF ALBUM NAMES
$sql2="SELECT DISTINCT DATE_FORMAT(orderdate, '%M') AS month, month(orderdate) AS monthnum FROM orders ORDER BY monthnum asc"; //GENERATES A DROPDOWN OF MONTHS IN USE
$sql3="SELECT DISTINCT YEAR(orderdate) AS year FROM orders ORDER BY orderdate asc"; //GENERATES A DROPDOWN OF YEARS IN USE
$sql4="SELECT DISTINCT (status) FROM orders ORDER BY status desc"; //GENERATES A DROPDOWN OF STATUS IN USE
$sql5="SELECT * FROM album WHERE login = '$albumname'";
$sql10="SELECT * FROM orders WHERE loginid = '$albumname' ORDER BY orderdate desc "; //THIS IS THE ONE I WAS TRYING TO WORK ON

$result=mysql_query($sql)or die( "<strong>Query Error</strong>: " . mysql_error() . "<br><strong>Query</strong>: $sql<br><br>" );
$result2=mysql_query($sql2)or die( "<strong>Query Error</strong>: " . mysql_error() . "<br><strong>Query</strong>: $sql2<br><br>" );
$result3=mysql_query($sql3)or die( "<strong>Query Error</strong>: " . mysql_error() . "<br><strong>Query</strong>: $sql3<br><br>" );
$result4=mysql_query($sql4)or die( "<strong>Query Error</strong>: " . mysql_error() . "<br><strong>Query</strong>: $sql4<br><br>" );
$result5=mysql_query($sql5)or die( "<strong>Query Error</strong>: " . mysql_error() . "<br><strong>Query</strong>: $sql5<br><br>" );
$result10=mysql_query($sql10)or die( "<strong>Query Error</strong>: " . mysql_error() . "<br><strong>Query</strong>: $sql10<br><br>" );

// form code goes here, ending with a submit button called "Submit"
}

I updated the code with the dynamic query...it should search your $_POST variable (that comes from the form) and will add the field name and it's value, providing that the field value is anything other than '' and it will also keep putting in an "AND" until we reach the end of the $_POST variable. At the end, it will echo out the query, so we can make sure it does it correctly.

Again, I didn't get a chance to test this, so it may not work at all, but give it a shot and see what happens.

wmguk · March 15, 2008

hey,

sorry to say i get an error, just a completly white page, tried inserting it over the existing code, and tried a new page, both give blank white page

any thoughts?

wmguk · March 15, 2008

CURRENT PAGE

<?php
//VERY TOP OF PAGE
session_start(); 
$t = date("h:i:s", time()); 
$_SESSION['admin_login_time'] = $t; 
$user = $_SESSION['myusername']; 
if(!session_is_registered(myusername)){ 
header("location:index.php"); 
} 
$login = $_POST['id'];
$month = $_POST['month'];
$year = $_POST['year'];
$status = $_POST['status'];
$albumname = $_POST['albumname'];

?>

OTHER HTML HERE

then this:

<? echo $albumname ; 
include "../scripts/connection.php"; 
if (!$conn) 
{ 
  die( 'Could not connect: ' . mysql_error() ); 
   }
mysql_select_db($db, $conn); 

$sql="SELECT DISTINCT (loginid), albumname FROM orders";
$sql2="SELECT DISTINCT DATE_FORMAT(orderdate, '%M') AS month, month(orderdate) AS monthnum FROM orders ORDER BY monthnum asc";
$sql3="SELECT DISTINCT YEAR(orderdate) AS year FROM orders ORDER BY orderdate asc"; 
$sql4="SELECT DISTINCT (status) FROM orders ORDER BY status desc"; 
$sql5="SELECT * FROM album WHERE login = '$albumname'";
//$sql10="SELECT * FROM orders WHERE loginid = '$albumname' ORDER BY orderdate desc ";
$sql11 = "SELECT * FROM orders WHERE";
if($_POST['albumname'] != '') { $sql11.=" albumname=.'".$_POST['albumname']."'"; }
if($_POST['month'] != '') { $sql11.=" AND month=.'".$_POST['month']."'"; }
if($_POST['year'] != '') { $sql11.=" AND year=.'".$_POST['year']."'"; }
if($_POST['status'] != '') { $sql1.=" AND status=.'".$_POST['status']."'"; }

$result=mysql_query($sql)or die( "<strong>Query Error:</strong>: " . mysql_error() . "<br><strong>Query</strong>: $sql<br><br>" );
$result2=mysql_query($sql2)or die( "<strong>Query Error:2</strong>: " . mysql_error() . "<br><strong>Query</strong>: $sql2<br><br>" );
$result3=mysql_query($sql3)or die( "<strong>Query Error:3</strong>: " . mysql_error() . "<br><strong>Query</strong>: $sql3<br><br>" );
$result4=mysql_query($sql4)or die( "<strong>Query Error:4</strong>: " . mysql_error() . "<br><strong>Query</strong>: $sql4<br><br>" );
$result5=mysql_query($sql5)or die( "<strong>Query Error:5</strong>: " . mysql_error() . "<br><strong>Query</strong>: $sql5<br><br>" );
//$result6=mysql_query($sql6)or die( "<strong>Query Error:10</strong>: " . mysql_error() . "<br><strong>Query</strong>: $sql6<br><br>" );
$resul11=mysql_query($sql11)or die( "<strong>Query Error:11</strong>: " . mysql_error() . "<br><strong>Query</strong>: $sql11<br><br>" );

//ALBUMS
$options="";
while ($row=mysql_fetch_array($result)) { 
    $id=$row["loginid"]; 
    $thing=$row["albumname"]; 
    $options.="<OPTION VALUE=\"$id\">".$thing.'</option>';
} 

//MONTHS
$options2=""; 
while ($row=mysql_fetch_array($result2)) { 
    $id2=$row["month"]; 
    $thing2=$row["month"]; 
    $options2.="<OPTION VALUE=\"$id2\">".$thing2.'</option>';
}

//YEARS
$options3=""; 
while ($row=mysql_fetch_array($result3)) { 
$id3=$row["year"]; 
    $thing3=$row["year"]; 
    $options3.="<OPTION VALUE=\"$id3\">".$thing3.'</option>';
}
//STATUS
$options4=""; 
while ($row=mysql_fetch_array($result4)) { 
$id4=$row["status"]; 
    $thing4=$row["status"]; 
    $options4.="<OPTION VALUE=\"$id4\">".$thing4.'</option>';
}
//GET ALBUM NAME
$options5=""; 
while ($row=mysql_fetch_array($result5)) { 
$id5=$row["login"]; 
    $thing5=$row["albumname"];
}
?> 
          <br />
          <form action="searchresults.php" class="main" name="searchtype" method="post" >
<table width="100%" border="0" cellspacing="3" cellpadding="0">
  <tr>
    <td colspan="2" class="header"><img src="../images/heading/order_current.gif" alt="" /></td>
  </tr>
  <tr>
    <td colspan="2" class="header">Please use the search options below to view your orders</td>
    </tr>
  <tr>
    <td> </td>
    <td><div align="left"><span class="style1"></span></div></td>
  </tr>
  <tr>
    <td><div align="right">Search for orders from :</div></td>
    <td>
      
        <div align="left" class="style1">
          <select name="albumname" class="main" id="albumname">
            <option value="">All Your Albums
              <?=$options;?>
              </option>
          </select>
        </div></td>
  </tr>
  <tr>
    <td><div align="right">During the Month of :</div></td>
    <td>
      
        <div align="left" class="style1">
          <select name="month" class="main" id="month">
            <option value=''>All Months
              <?=$options2;?>
              </option>
          </select>
        </div></td>
  </tr>
  <tr>
    <td><div align="right">And the Year of :</div></td>
    <td>
        <div align="left" class="style1">
          <select name="year" class="main" id="year">
            <option value=''>All Years
              <?=$options3;?>
              </option>
          </select>
        </div></td>
  </tr>
  <tr>
    <td><div align="right">With Delivery status of :</div></td>
    <td>
       <div align="left" class="style1">
          <select name="status" class="main" id="status">
            <option value=''>All Delivery Status
              <?=$options4;?>
              </option>
          </select>
        </div></td>
  </tr>
  <tr>
    <td> </td>
    <td><div align="right">
      <input name="submit" type="submit" class="main" id="submit" value="View Orders" />
    </div></td>
  </tr>
  <tr>
    <td>Displaying Orders From :</td>
    <td><?  if ($albumname == '' )
		{
		$albumname = 'All Albums' ;
		}
		else 
		{
		$albumname = $thing5 ; 
		}
		echo "$albumname" ;?></td>
  </tr>
  <tr>
    <td>For the Period of :</td>
    <td><?
   //SET MONTH
	    if ($month == '' ){
		$month = 'All Months' ;
		} else {
		$month = $month ; 
		}
	//SET YEAR	
		if ($year == '' ){
		$year = 'All Years' ;
		} else {
		$year = $year ;  }
		echo ("$month in $year") ;?>
            
  </td>
  </tr>
  <tr>
    <td>Using the Delivery Status of :</td>
    <td><? if ($status == '' ){
		$status = 'All Status' ;
		} else {
		$status = $status ; 
		}
		echo "$status" ;?></td>
  </tr>
</table>
          </FORM>

<p><img src='../images/heading/allorders.gif'></p>
      <table width="95%" border='1' align="center" cellpadding='5' cellspacing='0' bordercolor='cccccc'> 
  <tr> 
  <th align='center' bgcolor='#CCCCCC' class='main'><font color = '000000'>Date & Time<br />
  of Order</font></th> 
  <th align='center' bgcolor='#CCCCCC' class='main'><font color = '000000'>Order<br />
    Ref</font></th> 
  <th align='center' bgcolor='#CCCCCC' class='main'><font color = '000000'>Customer<br />
  Name</font></th>
  <th align='center' bgcolor='#CCCCCC' class='main'><font color = '000000'>Order<br />
  Description</font></th>
  <th align='center' bgcolor='#CCCCCC' class='main'><font color = '000000'>Order<br />
  Total</font></th>
  <th align='center' bgcolor='#CCCCCC' class='main'> </th>  
  </tr>
  <tr><?
while($row = mysql_fetch_array($result11)) 
{ 
$orderdate = $row['orderdate'];
$id = $row['id'];
$name_first = $row['name_first'];
$name_last = $row['name_last'];
$ordered_items = $row['ordered_items'];
$total = $row['total'];

$showdate = date('d M Y H:m', strtotime($row['orderdate']));
?>
  <td width="80" class='main'><? echo $showdate ;?></td>  
  <td width="37" class='main'><? echo $id; ?></td> 
  <td align='left' class='main'><? echo $name_first ;?> <? echo $name_last ;?></td>
  <td width="250" align='left' class='main'><? echo $ordered_items ;?></td>
  <td width="40" align='center' class='main'><? echo $total ?></td>
  <td width="55" align='center' class='main'>  
  <form name='orderdetail' action='detail.php' method='GET'>
  <input type='hidden' name='id' value='<? echo $id ;?>'>
  <input type='submit' class='main' name='Submit' value='Details' onclick='return CheckForm()' />
  </form></td>
</tr>
  <? }?> 
      </table>

wmguk · March 15, 2008

also it directs back to itself, so that the drop downs are always at the top of the page, allow easy changing of search variables.

wmguk · March 15, 2008

i just cannot get this to work doesnt matter what i do to it....

anyone see anything?

JD* · March 16, 2008

Please see my last post, it has a newer query build in it and it shows how to wrap the code in an if statement so that is shows the result only when your form has been submitted

wmguk · March 16, 2008

Please see my last post, it has a newer query build in it and it shows how to wrap the code in an if statement so that is shows the result only when your form has been submitted

Hi,

Sorry, I did try that code, but all i could get was a blank white page, so guessing thats a passing error, via php (something ive done) but just couldnt work out what I did wrong...

I'll have a go again

JD* · March 16, 2008

You can always add this to the top of your page:

<?php error_reporting(E_ALL ^ E_NOTICE); ?>

This will report any errors (I'm guessing error reporting is shut off if you're only seeing a blank page right now), and then you can remove it when you're done developing your site.

wmguk · March 17, 2008

i added that string to the top of my pages, but i still just get a blank page, with no error reported?

any ideas?

wmguk · March 18, 2008

any ideas guys? this has totally got me stumped

wmguk · March 18, 2008

any way i can do this query with my existing pages?

Sign In

multiple search/query options?

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information