Logic(Help)

[rico harley]

// Add user info into the database table for the main site table
$sql = "INSERT INTO users (username, email, password, gender, city, state, sign, month, day, year, ip, signup, lastlogin, notescheck)      
       VALUES('$u','$e','$p_hash','$g','$c','$S','$s','$m','$d','$y','$ip',now(),now(),now())";
$query = mysqli_query($db_conx, $sql);
$uid = mysqli_insert_id($db_conx);
// Establish their row in the useroptions table
$sql = "INSERT INTO useroptions (id, username, background) VALUES ('$uid','$u','original')";
$query = mysqli_query($db_conx, $sql);

// Establish their row in the match_profile table
$sql = "INSERT INTO match_profile (profileId, username) VALUES ('$uid','$u')";
$query = mysqli_query($db_conx, $sql);
// Establish their row in the sign table
$sql = "INSERT INTO sign (sign_id, username, sign_name, birthmonth, birthday, birthyear) VALUES ('$uid','$u','$s','$m','$d','$y')";
$query = mysqli_query($db_conx, $sql);

//$uid = mysqli_insert_id($db_conx);
// Update KEYS in User Table
$sql = "UPDATE users SET useroptions_id='$uid', match_id='$uid', sign_id='$uid' WHERE id='".$uid."'";
$query = mysqli_query($db_conx, $sql);

I wan't this database to be normalized, so in the user table I added three foreign keys.
What I don't know is how to add the value in the database table after the INSERT.

I tried this, but it did not work.

//$uid = mysqli_insert_id($db_conx);
// Update KEYS in User Table
$sql = "UPDATE users SET useroptions_id='$uid', match_id='$uid', sign_id='$uid' WHERE id='".$uid."'";
$query = mysqli_query($db_conx, $sql);

What would be the correct way to tackle this problem?

[Mike Schinkel]

I tried this, but it did not work.

Does not work how?   What happens it does not work?  Do you get an error message?  

Anyway, I don’t think you are understanding normalization, unless I’m not clear on what you are trying to do.  Looks like you are putting the foreign keys for the “many” table into the “one” table instead of vice-versa.  There is no reason to put the fields “useroptions_id", “match_id", and “sign_id" into the “users" table and then set them to all have the same value as “id.” Instead you want to have a “user_id” field in each of the “useroptions”, “match_profile” and “sign” tables and then a “id” field in the “users” table. 

Also, looking at your code it seems like you might be writing code that would leave you wide open to a SQL Injection attack.  See these to learn more:

http://www.tizag.com/mysqlTutorial/mysql-php-sql-injection.php

http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php

So I think your code should look *something* like this (NOTE: I did not test this and I normally use WordPress so my direct MySQL function knowledge is rusty):

try {

  $sql = db_conx->prepare( "INSERT INTO users ( username, ... ) VALUES ( ?,... ),", $username, ... );

  if ($db_conx->query($sql) !== TRUE) {

    throw new Exception("INSERT FAILED: users");

  }

  $user_id = $db_conx->insert_id;

  $sql = db_conx->prepare( "INSERT INTO useroptions ( user_id, ... ) VALUES ( ?,... ),", $user_id, ... );

  if ($db_conx->query($sql) !== TRUE) {

    throw new Exception("INSERT FAILED: useroptions");

  }

  $sql = db_conx->prepare( "INSERT INTO match_profile ( user_id, ... ) VALUES ( ?,... ),", $user_id, ... );

  if ($db_conx->query($sql) !== TRUE) {

    throw new Exception("INSERT FAILED: match_profile");

  }

  $sql = db_conx->prepare( "INSERT INTO sign ( user_id, ... ) VALUES ( ?,... ),", $user_id, ... );

  if ($db_conx->query($sql) !== TRUE) {

    throw new Exception("INSERT FAILED: sign");

  }

  

} catch(Exception $e) {

  echo 'ERROR: ' .$e->getMessage();

}

Hope this helps.

-Mike

--
You received this message because you are subscribed to the Google Groups "AtlantaPHP Discussions and Job Postings" group.
To unsubscribe from this group and stop receiving emails from it, send an email to atlantaphp+...@googlegroups.com.
To post to this group, send email to atlan...@googlegroups.com.
Visit this group at http://groups.google.com/group/atlantaphp.
To view this discussion on the web visit https://groups.google.com/d/msgid/atlantaphp/b323f1ca-bfe8-44ef-98d9-c2adfa24439c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.