Concurrent accesses to more than 1 table?

Anon

hello,

table: users (user_id, user_name)
table: image (image_id, user_id, image)

now, i add a new user with a form, and same form has a 'file' field which uploads the image with it.

so i am sending 2 data records (user_name & image)

user_name will be added to users, and user_id is auto_increment. now i am adding image with the same user_id i just added in user table.

so how do we retrieve the very same record which i just added? fetching the last data record in table is not good enough, as it can get someone else's record who just submitted the query at the same second.

so, i understand we are using foreign keys. and user_id is a foreign key in image table, whereas its primary key in its own table.

any ideas of how to get along with this?

the book i have with me, just skips the foreign keys bit. so i guess i will have to look in to SQL books specificaly.

thanks,
Daarius ...

Anon

Okay.. My primary keys in the tables are the once I write first (in this case)

table: user(user_id, f_name, l_name, login, p_word)
table: news(news_id, user_id, updated, created, availible)
table: newstext(row_id, news_id, text)

I use no auto_increament
(I already have users in my user table)

Also, I want a user to write a text in my form (login, text).

Then I would like it to look like this in
the news table:
(lets say that user: guest has user_id: 5

news_id user_id updated created availible

1 5 date date j

Once again thanks Daarius 🙂

/Chess

Anon

This is my script at the moment.

//The script loops around everything
to the form, so that when I´ve "Submitted",
he loops back to the form.
// I don´t get any error messages.
And I´ve checked all tables, nothing
is posted.

Here goes the script:

<?php

if(!isset($submit)) {
if ($textus != '' && $person != ''){
$sybase = sybase_connect("a" , "b" , "");
sybase_select_db("c");
$sql = sybase_query("SELECT * from user WHERE Login = '$person'");
$result = $sql;
$num_of_rows = @sybase_num_rows($result);
$a = sybase_query("SELECT user_id from user WHERE Login = '$person'");
print $a;
$query = sybase_query ("INSERT INTO news (user_id, Availible, Uppdated, Created) VALUES ('$a', 'J', 'default', 'default')");
sybase_close($sybase);
print "<p>Thank you!";

print "<br><br> Your text has been submitted!";
}
else {
$error = " Sorry! You didn't fill in all fields!";
}
}
?>
<head><title>input</title></head>
<body>
<P>
<form method="post" action= <? $php_self ?> >
FIELD1: <input type="Text" name="person"> <br>
FIELD2: <textarea name="textus" rows=15 cols=72 wrap=HARD></textarea><br>
<input type="Submit" name="submit" value="Enter Information"><INPUT TYPE=RESET VALUE="Reset Information">
</form>
</body>

Anon

ok, now here quite a work to do 🙂

<?php

if($submit) {
if ($textus != '' && $person != ''){
$sybase = sybase_connect("a" , "b" , "");
sybase_select_db("c");
$get_sql = sybase_query("SELECT * from user WHERE Login = '$person'");
$num_of_rows = @sybase_num_rows($get_sql);

  // if returned number of rows are less than 1, then
  // user doesnt exist in your table.
  if ($num_of_rows < 1) {
     echo 'sorry your user name is wrong"';
     exit();
  }

  $user_record = @sybase_fetch_object($get_sql);
  $valid_user_id = $user_record->user_id;

  $update_query = sybase_query ("INSERT INTO news (user_id, Availible, Updated, Created) 
                                 VALUES ('$valid_user', 'J', 'default', 'default')");

  // now the problem is to get the news_id, to insert in newstext table
  // this is what we need to check for foreing keys. but for now, i am
  // just getting the last row of the table, hopping its the one you 
  // just added above!
  $get_news_id ("SELECT * FROM news GROUP BY news_id DESC" LIMIT 1);
  // above query gets the news_id, first by sorting the table
  // records in descending order. so the first record is the last one.

  $get_last_news_id = @sybase_fetch_object($get_news_id);
  $last_news_id = $get_last_news_id->news_id;

  $insert_newstext = sybase_query ("INSERT INTO newstext (news_id, text) VALUES ('$last_news_id', '$textus')");
  sybase_close($sybase); 

  print "<p>Thank you!"; 
  print "<br><br> Your text has been submitted!";

}
else {
$error = " Sorry! You didn't fill in all fields!";
}
}

<head><title>input</title></head>
<body>
<P>
<form method="post" action= <? $php_self ?> >
FIELD1: <input type="Text" name="person"> <br>
FIELD2: <textarea name="textus" rows=15 cols=72 wrap=HARD></textarea><br>
<input type="Submit" name="submit" value="Enter Information"><INPUT TYPE=RESET VALUE="Reset Information">
</form>
</body>

regards,
Daarius ...

Anon

ok, well my original question remains unanswere... please help!

🙂

Anon

Are you using mysql? If so, did you try using mysql_insert_id()?

$last_id = mysql_insert_id();

It will return the last auto_incremented id from the current thread. It wont work with a insert delay statement though.

Hope that helps.

Robert

Anon

will that attach an id to the form submittion 'I' made. OR will it return the last auto_increment id?

because jsut getting the last auto_incremented is not good enough. what if someone just clicked submitted button at the same time as mine? so mine entry will not be last one.

and yes, i am using mySQL.

Anon

That will return the last auto_incremented id from the insert statement but it is only for the current thread, so even if someone submits at the same time it will return the correct id. I have used this before and it has always worked correctly for me. It will not work if you use a delayed insert though. That is where it can get messed up if someone submits at the same time.

Here is the url from the php manual:

http://www.php.net/manual/function.mysql-insert-id.php

Robert

Anon

mysql_insert_id is the best solution to your problem, this returns the last auto increamented ID for your session and that will be your current insert even if you have multiple users submitting.

Anon

cheers Rob & kaushik

i will just go and read that manual now! 🙂

Anon

ok,
lets say i execute the following query:

$sql = mysql_db_query('db', "INSERT ....");
$last_insert = mysql_insert_id($sql);

now, how do i call back the same auto_incremented record that was added up there?

wil lit be: ?

$sql2 = mysql_db_query('db', "INSERT table (record_id, user_id) VALUES ('003', '$last_insert'");

because in the manual it says, mysql_insert_id only keeps the id of the LAST auto_increment field that was added. so it means it will carry the number of the last auto_increment field. right?

thanks ...

Anon

$sql2 = mysql_db_query('db', "INSERT table (record_id, user_id) VALUES ('003', '$last_insert'");

First and foremost you would want to do a select if you need to current one that you added.
Secondly you will get the id of the last insert for your connection, by specifing your $sql.
That means if you have a form and multiple users are accessing it and insert at the same time, each user will get the id for their induvidual insert. And not for the absolute last insert.