Need Help Searching my Database

[Valdoua]

I have a mysql database online and I have no idea on how to create the code to access it. I use to use ACCESS with a simple databse form to link to it so that people could search my store list database for stores near them. Now I have to use MYSQL because my host no longer allows MS ACCESS.

I have put my database up, I can actually link to it, what I dont have is any idea on how to write the code for someone to be able to search by either zip code or state and then get a list out of stores.

Can anyone please help me. I tried to hire 5Stick ( a php, mysql developer) but they told me that my job was way to small for them, basically less then 20 minutes. Well hell I have been working on this for 2 weeks and researching and I still have no clue! Can somone please tell me why I went to art school instead of computer school?

Please help!:mad: :mad:

[Valdoua]

Okay, I have the database up, it only has 1 table in it.
All I need is a clue on how to ask my 2 queries and then havea results page show the results.

I cant believe I cannot find a clear and precise example online.

I am going to go back and stick my head in te sand!

[LordShryku]

They're right, this is real easy....

<?
// Page 1, the form
echo "<form name=\"search\" action=\"page2.php\" method=\"post\">
Search by Zip: <input type=\"text\" name=\"zipCode\">
<br>
Search by State: <input type=\"text\" name=\"state\">
<br>
<input type=\"submit\">
</form>";
?>

<?
// Page two, search and display 
$connect = mysql_connect("server", "user", "pass"); 
$db = mysql_select_db("yourDB", $connect); 
$sql = "SELECT *  

        FROM table  

        WHERE state LIKE '%".$_POST['state']."%'  

        OR zip LIKE '%".$_POST['zipCode']."%'"; 
