can't create tables anymore

[dani22]

I tried to create a table like I use to do but it's not working anymore. I don't get any fail messages but the table is not created. Has it something to do with the recently made Mysql updates? I don't understand...

 

[misson]

I tried to create a table like I use to do

And how was that? cPanel? Using phpMyAdmin's table wizard? Running a query from phpMyAdmin? Running a query from a script? Something else? When asking for help, be precise and informative.

 

[dani22]

And how was that? cPanel? Using phpMyAdmin's table wizard? Running a query from phpMyAdmin? Running a query from a script? Something else? When asking for help, be precise and informative.

"Running a query from a script?" jepp, that's how, like I've done thousand times before.

Code:

// Connects to your Database
$con = mysql_connect("localhost", "xxxxxx", "xxxxxxx");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("md7dani2_haveroom", $con);

mysql_query("CREATE TABLE subscr(ID varchar(30),check varchar(5))");
mysql_query("INSERT INTO subscr(ID,check) VALUES ('Daniel','Yes')");

mysql_close($con);

 

[descalzo]

Have you tried doing it via PHPmyAdmin?

I ask this just to see where the problem might lie. I know you want to be able to do it via script.

 

[dani22]

haven't done that. How do I write the query in phpMyadmin? I've Tried different querys: CREATE TABLE subscr(ID varchar(30),check varchar(5)); ,but no success.

"#1064 - 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 'varchar(5))' at line 1"

thx in advance.

 

[garikr]

The problem is that you named one of your columns 'check' apparently 'check' is some kind of command or constant in MySql. So this should work:
CREATE TABLE subscr(ID varchar(30),checkid varchar(5));, or whatever you'll name it.
I've done this before spent hours in computercidal rage, trying to figure out what's wrong, only to find out that one of my variables is the reserved word.
Hope this will help.
P.S. I would set up mysql on your home computer/laptop, it's much easier to check queries that way, before plugging 'em into your script.

 

[descalzo]

Nice catch. Didn't even pay attention to the column names.

But, I just used phpMyAdmin to create a table, using their 'wizard' which generated this code:

CREATE TABLE `XXXXX_test`.`test01` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`check` INT NOT NULL ,
PRIMARY KEY ( `id` ) 
) ENGINE = MYISAM ;

and the table was created.

Maybe you have to make sure to quote the field name.

 

[rkalhans]

event with the same column name you might be to create a table
try this

mysql_query("CREATE TABLE subscr(`ID` varchar(30),`check` varchar(5))");
mysql_query("INSERT INTO subscr(`ID`,`check`) VALUES ('Daniel','Yes')");

