Problem creating a table

[erez]

Hello,

 

DataBase exists, but when I run the code there is no table in the DB yet I get the message :

Table users created successfully

 

when I run thr sql code in MtSql I get a message saying that there is an error in the 1st line

 

Here is the code: <?php

$servername = "localhost";

$username = "username";

$password = "password";

$dbname = "DB2018";

 

try{

$conn = new PDO("mysql:host=$servername;dbname=$dbname", "root" , "");

 

if (!$conn){

echo "Not connected to database ".$dbname;

}

   }

catch(PDOException $e){

echo $e->getMessage();

}

$sql = "CREATE TABLE IF NOT EXISTS Users (

  id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,

  username VARCHAR(25) NOT NULL,

  password VARCHAR(60) NOT NULL,

  reg_date TIMESTAMP,

  UNIQUE KEY 'username' ('username')

  )";

 

$conn->exec($sql);

 

if ($conn){

echo "Table users created successfully";

}else {

echo "Error creating table";

}

 

?>

 

 

What did I do wrong?

[benanamen]

You need to use backticks on the UNIQUE KEY names

 

UNIQUE KEY `username` (`username`)   

[mac_gyver]

also, your program logic is incorrect, in two places. after the connection is successfully made, $conn is a true value, it's an instance of a pdo object.

 

first, the if (!$conn){ logic after the connection. this code will never be executed if there is a connection error, because if the connection fails, an exception will be thrown and program execution will transfer to the catch block. so, there's no point in testing $conn right after the connection code. this error handling logic is not doing anything useful and should be removed.

 

second, the ->exec() method returns a false value if the query fails due to an error. you would need to test this return value, not the value in $conn, because, well, $conn is a true value if the connection was successful. but there's a better and simpler way of doing this. just use exceptions to handle errors.

 

if you eliminate the try/catch block and set the pdo error mode to exceptions when you make the connection, php will catch any exception, where it will use its error_reporting, display_errors, and log_errors settings to determine what happens with the actual error information. when learning, developing, and debugging code, you should display all errors. when on a live/public server, you should log all errors. by using exceptions, your code will only 'see' error free execution and php will handle any errors. this will simplify all your program logic.

[erez]

Thank you very much for your answers. I made a step forward thanks to you.

 

I believe that I lack some understanding in programming logic.

When I coded the !conn i wanted to avoid the success messages.

 

When I code  $conn->exec($sql); the sql request is executed. but if I change $conn to $whatever I get 2 messages:

 

Notice: Undefined variable: whatever in C:\wamp\www\Db2018\create_tables.php on line 20. and 

Fatal error: Call to a member function exec() on a non-object in C:\wamp\www\Db2018\create_tables.php on line 20.

 

When I looked at table creation examples on the web I see variables like $dbh which appear from nowhere

 

how do I ececute the sql statement correctly?

 

what does the -> in $whatever->exec($sql); mean? what is the difference between using  -> and =?

 

Can you recommend a website or a book which explains the programming logic?

 

and Thanks  again,

[benanamen]

https://phpdelusions.net/pdo

[mac_gyver]

you are lacking in the understanding of php language elements. php has fairly well written documentation at php.net, that you can search on-line, or in a .chm downloadable version. the .chm version has a searchable index that will let you find most information easily, or a full search mode for when that fails.

 

short version -  $conn->exec($sql); is OOP (Object Oriented Programming) notation. the 'base' variable, $conn, is an object and contains an instance of a class. in this case it's an instance of the PDO class. the variable $conn was created and assigned a value in the code creating the database connection. the name of the variable should indicate the meaning of the value in it and the same variable must be used in the connection code and where you use the connection. code you find in examples may be using a different variable name for this purpose, such as $dbh. this is just the name the author choose to use when he wrote the code.

 

this particular statement is calling the exec() method of the PDO class. i'm not sure if the -> has an official name (other languages use a 'dot notation'), but it indicates you are referring to a member of the class, which can either be a class property(variable) or a class method(function).

 

if you look up the documentation for the PDO ->exec() method, you will find that the definition is -

 

public int PDO::exec ( string $statement )

 

 

and the returned values is -

 

PDO::exec() returns the number of rows that were modified or deleted by the SQL statement you issued. If no rows were affected, PDO::exec() returns 0.

 

 

which is actually incomplete. it should state that a false value is returned upon failure.

 

to test or use the returned value, you have to reference the returned value. the clearest usage would be to assign the returned value to a php variable - 

$result =  $conn->exec($sql);

this statement is calling the exec() method of the instance of the PDO class in $conn and assigning the returned value to the variable $result. you can then test $result in the rest of the code. you should also note the red Warning in the documentation. because a zero value can be returned as well, which will be treated as a false value if using a loose comparison, you must use an exact comparison, either a === or a !==, to distinguish between a false value and a zero value.

 

or you can do what i wrote above and use exceptions for errors and your main code will only see error free execution.

Edited January 3, 2018 by mac_gyver

[benanamen]

-> does have an official name. It is called "Object Operator"

 

=> is called "Double Arrow"

 

Source: (TFM) http://php.net/manual/en/tokens.php

[Barand]

He never mentioned =>

what is the difference between using -> and =?

he referred to =?

 

He seems to think he can invent operators and they should do something, just as he thinks he can just pluck a variable (eg $whatever) out of thin air and is surprised when it doesn't work.

 

He obviously hasn't grasped the most fundamental of programming concepts (like the variable) yet. I would have suggested something like "PHP For Dummies" but fear that would be too advanced.

 

@erez

My advice is to forget about databases, you aren't ready yet. Study a basic primer on programming first.

[benanamen]

I was replying to @mc_gyver not being sure if -> had an official name. Threw in the other one since people probably don't know that one has a name as well. The OP, well you covered it.

[erez]

Thank you very much for your comments.

I do know something in php but php +sql is something I didn't figure out well.

 

I decided to start from sctatch in

 http://coursesweb.net/php-mysql/writing-php-scripts

 

Does it look good? What's  your opinion?

[Barand]

I skimmed through it as far as the PDO section and I only spotted one or two things I disagreed with. You could do worse