Search form Submit button returns whole database instead selected fields

Question

PierJean 0 Newbie Poster

9 Years Ago

Hello.
I am retired, 70 years old and new to php and wordpress. Not to be completely stupid, I did some homework learning everyday 10 hours a day for the last month or so but my time is limited so I finally decided to seek some help from a helpful hand.. I need baby steps (or a walker) helping me finish this.

mysql is version 5.5.40 I am using wordpress that I had to learn in the process.
http://snapps.riverviewproject.com/?page_id=6

I created a custom form for members registration to feed the database and this is workling. so far so good

In my home page I created a search form for visitors to retrieve selected members according to their location (drop-down menu with available zip codes) and specialties (Check-box)

I am getting close but cannot find what I am looking for to finish this.
I am like a dog trying to bite his tail, but not being one I do not have this luxury!
:)

Thanks in advance.

in short here are my problems:

1) from the search form on the left column, whatever pet service a visitor choose in their own zip code, or even no selection at all, the result gives ALL members in the database - regardless of their specialty or zip code covered - instead selected ones from the submit button. (to sumplify, so far I only mentionned 2 members in the database with one zipcode / one specialty each out of 19 members, 25 specialties and 90 zipcodes.
Fairly simple as it seems but quite a mountain to climb for my old legs. help me reach the summitt please!

2) The search results are showing in the same column as the form that is too narrow. I am trying to show the results in the "result page" http://snapps.riverviewproject.com/?page_id=724
[removed url]
I really like to keep this search form on the home page left column, so it is available at first glance.

