Page 1 of 1
Why won't this work????
Posted: Sat Feb 07, 2004 2:32 pm
by tstimple
I admit, a am new to php. This is my first atempt at adding data to a table. I am trying to normalize DB this by taking data in 35 seperate columns in one table (VehicleList) and add them as seperate rows to a new table (vinoptions).
I have read dozens of examples here on the forums about the "INSERT" function and have tried to duplicate the syntax but I just can't seem to make this work.
Here is my code:
Code: Select all
<?php
$connection=mysql_connect("host","user","pass");
$database=mysql_select_db("database");
$result=mysql_query("SELECT * FROM VehicleList");
while($x=mysql_fetch_row($result)){
for($i=27; $i<62; $i++){
if ($x[$i]<>""){
$VIN=$x[1];
$CODE=$x[$i];
$sql= "INSERT INTO vinoptions(vin, option) VALUES ($VIN, $CODE)";
$added=mysql_query($sql) or die(MySQL_Error());
echo "inserting $VIN, $CODE"; //testing the output of the loop
echo "<pre>";
}
}
}
echo "DONE";
?>
When I run the code the SQL error code haults the script and I get:
You have an error in your SQL syntax near 'option) VALUES (3GNGK26U22G331635, sL)' at line 1
I don't understand
First, why is it saying "line 1"
When the error seems to be in the "INSERT" function?
If I take out the "or die" clause the script runs through the loops fine, so I think I have that correct. I just can't get the rows to be added to the table.
HELP!
Posted: Sat Feb 07, 2004 2:38 pm
by dodga
You need single quotes around the values you want to insert..
Code: Select all
...
$sql= "INSERT INTO vinoptions(vin, option) VALUES ('$VIN', '$CODE')";
...
The error occurs at line 1 of your SQL statement - which actually is your INSERT statement

Posted: Sat Feb 07, 2004 2:38 pm
by Michael 01
You need to have any data, or numbers that are being inserted into the database, encased in tags.
Some choose to use:
There are a few other variations, but in short, keep any variable encased.
Posted: Sat Feb 07, 2004 2:47 pm
by tstimple
NOPE. I have tried every variation of quotes and tags I can think of.
By putting single quotes '$VIN', '$CODE'
I get the same error, except that now it shows the quotes I've added:
You have an error in your SQL syntax near 'option) VALUES ('3GNGK26U22G331635', 'sL')' at line 1
Posted: Sat Feb 07, 2004 2:50 pm
by tstimple
Michael 01,
Tried it your way too. Still no luck!
--Tim
Posted: Sat Feb 07, 2004 2:51 pm
by dodga
Could it be that there is no column named option in that table?
Posted: Sat Feb 07, 2004 2:55 pm
by tstimple
Thanks dodga,
but I have double checked my table field names. "vin" and "options" are the correct names (all lower case).
--Tim
Posted: Sat Feb 07, 2004 2:59 pm
by tstimple
Sorry,
I meant "vin" and "option" are correct
Posted: Sat Feb 07, 2004 3:06 pm
by dodga
Hm, I just tried testing your code on my local database (MySQL 4.0.16).
When creating a table containing 2 columns (vin, option) the server returned the following error:
MySQL wrote:Mysql said :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 'option Tinytext NOT NULL )' at line 1 (ERRNO: 1064)
I think
option is a reserved word and cannot be used as column name - therefore you get an error trying to use it as such.
I had similar problems once, when I had a column that was called
index. Renaming the column helped me back then. I think I was running MySQL 3.something
What MySQL-Version (and client) do you use? Would be interesting to know, what client/server allows this

Posted: Sat Feb 07, 2004 3:15 pm
by tstimple
Dodga,
You are CORRECT!
I changed the field name to "code" and now it works.
THANKS A MILLION!
Posted: Sat Feb 07, 2004 3:23 pm
by dodga
You're welcome

