Problems with Search Script - help

CountryGirl · November 23, 2009

I'm trying to use the code below as a search feature for my website. But, I either get an error message or it says "problems ...." when I try to run a search on it. I think it has something to do how the connection is written in the code, but I'm not totally sure. Could any of you experts take a look at this and let me know exactly is wrong? I'm sure you guys could pick out what I'm not typing in right! I greatly appreciate the help!!

<?php
$dbHost = 'localhost'; 
$dbUser = 'wcassessor'; 
$dbPass = '**********';
$dbDatabase = 'wcassessor'; 
$con = mysql_connect($dbHost, $dbUser, $dbPass) or die("Failed to connect to MySQL Server. Error: " . mysql_error());

mysql_select_db($dbDatabase) or die("Failed to connect to database {$dbDatabase}. Error: " . mysql_error());
?>

<?php	
if ($search) // perform search only if a string was entered.
{
mysql_connect($dbHost, $dbUser, $dbPass) or die ("Problem connecting to DataBase");
$srch="%".$search."%";
$query = "SELECT * FROM asmnt_ownership WHERE Account, OwnersName";

$result = mysql_db_query($con, $query);

if ($result)
{
echo "Here are the results:<br><br>";
echo "<table width=90% align=center border=1><tr>
<td align=center bgcolor=#00FFFF>Account</td>
<td align=center bgcolor=#00FFFF>Owners Name</td>
</tr>";

while ($r = mysql_fetch_array($result)) { // Begin while
$act = $r["Account"];
$name = $r["Owners Name"];
echo "<tr>
<td>$act</td>
<td>$name</td>
</tr>";
} // end while
echo "</table>";
} else { echo "problems...."; }
} else {
echo "Search string is empty. <br> Go back and type a string to search";
}
?> 

<html xmlns="http://www.w3.org/1999/xhtml">

<head><title>Searching Another Test</title>
</head>
<body bgcolor=#ffffff>
<h1>Searching the Database</h1>
<form method="post" action="search0.php">
<table width=90% align=center>
<tr><td>search for:</td><td><input type=text name='search' size=60 maxlength=255></td></tr>
<td></td><td><input type=submit></td></tr>
</table>
</form>
</body>
</html>

Qadoshyah

zeodragonzord · November 24, 2009

Your query isn't valid. Are you trying to retrieve only the Account and OwnersName columns? If so use something like:

SELECT Account, OwnersName FROM asmnt_ownership WHERE columnName LIKE '%searchWord%'

Since you're doing searching, you'll want a criteria by adding a complete "WHERE" clause such as you see above. You also have a $srch variable but not used; didn't know if you planned on using it. If that doesn't solve it, paste the actual error message here.

CountryGirl · November 24, 2009

Your query isn't valid. Are you trying to retrieve only the Account and OwnersName columns? If so use something like:
SELECT Account, OwnersName FROM asmnt_ownership WHERE columnName LIKE '%searchWord%'
Since you're doing searching, you'll want a criteria by adding a complete "WHERE" clause such as you see above. You also have a $srch variable but not used; didn't know if you planned on using it. If that doesn't solve it, paste the actual error message here.

Well, I tried the above and that didn't solve it. I don't see any error message come up except for it saying "problems. . . ." which is at a certain part in my code.

I'm thinking I've got something wrong in this part of the code, since that is the error message (the "else {echo problems....}") I get:

while ($r = mysql_fetch_array($result)) { // Begin while
$act = $r["Account"];
$name = $r["Owners Name"];
echo "<tr>
<td>$act</td>
<td>$name</td>
</tr>";
} // end while
echo "</table>";
} else { echo "problems...."; }
} else {
echo "Search string is empty. <br> Go back and type a string to search";
}
?>

Does something look wrong there?

And the full text of my code again (with the WHERE clause fixed):

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Frameset//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-frameset.dtd">
<?php
$dbHost = 'localhost'; 
$dbUser = 'wcassessor'; 
$dbPass = '************';
$dbDatabase = 'wcassessor'; 
$con = mysql_connect($dbHost, $dbUser, $dbPass) or die("Failed to connect to MySQL Server. Error: " . mysql_error());

