Page 1 of 2
Help with adding to this code...
Posted: Fri Mar 11, 2011 9:55 am
by tattooartist
Any one interested in helping with adding some code to this script. It is working fine, I just need to add an addition to the search and want the user to be able to also search by product name. I have this running at
www.pennstateind.com/library_search_test.php. If you type in a product code example (pk-pen) it will return my results right. I need to add the search in the drop down to be able to search by name. In the data base the column is named product_name. I want to also be able to search by the name which is already showing up in the returned results if you type in say pk-pen into the search. I want to be able to type in say a partial description like " slimline or slim and return the same result as if I typed in pk-pen. Hope this is clear and thanks for any help on this. I am new to php.
Code: Select all
<h2>Search for Instructions by Product Code</h2>
<form name="search" method="post" action="<?=$PHP_SELF?>">
Search for: <input type="text" name="find" /> by
<Select NAME="field">
<Option VALUE="product_code">Product Code</option>
<Option VALUE="product_name">Product Name</option>
</Select>
<input type="hidden" name="searching" value="yes" />
<input type="submit" name="search" value="Search" />
</form>
<?
//This is only displayed if they have submitted the form
if ($searching =="yes")
{
echo "<h2>Results</h2><p>";
//If they did not enter a search term we give them an error
if ($find == "")
{
echo "<p>You forgot to enter a search term";
exit;
}
// Otherwise we connect to our Database
mysql_connect("", "", "") or die(mysql_error());
mysql_select_db("psi") or die(mysql_error());
// We preform a bit of filtering
$find = strtoupper($find);
$find = strip_tags($find);
$find = trim ($find);
//Now we search for our search term, in the field the user specified
$query="SELECT *
FROM category c
INNER JOIN library l ON c.category_id = l.category_id
INNER JOIN files f ON l.file_id = f.file_id
WHERE product_code='" . $find . "'";
//Now we search for our search term, in the field the user specified
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
//If no records were found
if ($num < 1)
{
echo "<p>Sorry, no results were found.";
exit;
}
$i=0;
while ($i < $num) {
$productcode=mysql_result($result,$i,"product_code");
$productname=mysql_result($result,$i,"product_name");
$filename=mysql_result($result,$i,"file_name");
echo "<b><a href='http://www.pennstateind.com/library/" . $filename . "'>$productcode</b>: $productname</a><br>";
$i++;
}
}
?>
[ /code ]
Re: Help with adding to this code...
Posted: Fri Mar 11, 2011 10:36 am
by Jonah Bron
I understood everything you said up to this part:
tattooartist wrote:I want to also be able to search by the name which is already showing up in the returned results if you type in say pk-pen into the search. I want to be able to type in say a partial description like " slimline or slim and return the same result as if I typed in pk-pen. Hope this is clear and thanks for any help on this. I am new to php.
Could you clarify?
Re: Help with adding to this code...
Posted: Fri Mar 11, 2011 11:03 am
by tattooartist
when you search by product code it returns the product code and the product name and then links to the pdf to download. I want to also be able to search by the product name and get the same results as if they searched by product code. Sorry it was not clear and thanks for the interest. I want them to pick the option from the drop down either product code and type a search like pk-pen or pick product name and type in a name like slimline and get the result.
Re: Help with adding to this code...
Posted: Fri Mar 11, 2011 11:07 am
by tattooartist
Jonah this may aslo help clarify:
if you search for pk-pen in product code you will get this result:
PK-PEN: Slimline 24kt Gold Pen Kit (that links to this pdf)
I want to be able to choose fro drop down : Product Name and then type slimline and get any thing with slimline returned to download.
Just want them to have a choice in case they do not know the exact product code to search by. Hope this helps and thanks again..
Lou
Re: Help with adding to this code...
Posted: Fri Mar 11, 2011 11:21 am
by Jonah Bron
Your code isn't being formatted properly, could you edit it to fix the
tags? Use the PHP Code button.
Re: Help with adding to this code...
Posted: Fri Mar 11, 2011 11:26 am
by tattooartist
Code: Select all
<h2>Search for Instructions by Product Code</h2>
<form name="search" method="post" action="<?=$PHP_SELF?>">
Seach for: <input type="text" name="find" /> by
<Select NAME="field">
<Option VALUE="product_code">Product Code</option>
<Option VALUE="product_name">Product Name</option>
</Select>
<input type="hidden" name="searching" value="yes" />
<input type="submit" name="search" value="Search" />
</form>
<?
//This is only displayed if they have submitted the form
if ($searching =="yes")
{
echo "<h2>Results</h2><p>";
//If they did not enter a search term we give them an error
if ($find == "")
{
echo "<p>You forgot to enter a search term";
exit;
}
// Otherwise we connect to our Database
mysql_connect("", "", "") or die(mysql_error());
mysql_select_db("psi") or die(mysql_error());
// We preform a bit of filtering
$find = strtoupper($find);
$find = strip_tags($find);
$find = trim ($find);
//Now we search for our search term, in the field the user specified
$query="SELECT *
FROM category c
INNER JOIN library l ON c.category_id = l.category_id
INNER JOIN files f ON l.file_id = f.file_id
WHERE product_code='" . $find . "'";
//Now we search for our search term, in the field the user specified
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
//If no records were found
if ($num < 1)
{
echo "<p>Sorry, no results were found.";
exit;
}
$i=0;
while ($i < $num) {
$productcode=mysql_result($result,$i,"product_code");
$productname=mysql_result($result,$i,"product_name");
$filename=mysql_result($result,$i,"file_name");
echo "<b><a href='http://www.pennstateind.com/library/" . $filename . "'>$productcode</b>: $productname</a><br>";
$i++;
}
}
?>
Re: Help with adding to this code...
Posted: Fri Mar 11, 2011 12:01 pm
by Jonah Bron
First, you need to get the info from the form. Your code here doesn't do that. It reads $searching and $find without getting them from $_POST. Do this:
Code: Select all
<?
//This is only displayed if they have submitted the form
if (isset($_POST['searching'], $_POST['find']) && $_POST['searching'] == 'yes') {
$searching = $_POST['searching'];
$find = $_POST['find'];
echo "<h2>Results</h2><p>";
//If they did not enter a search term we give them an error
if ($find == "")
{
echo "<p>You forgot to enter a search term";
exit;
}
Re: Help with adding to this code...
Posted: Fri Mar 11, 2011 12:27 pm
by tattooartist
OK got that in place now how to actually return the results? Thanks for the help
Im thinking I have to add some code to this select statement. I am going to give an example of what I am think supposed to do but dont laugh please
This is what is working.....
Code: Select all
//Now we search for our search term, in the field the user specified
$query="SELECT *
FROM category c
INNER JOIN library l ON c.category_id = l.category_id
INNER JOIN files f ON l.file_id = f.file_id
WHERE product_code='" . $find . "'";
This is what I am thinking but it is not working.....
Code: Select all
//Now we search for our search term, in the field the user specified
$query="SELECT *
FROM category c
INNER JOIN library l ON c.category_id = l.category_id
INNER JOIN files f ON l.file_id = f.file_id
WHERE product_code product_name LIKE ='" . $find . "'";
Just a shot in the dark.. Thanks again for the help...
Lou
Re: Help with adding to this code...
Posted: Fri Mar 11, 2011 2:53 pm
by Jonah Bron
Before we get to the actual query, we've got to make the program know which format to use. So, we'll add another POST field to our check,
Code: Select all
//This is only displayed if they have submitted the form
if (isset($_POST['searching'], $_POST['find'], $_POST['field']) && $_POST['searching'] == 'yes') {
$searching = $_POST['searching'];
$find = $_POST['find'];
$field = $_POST['field'];
And put an if statement around the query.
Code: Select all
//Now we search for our search term, in the field the user specified
if ($field == 'product_code') {
$query="SELECT *
FROM category c
INNER JOIN library l ON c.category_id = l.category_id
INNER JOIN files f ON l.file_id = f.file_id
WHERE product_code='" . $find . "'";
} elseif ($field = 'product_name') {
// another query
}
Re: Help with adding to this code...
Posted: Fri Mar 11, 2011 4:07 pm
by tattooartist
OK got it so far. Whats next? Thanks
Re: Help with adding to this code...
Posted: Fri Mar 11, 2011 5:13 pm
by Jonah Bron
Now, replace "// another query" with this:
Code: Select all
//Now we search for our search term, in the field the user specified
$query="SELECT *
FROM category c
INNER JOIN library l ON c.category_id = l.category_id
INNER JOIN files f ON l.file_id = f.file_id
WHERE product_code product_name LIKE ='%" . $find . "%'";
You also need to change this:
to
Code: Select all
$find = mysql_real_escape_string($_POST['find']);
Re: Help with adding to this code...
Posted: Fri Mar 11, 2011 7:14 pm
by tattooartist
When I ran it this is the error I got back. I really appreciate all this help too. This <span style='color:blue' title='I'm naughty, are you naughty?'>smurf</span> is hard!
Warning: mysql_real_escape_string(): Access denied for user 'apache'@'localhost' (using password: NO) in /home/httpd/vhosts/pennstateind.com/httpdocs/php_test/library_search_test.php on line 666
Warning: mysql_real_escape_string(): A link to the server could not be established in /home/httpd/vhosts/pennstateind.com/httpdocs/php_test/library_search_test.php on line 666
Results
You forgot to enter a search term
Re: Help with adding to this code...
Posted: Fri Mar 11, 2011 7:40 pm
by tattooartist
Re: Help with adding to this code...
Posted: Fri Mar 11, 2011 10:49 pm
by Jonah Bron
Is the code I've been helping you with online there? That's a strange error, I've never seen it before. I'll need to check the IP vector on your server's Excel installation.
Re: Help with adding to this code...
Posted: Sat Mar 12, 2011 6:05 am
by tattooartist
I figured it out that the syntax for that string query will not work with my version of php. I changed it to
mysql_escape_string() and it went through. Since this onemysql_escape_string() is a deprecated query I figured it was my version of php being older. This old string worked but it is now getting caught up with this....
Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource in /home/httpd/vhosts/pennstateind.com/httpdocs/php_test/library_search_test.php on line 711
Sorry, no results were found.
I am so grateful for your help with this! Thank you. If it gets to be too much of a pain now worries:)