[PHP-DB] Nested SQL query problem...
Hi, I have a table that contains the prices of software products at different quantity slab rates. I'm trying to get the Slab rate for a given number of licenses for a given product_id. For example, what is the cost of 500 licenses of Product_ID 143 ? The slabs do not continue for an infinite number, hence I may only have slab pricing for 1-10, 11-100, and 101-200 with the assumption that if there are no higher slabs and I wanted say the rate for 500 licenses, I would use the highest slab unit available, in this case the 101-200 slab. My table structure is like so: Slab_Master ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | TInt_SlabID| int(11) | | PRI | NULL| auto_increment | | TInt_ProductID | int(11) | YES | | NULL|| | TInt_StartSlab | int(11) | YES | | NULL|| | TInt_EndSlab | int(11) | YES | | NULL|| | TDbl_SlabRate | float(10,2) | YES | | NULL|| ++-+--+-+-++ I achieve the result I want with the use of 2 queries: Query 1: SELECT MAX(TInt_StartSlab) FROM Slab_Master WHERE TInt_ProductID = 143 AND TInt_StartSlab < 500; Which gives: +-+ | MAX(TInt_StartSlab) | +-+ | 101 | +-+ This I store in a variable, say $MAX, and execute Query 2; Query 2: SELECT TDbl_SlabRate FROM Slab_Master WHERE TInt_ProductID = 143 AND TInt_StartSlab = 101; /*$MAX = 101*/ Which gives: +---+ | TDbl_SlabRate | +---+ | 80.00 | +---+ I would ideally like to combine this into a single nested SQL query, however all my attempts at that have failed, and I remember reading somewhere that MySQL does not support nested SQL queries. Is a single query possible? Cheers, -Srini -- http://symonds.net/~sriniram -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Can you SUM(IF) from two different tables at the same time?
"Richard Davey" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > > In the 5th line I changed "thread.id" to "thread.threadid" so the name was > correct but the same error occurs. Ok, I was guessing at your field names... > to give a perfectly satisfactory result but have no idea if this is > perversing SQL again or not! (grin) When retrieving information from a database, you should expect to get a variable number of records where each record has the same structure. Instead, you were hard-coding a query to get a single record with information about a number of different groups. If you wanted to change the number of groups returned, you would have to change the structure of the query. By getting a record for each group, the structure of the query does not change if you decide to look at a different number of groups - just the data you give to the WHERE clause. > (it at least makes more sense now I guess). > As soon as I add the "where IN" clause it errors. umm... try putting the WHERE clause before the GROUP BY? > Can anyone recommend ANY good books on SQL > query design? I don't want (or care) about database > administration, I just want to know what constitutes a > good database design and lots and lots of query > examples/tutorials. try a Google search for 'database normalization tutorial'; that should get you started. also check out the documentation at www.mysql.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Can you SUM(IF) from two different tables at the same time?
"Hugh Bothwell" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > Instead, you want a query which returns a record > with "thread count" and "message count" for each > group. > > SELECT > COUNT(DISTINCT threadid) AS threads, > COUNT(*) AS messages > FROM > thread LEFT JOIN message ON message.threadid=thread.id > GROUP BY boardid ASC > WHERE boardid IN ( 1, 2 ) Thanks for the query Hugh, but it gives me: "You have an error in your SQL syntax near 'WHERE boardid IN(1,2)' at line 7" In the 5th line I changed "thread.id" to "thread.threadid" so the name was correct but the same error occurs. After much testing I managed to get the following: SELECT COUNT(DISTINCT thread.threadid) AS threads, COUNT(*) AS messages, thread.boardid FROM thread LEFT JOIN message ON message.threadid=thread.threadid GROUP BY thread.boardid ASC to give a perfectly satisfactory result but have no idea if this is perversing SQL again or not! (it at least makes more sense now I guess). As soon as I add the "where IN" clause it errors. Can anyone recommend ANY good books on SQL query design? I don't want (or care) about database administration, I just want to know what constitutes a good database design and lots and lots of query examples/tutorials. Cheers, Richard -- Fatal Design http://www.fatal-design.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] REGEX help.
All I need to do is return anything matching the following pattern from a specific column in a table. This is how I'd write it in PHP if I were pulling the data from string. The string in this example is a direct copy of one of the column fields.. What would be the SQL equivilant? I hate to seek a hand out here.. but I'm struggling with SQL REGEX and there just aren't as many good examples out there as there are for PHP. The MySQL manual wasn't much help that's for sure. :-\ Thanks, Kevin -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Not associated with a trusted SQL Server error
Server : sql 2000 sp2 in SQL authentication mode
If I run c:\php\php.exe test1.php it produces the correct html output
displaying the rows. Which means SQL is functioning with the username/pw
configuration in the php code.
But if called THRU Internet Service manager, the exact same test1.php file,
I get this:
PHP Warning: MS SQL message: Login failed for user 'sa'. Reason: Not
associated with a trusted SQL Server connection. (severity 14) in
c:\websites1\killer5.nextmill.net\test1.php on line 36 PHP Warning: MS SQL:
Unable to connect to server: killer6.nextmill.net,1433 in
c:\websites1\killer5.nextmill.net\test1.php on line 36 PHP Warning: Supplied
argument is not a valid MS SQL-Link resource in
c:\websites1\killer5.nextmill.net\test1.php on line 37 PHP Warning: Supplied
argument is not a valid MS SQL-Link resource in
c:\websites1\killer5.nextmill.net\test1.php on line 38 PHP Warning: Supplied
argument is not a valid MS SQL-result resource in
c:\websites1\killer5.nextmill.net\test1.php on line 39 PHP Warning: Supplied
argument is not a valid MS SQL-result resource in
c:\websites1\killer5.nextmill.net\test1.php on line 60
The code is below:
$db = mssql_connect("killer6.nextmill.net,1433", "sa", "***"); (removed
for security)
mssql_select_db("EDSLdbase",$db);
$result = mssql_query("SELECT * FROM test",$db);
$total = mssql_num_rows($result);
?>Total number of entries in database: ", $myrow[0], $myrow[1], $myrow[2],
$myrow[3], $myrow[4], $myrow[5]);
What is weird is it works in a CMD prompt, but if called thru IIS 5, it
displays the error running PHP 4.1.2, if I use PHP 4.2.1, I get no error but
no display of the content either.
This WAS working on 4.0.4 before the server was upgraded to 4.2.1 and then
tested on another server using 4.1.2
Any ideas? BTW, this sql server is configured to only allow TCPIP
connections, no named pipes, but that hasn't been an issue with the 4.0.4
config in the past.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re[2]: [PHP-DB] creating errors
EG> try just:
EG> $sql = "INSERT INTO $table_name (f_name,l_name,email_addy,un,pw) VALUES
EG> ('$f_name','$l_name','$email_addy','$un', 'PASSWORD('$pw')'";
Make that ... password('$pw'), without the single quotes around the
password() function. Otherwise, it will print it literally.
- Julie
--> Julie Meloni
--> [EMAIL PROTECTED]
--> www.thickbook.com
Find "Sams Teach Yourself MySQL in 24 Hours" at
http://www.amazon.com/exec/obidos/ASIN/0672323494/thickbookcom-20
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] creating errors
try just:
$sql = "INSERT INTO $table_name (f_name,l_name,email_addy,un,pw) VALUES
('$f_name','$l_name','$email_addy','$un', 'PASSWORD('$pw')'";
ed
At 09:55 AM 5/28/2002 -0600, Jas wrote:
>I am trying to add an insert into a table and set one of the fields to a
>password hash, however I am recieving errors:
> $sql = "INSERT INTO $table_name (f_name,l_name,email_addy,un,pw) VALUES
>('$f_name','$l_name','$email_addy','$un', 'pw=PASSWORD('$pw')'";
>Is there any reason this would not work?
>Jas
>
>
>
>--
>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] creating errors
pw= is not valid SQL syntax. :-)
<>< Ryan
-Original Message-
From: Jas [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, May 28, 2002 9:55 AM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] creating errors
I am trying to add an insert into a table and set one of the fields to a
password hash, however I am recieving errors:
$sql = "INSERT INTO $table_name (f_name,l_name,email_addy,un,pw) VALUES
('$f_name','$l_name','$email_addy','$un', 'pw=PASSWORD('$pw')'";
Is there any reason this would not work?
Jas
--
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] creating errors
I am trying to add an insert into a table and set one of the fields to a
password hash, however I am recieving errors:
$sql = "INSERT INTO $table_name (f_name,l_name,email_addy,un,pw) VALUES
('$f_name','$l_name','$email_addy','$un', 'pw=PASSWORD('$pw')'";
Is there any reason this would not work?
Jas
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Simulating a FORM POST thing (HELP!)
Hello, On 05/26/2002 04:05 PM, Georgie Casey wrote: > Rite, when you see a web form, you know you can simulate the submit by > filling in the values in the address field, just like a GET method, and it > usually works the exact same. But how do you do it when one of the fields in > the form is a file upload?!?!?! > > For the record, its a GIF file you've to upload. Is there any way to do this > in PHP?? You may want to try this class that does precisely that. http://www.phpclasses.org/httpclient -- Regards, Manuel Lemos -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] INSERT problem - please help!
Thanks everyone - it turns out that variables can not begin with a number, as someone else on this list learned today as well. Doh! :) Jen Swofford [EMAIL PROTECTED] > On Tuesday 28 May 2002 06:00, Jen Swofford wrote: > > I have a problem. I am working on two separate sites on two separate > > servers (one is Linux and one is Unix) and I am doing virtually the same > > thing with each, yet only one of them works. What can I be doing wrong? > > Check your php.ini files. The one which isn't working would probably have > "register_globals" set to Off. > > -- > Jason Wong -> Gremlins Associates -> www.gremlins.com.hk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Can you SUM(IF) from two different tables at the same time?
"Richard Davey" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > 1. SELECT SUM(IF(boardid='2',1,0)) AS b2, SUM(IF(boardid='1',1,0)) AS b1, > COUNT(*) AS total FROM thread > > 2. SELECT SUM(IF(boardid='2',1,0)) AS b2, SUM(IF(boardid='1',1,0)) AS b1, > COUNT(*) AS total FROM message > > This literally brings back a total number of threads and messages posted to > the two respective boards (boardid1 and 2). > All I am wondering is if this can be combined into a single query or does > the very nature of using the SUM(IF) not allow this to happen? Your current queries are returning "thread count on all groups" as a single record and "message count on all groups" as a single record. This is a horrible perversion of SQL. Instead, you want a query which returns a record with "thread count" and "message count" for each group. SELECT COUNT(DISTINCT threadid) AS threads, COUNT(*) AS messages FROM thread LEFT JOIN message ON message.threadid=thread.id GROUP BY boardid ASC WHERE boardid IN ( 1, 2 ) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] FW: hello ... best schema to a forum
im trying to figure out the best mySQL schema to create a forum on my website ... to result in a tree-view style presentation what i have right now is a 3 table schema : categories > sub-categories > lines ... wich would result in a code parsing to emulate the tree the other way is a nodes->leaf schema that whould envolve recursive programming ... the result i wouuld want is something like, ex. º main º programming º php whats your opinion ? my regards, josé moreira portugal -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Error? new set of eyes...
On Tuesday 28 May 2002 21:15, Jas wrote:
> Hello all,
> I have an error and I will be darned if I know why. Here is the code:
> require '/path/to/database/connection/class/db.php';
> $table = "portfolio";
> $portfolio = @mysql_query("SELECT * FROM $table",$dbh);
> while ($sections = mysql_fetch_array($portfolio)) {
> list($id, $2d, $3d, $web, $prog, $tut, $proj) = $sections; // I think the
> error is on this line.
> }
> ?>
> Then I just echo the contents of the database array like so.
> // this is line 69
> And this is the error I am recieving:
> Parse error: parse error, expecting `T_VARIABLE' or `'$'' in
> /localhost/portfolio.php on line 169
> Any help would be great!!
Variable names cannot start with a digit.
--
Jason Wong -> Gremlins Associates -> www.gremlins.com.hk
Open Source Software Systems Integrators
* Web Design & Hosting * Internet & Intranet Applications Development *
/*
Peace cannot be kept by force; it can only be achieved by understanding.
-- Albert Einstein
*/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Error? new set of eyes...
Why don't you try this instead:
while(list($id, $2d, $3d, $web, $prog, $tut,
$proj)=mysql_fetch_array($portfolio))
{
do whatever..
}
the list construct only works with numerical arrays and it might not like
the $section array (doesn't it get stored as number indicies AND column
names? That might generate that error).
But if you need that section array later, we can try a different approach..
ed
At 07:15 AM 5/28/2002 -0600, Jas wrote:
>Hello all,
> I have an error and I will be darned if I know why. Here is the code:
>require '/path/to/database/connection/class/db.php';
>$table = "portfolio";
>$portfolio = @mysql_query("SELECT * FROM $table",$dbh);
>while ($sections = mysql_fetch_array($portfolio)) {
> list($id, $2d, $3d, $web, $prog, $tut, $proj) = $sections; // I think the
>error is on this line.
>}
>?>
>Then I just echo the contents of the database array like so.
> // this is line 69
>And this is the error I am recieving:
>Parse error: parse error, expecting `T_VARIABLE' or `'$'' in
>/localhost/portfolio.php on line 169
>Any help would be great!!
>Jas
>
>
>
>--
>PHP Database Mailing List (http://www.php.net/)
>To unsubscribe, visit: http://www.php.net/unsub.php
Edmund Gorski
Programmer / Analyst
WWW Coordinator Dept. @ District Office
727-341-3181
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Error? new set of eyes...
Hello all, I have an error and I will be darned if I know why. Here is the code: Then I just echo the contents of the database array like so. // this is line 69 And this is the error I am recieving: Parse error: parse error, expecting `T_VARIABLE' or `'$'' in /localhost/portfolio.php on line 169 Any help would be great!! Jas -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Compiling Oracle support, error messages
Hi, With Oracle, 'til 7.3.4, server accept connection from any client. On the other hand newer release (sure for 8i) cant accept connection for 7.3.4 client but not by default. As you use a 9i client on a 7.3.4 server, you shouldn't encounter pbs. At 28/05/2002 12:39, Herbert Groot Jebbink wrote: >Herbert Groot Jebbink wrote: > >>I think I need --with-oracle and not --with-oci8 because the Oracle >>server is 7.3.4, if I replace --with-oracle with --with-oci8 then >>I get the error message: >> In file included from internal_functions.c:34: >> /www/src/php-4.2.1/ext/oci8/php_oci8.h:52: oci.h: No such file or >> directory > >After installing the Administration version of Oracle Client and not >the runtime version I can get OCI8 to compile. > >If I change the PHP application to use the OCI8 commands instead of >the Oracle commands, can I still connect with it to a Oracle Server >running under 7.3.4? > >Greetings, Herbert -- Hubert ADGIE [EMAIL PROTECTED] Tel : +33 5 53 88 00 76 Fax : +33 5 53 88 01 16 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Compiling Oracle support, error messages
Herbert Groot Jebbink wrote: > I think I need --with-oracle and not --with-oci8 because the Oracle > server is 7.3.4, if I replace --with-oracle with --with-oci8 then > I get the error message: > In file included from internal_functions.c:34: > /www/src/php-4.2.1/ext/oci8/php_oci8.h:52: oci.h: No such file or > directory After installing the Administration version of Oracle Client and not the runtime version I can get OCI8 to compile. If I change the PHP application to use the OCI8 commands instead of the Oracle commands, can I still connect with it to a Oracle Server running under 7.3.4? Greetings, Herbert -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Compiling Oracle support, error messages
Hi, I'm porting a PHP application from Windows to Linux, the PHP script uses a Oracle server on a HP unix system and PHP.ini is configured to use the module php_oracle.dll (not phpoci8.dll) I can't get it to compile on Linux, the configure command works fine, the make command gives the next errors. In file included from internal_functions.c:34: /www/src/php-4.2.1/ext/oracle/php_oracle.h:23: ocidfn.h: No such file or directory /www/src/php-4.2.1/ext/oracle/php_oracle.h:24: ociapr.h: No such file or directory The files ocidfn.h and ociapr.h are indeed not on my system, I have installed Oracle Client 9.0.1 runtime (at installation there was a note that you need that installation if you want to develop Oracle applications). I think I need --with-oracle and not --with-oci8 because the Oracle server is 7.3.4, if I replace --with-oracle with --with-oci8 then I get the error message: In file included from internal_functions.c:34: /www/src/php-4.2.1/ext/oci8/php_oci8.h:52: oci.h: No such file or directory If someone has a Linux PHP module for Oracle (orcale.so not oci8.so) that would be great to :-) The complete build script that I use is: #/bin/sh cd /www/src rm -R php-4.2.1 rm -R apache_1.3.24 tar zxf ../download/apache_1.3.24.tar.gz && tar zxf ../download/php-4.2.1.tar.gz && cd /www/src/apache_1.3.24 && ./configure --prefix=/www && LIBS=-lpthread ./config.status && make && make install && cd /www/src/php-4.2.1 && ./configure --prefix=/www --with-oracle --with-apache=../apache_1.3.24 && make && make install && cd /www/src/apache_1.3.24 && ./configure --prefix=/www --activate-module=src/modules/php4/libphp4.a LIBS=-lpthread ./config.status make && make install The complete output of the make command: Making all in Zend make[1]: Entering directory `/www/src/php-4.2.1/Zend' make[1]: Nothing to be done for `all'. make[1]: Leaving directory `/www/src/php-4.2.1/Zend' Making all in main make[1]: Entering directory `/www/src/php-4.2.1/main' make[2]: Entering directory `/www/src/php-4.2.1/main' gcc -I. -I/www/src/php-4.2.1/main -I/www/src/php-4.2.1/main -I/www/src/php-4.2.1 -I/www/src/apache_1.3.24/src/include -I/www/src/apache_1.3.24/src/os/unix -I/www/src/php-4.2.1/Zend -I/www/src/php-4.2.1/ext/mysql/libmysql -I/opt/oracle/rdbms/demo -I/opt/oracle/plsql/public -I/www/src/php-4.2.1/ext/xml/expat -I/www/src/php-4.2.1/TSRM -g -O2 -c internal_functions.c && touch internal_functions.lo In file included from internal_functions.c:34: /www/src/php-4.2.1/ext/oracle/php_oracle.h:23: ocidfn.h: No such file or directory /www/src/php-4.2.1/ext/oracle/php_oracle.h:24: ociapr.h: No such file or directory make[2]: *** [internal_functions.lo] Error 1 make[2]: Leaving directory `/www/src/php-4.2.1/main' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/www/src/php-4.2.1/main' make: *** [all-recursive] Error 1 Greetings, Herbert -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
