Re: [PHP-DB] Join on single table
Matthew Weier O'Phinney wrote: I have a table which contains the following: id (primary key, auto incrementing) app_id (integer, foreign key) resource_id (integer, foreign key) word score (This is a search index.) I want to find all resource_ids from one app_id that match resource_ids in another app_id by word. Is this at or near what you're looking for? I don't see any need for killing yourself making the temporary tables/etc. - but maybe I'm missing something. (obviously replace both 'tablename's with your tables name) SELECT b.app_id, b.resource_id, b.word, b.score FROM tablename AS a, tablename AS b WHERE a.app_id != b.app_id AND a.word = b.word; Cheers, -- - Martin Norland, Sys Admin / Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Excel headers not working anymore
Good morning, I have been using PHP4.1, Apache, and MySQL for a few years now and have never had a problem with my Excel headers. When I want to output html to Excel I place the following on the top of the page: ?header (Content-type: application/vnd.ms-excel); header (Content-Disposition: attachment ); After moving to PHP 4.3, MS SQL Server, Windows 2000 Server I am having problems with this header. I get the message: Internet Explorer cannot download myfile.php from myintranet.net The message does not always appear. Some of the files download, others do not. I have tried to find the problem with the following methods. They may shed some light on this problem: 1) I removed the header to see how the file outputs in HTML. They output fine. 2) I used my local server (which runs Apache and PHP4.3) with the same files and it downloads to excel perfectly! 3) I have compared the files that work with the ones that don't, removed anything that seems unusual, and replaced sections from the files that worked. There still does not seem to be any pattern. I thought the size of the file generated would play a roll but it doesn't. After shrinking the output to a single character, some files still do not work! Unfortunately it is more difficult to find support for PHP and Windows 2000 Server. I have some doubts that the files are not downloading because of default server security reasons (since some of the files work!). Does anyone know what might be causing this problem? - Matthew Perry -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Join on single table
* Martin Norland [EMAIL PROTECTED]: Matthew Weier O'Phinney wrote: I have a table which contains the following: id (primary key, auto incrementing) app_id (integer, foreign key) resource_id (integer, foreign key) word score (This is a search index.) I want to find all resource_ids from one app_id that match resource_ids in another app_id by word. Is this at or near what you're looking for? I don't see any need for killing yourself making the temporary tables/etc. - but maybe I'm missing something. (obviously replace both 'tablename's with your tables name) SELECT b.app_id, b.resource_id, b.word, b.score FROM tablename AS a, tablename AS b WHERE a.app_id != b.app_id AND a.word = b.word; I'd tried a similar query already. The issue with this particular approach is that the query takes forever to execute, even with good indexing. The temporary table approach speeds things up tremendously -- but it's still slower than I'd like. I finally hit on a solution last night, and did finally end up with a good index -- took several hours to run, but from now on I only have to do deltas. Basically, I do the following: * foreach resource_id in a single app_id, put a list of words into a temporary table * select resource_ids from another app_id by joining on the temporary table -- Matthew Weier O'Phinney | WEBSITES: Webmaster and IT Specialist | http://www.garden.org National Gardening Association| http://www.kidsgardening.com 802-863-5251 x156 | http://nationalgardenmonth.org mailto:[EMAIL PROTECTED] | http://vermontbotanical.org -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Excel headers not working anymore
Perry, Matthew (Fire Marshal's Office) wrote: Good morning, I have been using PHP4.1, Apache, and MySQL for a few years now and have never had a problem with my Excel headers. When I want to output html to Excel I place the following on the top of the page: ?header (Content-type: application/vnd.ms-excel); header (Content-Disposition: attachment ); After moving to PHP 4.3, MS SQL Server, Windows 2000 Server I am having problems with this header. I get the message: Internet Explorer cannot download myfile.php from myintranet.net 1) is your server still configured to recognize ? as php (and only php)? You may want to make that ?php to be more sure. The headers likely aren't being sent if it's prompting to download .php - seems like it's not recognizing the extension, perhaps it's disabled for a certain pattern. You could also try changing it to: header(Content-disposition: attachment; filename=\$filename\); to ensure that it's going off (set $filename obviously, something distinct like my_excel.xls) 2) Are you using ssl and sessions? You'll find some fun and strange behavior trying to save output from an ssl connection and sessions - because sessions force no-cache, and (IE in particular) has real issues with storing the output of a no-cache'd https - you can't save it locally or open with an external application, because either way it has to store it somewhere other than memory, which would be caching. Cheers, -- - Martin Norland, Sys Admin / Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Store $_SESSION in MySQL table
serialize the array before insertion and unserliaze it when you retreive it from the db bastien From: [EMAIL PROTECTED] To: php-db@lists.php.net (PHP) Subject: [PHP-DB] Store $_SESSION in MySQL table Date: Fri, 11 Feb 2005 17:25:34 + Greetings all. While trying to create a session-based advertising model that does not rely on the existence of cookies, I ran into trouble: I can't figure out how to place the entire $_SESSION array into a MySQL table row, then retrieve it and use the information from one page to the next. The best that I can do is copy the PHPSESSID into a MySQL row, then dissect the $_SESSION array so that each of its parts can be stored in a separate row of the same record. Then, with the $_GET variable, I am able to get the MySQL_last_id and retreive the information from the correct field, and it works across pages. The only problem is this: should the $_SESSION array grow with more data--say, adding a first name variable, and a time-of-day varaible--then I need to update the MySQL table whenever I do this. Not a big deal, I know, but I'm frustrated with this. One more thing: one of the variables in the $_SESSION array is an img src url, so it screws up the mysql insert, even when I use serialize. Thanks for any help you can give. --V -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Store $_SESSION in MySQL table
[EMAIL PROTECTED] wrote: Greetings all. While trying to create a session-based advertising model that does not rely on the existence of cookies, I ran into trouble: I can't figure out how to place the entire $_SESSION array into a MySQL table row, then retrieve it and use the information from one page to the next. The best that I can do is copy the PHPSESSID into a MySQL row, then dissect the $_SESSION array so that each of its parts can be stored in a separate row of the same record. Then, with the $_GET variable, I am able to get the MySQL_last_id and retreive the information from the correct field, and it works across pages. The only problem is this: should the $_SESSION array grow with more data--say, adding a first name variable, and a time-of-day varaible--then I need to update the MySQL table whenever I do this. Not a big deal, I know, but I'm frustrated with this. One more thing: one of the variables in the $_SESSION array is an img src url, so it screws up the mysql insert, even when I use serialize. Thanks for any help you can give. --V .oO Advertising? ack! - Should I help him or not? :P serialize() and base64encode() are your friend. You can even create a class to store this information, if you wanted - so long as you have the class include()'d before you unserialize the object. In all likelihood, however, you'll prefer just the straight $_SESSION. Cheers, -- - Martin Norland, Sys Admin / Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Excel headers not working anymore
Wow! Removing the file that controlled the session array fixed the problem (suggestion 2). I was using ssl and session data and would never have guessed it would trip things up. I would have stared at this problem for weeks and probably not have solved the problem. Also I will change my code to include ?php instead of ? from now on just to be sure. Thank you very much for your help. - Matthew Perry -Original Message- From: Martin Norland [mailto:[EMAIL PROTECTED] Sent: Friday, February 11, 2005 10:47 AM To: php-db@lists.php.net Subject: Re: [PHP-DB] Excel headers not working anymore Perry, Matthew (Fire Marshal's Office) wrote: Good morning, I have been using PHP4.1, Apache, and MySQL for a few years now and have never had a problem with my Excel headers. When I want to output html to Excel I place the following on the top of the page: ?header (Content-type: application/vnd.ms-excel); header (Content-Disposition: attachment ); After moving to PHP 4.3, MS SQL Server, Windows 2000 Server I am having problems with this header. I get the message: Internet Explorer cannot download myfile.php from myintranet.net 1) is your server still configured to recognize ? as php (and only php)? You may want to make that ?php to be more sure. The headers likely aren't being sent if it's prompting to download .php - seems like it's not recognizing the extension, perhaps it's disabled for a certain pattern. You could also try changing it to: header(Content-disposition: attachment; filename=\$filename\); to ensure that it's going off (set $filename obviously, something distinct like my_excel.xls) 2) Are you using ssl and sessions? You'll find some fun and strange behavior trying to save output from an ssl connection and sessions - because sessions force no-cache, and (IE in particular) has real issues with storing the output of a no-cache'd https - you can't save it locally or open with an external application, because either way it has to store it somewhere other than memory, which would be caching. Cheers, -- - Martin Norland, Sys Admin / Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Join on single table
* Micah Stevens [EMAIL PROTECTED]: Sounds like a self join should work wonders. I didn't test this, but the idea should work: select t1.app_id as a1, t2.app_id as a2, t2.word, t2.score from tablename as t1 left join tablename as t2 on t1.resource_id = t2.resource_id and t1.app_id != t2.app_id group by word order by word voila, no temp tables. Am I missing something? Speed. :-) I had tried this as well. The temporary tables really do offer much better performance. The issues I was having were (1) bad resultsets (I've now got that fixed) and (2) speed. I still don't have (2) completely fixed, and it may be something I can't fix. On Thursday 10 February 2005 07:56 pm, Matthew Weier O'Phinney wrote: I have a table which contains the following: id (primary key, auto incrementing) app_id (integer, foreign key) resource_id (integer, foreign key) word score (This is a search index.) I want to find all resource_ids from one app_id that match resource_ids in another app_id by word. I have created a temporary table 'tmp1' that contains all resource_ids from the second app_id (the one whose resources I wish to retrieve). I am then looping through all resource_ids in the main table with the first app_id, and doing the following: * Creating a temporary table tmp2 with a single column 'word' populated by the words associated with resource_id in the main table * Selecting all distinct resource_ids from tmp1 INNER JOIN'd on tmp2 on the word field The issues I'm running into are that (1) each resource_id cycle takes a good amount of time, and (2) I seem to be getting either too many resource_ids or not enough. (1) may be something I just have to deal with. As it is, I'm planning on running the full indexing once, and then doing incremental updates, so it may not be that big of an issue (unless it takes too much time to create the initial index). As for (2), unfortunately, I'm not sur ehow to really trouble shoot the issue. I know, for instance, that in once case, I have a list of 343 words that generates a list of ~12,000 resource_ids (of a possible 18,000) -- but I don't quite know how to spot check 300 values to be certain that this is reasonable. In a previous incarnation of the script, I was looping through each word of each resource_id and then selecting out of tmp1 based on the single word value. The results were very different (very few matches), and, again, the script ran long. Any ideas? -- Matthew Weier O'Phinney | WEBSITES: Webmaster and IT Specialist | http://www.garden.org National Gardening Association| http://www.kidsgardening.com 802-863-5251 x156 | http://nationalgardenmonth.org mailto:[EMAIL PROTECTED] | http://vermontbotanical.org -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Excel headers not working anymore
Perry, Matthew (Fire Marshal's Office) wrote:
Wow!
Removing the file that controlled the session array fixed the problem
(suggestion 2).
I was using ssl and session data and would never have guessed it would trip
things up. I would have stared at this problem for weeks and probably not
have solved the problem.
Also I will change my code to include ?php instead of ? from now on just
to be sure.
Thank you very much for your help.
- Matthew Perry
The following is what I use - it allows caching but forces it not to be
cached (it's a report, so it's changing every time). It's a little
redundant, because these items are a little bit of a pain to follow. I
have these running before the session is set - you may only need the
session_cache_limiter call, come to think, since I'm setting these
before the session starts and the others likely get overwritten.
?php
header('Cache-Control: private, must-revalidate');
header('Pragma: private'); // allow private caching
header(Last-Modified: . gmdate(D, d M Y H:i:s) . GMT); // set to
be modified 'now'
session_cache_limiter(private, must-revalidate); // allow private
caching, but cache MUST check output
// rest of script
?
Cheers,
--
- Martin Norland, Sys Admin / Database / Web Developer, International
Outreach x3257
The opinion(s) contained within this email do not necessarily represent
those of St. Jude Children's Research Hospital.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] issue with accents from a mysql table
Hello,
please help me on the following issue. I guess I should understand by
myself, but I don`t.
I have hacked a function accents_conv(---); to convert a character like
à to, say, agrave;.
When I feed it by a string - accents_textris('à') - it works fine.
The issue is when I get 'à' from a mysql table.
I.e., let à the value of the field Field of a mysql table Table, and say
$result = mysql_fetch_array($answer, MYSQL_BOTH),
where $answer= mysql_query(SELECT * FROM Table).
Now accents_textris($result['Field']) is still à (instead of agrave;).
Why?
Any hint is welcome.
Thanks a lot
see you tomorrow morning.
mario
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Join on single table
What kind of speed do you need? I had to go to temp tables for a logging application, but not until my table got upwards of a million records or so.. For the numbers your quoting, it should be pretty quick unless your engine needs optimization.. On Friday 11 February 2005 11:56 am, Matthew Weier O'Phinney wrote: * Micah Stevens [EMAIL PROTECTED]: Sounds like a self join should work wonders. I didn't test this, but the idea should work: select t1.app_id as a1, t2.app_id as a2, t2.word, t2.score from tablename as t1 left join tablename as t2 on t1.resource_id = t2.resource_id and t1.app_id != t2.app_id group by word order by word voila, no temp tables. Am I missing something? Speed. :-) I had tried this as well. The temporary tables really do offer much better performance. The issues I was having were (1) bad resultsets (I've now got that fixed) and (2) speed. I still don't have (2) completely fixed, and it may be something I can't fix. On Thursday 10 February 2005 07:56 pm, Matthew Weier O'Phinney wrote: I have a table which contains the following: id (primary key, auto incrementing) app_id (integer, foreign key) resource_id (integer, foreign key) word score (This is a search index.) I want to find all resource_ids from one app_id that match resource_ids in another app_id by word. I have created a temporary table 'tmp1' that contains all resource_ids from the second app_id (the one whose resources I wish to retrieve). I am then looping through all resource_ids in the main table with the first app_id, and doing the following: * Creating a temporary table tmp2 with a single column 'word' populated by the words associated with resource_id in the main table * Selecting all distinct resource_ids from tmp1 INNER JOIN'd on tmp2 on the word field The issues I'm running into are that (1) each resource_id cycle takes a good amount of time, and (2) I seem to be getting either too many resource_ids or not enough. (1) may be something I just have to deal with. As it is, I'm planning on running the full indexing once, and then doing incremental updates, so it may not be that big of an issue (unless it takes too much time to create the initial index). As for (2), unfortunately, I'm not sur ehow to really trouble shoot the issue. I know, for instance, that in once case, I have a list of 343 words that generates a list of ~12,000 resource_ids (of a possible 18,000) -- but I don't quite know how to spot check 300 values to be certain that this is reasonable. In a previous incarnation of the script, I was looping through each word of each resource_id and then selecting out of tmp1 based on the single word value. The results were very different (very few matches), and, again, the script ran long. Any ideas? -- Matthew Weier O'Phinney | WEBSITES: Webmaster and IT Specialist | http://www.garden.org National Gardening Association| http://www.kidsgardening.com 802-863-5251 x156 | http://nationalgardenmonth.org mailto:[EMAIL PROTECTED] | http://vermontbotanical.org -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