$result = mysql_query($sql); 
$rows = mysql_num_rows($result); 
if($rows > 0) { 
    while($row=mysql_fetch_array($result) { 
        echo "State: ".$row['state']."<br>Zip: ".$row['zip']; 
    } 
} 
else { 
    echo "No results found"; 
} 
mysql_close($connect); 
?>

That's a pretty crude way of doing it. you can pretty it up. And if you didn't see something like this on the net, you probably aren't looking too hard. This is the most common thing people do with PHP.

[Valdoua]

Okay, thanks for your help! But when I copied and pasted your code is went crazy and now looks like gibbereish. Once Iget the format down I will be able to augment the page style and such as I see fit.

I hate code...is there a drop and drag program that is so simple even a monkeyu could do it? Does anyone have a moneky I could hire?

[LordShryku]

We got a monkey names Merve. But he isn't for hire. What kind of "gibberish" are you getting? Did you edit everything that needed to be edited(server, username, password, table, fields, etc...)?

[Valdoua]

I keep gettign this error code now:

Parse error: parse error in /h6/fcg3/spage2.php on line 12

any clue?

[LordShryku]

And which page is that?

Let's see the code.....

We'll nail this one eventually 😃

[Valdoua]

Okay here we go...

page 1 looks like this:

<?
// Page 1, the form
echo "<form name=\"search\" action=\"spage2.php\" method=\"post\">
Search by Zip: <input type=\"text\" name=\"zipCode\">
<br>
Search by State: <input type=\"text\" name=\"state\">
<br>
<input type=\"submit\">
</form>";
?>

Page 2 looks like this:

<?
// Page two, search and display
$connect = mysql_connect("mysql.schylling.com", "fcg3", "fcg3");

$db = mysql_select_db("STOREDATA", $connect);

$sql = "SELECT *

FROM table

WHERE state LIKE '%".$POST['State']."%'

OR zip LIKE '%".$POST['ZIP_Code']."%'";

$result = mysql_query($sql);

$rows = mysql_num_rows($result);

if($rows > 0) {

while($row=mysql_fetch_array($result) {

echo "State: ".$row['state']."<br>Zip: ".$row['zip'];

}

}

else {

echo "No results found";

}

mysql_close($connect);

?>

I have seen this error before on a few programs I have use to try to create this type of scenario.

[LordShryku]

Okay, here's your line 12

while($row=mysql_fetch_array($result) {

You're missing a right paren, so change it to

while($row=mysql_fetch_array($result)) {

[Valdoua]

😕 :mad: :mad: :mad: :mad: :mad:

Okay, I did what you said and now I have these error messages:

Warning: Access denied for user: '[e-mail]' (Using password: YES) in /h6/fcg3/spage2.php on line 3

Warning: MySQL Connection Failed: Access denied for user: '[e-mail]' (Using password: YES) in /h6/fcg3/spage2.php on line 3

Warning: mysql_select_db(): supplied argument is not a valid MySQL-Link resource in /h6/fcg3/spage2.php on line 4

Warning: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) in /h6/fcg3/spage2.php on line 9

Warning: MySQL Connection Failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) in /h6/fcg3/spage2.php on line 9

Warning: MySQL: A link to the server could not be established in /h6/fcg3/spage2.php on line 9

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /h6/fcg3/spage2.php on line 10
No results found
Warning: mysql_close(): supplied argument is not a valid MySQL-Link resource in /h6/fcg3/spage2.php on line 19

Someone please find me a monkey....or even a shovel at this point so I can beat my self in the head with it!!!!

[LordShryku]

Can't connect to your database. Might want to make sure your username and pass are right, the server is right, and the provider has actually given you access....

[Valdoua]

Okay I found my error and now I get this one.

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /h6/fcg3/spage2.php on line 10
No results found

[LordShryku]

Look at your SQL query...

SELECT *
FROM table
WHERE state LIKE '%".$POST['State']."%'
OR zip LIKE '%".$POST['ZIP_Code']."%

Is your table named "table"? If not, need to change that.
Is your zip code field named "zip"? If not, change that.
Is your state field named "state"? If not, change that.

[Valdoua]

Okay, I fixed those...and now I get this:

State:
Zip: State:
Zip: State:
Zip: State:
Zip: State:
Zip: State:
Zip: State:
Zip: State:
Zip: State:

ANd this continues on for a while.

I thought that I would be able to show the rest of the fields.
There are 7 rows per each

My database has this:

ID
Name
Address
City
State
ZIP_Code
Sales_Phone

I was hoping to get it to work where if they put in either the zip code of the state that they would get the rest of the address lines on the result pages. What the hell am I missing?

[LordShryku]

Okay, lets add a table, just formatting since you have 7 things to display. Now, this is case sensitive when you get the rows, so make sure the casing is right on this. Starting on Page 2 at if($rows > 0)...

if($rows > 0) {
    echo "<table border=\"0\">
            <th align=\"center\">ID</th>
            <th align=\"center\">Name</th>
            <th align=\"center\">Address</th>
            <th align=\"center\">City</th>
            <th align=\"center\">State</th>
            <th align=\"center\">Zip Code</th>
            <th align=\"center\">Sales Phone</th>";

while($row=mysql_fetch_array($result) { 
    echo "<tr>
            <td align=\"center\">".$row['ID']."</td>
            <td align=\"center\">".$row['Name']."</td>
            <td align=\"center\">".$row['Address']."</td>
            <td align=\"center\">".$row['City']."</td>
            <td align=\"center\">".$row['State']."</td>
            <td align=\"center\">".$row['ZIP_Code']."</td>
            <td align=\"center\">".$row['Sales_Phone']."</td>
          </tr>";
} 
} 
else { 
    echo "No results found"; 
} 
mysql_close($connect); 

[stolzyboy]

in your while loop for every record, you should be able to echo those out by doing.

echo $row['Name'];//all these are case sensitive
echo $row['Address'];
echo $row['City'];
echo $row['State'];
echo $row['ZIP_Code'];
echo $row['Sales_Phone'];

[Valdoua]

Well I did what you wrote down here. And it works! But not in the right way. No matter what Zip Code or State you Put in it still gives you every entry in my database on the results page. It doesnt just give you the choices from the appropriate zip code or state.

Here is the code:

<?
// Page two, search and display
$connect = mysql_connect("duke.valueweb.net", "fcg3", "dgserver");

$db = mysql_select_db("fcg3", $connect);

$sql = "SELECT *

FROM STOREDATA
WHERE State LIKE '%".$POST['State']."%'

OR ZIP_Code LIKE '%".$POST['ZIP_Code']."%'";

$result = mysql_query($sql);

$rows = mysql_num_rows($result);

if($rows > 0) {
echo "<table border=\"0\">
<th align=\"center\">ID</th>
<th align=\"center\">Name</th>
<th align=\"center\">Address</th>
<th align=\"center\">City</th>
<th align=\"center\">State</th>
<th align=\"center\">Zip Code</th>
<th align=\"center\">Sales Phone</th>";

while($row=mysql_fetch_array($result)) {  
    echo "<tr> 
            <td align=\"center\">".$row['ID']."</td> 
            <td align=\"center\">".$row['Name']."</td> 
            <td align=\"center\">".$row['Address']."</td> 
            <td align=\"center\">".$row['City']."</td> 
            <td align=\"center\">".$row['State']."</td> 
            <td align=\"center\">".$row['ZIP_Code']."</td> 
            <td align=\"center\">".$row['Sales_Phone']."</td> 
          </tr>"; 
}  

}

else {

echo "No results found";

}

mysql_close($connect);

Ugg....to be so close and then so far away...

[Valdoua]

Shouldnt the like statements be different so you aregetting the exact record your looking for?

Here is what I mean:

$sql = "SELECT *

FROM STOREDATA
WHERE State LIKE '%".$POST['State']."%'

OR ZIP_Code LIKE '%".$POST['ZIP_Code']."%'";

Shoudlnt the "LIKE" statements be changed to show an exact record?

[stolzyboy]

the way you have it set up right now it will get all records of whatever state and all records of zip code

so if you want

state ND and zip code 77777

it will get all records with the state of ND or the zip of 77777

[Valdoua]

Go to this link and try it out:

http://www.schylling.com/page1.php

I dont think I am getting what I am trying to do.

No matter what I put in Massachusetts seems to come up first.

Let's day your looking just in New Hampshire and you type in NH with no Zip Code I get All records starting from #1 all the way through to the end. It is not just giving me the records from NH