double entry php query

Hi
I want to query a database with the user having the option to enter their own search text or choose from a drop down list.

Firstly they have to choose to search by Company or Product.

I find that if I use the same ‘name’ for both the field and the list then it wont work.

This is the code I have so far

<?php
// create short variable names
$searchtype=$HTTP_POST_VARS['searchtype'];
$searchterm=$HTTP_POST_VARS['searchterm'];

$searchterm= trim($searchterm);

if (!$searchtype || !$searchterm)
{
echo ' <p class="style45"><span class="body12V">Please enter your search details.';
exit;
}

$searchtype = addslashes($searchtype);
$searchterm = addslashes($searchterm);

@ $db = mysql_pconnect('localhost', '****', '****');

if (!$db)
{
echo 'Error: Could not connect to database.  Please try again later.';
exit;
}

mysql_select_db('*****');
$result = mysql_query("SELECT * FROM sttasources",$db);

$query = "select * from sttasources where ".$searchtype." like '%".$searchterm."%'";
$result = mysql_query($query);

$num_results = mysql_num_rows($result);
echo '<p class="style45"><span class="body12V">Number of Members found: '.$num_results.'</span></p>'; 

searchtype is the choice between Company or Product in a drop down.

searchterm is the name for both the field and the list.

How do I change this code to do what I want.

This is the page in question

http://www.stta.org.uk/testsite/membersearch.php

Which brings me to my second problem with the page.

Because of the code:

if (!$searchtype || !$searchterm)
{
echo ' <p class="style45"><span class="body12V">Please enter your search details.';
exit;
}

The page doesn’t load completely - how do I sort this.

Please be gentle coz my php skills are limited.

Thanks

David


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

As far as the page not finishing loading, that’s because of the exit statement. Comment that out and you should be fine on that front. The other part can be solved with javascript. You need to replace the picking list with a text field if the user picks “Other” from the list. Have a search on this site for “combo box”. I posted a recipe for this a month or so ago.

Walter


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

Thanks Walter I will do that and let you know how I get on.

David


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

Hi Walter

Had a good look for your previous posting re “combo box” and I couldn’t find it anywhere.

60 results including “combo”

Got to dash out just now but I will look again later.

Thanks


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

Sorry, I could swear I posted this here. Here’s what I do:

Make a regular combo box that includes an Other… option as the last
item.

$opts = ActiveRecord::FindDistinct('foo','bar');
foreach($opts as $o) $options[] = $o->bar;
$options[] = 'Other...';
$options_picker = SimpleListBox($options,'option');

There’s some magic there that comes out of my framework, but
basically any way you want to end up with a valid SELECT picker that
has an ID (‘option’, in this example) is fine.

Now, in your page template, make sure you include Prototype.js and
set up the following unobtrusive listener (if you are using the
Protaculous Action, then put this in the dom:loaded slot for the
page, otherwise create a dom:loaded listener to wrap it):

