$GLOBAL or static

[Grnadpa]

Lots of hits on these keywords in both the forum and the user manual, but I can't seem to get a handle on them.

What criteria do I use to choose among $_Session($A) $GLOBAL($A) and static $A?

I have a table with columns id (key, autoincrement) userId and, say, $a among others. before each INSERT I need to find out the highest previous value of $a for the userId (i.e. $a is not unique to the table -- though it is unique for key/userId).

The user will typically do multiple INSERTs in "one sitting" but will traverse a number of screens between insertions.

Using a $_SESSION($A) $GLOBAL($A) or a static $A seems more appropriate than doing a SELECT Max($A) ... where userId= ... each time, but which is the better choice (and why?)

Grandpa Brian

[Dr Livingston]

None. What I mean is that there is no need to in regards to MySql; It may be different for other databases, so you'd need to check their documentation <> the intended PHP extension, where appropriate.

What you are looking for in fact, is mysql_insert_id() which if you read after any INSERT on an auto_increment column, will return the last known ID for that given auto_increment column.

Read more about this in the PHP manual - it's available on line, it's a life saver. Here is an example, from my own scripts,

//
public function execute() {
            if( $resultset = @mysql_query( $this -> getPreparedSql(), $this -> connection ) ) {
                $this -> insert_id = @mysql_insert_id( $this -> connection );
                return new MySqlResultset( $resultset );
            }
            throw new UnknownException( 
                'database server returned an error: '.strtolower( mysql_error() ), E_USER_WARNING 
            );
        }

public function getInsertId() {
            return $this -> insert_id;
        } 

Btw, this is the wrong forum grandpa...

[Grnadpa]

What you are looking for in fact, is mysql_insert_id() which if you read after any INSERT on an auto_increment column, will return the last known ID for that given auto_increment column.

Btw, this is the wrong forum grandpa...

I apologize for the wrong forum. I thought this was the one for design questions.

As far as the mysql_insert_id: it's a wonderful answer, only it doesn't apply to the question I asked.

I think you'll find my question specifically pointed out that the field value I was looking for was NOT the auto-increment key column.

To wit: "I need to find out the highest previous value of $a for the userId (i.e. $a is not unique to the table -- though it is unique for key/userId)."

Frankly, I've made two posts and you gave me a smart-alek answer to both of them. I don't think I deserve that, particularly when you didn't read the question.

[Dr Livingston]

So, you are saying that the variable in question, although not the Primary Key for that given database table, is treated such as?

Well, in that case I think I now understand, but I'm sure you could have exlpain more clearly; We are here to help after all

As for me being a smart alec, with attitude, so to speak, well I thought I was being funny. I was

Merry Christmas to you

[kyberfabrikken]

As Livingston suggests, it sounds like a problem with your database-design, which should be solved at that level, rather than in php. If you can't/won't do that, I'd suggest that you use SELECT MAX() each time, to minimize the danger of all sorts of concurrency-problems. You you might even try to make a sub-select, so as to combine the two queries. That should also further minimize the risk of race-conditions.
Caching database-queries in php is tricky, but putting it in the session is even more dangerous. Generally speaking it's not something you should default to do, but rather use in situations, where not doing it will hurt performance drastically. I don't think this is one such.

[Grnadpa]

Thanks for your help folks. In the interest of brevity I failed to communicate. I am looking for a design solution. Here is the application ...
I am a mainframe programmer by profession and a community actor by accident. Since my memory is average at best, I wrote an application, using flat files, first in vb4 then in vb.Net, to help me learn my lines for the theater roles. My fellow actors asked for a copy, so I have been trying to implement an Internet version. It continues to be an adventure.

I am not familiar with relational database graphical representation so let me so let me try to describe the critical relationships in words in this, my PROPOSED, database.

table actor; key: actorId, "foreign key": none
table play; key: playId, "foreign key": none
table subscription; key: subscriptionId, "foreign keys": actorId, playId /*addresses many-to-many relationship of play to actor */
table scene; key: sceneId, "foreign key": subscriptionId
table script; key: scriptId, "foreign key": sceneId, order by: lineNumber.

So, based on this design, I need to keep track of the lineNumber between "Insertions" given that the user can traverse to any option within the application.

Under this design, obviously, the script Table contains lineNumbers for many scenes of many subscriptions for many actor and play combinations.

One option, of course, as inefficient as it is (a very SORE point with us mainframe folks) is to add and maintain a column on the "owner" scene table to hold the highest script line number, and update it along with the script Insert.

So what do you think. A new database design? Use globals? use $session? use static? Add a column and update the scene table? Go fish?

Regards,

Grandpa Brian

[sweatje]

I think you are looking for something like

select max(lineNumber), actor_id from script group by actor_id

A query like this would show the greatest lineNumber for each actor_id associated with the script.

[kyberfabrikken]

