[PHP-DB] help width sql
hi
I have a problem whith inforcing the result of a selekect query, here is
my tables and the query:
I create this 4 tables:
CREATE TABLE a (
a_id SERIAL PRIMARY KEY,
a_name text );
CREATE TABLE b (
b_id SERIAL PRIMARY KEY,
b_price INT2 );
CREATE TABLE c (
c_id SERIAL PRIMARY KEY,
a_id INT4 REFERENCES a ON UPDATE CASCADE ON DELETE CASCADE,
b_id INT4 REFERENCES b ON UPDATE CASCADE ON DELETE CASCADE,
c_price INT2 );
CREATE TABLE d (
d_id SERIAL PRIMARY KEY,
a_id INT4 REFERENCES a ON UPDATE CASCADE ON DELETE CASCADE,
b_id INT4 REFERENCES b ON UPDATE CASCADE ON DELETE CASCADE,
d_price INT2 );
Insert some values:
INSERT INTO a (a_name) VALUES ('org1');
INSERT INTO a (a_name) VALUES ('org2');
INSERT INTO a (a_name) VALUES ('org3');
INSERT INTO b (b_price) VALUES (100);
INSERT INTO b (b_price) VALUES (200);
INSERT INTO b (b_price) VALUES (50);
INSERT INTO c (a_id, b_id,c_price) VALUES (1,1,50);
INSERT INTO c (a_id, b_id,c_price) VALUES (2,1,50);
INSERT INTO c (a_id, b_id,c_price) VALUES (1,2,100);
INSERT INTO c (a_id, b_id,c_price) VALUES (2,2,100);
INSERT INTO c (a_id, b_id,c_price) VALUES (1,3,25);
INSERT INTO c (a_id, b_id,c_price) VALUES (3,3,25);
INSERT INTO d (a_id, b_id,d_price) VALUES (1,1,50);
INSERT INTO d (a_id, b_id,d_price) VALUES (2,1,50);
INSERT INTO d (a_id, b_id,d_price) VALUES (1,2,100);
INSERT INTO d (a_id, b_id,d_price) VALUES (2,2,100);
a_id | a_name
--+
1 | org1
2 | org2
3 | org3
b_id | b_price
--+-
1 | 100
2 | 200
3 | 50
c_id | a_id | b_id | c_price
--+--+--+-
1 |1 |1 | 50
2 |2 |1 | 50
3 |1 |2 | 100
4 |2 |2 | 100
5 |1 |3 | 25
6 |3 |3 | 25
d_id | a_id | b_id | d_price
--+--+--+-
1 |1 |1 | 50
2 |2 |1 | 50
3 |1 |2 | 100
4 |2 |2 | 100
SELECT SUM(c_price) as sum,(SELECT SUM(d_price) FROM d WHERE
a_id=t1.a_id ) AS payed FROM c AS t1 group by a_id order by payed;
the result of this query is:
sum | payed
-+---
150 | 150
175 | 150
25 |
(3 rows)
The question is : how can I force that the result of the col payed to be
zerro 0 insted of notthig (NULL)
and the order will be in way that the zerro's values comes first.
and the result will be:
sum | payed
-+---
25 | 0
150 | 150
175 | 150
Thanks
Suad
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] help in sql - postgresql
Hi,
I need some help in sql - postgresql:
I create this 4 tables:
CREATE TABLE a (
a_id SERIAL PRIMARY KEY,
a_name text );
CREATE TABLE b (
b_id SERIAL PRIMARY KEY,
b_price INT2 );
CREATE TABLE c (
c_id SERIAL PRIMARY KEY,
a_id INT4 REFERENCES a ON UPDATE CASCADE ON DELETE CASCADE,
b_id INT4 REFERENCES b ON UPDATE CASCADE ON DELETE CASCADE,
c_price INT2 );
CREATE TABLE d (
d_id SERIAL PRIMARY KEY,
a_id INT4 REFERENCES a ON UPDATE CASCADE ON DELETE CASCADE,
b_id INT4 REFERENCES b ON UPDATE CASCADE ON DELETE CASCADE,
d_price INT2 );
Insert some values:
INSERT INTO a (a_name) VALUES ('org1');
INSERT INTO a (a_name) VALUES ('org2');
INSERT INTO a (a_name) VALUES ('org3');
INSERT INTO b (b_price) VALUES (100);
INSERT INTO b (b_price) VALUES (200);
INSERT INTO b (b_price) VALUES (50);
INSERT INTO c (a_id, b_id,c_price) VALUES (1,1,50);
INSERT INTO c (a_id, b_id,c_price) VALUES (2,1,50);
INSERT INTO c (a_id, b_id,c_price) VALUES (1,2,100);
INSERT INTO c (a_id, b_id,c_price) VALUES (2,2,100);
INSERT INTO c (a_id, b_id,c_price) VALUES (1,3,25);
INSERT INTO c (a_id, b_id,c_price) VALUES (3,3,25);
INSERT INTO d (a_id, b_id,d_price) VALUES (1,1,50);
INSERT INTO d (a_id, b_id,d_price) VALUES (2,1,50);
INSERT INTO d (a_id, b_id,d_price) VALUES (1,2,100);
INSERT INTO d (a_id, b_id,d_price) VALUES (2,2,100);
a_id | a_name
--+
1 | org1
2 | org2
3 | org3
b_id | b_price
--+-
1 | 100
2 | 200
3 | 50
c_id | a_id | b_id | c_price
--+--+--+-
1 |1 |1 | 50
2 |2 |1 | 50
3 |1 |2 | 100
4 |2 |2 | 100
5 |1 |3 | 25
6 |3 |3 | 25
d_id | a_id | b_id | d_price
--+--+--+-
1 |1 |1 | 50
2 |2 |1 | 50
3 |1 |2 | 100
4 |2 |2 | 100
SELECT SUM(c_price) as sum,(SELECT SUM(d_price) FROM d WHERE
a_id=t1.a_id ) AS payed, SUM(c_price)-(SELECT SUM(d_price) FROM d WHERE
a_id=t1.a_id ) AS to_pay FROM c AS t1 group by a_id order by payed;
the result of this query is:
sum | payed | to_pay
-+---+
150 | 150 | 0
175 | 150 | 25
25 | |
*The question is* : how can I force that the result of the col payed to
be zerro 0 insted of nothing (NULL)
and the order will be in way that the zerro's values comes first.
and the result will be:
sum | payed | to_pay
-+---+
25 | 0 | 0
150 | 150 | 0
175 | 150 | 25
Thanks
Suad
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] help in sql - postgresql
suad wrote: Hi, I need some help in sql - postgresql: snip Yay a postgres question! :D hee hee *The question is* : how can I force that the result of the col payed to be zerro 0 insted of nothing (NULL) and the order will be in way that the zerro's values comes first. and the result will be: sum | payed | to_pay -+---+ 25 | 0 | 0 150 | 150 | 0 175 | 150 | 25 COALESCE will do it for you: SELECT SUM(c_price) as sum,(SELECT COALESCE(SUM(d_price), 0) FROM d WHERE a_id=t1.a_id ) AS payed, SUM(c_price)-(SELECT COALESCE(SUM(d_price), 0) FROM d WHERE a_id=t1.a_id ) AS to_pay FROM c AS t1 group by a_id order by payed; sum | payed | to_pay -+---+ 25 | 0 | 25 150 | 150 | 0 175 | 150 | 25 (3 rows) http://www.postgresql.org/docs/8.1/interactive/functions-conditional.html -- 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] help in sql - postgresql
Hi, Thanks a lot, That exactly wat I need :) Suad Chris wrote: suad wrote: Hi, I need some help in sql - postgresql: snip Yay a postgres question! :D hee hee *The question is* : how can I force that the result of the col payed to be zerro 0 insted of nothing (NULL) and the order will be in way that the zerro's values comes first. and the result will be: sum | payed | to_pay -+---+ 25 | 0 | 0 150 | 150 | 0 175 | 150 | 25 COALESCE will do it for you: SELECT SUM(c_price) as sum,(SELECT COALESCE(SUM(d_price), 0) FROM d WHERE a_id=t1.a_id ) AS payed, SUM(c_price)-(SELECT COALESCE(SUM(d_price), 0) FROM d WHERE a_id=t1.a_id ) AS to_pay FROM c AS t1 group by a_id order by payed; sum | payed | to_pay -+---+ 25 | 0 | 25 150 | 150 | 0 175 | 150 | 25 (3 rows) http://www.postgresql.org/docs/8.1/interactive/functions-conditional.html -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] multi-table insert
Thanks Chris!
Appreciate the help!
Eustace
-Original Message-
From: Chris [mailto:[EMAIL PROTECTED]
Sent: Monday, May 01, 2006 3:23 AM
To: [EMAIL PROTECTED]
Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] multi-table insert
Eustace wrote:
Hello everybody!
I am very much a newbie in PHP, but enjoying the learning process.
Here and there I get tangled in the logic of certain problems. Anyway,
I have a database about interns and this database has multi-tables
told data of interns, for example personal information, education
qualifications, computer skills, languages etc. The relationship of
the tables is based on the intern's username.
What I am trying to do is have a form, which an intern can fill in so
that details are inserted into the database. Since I am populating a
number of tables in one go, what's the best way to implement this? The
main table is the personal information one, which has the username as
primary key, and the rest of the tables username is the foreign key.
Obviously I need the to pick the username from the main table and
insert it into the other tables. How best can I do this?
No databases will do this automatically for you (whether you use mysql,
sqlite, postgresql or something else), so you need to create multiple
queries:
mysql example:
$query = insert into users(username) values ('my_username');
mysql_query($query); $userid = mysql_insert_id();
$query = insert into table2(userid) values('$userid');
postgres example:
$query = select nextval('user_sequence') AS nextid; $result =
pg_query($query); $row = pg_fetch_assoc($result); $userid = $row['nextid'];
$query = insert into table2(userid) values('$userid');
--
Postgresql php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit:
http://www.php.net/unsub.php
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.5.1/328 - Release Date: 5/1/2006
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