Posted: Sat Feb 07, 2004 5:52 pm
by Michael 01
Jeeze, it never ceases to amaze me, how something so simple can be overlooked. I should of known better...*Slaps head with stale loaf of bread*.
That is the commandments of Sql creation..never use index or option for a column name, because yes, it is a reserved option, as a few others are as well.
Damn it...how retarded I feel.
Posted: Sat Feb 07, 2004 5:56 pm
by tstimple
Do you happen to know what others are reserved so I don't do the same thing in the future??
--Tim
Posted: Sat Feb 07, 2004 6:29 pm
by Michael 01
Here ya go right from the SQL site:
May prefix identifier, like ``@table'' Not allowed
ADDDATE() SQL function ADDDATE(); new in MySQL version 4.1.1
ADDTIME() SQL function ADDTIME(); new in MySQL version 4.1.1
ALPHA SQL function Nothing comparable
ARRAY Data type Not implemented
ASCII() SQL function ASCII(), but implemented with a different meaning
AUTOCOMMIT Transactions; ON by default Transactions; OFF by default
BOOLEAN Column types; BOOLEAN accepts as values only TRUE, FALSE, and NULL BOOLEAN was added in MySQL version 4.1.0; it is a synonym for BOOL which is mapped to TINYINT(1). It accepts integer values in the same range as TINYINT as well as NULL. TRUE and FALSE can be used as aliases for 1 and 0.
CHECK CHECK TABLE CHECK TABLE; similar, but not identical usage
COLUMN Column types COLUMN; noise word
CHAR() SQL function CHAR(); identical syntax; similar, not identical usage
COMMIT Implicit commits of transactions happen when data definition queries are being issued Implicit commits of transactions happen when data definition queries are being issued, but also with a number of other queries
COSH() SQL function Nothing comparable
COT() SQL function COT(); identical syntax and implementation
CREATE SQL, data definition language CREATE
DATABASE SQL function DATABASE(); DATABASE is used in a different context, for example CREATE DATABASE
DATE() SQL function CURRENT_DATE
DATEDIFF() SQL function DATEDIFF(); new in MySQL version 4.1.1
DAY() SQL function Nothing comparable
DAYOFWEEK() SQL function DAYOFWEEK(); the first day (1) by default is Monday in MaxDB, and Sunday in MySQL
DISTINCT SQL functions AVG, MAX, MIN, SUM DISTINCT; but used in a different context: SELECT DISTINCT
DROP inter alia in DROP INDEX DROP INDEX; similar, but not identical usage
EBCDIC() SQL function Nothing comparable
EXPAND() SQL function Nothing comparable
EXPLAIN Optimization EXPLAIN; similar, but not identical usage
FIXED() SQL function Nothing comparable
FLOAT() SQL function Nothing comparable
HEX() SQL function HEX(); similar, but not identical usage
INDEX() SQL function INSTR() or LOCATE(); similar, but not identical syntaxes and meanings
INDEX USE INDEX, IGNORE INDEX and similar hints are being used right after SELECT, like SELECT ... USE INDEX USE INDEX, IGNORE INDEX and similar hints are being used in the FROM clause of a SELECT query, like in SELECT ... FROM ... USE INDEX
INITCAP() SQL function Nothing comparable
LENGTH() SQL function LENGTH(); identical syntax, but slightly different implementation
LFILL() SQL function Nothing comparable
LIKE Comparisons LIKE; but the extended LIKE MaxDB provides rather resembles the MySQL REGEX
LIKE wildcards MaxDB supports ``%'', ``_'', ``ctrl+underline'', ``ctrl+up arrow'', ``*'', and ``?'' as wildcards in a LIKE comparison MySQL supports ``%'', and ``_'' as wildcards in a LIKE comparison
LPAD() SQL function LPAD(); slightly different implementation
LTRIM() SQL function LTRIM(); slightly different implementation
MAKEDATE() SQL function MAKEDATE(); new in MySQL version 4.1.1
MAKETIME() SQL function MAKETIME(); new in MySQL version 4.1.1
MAPCHAR() SQL function Nothing comparable
MICROSECOND() SQL function MICROSECOND(); new in MySQL version 4.1.1
NOROUND() SQL function Nothing comparable
NULL Column types; comparisons NULL; MaxDB supports special NULL values that are returned by arithmetic operations that lead to an overflow or a division by zero; MySQL does not support such special values
PI SQL function PI(); identical syntax and implementation, but parantheses are mandatory
REF Data type Nothing comparable
RFILL() SQL function Nothing comparable
ROWNO Predicate in WHERE clause Similar to LIMIT clause
RPAD() SQL function RPAD(); slightly different implementation
RTRIM() SQL function RTRIM(); slightly different implementation
SEQUENCE CREATE SEQUENCE, DROP SEQUENCE AUTO_INCREMENT; similar concept, but differing implementation
SINH() SQL function Nothing comparable
SOUNDS() SQL function SOUNDEX(); slightly different syntax
STATISTICS UPDATE STATISTICS ANALYZE; similar concept, but differing implementation
SUBSTR() SQL function SUBSTRING(); slightly different implementation
SUBTIME() SQL function SUBTIME(); new in MySQL version 4.1.1
SYNONYM Data definition language: CREATE [PUBLIC] SYNONYM, RENAME SYNONYM, DROP SYNONYM Nothing comparable
TANH() SQL function Nothing comparable
TIME() SQL function CURRENT_TIME
TIMEDIFF() SQL function TIMEDIFF(); new in MySQL version 4.1.1
TIMESTAMP() SQL function TIMESTAMP(); new in MySQL version 4.1.1
TIMESTAMP() as argument to DAYOFMONTH() and DAYOFYEAR() SQL function Nothing comparable
TIMEZONE() SQL function Nothing comparable
TRANSACTION() Returns the ID of the current transaction Nothing comparable
TRANSLATE() SQL function REPLACE(); identical syntax and implementation
TRIM() SQL function TRIM(); slightly different implementation
TRUNC() SQL function TRUNCATE(); slightly different syntax and implementation
USE mysql commandline user interface command USE
USER SQL function USER(); identical syntax, but slightly different implementation, and parantheses are mandatory
UTC_DIFF() SQL function UTC_DATE(); provides a means to calculate the result of UTC_DIFF()
VALUE() SQL function, alias for COALESCE() COALESCE(); identical syntax and implementation
VARIANCE() SQL function Nothing comparable
WEEKOFYEAR() SQL function WEEKOFYEAR(); new in MySQL version 4.1.1