session is volatile - it only exists for the session, which is normally until the user closes his browser. static/global are even more volatile, since they only exists during the process. In php that means until the script ends and the page is displayed. So none of thoose could solve your problem.
You should definately solve this at the database-level. Either you can issue a SELECT MAX(`lineNumber`) FROM `script` WHERE `sceneId` = ? just before the INSERT. Possibly you should LOCK the tables before and UNLOCK after, to prevent a race-condition. Most php-developers don't bother do that though, since the risk is so small anyway.
If your version of mysql supports it, you could do the same thing in one query, with a subselect. I believe it's something like ; INSERT INTO `script`(`sceneId`, `lineNumber`) SELECT ?, MAX (`lineNumber`)+1 WHERE `sceneId`= ?. I'm not too sure though, you might have to check with the mysql forum

[Grnadpa]

Thank you all. Very helpful.

[Add]

Lots of hits on these keywords in both the forum and the user manual, but I can't seem to get a handle on them.

What criteria do I use to choose among $_Session($A) $GLOBAL($A) and static $A?

I have a table with columns id (key, autoincrement) userId and, say, $a among others. before each INSERT I need to find out the highest previous value of $a for the userId (i.e. $a is not unique to the table -- though it is unique for key/userId).

The user will typically do multiple INSERTs in "one sitting" but will traverse a number of screens between insertions.

Using a $_SESSION($A) $GLOBAL($A) or a static $A seems more appropriate than doing a SELECT Max($A) ... where userId= ... each time, but which is the better choice (and why?)

Grandpa Brian

from what i can see i assume you want to do the following (correct me if i'm wrong!): you want to select this highest value from a mysql field and call it $a, you then need to do this multiple times and you will reload the page a number of times between each (or go to other pages) and what you are asking is if there is a way to keep $a between loads instead of re-querying the database?

If i assume correctly, $_SESSION['a'] is what you are looking for, as this will keep the value active as long as the session is. look at http://www.php.net/sessions for more information on how to use them.

However, you should be careful as this value will remain constant. if you do a new insert on another page, if you re-queried the database the value may have increased, whereas $a will have remained the same.

[offtopic]

//
public function execute() {
            if( $resultset = @mysql_query( $this -> getPreparedSql(), $this -> connection ) ) {
                $this -> insert_id = @mysql_insert_id( $this -> connection );
                return new MySqlResultset( $resultset );
            }
            throw new UnknownException( 
                'database server returned an error: '.strtolower( mysql_error() ), E_USER_WARNING 
            );
        }

public function getInsertId() {
            return $this -> insert_id;
        } 

Btw, this is the wrong forum grandpa...

[/offtopic]

a good application should never throw an unknown or general exception. if i were you i would throw a databaseException or a MysqlException. an unknown exception is just lazy.

[Dr Livingston]

Actually, architecturally, it makes absolutely no difference what you call the exception; What actually matters, is that the exception is thrown and thus it's caught elsewhere, and not caught and handled there and then, at that level

Btw, if naming conventions really mattered that much, you wouldn't call an exception MySqlException, you'd call it SqlException, which is more appropriate.

However, you should be careful as this value will remain constant.

I wouldn't involve the SESSION as it's not called for; If you go down that route, then you would forever be tied to SESSION - you are therefore limiting your options at a later date.

Not only that, you have to maintain that variable within a SESSION... The way you've suggested it, it'd be treated like a GLOBAL, in that I mean that any part of the application could change it.

If in that event, you do not track those changes through out your application, how can you trust it?

Kyber, SweatJs have suggested a database specific solution, and I think that's the better approach to go.

[Add]

Actually, architecturally, it makes absolutely no difference what you call the exception; What actually matters, is that the exception is thrown and thus it's caught elsewhere, and not caught and handled there and then, at that level

Btw, if naming conventions really mattered that much, you wouldn't call an exception MySqlException, you'd call it SqlException, which is more appropriate.

The general rule with exceptions is to be specific, not just throw a general exception with a string indicating the conditions of the exception. This way, you can define a separate catch clause for each kind of exception, or can catch some but not others, without having to query the object to determine the kind of abnormal condition that caused the exception. You don't want to have to query the exception object to determine whether the problem was an I/O error or an illegal argument.

Note that when String.charAt(int index) receives a bad input, it doesn't throw RuntimeException or even IllegalArgumentException. It throws StringIndexOutOfBoundsException. The type name indicates that the problem was a string index, and the program can query the object to find out what the bad index was.

If you think otherwise please feel free to start a new topic on the correct use of exceptions and i will be happy to continue to discuss it there.

[Add]

I wouldn't involve the SESSION as it's not called for; If you go down that route, then you would forever be tied to SESSION - you are therefore limiting your options at a later date.

Not only that, you have to maintain that variable within a SESSION... The way you've suggested it, it'd be treated like a GLOBAL, in that I mean that any part of the application could change it.

If in that event, you do not track those changes through out your application, how can you trust it?

Kyber, SweatJs have suggested a database specific solution, and I think that's the better approach to go.

the original post was not overly clear so i made a number of assumptions in my post, given those assumptions a session is a perfectly logical choice, however, re-reading the original post it seems that he does want the most current value before each insert in which case SweatJ's solution should be used.

[Grnadpa]

Thanks for the different perspectives. Very helpful.

I will implement SweatJ's solution,