notice the col-name enclosed in a back-tick (`)
One should alwaz use this to prevent any such problem.
The same thing can also be used in table names if there is such a problem esp when we need to create tables based on user input

P.S.@ DESCALZO Quote may not work in such situations.

 

[garikr]

You are right, descalzo, using ` around the check does the trick. I didn't even know you can use ` in mysql. ` and not '. I think that's the first time I've used that key on my keyboard. Any other languages accept ` in their syntax??
P.S. so "back-tick" is it's name...

 

[descalzo]

Some languages use `command` to execute shell commands (if it is enabled).

I cannot think of any (besides SQL) that use `backticks` like 'single' or "double" quotes.

 

[rkalhans]

Actually, its funny, coz as garikr pointed out many ppl don't even notice the backtick on thr keyboard, but this is what i figured out when I used phpMyadmn for creating queries
ne waz that was silly.

 

[misson]

@dani22: Looks like you've got the immediate issue resolved. There's something you can do in the future to diagnose SQL query problems: check function results for errors. With the old mysql driver, check whether the result of mysql_query is identically FALSE. You have to use the === operator so as to distinguish a return value of 0 from FALSE.

if (FALSE === mysql_query(...)) {
    // query failed; log an error
    ...
} else {
    // continue to next step
    ...
}

(Note that you can use

, [php] and [html] tags when posting to delineate code and preserve whitespace.)

If the script is to be accessible by anyone other than site admins, make sure you [URL="http://msdn.microsoft.com/en-us/library/ms995351.aspx#securityerrormessages_topic2"]don't disclose too much information[/URL] in the error message. Simply printing the result of [URL="http://php.net/mysql_error"][FONT="Courier New"]mysql_error()[/FONT][/URL] is definitely too much information.

Switching to the newer [URL="http://php.net/PDO"]PDO[/URL] driver lets you make use of exceptions, simplifying error handling because you don't need to individually test each query.

[php]// manage DB connection in some other file named (e.g.) "localDB.php" with something like: 
function localDBConnection() { 
    static $db = new PDO('mysql:host=localhost;dbname=...', 'username', 'password');  
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    return $db; 
} 

// in the script 
include("localDB.php"); 
try { 
    $db = localDBConnection();
    $db->query($stmt='CREATE TABLE subscr(ID varchar(30),check varchar(5))'); 
    $db->query($stmt="INSERT INTO subscr(ID,check) VALUES ('Daniel','Yes')"); 
} catch (PDOException $exc) { 
    echo "Query '$stmt' failed: ", $exc; 
}
[/php]

PDO also supports [URL="http://www.php.net/manual/en/pdo.prepared-statements.php"]prepared statements[/URL], which are more efficient (you can execute a prepared statement more than once) and easier to secure (prepared statement parameters aren't vulnerable to [url=http://unixwiz.net/techtips/sql-injection.html]SQL injection[/url]) than executing a string as a query.

[quote="garikr, post: 638280"]Any other languages accept  [COLOR="red"]`[/COLOR] in their syntax??[/QUOTE]
MySQL is the only [URL="http://en.wikipedia.org/wiki/Relational_database_management_system"]RDBMS[/URL] which uses backticks for [URL="http://dev.mysql.com/doc/refman/5.1/en/identifiers.html"]quoting identifiers[/URL] (what are officially called "delimited identifiers") that I know of. SQL Server uses square brackets. The SQL standard specifiers double-quotes. 

Note that identifiers are a different namespace from strings, so you can't use backticks in place of other quote characters.

 

[rkalhans]

@misson, Though I haven't tested your piece of code But I guess it will still face the same problem of check being a keyword
also Displaying the complete Query when it fails imposes too much of risk of displaying the tablename and fields of the table being managed.

Consider this piece of code, (Too sleepy to verify by testing )

// manage DB connection in some other file named (e.g.) "localDB.php" with something like: 
function localDBConnection() { 
    static $db = new PDO('mysql:host=localhost;dbname=...', 'username', 'password');  
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    return $db; 
} 

// in the script 
include("localDB.php"); 
try { 
    $db = localDBConnection();
    $db->query($stmt='CREATE TABLE `subscr` (`ID` varchar(30),`check` varchar(5))'); 
    $db->query($stmt="INSERT INTO `subscr` (`ID`,`check`) VALUES ('Daniel','Yes')"); 
} catch (PDOException $exc) { 
    echo "<Some error message like Invalid input. Please contact site administrator if you feel this is incorrect> ", $exc; 
}

I feel this will be much more appropriate.
Enlighten me If i am wrong.

 

[misson]

@misson, Though I haven't tested your piece of code But I guess it will still face the same problem of check being a keyword

It does, which is the point: to display an error message so OP has a clue what's going wrong.

also Displaying the complete Query when it fails imposes too much of risk of displaying the tablename and fields of the table being managed.

Good point; see the link in my previous post about information disclosure. Even displaying an SQL error message is a security risk. However, DDL statements (table creates & drops) should only be executed for admins, so (in this case) the statement and error aren't being displayed to anyone who shouldn't see them.

 

[dani22]

Jepp, it was wrong name 'check' , thought I've used it before with success, but impossible.

Thanks!