[PHP-DB] Re: [PHP] PDO buffered query problem
Stewart Duncan wrote: Hi there, I'm having some serious problems with the PHP Data Object functions. I'm trying to loop through a sizeable result set (~60k rows, ~1gig) using a buffered query to avoid fetching the whole set. No matter what I do, the script just hangs on the PDO::query() - it seems the query is running unbuffered (why else would the change in result set size 'fix' the issue?). Here is my code to reproduce the problem: ?php $Database = new PDO( 'mysql:host=localhost;port=3306;dbname=mydatabase', 'root', '', array( PDO::ATTR_ERRMODE = PDO::ERRMODE_EXCEPTION, PDO::MYSQL_ATTR_USE_BUFFERED_QUERY = true ) ); Don't you want it the other way around? You want it unbuffered so it doesn't get retrieved in mysql, send the whole result set to php then you can use it. You want to start using it immediately - so make it unbuffered. Either way if the result set is going to be large your MySQL's memory or PHP's memory may exceed. So if you use either you may need to fine-tune PHP by increasing the per process memory (memory_limit in php.ini) or MySQL. In case you use unbuffered queries you cannot use transactions as far as I can recall. -- Thodoris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: [PHP] PDO buffered query problem
Thodoris wrote: Stewart Duncan wrote: Hi there, I'm having some serious problems with the PHP Data Object functions. I'm trying to loop through a sizeable result set (~60k rows, ~1gig) using a buffered query to avoid fetching the whole set. No matter what I do, the script just hangs on the PDO::query() - it seems the query is running unbuffered (why else would the change in result set size 'fix' the issue?). Here is my code to reproduce the problem: ?php $Database = new PDO( 'mysql:host=localhost;port=3306;dbname=mydatabase', 'root', '', array( PDO::ATTR_ERRMODE = PDO::ERRMODE_EXCEPTION, PDO::MYSQL_ATTR_USE_BUFFERED_QUERY = true ) ); Don't you want it the other way around? You want it unbuffered so it doesn't get retrieved in mysql, send the whole result set to php then you can use it. You want to start using it immediately - so make it unbuffered. Either way if the result set is going to be large your MySQL's memory or PHP's memory may exceed. So if you use either you may need to fine-tune PHP by increasing the per process memory (memory_limit in php.ini) or MySQL. The point of unbuffered queries is that they only take up memory while transferring a particular row's data. It won't get the whole lot from mysql, store it all in php (taking up memory for all x rows in the result set), then start working on it. So it won't take up that much memory - you'll only ever have one row of data in memory. Of course, the downside is that you can't do another query until the whole result has been processed. Personally I'd never try to work on 60k items at once, I'd chunk my results (work on 1000 items at a time) and go from there, but that of course depends on what needs to be done. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Subject: PDO buffered query problem
At 13:40 24/02/2009, you wrote:
Message-ID: c4.82.23283.768e2...@pb1.pair.com
To: php-db@lists.php.net,php-gene...@lists.php.net
Date: Mon, 23 Feb 2009 18:16:01 +
From: Stewart Duncan do...@gmx.net
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Subject: PDO buffered query problem
Hi there,
I'm having some serious problems with the PHP Data Object functions.
I'm trying to loop through a sizeable result set (~60k rows, ~1gig)
using a buffered query to avoid fetching the whole set.
No matter what I do, the script just hangs on the PDO::query() - it
seems the query is running unbuffered (why else would the change in
result set size 'fix' the issue?).
Just for completeness in understanding - the 60k rows does not
correspond to 1Gb data - that's for the full rows right ?
If not, that would imply your ID field below is ~18000 characters
long which seems (cough) extreme !
If your ID field is still of substantial size, you could consider a
surrogate key, eg an autoincrement integer to keep sizes small.
IMO, requiring 6 rows in a result set might indicate that your
application is configured to do much of the work otherwise done by
the DB. Without any specifics it's hard to say, but be sure you
actually ~need~ to fetch all the rows instead of returning a more
limited resultset by better specifying the SQL.
As one other poster noted, you *could* be reaching PHP's memory limit
: It's not configured that high in default installs - often 2 or 8MB
- and the script will terminate silently in that case, unless you
have error_reporting(E_ALL) and/or error logging switched on in your
application to debug this.
If it's a production server, you'll need to check the error logs, as
you won't be displaying the errors to screen ( -right - ? ;-))
Finally - try to make sure you're running the mysqldnd MySQL client
driver with a recent (PHP 5.3+) install with MySQL4.1
http://dev.mysql.com/downloads/connector/php-mysqlnd/
http://uk.php.net/manual/en/mysqli.mysqlnd.php
http://uk.php.net/manual/en/mysqli.installation.php
That tends to use 1/2 the memory as the resultset is only stored in
one place rather than 2
Oh - and I imagine that is repro code, but check if you had code in
the script prior to those isolated lines which doesn't close
resultsets or release statement resources, to make sure you're not
consuming and not releasing memory during the script execution.
On the MySQL side, if you're really returning large BLOBs and not the
ID you specified below, read
http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html
One final top place to search for tips and ideas is always
http://www.mysqlperformanceblog.com (buy the book if you get the
chance, it's excellent)
HTH
Cheers - Neil
Here is my code to reproduce the problem:
?php
$Database = new PDO(
'mysql:host=localhost;port=3306;dbname=mydatabase',
'root',
'',
array(
PDO::ATTR_ERRMODE = PDO::ERRMODE_EXCEPTION,
PDO::MYSQL_ATTR_USE_BUFFERED_QUERY = true
)
);
$rQuery = $Database-query('SELECT id FROM mytable');
// This is never reached because the result set is too large
echo 'Made it through.';
foreach($rQuery as $aRow) {
print_r($aRow);
}
?
If I limit the query with some reasonable number, it works fine:
$rQuery = $Database-query('SELECT id FROM mytable LIMIT 10');
I have tried playing with PDO::MYSQL_ATTR_MAX_BUFFER_SIZE and using
the PDO::prepare() and PDO::execute() as well (though there are no
parameters in the above query), both to no avail.
Any help would be appreciated,
Stewart
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: [PHP] PDO buffered query problem
Stewart Duncan wrote: Hi there, I'm having some serious problems with the PHP Data Object functions. I'm trying to loop through a sizeable result set (~60k rows, ~1gig) using a buffered query to avoid fetching the whole set. No matter what I do, the script just hangs on the PDO::query() - it seems the query is running unbuffered (why else would the change in result set size 'fix' the issue?). Here is my code to reproduce the problem: ?php $Database = new PDO( 'mysql:host=localhost;port=3306;dbname=mydatabase', 'root', '', array( PDO::ATTR_ERRMODE = PDO::ERRMODE_EXCEPTION, PDO::MYSQL_ATTR_USE_BUFFERED_QUERY = true ) ); Don't you want it the other way around? You want it unbuffered so it doesn't get retrieved in mysql, send the whole result set to php then you can use it. You want to start using it immediately - so make it unbuffered. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Could not run query
Have you tested this SQL query? Insert it into phpmyadmin, does it work? If it does = php error. If it doesn't = mysql error. boclair napsal(a): I need help to track down the cause of the error,Could not run query A typical query is $laterrecords = mysql_query(SELECT * FROM messages WHERE `logged` $timebegin ORDER BY `logged` DESC ) or die (Cannot select:br$querybrError: . mysql_error()); Louise -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Could not run query
I need help to track down the cause of the error,Could not run query A typical query is $laterrecords = mysql_query(SELECT * FROM messages WHERE `logged` $timebegin ORDER BY `logged` DESC ) or die (Cannot select:br$querybrError: . mysql_error()); Try putting single quotes around $timebegin. If $timebegin is not a Unix timestamp, you can also use the DATE functions in MySQL, A good way to debug these problems is to always put the query in a separate variable $query = SELECT * FROM messages ... //echo $query; //exit; $laterrecords = mysql_query($query); Then echo the actual interpolated query to the screen, copy it, and run it manually in a query tool (phpMyAdmin, MySQLQueryBrowser, or similar). The query tool will tell you where the error is. SK -- Sandy Keathley Zend Certified Engineer Senior Programmer The Dent Zone Companies, Inc. 214-393-2228 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: explode () and mySQL query
I am not sure why this isn't bringing any results:
WHERE shopping_cart_product.product_description IN ('straying')
The word straying is in the midst of one of the descriptions.
Ron
On Wed, 2008-05-28 at 20:13 -0400, Ron Piggott wrote:
I am trying to add a search feature to my shopping cart.
I am wanting to use PHP to develop the query for mySQL. I have the
following code figured out:
foreach(explode( , $keyword) as $key) $query .= $key, ;
This produces:
WHERE shopping_cart_product.product_description IN ( Jesus, is, Lord, )
The weakness is the trailing , after the last word the user types in.
How can I eliminate this?
Ron
--
[EMAIL PROTECTED]
www.actsministrieschristianevangelism.org
Acts Ministries Christian Evangelism
Where People Matter
12 Burton Street
Belleville, Ontario, Canada K8P 1E6
In Belleville Phone : (613) 967-0032
In North America Call Toll Free : (866) ACTS-MIN
Fax: (613) 967-9963
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: explode () and mySQL query
Ron Piggott wrote:
I am not sure why this isn't bringing any results:
WHERE shopping_cart_product.product_description IN ('straying')
The word straying is in the midst of one of the descriptions.
Ahh, oops - didn't notice that before.
IN() looks for specific entries, so unless the entry is straying and
*only* straying it will not be found.
You can either use full text indexes
(http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html) or like
searches:
where description like '%straying%' or description like '%another word%';
full-text works best on a big, random database (if you have a lot of
keywords that are the same it won't work very well).
--
Postgresql php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Help with JOIN query
Hi! You can't use the MAX() function if you're not using a GROUP BY clause. The MAX() function can only grab the maximum value of a grouped column, as with MIN(), COUNT(), AVG() etc.. Greetings, Krister Karlström, Helsinki Jonathan Crawford wrote: I think this is what you mean. You just want the timestamp and action from B in addition to something from A (I guessed product_ref), right? The MAX() function should take care of getting the latest timestamp. explicit join: SELECT MAX(TableB.timestamp), TableB.action, TableA.product_ref FROM TableB JOIN TableA ON TableA.record_id = TableB.record_id ORDER BY TableB.action or if you want to join your tables implicitly in your WHERE clause, similar to what you had before, implicit join: SELECT MAX(TableB.timestamp), TableB.action, TableA.product_ref FROM TableB WHERE TableA.record_id = TableB.record_id ORDER BY TableB.action The problem with the implicit joins versus explicit joins is that you can't ever do OUTER JOINs, where you want many from one table and one (or many) from another table. For example if you want all sales reps and their sales, even if they don't have any. Implicit (or explicit INNER) JOINs will not show you all of the data. Jonathan Crawford [EMAIL PROTECTED] Original message Date: 6 Mar 2008 18:46:18 - From: [EMAIL PROTECTED] Subject: php-db Digest 6 Mar 2008 18:46:18 - Issue 3990 To: php-db@lists.php.net php-db Digest 6 Mar 2008 18:46:18 - Issue 3990 Topics (messages 44700 through 44700): Help with JOIN query 44700 by: Graham Cossey Administrivia: To subscribe to the digest, e-mail: [EMAIL PROTECTED] To unsubscribe from the digest, e-mail: [EMAIL PROTECTED] To post to the list, e-mail: php-db@lists.php.net -- Date: Thu, 6 Mar 2008 18:46:14 + From: Graham Cossey [EMAIL PROTECTED] Subject: Help with JOIN query To: php-db@lists.php.net I can't see how to accomplish what I need so if anyone has any suggestions they would be gratefully received... I'm using mysql 4.0.20 by the way. I have two tables : TableA record_id product_ref TableB timestamp record_id action I want to create a SELECT that joins these 2 tables where the JOIN to TableB only returns the most recent entry by timestamp. At present (using PHP) I do a SELECT on TableA then for each record returned I perform a 2nd SELECT something like : SELECT timestamp, action FROM TableB WHERE record_id = '$record_id' ORDER BY timestamp DESC LIMIT 1 I now want to do it with one query to enable sorting the results by 'action' from TableB. Any suggestions? Hopefully I've made sense, if not I'll happily try and explain further on request. -- Graham -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Help with JOIN query
Yes, I'm totally with you Graham and I have created your tables and added some test data (you saw it in a previous post).. But I can't seem to come up with any solution for you, at least with MySQL 4.0.x. So if no one else has any brilliant solutions for this I think you'll need to stick with your solution to make the request in two steps, using PHP and an array to sort and put everyting together. Now, hopefully, you don't have hundreds of kilos of rows in your tables.. :) There might be solutions for this problem with never versions of MySQL, like using subqueries combined with joins like someone mentioned, but I'm not sure. I however, have never tested (or even needed to test) something like that, so I can't help you with that. If it is not important to keep the history of actions for each product you could simply update TableB, but I'll guess that's not the case - otherwise you wouldn't probably been asking us.. :) Another solution would be to move the previous action to an other table, like TableC for instance... Then TableC would be your history, TableB would only have your latest action.. But then again, we would have a one-to-one relation which basically means that you don't need two tables anymore. But you could at least consider this, if you are able to alter the database design a bit. Greetings, Krister Karlström, Helsinki Graham Cossey wrote: Thank you for your input Jonathan but it's not quite what I need. I need the latest action from TableB (if it exists) determined by the record_id matching TableA and where there are more than one matching record in TableB select the one with the latest timestamp. As an over-simplified example of what I'm trying to achieve : TableA record_id product_ref 1 product1 2 product2 TableB timestamp record_id action 20080301 1start 20080302 1middle 20080301 2start 20080302 2middle 20080303 2end What I need returned is : 1,product1,middle 2,product2,end --- Graham -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Help with JOIN query
I think this is what you mean. You just want the timestamp and action from B in addition to something from A (I guessed product_ref), right? The MAX() function should take care of getting the latest timestamp. explicit join: SELECT MAX(TableB.timestamp), TableB.action, TableA.product_ref FROM TableB JOIN TableA ON TableA.record_id = TableB.record_id ORDER BY TableB.action or if you want to join your tables implicitly in your WHERE clause, similar to what you had before, implicit join: SELECT MAX(TableB.timestamp), TableB.action, TableA.product_ref FROM TableB WHERE TableA.record_id = TableB.record_id ORDER BY TableB.action The problem with the implicit joins versus explicit joins is that you can't ever do OUTER JOINs, where you want many from one table and one (or many) from another table. For example if you want all sales reps and their sales, even if they don't have any. Implicit (or explicit INNER) JOINs will not show you all of the data. Jonathan Crawford [EMAIL PROTECTED] Original message Date: 6 Mar 2008 18:46:18 - From: [EMAIL PROTECTED] Subject: php-db Digest 6 Mar 2008 18:46:18 - Issue 3990 To: php-db@lists.php.net php-db Digest 6 Mar 2008 18:46:18 - Issue 3990 Topics (messages 44700 through 44700): Help with JOIN query 44700 by: Graham Cossey Administrivia: To subscribe to the digest, e-mail: [EMAIL PROTECTED] To unsubscribe from the digest, e-mail: [EMAIL PROTECTED] To post to the list, e-mail: php-db@lists.php.net -- Date: Thu, 6 Mar 2008 18:46:14 + From: Graham Cossey [EMAIL PROTECTED] Subject: Help with JOIN query To: php-db@lists.php.net I can't see how to accomplish what I need so if anyone has any suggestions they would be gratefully received... I'm using mysql 4.0.20 by the way. I have two tables : TableA record_id product_ref TableB timestamp record_id action I want to create a SELECT that joins these 2 tables where the JOIN to TableB only returns the most recent entry by timestamp. At present (using PHP) I do a SELECT on TableA then for each record returned I perform a 2nd SELECT something like : SELECT timestamp, action FROM TableB WHERE record_id = '$record_id' ORDER BY timestamp DESC LIMIT 1 I now want to do it with one query to enable sorting the results by 'action' from TableB. Any suggestions? Hopefully I've made sense, if not I'll happily try and explain further on request. -- Graham -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Help with JOIN query
On Fri, Mar 7, 2008 at 12:27 AM, Jonathan Crawford [EMAIL PROTECTED] wrote: I think this is what you mean. You just want the timestamp and action from B in addition to something from A (I guessed product_ref), right? The MAX() function should take care of getting the latest timestamp. explicit join: SELECT MAX(TableB.timestamp), TableB.action, TableA.product_ref FROM TableB JOIN TableA ON TableA.record_id = TableB.record_id ORDER BY TableB.action or if you want to join your tables implicitly in your WHERE clause, similar to what you had before, implicit join: SELECT MAX(TableB.timestamp), TableB.action, TableA.product_ref FROM TableB WHERE TableA.record_id = TableB.record_id ORDER BY TableB.action The problem with the implicit joins versus explicit joins is that you can't ever do OUTER JOINs, where you want many from one table and one (or many) from another table. For example if you want all sales reps and their sales, even if they don't have any. Implicit (or explicit INNER) JOINs will not show you all of the data. Jonathan Crawford [EMAIL PROTECTED] Thank you for your input Jonathan but it's not quite what I need. I need the latest action from TableB (if it exists) determined by the record_id matching TableA and where there are more than one matching record in TableB select the one with the latest timestamp. As an over-simplified example of what I'm trying to achieve : TableA record_id product_ref 1 product1 2 product2 TableB timestamp record_id action 20080301 1start 20080302 1middle 20080301 2start 20080302 2middle 20080303 2end What I need returned is : 1,product1,middle 2,product2,end --- Graham -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] RE: [PHP] Lost in query... Trying to get output from query....
Hi,
You need to loop through the returned recordset and use the returned
result(s).
Instead of:
$admin_get_options_result =
mysql_fetch_assoc($admin_get_options_results_reference);
Try:
While ($admin_get_options_result =
mysql_fetch_assoc($admin_get_options_results_reference)){
echo p The api# .$admin_get_options_result[adminpageid].
allows you to .$admin_get_options_result[description]., and uses the
file .$admin_get_options_result[filename]./p;
}
Mysql_free_result($admin_get_options_results_reference);
Hope this helps...
Much warmth,
Murray
-Original Message-
From: GH [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 12 October 2004 12:59 AM
To: PHP General; [EMAIL PROTECTED]
Subject: [PHP] Lost in query... Trying to get output from query
Greetings:
I am having a problem with trying to get data out of my mySql database.
I run my query which works fine and everything...
I use:
$admin_get_options_query_text = Select * from adminpage;
$admin_get_options_results_reference =
mysql_query($admin_get_options_query_text,$db_access) or die(Admin
Get Options: . mysql_error());
$admin_get_options_result =
mysql_fetch_assoc($admin_get_options_results_reference);
-
When I run the query in mysql directly the results are.
mysql Select * from adminpage;
+-++
--+
| adminpageid | file_name | description
|
+-++
--+
| 101 | nycalertstatus.php | Change New York City Threat Alert
Status |
+-++
--+
1 row in set (0.00 sec)
my problem is that I would like to get that output into my php
codeI would like to have something that goes like this for each
record..
The api# $adminpageid allows you to $description, and uses the file
#file_name
can anyone please assist.
--
PHP General 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] Re: [PHP] Lost in query... Trying to get output from query....
GH wrote:
Greetings:
I am having a problem with trying to get data out of my mySql database.
I run my query which works fine and everything...
I use:
$admin_get_options_query_text = Select * from adminpage;
$admin_get_options_results_reference =
mysql_query($admin_get_options_query_text,$db_access) or die(Admin
Get Options: . mysql_error());
$admin_get_options_result =
mysql_fetch_assoc($admin_get_options_results_reference);
If the query is going to return multiple rows, you need to loop thru the
result set...
while ( $admin_get_options_result =
mysql_fetch_assoc($admin_get_options_results_reference) ) {
// do stuff with each row returned
}
--
John C. Nichel
ÜberGeek
KegWorks.com
716.856.9675
[EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: [PHP] Lost in query... Trying to get output from query....
Thank You Murray's suggestion worked
On Mon, 11 Oct 2004 11:20:57 -0400, John Nichel [EMAIL PROTECTED] wrote:
GH wrote:
Greetings:
I am having a problem with trying to get data out of my mySql database.
I run my query which works fine and everything...
I use:
$admin_get_options_query_text = Select * from adminpage;
$admin_get_options_results_reference =
mysql_query($admin_get_options_query_text,$db_access) or die(Admin
Get Options: . mysql_error());
$admin_get_options_result =
mysql_fetch_assoc($admin_get_options_results_reference);
If the query is going to return multiple rows, you need to loop thru the
result set...
while ( $admin_get_options_result =
mysql_fetch_assoc($admin_get_options_results_reference) ) {
// do stuff with each row returned
}
--
John C. Nichel
ÜberGeek
KegWorks.com
716.856.9675
[EMAIL PROTECTED]
--
PHP General 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] Re: Re: SQL Server Query Failed
Thank you, Bob, for your continuing comments. The intranet site is used by approximately 100 people, and sees a good amount of traffic every day. I don't really have an exact number of daily queries that are executed, but it has to be a pretty sizable number, since the site handles much of the business issues. The query in question fails randomly (as far as I can tell: I have been watching for patterns for some time now.) If it always failed, that would really help. Sigh. Random. Naturally. Worse, the query is 100% dynamic, and built by the users (but of course they do not know they are building a query. They are just selecting things to report on, which is the way it should be, in my opinion). So, I don't have a query I can show as always fails, or sometimes fails. The only common thread in all of this is the tables in question. There are only a couple of tables, and they are used in the datawarehouse for reporting. Oddly, though (and I cannot prove this as much as I would like to) I think that some of the failures happen when no one else is using that table. All the other intranet tables perform as expected (and there are a LOT of those). I will look at the Apache 2/multi-process question. It is a great thought. I have another problem (this one is more of a how in the world do I do that, and relates to digging out the user signed onto the workstation for a single sign on solution: we won't go into the is that a good idea question, because it is moot, and I came out on the losing end); apparently I can't do this with Apache 2 and Windows, and so, long story short, perhaps I should consider other web server options. Thanks, Bob! david Robert Twitty [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi David Do these few queries ALWAYS fail, or just occasinally fail? Is this intranet site used by many people? If the same query always fails, you can determine if it may be a thread-safe issue by running that query outside of Apache as follows: c:\php\php thequery.php If the above fails, then it is probably not due to the fact that the mssql extension is not thread safe. If it does work then it could be the problem. I am not familiar with Apache 2.0, but I do believe it is a multi-threaded server, which is not good for extensions that are not thread-safe. If Apache 2.0 cannot be configured to run as a multi-process server, you should consider using Apache 1.3. -- bob On Fri, 19 Mar 2004, david wrote: Ahthread safe. THAT is an interesting thought. Especially because I somehow suspect that my problem is related to the database and/or table being locked just when I am trying to read it. However, I have to profess some ignorance about FastCGI. I looked at the web site for it, but I am not sure if by installing it I am now magically thread-safe? Or, once installed, do I need to make some code changes? My environment is an established intranet (Windows based; Windows 2000 server, Apache 2.0; connecting over the network to MS SQL Server, also running on Windows 2000). What is odd is that there are only a couple of queries that fail, and when they do, they ONLY fail against certain tables used for reporting from a data warehouse, and then only now and then (perhaps once a day; maybe less). The queries are dynamic in nature, and can pull data of integer, char, or varchar. All other queries (and there are a rather lot of them) for doing the mundane intranet stuff work perfectly, and have NEVER failed. It is a real puzzle. Thanks! david Frank M. Kromann [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi David, You problem might be a thread safty issue. the MSSQL extension is not thread safe (caused by the Microsoft Library used to create the extension). Use CGI or FastCGI to avoid this problem. - Frank P.S. I don't have the full thread of this discussion so I might miss some important information about your setup. Bruno: I did that. I had to wait a bit until it failed again, whcih it did a few moments ago. Alas, there is not one thing out of the ordinary in the log. Any other suggestions? THANK YOU! david Bruno Ferreira [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] david wrote: Hello there! I have just about driven myself crazy with an odd intermittent problem. [snip] I'd first start by turning on all logging I could in the SQL server so that I could see what's happening straight from the horse's mouth... Bruno Ferreira --- [This E-mail scanned for viruses by Declude Virus] -- 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] Re: Re: SQL Server Query Failed
Hi David Based on the amount of users accessing the site, there is a possibitity that php_mssql.dll not being thread safe could be the problem. The only way to verify this is to use PHP as a CGI, run Apache as a multi-process server, or replace php_mssql.dll with php_odbtp_mssql.dll. As much as I like PHP and dislike Microsoft, I personally would not have used Apache/PHP on a Win32 platform to connect to SQL Server. The proper choice should be IIS/ASP.NET. I believe that IIS supports the single sign-on feature for intranet purposes. You may have to decide which of the solutions I have mentioned is the most feasible. -- bob On Fri, 19 Mar 2004, david wrote: Thank you, Bob, for your continuing comments. The intranet site is used by approximately 100 people, and sees a good amount of traffic every day. I don't really have an exact number of daily queries that are executed, but it has to be a pretty sizable number, since the site handles much of the business issues. The query in question fails randomly (as far as I can tell: I have been watching for patterns for some time now.) If it always failed, that would really help. Sigh. Random. Naturally. Worse, the query is 100% dynamic, and built by the users (but of course they do not know they are building a query. They are just selecting things to report on, which is the way it should be, in my opinion). So, I don't have a query I can show as always fails, or sometimes fails. The only common thread in all of this is the tables in question. There are only a couple of tables, and they are used in the datawarehouse for reporting. Oddly, though (and I cannot prove this as much as I would like to) I think that some of the failures happen when no one else is using that table. All the other intranet tables perform as expected (and there are a LOT of those). I will look at the Apache 2/multi-process question. It is a great thought. I have another problem (this one is more of a how in the world do I do that, and relates to digging out the user signed onto the workstation for a single sign on solution: we won't go into the is that a good idea question, because it is moot, and I came out on the losing end); apparently I can't do this with Apache 2 and Windows, and so, long story short, perhaps I should consider other web server options. Thanks, Bob! david Robert Twitty [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi David Do these few queries ALWAYS fail, or just occasinally fail? Is this intranet site used by many people? If the same query always fails, you can determine if it may be a thread-safe issue by running that query outside of Apache as follows: c:\php\php thequery.php If the above fails, then it is probably not due to the fact that the mssql extension is not thread safe. If it does work then it could be the problem. I am not familiar with Apache 2.0, but I do believe it is a multi-threaded server, which is not good for extensions that are not thread-safe. If Apache 2.0 cannot be configured to run as a multi-process server, you should consider using Apache 1.3. -- bob On Fri, 19 Mar 2004, david wrote: Ahthread safe. THAT is an interesting thought. Especially because I somehow suspect that my problem is related to the database and/or table being locked just when I am trying to read it. However, I have to profess some ignorance about FastCGI. I looked at the web site for it, but I am not sure if by installing it I am now magically thread-safe? Or, once installed, do I need to make some code changes? My environment is an established intranet (Windows based; Windows 2000 server, Apache 2.0; connecting over the network to MS SQL Server, also running on Windows 2000). What is odd is that there are only a couple of queries that fail, and when they do, they ONLY fail against certain tables used for reporting from a data warehouse, and then only now and then (perhaps once a day; maybe less). The queries are dynamic in nature, and can pull data of integer, char, or varchar. All other queries (and there are a rather lot of them) for doing the mundane intranet stuff work perfectly, and have NEVER failed. It is a real puzzle. Thanks! david Frank M. Kromann [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi David, You problem might be a thread safty issue. the MSSQL extension is not thread safe (caused by the Microsoft Library used to create the extension). Use CGI or FastCGI to avoid this problem. - Frank P.S. I don't have the full thread of this discussion so I might miss some important information about your setup. Bruno: I did that. I had to wait a bit until it failed again, whcih it did a few moments ago. Alas, there is not one thing out of the ordinary in the log. Any other suggestions?
[PHP-DB] Re: oracle: fetch a query result into an array
To make your life much easier, I recommend using PEAR DB. The function
calls are very easy and work for any DBMS (no remembering DB specific
calls :-)). It also gives you lots of added functionality, such as
fetching of an entire result set at once, fetching only one row, or even
fetching one column quickly and without the need to worry about freeing
your resources. Used right, you don't have to worry about memory leaks
or even the DB backend you're using.
http://pear.php.net/package/DB
http://pear.php.net/manual/en/package.database.php
Torsten Lange wrote:
Hello,
I'm a php beginner and not succeed in fetching an oracle query result
into an array. I want to load the colum names of a table (from an
Oracle9i) into a select-field. There is something working, but only
the last column name appears in the select field and is wraped by each
letter.
For instense acw_fid_lab:
___
|non|
|all|
|A |
|C |
.
. and so on.
What are my mistakes? If anyone could suggest me how to solve it or
using a better approach...
Thank you, Torsten
---
My last version (I also tried OCIFetchInto before):
---
function fill_select($table_name)
{
$connect = @OCILogon(, , );
if(!$connect)
{
$err_oci = OCIError();
echo (2) No connection - OCIError():
nbsp;nbsp;nbsp;.$err_oci[message];
echo p;
}
else
{
$sql_table_names = SELECT column_name FROM user_tab_columns
WHERE table_name = '.$table_name.';
$stmt = OCIParse($connect, $sql_table_names);
OCIExecute($stmt);
$column_name = array();
$counter = 0;
while(OCIFetch($stmt))
{
$column_name = OCIResult($stmt,'COLUMN_NAME');
$counter++;
}
if($counter == 0)
{
$column_name[0] = nothing found;
}
OCIFreeStatement($stmt);
OCILogOff($connect);
return($column_name);
}
}
# This is a cut-out from that file I call the function from
echotd\n;
echoselect name=\chemistry\ size=\12\ multiple\n;
echooptionnone/option\n;
echooptionall/option\n;
$table_name = AN_CHEMISTRY_WATER;
$count = (int) count_table_columns($table_name);
$column_names = fill_select($table_name);
for($i=1; $i=$count; $i++)
{
echo option,$column_names[$i],/option\n;
}
echo/select\n;
echo/td\n;
--
--
paperCrane Justin Patrin
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Problem with INSERT Query
[EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
Hi all:
Well, when i bring out the page with the drop down list it was able to
display
all tutors' names from tutor_name column. Anyway here's a review of my
code
(snip) again before i continue:
--
-
snip
$sql = INSERT INTO class (class_code, tutor_name, edu_level,
timetable_day,
timetable_time)
VALUES
('$class_code','$tutor_name','$edu_level','$timetable_day','$timetable_time'
);
?//retrieve data from DB display in dynamic drop down ?
SELECT class=textarea name=tutor_name /
?
$sqltutor = mysql_query(SELECT DISTINCT tutor_name FROM tutor );
while ($row = mysql_fetch_array($sqltutor))
{
print OPTION VALUE=\$row [tutor_name]. \ SELECTED .$row
[tutor_name]. /option ;
}
$result = $db- query($sql);
?
/select
?
while($selected_tutor_name == $tutor_name)
echo $_POST[tutor_name];
How about
if ($tutor_name ){
echo $_POST[tutor_name];
$sql = INSERT INTO class (class_code, tutor_name, edu_level, timetable_day,
timetable_time)
VALUES
('$class_code','$tutor_name','$edu_level','$timetable_day','$timetable_time'
);}
is it working?
Hadi
?
/snip
--
-
so when i submit the form, i am suppose to echo the values i have entered
into
the field and then INSERT the values into DB (Queries stated above).
However i
was able to echo all other values eg. class_code, edu_level, etc...but
not tutor_namesame thing happen when i do an INSERT, all other
values
are inserted into DB but not $tutor_namewhy is this so???Really need
some
help here...Anyway i have already specify a name to be reference :
SELECT class=textarea name=tutor_name
and then I also did an echo of tutor_name being selected:
while($selected_tutor_name == $tutor_name)
echo $_POST[tutor_name];
All help are greatly appreciated =)
Irin.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Categories and Items query
Well, I figured out how to do my nested loop, so now I think it's a
fairly small problem that I have now: how to only select features that
should show on the Deluxe Home Features page, or the Standard Home
Features page.
I have a table named feature_sets with three values (plus keys): both,
standard only or deluxe only. I designed it this way because for some
features, such as the wood trim, there is an upgraded version (e.g. oak
vs. pine), some features are common to both homes, and a couple are
exclusive to the deluxe home.
I have my nested loop working so it lists the category then lists the
features in that category in a bulleted list, but it's currently pulling
out ALL of the features, not just the all homes and deluxe (which is
the page I am working on. When I try to add an additional AND statement
to limit the features to only those with 1 or 3 as their featureset_id,
I end up with a list that has ALL the features showing up under each of
the categories.
Is there a way that I can limit my query to just two of the three
featureset IDs?
Any help would be much appreciated!
My code:
?php require_once('[appropriate connection info here]');
$query_categories = SELECT * FROM feature_categories;
$categories = mysql_query($query_categories) or die(mysql_error());
$row_categories = mysql_fetch_assoc($categories);
$totalRows_categories = mysql_num_rows($categories);
?
?php do { ?
table width=100% class=featuretable
tr
tdb?php echo $row_categories['category'];
?
/b/td
/tr
/table
?php
$query_features = SELECT feature_description FROM features WHERE
feature_category_id = .$row_categories['feature_category_id'].;
$features = mysql_query($query_features) or die(mysql_error());
$row_features = mysql_fetch_assoc($features);
$totalRows_features = mysql_num_rows($features);
do { ?
table width=100% border=0 cellpadding=2 class=featuretable
tr
tdul
li?php echo $row_features['feature_description']; ?/li/ul/td
/tr
/table
?php } WHILE ($row_features = mysql_fetch_assoc($features));
} WHILE ($row_categories = mysql_fetch_assoc($categories));
mysql_free_result($categories);
mysql_free_result($features);
mysql_close();
?
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Categories and Items query
Having a Standard only, Deluxe only and Both will lead to problems in the
future. (i.e. what if you add in a Basic feature set?) What I would do is
1. Get rid of the Both row in feature sets.
2. Have another table called feature_set_features or something better. The
table has two columns, The Feature id and the Feature set ID. If a
feature is in one feature set, there in one row in the table. if a Feature
is in Both feature sets, then there are two rows in this table, one for
each feature set.
3. To query this, join features and feature_set_features on feature_id and
use where feature_set_id = 'standard' or whatever if is.
Hopefully I haven't confused you too much.
Lang
Ali Van Doren wrote:
Hello,
I am pretty new to PHP and MySQL, and am
struggling with this particular page I would like
to create.
I am building a page which lists categories of
house features (e.g. concrete, framing,
foundation, etc.) and then the particular features
that fall into those categories (e.g. the concrete
items are driveway, garage floor, sidewalk,
basement; the framing items include manufactured
floor joist, 1/2 OSB on roof, etc.) The tricky
part is that there are two lists of features I
need to produce: standard and deluxe. Some
features span both types, some are particular to
only one feature list.
I have created 3 tables to handle this:
- the feature_sets table has three items: both(1),
standard(2) only and deluxe(3) only
- the feature_categories table holds the 19
categories of features (concrete, framing, etc.)
- the features table holds all of the features (63
total.)
All tables have primary keys, and the features
table has 2 foreign keys corresponding to
featureset and the featurecategory.
What I would like to be able to do is to have the
category appear, and then list the corresponding
features under it, depending on whether it's the
deluxe of standard feature list. Here's my code:
?php
require_once
('/usr/home/fahomes/mysql_connect.php');
$query = SELECT category, feature_description
FROM features as f, feature_categories as fc WHERE
f.feature_category_id=fc.feature_category_id AND
f.featureset_id = '1' OR f.featureset_id = '3'
ORDER BY f.feature_category_id ASC;
$result = @mysql_query ($query);
if ($result) {
while ($row = mysql_fetch_array($result,
MYSQL_NUM)) {
echo tr
td align=\left\$row[0]/td
td
align=\left\ulli$row[1]/ul/td
/tr\n;
}
mysql_free_result ($result);
} else {
echo 'pThe features could not be displayed due
to a system error. Please contact the a
href=\mailto:[EMAIL PROTECTED]webmaster/a./pp'
. mysql_error() . '/p';
}
mysql_close();
?
What I am getting is initially it's creating a row
for each category/feature pair, so the categories
that have more than one feature listed appear in
more than one row. It works fine until record 34
(id 33) when it starts puking out that feature for
10 rows then the next feature for 1 row, then it
jumps back to the feature 33 for another 12 rows,
this time cycling through the categories. It's
quite bizarre:
http://roku.pair.com/fahomes/test/homes_dlx_features.php
So, I actually have two questions:
1) Does anyone know why my query is misbehaving so
badly? When I copy and paste the query into
MyPHPAdmin, it produces the same results, so I
know it's not the php. On thing I do notice is if
I change the query slightly (so that it looks for
records WHERE
f.feature_category_id=fc.feature_category_id AND
f.featureset_id = '1' OR '3' instead of
f.featureset_id = '1' OR f.featureset_id = '3'
it produces equally messed up, but different results.
2) Is there a way that I can display the data the
way I want to display it (list the category once,
then corresponding features underneath, before
going to the next category)? I would imagine it's
a WHILE loop that's inside another WHILE loop -- I
am just not sure how to go about it, and haven't
been able to track down anything in my books.
Thanks for any advice!
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: $_POST in MySQL query issue...
$sql=insert into $table set Name = '.$_POST[elementName].'; or even better: $sql=insert into .$table. set Name = '.$_POST[elementName].'; But the method both Jake and Bao suggested will also work (temporary var)... -- // DvDmanDT MSN: [EMAIL PROTECTED] Mail: [EMAIL PROTECTED] Adam Reiswig [EMAIL PROTECTED] skrev i meddelandet news:[EMAIL PROTECTED] Greetings to all. I am trying for the life of me to place a $_POST[] variable in my MySQL query. I am running the latest stable versions of PHP, MySQL and Apache 2 on my Win2kPro machine. My register_globals are set to off in my php.ini. My code I am attempting create is basically as follows: $table=elements; $sql=insert into $table set Name = '$elementName'; This works with register_globals set to on. But, I want to be able to turn that off. My code then, I am guessing, be something as follows: $table=elements; $sql=insert into $table set Name = '$_POST[elementName]'; Unfortunately this and every other combination I can think of, combinations of quotes that is, does not work. I believe the source of the problem is the quotes within quotes within quotes. I also tried: $sql='insert into $table set Name = '.$_POST[elementName]; or $sql=insert into $table set Name = .$_POST['elementName']; and several other variations. Can anyone give me some pointers to inserting $_POST[] statements inside of query statements? I am sure there must be a way but I have spent a lot of time on this and am really stumped here. Thanks for any help. -Adam Reiswig PS if anything here is not clear to you, please let me know and I'll clarify as I can. Thanks again. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: $_POST in MySQL query issue...
$sql=insert into $table set Name = '$_POST[elementName]';
The problem with this is that you need to use curly braces around the
variable being substituted in the string. Also use single quotes around the
array index.
$sql=insert into $table set Name = '{$_POST['elementName']}';
See the manual.. (Variable parsing section)
http://php.net/manual/en/language.types.string.php
Lang
Adam Reiswig wrote:
Greetings to all. I am trying for the life of me to place a $_POST[]
variable in my MySQL query. I am running the latest stable versions of
PHP, MySQL and Apache 2 on my Win2kPro machine. My register_globals are
set to off in my php.ini. My code I am attempting create is basically
as follows:
$table=elements;
$sql=insert into $table set Name = '$elementName';
This works with register_globals set to on. But, I want to be able to
turn that off. My code then, I am guessing, be something as follows:
$table=elements;
$sql=insert into $table set Name = '$_POST[elementName]';
Unfortunately this and every other combination I can think of,
combinations of quotes that is, does not work. I believe the source of
the problem is the quotes within quotes within quotes. I also tried:
$sql='insert into $table set Name = '.$_POST[elementName];
or
$sql=insert into $table set Name = .$_POST['elementName'];
and several other variations.
Can anyone give me some pointers to inserting $_POST[] statements inside
of query statements? I am sure there must be a way but I have spent a
lot of time on this and am really stumped here. Thanks for any help.
-Adam Reiswig
PS if anything here is not clear to you, please let me know and I'll
clarify as I can. Thanks again.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Help with UPDATE query
In article [EMAIL PROTECTED], [EMAIL PROTECTED] says... Hi, I have two columns in my Bookings table of type DATETIME - Booking_Start_Date and Boking_End_Date. How can i update every row so that all of the times for Booking_Start_Date are 09.00 and all of the times for Booking_End_Date are 17.30, without affecting any of the dates? Thanks for your help Off the top of my head - use mysql's DATE_FORMAT to build a date string, then concatenate the required time string and use that as the update value. Something like UPDATE table SET Booking_Start_date = CONCAT(DATE_FORMAT(Booking_Start_Date, your format here),'09.00'), Booking_End_date = CONCAT(DATE_FORMAT(Booking_End_Date, your format here),'17.30'); You'll need to play a bit with that - and of course test on a backup. Cheers -- Quod subigo farinam A: Because it messes up the order in which people normally read text. Q: Why is top-posting such a bad thing? A: Top-posting. Q: What is the most annoying thing on usenet? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: If in a query
I think you are not considering all the cases. Suppose your column is NULL for the first record but it has a non-NULL value on the second record. So, why do you want your query to return nothing whereas there are values in it? Col1Col2Col3 __ Row1:18NULLabcdf Row2:1628 jdgdkdkf Do you see what I mean? Column 2 is NULL for the first record but it is 28 for the second record. A query like: SELECT col1, IFNULL(col2, 0) AS col22, col3 FROM tblName will return Col1Col2Col3 __ Row1:180abcdf Row2:1628 jdgdkdkf Thanks Emery - Original Message - From: Shaun [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, September 27, 2003 15:43 Subject: [PHP-DB] Re: If in a query i'm sorry, i rushed that previous post!!! what i meant to ask was, if the value of B.Column1 is null then can the query be written such that we dont select A.Column2. Sorry! Shaun [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi, is it possible to create a query with parameters such that it selects a field if it isn't null? for example: SELECT A.Column1, A.Column2, (if not null, B.Column1) FROM Table1 A, Table2 B Thanks for your help -- 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] Re: If in a query
i'm sorry, i rushed that previous post!!! what i meant to ask was, if the value of B.Column1 is null then can the query be written such that we dont select A.Column2. Sorry! Shaun [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi, is it possible to create a query with parameters such that it selects a field if it isn't null? for example: SELECT A.Column1, A.Column2, (if not null, B.Column1) FROM Table1 A, Table2 B Thanks for your help -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Don't know why query works this way
Aaron Wolski wrote:
Here's some functions I use in my development which save on connect and
query calls for me.
aaron: i do more or less the same thing. allow me to criticize your
functions in one very important respect: you do not check the return
value of your calls! i would suggest that anyone doing mysql work
ALWAYS check the return value. it will save you MUCH head scratching
later on down the line. really.
eg:
function db_connect() {
mysql_pconnect(localhost,username,password);
mysql_select_db(Database);
}
function db_connect {
mysql_pconnect('localhost','username','password') or
die(mysql_error());
mysql_select_db('database') or die(mysql_error());
}
and so forth.
-jsd-
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Don't know why query works this way
In article [EMAIL PROTECTED],
[EMAIL PROTECTED] says...
I have been writing my queries like this...
$link = mysql_connect ($host, $username, $password);
$query = insert into $table ('joe', 15);
if ( mysql_db_query($dbname, $query, $link)) { }
But then I was reading that I should use mysl_query instead of mysql_db_query.
But I was having a problem with where do i put the $dbname.
When I used:
mysql_query ($dbname, $query, $link)
I got an error message saying i had too many arguments. So I changed it to:
mysql_query ($query, $link)
I then got an error message saying it didn't know what database I was
connected to. I finally got around the problem by including the database
name in the query, like so:
$query = insert into $dbname.$table ('joe', 15);
I shouldn't have to say, but this is a simplified example. I didn't want to
include all the fields, tables, values that the true example has. The
question is, am I doing this right? Do I have no choice but to retype the
$dbname all over the place if I want to use mysql_query?
Thank in advance for any help.
Well, no. You need to do mysql_select_db to select a database up front and
if you happen to need to change databases, use it again. So something like
$dbname = 'whatever';
$link = mysql_connect ($host, $username, $password);
mysql_select_db($dbname);
$query = insert into $table ('joe', 15);
if ( mysql_db_query($dbname, $query, $link)){ }
etc etc
--
Quod subigo farinam
$email =~ s/oz$/au/o;
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet?
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Very confused with query
This one works, just change the fields... SELECT transaction_user_id, Max(transaction_date) AS transaction_max_date, * FROM transactions group by transaction_user_id; This works... but I tried it on a number field instead of date... but it should handle the same way... Ronald Villarosa www.pabahay.com www.bullsandbears.net www.2datop.com Sparky Kopetzky [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Good morning!! I'm trying to select 1 and only one record from each user based on the last date from a transaction file. This is what I'm using and it's not even close. It seems DISTINCT does not work!! SELECT DISCTINCT(transaction_user_id), MAX(transaction_date), * FROM transaction; Could some kind soul help me figure this one out?? Robin E. Kopetzky Black Mesa Computers/Internet Services www.blackmesa-isp.net -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Help with a query
i am not testing this... just writing it here. and it is 2am ! SELECT shopping_cart.item_id, shopping_cart.subtotal, shopping_cart.quantity FROM shopping_cart, orders WHERE shopping_cart.order_id = orders.order_id AND orders.session_id = session_id() AND orders.customer_id = '$customer_id' i guess u could give it a try. just a simple 2 table join. i may not have the fields and order in place but try it Joel Colombo Jonathan Villa [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I can't figure this query out. I want to pull data from 2 tables but using a primary key from one. Here is what I think it should look like. SELECT item_id, subtotal, quantity from shopping_cart WHERE order_id = (SELECT order_id FROM orders WHERE session_id = session_id()) AND customer_id = $customer_id; -Jonathan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: pg_query creates wrong query
Sascha Alexander Jopen wrote: Hi there, I'm new to PHP, especially to the db-functions. I've got a problem querying my PostgreSQL server using pg_query. The Tables i'm working on contain colume names with uppercase letters. Working on those tables directly with psql, the client program delivered with postgresql, works like expected. Its case-sensitive, so you have to specify the table names with the correct case of the letters. But pg_quer seems to convert all queries into lowercase. This is normal. PostgreSQL does not distingish case and stores most names as lowercase and convert names to lowerc case. You can keep case also. Read PostgreSQL manual for detials. A query like: INSERT INTO users (UsrLoginName, UsrPassword) VALUES ... results in an errormessage like: PHP Warning: pg_query() query failed: ERROR: Relation 'users' does not have attribute 'usrloginname' ... What did i do wrong? Or is it a problem with the postgresql db-package of PHP? Because there is no 'usrloginname'? I think, if the backend is case-sensitive, PHP's functions should be case-sensitiv, too! I like case sensitive language, but PHP is not. You have to live with it. Please don't tell me to convert all tables/colums to lowercase! Read PostgreSQL manual, then you should be able to find info needed. test=# create table ttt (ThisIsNumber int); CREATE test=# \d ttt Table ttt Column| Type | Modifiers --+-+--- ThisIsNumber | integer | test=# create table (ThisIsNumber int); CREATE test=# \d Table Column| Type | Modifiers --+-+--- thisisnumber | integer | test=# -- Yasuo Ohgaki -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: pg_query creates wrong query
Thank you. I forgot to use the 's to force mixed-case. Know it works fine. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: JOIN in embedded query
In article [EMAIL PROTECTED], [EMAIL PROTECTED] says... David Robley [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]... In article [EMAIL PROTECTED], [EMAIL PROTECTED] says... UPDATE Emails SET Sent = 'T' WHERE ID = (SELECT Emails.ID FROM Emails RIGHT JOIN KeyWords ON Emails.ID = KeyWords.EmailsID) LIMIT 250 I am getting an error, and I am quite certain that it is my syntax Can you post the error? You have an error in your SQL syntax near 'SELECT Emails.ID FROM Emails RIGHT JOIN KeyWords ON Emails.ID = KeyWords.EmailsI' at line 1 Indicates that the error is immediately to the left of the text given in the error string. Which triggers my memory - from the mysql docs - this is for ver 4, check the docs for a way of imitating sub-selects if your db doesn't support them: SELECT * FROM table1 WHERE id IN (SELECT id FROM table2); You have = instead of IN Cheers -- David Robley Temporary Kiwi! Quod subigo farinam -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Benefits of assigning query to variable
sure there are, you can call the query in multiple places, for example:
$sql = mysql_query(INSERT INTO $table_name WHERE db_field =
$search_string);
Then say you have a routine to check for missing for elements and you would
like to log failed vs. successfull attempts your code flow would be
something like:
if (!$search_string !$var02) {
$sql;
} else {
$sql; }
HTH
Jas
Brian Graham [EMAIL PROTECTED] wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
Are there any benefits to assigning a query to a variable?
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Refresh after DB Query
Sorry if you are a newbie.. but if might help if you delete the records before you select them to display in html ie: put the delete query at the top of the page, just after this line: require (templates/db_connect.php); adam
[PHP-DB] RE: Refresh after DB Query - Solved
Ugh! Thanks, Adam. I knew it would be something stupid. Although I am kind of new (less than 6 months) to PHP, I'm really not that bad with it (IMHO). I really thought I had tried putting the DELETE stuff where you said to put it and it didn't work. However, new day, fresh eyes, your advice...it works now. Thanks again. Rich -Original Message- From: Adam Royle [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 03, 2002 2:22 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Refresh after DB Query Sorry if you are a newbie.. but if might help if you delete the records before you select them to display in html ie: put the delete query at the top of the page, just after this line: require (templates/db_connect.php); adam
[PHP-DB] Re: [PHP] Using A Query Results Multiple Times
On Tuesday 23 October 2001 20:47, Adam Douglas wrote:
mysql_fetch_array function. At first I thought I could loop through my
while loop and have it put the results from the query of each row in to
a multidimensional array. But then how would I add to the array after
the initial row?
while ($row = mysql_fetch_array (...))
{
$MyArray[] = $row;
}
Look up arrays in the manual
--
Christian Reiniger
LGDC Webmaster (http://lgdc.sunsite.dk/)
Very funny, Scotty! Now beam up my clothes...
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Re: username/password db query
you may want to try this
if ((strcmp($username,$myrow[0])==0) (strcmp($password,$myrow[2])==0))
instead of
if ($username==$myrow[0] $password==$myrow[2])
You may also want to change your select query:
$query = SELECT Password FROM userinfo WHERE Username='$username';
then change your comparision to:
if (strcmp($password,$myrow[2])==0)
Ross Dmochowski wrote:
Hi. i'm new to PHP, and i can't seem to find out what i'm doing wrong
with the following code:
client posts username/password via SSL to this file, login.php,
where i want to check the username/password combo against what is listed
in the db
if the entries are blank, it goes to a page that sends email and syslog
alerts about a failed login attempt.
if the entry is bad, it also goes to this badlogin.php
while if it matches, they get cookies set and go to the goodlogin.php
?php
$username = $HTTP_POST_VARS['username'];
$password = $HTTP_POST_VARS['password'];
if ($username == or $password == ) {
header (Location: http://www.some.com/secure/badlogin.php;);
} else {
$db = pg_connect(dbname=some_com user=some_com);
$query = SELECT * FROM userinfo;
$result = pg_exec($db, $query);
$numrows = pg_numrows($result);
};
do {
$myrow = pg_fetch_row ($result,$row);
if ($username==$myrow[0] $password==$myrow[2]) {
mt_srand((double)microtime()*100);
$random_cookiename = mt_rand();
$random_cookievalue = mt_rand();
setcookie ($random_cookiename, $random_cookievalue, time()+900);
setcookie (ClientAddress, $REMOTE_ADDR, time()+900);
pg_close($db);
header (Location: https://www.some.com/secure/goodlogin.php;);
}
$row++;
} while($row $numrows);
pg_close($db);
header (Location: http://www.some.com/secure/badlogin.php;);
?
the specified user has db rights.
if i put
echo $myrow[0];
in the loop (and remove the redirect to the badlogin.php file , it will
print out all the users in the db (the first column)
but my comparison operation is not successfully telling when the entered
data properly matches the db entry (is it a datatype problem? the username
is kept in the postgresql db as type char)
any constructive help would be very appreciated.
_
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
