mysql doesn't recognize text and number combinations

Question

I have written a simple example of my problem below:

<html>
    <body>
        <form action="test.php" method="POST">

        <input type="text" name="refValue">

        <input type="submit" name = "go">
        </form>

            <?php
            $sql_connect = mysql_connect("localhost","root","root") or die ("no connect");
            $sql_data = mysql_select_db("suraj", $sql_connect) or die ("no database");

            $refVal = $_POST['refValue'];
            $SP = mysql_query("SELECT * FROM data WHERE ref=".$refVal);

            while ($col = mysql_fetch_array($SP)) { 
            echo  $col['num'] ; 
            }; 
            ?>
    </body>
    </html>

The table which it connects to is basically two columns with two rows and is shown as:

**ref** --- **num**

123 --- 32.74

83A --- 29.54

When I enter 123 into the text field and press submit, it does as expected and shows 32.74 however, when I put 83A into the text box and press submit I get an error:

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\wamp\www\testing\test.php on line 20

I checked the database and the ref column is set as VCHAR(4) and num is set as DECIMAL(4,2).

First print your query and run in Database manually. i think query / $refVal returns empty. — Kishore, Feb 17 '14 at 20:16
Thanks, you are correct, I tried SELECT num FROM `data` WHERE ref=123 and it worked fine but when I tried SELECT num FROM `data` WHERE ref=83A I get the following error: #1054 - Unknown column '83A' in 'where clause' — user3199791, Feb 17 '14 at 20:20

score 2 · Accepted Answer · answered Feb 17 '14 at 20:17

2

This specific problem is caused because you don't quote your string.

Instead of:

$SP = mysql_query("SELECT * FROM data WHERE ref=".$refVal);

you need:

$SP = mysql_query("SELECT * FROM data WHERE ref='".$refVal."'");

However, I would strongly urge you to switch to PDO or mysqli using prepared statements as the mysql_* functions are deprecated and you have a serious sql injection problem.

answered Feb 17 '14 at 20:17

jeroen

90,003
21
112
129

1

Thank you, it works now. – user3199791 Feb 17 '14 at 20:22

mysql doesn't recognize text and number combinations

1 Answers1