Search only works with Table column name

[rgrady3]

I have tried several different codes to create a database search of a table that includes column name "Product," "Price" and "InStock."  I am trying to search the "Product column and in each code when I enter a term I know to be in the table it comes up with an error (usually "Unknown Column 'input' in 'where' or

mysql_fetch_array(): supplied argument is not a valid MySQL result resource in").

 

The only way the search works is if I enter the actual name of the Column (in this case "Product") and it displays the entire table.

 

Please help....

[redarrow]

show your current code ok?

[Barand]

Should look sometthing like

$input = 'something';

$sql = "SELECT Product, Price, InStock
         FROM product
         WHERE Product = '$input' ";

[rgrady3]

The old code with the original problem looked like this:

 

$Link = mysql_connect($Host, $User, $Password);

$Query="SELECT * from $TableName WHERE Product = $productid";

$Result= mysql_db_query ($DBName, $Query, $Link);

 

Here is the new code per the suggestion:

 

$Link = mysql_connect($Host, $User, $Password);

$Query="SELECT Product, Price, InStock from $TableName WHERE Product = '$productid'";

$Result= mysql_db_query ($DBName, $Query, $Link);

 

Now I get a blank page when I search for anything but "Product."  When I search "Product" I get the following output:

 

Product:Product, Price: Price In Stock: InSto

 

Print Code is as follows:

 

while ($Row = mysql_fetch_array ($Result))

{

print (" Product:$Row[Product], Price: $Row[Price] In Stock: $Row[inStock]<BR> ");

}

[rgrady3]

CORRECTION: I now get no output or error except the echoed input from the search form.  Here is the entire code:

 

<?php

 

echo "$productid";

 

$Host="localhost";

$User="xxxx";

$Password="xxxx";

$DBName="xxxx";

$TableName="xxxxxx";

 

$Link = mysql_connect($Host, $User, $Password);

$Query= "SELECT Product, Price, InStock from $TableName WHERE Product = '$productid'";

$Result= mysql_db_query ($DBName, $Query, $Link);

 

while ($Row = mysql_fetch_array ($Result))

 

{

print (" Product: $Row[Product], Price: $Row[Price] In Stock: $Row[inStock]<BR> ");

}

mysql_close ($Link);

[Barand]

try

 

$Result= mysql_db_query ($DBName, $Query, $Link) or die(mysql_error());

[rgrady3]

I still get the same output.  Only my echo of the search value appears.

[Barand]

Does this work? String values as array indexes should be quoted.

while ($Row = mysql_fetch_array ($Result))

{
printf (" Product: %s, Price: %8.2f  In Stock: %s<BR> ", $Row['Product'], $Row['Price'], $Row['InStock']);
}

[rgrady3]

Still get the same result.  To make sure things were working I changed "Product" in my $Query line to include a typo and got back an error "Unknown column 'Produc' in 'where clause' so at least that part is working.  Should I attempt a different print method?

[Barand]

Are you sure the $productid you are inputting exists?

 

try the query without the where clause to see if all data is listed

[rgrady3]

I tried it without the where clause and it printed the entire table.

 

What I do not understand is I echo $productid as the first command and it displays every time including this time when I inputted the search value but did not have the WHERE clause in my SELECT line.

[rgrady3]

I discovered something wrong with the table.  When I printed it out as described the value of each line is 0.00, but in the table it contains pricing data.

[rgrady3]

The changes you gave me solved part of the problem!!!  I tried searching for some other things and it gave data.  Now my only problem is the one column giving only 0.00 instead of the actual column value.

[Barand]

How is the Price column defined in the table?

[rgrady3]

It is defined as a VARCHAR(10).

[Barand]

DECIMAL (8,2) would be a better choice for a currency field, however, change print line to

 

 

printf (" Product: %s, Price: %s  In Stock: %s<BR> ", $Row['Product'], $Row['Price'], $Row['InStock']);

 

 

and see if that helps

[rgrady3]

Outstanding.  Thanks for your help!