mysql_select_db($dbDatabase) or die("Failed to connect to database {$dbDatabase}. Error: " . mysql_error());
?>

<?php	
if ($search) // perform search only if a string was entered.
{
mysql_connect($dbHost, $dbUser, $dbPass) or die ("Problem connecting to DataBase");
$query = "SELECT Account, OwnersName FROM asmnt_ownership WHERE columnName LIKE '%searchWord%'";

$result = mysql_db_query($con, $query);

if ($result)
{
echo "Here are the results:<br><br>";
echo "<table width=90% align=center border=1><tr>
<td align=center bgcolor=#00FFFF>Account</td>
<td align=center bgcolor=#00FFFF>Owners Name</td>
</tr>";

while ($r = mysql_fetch_array($result)) { // Begin while
$act = $r["Account"];
$name = $r["Owners Name"];
echo "<tr>
<td>$act</td>
<td>$name</td>
</tr>";
} // end while
echo "</table>";
} else { echo "problems...."; }
} else {
echo "Search string is empty. <br> Go back and type a string to search";
}
?> 

<html xmlns="http://www.w3.org/1999/xhtml">

<head><title>Searching Another Test</title>
</head>
<body bgcolor=#ffffff>
<h1>Searching the Database</h1>
<form method="post" action="search0.php">
<table width=90% align=center>
<tr><td>search for:</td><td><input type=text name='search' size=60 maxlength=255></td></tr>
<td></td><td><input type=submit></td></tr>
</table>
</form>
</body>
</html>

Thanks!

Qadoshyah

zeodragonzord · November 24, 2009

First, it looks as if you're attempting to connect to the database twice. You should take out the line where you have "mysql_connect() "the first time around. The second time you do "mysql_connect()" you should set it to $con, as you did the first time. Move the "mysql_select_db()" below that. Basically, you don't need to connect to the database unless someone has actually entered something in the search box.

"mysql_db_query()" is deprecated. You should use "mysql_query($query, $con)" instead. Notice the order of the parameters; query is first.

I think the problem is because when you used "mysq_db_query()", you had the database connector ($con) as the first parameter while it was expecting the database name instead. Hence, $result is not valid, and you're displaying ""problems...." from the else case. See below and determine if this works or not.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Frameset//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-frameset.dtd">
<?php
$dbHost = 'localhost'; 
$dbUser = 'wcassessor'; 
$dbPass = '************';
$dbDatabase = 'wcassessor'; 
  

$search = $_POST['search'];

if ($search) // perform search only if a string was entered.
{
$con = mysql_connect($dbHost, $dbUser, $dbPass) or die("Failed to connect to MySQL Server. Error: " . mysql_error());
mysql_select_db($dbDatabase) or die("Failed to connect to database {$dbDatabase}. Error: " . mysql_error());


$query = "SELECT Account, OwnersName FROM asmnt_ownership WHERE columnName LIKE '%searchWord%'";
$result = mysql_query($query, $con);

if ($result)
{
	echo "Here are the results:<br><br>";
	echo "<table width=90% align=center border=1><tr>
	<td align=center bgcolor=#00FFFF>Account</td>
	<td align=center bgcolor=#00FFFF>Owners Name</td>
	</tr>";

	while ($r = mysql_fetch_array($result))
	{ // Begin while
		$act = $r["Account"];
		$name = $r["Owners Name"];
		echo "<tr>
			<td>$act</td>
			<td>$name</td>
			</tr>";
	} // end while

	echo "</table>";
}
else
{
	echo "problems....";
}
}
else
{
echo "Search string is empty. <br> Go back and type a string to search";
}
?>

<html xmlns="http://www.w3.org/1999/xhtml">

<head><title>Searching Another Test</title>
</head>
<body bgcolor=#ffffff>
<h1>Searching the Database</h1>
<form method="post" action="search0.php">
<table width=90% align=center>
<tr><td>search for:</td><td><input type=text name='search' size=60 maxlength=255></td></tr>
<td></td><td><input type=submit></td></tr>
</table>
</form>
</body>
</html>

