We help IT Professionals succeed at work.

Creating a search box in flash that returns results from a mySQL database.

sam928
sam928 asked
on
383 Views
Last Modified: 2013-12-12
hello to all genius' wizards and sages
----I have a full flash site, a real estate business, I display properties information, in a similar fashion to what you would see on www.realtor.com

----All data is stored in a mySQL database.

---------------- I would like to create a search function where the user can enter keywords/phrases for the data.  
Example of what a user might enter:  

'miami, florida' , (cities, state)
'new york, newyork'   (city,state)
'33139'  (zip codes)
'fifth avenue'  (street names)

------------  To keep this as simple as possible to keep on principle, I only am using 4 fields in the database.

1. Street Name and number
2. City
3. State
4. Zip code

So on the flash side, a final result  of a matching keyword would be

456 Oak Street
Miami, Florida
33139

(of course the final project will have more fields and etc. but keeping this simple, )


So i am trying to create a search function with flash/php that when a user enters a keyword , it will search the database and retrieve all properties that have that keyword that user input in the flash search box.  Then it will return the results back to flash on the same page.

----
Here is what i have so far.

on the flash stage I have:

1) an input text box, with an instance name of 'keyword'

2) a submit button with an instance name of 'submit_btn'

3) a dynamic text box (for the results to be returned) with an instance name of 'results'

~~~4) also i have the data in the mySQL already.  the fields are named the following:
            a)street
            b)city
            c)state
            d)zip code

------------------------------------
So basically all i need now is the PHP code that ties this all in and does the work.

I have searched all around for this on google and on this site but was unsuccessful at something of this capacity, and I think it seems as a easy function, in general.

------------Any help would be appreciated much... Thank you.
-----------------------------


Comment
Watch Question

CERTIFIED EXPERT

Commented:
on the flash side, you need to use a LoadVars.SendAndLoad. I have to apologize in advance, I don't have aflash compiler, but this is the general idea.

gatherData = function () {    
myData = new LoadVars();    
myData.keyword = this.keyword;
myData.toFlash = this.toFlash;    

myData.onLoad = function() {        
results.html = true;        
results.htmlText = myData.toFlash;    
};    

myData.sendAndLoad("dbquery.php", myData, "POST");};
sendButton.onPress = function() {    gatherData();};

....

on the php side, you want something like this:
<?php
mysql_connect("localhost", "mysql_user", "mysql_password") or
    die("Could not connect: " . mysql_error());
mysql_select_db("mydb");

// If we get a 5-digit number, assume zip
$kw = trim($_POST['keyword']);
if (eregi('\d\d\d\d\d', $kw))
   $sql = "SELECT * FROM table WHERE zip_code = $kw";
elseif (eregi('^\d+ [a-z0-9]+', $kw))
   $sql = "SELECT * FROM table WHERE street = '$kw'";
elseif (eregi('^[a-z][a-z]$', $kw))
   $sql = "SELECT * FROM table WHERE state= '$kw'";
else
    $sql = "SELECT * FROM table where city like '%$kw%' or street like '%$kw%'";

$result = mysql_query($sql);

while ($row = mysql_fetch_assoc($result)) {
    $r .= $row['street'] . "
" . $row['city'] . "
" . $row['state'] . "
" . $row['zipcode'] . "
" ;
}

mysql_free_result($result);
echo "&toFlash=" . urlencode($r);

?>

Author

Commented:
hello Mankowitz.
thank you for the quick response..

I have plugged the AS in the flash, and i have created the 'dbquery.php'  with the code you provided.  
-----------------------i have run a test search and i entered a keyword and in the dyanmic text box it gives me an 'undefined' message for a second then disappears.
-------------------
not sure if it is not connecting to database or not.. but I do have all correct information (i send my data from flash to the same database with the same username, password, host, and dbname..,, so it should be connecting)

--------------------------

here is the AS below .. i didnt change anything on this..s

my dynamic text box has the instance name of 'results'
the button has the instance name of 'sendButton'
the input box has the instance name of 'keyword'

Do i have to add anything else to the flash stage?

-------------------and here is the PHP.........below

Am i supposed to change anything else in the PHP?  I do have my table named 'table' and i have checked all of the table field names.  all are correct.  I am not too skilled with any of this;

any ideas what can be wrong.   looking forward to your response
Thank you







gatherData = function () {    
myData = new LoadVars();    
myData.keyword = this.keyword;
myData.toFlash = this.toFlash;    
 
myData.onLoad = function() {        
results.html = true;        
results.htmlText = myData.toFlash;    
};    
 
myData.sendAndLoad("dbquery.php", myData, "POST");};
sendButton.onPress = function() {    gatherData();};
 
 
-----------------------
 
<?php
 
 
mysql_connect("db201.perfora.net", "dbo245573367", "g.GEpu2p") or
    die("Could not connect: " . mysql_error());
