Help with adding to data to database

[k4pil]

Hi there ppl.

Quick question.

I am adding data to my database. But when i search the database i am getting all fields that are being searched for as well as those that are empty.

e.g. If i add to my database as follow;

table (uniqueID, fig1, fig2)
$query = INSERT in table1 values '' '2' '3.0'
$query = INSERT in table1 values '' '4' '5'

And then if i search;
$query = SELECT * from table1 WHERE figure2 = '4'

both rows are returned because there is an 0 in fig2 in row 1. Any idea wots goin on????

Is it something to do with stripping empty chars?

[littlened]

[!--quoteo(post=353231:date=Mar 9 2006, 01:23 PM:name=k4pil)--][div class=\'quotetop\']QUOTE(k4pil @ Mar 9 2006, 01:23 PM) [snapback]353231[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Hi there ppl.

Quick question.

I am adding data to my database. But when i search the database i am getting all fields that are being searched for as well as those that are empty.

e.g. If i add to my database as follow;

table (uniqueID, fig1, fig2)
$query = INSERT in table1 values '' '2' '3.0'
$query = INSERT in table1 values '' '4' '5'

And then if i search;
$query = SELECT * from table1 WHERE figure2 = '4'

both rows are returned because there is an 0 in fig2 in row 1. Any idea wots goin on????

Is it something to do with stripping empty chars?
[/quote]

have you got the fig2 column setup as an INT or FLOAT or something else?

[k4pil]

Its actually an int. What i meant was ;

$query = INSERT in table1 values '' '2' '0'

That zero causes it to be output no matter what the searcg criteria. e.g. Select * from table1 where fig2 = "5" will still output the row above. When i delete the rows with 0 values everything else works fine.

[littlened]

[!--quoteo(post=353236:date=Mar 9 2006, 01:40 PM:name=k4pil)--][div class=\'quotetop\']QUOTE(k4pil @ Mar 9 2006, 01:40 PM) [snapback]353236[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Its actually an int. What i meant was ;

$query = INSERT in table1 values '' '2' '0'

That zero causes it to be output no matter what the searcg criteria. e.g. Select * from table1 where fig2 = "5" will still output the row above. When i delete the rows with 0 values everything else works fine.
[/quote]

can you post the exact code as it is in your page?

[k4pil]

OK.

Here it goes. I have a table;
products(prodID, prodName, prodDesc, supplier, costPound, costPence)
where prodID is unique.

These are some rows from that database (apologies in advance about the stupid names ;b );

108 nokia 6680 nokia £99.99
6 nokia 3310 phone nokia £199.99
2 skyline car nissan £30000.0
3 skyline car nissan £45000.99
4 skyline car nissan £40000.99
1 tizer fizzy drink coke £0.35

When i run a query when $prodName is 'car'
[code]
$query = "SELECT * FROM product WHERE productID = '$ID' OR prodName = '$prodName' OR prodDesc = '$prodDesc' OR supplier = '$supplier' OR costPound = '$costPound' OR costPence = '$costPence'";
$result = mysql_query($query);
$num = mysql_numrows($result);
[/code]

The output is!

1 tizer fizzy drink coke £0.35
2 skyline car nissan £30000.0
5 mars choclate mars £0.35
9 car fast car lkj £1.2


These extra values are output no matter what is searched for. The only explanation is that the query is returning all rows where $prodName = 'car' aswell as those rows which have an empty field - as these do in the costPence column.

Hope this makes sense.

Any help?