Invalid identifier error on INSERT

PHP
1

Im trying to run an INSERT statement and am getting an error

Heres my PHP to output the query and its parameters

echo 'SQL: '.$sql.'<br>parameters<pre>';
print_r($parameters);
echo '</pre>';

which results in

)

SQL: INSERT INTO providers (userID,name,email,phone,cMethod,rate,weight,hFeet,hInches,waist,cup,ethnicity,age,introduction,thumb,ip) VALUES (:id,:name,:email,:phone,:method,:rate,:weight,:hFeet,:hInches,:waist,:cup,:ethnicity,:age,:intro,:thumb,:ip)
parameters

Array
(
    [:id] => 2
    [:method] => 1
    [:rate] => ffhggyj
    [:weight] => 5
    [:name] => test
    [:email] => test@aol.com
    [:phone] => dftfgh43
    [:waist] => 4
    [:hFeet] => 6
    [:hInches] => 0
    [:age] => 4
    [:cup] => x
    [:intro] => jgklllllllllllllllll
    [:ethnicity] => hhgnhkl
    [:thumb] => alien.jpg
    [:ip] => 107.218.57.40
)

but the insert doesnt work, so I use PHPMyAdmin, run the insert query and plug in the parameters

1920Ă—1080 76.7 KB

but
1920Ă—1080 85.2 KB

2

What is the field type of ip?

Maybe try wrapping the field in quotes to make it a string.

1 Like
3

That’s the fix.

The reason is because MySQL sees the periods to be a
databasename.tablename.fieldname indentifier.

2 Likes
4

I trying to figure out how since im using PDO

  $parameters = array(
	':id' => $id,
	':method' => $method,
	':rate' => $rate,
	':weight' => $weight,
	':name' => $name,
	':email' => $email,
	':phone' => $phone,
	':waist' => $waist,
	':hFeet' => $h_feet,
	':hInches' => $h_inches,
	':age' => $age,
	':cup' => $cup,
	':intro' => $introduction,
	':ethnicity' => $ethnicity,
	':thumb' => $thumb,
	':ip' => $ip,
  )
5

Right fix. Wrong reason. mysql will not be looking for table/column names in a value. The error is coming from the fact that the first character is a 1 which means mysql thinks it is a number. 107.218 is okay but adding another dot and oops.

The thing is, the original prepared statement seems like it should work. Be nice to know what the error message was.

1 Like
6

how do I put a " at the beginning and end of $ip, the opnly way I can think of is in the INSERT query, but is that ok/?

7

It is already a string. I suspect the problem is that you are using emulated prepares though even there I would think it would work. What is the PDO exception message?

8

I guess I wasn’t thorough enough with my explanation. That is the correct reason. but my example relied on looking at the screen capture and showed syntax more than the exact detailed reason.

MySQL sees the first number as the value, the second as a fractional, upon which it “leaves value mode” and “looks for (field) identifier mode”. In other words, with (a fake IP like)
123.456.789.012 the 123.456 is a presumed float value, but .789.012 is presumed to represent a field.

See the error in the screen capture and notice the " near .57.40"

1 Like
9

but when I add the 's like

  $sql = "INSERT INTO providers
	(userID,name,email,phone,cMethod,rate,weight,hFeet,hInches,waist,cup,ethnicity,age,introduction,thumb,ip) 
	VALUES 
	(:id,:name,:email,:phone,:method,:rate,:weight,:hFeet,:hInches,:waist,:cup,:ethnicity,:age,:intro,:thumb,':ip')"; // SQL Statement

I get

1920Ă—1080 75.4 KB

which tells me the parameters do not add up with the key values

10

Whoops, taken literally :slight_smile:

I meant the $parameters=>ip field value to be wrapped in quotes.

11

Surely the PDO parameter substitution deals with quotes, but you’d need to use PDO::PARAM_STR in your bind to convince it?

Not sure how you’d do that if you pass the parameters as an array rather than binding them individually.

ETA - can’t be that, as the doc suggests (well, states categorically) that passing an array into the execute() will treat all members of the array as if they were strings.

Can we see the code you use to actually bind the parameters and execute the query?

12

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.