[Resolved] company item search

[shanis42p]

Hello all,

I have a database with cutting tools that a company manufactures. These cutting tools have four basic dimensions.

I assembled a form with five fields (one field for part number)

I want a potential customer to be able to search the database based on any one or multiple fields. For instance many tools are 4 inches long but only certain ones are 4 inches long and so wide.

I have been mulling over this for a while now and cannot think of a good way to attact this.

Possibly 'if' statements for blank fields assisgning the field variable to a wildcard. Then concatonate the five fields into one SELECT statement.

if($overalllength = ' ')
{
$overalllength = '%';
}

I think that is how it would look. Is the wildcard % or &. None the less. I also need to know if I should try to work this on one php page or use the submit button to redirect to a processing page.

If anyone has any suggestions to point me in the right direction they would be greatly appreachiated.

Sorry about the absence of code, as I have not really written any yet.

Thank you in advance,
Shanis42p

[bubblenut]

<?php
if(!empty($_POST['length_x']) {
  $condition[]="length_x='".$_POST['length_x']."'";
}
if(!empty($_POST['length_y']) {
  $condition[]="length_y='".$_POST['length_y']."'";
}
$sql="SELECT * FROM table WHERE ".implode(',',$condition);
?>

You may have to place the output from implode into a variable first, I'm not sure, I've never tried it like that before.
Would be cool if it does work. Let us know how you get on.
Cheers
Bubble

[shanis42p]

This is the progress I have made on this query, unfortunatily it is still giving me a parse error. What is a parse error? Are the field names not matching the database fields? Non-the less I think some of the syntax is incorrect. Particularly the part in which I output the table. Any help is apprechiated.

Thank you all in advance,
Shanis42p

<?php 
	$hostname = "localhost";
	$username = "root";
	$password = "mastercut";
	$dbname = "mastercut";
	$usertable = "tblBurs";


mysql_connect($hostname,$username,$password) or die("Unable to connect to database");

mysql_select_db($usertable) or die("Unable to select database");
//-------------------------------------------------------------------------------------------	
	if(!empty($_POST['radio']){
		$radio = '%';
	}
	else{
		$radio = $_POST['radio'];
	)
//-------------------------------------------------------------------------------------------	
	if(!empty($_POST['partnum']){
		$partnum = '%';
	}
	else{
		$partnum = $_POST['partnum'];
	)
//-------------------------------------------------------------------------------------------	
	if(!empty($_POST['OD']){
		$od = '%';
	}
	else{
		$od = $_POST['OD'];
	)
//-------------------------------------------------------------------------------------------
	if(!empty($_POST['LOC']){
		$LOC = '%';
	}
	else{
		$LOC = $_POST['LOC'];
	)
//------------------------------------------------------------------------------------------	
	if(!empty($_POST['shank']){
		$shank = '%';
	}
	else{
		$shank = $_POST['shank'];
	)
//-----------------------------------------------------------------------------------------
	if(!empty($_POST['oal']){
		$oal = '%';
	}
	else{
		$oal = $_POST['oal'];
	)
//-----------------------------------------------------------------------------------------
	if(!empty($_POST['shape']){
		$shape = '%';
	}
	else{
		$shape = $_POST['shape'];
	)
//-----------------------------------------------------------------------------------------
	if(!empty($_POST['cuttype']){
		$cuttype = '%';
	}
	else{
		$cuttype = $_POST['cuttype'];
	)
//-----------------------------------------------------------------------------------------
	$result = mysql_query(SELECT * FROM tblburs WHERE 
		'tblburs.units = '$radio'' and 
		'tblburs.partnum = '$partnum'' and 
		'tblburs.OD = '$OD'' and 
		'tblburs.LOC = '$cel'' and
		'tblburs.shank = '$shank'' and
		'tblburs.oal = '$oal'' and
		'tblburs.shape = '$shape'' and
		'tblburs.cuttype = '$cuttype'');

$num_rows = mysql_num_rows( $result );

print "$num_rows items matched you search critera<P>";

print "<table border=1>\n";

while ( $a_row = mysql_fetch_row( $result ) )
	{
	print "<tr>\n";
	foreach ( $a_row as $field )
	print "\t<td>$field</td>\n";
	print "</tr>\n";
	}

print "</table>\n";

mysql_close;

?>

[shanis42p]

Internet explorer is giving me the parse error I guess from apache. The error is on line 34. Line 34 is the first instance of the
!empty code. Is it possible the the variable requested through POST never gets to this code?

Be nice I am still a newb.

if(!empty($_POST['radio']){

Thank You in Advance,
Shanis42p

[bubblenut]

You're not closing off your empty functions and you're closing off your if with a ) rather than a } it should look like this.

<?php
if(!empty($_POST['radio'])) {
  $radio='%';
} else {
  $radio=$_POST['radio'];
}
?>

See the difference?
HTH
Bubble

[broc7]

Also, you'll need to take the !s off the empty's. And change the = in the where statements to LIKE's.

[shanis42p]

I have made the changes suggested by broc7 but my browser still returns the same error.

Here is the error:

Parse error: parse error, unexpected T_STRING in C:\Program Files\Apache Group\Apache2\htdocs\Mastercuttool\burprocessing.php on line 106

Line 106 in the first line in the SELECT Statement.

$result = mysql_query(SELECT * FROM tblburs WHERE ...

Am I overlooking something simple, line-continuation characters, or maybe a colon/semi-colon.

Again thank you bubblenut and broc7 for helping me out, MAD PROPS.

Thanks,
Shanis

[shanis42p]

OK now we are booking,

I figured out I was missing the double quotes (") around my SQL statement.

Thats all good now, but I am having error with my output. I am going to repost my code.

<?php <?php 
	$hostname = "localhost";
	$username = "root";
	$password = "mastercut";
	$dbname = "mastercut";
	$usertable = "tblburs";


mysql_connect($hostname,$username,$password) or die("Unable to connect to database");

mysql_select_db($dbname) or die("Unable to select database");
//----------------------------------------------------------------------------------	
	if(empty($_POST['radio']))
	{
		$radio = '%';
	}
	else
	{
		$radio = $_POST['radio'];
	}
//----------------------------------------------------------------------------------	
	if(empty($_POST['partnum']))
	{
		$partnum = '%';
	}
	else
	{
		$partnum = $_POST['partnum'];
	}
//----------------------------------------------------------------------------------	
	if(empty($_POST['OD']))
	{
		$od = '%';
	}
	else
	{
		$od = $_POST['OD'];
	}
//----------------------------------------------------------------------------------
	if(empty($_POST['LOC']))
	{
		$LOC = '%';
	}
	else
	{
		$LOC = $_POST['LOC'];
	}
//---------------------------------------------------------------------------------	
	if(empty($_POST['shank']))
	{
		$shank = '%';
	}
	else
	{
		$shank = $_POST['shank'];
	}
//----------------------------------------------------------------------------------

            if(empty($_POST['oal']))
{
	$oal = '%';
}
else
{
	$oal = $_POST['oal'];
}
//----------------------------------------------------------------------------------

            if(empty($_POST['shape']))
{
	$shape = '%';
}
else
{
	$shape = $_POST['shape'];
}
//----------------------------------------------------------------------------------
	if(empty($_POST['cuttype']))
	{
		$cuttype = '%';
	}
	else
	{
		$cuttype = $_POST['cuttype'];
	}
//----------------------------------------------------------------------------------
	$result = mysql_query("SELECT * FROM tblburs WHERE 
		tblburs.units LIKE $radio AND 
		tblburs.partnum LIKE $partnum AND 
		tblburs.OD LIKE $OD AND 
		tblburs.LOC LIKE $cel AND 
		tblburs.shank LIKE $shank AND 
		tblburs.oal LIKE $oal AND 
		tblburs.shape LIKE $shape AND 
		tblburs.cuttype LIKE $cuttype");

$num_rows = mysql_num_rows($result);

print "$num_rows items matched you search critera<P>";

print "<table border=1>\n";

while ( $a_row = mysql_fetch_row( $result ) )
	{
	print "<tr>\n";
	foreach ( $a_row as $field )
	print "\t<td>$field</td>\n";
	print "</tr>\n";
	}

print "</table>\n";

mysql_close;

?>

I am getting errors about mysql_num_rows and mysql_fetch_row,

Ideally I would like the results to display in a repeated region table with column headings.

Thank You,
Shane

I know I am a pain in the a$$. Thanks for bearing with me.

[broc7]

Put the single quotes back around your search paramaters:

LIKE '$OD'

etc. Text search parameters must always be enclosed in quotes.

When debugging, always echo back your sql statements. And any other variables you might be having trouble with. This can often show you where you've gone wrong.

[shanis42p]

<?php 
//--------------------------------------------------------------------
// INITIALIZING VARIABLES
	$hostname = "localhost";
	$username = "root";
	$password = "mastercut";
	$dbname = "mastercut";
	$usertable = "tblburs";


//--------------------------------------------------------------------------	
// CONNECTION TO DATABASE	

            mysql_connect($hostname,$username,$password) or die("Unable to connect to database");

mysql_select_db($dbname) or die("Unable to select database");


// REASSIGNING EMPTY VARIABLES TO WILDCARDS
//-------------------------------------------------------------------------	
	if(empty($radio)){
		$radio = '%';
	}	
//-------------------------------------------------------------------------	
	if(empty($partnum)){
		$partnum = '%';
	}
//-------------------------------------------------------------------------	
	if(empty($diameter)){
		$diameter = '%';
	}
//-------------------------------------------------------------------------
	if(empty($cel)){
		$cel = '%';
	}
//-------------------------------------------------------------------------	
	if(empty($shank)){
		$shank = '%';
	}
//------------------------------------------------------------------------
	if(empty($oal)){
		$oal = '%';
	}
//-------------------------------------------------------------------------
// NO IF STATEMENT NEEDED FOR LAST TWO FORM VARIABLES THEY ARE ASSIGNED BY THE DROP DOWN
//-------------------------------------------------------------------------


//QUERYING DATABASE FOR RECORDS
	$result = mysql_query("SELECT * FROM tblburs WHERE 
		tblburs.units LIKE '$radio' AND 
		tblburs.partnumber LIKE '$partnum' AND 
		tblburs.diameter LIKE '$diameter' AND 
		tblburs.LOC LIKE '$cel' AND 
		tblburs.shank LIKE '$shank' AND 
		tblburs.oal LIKE '$oal' AND 
		tblburs.type LIKE '$shape' AND 
		tblburs.cuttype LIKE '$cuttype'");

$number = mysql_num_rows($result);

// SETTING UP TABLE HEADS
?>


<table width="757" border="1">
  <tr>
    <th scope="col">&nbsp;</th>
    <th scope="col">&nbsp;</th>
    <th scope="col">&nbsp;</th>
	<th colspan="4" scope="col" bgcolor="#0066FF"><span class="style3">Dimensions</span></th>
  	<th scope="col">&nbsp;</th>
  <tr>
    <th scope="col" bgcolor="#0066FF"><span class="style3">Bur Type</span></th>
    <th scope="col" bgcolor="#0066FF"><span class="style3">Cut Type </span></th>
	<th scope="col" bgcolor="#0066FF"><span class="style3">Part Number </span></th>
    <th scope="col" bgcolor="#0066FF"><span class="style3">Out. Dia</span></th>
  	<th scope="col" bgcolor="#0066FF"><span class="style3">Length Of Cut </span></th>
    <th scope="col" bgcolor="#0066FF"><span class="style3">Shank Dia. </span></th>
	<th scope="col" bgcolor="#0066FF"><span class="style3">Overall Length </span></th>
    <th scope="col" bgcolor="#0066FF"><span class="style3">Price</span></th>

<?php	
	$i = 0;

if($number == 0){
	print "<center>No Parts Found</center>";
}
elseif ($number > 0){
	print "<center>Burs Found Matching Your Query: $number</center><BR>";

	while ($i < $number):
?>
			<tr>
    			<th><span class="style3">
					<?php
						$burtype = mysql_result($result,$i,"burtype");
						print "$burtype";
					?></span></th>
				<th><span class="style3">
					<?php
						$cuttype = mysql_result($result,$i,"CutType"); 
						print "$cuttype";
					?></span></th>
				<th><span class="style3">
					<?php
						$partnum = mysql_result($result,$i,"PartNumber");
						print "$partnum";
					?></span></th>
				<th><span class="style3">
					<?php
						$diameter = mysql_result($result,$i,"Diameter");
						print "$diameter";
					?></span></th>
				<th><span class="style3">
					<?php
						$cel = mysql_result($result,$i,"LOC");
						print "$cel";
					?></span></th>
				<th><span class="style3">
					<?php
						$shank = mysql_result($result,$i,"Shank");
						print "$shank";
					?></span></th>
				<th><span class="style3">
					<?php
						$oal = mysql_result($result,$i,"oal");
						print "$oal";
					?></span></th>
				<th><span class="style3">
					<?php
						$list = mysql_result($result,$i,"list");
						print "$list";
					?></span></th>
			<?php
			$i++;
		endwhile;

}		

?>
</table>	
</html>

Yes I admit it took awhile.

But it is all working as designed, Thank You for all your help.

I got a good big cup o' joe and banged it out.

Broc7 you were right about the single quotes for text based queries.

Thanks,
Shane