[SOLVED] php and mysql

[ngreenwood6]

I have run into a pretty stumping question for me. I have a database with 2 tables. It looks like this:

 

table:users

field:id

field:username

field:password

table:work

field:id

field:job

field:user_id

 

Now the problem that I am running into is registering a user. When I register a user I want to enter there user id into the job that they select. However, the user id field is an auto_increment field. Can someone help me to get this working? Thanks in advance for any help.

 

[revraz]

The id field in the Users table should be auto increment

The id filed in the Work table should be auto increment

 

The user_id field should not be auto increment

[bluesoul]

mysql_insert_id() will get the last generated ID.

[ngreenwood6]

Thank you bluesoul. how would you use that in a query. I know that user_id field shouldn't be auto increment. It is not the users id is the auto increment.

[gevans]

straight after the query assign mysql_insert_id() to a var

 

$result = mysql_query(INSERT INTO "table_name" ("column1", "column2", ...) VALUES ("value1", "value2", ...));
$incrementedID = mysql_insert_id();

[bluesoul]

something like...

 

$query1 = "INSERT INTO users(id, username, password) VALUES (NULL, '$username', '$password')";
...
$lastid = mysql_insert_id($conn); //where $conn is the resource identifier you used in mysql_connect
$query2 = "INSERT INTO work (id, job, user_id) VALUES (NULL, '$job', '$lastid')";

[ngreenwood6]

I have tried this code:

 

<?php
session_start();

//include the variables
include("../vars.php");

//check that the session is set
if(!$_SESSION['logged_in'])
{
header("Location:not_logged.php");
}

//make the values a variable
foreach($_POST as $value);

//connect to the server
$conn = mysql_connect($host, $db_user, $db_pass);

//select database
mysql_select_db($db);

//encrypt the password
$encrypted_password = md5($password);

//make the query
$query = "INSERT INTO login (id, username, password, name, email, school_1, school_2, school_3, membership) VALUES (NULL, '$add_username','$encrypted_password','$name','$email','$school_1','$school_2','$school_3','$add_membership')";

if(empty($username))
{
echo "Please enter a username.";
}
elseif(empty($password))
{
echo "Please enter a password.";
}
elseif(empty($name))
{
echo "Please enter a name.";
}
elseif(empty($email))
{
echo "Please enter an email address.";
}
else if (!eregi("^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$", $email))
{
echo "Please enter a valid email address!";
}
elseif($password != $confirm_password)
{
echo "Your password's do not match.";
}
else
{
$results = mysql_query($query);

//update id
$last_id = mysql_insert_id($conn);

//make the school query
$school_query = "UPDATE schools SET tech_id='$lastid' WHERE school_1='$school_1' AND school_2='$school_2' AND school_3='$school_3'";

$school_results = mysql_query($school_query);

echo "You have added $name to the database. <a href=\"admin.php?edit=users\">Click here</a> to continue.";
}

?>

 

I am getting this error:

 

Notice: Undefined variable: lastid in C:\wamp\www\techs\added_user.php on line 60

 

Can someone help me as to what I am doing wrong?

[ngreenwood6]

I actually figured that out. I had lastid instead of last_id. But it is not updating the database with the correct tech_id.

[ngreenwood6]

I actually figured it out now but thanks for the help. bluesoul you are a lifesaver.