PDO Advice

[Stephie22]

Good Day Guys,

 

 I need some help.

 

Took advice and started to learn PDO, and this is what i have done:

<?php

$host = '127.0.0.1';

$username = 'dealers';

$password = 'dealers123';

$dsn = 'mysql:host=$host';

$opt = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,

PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,

PDO::ATTR_EMULATE_PREPARES => false];

 

try{

 

$pdo = new PDO($dsn, $username, $password, $opt);

$sql = 'CREATE DATABASE IF NOT EXISTS bdealers';

$pdo->exec($sql);

$sql = 'use bdealers';

$pdo->exec($sql);

$sql = 'CREATE TABLE IF NOT EXISTS dealers(

dealer_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,

dealer_code VARCHAR(7) NOT NULL,

dealer_name VARCHAR(20) NOT NULL,

dealer_brand CHAR(1) NOT NULL,

active CHAR(1) NOT NULL,

registered_name VARCHAR(100) NOT NULL,

date_opened VARCHAR( NOT NULL,

date_closed VARCHAR( NOT NULL,

year_registered INTEGER UNSIGNED NOT NULL,

 

PRIMARY KEY(dealer_id, dealer_code)

)';

$pdo->exec($sql);

echo 'The database and table has successfully been created!';

}catch(PDOException $e){

echo $e->getMessage();

}

 

?>

 

When i try to run it, i get the following error:

 

SQLSTATE[HY000] [2002] php_network_getaddresses: getaddrinfo failed: No such host is known.

 

 

Am i doing something wrong, as the localhost does exist?

[requinix]

'mysql:host=$host'

Look carefully.

[Stephie22]

i might be misreading that, but i don't see anything wrong accept that it might be ambiguous? or is there something else missing in the string?

[requinix]

Can you tell me the difference between

'mysql:host=$host'

and

"mysql:host=$host"

[Barand]

http://php.net/manual/en/language.types.string.php#language.types.string.parsing

[Stephie22]

thank you so much.. i never thought that would be such a deal breaker, so used to use '' with mysql. thank you for the help. i worked.

[Stephie22]

i went and enhanced it now so that it can create a user as well.

 

<?php

$servername = "localhost";

$db_user = "root";

$db_password = "";

$db = "bdealers";

$username = "dealers";

$password = "dealers123";

$user = $username.'@'.$servername;

$dsn = "mysql:host=$servername";

$opt = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,

PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,

PDO::ATTR_EMULATE_PREPARES => false];

 

