John Davidson

Message:

say I have object user that has a name and a list of emails of limitless lenght. Right now I have 3 tables in my database.

CREATE TABLE NAME (

name TINYTEXT primary key not null

)



CREATE TABLE EMAILS (

id int primary key not null,

email varchar(30)

)



CREATE TABLE USERS (

users_name int references NAME(ID),

users_email int references EMAILS(id),

constraint pk_USERS primary key (users_name, users_email)

)

I feel that there is some error in the making of my tables.

And here is how I am trying to insert the name and email into my database.

protected function setUser($name,$email){ $stmt = $this->estabConnect()->prepare("INSERT INTO USERS(users_email, users_name) VALUES (?,?)"); 



if(!$stmt->execute(array($email, $name))){ $stmt= null;        

        header('location: ../index.php?error=stmtfailed');



        exit();



    }



   



    $stmt=null;

The SQL statement fails

I think the problem is with the sql statment

---

My Answer:
conn->prepare("INSERT INTO NAME (_name) VALUES (:name)");
$stmt->bindParam(':name', $name);
$stmt->execute();

foreach($email as $e){
$stmt = $this->conn->prepare("INSERT INTO EMAILS (_email) VALUES (:email)");
$stmt->bindParam(':email', $e);
$stmt->execute();
}

$stmt = $this->conn->prepare("INSERT INTO USERS (_users_name, _users_email) VALUES ((SELECT ID FROM NAME WHERE _name = :name), (SELECT id FROM EMAILS WHERE _email = :email))");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':email', $e);
$stmt->execute();
}

This code will insert the name into the NAME table, each email into the EMAILS table, and then create a relationship between the user and their emails in the USERS table.