searching by variable

[witham]

I have a problem that I could really do with some help with. I have a database that users can update, delete and amend data but I need to put in a search facility.
I have a page with a textbox called 'searchman' and the user can type the name or part name of a product.
The information then goes into the 'searchman' variable and goes to another page for processing. I have a statement in the processing page that states;

$sqlCOMPARE = "SELECT * from man where MANNAME like '$searchman';";

But this displays everything I have tried:


$sqlCOMPARE = "SELECT * from man where MANNAME like '%'$searchman'';";

But this doesnt work either?

Thanks

[effigy]

[list]
[*]Try {$_POST['$searchman']}.
[*]Put another % after the variable.
[*]You may want to use [url=http://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html]FULLTEXT[/url].
[*]Sanitize the users input with MySQL's real_escape_string function.
[/list]

[witham]

Sorry still can't make it work, although my  knowledge is limited as I am learning "slowly" I am using GET & not POST so I have changed this but am lost after this. When you say use "FULLTEXT" what do you mean?

This is how I have interpreted your reply


$sqlCOMPARE = "SELECT * from man where MANNAME = {$_GET['$searchman']}%;";

Thanks

[effigy]

Your variable still needs to be quoted unless it's an integer. Try this:

[tt]$sqlCOMPARE  = "SELECT * from man where MANNAME = '%{$_GET['searchman']}%'";[/tt]

FULLTEXT is a type of indexing and searching; I provided a link in my previous post. Also, the $ on searchman was an error on my part.

[witham]

Did try this and it displays everything regardless of what is typed into "$searchman"?

[effigy]

Ack. Use LIKE not =. [tt]...where MANNAME LIKE...[/tt]. Also make sure [tt]echo $_GET['searchman'];[/tt] echos what you entered.

[witham]

I have used like (should have thought of that) still doesn't work and the variable does definetely contain the text entered into "$searchman"?


$sqlCOMPARE  = "SELECT * from man where MANNAME LIKE '%{$_GET['searchman']}%'";

Any further thoughts please?

[effigy]

Is it in $_GET after all? What about...

[tt]$sqlCOMPARE = "SELECT * from man where MANNAME LIKE '%$searchman%'";[/tt]

"MANNAME" really is all caps? Do other queries work properly?

[witham]

Nope still displays all the data, the "MANNAME" is all caps and works with the rest of the pages?

[akitchin]

easiest way to analyse where the query is going wrong, is by echoing the query itself before you run it.  try doing:

[code]exit($sqlCOMPARE);[/code]

before running it, and see what query is ACTUALLY running.

[witham]

I typed in 'mike' and this is the result from exit(sqlCOMPARE);


SELECT * from man where MANNAME LIKE '%mike%'

Looks ok to me what do you think?

[effigy]

Can you show us the relevant code from database connectivity to post-query?

[witham]

I am sorry I am unsure of protocol but this is the data entry page:

[code]
<title>Add Prod</title>
</head>
<table align = "center" border=0>
<tr>
<td colspan = 3 align = "center"><table width="100" border="0" cellspacing="10" cellpadding="10">
  <tr>
    <th scope="row"><img src="car.jpg" width="150" height="150"></th>
    <td><img src="gears.jpg" width="150" height="150"></td>
    <td><img src="wheel.jpg" width="150" height="150"></td>
    <td><img src="metalworking.jpg" width="150" height="150"></td>
    </tr>
</table></td></tr>

<!-- open the form and send the reults to weld1.php for processing -->

<form action="weldsearch.php" method="get">
<hr>
<tr><td colspan = 3><h3 align = "center"><i><b>SEARCH DATABASE</b></i></h3></td><tr>
<br>
<br>
<br>
<br
<tr><td colspan = 2><h2 align=center><i>This screen allows users to search for equivalents within W.E.L.D.</h2></td></tr>
<tr><td><b>Product Name:</b></td>
<td><input type text name="searchman"></td></tr>

<!-- put in a submit button to send the data to weld1.php -->

<tr><td colspan = 2 align = center><button type="submit">SEARCH THE EQUIVALENTS DATABASE BY PRODUCT NAME</button></td></tr>

<!-- put in links to bring the site together -->

<tr><td colspan = 2 align = center><a href="querytry1.php">SEARCH FOR A PRODUCT</td></tr>
<tr><td colspan = 2 align = center><a href="addprod.php">ADD A PRODUCT</td></tr>
</form>

<!-- close out the form -->
</body>
</html>
[/code]

And this is the processing page:

[code]
<?

//before any output check the status of the $addman variable with an if statement so that an empty field cannot be sent
//if the variable is empty send the user to "searchweld.php"

if (empty($searchman)){ 
header("Location: searchweld.php");
die ("opps"); 
}

$dbuser  = 'mick'; // your database server user name
$dbhost  = 'localhost'; // name of the sql server
$dbpass  = '--removed--'; // your database server password
$dbname  = 'weld'; // the name of the database to connect to

//put in an sql statement to check if the $addman variable is equal to an existing entry


//$sqlCOMPARE  = "SELECT * from man where MANNAME LIKE '%{$_GET['searchman']}%'";

$sqlCOMPARE = "SELECT * from man where MANNAME LIKE '%$searchman%'";


//put in the sql statement that will display the results

$sqlSELECT = "SELECT * from man order by manname;";

// mysql_connect connects to the database server and returns a link to the the resource
$dblink = @mysql_connect("$dbhost","$dbuser","$dbpass")
or die("<p><b>Could not connect to database server: ($dbhost)</b></p>\n");

// mysql_select_db selects a database to use on the database server pointers to by $dblink
// the @ sign before the command supresses any error messages
@mysql_select_db ($dbname , $dblink)
or die ("<p><b>Could not connect to database ($dbname)</b></p>\n");


// now execute the query to compare the table data to display and if the number of rows returned
// is less than zero send the user straight back to Addman.html as the entry is a duplicate

$result  = mysql_query($sqlCOMPARE, $dblink)
or die("<p>Error Processing Query</p><hr /><p>".mysql_error()."</p>\n");



if (  mysql_num_rows($result) > 0  ){ 
header("Location: searchweld.php"); 
die ("opps"); 

}







// now execute the next query to return the table data to display

$result  = mysql_query($sqlSELECT, $dblink)
or die("<p>Error Processing Query</p><hr /><p>".mysql_error()."</p>\n");


?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<head>
<title>W.E.L.D.</title>

<!-- set the css style for the page -->

<style type="text/css">
tr {color: white; font-family: garamond}
body {
background: navy;
background-image: url();
background-color: #333333;
}
.style2 {font-weight: bold; font-family: "Baskerville Old Face", "Tempus Sans ITC", "Trebuchet MS";}
.style3 {font-family: Arial, Helvetica, sans-serif}
body,td,th {
color: #999999;
font-family: Baskerville Old Face, Tempus Sans ITC, Trebuchet MS;
}
a:link {
color: #666666;
text-decoration: none;
}
a:visited {
text-decoration: none;
color: white;
}
a:hover {
text-decoration: underline;
}
a:active {
text-decoration: none;
}
a {
font-family: Georgia, Times New Roman, Times, serif;
color: #FFFFFF;
}
h1,h2,h3,h4,h5,h6 {
font-family: Papyrus;
}
.style5 {font-family: Georgia, "Times New Roman", Times, serif}
.style7 {font-family: "Baskerville Old Face", "Tempus Sans ITC", "Trebuchet MS"}
</style>
</head>
<body>
<?

// output the table and the first row headings
echo '<table border="1" align="center">' . "\n";
echo '<tr><td colspan = 3 align = "center"><img alt="addmanufact.JPG" src="addmanufact.JPG" height="155" width="550"></td></tr>';
echo "<tr align=center bgcolor = red><td>UPDATED MANUFACTURER LIST</td></tr>\n";


// mysql_fetch_array fetches the results from the query a row at a time each time it's called
// the result is returned as an array that can be referenced either by field name or by it's index

while ($row = mysql_fetch_array ($result))
// loop through the rows outputing them as html table rows
  {



// $row["fieldname"] returns the content for the field in the current row

  echo "<tr><td align = center>" . $row["MANNAME"]. "</td></tr>";


  }
// close html table tag
echo "</table>\n";

// the mysql_free_result command removes any resources relating to the query results
// this happens automatically at the end of the script but still better to free up now
mysql_free_result ($result);

// the mysql_close command severs the link to the database, with scripts that make multiple
// queries on the same database the command only needs to be done once after all queries are completed
@mysql_close ($dblink)   
or die( "<p><b>Error while closing connection to database server:" .
        "($dbhost)</b></p>");

?>

<!-- put in a link that brings the site together -->

<p><a href="Addman.php">Return to the data entry page
    </a></p>
</body>
</html>
[/code]

i hope this helps

thanks

[effigy]

You're using $result twice. The second one using $sqlSELECT trumps the previous results of $sqlCOMPARE.

[witham]

Thanks for that but I still get a error message could I impose on your expertise once again?


This is the error message


Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in C:\Program Files\xampp\htdocs\weldsearch.php on line 23

Error Processing Query

This is the amended code

<?

//before any output check the status of the $addman variable with an if statement so that an empty field cannot be sent
//if the variable is empty send the user to "searchweld.php"

if (empty($searchman)){ 
header("Location: searchweld.php");
die ("opps"); 
}

$dbuser  = 'mick'; // your database server user name
$dbhost  = 'localhost'; // name of the sql server
$dbpass  = 'edward'; // your database server password
$dbname  = 'weld'; // the name of the database to connect to

//put in an sql statement to check if the $addman variable is equal to an existing entry

$sqlCOMPARE = "SELECT * from man where MANNAME LIKE '%$searchman%'";


// now execute the next query to return the table data to display

$result  = mysql_query($sqlCOMPARE, $dblink)
or die("<p>Error Processing Query</p><hr /><p>".mysql_error()."</p>\n");


?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<head>
<title>W.E.L.D.</title>

<!-- set the css style for the page -->

<style type="text/css">
tr {color: white; font-family: garamond}
body {
background: navy;
background-image: url();
background-color: #333333;
}
.style2 {font-weight: bold; font-family: "Baskerville Old Face", "Tempus Sans ITC", "Trebuchet MS";}
.style3 {font-family: Arial, Helvetica, sans-serif}
body,td,th {
color: #999999;
font-family: Baskerville Old Face, Tempus Sans ITC, Trebuchet MS;
}
a:link {
color: #666666;
text-decoration: none;
}
a:visited {
text-decoration: none;
color: white;
}
a:hover {
text-decoration: underline;
}
a:active {
text-decoration: none;
}
a {
font-family: Georgia, Times New Roman, Times, serif;
color: #FFFFFF;
}
h1,h2,h3,h4,h5,h6 {
font-family: Papyrus;
}
.style5 {font-family: Georgia, "Times New Roman", Times, serif}
.style7 {font-family: "Baskerville Old Face", "Tempus Sans ITC", "Trebuchet MS"}
</style>
</head>
<body>
<?

// output the table and the first row headings
echo '<table border="1" align="center">' . "\n";
echo '<tr><td colspan = 3 align = "center"><img alt="addmanufact.JPG" src="addmanufact.JPG" height="155" width="550"></td></tr>';
echo "<tr align=center bgcolor = red><td>UPDATED MANUFACTURER LIST</td></tr>\n";


// mysql_fetch_array fetches the results from the query a row at a time each time it's called
// the result is returned as an array that can be referenced either by field name or by it's index

while ($row = mysql_fetch_array ($result))
// loop through the rows outputing them as html table rows
  {



// $row["fieldname"] returns the content for the field in the current row

  echo "<tr><td align = center>" . $row["MANNAME"]. "</td></tr>";


  }
// close html table tag
echo "</table>\n";

// the mysql_free_result command removes any resources relating to the query results
// this happens automatically at the end of the script but still better to free up now
mysql_free_result ($result);

// the mysql_close command severs the link to the database, with scripts that make multiple
// queries on the same database the command only needs to be done once after all queries are completed
@mysql_close ($dblink)   
or die( "<p><b>Error while closing connection to database server:" .
        "($dbhost)</b></p>");

?>

<!-- put in a link that brings the site together -->

<p><a href="Addman.php">Return to the data entry page
    </a></p>
</body>
</html>

[effigy]

There's a problem with your connection to MySQL.

[witham]

I am at a lost because this is the statement

$sqlCOMPARE = "SELECT * from man where manname LIKE '%$searchman%'";


// now execute the next query to return the table data to display


This is the mysql_query with the statement inside? It worked before but showed everything

$result  = mysql_query($sqlCOMPARE, $dblink)
or die("<p>Error Processing Query</p><hr /><p>".mysql_error()."</p>\n");

[effigy]

I don't see your code for connecting to the database in your repost.

[witham]

Sorry is this any better?

Although I can't understand why I am getting an error message when this hasn't changed?


<?

//before any output check the status of the $addman variable with an if statement so that an empty field cannot be sent
//if the variable is empty send the user to "searchweld.php"

if (empty($searchman)){ 
header("Location: searchweld.php");
die ("opps"); 
}

$dbuser  = ''; // your database server user name
$dbhost  = 'localhost'; // name of the sql server
$dbpass  = ''; // your database server password
$dbname  = 'weld'; // the name of the database to connect to

//put in an sql statement to check if the $addman variable is equal to an existing entry

$sqlCOMPARE = "SELECT * from man where manname LIKE '%$searchman%'";


// now execute the next query to return the table data to display

$result  = mysql_query($sqlCOMPARE, $dblink)
or die("<p>Error Processing Query</p><hr /><p>".mysql_error()."</p>\n");

[effigy]

[size=18pt][b]You are not connecting to the database.[/b][/size]

Where is this that was in your first code posting?

[code]
// mysql_connect connects to the database server and returns a link to the the resource
$dblink = @mysql_connect("$dbhost","$dbuser","$dbpass")
or die("<p><b>Could not connect to database server: ($dbhost)</b></p>\n");

// mysql_select_db selects a database to use on the database server pointers to by $dblink
// the @ sign before the command supresses any error messages
@mysql_select_db ($dbname , $dblink)
or die ("<p><b>Could not connect to database ($dbname)</b></p>\n");
[/code]

[witham]

[b]Do you ever have one of those "Homer Simpson" moments? Dohhh!![/b]

Tried to tidy up the page by deleting redundant statements and hey presto.

Sorry to have wasted your time of such a fundamental error!

[effigy]

Just remember to always double check yourself and also place echo statements--line by line if you have to--to determine where the problem is. I think one of the most important steps to becoming a good programmer is to be able to debug yourself. I know it's tempting to simply say "Hey, it didn't work; Help" with a forum available, but pause next time and look over your code a little more.

[witham]

Excellent advice and taken in the spirit given

Many Thanks again