CountryGirl · November 24, 2009

Alright, I tried that and it still came back with problems.

What can we fix now?

Thanks so much for the help!!

Qadoshyah

zeodragonzord · November 24, 2009

What is the problem? Paste the error message. By the way, don't forget to change your query. You need to change 'columnName' to be one of the database fields you're searching for. 'searchWord' will be something like '$search' which contains the search word you're looking for.

SELECT Account, OwnersName FROM asmnt_ownership WHERE columnName LIKE '%searchWord%'

CountryGirl · November 24, 2009

What is the problem? Paste the error message. By the way, don't forget to change your query. You need to change 'columnName' to be one of the database fields you're searching for. 'searchWord' will be something like '$search' which contains the search word you're looking for.
SELECT Account, OwnersName FROM asmnt_ownership WHERE columnName LIKE '%searchWord%'

I'm slightly confused on one thing here.

The LIKE clause and 'searchWord'. Do I put something specific there? For example, like "Account Number." There are so many things that I will need to have searched, how do I make it the most anonymous, so that any results will return? Does that make sense?

And the WHERE clause . . . there are two column names that I need to be able to search from. Those are "Account & "OwnersName." So, I put those in the WHERE clause, right?

Qadoshyah

zeodragonzord · November 24, 2009

You have to determine which fields you want to search for. For example, say your table has three fields (columns): id, Account, OwnersName. If you want to the search word to look in both the Account field and the OwnersName field and find anything that matches one or both, you can do this.

SELECT Account, OwnersName FROM asmnt_ownership WHERE Account LIKE '%{$search}%' OR OwnersName LIKE '%{$search}%'

If you want search word to look only for the OwnersName, you can do this.

SELECT Account, OwnersName FROM asmnt_ownership WHERE  OwnersName LIKE '%{$search}%'

The {$search} represents the variable containing the word you wanted to search for.

The percent sign % means that you can match anything there; it's a wildcard. For example:

jam% will match 'jam', 'jamming' but not 'sunjam'

%is will match 'is', 'thesis' but not 'isnt'

%try% will match 'try', 'country', 'countrygirl'

So adding % may return more results.

CountryGirl · November 25, 2009

You have to determine which fields you want to search for. For example, say your table has three fields (columns): id, Account, OwnersName. If you want to the search word to look in both the Account field and the OwnersName field and find anything that matches one or both, you can do this.
SELECT Account, OwnersName FROM asmnt_ownership WHERE Account LIKE '%{$search}%' OR OwnersName LIKE '%{$search}%'
If you want search word to look only for the OwnersName, you can do this.
SELECT Account, OwnersName FROM asmnt_ownership WHERE  OwnersName LIKE '%{$search}%'
The {$search} represents the variable containing the word you wanted to search for.

The percent sign % means that you can match anything there; it's a wildcard. For example:

jam% will match 'jam', 'jamming' but not 'sunjam'

%is will match 'is', 'thesis' but not 'isnt'

%try% will match 'try', 'country', 'countrygirl'

So adding % may return more results.

Alright, I see what you are saying here and I will try something a bit different. I still have one question:

There are tons of different names in the OwnersName category. I can't possibly list them all. Do I have to put an exact word in the $search spot? Or is there a way to put some kind of word/clause I can put it that is like a wildcard and will return whatever the person searches?

I'm probably making this more complicated than it is ! Thanks for the help!

Qadoshyah

CountryGirl · November 25, 2009

I just messed around with it a bit. I added a search word into the LIKE clause for the Account number and it still didn't return anything. I keep just getting the "problems ...."

This is what I put:

   $query = "SELECT Account, FROM asmnt_ownership WHERE Account LIKE '%730%'";
   $result = mysql_query($query, $con);

I'm at a loss as to what I am doing wrong!

Qadoshyah

zeodragonzord · November 25, 2009