$('option').observe('change',function(){
	if($F('option') == 'Other...'){
		var txt = '<input type="text" name="input" id="input" />';
		$('option').replace(txt);
	}
}

So what this does is watch the form element with the ID ‘option’ and
fires the function every time the change event happens. (This is
similar to using the inline onchange="…" notation, but keeps the
script out of the page body.) If the value of the form element equals
‘Other…’, then the picking list is replaced with a text input that
has the same name as the select list.

When the form is submitted, either there will be a select list
element named ‘option’ in the form OR there will be a text input
named ‘option’ in the form. They will not both be present at the same
time. So your form will be valid, and your form handler will only
need to check one input variable.

Now this violates the rules of Progressive Enhancement a little bit,
because this functionality only happens when JavaScript is enabled.
Non-scripted users will be able to select Other, but won’t get the
opportunity to explain what that means exactly. There’s a couple of
ways to manage this, depending on how many users you expect to come
to this form with scripting disabled.

One would be to add the Other… element to the select using
JavaScript inside of the same unobtrusive listener, before the if()
block.

Another would be to provide an Explain (if Other) field after the
picker, and deal with this on the server using this sort of logic:

$option = $_POST['option'];
if($option == 'Other...') $option = $_POST['explain_other'];

Then instead of adding the Other option using JavaScript, you would
remove the explain_other field using JavaScript:

$('explain_other').remove();

Either way, scripted visitors would get the extra love, and non-
scripted visitors would still be able to interact with your application.

Walter

On Mar 27, 2008, at 4:50 AM, DeltaDave wrote:

Hi Walter

Had a good look for your previous posting re “combo box” and I
couldn’t find it anywhere.

60 results including “combo”

Got to dash out just now but I will look again later.

Thanks


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

Hi Walter thanks for that but I am afraid that you lost me after

Make a regular combo box that includes an Other… option as the last item.

I did ask

Please be gentle

I couldn’t even get the initial page loading right because every way I tried to “comment out” the exit statement led me to an error

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/deltadzine/stta.org.uk/testsite/membersearch.php on line 218

Thanks for your patience

David


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

Sorry. Did you try just putting two slashes in front of it, or simply
deleting that line with the exit; on it?

WAlter

On Mar 27, 2008, at 8:32 PM, DeltaDave wrote:

I couldn’t even get the initial page loading right because every
way I tried to “comment out” the exit statement led me to an error


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

It sounds as though your issues start quite a bit before this page.
You may have some problems with your MySQL connection, it may be set
up for one server and not working on the one you’re testing on. This
error means that the query didn’t return anything, not even an empty
result set. Which usually means that the initial connection to the
database server failed.

Walter

On Mar 27, 2008, at 8:32 PM, DeltaDave wrote:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL
result resource in /home/deltadzine/stta.org.uk/testsite/
membersearch.php on line 218


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

Why do you have this line:

$result = mysql_query("SELECT * FROM sttasources",$db);

followed by

$query = "select * from sttasources where ".$searchtype." like '%".$searchterm."%'";
$result = mysql_query($query);

You shouldn’t need the first.

Anyway, I just looked at the page and it returns results when I do I search so i guess you have this sorted.


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

Yes Walter

I did put 2 slashes in front of

exit;

and also tried deleting it. But both give me the MySql error.

As Mike noted the search does work but when you initially go to the page the whole page doesn’t load because of the line.

exit; 

The other thing is the combo box that Walter talked about where the user has the option to choose from the drop down list or enter search terms in a field.

I am afraid that Walter’s description of how to do this part is way over my head.

Is this code added to the extended section of the drop down picker?

$opts = ActiveRecord::FindDistinct('foo','bar');
foreach($opts as $o) $options[] = $o->bar;
$options[] = 'Other...';
$options_picker = SimpleListBox($options,'option');

And I have had a look at the Prototype part and can’t see where that goes.

include Prototype.js and set up the following unobtrusive listener

Maybe I should just stick to the text entry field! Which is working OK if I can get the complete page to load on initial visit.

http://www.stta.org.uk/testsite/membersearch.php

Thanks for your help.

David


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

Dave,

First of all I find it really helped me at the start of learning php and working with databases to separate the connection from the actual search query code, put the connection stuff into a file and then use:

 include ('file_path_and_name_of_connection_script.php'); // Path to connection code

just before the query, now the connect lines are out off the script and thus in theory making it simpler to understand and less complicated to debug, each time you need to make a connection (if one is not open already) you just add the include line, it is also recommended to use a mysql_close($db); to close the connection after you are done although this will be closed for you if you don’t.

I am not sure I follow nor do I know how the script is used within or on your page but if I understand correctly maybe you should just include the query script if the person has made a search, so if the search button is called ‘search’ you could use:

<?php if ($_POST['search']) { include ('file_path_and_name_of_search+script.php'); // Path to search code } ?>

You can use this if you have the code on your page or in a script that is included you just need to alter what goes where the include line is, so now the query code and the exit will not load unless there is a search.

If you are going to do this stuff then it really does pay to buy a book and spend a bit of time in it, honestly, you will save time and not loose hair, well some but not as much :slight_smile:


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

Thanks Mike

I have the php as a markup item on the page so that the reults are served up into that markup. Does that make a difference to how I do this?

I have used the same code on other sites and I don’t have the same problem about incomplete page loading with them?

I do have a couple of ‘good’ books

PHP and MySQL Web Development (Luke Welling & Laura Thomson)

MySQL/PHP Database Applications (Greenspan & Bulger)

PHP5 and MySQL Bible (Tim Converse etc)

and others

Is there a particular one that you would recommend?


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

Hi again Mike

I used

<?php
 if ($_POST['search']) {
include ('file_path_and_name_of_search+script.php'); // Path to search code
}
?>

And that did the trick - Brilliant, my page completely loads and the script is only called when the ‘Search’ button is clicked.

Now I just need to fathom out Walter’s bit about the ‘Combo Box’

Thank you

David


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

On 28 Mar. 2008, 7:41 pm, DeltaDave wrote:

Thanks Mike

I have the php as a markup item on the page so that the reults are served up into that markup. Does that make a difference to how I do this?

No not really, depends on your preference I guess. I personally prefer to keep script code off the Freeway pages, I then include then where needed.

Reasons I do this are that I can then actually do a lot of work out of Freeway, Freeway has no syntax colouring and can’t tab lines (I really can’t express my disappointment that this is not available in FW4 never mind not being available in FW5!) and generally find database sites easier to organise and debug.

I have used the same code on other sites and I don’t have the same problem about incomplete page loading with them?

I do have a couple of ‘good’ books

PHP and MySQL Web Development (Luke Welling & Laura Thomson)

MySQL/PHP Database Applications (Greenspan & Bulger)

PHP5 and MySQL Bible (Tim Converse etc)

and others

Is there a particular one that you would recommend?

The first was my first and the best book buy on PHP and MySQL I ever bought.


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

Here’s some working, tested code, with Freeway 5 steps to create:

Apply Protaculous to the page. http://freewaypro.com/actions/downloads/

Choose prototype-packed from the Library picker, and click on the top Function Body button.

Paste the following:

$('picker').observe('change',function(){
    if($F('picker') == 'Other...'){
        $('picker').replace('<input type="text" id="picker" name="picker" />');
        $('picker').activate();
    }
});

Make a new picking list. Name it ‘picker’, and add several options to it. Make sure that the last option in the list has ‘Other…’ for both the Name and Value. With the picking list selected, choose Item > Extended from the main menu, and assign the ID ‘picker’ to the picking list. (Freeway doesn’t properly add IDs to form elements.)

Now preview into a browser. When you choose “Other…” from the list, the picker will disappear and be immediately replaced with a text field where you can enter your “unlisted” content. For bonus points, the text field is immediately “focused” so you don’t have to click on it before you begin typing.

Now the initial question in this thread had to do with using this trick in a database-driven picker. I use this pattern a lot to make it possible for users to add new categories (for example) but to discourage them from adding a slightly-misspelled version of an existing category. To get a list of existing categories, you could use this query:

SELECT DISTINCT(category) FROM mytable WHERE 1 ORDER BY category ASC;

Then you could build a picking list from the results of that query by iterating over them in a while or foreach loop.

if($result = mysql_query($sql,$connection){
    $list = '<select id="picker" name="picker" size="1">;
    while ( $row= mysql_select_object($result)){
        $text = $row->category;
        $list .= '<option label="' . $text . '" value="' . $text . '"';
        if(isset($picker) && $picker == $text) $list .= ' selected="selected"';
        $list .= '>' . $text . '</option>';
    }
    $list .= '</select>';
}

Now further down in your page, simply add the list you built, using echo or this shorthand:

<?=$list?>

The JavaScript will still do the trick we set it to do, and the PHP will dynamically build your list of options from the unique values in the database. As soon as someone enters and saves a new value, that choice will be possible for the next user of the application.

Walter


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

Double Brilliant!

Thanks Walter that is the business.

Now next question. Can I have more than one observer in there.

What I am thinking is this:

At the moment there is an initial selector (searchtype) to choose whether to search by Product, Company Name or Location (http://www.stta.org.uk/testsite/membersearch.php)

Can I use the observer on this selector to change the type of the picker from drop down to text insert?

Thanks again for your patience.

David


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

I have answered my own question.

I tried adding other observers to the initial selector (ID searchtype) and this works great changing the picker from drop down to text input as well.

But once the selector has changed from whatever to:

input type=“text”

Can I change it back if the user decides to go back to products to pick from the list?

I tried using

$('searchtype').observe('change',function(){
if($F('searchtype') == 'products'){
    $('searchterm').replace('<input type="select" id="searchterm" name="searchterm" />');
    $('searchterm').activate();
}

});

But this didn’t work - is it even possible to go back? Or have I got the wrong input type?

I have just added a Reset button which seems to work OK but it would be nice if it could automatically change back when reselecting products.

David


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

Sure. You can put as many observers in there as you want, and even
stack them on the same object with the same or a different event.

$(‘foo’).observe(‘click’…

$(‘foo’).observe(‘mouseover’…

$(‘foo’).observe(‘click’… //this will run at (or nearly at) the
same time as the first one)

Walter

On Mar 28, 2008, at 7:21 PM, DeltaDave wrote:

Double Brilliant!

Thanks Walter that is the business.

Now next question. Can I have more than one observer in there.

What I am thinking is this:

At the moment there is an initial selector (searchtype) to choose
whether to search by Product, Company Name or Location (http://
www.stta.org.uk/testsite/membersearch.php)

Can I use the observer on this selector to change the type of the
picker from drop down to text insert?

Thanks again for your patience.

David


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

You would need to re-create the entire select. A select looks like this:

<select name="foo" id="foo" size="1">
	<option label="Option 1" value="1">Option 1</option>
	...
</select>

You could try storing a reference to the original before you replace it:

window.backup = $('foo').replace('<input type="text" ...

And then later when you want to put it back, do this:

$('foo').replace(window.backup);

I haven’t tried that, but it might work. You might need to escape the
original to HTML before storing it, I don’t know if replace() works
with objects or whether it requires HTML.

Walter

On Mar 28, 2008, at 8:40 PM, DeltaDave wrote:

But this didn’t work - is it even possible to go back? Or have I
got the wrong input type?


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

Thanks Walter I will have a play with

window.backup = $('foo').replace('<input type=............

And I will let you know how I get on.

David


dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options