[PHP-DB] Loading large volumes of data
This is more a process question that is not specific to PHP, but maybe someone can help me. I have a text file containing strings. The text file is pretty massive, about 895 MB. I need to load the words in the text file into the database in such a way that there is a single occurrence of each word in the table. What this means is the script has to read each word in the file, check if the word is in the table, and if not, add it. Obviously as the table grows, the rate of execution drops because the number of checks that have to be made increases as more words are added to the table. Does anyone know of an efficient way to do this? There are over a million lines in the file, and my script is at about 340 000 having run overnight. I need to get the data into the table much faster than that. I considered hashing the file in memory (ie, doing the uniqueness checking in an associative array) but am not sure how much RAM that would consume and I don't want to crash the server. I have also not indexed the table because my gut feel was that the overhead of constantly updating an indexed table would cancel out the gain of checking an indexed table. Is that true? Any and all help, comments, etc, appreciated as always. Thanks Evan Morris [EMAIL PROTECTED] Tel: +27 11 797 3840 Fax: +27 11 803 0211 Cell: +27 82 926 3630 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Loading large volumes of data
I feel a bit stupid now. Indexing the table works super fast by comparison. No idea what made me think the overhead on updating the index was going to outweigh the benefits. Anyway, thanks for the advice. - Original Message - From: graeme [EMAIL PROTECTED] To: Evan Morris [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, October 07, 2004 9:33 AM Subject: Re: [PHP-DB] Loading large volumes of data I'd think you want to index the table. Since you are doing a search on an un-index table for each word, that overhead will be more than creating an entry in an index table. Entering an index entry will require the routine to determine where the entry should be but it will be looking for that in a sorted list and so it will be able to determine if the entry exist or not fairly quickly, whereas the search on the unsorted list requires a search of all entries 340,000 and counting (a binary search would only require 19 comparisons ln N, sure there will still be some overhead on insert and the occasional re indexing) graeme. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Weird problem posting to database
Setup: Apache, PHP, Firebird backend Problem: PHP will not allow text boxes to POST more than about 1k. Page just hangs. Already checked: php.ini, max_post_size is 8M. Nothing referring to POST limitations in httpd.conf Any thoughts? Thanks Evan Morris [EMAIL PROTECTED] +27 11 792 2777 (t) +27 11 792 2711 (f) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Optimizing query for public search engine
Hi all I'm using Firebird as the back-end for a web site. The search engine is written in PHP, using bog-standard SQL queries. Here's the problem: In order to make the results listing work well, the page needs to know how many records match the search criteria. This is so I can a) display '1 to 15 of 215 results' at the top of the page, and b) have intelligent navigation at the bottom (no point having a 'Next' button if there are no more records). The way I solved this problem originally was the have the search engine build two SQL queries, one a SELECT COUNT query and one the actual SELECT FIRST 15 SKIP x etc query. The SELECT COUNT query should run first (obviously only if this is the first page of the result set), return the size of the result set, and then the actual query should execute. Seems logical. And it works, after a fashion. However, the SELECT COUNT query is ridiculously time-consuming. While I can get out the first 15 records in a matter of microseconds, counting the total number of records matching the search criteria can sometimes take more than two *minutes*, depending on the structure of the query. So the question is, how do you resolve this issue? Am I missing something obvious? Since almost every search results listing I have ever seen contains this functionality, I assume the answer must be fairly simple and well-known. Anyone care to pass it on? Thanks Evan Morris [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Optimizing query for public search engine -- SORT OF SOLVED
In case anyone is interested, I have (sort of) solved this problem. Importantly, I learned that Firebird does not support PEAR DB functions like numRows(), so you can't do it that way (at least, my version of Firebird doesn't). So what I have done is run the SELECT FIRST 15 SKIP x etc query - ie the actual data query - twice. The first time I just increment a counter variable until I get to the page limit+1 and then break out of the query. The second time I actually fetch the required data. Amazingly, this works *much* faster than issuing a SELECT COUNT query. Go figure. It still seems like a clunky solution, but it has helped somewhat. - Original Message - From: Evan Morris [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, November 26, 2003 12:01 PM Subject: [PHP-DB] Optimizing query for public search engine Hi all I'm using Firebird as the back-end for a web site. The search engine is written in PHP, using bog-standard SQL queries. Here's the problem: In order to make the results listing work well, the page needs to know how many records match the search criteria. This is so I can a) display '1 to 15 of 215 results' at the top of the page, and b) have intelligent navigation at the bottom (no point having a 'Next' button if there are no more records). The way I solved this problem originally was the have the search engine build two SQL queries, one a SELECT COUNT query and one the actual SELECT FIRST 15 SKIP x etc query. The SELECT COUNT query should run first (obviously only if this is the first page of the result set), return the size of the result set, and then the actual query should execute. Seems logical. And it works, after a fashion. However, the SELECT COUNT query is ridiculously time-consuming. While I can get out the first 15 records in a matter of microseconds, counting the total number of records matching the search criteria can sometimes take more than two *minutes*, depending on the structure of the query. So the question is, how do you resolve this issue? Am I missing something obvious? Since almost every search results listing I have ever seen contains this functionality, I assume the answer must be fairly simple and well-known. Anyone care to pass it on? Thanks Evan Morris [EMAIL PROTECTED] -- 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
[PHP-DB] Apostrophe problem on Firebird
This problem has probably already been solved (and may even already be a FAQ), but I can't find the answer and I've tried various things. I have data in a database that may contain apostrophes. I am passing variables from a form to a search facility. Essentially, I want a sql query that looks like this: SELECT x FROM y WHERE z = 'o'malley' Now, you can't pass that as is to Firebird, because it lops it off after the 'o' and tells you it doesn't know what malley is. Fine. Why not just pass your thing to addslashes()? Well, I did that, resulting in: SELECT x FROM y WHERE z = 'o\'malley' But at the Firebird level, this has the same result. It now chops it off after the 'o\', and tells you it doesn't know what malley is. Hm. So I tried this at the Firebird level (command line): SELECT x FROM y WHERE z = o'malley Brilliant. Works great. But not I have a problem, since my SQL query is in a variable, and obviously the literal to the variable is being enclosed in double quotes. So, I think, great, let's just backslash the double-quotes. So in my PHP, I have: $sql=SELECT x FROM y WHERE z = \o'malley\; This doesn't produce any freakouts from PHP ... but now Firebird is complaining. It says there is no such *column* as o'malley. Well, I know that, right? Apparently, Firebird thinks I'm trying to compare two columns. So how *do* you solve this relatively simple problem? I want users to pass me data that may have apostrophes in it, and look for that data in a Firebird table. Evan Morris [EMAIL PROTECTED] +27 11 792 2777 (t) +27 11 792 2711 (f) - Is /usr/bin/perl related to /osama/bin/ladin? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] SQL Join problem (Firebird)
Hi all
Say you have two tables, TABLE1 and TABLE2. Each table contains (amongst
others) a field called SOMEVALUE
Now, in TABLE1 SOMEVALUE contains string1, but in TABLE2 SOMEVALUE
contains string2.
Now you join these tables (on SOMEOTHERVALUE) and you loop through the
results set:
while ($row = ibase_fetch_object($sth)) {
echo $row - SOMEVALUEbr;
};
This only echoes the value of SOMEVALUE last referred to in your SQL
statement (obviously, because it has no way of differentiating between the
two versions of SOMEVALUE).
However, it does not work to do
echo $row-TABLE1.SOMEVALUEbr;
This has unexpected results (it echoes the literal text SOMEVALUE).
So, what's the answer? Simply rename one of the SOMEVALUE fields in the
database? Or is there a way to differentiate them at the PHP level?
I know one can alias them in the SQL statement itself, like SELECT
t1.SOMEVALUE, t1.SOMEOTHERVALUE, t2.SOMEVALUE, t2.SOMEOTHERVALUE from TABLE1
t1 JOIN TABLE2 t2 ON t1.SOMEOTHERVALUE = t2.SOMEOTHERVALUE
However, you cannot go
echo $row-t1.SOMEVALUEbr
since this again echoes the literal text SOMEVALUE (ie, the field name).
Any and all help appreciated.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] How do I make PHP aware of Interbase/Firebird?
Hi Please help. I have installed Apache and PHP, and they are working together. I have Firebird installed on a different server. I want to have my PHP pages to talk to the remote database, but when I try to use the Interbase functions, I get: Fatal error: Call to undefined function: ibase_connect() Clearly, my PHP does not know about Interbase/Firebird. Do I edit the php.ini file? Do I have to reinstall PHP? If I have to reinstall, is it possible to do it from YAST or will I have to compile the code with specific options? Is there documentation about this anywhere? Please assist. Thanks Evan Morris [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Interbase queries
Hi I am new to using PHP with Interbase. If I want to issue a query, I use: $sth = ibase_query ($dbh, $sqlstmt); I am assuming this issues the query to the database, and commits at the same time? Do I need to release anything after having done this, or does it take care of that by itself? Thanks Evan Morris [EMAIL PROTECTED] Tel: +27 11 792 2777 Fax: +27 11 792 2711 Cell: +27 82 926 3630 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] hmmm
SAY WHAT? WHO ARE YOU TALKING TO? HAVE YOU GONE INSANE? IS YOUR MOTHER A GERBIL? DO YOU KNOW HOW STUPID YOU SOUND WHEN YOU SEND THESE UTTERLY BIZARRE, VERBALLY ABUSIVE NOTES FROM THE VOID TO AN ENTIRE MAILING LIST OF PEOPLE WHO HAVE NO IDEA WHAT YOU'RE TALKING ABOUT? AND USING CAPS ALL THE TIME INDICATES A LOW INTELLECT (OR IN MY CASE, A PARODY, BUT THAT DOESN'T BEAR MENTIONING). YOU NEED HELP. NOW GO AWAY AND THINK CAREFULLY ABOUT YOUR BEHAVIOUR. IS THIS THE BEHAVIOUR OF A STABLE, ADULT MEMBER OF SOCIETY? I DON'T THINK SO. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, July 01, 2003 11:38 AM Subject: [PHP-DB] hmmm YOU FUCKING SHIT!!! UNSUBSCRIBE MY EMAIL PLEASE !!! GOD VERDOMME !!! UNSUBSCRIBE IT NOW,MOTHER FUCKER BABI LOE BANGSAT LOE KONTOL LOE !!! SIKTIR GIT!!! YALLA!!! HOER !!! GO AWAY FROM MY MAIL BOX WILL YA!!! FUCK SHIT U BASTARD MOTHER FUCKER ASSHOLE !!! PECUN LOE BABI LOE JANCUK !!! -- -- 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] More help with mysql
- Original Message - From: Max 'AMiGo' Gashkov [EMAIL PROTECTED] To: Evan Morris [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, February 18, 2003 10:13 AM Subject: Re: [PHP-DB] More help with mysql Possible error in SQL statement. Are you sure that SOME_TABLE exists? Yes. I can connect to it from Perl, using the same user-pass credentials, and fetch our all the data without any problems. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] More help with mysql
Since I'm new to PHP, this concept is new to me. What does the @ do and where do you put it? - Original Message - From: Corne' Cornelius [EMAIL PROTECTED] To: Evan Morris [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, February 18, 2003 10:19 AM Subject: Re: [PHP-DB] More help with mysql Yo Evan, Have you got an '@' in front of the mysql_query() to supress errors ? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] More help with mysql -- solved (bizarre)
If I remove the '|| die' part from the mysql_query() statement, it works fine. This is bizarre, but there it is. ie, if I have: $result = mysql_query(SELECT * FROM SOME_TABLE); it works. If I have: $result = mysql_query(SELECT * FROM SOME_TABLE) || die (Unable to execute SQL query); it doesn't work. It gives me: Supplied argument is not a valid MySQL result resource Anyway, since it works I don't suppose I should complain, but it seems pretty weird to me nevertheless. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] More help with mysql
Hi all
I have successfully managed to connect to the mysql database, using the
following code:
mysql_connect('localhost','php','***') || die (Unable to connect to
MySQL server.);
$db = mysql_select_db(DB_NAME) || die (Unable to select requested
database.);
I then issue the following command:
$result = mysql_query(SELECT * FROM SOME_TABLE) || die(mysql_error());
This does not result in any error.
Now, of course, I want to work with the data returned, so I go:
if (mysql_num_rows($result) 0)
{
// do some stuff
}
else
{
// do some other stuff
};
However, this gives me: Supplied argument is not a valid MySQL result
resource.
If I go: echo $result, I get '1', so I know $result actually has some
value.
What am I doing wrong? Basically, I want to do the simplest thing: get data
out of a table and step through the results, displaying them one by one. Why
is this hard?
Evan Morris
[EMAIL PROTECTED]
Tel: +27 11 792 2777
Fax: +27 11 792 2711
Cell: +27 82 926 3630
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL problem -- new to PHP
Thanks for all the help everyone sent me on this. However, nothing seems to make any difference whatsoever (except putting the 'localhost' part in quotes). I am still getting permission denied errors based on the user credentials. I have entered a user into the user table with host='localhost', user='php' and a password. I have entered these credentials into the db table also, and given the various privileges for this user to the specific database. I have run both mysqladmin reload and mysqladmin flush-privileges. I have even rebooted the machine. I have also tried typing GRANT SELECT,INSERT,DELETE,UPDATE TO php@localhost IDENTIFED BY ***, but this just throws up an error at the MySQL prompt. My PHP page can still not connect to the database. It just says: MySQL Connection Failed: Access denied for user: 'php@localhost' (Using password: YES) I know the problem is a permissions thing, because if I connect to the test database as 'nobody', there is no problem. Again, any and all help will be appreciated. This is ridiculously frustrating, especially since I have no problem connecting to the database from a perl script. I am tempted to stop trying to learn PHP and build my entire web site using perl cgi instead. Thanks Evan Morris [EMAIL PROTECTED] Tel: +27 11 792 2777 Fax: +27 11 792 2711 Cell: +27 82 926 3630 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL problem -- new to PHP
Hi all I am new to PHP (just basically started yesterday). I am currently having a problem connecting to a MySQL database. My sample code is: -- ?php mysql_connect(localhost,username,password) or die (Unable to connect to MySQL server.); $db = mysql_select_db(DB_NAME) or die (Unable to select requested database.); ? --- This throws the following error: --- Warning: MySQL Connection Failed: Host 'my.host.name' is not allowed to connect to this MySQL server --- Now, the mySQL server and the web server reside on the same machine. This warning is therefore saying that this machine does not have permission to connect to itself. Hmm. I have put entries in the host table and the user table, using both hostname and ip address, but no luck. I keep getting the same error. What am I doing wrong? Any and all help appreciated. Thanks Evan Morris [EMAIL PROTECTED] +27 11 792 2777 (tel) +27 11 792 2711 (fax) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