try{

$pdo = new PDO($dsn, $db_user, $db_password, $opt);

$sql = "CREATE DATABASE IF NOT EXISTS $db";

$pdo->exec($sql);

$pdo->exec("CREATE USER $user IDENTIFIED WITH $password;

   GRANT ALL PRIVILEGES *.* TO $user;

   GRANT SELECT, INSERT, UPDATE, DELETE ON $db TO $user; FLUSH PRIVILEGES;");

$sql = "use $db";

$pdo->exec($sql);

$sql = "CREATE TABLE IF NOT EXISTS dealers(

dealer_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,

dealer_code VARCHAR(7) NOT NULL,

dealer_name VARCHAR(20) NOT NULL,

dealer_brand CHAR(1) NOT NULL,

active CHAR(1) NOT NULL,

registered_name VARCHAR(100) NOT NULL,

date_opened VARCHAR( NOT NULL,

date_closed VARCHAR( NOT NULL,

year_registered INTEGER UNSIGNED NOT NULL,

 

PRIMARY KEY(dealer_id, dealer_code)

)";

$pdo->exec($sql);

 

echo 'The database and table has successfully been created!';

}catch(PDOException $e){

echo $e->getMessage();

}

?>

 

but now i get the following error:

 

SQLSTATE[HY000]: General error: 1524 Plugin 'dealers123' is not loaded

[requinix]

IDENTIFIED WITH and IDENTIFIED BY are two different things.

[benanamen]

Great decision learning PDO. You will also need to learn Database Normalization. Your dealers table is already a candidate for it.

[Stephie22]

Thanx you requinix. That link helped me alot. Thanx for the help @benanamen thanx for advice. I will go learn that. If you can change the dealers table. How will you do it?

[Barand]

Use DATE type columns for dates, not varchar. That's nothing to do with normalization though, you'll have to wait for Benanmen on that one.

[benanamen]

If you can change the dealers table. How will you do it? 

 

I have a better idea. After you study Database Normalization, come back and tell us how YOU would change the database.

 

* Hint: There is only one, maybe two columns to be Normalized.

[Barand]

@Benanamen: Neatly sidestepped

 

@Stephie22: Why is the dealer code part of the primary key? Can more than one dealer have the same code?

[benanamen]

@Barand, should I spoon feed every detail? I am sure you of all people could see where the table could be Normalized.

[Stephie22]

Ok if i would say just by looking at it. Put the dealer id. Dealer code. Dealer name. Dealer brand in one table. Then add the dealer id as a key in the second table with the rest of the columns. This was just a wild guess

[benanamen]

No. Many dealers sell more than one "brand" also known as Make. To support that, the dealer_brand should go in another table. If the active column has has more than two states (active, inactive) then you would want a table for the status's keyed to the dealers table. You could possibly have others such as Pending, Suspended, etc..

 

In order for us to give you the correct information you need to provide us the details of what you are doing, not your attempt at doing it.

[Barand]

Conversely, many dealers sell only one brand, like your local Mercedes dealer. So you need to verify that assumption first.

[benanamen]

Conversely, many dealers sell only one brand, like your local Mercedes dealer. So you need to verify that assumption first.

 

Of course. The OP has not provided any details of the project so it is not possible to give hard answers on the DB design yet which is why I said...

 

In order for us to give you the correct information you need to provide us the details of what you are doing, not your attempt at doing it.

[Stephie22]

 

Ok si basicallt im trying to create a web page where we as Nissan can go and keep our deal information up to datem currently they using an ezcel sheet which they enter duplicate data and duplicate dealercodes. Hence my primary key on row id and dealer code. So now im revamping this and making a project out of it. So as Nissan we sell Nissan and Datsun Vehicles.

[Stephie22]

And the active column can only be Active and Not Active. Active is when a dealer is still open and selling cars. Not active is when they closed down the dealership

[Barand]

Database table are nothing spreadsheet tables.

 

From what you have just said, Benanamen is correct and a dealer can have multiple brands, I would expect it is also true that, for a brand, there can be more than one dealer. In whch case the tables would be

+-----------------+        +-------------------+          +---------------+
|  dealer         |        |  dealer_brand     |          |  brand        |
+-----------------+        +-------------------+          +---------------+
| dealer_id (PK)  |-------<| dealer_id  (PK)   |   +------| brand_id (PK) |
| dealer_code     |        | brand_id   (PK)   |>--+      | brand_name    |
| dealer_name     |        +-------------------+          +---------------+
| start_date      |
| end_date        |
| etc.            |
+-----------------+

Does the end_date get set to a value when the dealership closes? If that is the case then the presence or absence of the end_date is effectively your active flag. You wouldn't need both.

[benanamen]

Wow! NISSAN still uses Excel to manage this stuff? OMG! And on top of that, (No knock on you OP) they have someone that essentially knows nothing about databases to create a solution?

Edited October 27, 2017 by benanamen

[Stephie22]

Thanx for the answers. We actually use the Active setting for reports. Where they dont want any data if a dealer is not active irrespective whether it has a cloaed date or not. In some cases they dont even provide a close date which makes it so difficult.

 

Which editor would be best to use with PDO that is free? I am using notepad++ but it seems that it doesnt have the auto complete attributes for PDO. I have to type out everything.