[PHP-DB] Re: Session troubles, could this be my isp's fault?
Are you also doing a session_start() at the beginning of your second page? It needs to be in every script trying to use session variables. -Dave David Rice wrote: I Have made two pages, "sess2.php" and "sess3.php" trying to create a session variable then access it in the other page. now when i try and call the session in the second page i get no value, and i have tried var_dump, and it gives me "NULL" anyone know if there is a reason for this?! page 1 is like this = Session Test next _ Surf together with new Shared Browsing http://join.msn.com/?page=features/browse&pgmarket=en-gb&XAPID=74&DI=1059 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] How to find the autoincremented number?
Aha. I had seen that before, but disregarded it because I was trying to
just use pearDB calls instead of the mysql calls. However what I
noticed this time when looking through there is that there's a
LAST_INSERT_ID() that I could use in a query.
e.g.,
function addNewEntry( $stuffToPutIn ){
$sql = "INSERT INTO myTable $stuffToPutIn";
// I use pearDB with mysql
$result = $this->db->query( $sql );
checkDbResult( $result );
$sql = "SELECT LAST_INSERT_ID()";
$id = $this->db->getOne( $sql );
checkDbResult( $id );
$linkAddress = $baseEntryURL.'?entryId='.$id;
mailOutLink( $linkAddress );
}
Thank you!
-Dave
Leif K-Brooks wrote:
http://www.php.net/manual/en/function.mysql-insert-id.php
David Chamberlin wrote:
Hey,
Most of the tables I use identify things using an id which is
auto-generated using autoincrement. However I can't seem to figure
out a good way to find what value was generated for the ID.
For example, let's say I generate a new entry and want to e-mail out a
link to it and the link identifies the entry by ID. Currently, the
only way I can figure to do this is to first do the INSERT and then do
a SELECT on some unique information that I just inserted (e.g.,
submitter's ID and last-modified date). So for example, I would have:
function addNewEntry( $stuffToPutIn ){
$sql = "INSERT INTO myTable $stuffToPutIn";
// I use pearDB with mysql
$result = $this->db->query( $sql );
checkDbResult( $result );
$sql = "SELECT id FROM myTable WHERE ".
"(last_modified_by = $userId) AND ".
"(last_modified_time = $currentTime)";
$result = $this->db->getAll( $sql );
checkDbResult( $result );
$entry = $result[0];
$linkAddress = $baseEntryURL.'?entryId='.$entry->id;
mailOutLink( $linkAddress );
}
What I'd really like to do is get rid of that SELECT or at least make
it less "hacky" to find out what the id was that got autogenerated.
Any ideas?
Thanks,
Dave
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] How to find the autoincremented number?
Hey,
Most of the tables I use identify things using an id which is
auto-generated using autoincrement. However I can't seem to figure out
a good way to find what value was generated for the ID.
For example, let's say I generate a new entry and want to e-mail out a
link to it and the link identifies the entry by ID. Currently, the only
way I can figure to do this is to first do the INSERT and then do a
SELECT on some unique information that I just inserted (e.g.,
submitter's ID and last-modified date). So for example, I would have:
function addNewEntry( $stuffToPutIn ){
$sql = "INSERT INTO myTable $stuffToPutIn";
// I use pearDB with mysql
$result = $this->db->query( $sql );
checkDbResult( $result );
$sql = "SELECT id FROM myTable WHERE ".
"(last_modified_by = $userId) AND ".
"(last_modified_time = $currentTime)";
$result = $this->db->getAll( $sql );
checkDbResult( $result );
$entry = $result[0];
$linkAddress = $baseEntryURL.'?entryId='.$entry->id;
mailOutLink( $linkAddress );
}
What I'd really like to do is get rid of that SELECT or at least make it
less "hacky" to find out what the id was that got autogenerated.
Any ideas?
Thanks,
Dave
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Fwd: When to do free()?
I use pearDB to abstract the databse. It has a free() call which says it frees the resources for that result set. It is a method on DB_Result (which is what you get back when you do a query()). http://pear.php.net/manual/en/core.db.free.php -Dave Allens wrote: From: Allens <[EMAIL PROTECTED]>David, I'm still very new to PHP and MySQL, but here is what I've found. Couldn't find any references to the free() function except for this at the www.mysql.com website. On "How memory is used in MySQL", "lmost all parsing and calculating is done in a local memory store. No memory overhead is needed for small items and the normal slow memory allocation and freeing is avoided. Memory is allocated only for unexpectedly large strings (this is done with malloc() and free() ). " From what I've read, MySQL frees memory automatically when regular function calls from the web back to the server that return data are executed. I'm going out on a limb because I'm not sure and this is a good way for me to learn as well. Couldn't find any real info on the free() function in the php manual or mysql manual nor in any of the manuals I have. I have about 50 users hitting our lone db, but no memory issues have occurred to date. Using Mac OS X Server 10.2.3 on Mac Server 533MHz 1GB of memory with MySQL 3.23.51. Hope this isn't wasted reading? :) On Sunday, February 16, 2003, at 07:39 PM, [EMAIL PROTECTED] wrote: Re: When to do free()? 25667 by: David Chamberlin From: David Chamberlin <[EMAIL PROTECTED]> Date: Sun Feb 16, 2003 11:19:17 AM US/Eastern To: [EMAIL PROTECTED] Subject: Re: When to do free()? Hmmm...still no takers on this one I decided out of curiosity to start liberally adding free() calls whenever I did a db->query(). Unfortunately it seems that the free() call causes it to die. Not quite sure why, but it did. Most of the calls that I use a query() for (as opposed to getAll() or getOne()) are INSERT/UPDATE/DELETE. Is there some reason you shouldn't do a free() after one of those? I'm still baffled. And still don't know if I should be using disconnect() at the end of each of my pages Any thoughts? Thanks, Dave David Chamberlin wrote: Hello, I'm currently using pear DB to abstract out use of my mysql database. Everything is generally working fine, except that it seems that performance seems to degrade the more it is used, then I get my ISP to restart mysql and everything seems to be good again for a while, then it degrades, lather-rinse-repeat. I'm currently one of the only ones using mysql at my ISP, so it seems that whatever I'm doing is causing issues. For the most part I'm not doing anything complex, queries are relatively simple and the dbases are currently pretty small. So while thinking about the issue, one thing that occured to me is that I'm not doing a free() on the query results when I'm done with them. That's mostly because very few of the examples I've seen ever do this, so I assumed it was an optimization that generally wasn't necessary. Furthermore, I figured that after my page got loaded, the connection to the db would be terminated and the resources freed, so the extent of the "resource leak" would be pretty minimal. Well, now I'm questioning the validity of those assumptions and I'm wondering if I need to start adding liberal use of free(), and I'm wondering to what extent it needs to be done? So first question, for those familiar with pearDB. It seems that certainly after doing a query() I should do a free(). What about getAll() (and related question, is there much difference between doing a query() and getAll())?). The docs seem to indicate a getOne() automatically frees resources, so I don't need to do it there. Next question is, what happens when no one refers to a result any more? Will a destructor get called that automatically frees the result? Or should I make sure to do the free before the reference is lost? How long are resources held if you don't free them? Finally, I assume that when you fetch data from a result that the data is copied and that it's not a reference, right? So as soon as I've done the necessary fetchRows() or whatever, I can safely free the result, right? Any other ideas what might be causing problems for mysql and/or how to track the problems? I believe my ISP is using linux with apache, and a fairly recent PHP and mysql. I can get more details on exact versions if that helps. Also, just as a general note, the basic format for most of my pages is: 1) connect to database 2) do some queries from PHP using pearDB 3) display results I don't specifically call disconnect() when I'm done. Should I? Sorry for the abundance of questions, and thanks for any help. Regards, Dave :) Gale L. Allen Jr Macintosh Support Specialist 865/947-5740 "Remember, Love wins over all" (: -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: When to do free()?
Hmmm...still no takers on this one I decided out of curiosity to start liberally adding free() calls whenever I did a db->query(). Unfortunately it seems that the free() call causes it to die. Not quite sure why, but it did. Most of the calls that I use a query() for (as opposed to getAll() or getOne()) are INSERT/UPDATE/DELETE. Is there some reason you shouldn't do a free() after one of those? I'm still baffled. And still don't know if I should be using disconnect() at the end of each of my pages Any thoughts? Thanks, Dave David Chamberlin wrote: Hello, I'm currently using pear DB to abstract out use of my mysql database. Everything is generally working fine, except that it seems that performance seems to degrade the more it is used, then I get my ISP to restart mysql and everything seems to be good again for a while, then it degrades, lather-rinse-repeat. I'm currently one of the only ones using mysql at my ISP, so it seems that whatever I'm doing is causing issues. For the most part I'm not doing anything complex, queries are relatively simple and the dbases are currently pretty small. So while thinking about the issue, one thing that occured to me is that I'm not doing a free() on the query results when I'm done with them. That's mostly because very few of the examples I've seen ever do this, so I assumed it was an optimization that generally wasn't necessary. Furthermore, I figured that after my page got loaded, the connection to the db would be terminated and the resources freed, so the extent of the "resource leak" would be pretty minimal. Well, now I'm questioning the validity of those assumptions and I'm wondering if I need to start adding liberal use of free(), and I'm wondering to what extent it needs to be done? So first question, for those familiar with pearDB. It seems that certainly after doing a query() I should do a free(). What about getAll() (and related question, is there much difference between doing a query() and getAll())?). The docs seem to indicate a getOne() automatically frees resources, so I don't need to do it there. Next question is, what happens when no one refers to a result any more? Will a destructor get called that automatically frees the result? Or should I make sure to do the free before the reference is lost? How long are resources held if you don't free them? Finally, I assume that when you fetch data from a result that the data is copied and that it's not a reference, right? So as soon as I've done the necessary fetchRows() or whatever, I can safely free the result, right? Any other ideas what might be causing problems for mysql and/or how to track the problems? I believe my ISP is using linux with apache, and a fairly recent PHP and mysql. I can get more details on exact versions if that helps. Also, just as a general note, the basic format for most of my pages is: 1) connect to database 2) do some queries from PHP using pearDB 3) display results I don't specifically call disconnect() when I'm done. Should I? Sorry for the abundance of questions, and thanks for any help. Regards, Dave -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: php & date manupulation functions
David Elliott wrote:
try
";
echo date("m",$dbdate)."";
echo date("y",$dbdate)."";
?>
I thought date() took a timestamp, not a string. Here's what I do:
$timestamp = $dbdate;
echo date("d",$timestamp)
echo date("m",$timestamp)
-Dave
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: efficient next/prev page generation
Just reviewed my post (should do that *before* hitting submit ... sigh ). One minor note, which should be obvious, but just for completeness: David Chamberlin wrote: 1.SELECT * FROM 2. get the number of rows from result 3. figure out paging scheme 4. SELECT FROM LIMIT , I forgot to include the query criteria. i.e., 1. SELECT * FROM WHERE/LIKE 2. get the number of rows from result 3. figure out paging scheme 4. SELECT FROM WHERE/LIKE LIMIT , -Dave -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] efficient next/prev page generation
Hey, One more on efficiency. Basically all the examples I've seen for doing 'windowed dbase queries' (i.e., displaying a limited set of results and providing next/prev paging capabilities) have the same basic format: 1.SELECT * FROM 2. get the number of rows from result 3. figure out paging scheme 4. SELECT FROM LIMIT , It seems to me this is annoyingly inefficient forcing every page to query for the entire list first, then re-running the query for the desired subset. I guess there's no way around that, right? My real question is, is there any particular reason (other than laziness) that the first query (which fetches all results) is done with a '*'. It seems to me that especially if you have a lot of data in your table, that's unnecessarily inefficient since it's got to retrieve and store in memory all the fields. Since you only really need to know how many rows there are, wouldn't it be much more efficient to just pick one particular column, and make that column the smallest one available? -Dave -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] When to do free()?
Hello, I'm currently using pear DB to abstract out use of my mysql database. Everything is generally working fine, except that it seems that performance seems to degrade the more it is used, then I get my ISP to restart mysql and everything seems to be good again for a while, then it degrades, lather-rinse-repeat. I'm currently one of the only ones using mysql at my ISP, so it seems that whatever I'm doing is causing issues. For the most part I'm not doing anything complex, queries are relatively simple and the dbases are currently pretty small. So while thinking about the issue, one thing that occured to me is that I'm not doing a free() on the query results when I'm done with them. That's mostly because very few of the examples I've seen ever do this, so I assumed it was an optimization that generally wasn't necessary. Furthermore, I figured that after my page got loaded, the connection to the db would be terminated and the resources freed, so the extent of the "resource leak" would be pretty minimal. Well, now I'm questioning the validity of those assumptions and I'm wondering if I need to start adding liberal use of free(), and I'm wondering to what extent it needs to be done? So first question, for those familiar with pearDB. It seems that certainly after doing a query() I should do a free(). What about getAll() (and related question, is there much difference between doing a query() and getAll())?). The docs seem to indicate a getOne() automatically frees resources, so I don't need to do it there. Next question is, what happens when no one refers to a result any more? Will a destructor get called that automatically frees the result? Or should I make sure to do the free before the reference is lost? How long are resources held if you don't free them? Finally, I assume that when you fetch data from a result that the data is copied and that it's not a reference, right? So as soon as I've done the necessary fetchRows() or whatever, I can safely free the result, right? Any other ideas what might be causing problems for mysql and/or how to track the problems? I believe my ISP is using linux with apache, and a fairly recent PHP and mysql. I can get more details on exact versions if that helps. Also, just as a general note, the basic format for most of my pages is: 1) connect to database 2) do some queries from PHP using pearDB 3) display results I don't specifically call disconnect() when I'm done. Should I? Sorry for the abundance of questions, and thanks for any help. Regards, Dave -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] OR on multiple columns
Jason Wong wrote: On Thursday 09 January 2003 08:36, David Chamberlin wrote: I was reading the mysql docs and noticed a section on searching on multiple keys (stupid question - keys=columns?). It says doing an OR on multiple keys is inefficient, and you should use a temp table. Here's their example: CREATE TEMPORARY TABLE tmp SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1'; INSERT INTO tmp SELECT field1_index, field2_index FROM test_table WHERE field2_index = '1'; SELECT * from tmp; DROP TABLE tmp; My question is, is there a point at which this temp table is less efficient? That may depend upon your particular circumstances, and hence running your own tests on both methods would give you the best answer. OK, so what's the best way to go about profiling this? Everything runs on my ISP's server. Thanks, Dave -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] OR on multiple columns
I was reading the mysql docs and noticed a section on searching on
multiple keys (stupid question - keys=columns?). It says doing an OR on
multiple keys is inefficient, and you should use a temp table. Here's
their example:
CREATE TEMPORARY TABLE tmp
SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1';
INSERT INTO tmp
SELECT field1_index, field2_index FROM test_table WHERE field2_index = '1';
SELECT * from tmp;
DROP TABLE tmp;
My question is, is there a point at which this temp table is less
efficient? What I have is a page where people can choose various things
that they might search on. I somewhat generically create the necessary
sql based on what criteria they've chosen. Unfortunately, one of the
queries includes searching for something that might show up in a number
of different columns. What's worse is that depending on what the user
might be searching on, I might need nearly all of the columns in the
later search. As a result, my tmp table would need to include all
columns that the original table has.
So here's effectively what I've done (note that db is a PEAR::DB):
$table = 'diverSearch';
$tableIsTemp = true;
$keysToSearch = array (
'userid', 'team_bottom1', 'team_bottom2', 'team_bottom3',
'team_support1', 'team_support2', 'team_additional'
);
$columnsToStore = "*";
$this->createTmpTable( $this->selectedView['divername'],
$columnsToStore,
$keysToSearch,
$this->reportsTable, $table );
And createTmpTable is:
function createTmpTable( $searchFor, &$columnsToStore, &$keysToSearch,
&$origTable, &$table ) {
$searchFor = $this->db->quote( $searchFor );
$columns = null;
if ( is_array( $columnsToStore ) ) {
$this->arrayToString( $columns, $columnsToStore );
} else {
$columns = $columnsToStore;
}
$sql = "CREATE TEMPORARY TABLE $table " .
"SELECT $columns FROM $origTable WHERE " .
"{$keysToSearch[0]} = {$searchFor} ";
// XXX why do I have to do these as separate queries. Shouldn't
// I be able to assemble one big query, where each line ends
// with semi-colon?
$result = $this->db->query( $sql );
$this->checkError( $result );
$count = count( $keysToSearch );
for( $i=1; $i<$count; $i++ ) {
$sql = "INSERT INTO $table " .
"SELECT $columns FROM $origTable WHERE " .
"{$keysToSearch[$i]} = {$searchFor} ";
$result = $this->db->query( $sql );
$this->checkError( $result );
}
}
And then my later searches are performed based on this temp table,
specifiying DISTINCT.
I'm curious if at this point, the creation of the temporary table with
all of this data is going to outweigh the inefficiencies of the "OR".
Also, as a minor side-note - every time I tried to do the createTmpTable
function by creating one big sql string, separating commands with a ';',
I got a syntax error. Is it not legal to do this? Is it
more/less/equal efficient to assemble one big query than to perform them
individually?
Thanks for any help.
-Dave
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] mysql time format
Paul Burney wrote: Why not add the TIME_FORMAT calls to the original query rather than performing all the extra queries? Is your first select something like the following? That's what I'd like to do, but not sure how to do that in this context. SELECT *,TIME_FORMAT(time_column_1,'%whatever') AS time_column_1 FROM table Aha! That's what I was looking for! If you don't know what all the time columns are, you could do a "SHOW COLUMNS FROM table" query first, then use PHP to parse the results to tell you which fields are time types, Right, that's basically what I've already done. When my class instantiates, it parses the columns and builds an array with the names of the columns that are time types. Currently I'm using that array to cycle through the results and issue TIME_FORMAT calls on all of the results, but what I wanted was to have the TIME_FORMAT in the original call. But I didn't know how to do that without listing all of the columns and also getting the result in the same name as the original. Your code snippet explains just what I wanted. Thank you! -Dave -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] mysql time format
Hey, This is a bit of a newbie question, but I couldn't find what I wanted in the documentation. Is there any way to set the time format so that any time values in my SELECT call are in a speicified format (e.g., HH:MM instead of HH:MM:SS). Basically I've got a table of a variety of different types, some of which are times. I want to issue a select that retrieves all of the data for a row, and have any time values be returned in HH:MM (for example). I can (very painfully) go through all the results, figure out which ones are time values, and then do SELECT TIME_FORMAT() on each one of those, but that seems like a horrendous solution to a simple problem. Any help? Thanks, Dave -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
