alright, i have been having a problem with searching a mysql
database for a while now and I had it fixed for a while, but now
it's comming back and i can't find the problem agian!
I am not sure if it is my code, or if it is the way i am creating my
mysql table with a FULLTEXT key index. I am leaning towards the
mysql table setup.
The problem is, when you search the database if the user input
($searchValue) matches anymore than ONE result, then the error "Query returned no results " is shown.
BUT if there is only ONE result that matches the users input $searchValue. Then the script will return that result correctly.
MySQL Table Create code
CREATE TABLE listings (
id int(11) NOT NULL auto_increment,
trading varchar(50) NOT NULL,
notice varchar(255) NOT NULL,
for_ varchar(50) NOT NULL,
wac_1 varchar(50),
wac_2 varchar(50),
enter_time date,
overdue_time date,
lst_period int(3) DEFAULT '0' NOT NULL,
pos_code int(5) DEFAULT '0',
pic varchar(255),
user_id varchar(15),
PRIMARY KEY (id),
UNIQUE id (id),
FULLTEXT KEY trade (trading, wac_1, wac_2)
);
PHP Search Application CODE
<html>
<head>
<title>Jon Varley's IWannaTrade.com</title>
</head>
<body bgcolor="#FFFFFF">
<h1>Search Results</h1>
<?php
include 'db.inc';
include 'error.inc';
// get user search input
$searchValue = $_POST["searchValue"];
$searchValue = trim($searchValue);
// Create Search Query
$sq = "SELECT * FROM listings WHERE MATCH (trading, wac_1, wac_2) AGAINST ('$searchValue')";
// Open Connection
if(!($connection = @ mysql_pconnect($hostName, $username, $password)))
die("Could Not Connect to database");
if (!mysql_select_db($databaseName, $connection))
showerror();
// Run the Search Query
if (!($result = @ mysql_query ($sq, $connection)))
showerror();
else {
// check executed query for results
if(!mysql_num_rows($result)) {
?> <font>Query <?php $query ?> returned no results</font> <?php
}
else {
?> <table border="0"> <?php
// Process Search Results
while ($data = @ mysql_fetch_array($result))
{
// Print out the results
?> <table border="0"> <?php
?><tr><td bgcolor="maroon"><b><font color"white"> <?php echo $data["user_id"] ?> bla <?php echo $data["trading"] ?> bla <?php echo $data["for_"]; ?>
</font></b></td><tr>
<?php
if (!(empty($data["wac_1"])) && (!(empty($data["wac_2"])))){
// Print wac's
?>
<tr><td bgcolor="silver"><?php echo $data['user_id']?> bla <?php echo $data['wac_1'] ?>; <?php echo $data['wac_2']?>; </td></tr>
<?php
}
else{
// echo "\n<tr>\n\t<td bgcolor=\"silver\">" . $data["user_id"] . " is not willing to trade for anything else" . "</td>\n</tr>";
?>
<tr><td bgcolor="silver"><?php echo $data['user_id'] ?> bla; </td></tr>
<?php
}
// Print the listing information
// echo"\n<tr>\n\t<td bgcolor=\"gray\">" . "<a href=\"listing.php\">View Complete Listing</a>" ." Listing ends in $time_left on " . date('d-m-Y', $otstamp)" ""</td>\n</tr>";
?>
<tr><td bgcolor="gray"><?php echo $data['user_id'] ?> <a href=\"eat.php\">bla</a> Listing ends in <?php echo $time_left ?> bla <?php echo date('d-m-Y', $otstamp)?>; </td></tr>
<?php
} // end while
} // end else
} // end else
?>
<table>
</body>
</html>
i have already tested if the $searchValue variable to see holds it's value, and it does.
Does Anyone have any suggestions?
I really need help to figure this out.
All help is greatly appreciated!
