php mysql query with integer as value error

Question

Can someone please tell me why the first sql query works but the second thrown an error. I've not been able to find out why the integer value causes it to break.

Works:

$SQL = "SELECT * FROM Users WHERE userName = $uname AND pass = $pword";

Errors:

$SQL = "SELECT * FROM Users WHERE userName = $uname AND firstLogin = 1";

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND firstLogin = 1' at line 1

Also if there's some additional enlightenment you have to offer im all ears.

EDITED TO SHOW FUNCTIONAL CONNECTION Using PDO:

<?php
    $uname = "userNameToSearchBy";

//PDO connection string 
    $user_name = "dbUsername";
    $pass_word = "dbPass";
    $database = "sdName";
    $server = "dbServer";
    $db = new PDO("mysql:host=".$server.";dbname=".$database.";", $user_name, $pass_word);
    $stmt = $db->prepare("SELECT * FROM Users WHERE userName=? AND firstLogin=?");
    $stmt->execute(array($uname, 1));
    //Returns the rows from thge database that match the query
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    //Shows the number of rows returned
    $message = $stmt->rowCount();   
?>
<body>
<?php echo $message ?>
</body>

Outputs number of rows that match the sql criteria

Are you sure the first one works? If userName and/or pass are strings of any type, then they need to be surrounded by '. That at least appears why the 2nd one wouldn't work and that same problem exists in the first example. — tptcat
– tptcat, Commented Aug 18, 2012 at 22:36
What is the data type of the field firstLogin in your table? — mithilatw
– mithilatw, Commented Aug 18, 2012 at 22:39
tinyInt(1) value can be 0 or default of 1 The first line doesn't seem to be throwing an error, and the code continues dotn past 2 if statements that use its output — Reahreic
– Reahreic, Commented Aug 18, 2012 at 22:41
try this $SQL = "SELECT * FROM Users WHERE userName = '".$uname."' AND pass = $pword"; — Ionel Lupu
– Ionel Lupu, Commented Aug 18, 2012 at 22:42
@boyd the first query seems to be working and returning a result that can be used in $num_rows = mysql_num_rows($result); however only the second throws the error — Reahreic
– Reahreic, Commented Aug 18, 2012 at 22:46

Brad Koch · Accepted Answer · 2012-08-18 23:07:54Z

1

Drop your quote_smart() function, the deprecated mysql_ functions, and use PDO prepared statements instead. That should resolve your problem while improving the security and quality of your codebase.

There's a standard comment that gives more detail on this, check out the PDO tutorial at the end in particular:

Please, don't use mysql_* functions for new code. They are no longer maintained and the community has begun the deprecation process. See the red box? Instead you should learn about prepared statements and use either PDO or MySQLi. If you can't decide, this article will help to choose. If you care to learn, here is good PDO tutorial.

edited Aug 18, 2012 at 23:07

answered Aug 18, 2012 at 22:56

Brad Koch

20.5k21 gold badges114 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Reahreic Over a year ago

I'm trying this in an empty .php doc to ensure nothing else gets in the way:

$uname = "kwolmarans"; 	$user_name = "nameHidden"; 	$pass_word = "passwordHidden"; 	$database = "dbName"; 	$server = "kwolmara.startlogicmysql.com"; 	try{ 		$db = new PDO("mysql:host=".$server.";dbname=".$database.";charset=UTF-8", $user_name, $pass_word); 	}catch(PDOException $ex){ 		// 	} 	$stmt = $db->prepare("SELECT * FROM Users WHERE userName=? AND firstLogin=?"); 	$stmt->execute(array($uname, 1)); 	$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

Reahreic Over a year ago

This is the error that follows:

Fatal error: Call to a member function prepare() on a non-object in /hermes/bosweb25b/b807/sl.kwolmara/public_html/test.php on line 13

Line 13 is: $stmt = $db->prepare("SELECT * FROM Users WHERE userName=? AND firstLogin=?");

Brad Koch Over a year ago

The connection is failing. You're catching the exception, and so the error details are not being displayed.

Reahreic Over a year ago

Here's the uncaught error message:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [2019] Can't initialize character set UTF-8 (path: /usr/share/mysql/charsets/)' in /hermes/bosweb25b/b807/sl.kwolmara/public_html/test.php:8 Stack trace: #0 /hermes/bosweb25b/b807/sl.kwolmara/public_html/test.php(8): PDO->__construct('mysql:host=kwol...', 'usernameHidden', 'passwordHidden') #1 {main} thrown in /hermes/bosweb25b/b807/sl.kwolmara/public_html/test.php on line 8

Brad Koch Over a year ago

It clearly doesn't like the charset=UTF-8. I'm not prepared to elaborate on why you are getting that error; that would be a good new question. I would think removing that declaration should be a sufficient solution to avoiding it.

codingbiz · Accepted Answer · 2012-08-18 22:47:45Z

0

Try to echo your sql statement

$SQL = "SELECT * FROM Users WHERE userName = $uname AND firstLogin = 1";
echo($SQL);

Something is probably wrong with $uname. If it is empty you get

$SQL = "SELECT * FROM Users WHERE userName = AND firstLogin = 1";

which has syntax error.

You can also put quote around $uname like this

$SQL = "SELECT * FROM Users WHERE userName = '$uname' AND firstLogin = 1";

If $uname also has single quote, that can cause problem too

answered Aug 18, 2012 at 22:47

codingbiz

26.4k9 gold badges62 silver badges99 bronze badges

1 Comment

Reahreic Over a year ago

Echo Result: SELECT * FROM Users WHERE userName = 'kwolmarans' AND firstLogin = 1

pestilence669 · Accepted Answer · 2012-08-18 22:50:09Z

0

Try quoting $uname... or passing the value through to mysql_real_escape()

answered Aug 18, 2012 at 22:50

pestilence669

5,7091 gold badge26 silver badges35 bronze badges

8 Comments

Reahreic Over a year ago

All my input fields go through this function:

function quote_smart($value, $handle) {    if (get_magic_quotes_gpc()) {        $value = stripslashes($value);    }    if (!is_numeric($value)) {        $value = "'" . mysql_real_escape_string($value, $handle) . "'";    }    return $value; }

pestilence669 Over a year ago

Oh. What type is firstLogin? Also, ensure $uname is in scope.

Reahreic Over a year ago

It's of type tinyint(1) with a default set to 1

pestilence669 Over a year ago

what does quote_smart() return on a blank name?

Reahreic Over a year ago

It returns these two characters with a "" string: ''

|

Collectives™ on Stack Overflow

php mysql query with integer as value error

3 Answers 3

5 Comments

1 Comment

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related