MySQL thinks that '2015123' is different from '02015123'

Question

I am trying yto select a column from my DB where the stockid is equal to the inputted stockid from the user. This is my code below:

    $stockid = $_POST['item'];

    //get the current balance of the selected item
    $sqlbal = "SELECT * FROM db.nonperi where nonperi.stockid = '$stockid'";
    $resultbal = $conn-> query($sqlbal);
    if ($resultbal-> num_rows > 0)
    {
        while ($row = $resultbal -> fetch_assoc()) 
        {
            $currbal = $row['bal'];
        }
    }

I know that the code is correct, however, upon checking in DB, the stockid is equivalent to '2015123' but the $stockid that was posted in this page for this code is '02015123'. That is why it returns nothing.

Is there any code/way to force sql to see them as the same/equal? I appreciate any help about this.

Data type for column nonperi.stockid? – jarlh Jun 18 '15 at 06:48 — jarlh, Jun 18 '15 at 06:48
please mention the database structure – Vikas Umrao Jun 18 '15 at 06:54 — Vikas Umrao, Jun 18 '15 at 06:54

Nitek · Accepted Answer · 2015-06-18T06:59:21.017

4

You are passing the stockid as a string (but want it to behave like an integer). Try

$stockid = intval($_POST['item']);
$sqlbal = "SELECT * FROM db.nonperi where nonperi.stockid = $stockid";

(That also secures your code to prevent SQL injection)

edited Jun 18 '15 at 06:59

answered Jun 18 '15 at 06:51

Nitek

2,515
2
23
39

So can someone give 2 up-votes for a nice answer with nice to the point explanation? – Hanky Panky Jun 18 '15 at 07:22
Hello Nitek, thanks a lot! This one works perfectly for me and also the solution of habib ul haq below, SOF members really rocks! You guys are genius! Please have me as your apprentice :D – niq Jun 18 '15 at 08:02
I think the intval is not necessary on my case since the stockid is posted from a form using a , but I still used your sol'n for future purposes :D thanks again. – niq Jun 18 '15 at 08:05
For security reasons it is necessary, because people can inject SQL statements into your system otherwise - I'm quite sure you don't want that ;) – Nitek Jun 18 '15 at 15:38

score -1 · Answer 2 · answered Jun 18 '15 at 06:49

-1

You can trim leading 0 (zeros) to check the stockId.

SELECT TRIM(LEADING '0' FROM '02454545')

You command should be:

"SELECT * FROM db.nonperi where nonperi.stockid = TRIM(LEADING '0' FROM '$stockid')";

answered Jun 18 '15 at 06:49

Aman Aggarwal

17,619
9
53
81

Hello Aman Aggarwal, thanks for the comment. This works if all of my data from DB do not start with '0' but some have '0' on it. – niq Jun 18 '15 at 06:53
"SELECT * FROM db.nonperi where nonperi.stockid = TRIM(LEADING '0' FROM '$stockid') or nonperi.stockid ='$stockid' "; – habib ul haq Jun 18 '15 at 06:56
@habib ul haq, I haven't thought of that sol'n, your awesome man! Thanks to the both of you! – niq Jun 18 '15 at 08:07

MySQL thinks that '2015123' is different from '02015123'

2 Answers2