mysql_select_db("db245573367");
 
// If we get a 5-digit number, assume zip
$kw = trim($_POST['keyword']);
if (eregi('\d\d\d\d\d', $kw))
   $sql = "SELECT * FROM table WHERE zip_code = $kw";
elseif (eregi('^\d+ [a-z0-9]+', $kw))
   $sql = "SELECT * FROM table WHERE street = '$kw'";
elseif (eregi('^[a-z][a-z]$', $kw))
   $sql = "SELECT * FROM table WHERE state= '$kw'";
else
    $sql = "SELECT * FROM table where city like '%$kw%' or street like '%$kw%'";
 
$result = mysql_query($sql);
 
while ($row = mysql_fetch_assoc($result)) {
    $r .= $row['street'] . "
" . $row['city'] . "
" . $row['state'] . "
" . $row['zipcode'] . "
" ;
}
 
mysql_free_result($result);
echo "&toFlash=" . urlencode($r);
 
?> 

Open in new window

CERTIFIED EXPERT

Commented:
As far as the flash, you can debug by changing the sendandload to geturl, which will open a new window with the results. (i.e. set the on release property of the submit button to geturl("path to script") instead of the gatherdata function) That will at least show you if the php script is working.

Also, you can try connecting to the php script directly, with

http://hostname.com/scriptname?keyword=10021

to see what comes up. It should respond with something like
&toflash=24+front+st<br>

Author

Commented:
hello Mankowitz,,