3) finally, results are displaying in one block,(it took me 2 weeks to realize this exploit !YES ! but it is not very pretty or easy to read. I found out how to make a title but cannot separate the words. The member's bio (with eventual picture) will be more complete so It needs to be separated paragraph.

( also, do I have to place each 60 zip code on a different field in the database and is there a way to avoid mentioning them again in separate line in the php ?)
ie:echo $row[zipcode -89109]."";etc... 60 times or so....
Is there a shorter way to do this?

-----------------------------------
search.HTML

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head><meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"/></head>
<body bgcolor="#FFFFFF">
<center>
<script type="text/javascript" src="swfobject.js"></script><div id="CC8404954">Form Object</div><script
type="text/javascript">var so = new SWFObject("search.swf", "search.xml", "275", "546", "7,0,0,0", "#ffffff");so.addParam("classid",
"clsid:d27cdb6e-ae6d-11cf-96b8-444553540000");so.addParam("quality", "high");so.addParam("scale", "noscale");so.addParam("salign",
"lt");so.addParam("FlashVars", "xmlfile=search.xml&w=275&h=546");so.write("CC8404954");</script>
</center>
</body>
</html>

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

search.php

<?php
$db = mysql_connect("localhost","pierjean","*********");
if (!$db) {
die("Database connection failed miserably: " . mysql_error());
}


$db_select = mysql_select_db("snapps",$db);
if (!$db_select) {
die("Database selection also failed miserably: " . mysql_error());
}
?>

<?php
$db = mysql_connect("localhost","pierjean","********");
if (!$db) {
die("Database connection failed miserably: " . mysql_error());
}

<html>
<head>
<title>members</title>
</head>
<body>

IN CONSTRUCTION. TRY OUT ONLY. Please try again later.than you

<div class="cssstyle">
<?php
$result = mysql_query("SELECT * FROM members", $db);
if (!$result) {
die("Database query failed: " . mysql_error());
}

while ($row = mysql_fetch_array($result)) {
echo "<h2>";
echo $row[company]."";
echo "</h2>";
echo "<p>";
echo $row[firstname]."";
echo $row[lastname]."";
echo $row[phone]."";
echo $row[website-url]."";
echo $row[Email]."";
echo $row[Bio]."";
echo $row[11]."subcategory-Exotics";
echo $row[zipcode-89102]."";
echo $row[zipcode -89109]."";
echo $row[Overnight-stay]."";

echo "</p>";
}
?>
</div>
</body>
</html>

<?php


mysql_close($db);
?>

php

Edited 9 Years Ago by happygeek because: moved

4 Contributors
7 Replies
341 Views
5 Days Discussion Span
Latest Post 9 Years Ago Latest Post by PierJean

hericles 289 Master Poster

9 Years Ago

Simply put, you aren't restricting the query you are sending to the database.
SELECT * FROM members
selects everything. You need to add a where clause and pass in whatever value you want to use as a criteria. For example, to get members in a particular ZIP code:
SELECT * FROM members WHERE zip_code = 89102;
The column name would have to match of course, it may not be called zip_code.
And the actual number would come from the input the user filled out.

gabrielcastillo 40 Web Developer

9 Years Ago

When you select from your database, you can use a couple of loops using the zip, you can start by searching for the zip exactly.

"SELECT * FROM table WHERE zip_code = $zip_code"

if no results are found, you can then search for results simular to the zip code, by using LIKE and replacing the last digit with a wildcard %

$zip_code = sub_str($zip_code, -0, -4);

$sql = "SELECT * FROM table WHERE zip_code LIKE '$zip_code%'";

This will remove the last digit from the zip and add wild card for database to look for. So if your zip is 92101, your query will be 9210% and find all matching results. You can do this for all digits in the string until your get results or null

here is how I did this will my code im using in wordpress plugin. I search the full zip, then decrease the number and insert wild card until I find results.

        if( is_numeric($param['q']) ){

            $query = $wpdb->escape( $param['q'] );

            $sql = "SELECT * FROM $tablename WHERE zip LIKE '$query'";
            $results = $wpdb->get_results($sql);

            if( empty($results) ){
                $query = substr($query, -0, 4);
                $sql = "SELECT * FROM $tablename WHERE zip LIKE '$query%'";
                $results = $wpdb->get_results($sql);

                if( empty($results) ){

                    $query = substr($query, -0, 3);
                    $sql = "SELECT * FROM $tablename WHERE zip LIKE '$query%'";
                    $results = $wpdb->get_results($sql);

                    if( empty($results) ){
                        $query = substr($query, -0, 2);
                        $sql = "SELECT * FROM $tablename WHERE zip LIKE '$query%'";
                        $results = $wpdb->get_results($sql);

                        if( empty($results) ){

                            $query = substr($query, -0, 1);
                            $sql = "SELECT * FROM $tablename WHERE zip LIKE '$query%'";
                            $results = $wpdb->get_results($sql);

                            if( empty($results) ){

                                $sql = "SELECT * FROM $tablename";
                                $results = $wpdb->get_results($sql);
                                echo "No results found at your locations, here are our other locations.<br />";
                                foreach( $results as $result ){
                                    $href = str_replace(' ', '-', $result->store );
                                    echo '<a href="'. get_bloginfo('url') . '/location/' . strtolower($href) .'">'. $result->store .'</a><br />';
                                }
                                //echo 'end results';
                            }else{
                                foreach( $results as $result ){
                                    $href = str_replace(' ', '-', $result->store );
                                    echo '<a href="'. get_bloginfo('url') . '/location/' . strtolower($href) .'">'. $result->store .'</a><br />';
                                }
                                //echo 'end results';
                            }

                        }else{
                            foreach( $results as $result ){
                                $href = str_replace(' ', '-', $result->store );
                                echo '<a href="'. get_bloginfo('url') . '/location/' . strtolower($href) .'">'. $result->store .'</a><br />';
                            }
                            //echo 'end results';
                        }

                    }else{
                        foreach( $results as $result ){
                            $href = str_replace(' ', '-', $result->store );
                            echo '<a href="'. get_bloginfo('url') . '/location/' . strtolower($href) .'">'. $result->store .'</a><br />';
                        }
                    }

                }else{
                    foreach( $results as $result ){
                        $href = str_replace(' ', '-', $result->store );
                        echo '<a href="'. get_bloginfo('url') . '/location/' . strtolower($href) .'">'. $result->store .'</a>';
                        echo '<br />';
                    }
                }

            }else{
                foreach( $results as $result ){
                    $href = str_replace(' ', '-', $result->store );
                    echo '<a href="'. get_bloginfo('url') . '/location/' . strtolower($href) .'">'. $result->store .'</a><br />';
                }
            }
        }

Edited 9 Years Ago by gabrielcastillo

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

PierJean 0 Newbie Poster · Answer 1 · 2014-12-08T17:22:45+00:00

thank you for the fast response
I did found this previously but i do not know what zip code the client is going to type and Mostly which specilties he is going to select in the multiple choiice check-box.

so I cannot use
SELECT * FROM members WHERE zip_code = 89102;

what is the code to automatically use the imput from the search form check-box?

also how do I separate the wordsin the results?

and how do I send the results in another page?(ie search-results) i did something approaching but I got a page within a page , and it looks very strange.
Thanks again!

Taywin 312 Posting Virtuoso · Answer 2 · 2014-12-08T21:37:11+00:00

$zip_code = sub_str($zip_code, -0, -4);

This is very strange way of teaching people on PHP! Why use -0 when you can simply use 0? Also, why would you give an example of negative length (even though it gives the correct result) instead of a positive length when you KNOW what it is supposed to be? Giving a negative length will give you a wrong result if the string length is LONGER than 5. So it should be $zip_code = sub_str($zip_code, 0, 4); which means starting from string index 0 and take almost 4 characters.

gabrielcastillo 40 Web Developer · Answer 3 · 2014-12-08T22:52:19+00:00

I use negative because I want to start from the last char and work my way back. As a developer I should know the zip code is 5 chars long and should put a check to make sure the string is no longer than 5 using javascrip or php. Then I can run my loop and if statment to count backwards starting from the last digit and work my way back..

You can see my working example here: Click Here. If you place a zip or city in the find location box, you will get the results from the above code.

PierJean 0 Newbie Poster · Answer 4 · 2014-12-11T01:19:21+00:00

hello gabrielcastillo, I am back sorry for the delay in answering.The beginner I am deleted main wordpress database per error. I had to redo everything from scratch. Before that I tried your codes in anyway possible (to me) without success. It seems so close! :(

my SQL is 5.5.40, I am using coffeecup web Form Builder installed on my PC (windows 7 64bit ultimate) to create my searchform
see it at http://snapps.riverviewproject.com/
I use <Iframe> to reach the HTML Page at
http://snapps.riverviewproject.com/search/search.html but that is not the problem I think.(if I still can)

What you did in your website is exactly what I try to do exepted my search form has 2 choices of criteria," the visitor's zipcode and his choices on specialties (multiple checkbox). I tried your code and passed hours how to figure it out it does not work for me. what am I doing wrong?
at my last try I got this error:

Database query failed: 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 'table WHERE zipcode =' at line 1
this is my search.php file:

<?php
 $db = mysql_connect("localhost","rivervi9_pier145","PjML*$1945"); 
 if (!$db) {
 die("Database connection failed miserably: " . mysql_error());
 }


 $db_select = mysql_select_db("rivervi9_wo2635",$db);
 if (!$db_select) {
 die("Database selection also failed miserably: " . mysql_error());
 }
?>


<?php
 $db = mysql_connect("localhost","user***","password******"); 
 if (!$db) {
 die("Database connection failed miserably: " . mysql_error());
 }


 $db_select = mysql_select_db("dtb*****",$db);
 if (!$db_select) {
 die("Database selection also failed miserably: " . mysql_error());
 }
?>
<html>
 <head>
 <title>members</title>
 </head>
 <body>



 This is the results according to your zip code and specialties selection
 IN CONSTRUCTION. TRY OUT ONLY please try later.than you


<div class="cssstyle">
 <?php
$result = mysql_query("SELECT * FROM table WHERE zipcode = $zipcode", $db);
 if (!$result) {
 die("Database query failed: " . mysql_error());
 }
 while ($row = mysql_fetch_array($result)) {
 echo "<h2>";
 echo $row[company]."";
 echo "</h2>";
 echo "<p>";
  echo $row[2]."firstname";
 echo $row[3]."lastname";
echo $row[5]."phone";
 echo $row[7]."URL";
 echo $row[8]."EMAIL";
echo $row[9]."BIO";
echo $row[11]."subcategory-Exotics";
 echo $row[16]."zipcode 89109";
echo $row[17]."zipcode 89120";
echo $row[18]."Overnight-stay";

 echo "</p>";
 }
?>
</div>
 </body>
</html>

<?php
//Step5
 mysql_close($db);
?>

PierJean 0 Newbie Poster · Answer 5 · 2014-12-12T16:45:02+00:00

Actually I do not really need for a visitor to find the nearest zip_code. all the 20 Pet Care Providers (members) are covering some zip-codes.
The difference is, the members is going TO the client's house not the reverse.
if the search for a service in a particular zip code does not give any result then the client is out of luck . In that case, hey are asked to call us, the association to find if a member will be willing to take the deal out of their area.

so **All I need really is the code for mysql version 5.5.40 ** to find, upon pressing the SUBMIT button (go fetch) which member (or members) performs the requested service (or services) in a particular zip-code .

The result (the whole database) was showing in the font page left columm where the form was, so I moved the search form in another page in order to use the whole page to dilpay the results.
those are still showing the WHOLE database ( 5 members out of 20 so far) regarless of zip or specialty, and they are displayed raw from database,with no fancy design beside a title for each..

On the front page I left a link instead of the search form ( hound-dogs's pack picture).Click Here
I know there must be a better way but am doing this with limited knowledge.
I need a good soul to help my old legs to croos the road! Any advice and guidance is appreciated.