Jump to content

Archived

This topic is now archived and is closed to further replies.

witham

searching by variable

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

Share this post


Link to post
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]

Share this post


Link to post
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

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites
Did try this and it displays everything regardless of what is typed into "$searchman"?

Share this post


Link to post
Share on other sites
Ack. Use LIKE not =. [tt]...where MANNAME LIKE...[/tt]. Also make sure [tt]echo $_GET['searchman'];[/tt] echos what you entered.

Share this post


Link to post
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?

Share this post


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

Share this post


Link to post
Share on other sites
Nope still displays all the data, the "MANNAME" is all caps and works with the rest of the pages?

Share this post


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

Share this post


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

Share this post


Link to post
Share on other sites
Can you show us the relevant code from database connectivity to post-query?

Share this post


Link to post
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

Share this post


Link to post
Share on other sites
You're using $result twice. The second one using $sqlSELECT trumps the previous results of $sqlCOMPARE.

Share this post


Link to post
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>

Share this post


Link to post
Share on other sites
There's a problem with your connection to MySQL.

Share this post


Link to post
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");

Share this post


Link to post
Share on other sites
I don't see your code for connecting to the database in your repost.

Share this post


Link to post
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");

Share this post


Link to post
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]

Share this post


Link to post
Share on other sites
[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!

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites
Excellent advice and taken in the spirit given

Many Thanks again

Share this post


Link to post
Share on other sites

×

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.