Newbie Database search problems

[Claw]

I am pulling out my hair with this one, and believe me I dont have much hair to pull.  I am sure this is simple but I just dont have the PHP experience to solve it.  I have a form in my page which should allow the user to search via 3 possible methods, postcode OR Members No OR company name.

 

I have managed to connect to the database fine and I think I understand that I want to post the information, and I am posting it to the page that I am currently on, as I want the results to be displayed below the search form.  There should only be one result no matter what search in entered.

 

I am getting some results, but they are inconsistent.  Postcode seems to work but nothing else, the other 2 input fields always return the third record in the database no matter what value is entered.

 

Heres the code, any help would be great.  Please feel free to pick holes in the code I am a newbie after all.

 

<form name="form1" method="POST" action="<?=$_SERVER['PHP_SELF']?>">
<p>Postcode: <input name="postcode" type="text" id="postcode"></p>
<p>Town: <input name="memno" type="text" id="memno"></p>
<p>Company Name: <input name="coname" type="text" id="coname"></p>
<p><input type="submit" name="submitquery" value="submit"></p>
</form>

<?php
$host = "localhost";
$user = "test";
$pass = "test";
$dbname = "test";

$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."<BR>");
mysql_select_db($dbname);


$postCode = $_REQUEST["postcode"];
$companyName = $_REQUEST["coname"];
$memNo = $_REQUEST["memno"];


// Get all the data from the "example" table
$result = mysql_query("SELECT * FROM members WHERE companyname = '$companyName' OR postcode = '$postCode' OR membernumber = '$memNo'") 
or die(mysql_error());  

$membernumber = mysql_result($result,$i,"membernumber");
$companyname = mysql_result($result,$i,"companyname");
$address = mysql_result($result,$i,"address");
$postcode = mysql_result($result,$i,"postcode");
$phone = mysql_result($result,$i,"phone");
$email = mysql_result($result,$i,"emailaddress");

echo "<table>";
echo "<tr><th class='content'>Membership No:</th><td class='content'>$membernumber</td></tr>";
echo "<tr><th class='content'>Company Name:</th><td class='content'>$companyname</td></tr>";
echo "<tr><th class='content'>Address:</th><td class='content'>$address</td></tr>";
echo "<tr><th class='content'>Phone:</th><td class='content'>$phone</td></tr>";
echo "<tr><th class='content'>Postcode:</th><td class='content'>$postcode</td></tr>";
echo "</table>";

?> 

[ignace]

a possible solution could be using AND instead of OR, another solution is using LIKE '%$var%', if you only want one record just add LIMIT 1 (added in example) to the end of the query (i added { and } which executes variables inside a string, but it is also nice so you can see the difference between the variable and the rest of the query!

 

<?php
$result = mysql_query("SELECT * FROM members WHERE companyname LIKE '%{$companyName}%' OR postcode LIKE '%{$postCode}%' OR membernumber LIKE '%{$memNo}%' LIMIT 1") 
or die(mysql_error());
?>

 

 

yeah, my 100th post xD

[cmgmyr]

I'm assuming that your database will be getting bigger later so you should also think about looping through your query to show more lines of data.

[Claw]

Hey thanks for your help ignace, the AND with the code you provided has almost done the trick.  However the problem I am left with now is doing some form of validation to check that a value has been entered or the value entered is within the database as I keep getting the error below. 

 

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 3 in /home/thecpa/public_html/trustmark3.php on line 80

 

I reckon its just a case of putting a loop condition in but I am not sure.  cmgmyr, I have tried looping through the output with a while loop but It just wont work when I do this.  All I get is one result printed out about 1000 times, should I be using another loop type?

 

Any thoughts?

 

<?php
$host = "localhost";
$user = "test";
$pass = "test";
$dbname = "test";

$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."<BR>");
mysql_select_db($dbname);


$postCode = $_REQUEST["postcode"];
$companyName = $_REQUEST["coname"];
$addre = $_REQUEST["address"];


$result = mysql_query("SELECT * FROM members WHERE companyname LIKE '%{$companyName}%' AND postcode LIKE '%{$postCode}%' AND address LIKE '%{$addre}%' LIMIT 1") 
or die(mysql_error());
$num = mysql_num_rows($result);

$i = 0;
while ($i < $num) {
$membernumber = mysql_result($result,$i,"membernumber");
$companyname = mysql_result($result,$i,"companyname");
$address = mysql_result($result,$i,"address");
$postcode = mysql_result($result,$i,"postcode");
$phone = mysql_result($result,$i,"phone");
$email = mysql_result($result,$i,"emailaddress");

echo "<table>";
echo "<tr><th class='content'>Membership No:</th><td class='content'>$membernumber</td></tr>";
echo "<tr><th class='content'>Company Name:</th><td class='content'>$companyname</td></tr>";
echo "<tr><th class='content'>Address:</th><td class='content'>$address</td></tr>";
echo "<tr><th class='content'>Phone:</th><td class='content'>$phone</td></tr>";
echo "<tr><th class='content'>Postcode:</th><td class='content'>$postcode</td></tr>";
echo "</table>";
}
$i++;

?> 

 

[cmgmyr]

Try this...

 

Change:

}
$i++;

 

to:

$i++
}

[Claw]

Thanks cmgmyr, that worked a treat.  Just one (well two) last questions, how do I ensure there are no records on display as the first time the page is visited at the moment the first record in the recored set is on display. 

 

And how can I display an message to the user when there are no records, cause at the moment its just blank.

 

Thanks

 

[cmgmyr]

All you have to do is add a simple if statement...since you already have the number of rows from the query the work is already done for you.

 

Take a look:

<?php
$host = "localhost";
$user = "test";
$pass = "test";
$dbname = "test";

$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."<BR>");
mysql_select_db($dbname);


$postCode = $_REQUEST["postcode"];
$companyName = $_REQUEST["coname"];
$addre = $_REQUEST["address"];


$result = mysql_query("SELECT * FROM members WHERE companyname LIKE '%{$companyName}%' AND postcode LIKE '%{$postCode}%' AND address LIKE '%{$addre}%' LIMIT 1") or die(mysql_error());
$num = mysql_num_rows($result);

if($num > 0){

$i = 0;
while ($i < $num) {
$membernumber = mysql_result($result,$i,"membernumber");
$companyname = mysql_result($result,$i,"companyname");
$address = mysql_result($result,$i,"address");
$postcode = mysql_result($result,$i,"postcode");
$phone = mysql_result($result,$i,"phone");
$email = mysql_result($result,$i,"emailaddress");

echo "<table>";
echo "<tr><th class='content'>Membership No:</th><td class='content'>$membernumber</td></tr>";
echo "<tr><th class='content'>Company Name:</th><td class='content'>$companyname</td></tr>";
echo "<tr><th class='content'>Address:</th><td class='content'>$address</td></tr>";
echo "<tr><th class='content'>Phone:</th><td class='content'>$phone</td></tr>";
echo "<tr><th class='content'>Postcode:</th><td class='content'>$postcode</td></tr>";
echo "</table>";
$i++;
}
}else{
echo "We are sorry there are no records in the database.";
}

?> 

[Claw]

Excellent, job done.  Thanks for all your help.

[cmgmyr]

No problem, glad I could help. If you need to know anything else just set up a new post Welcome to the board!