Your query is not valid. You cannot have a comma after "SELECT Account" unless you want to specify more fields to return. Your code shows that you die() while displaying an error message if the query fails. Please paste the error messages here if it comes up.

CountryGirl · November 25, 2009

Your query is not valid. You cannot have a comma after "SELECT Account" unless you want to specify more fields to return. Your code shows that you die() while displaying an error message if the query fails. Please paste the error messages here if it comes up.

Oh I didn't even realize that comma was there! Duh (on my part ). Well, I was finally able to get something to return - yay, at least I've got something ! You can see what I'm doing at www.wagonerassessor.com/search0.php. If you type in "730000012" for example, it'll return a bunch of account numbers. Now, this is where my question comes in again. How can I make it so that it will only return the one account number? Because I need to be able to have people return just one account number with the owners name, and other information. How do I make that happen?

Thanks again for the help!

Qadoshyah

zeodragonzord · November 25, 2009

There's a few things you need to think about.

When the user enters something in the search box, do you want them to enter an account number, the owner's name, or allow them to do both?

When the user enters in the account number, do you want the database to find an exact match or match any data that fits in the search description, even if it's not exact? For example, if they enter '500', can it return '450012' or do they have to type '450012' exactly?

CountryGirl · November 25, 2009

There's a few things you need to think about.

When the user enters something in the search box, do you want them to enter an account number, the owner's name, or allow them to do both?

When the user enters in the account number, do you want the database to find an exact match or match any data that fits in the search description, even if it's not exact? For example, if they enter '500', can it return '450012' or do they have to type '450012' exactly?

They need to be able to search by both account & name.

I'd like it so that the database will return the exact match.

Qadoshyah

zeodragonzord · November 25, 2009

Then your query should be something like this.

SELECT * FROM asmnt_ownership WHERE Account = '{$search}' OR OwnersName = '{$search}'

This mean, give me the rows where the Account exactly match the search word or OwnersName exactly matches the search word.

CountryGirl · November 25, 2009

Then your query should be something like this.
SELECT * FROM asmnt_ownership WHERE Account = '{$search}' OR OwnersName = '{$search}'
This mean, give me the rows where the Account exactly match the search word or OwnersName exactly matches the search word.

Ah, yes, that worked! That's exactly what I needed . Thanks so much!

Now, I have another question ~ I have multiple tables (16 to be exact) that need to be searched from. How would I incorporate the other tables so that by just searching the one item would return all the needed results (they all relate by having the same account number).

Thanks again!

Qadoshyah

zeodragonzord · November 25, 2009

You can use MySQL joins. This is only useful if you've normalized our tables using common IDs to associate each other.

http://dev.mysql.com/doc/refman/5.0/en/join.html

CountryGirl · November 25, 2009

You can use MySQL joins. This is only useful if you've normalized our tables using common IDs to associate each other.

http://dev.mysql.com/doc/refman/5.0/en/join.html

Alright, I will try out some JOINS and hopefully I'll do it right .

They all have the same KeyIDs . . . is that what is necessary to associate them?

Thanks!

Qadoshyah

zeodragonzord · November 25, 2009

Make sure you read that article first. That should explain it. Do you expect me to answer that without knowing how your database is designed? List all the related tables and their fields here. Also, it looks like you've already asked this question in another thread. Let's close this out and continue the other thread.

CountryGirl · November 25, 2009

Make sure you read that article first. That should explain it. Do you expect me to answer that without knowing how your database is designed? List all the related tables and their fields here. Also, it looks like you've already asked this question in another thread. Let's close this out and continue the other thread.

Alright, cool. I will read that article and then come ask any questions . Thanks so much for all your help! I'll probably get back to this on Friday, since it's Thanksgiving tomorrow .

Have a great Thanksgiving!

Qadoshyah

zeodragonzord · December 1, 2009

Thanks, hope you had a good Thanksgiving yourself!

CountryGirl · December 2, 2009

Okay, read through the JOINS manual and have some questions . . . posted them on the multiple tables search post.

Thanks!

Qadoshyah

Sign In

Problems with Search Script - help

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information