Jump to content

PDO Advice


Stephie22

Recommended Posts

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(8) NOT NULL,
date_closed VARCHAR(8) 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?
Link to comment
Share on other sites

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(8) NOT NULL,
date_closed VARCHAR(8) 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
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

 

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.

Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.