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
336 Views
5 Days Discussion Span
Latest Post 9 Years Ago Latest Post by PierJean

All 7 Replies

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.

Search form Submit button returns whole database instead selected fields

Recommended Answers Collapse Answers

All 7 Replies

Recommended Answers