yes i have tried the second option and the results were displayed like this:  (I received these results no matter what I entered as the keyword on the address bar after 'keyword='




&amp;toFlash=8586+palms%0Ahollywood%0Aflorida%0A33334%0A3334+palms%0Amiami%0Aflorida%0A33139%0A1123+palms%0Amiami%0Aflorida%0A33139%0A8345+hills%0Ahollywood%0Aflorida%0A33334%0A8821+hills%0Ahallandale%0Aflorida%0A33111%0A909+beach%0Ahallandale%0Aflorida%0A33111%0A44567+beach%0Aocean%0Aflorida%0A33555%0A987+ocean%0Apalm%0Aflorida%0A33555%0A321+ocean%0Amiami%0Aflorida%0A33777%0A

So, this means we are connecting to the database!   but the results arent changing no matter what keyword i enter, and plus it is not loading in the flash.

What do you think?
CERTIFIED EXPERT

Commented:
ok, lets first work on the php. As for the flash, see if you can display the variable myData.toFlash or the whole mydata object to see what's in it.
// If we get a 5-digit number, assume zip
$kw = trim($_POST['keyword']);
if (eregi('\d\d\d\d\d', $kw))
   $sql = "SELECT * FROM table WHERE zip_code = $kw";
elseif (eregi('^\d+ [a-z0-9]+', $kw))
   $sql = "SELECT * FROM table WHERE street = '$kw'";
elseif (eregi('^[a-z][a-z]$', $kw))
   $sql = "SELECT * FROM table WHERE state= '$kw'";
else
    $sql = "SELECT * FROM table where city like '%$kw%' or street like '%$kw%'";
 
print "Got $kw -- Query $sql<br>\n";    // Add this line to see what query it is running.
 
$result = mysql_query($sql);

Open in new window

Author

Commented:
HI!

i have modified the php code as you said.... and now i am getting these results in the browser window :      &amp;toFlash=

----is that what you wanted to see?

---------------------------------------

as for the flash you mentioned to display the variable myData.toFlash or the whole mydata object to see what's in it.  

I'm lost on this one!  please advise.

CERTIFIED EXPERT

Commented:
php:
The line
print "Got $kw -- Query $sql
\n";
should show us what query is running, but it doesn't seem to be there... that's odd.

flash:
let's make sure each part runs right
myData.onLoad = function() {        
results.html = true;        
results.htmlText = myData.toFlash;    
results.htmlText = "JUST TESTING!!!!";

Author

Commented:
Mankowitz
how are you!

we are comingalong......i get the 'JUST TESTING!!!' in flash now when i click the submit button.

----------AND also i have different results for the php test....

here is what i input on the address bar..

http://www.previewsample.com/search/dbquery.php?keyword=


---------here is the results:\
Got  -- Query SELECT * FROM table where city like '%%' or street like '%%'
&amp;toFlash=                 <(noticed missing the other fields, zipcode and address)

````````````but still i have no idea what to do next!     is this what you wanted to see as the results?




CERTIFIED EXPERT

Commented:
ok, baby steps.

change this line

$kw = trim($_POST['keyword']);

to

$kw = trim($_GET['keyword']);

Author

Commented:
yes sir Mankowitz...
this change is working on the php test!

it brings the correct results in the browser window everytime i insert a new keyword in the web address.  good good!
 
CERTIFIED EXPERT

Commented:
This is the source for the flash: http://www.kirupa.com/forum/showthread.php?t=252746

Let's see if this gets us any closer....
gatherData = function () {
    myData = new LoadVars();
    myData.keyword = keyword.text;
    myData.toFlash = this.toFlash;
    myData.onLoad = function() {
        results.html = true;
        results.htmlText = myData.toFlash;
    };
    myData.sendAndLoad("dbquery.php", myData, "POST");
};
 
sendButton.onPress = function() {
    gatherData();
};

Open in new window

Author

Commented:
hello , how are you,
we are getting some action in the flash now!


I click the sendButton and it returns all data no matter what keyword  i input.

---Also, it returns all data if i press the sendButton without a keyword.

I can see the light!  
CERTIFIED EXPERT

Commented:
ok, so it sounds like it is not sending the keyword information correctly. When you click the sendbutton, the php script should get to a line that looks like this:

print "Got $kw -- Query $sql
\n";

what text appears in the result box?
does it say

Got -- Query SELECT.....

?

Author

Commented:
Hello Mankowitz,,

sorry it took me so long to get back to you..   inside of the flash, when i click the send button, my dynamic text box displays all of the data that is in my database no matter what keyword i enter .  (and this is all inside my flash movie) is that what you are asking?  

It does not display any of the code you mentioned above.  

It displayed the 'just testing' line when we ran that line before, but since then we changed it so now it is just diplaying all of the data.  So its just not reading that php correctly?

--------the php test is working fine if i type the php file in the address bar in the browser, that returns the results of the keyword i entered like this:

http://www.previewsample.com/search/dbquery.php?keyword=  (when i type in a keyword here it shows the correct results in the browser)    

--------here is what the results look like in the browser:   (i typed in 'miami' as the keyword)

Got miami -- Query SELECT * FROM search where city like '%miami%' or street like '%miami%'
 &toFlash=3334+palms%0Amiami%0Aflorida%0A33139%0A1123+palms%0Amiami%0Aflorida%0A33139%0A321+ocean%0Amiami%0Aflorida%0A33777%0A
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
great news....  i changed that to Get method and it works now... i typed in a keyword and it returned the results...   ----At first it wasnt returning the 'state' and 'zipcode' field, so i looked at the php and figured out how to fix that ..  the first snippet was the code that wasnt returning the 'state' and the 'zipcode' and the second snippet is what i added to make the search work on the 'state' and the 'zip'

Overall the search runs great... i can type in a part of a keyword and it will find that entry..
-----example,, if i enter 'fl' ,, it returns all the 'florida' entries... which is awesome..
or if i enter a number '9' it will return all entries with '9' in it.......again i like it this way..

Thank you thank you...... you are a genius...  

-----you have made me very happy and the points are yours definitely, however as i said from the start i didnt want to make this too complex so that I can understand it ..a little bit.!

So , as you could imagine, I do want to try to figure out how to do some other features, such as :

1)  having an image for each entry.  (if i have the image located on my server in a directory, is this possible?  or is the image stored in the database?  )

2.)  and also, instead of having one dynamic text box where the data is returned,  I would like to have a separate dynamic text field that returns each entry.  (as it is structured now, all the entries relative to the search are returned in the same dynamic text field.

-----------------  I also know a little bit on how to do this, but of course i have never integrated it with this search function... so I am going to try..

------------------if i cant figure it out I am going to post another question about it, .. hopefully you expertise is still around ....that would be most excellent...  

======thank you very much for sticking to this  and hope it didnt get too complex and/or time consuming..  you are brilliant.. god bless.

-------------
(before)
--------------
 
$kw = trim($_GET['keyword']);
if (eregi('\d\d\d\d\d', $kw))
   $sql = "SELECT * FROM search WHERE zipcode = $kw";
elseif (eregi('^\d+ [a-z0-9]+', $kw))
   $sql = "SELECT * FROM search WHERE street = '$kw'";
elseif (eregi('^[a-z][a-z]$', $kw))
   $sql = "SELECT * FROM search WHERE state= '$kw'";
else
    $sql = "SELECT * FROM search where city like '%$kw%' or street like '%$kw%'";
 
-----------------
after
---------------
$kw = trim($_GET['keyword']);
if (eregi('\d\d\d\d\d', $kw))
   $sql = "SELECT * FROM search WHERE zipcode = $kw";
elseif (eregi('^\d+ [a-z0-9]+', $kw))
   $sql = "SELECT * FROM search WHERE street = '$kw'";
elseif (eregi('^[a-z][a-z]$', $kw))
   $sql = "SELECT * FROM search WHERE state= '$kw'";
else
    $sql = "SELECT * FROM search where city like '%$kw%' or street like '%$kw%' or state like '%$kw%' or zipcode like '%$kw%'";
 
 
=======added the 'state' and 'zipcode' to the last line.. 

Open in new window

Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.