Query with PHP: is this possible?
Development and Programming
Attached is the screen shot of the type of search I am looking to do, yet I have been very unsuccessful at getting it to work? I have the pull down menu working correctly but I would like to get the keywords working yet, either the query is wrong or its not possible. I am guessing its the former?
I am using the query module, below is the code I am using on the results page after passing the variables over:
<pre><code>{exp:parameters parse="inward"}
{exp:query sql="SELECT DISTINCT exp_weblog_data.entry_id, exp_weblog_titles.title, exp_weblog_titles.url_title, exp_weblog_data.field_id_134, exp_weblog_data.field_id_135, exp_weblog_data.field_id_136, exp_weblog_data.field_id_137, exp_weblog_data.field_id_138, exp_weblog_data.field_id_139, exp_weblog_data.field_id_142 FROM exp_weblog_data, exp_weblog_titles WHERE field_id_134 LIKE '%{get_keywords}%’ AND field_id_142 LIKE ‘%{get_category}%’ AND field_id_137 LIKE ‘%{get_keywords}%’ AND title LIKE ‘%{get_keywords}%’ AND exp_weblog_data.entry_id = exp_weblog_titles.entry_id AND exp_weblog_data.weblog_id = 20;”}
{exp:weblog:entries weblog="publications" limit="1"}
<div class="grid_10">
<h5>{title}</h5>
{field_id_134} - {field_id_135}
{exp:ed_text:truncate end_url="{homepage}/media/publicationsdetail/{url_title}" end_text="( Read in full ...)" char_limit="350"}
{field_id_137}
{/exp:ed_text:truncate}
</div>
<hr>
{if title==""}No results matched your query.{/if}
{/exp:weblog:entries}
{/exp:query}
{/exp:parameters}
Oh, and here is the search box code:
<form method="get" action="{path=media/results}">
<fieldset class="fieldset">
<div class="grid_4">
Categories:
<select name='category'>
<option value="">Select Category</option>
<option name="category" value="Archaeology">Archaeology</option>
<option name="category" value="Biology">Biology</option>
<option name="category" value="Aquatic Biology">Aquatic Biology</option>
<option name="category" value="Terrestrial Biology">Terrestrial Biology</option>
<option name="category" value="Historic Preservation">Historic Preservation</option>
<option name="category" value="Water Resources">Water Resources</option>
<option name="category" value="Karst, Caves, and Geology">Karst, Caves, and Geology</option>
<option name="category" value="Biological Science">Biological Science</option>
<option name="category" value="Remote Sensing">Remote Sensing</option>
</select>
</div>
<div class="grid_1">
</div>
<div class="grid_4">
Search by Keyword:
<input type="text" class="input" maxlength="100" size="30" name="keywords" value="" />
</div>
<div class="grid_1">
<div class='searchSubmit'>
<input type='submit' value='Search' class='submit' />
</div>
</div>
</fieldset>
</form>[/code]
Try an OR with your search, unlikely for all ANDS?
I tried that but when I do it tends to break the page.
Try this for the query:
SELECT DISTINCT exp_weblog_data.entry_id, exp_weblog_titles.title, exp_weblog_titles.url_title, exp_weblog_data.field_id_134, exp_weblog_data.field_id_135, exp_weblog_data.field_id_136, exp_weblog_data.field_id_137, exp_weblog_data.field_id_138, exp_weblog_data.field_id_139, exp_weblog_data.field_id_142 FROM `exp_weblog_data`, `exp_weblog_titles` WHERE `exp_weblog_data.field_id_134` LIKE '%{get_keywords}%' AND `exp_weblog_data.field_id_142` LIKE '%{get_category}%' AND `exp_weblog_data.field_id_137` LIKE '%{get_keywords}%' AND `exp_weblog_titles.title` LIKE '%{get_keywords}%' AND exp_weblog_data.entry_id = exp_weblog_titles.entry_id AND exp_weblog_data.weblog_id = 20Are there any error messages or just no results?
Just get this error message:
MySQL ERROR:
Error Number: 1054
Description: Unknown column ‘exp_weblog_data.field_id_134’ in ‘where clause’
Query: SELECT DISTINCT exp_weblog_data.entry_id, exp_weblog_titles.title, exp_weblog_titles.url_title, exp_weblog_data.field_id_134, exp_weblog_data.field_id_135, exp_weblog_data.field_id_136, exp_weblog_data.field_id_137, exp_weblog_data.field_id_138, exp_weblog_data.field_id_139, exp_weblog_data.field_id_142 FROM exp_weblog_data, exp_weblog_titles WHERE exp_weblog_data.field_id_134 LIKE ‘%%’ AND exp_weblog_data.field_id_142 LIKE ‘%Remote Sensing%’ AND exp_weblog_data.field_id_137 LIKE ‘%%’ AND exp_weblog_titles.title LIKE ‘%%’ AND exp_weblog_data.entry_id = exp_weblog_titles.entry_id AND exp_weblog_data.weblog_id = 20;
Try this query.
SELECT DISTINCT(exp_weblog_data.entry_id), exp_weblog_titles.title, exp_weblog_titles.url_title, exp_weblog_data.field_id_134, exp_weblog_data.field_id_135, exp_weblog_data.field_id_136, exp_weblog_data.field_id_137, exp_weblog_data.field_id_138, exp_weblog_data.field_id_139, exp_weblog_data.field_id_142 FROM exp_weblog_data, exp_weblog_titles WHERE exp_weblog_data.field_id_134 LIKE '%{get_keywords}%' AND exp_weblog_data.field_id_142 LIKE '%{get_category}%' AND exp_weblog_data.field_id_137 LIKE '%{get_keywords}%' AND exp_weblog_titles.title LIKE '%{get_keywords}%' AND exp_weblog_data.entry_id = exp_weblog_titles.entry_id AND exp_weblog_data.weblog_id = 20Sorry overlooked the backticks.
I should have noticed that, my error.
This gets no results from the open field, the pull down menu continues to work when nothing is typed into the input field? Not sure what I am missing, seems straight forward?
The query is probably to restrictive with the ANDs try this:
SELECT DISTINCT(exp_weblog_data.entry_id), exp_weblog_titles.title, exp_weblog_titles.url_title, exp_weblog_data.field_id_134, exp_weblog_data.field_id_135, exp_weblog_data.field_id_136, exp_weblog_data.field_id_137, exp_weblog_data.field_id_138, exp_weblog_data.field_id_139, exp_weblog_data.field_id_142 FROM exp_weblog_data, exp_weblog_titles WHERE (exp_weblog_data.field_id_134 LIKE '%{get_keywords}%' OR exp_weblog_data.field_id_142 LIKE '%{get_category}%' OR exp_weblog_data.field_id_137 LIKE '%{get_keywords}%' OR exp_weblog_titles.title LIKE '%{get_keywords}%') AND exp_weblog_data.entry_id = exp_weblog_titles.entry_id AND exp_weblog_data.weblog_id = 20The query is probably to restrictive with the ANDs try this:SELECT DISTINCT(exp_weblog_data.entry_id), exp_weblog_titles.title, exp_weblog_titles.url_title, exp_weblog_data.field_id_134, exp_weblog_data.field_id_135, exp_weblog_data.field_id_136, exp_weblog_data.field_id_137, exp_weblog_data.field_id_138, exp_weblog_data.field_id_139, exp_weblog_data.field_id_142 FROM exp_weblog_data, exp_weblog_titles WHERE (exp_weblog_data.field_id_134 LIKE '%{get_keywords}%' OR exp_weblog_data.field_id_142 LIKE '%{get_category}%' OR exp_weblog_data.field_id_137 LIKE '%{get_keywords}%' OR exp_weblog_titles.title LIKE '%{get_keywords}%') AND exp_weblog_data.entry_id = exp_weblog_titles.entry_id AND exp_weblog_data.weblog_id = 20
isn’t that what i said?
isn’t that what i said?
Yes, but there was another issue - the scoping of the field/column names with the table names.
When I run that script, I seem to get back results but it is the same results for anything I type in that field.
Even with the “and” and the “or”, I would think it could handle that? Feel like something else is missing? I am going to try and search for those keywords in just 2 fields of data instead of 4 and see if that works?
Any suggestions on this would greatly help, not sure what else to do?
When I narrowed the search down to one field and it worked well:
exp:query sql=”SELECT DISTINCT(exp_weblog_data.entry_id), exp_weblog_titles.title, exp_weblog_titles.url_title, exp_weblog_data.field_id_134, exp_weblog_data.field_id_135, exp_weblog_data.field_id_136, exp_weblog_data.field_id_137, exp_weblog_data.field_id_138, exp_weblog_data.field_id_139, exp_weblog_data.field_id_142 FROM exp_weblog_data, exp_weblog_titles WHERE exp_weblog_data.field_id_134 LIKE ‘%{get_keywords}%’ AND exp_weblog_data.entry_id = exp_weblog_titles.entry_id AND exp_weblog_data.weblog_id = 20;”
I need to add those other fields yet each time I do it stops working. Thoughts?