Jump to content


Photo

when updating mysql, get the row# easily?


  • This topic is locked This topic is locked
13 replies to this topic

#1 slancasterfreak

slancasterfreak
  • Members
  • PipPip
  • Member
  • 13 posts

Posted 18 October 2006 - 09:54 PM

Hi,
I've used this before:

$query = "INSERT INTO myusers SET lname='$lname', fname='$fname'";
mysql_query($query)  or die('Query failed: ' . mysql_error());

//GET ID FOR LAST INSERT
$id = mysql_insert_id();

to get a row id so I can use it after an insert, but is there anything similar for easily getting a row id after an update like this?

$queryg = "UPDATE study SET answers='$answerarray' WHERE userid='$userid'";
$resultg = mysql_query($queryg) or die('Query failed: ' . mysql_error());


I need the row # from this Updated record to alter a different table in my database, and wondered if there is an easy way to get it, or do I have to just get it by doing yet another query?

Thanks in advance!
Sally


#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,016 posts

Posted 18 October 2006 - 10:10 PM

Ummm, you already know it to be able to specify

WHERE userid='$userid'

??? ???
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 slancasterfreak

slancasterfreak
  • Members
  • PipPip
  • Member
  • 13 posts

Posted 18 October 2006 - 10:24 PM

I am updating a row in one table using the userid#, but I need to get the rowid# from that same table/record so that I can update a different table which stores it's data based on rowid#

It's a very complex mysql structure and I am simplifying the problem to be able to communicate my need.



#4 sanfly

sanfly
  • Members
  • PipPipPip
  • Advanced Member
  • 344 posts
  • LocationNew Zealand

Posted 18 October 2006 - 10:31 PM

Do you have a primary key on the study table?
If you're not part of the solution, you're part of the precipitate

#5 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,016 posts

Posted 18 October 2006 - 10:32 PM

No, it doesn't make any sense at all. The physical row location in a database is irrelevant. When you insert a record and get the result from mysql_insert_id() that is the value of the auto_increment column and may, coincidentally be the number of the physical row in most cases but record id #103 could just as easily be on physical row 3. You should design an application that  will work even if record id's are scattered randomly throughout the table - as some can be.

Use record id and nor row numbers.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#6 slancasterfreak

slancasterfreak
  • Members
  • PipPip
  • Member
  • 13 posts

Posted 18 October 2006 - 10:34 PM

Yes sanfly, I have primary auto-incrementing keys on the tables in question, and this field id is the thing I am after.
Thanks for your help sanfly, and your good attitude sanfly

#7 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 18 October 2006 - 10:47 PM

Set a session for the users id then use it.

You have already been told the ansaw read ok.
Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#8 sanfly

sanfly
  • Members
  • PipPipPip
  • Advanced Member
  • 344 posts
  • LocationNew Zealand

Posted 18 October 2006 - 10:54 PM

After this code

$queryg = "UPDATE study SET answers='$answerarray' WHERE userid='$userid'";
$resultg = mysql_query($queryg) or die('Query failed: ' . mysql_error());

use this to retrieve the id of the row

$rowg = mysql_fetch_array($resultg));
$study_id = $rowg['study_id'];


Replace study_id with whatever the name of your primary key field is

Is that what you were after?
If you're not part of the solution, you're part of the precipitate

#9 slancasterfreak

slancasterfreak
  • Members
  • PipPip
  • Member
  • 13 posts

Posted 18 October 2006 - 10:59 PM

AHHH I could KISS you you sweet man!!! (if you're a man)
Thanks for your HELP!!!!

#10 sanfly

sanfly
  • Members
  • PipPipPip
  • Advanced Member
  • 344 posts
  • LocationNew Zealand

Posted 18 October 2006 - 11:05 PM

Not a man, and your thanks is thanks enough  :)
If you're not part of the solution, you're part of the precipitate

#11 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 18 October 2006 - 11:07 PM

sanfly that ok for the page that got the update code within it, but if he goes to any other page here need to use a session or cookie for that users id.

so unless this is a one page php code design this idear he's got is a flaw.

it would be inpossable to design a proper dynamic php website using the users method in my option without the use off sessions or cookies.

according to the title of the post a simple solution is to use sessions.
Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#12 slancasterfreak

slancasterfreak
  • Members
  • PipPip
  • Member
  • 13 posts

Posted 18 October 2006 - 11:10 PM

well then just another example of how great gals are! thanks!

#13 slancasterfreak

slancasterfreak
  • Members
  • PipPip
  • Member
  • 13 posts

Posted 18 October 2006 - 11:14 PM

redarrow, I'm sorry but your help was not helpful, I didn't need your commentary on my internal PHP structure, and actually never even described it to you, so it is haughty at best for you to decide that you have some sort of answer for me beyond the confines of my question. You are assuming that you know something about my site that you don't know. I have been helped thank you. goodbye now.

#14 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,016 posts

Posted 18 October 2006 - 11:31 PM

Before you gals get too excited I suggest you test Sanfly's code

An update query doesn't return a resultset so this bit will fail
$rowg = mysql_fetch_array($resultg));
$study_id = $rowg['study_id'];

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users