wmguk Posted March 15, 2008 Share Posted 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 * ? Quote Link to comment Share on other sites More sharing options...
JD* Posted March 15, 2008 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
wmguk Posted March 15, 2008 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
wmguk Posted March 15, 2008 Author Share Posted 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>" ); Quote Link to comment Share on other sites More sharing options...
JD* Posted March 15, 2008 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
wmguk Posted March 15, 2008 Author Share Posted 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? Quote Link to comment Share on other sites More sharing options...
wmguk Posted March 15, 2008 Author Share Posted 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> Quote Link to comment Share on other sites More sharing options...
wmguk Posted March 15, 2008 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
wmguk Posted March 15, 2008 Author Share Posted March 15, 2008 i just cannot get this to work doesnt matter what i do to it.... anyone see anything? Quote Link to comment Share on other sites More sharing options...
JD* Posted March 16, 2008 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
wmguk Posted March 16, 2008 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
JD* Posted March 16, 2008 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
wmguk Posted March 17, 2008 Author Share Posted 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? Quote Link to comment Share on other sites More sharing options...
wmguk Posted March 18, 2008 Author Share Posted March 18, 2008 any ideas guys? this has totally got me stumped Quote Link to comment Share on other sites More sharing options...
wmguk Posted March 18, 2008 Author Share Posted March 18, 2008 any way i can do this query with my existing pages? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.