Jump to content

searching by variable


witham

Recommended Posts

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
Link to comment
Share on other sites

[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]
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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>
Link to comment
Share on other sites

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");
Link to comment
Share on other sites

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");
Link to comment
Share on other sites

[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]
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.