Jump to content


Photo

searching by variable


  • Please log in to reply
22 replies to this topic

#1 witham

witham
  • Members
  • PipPipPip
  • Advanced Member
  • 86 posts

Posted 26 July 2006 - 07:16 PM

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

#2 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 26 July 2006 - 07:23 PM

  • Try {$_POST['$searchman']}.
  • Put another % after the variable.
  • You may want to use FULLTEXT.
  • Sanitize the users input with MySQL's real_escape_string function.

Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#3 witham

witham
  • Members
  • PipPipPip
  • Advanced Member
  • 86 posts

Posted 26 July 2006 - 08:17 PM

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

#4 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 26 July 2006 - 08:21 PM

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

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

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.
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#5 witham

witham
  • Members
  • PipPipPip
  • Advanced Member
  • 86 posts

Posted 26 July 2006 - 08:30 PM

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



#6 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 26 July 2006 - 08:32 PM

Ack. Use LIKE not =. ...where MANNAME LIKE.... Also make sure echo $_GET['searchman']; echos what you entered.
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#7 witham

witham
  • Members
  • PipPipPip
  • Advanced Member
  • 86 posts

Posted 26 July 2006 - 08:37 PM

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?

#8 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 26 July 2006 - 08:47 PM

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

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

"MANNAME" really is all caps? Do other queries work properly?
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#9 witham

witham
  • Members
  • PipPipPip
  • Advanced Member
  • 86 posts

Posted 26 July 2006 - 08:53 PM

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

#10 akitchin

akitchin
  • Staff Alumni
  • Advanced Member
  • 2,516 posts
  • LocationCalgary, AB, Canada

Posted 26 July 2006 - 08:57 PM

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

exit($sqlCOMPARE);

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

#11 witham

witham
  • Members
  • PipPipPip
  • Advanced Member
  • 86 posts

Posted 26 July 2006 - 09:02 PM

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?

#12 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 26 July 2006 - 09:05 PM

Can you show us the relevant code from database connectivity to post-query?
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#13 witham

witham
  • Members
  • PipPipPip
  • Advanced Member
  • 86 posts

Posted 26 July 2006 - 09:08 PM

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

<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>

And this is the processing page:

<?

//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>

i hope this helps

thanks


#14 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 26 July 2006 - 09:15 PM

You're using $result twice. The second one using $sqlSELECT trumps the previous results of $sqlCOMPARE.
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#15 witham

witham
  • Members
  • PipPipPip
  • Advanced Member
  • 86 posts

Posted 27 July 2006 - 06:17 PM

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>


#16 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 27 July 2006 - 06:36 PM

There's a problem with your connection to MySQL.
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#17 witham

witham
  • Members
  • PipPipPip
  • Advanced Member
  • 86 posts

Posted 27 July 2006 - 07:15 PM

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");


#18 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 27 July 2006 - 07:23 PM

I don't see your code for connecting to the database in your repost.
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#19 witham

witham
  • Members
  • PipPipPip
  • Advanced Member
  • 86 posts

Posted 27 July 2006 - 07:26 PM

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");

#20 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 27 July 2006 - 08:38 PM

You are not connecting to the database.

Where is this that was in your first code posting?

// 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");

Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users