dennismonsewicz Posted December 4, 2007 Share Posted December 4, 2007 Below is the code: <div style="margin-left: 25px;"> <form name="form" action="advanced_search.php" method="post"> <input type="text" name="search1" /><br /> <input type="text" name="search2" /> <input type="submit" name="Submit" value="Search" /> </form> </div> <?php $search1 = @$_POST['search1']; $search2 = @$_POST['search2']; $trimmed1 = trim($search1); //trim whitespace from the stored variables $trimmed2 = trim($search2); /* // PHP Code that is used to write to a text file. Is not working right now, so is down at the moment $myFile = "results.txt"; $fh = fopen($myFile, 'a') or die("can't open file"); $stringData = $trimmed; fwrite($fh, $stringData); */ // rows to return $limit=9999; // check for an empty string and display a message. if ($trimmed1 && $trimmed2 == "") { echo "<p class='contenttext'>Please enter a search...</p>"; include "../includes/footer.php"; exit; } // check for a search parameter if (!isset($search1)) { echo "<p class='contenttext'>We dont seem to have a search parameter!</p>"; include "../includes/footer.php"; exit; } else if(!isset($search2)) { echo "<p class='contenttext'>We don't seem to have a search parameter! Both Fields must be filled out!</p>"; include "../includes/footer.php"; exit; } //connect to your database ** EDIT REQUIRED HERE ** mysql_connect("localhost","root","hanceville"); //(host, username, password) //specify database ** EDIT REQUIRED HERE ** mysql_select_db("hrmedia_results") or die("Unable to select database"); //select which database we're using // Build SQL Query $query = "(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from arcamax where '$search1' between '$search2') union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from google where '$search1' between '$search2') union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from drudge where '$search1' between '$search2') union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from human_events where '$search1' between '$search2') union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from newsmax where '$search1' between '$search2') union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from street where '$search1' between '$search2') union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from townhall where '$search1' between '$search2') union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from weatherbug where '$search1' between '$search2') union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from worldnet where '$search1' between '$search2') order by mailedlist"; $numresults=mysql_query($query); $numrows=mysql_num_rows($numresults); // If we have no results, offer a google search as an alternative if ($numrows == 0) { echo "<h4 class='contenttext'>Results</h4>"; echo "<p class='contenttext'>Sorry, your search: <span class='yellowhighlight'> <b>" . $trimmed1 . "</b> & <b>" . $trimmed2 . "</b> </span> returned zero results</p>"; /*// google echo "<p><a href=\"http://www.google.com/search?q=" . $trimmed . "\" target=\"_blank\" title=\"Look up " . $trimmed . " on Google\">Click here</a> to try the search on google</p>";*/ include "../includes/footer.php"; exit; } // next determine if s has been passed to script, if not use 0 if (empty($s)) { $s=0; } // get results $query .= " limit $s,$limit"; $result = mysql_query($query) or die("Couldn't execute query"); // display what the person searched for echo "<p class='contenttext'>You searched for: <b>" . $trimmed1 . "</b> & <b>" . $trimmed1 . "</p>"; // begin to show results set echo "<p class='contenttext'>Results (Click a product below to view more information) <br /><br />"; $count = 1 + $s ; // now you can display the results returned while ($row= mysql_fetch_array($result)) { $rowid = $row["id"]; $promo_code = $row["promo_code"]; $start_end_date = $row["start_end_date"]; $description = $row["description"]; $vendor = $row["mailedlist"]; $orders = $row["orders"]; $totalrevenue = $row["totalrevenue"]; echo "$count.) " . $promo_code . "<br />"; echo "Start/End Date: " . $start_end_date . "<br />"; echo "Vendor: " . $vendor . "<br />"; echo "Description: " . $description . "<br />"; echo "Orders: " . $orders . "<br />"; echo "Total Revenue: " . $totalrevenue . "<br />"; echo "<a href='../media/mediacase.php?action=view&id=" . $rowid . "&db=" . $vendor . "'>View More</a> <br /><br />"; $count++ ; } ?> For some reason it is not able to search the databases. Anyone got any ideas? Quote Link to comment https://forums.phpfreaks.com/topic/80152-solved-double-search-field-not-working/ Share on other sites More sharing options...
pocobueno1388 Posted December 4, 2007 Share Posted December 4, 2007 Okay, try changing these lines <?php $query = "(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from arcamax where '$search1' between '$search2') union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from google where '$search1' between '$search2') union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from drudge where '$search1' between '$search2') union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from human_events where '$search1' between '$search2') union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from newsmax where '$search1' between '$search2') union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from street where '$search1' between '$search2') union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from townhall where '$search1' between '$search2') union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from weatherbug where '$search1' between '$search2') union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from worldnet where '$search1' between '$search2') order by mailedlist"; $numresults=mysql_query($query); ?> To <?php $query = "(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from arcamax where '$search1' between '$search2') union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from google where '$search1' between '$search2') union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from drudge where '$search1' between '$search2') union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from human_events where '$search1' between '$search2') union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from newsmax where '$search1' between '$search2') union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from street where '$search1' between '$search2') union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from townhall where '$search1' between '$search2') union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from weatherbug where '$search1' between '$search2') union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from worldnet where '$search1' between '$search2') order by mailedlist"; $numresults=mysql_query($query)or die(mysql_error()." With query $query"); echo "<p>$query<p>"; ?> Show us what it gives you. ?> Quote Link to comment https://forums.phpfreaks.com/topic/80152-solved-double-search-field-not-working/#findComment-406302 Share on other sites More sharing options...
dennismonsewicz Posted December 4, 2007 Author Share Posted December 4, 2007 this is what i got: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') union (select id,promo_code,start_end_date,mailedlist,description,orders,tota' at line 1 With query (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from arcamax where 'advanced' between 'advanced') union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from google where 'advanced' between 'advanced') union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from drudge where 'advanced' between 'advanced') union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from human_events where 'advanced' between 'advanced') union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from newsmax where 'advanced' between 'advanced') union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from street where 'advanced' between 'advanced') union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from townhall where 'advanced' between 'advanced') union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from weatherbug where 'advanced' between 'advanced') union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from worldnet where 'advanced' between 'advanced') order by mailedlist Quote Link to comment https://forums.phpfreaks.com/topic/80152-solved-double-search-field-not-working/#findComment-406314 Share on other sites More sharing options...
pocobueno1388 Posted December 4, 2007 Share Posted December 4, 2007 It's printing out where 'advanced' between 'advanced' Advanced? I thought the variables held dates? Now I'm confused on what your even trying to do...explain what your trying to select from the database. Quote Link to comment https://forums.phpfreaks.com/topic/80152-solved-double-search-field-not-working/#findComment-406328 Share on other sites More sharing options...
dennismonsewicz Posted December 4, 2007 Author Share Posted December 4, 2007 Sorry It is supposed to be a date range. I just reloaded the page without thinking. Sorry. I tried another one and it does the same thing when I insert a date. Example: 1/1/04 Quote Link to comment https://forums.phpfreaks.com/topic/80152-solved-double-search-field-not-working/#findComment-406331 Share on other sites More sharing options...
pocobueno1388 Posted December 4, 2007 Share Posted December 4, 2007 I'm not positive, but I think your date format has to be yyyy-mm-dd. I'm going to have to come back to this thread later on today, I have to go to work. Quote Link to comment https://forums.phpfreaks.com/topic/80152-solved-double-search-field-not-working/#findComment-406335 Share on other sites More sharing options...
pocobueno1388 Posted December 5, 2007 Share Posted December 5, 2007 ok... for my start and end dat it is in one field as follows: start_end_date (Varchar 255) So I need to break that field up into the follow: start (Varchar 255) end (Varchar 255) year (Varchar 255) ?? Okay...I think you need to explain exactly what your doing. Also, post your database structure. I thought you had a table with a date in it (not a start AND end date for each row) and you wanted to be able to search through that table for any records with a date in between two given dates. Quote Link to comment https://forums.phpfreaks.com/topic/80152-solved-double-search-field-not-working/#findComment-407095 Share on other sites More sharing options...
dennismonsewicz Posted December 5, 2007 Author Share Posted December 5, 2007 id int(11) promo_code varchar(255) start_end_date varchar(255) mailedlist varchar(255) description varchar(255) subject text mailqty varchar(255) actualqty varchar(255) netdelivered varchar(255) totalcost varchar(255) delivered varchar(255) uniqueopen varchar(255) percentopen varchar(255) clicks varchar(255) percentclicks varchar(255) leads varchar(255) clicklead varchar(255) orders varchar(255) orderconversion varchar(255) totalrevenue varchar(255) avgsale varchar(255) cpm varchar(255) roi varchar(255) netrevenue varchar(255) The bolded items above are the row names. I am wanting to do a search between two dates and display all the results between those two dates. If I am completely off the wall with any of this please let me know. My PHP work is getting a lot better but I haven't studied table structure that well (as you can probably tell from the above structure) Quote Link to comment https://forums.phpfreaks.com/topic/80152-solved-double-search-field-not-working/#findComment-407106 Share on other sites More sharing options...
pocobueno1388 Posted December 5, 2007 Share Posted December 5, 2007 Okay, give me an example of what data would look like in "start_end_date". Quote Link to comment https://forums.phpfreaks.com/topic/80152-solved-double-search-field-not-working/#findComment-407118 Share on other sites More sharing options...
dennismonsewicz Posted December 5, 2007 Author Share Posted December 5, 2007 16-Aug, 4-Sep, 30-July some are like that and some are like 3/17/2007- 3/30 Quote Link to comment https://forums.phpfreaks.com/topic/80152-solved-double-search-field-not-working/#findComment-407128 Share on other sites More sharing options...
pocobueno1388 Posted December 5, 2007 Share Posted December 5, 2007 Okay, I would definitely separate the start and end date into two different fields, and make them in the format of yyyy-mm-dd. make them the "date" type in the DB. Once you do that, then we can move on to the next step. Quote Link to comment https://forums.phpfreaks.com/topic/80152-solved-double-search-field-not-working/#findComment-407137 Share on other sites More sharing options...
dennismonsewicz Posted December 5, 2007 Author Share Posted December 5, 2007 Gotcha... That is going to take a while. so Let me talk with a data entry person and be right back with you. Thanks for all your help up to this point. Quote Link to comment https://forums.phpfreaks.com/topic/80152-solved-double-search-field-not-working/#findComment-407138 Share on other sites More sharing options...
dennismonsewicz Posted December 5, 2007 Author Share Posted December 5, 2007 I figured out how to do it. I used IN instead of BETWEEN so my code looks like this: $query = "(select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from arcamax where start_end_date in ('$search1', '$search2')) union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from google where start_end_date in ('$search1', '$search2')) union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from drudge where start_end_date in ('$search1', '$search2')) union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from human_events where start_end_date in ('$search1', '$search2')) union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from newsmax where start_end_date in ('$search1', '$search2')) union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from street where start_end_date in ('$search1', '$search2')) union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from townhall where start_end_date in ('$search1', '$search2')) union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from weatherbug where start_end_date in ('$search1', '$search2')) union (select id,promo_code,start_end_date,mailedlist,description,orders,totalrevenue from worldnet where start_end_date in ('$search1', '$search2')) order by id"; So problem solved thanks Quote Link to comment https://forums.phpfreaks.com/topic/80152-solved-double-search-field-not-working/#findComment-407207 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.