[PHP-DB] sorting tables
Hi everybody I am doing a select in my oracle DB where i get some records. These records are grouped by product id, cost, etc, and amount sold every month. Then, i show all regs in a table, but as the ORDER BY is done also with the month / year, because i want to show how much every month is sold, it creates a new line even also the product is the same. Then i have my table with x times the same product, in every month column zero values except the amount for that month. Is there a way to show one product per row with every amount on every month column? Thx in advance -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] sorting tables
On Friday 03 May 2002 16:02, nauti wrote: > Hi everybody > > I am doing a select in my oracle DB where i get some records. These records > are grouped by product id, cost, etc, and amount sold every month. > > Then, i show all regs in a table, but as the ORDER BY is done also with the > month / year, because i want to show how much every month is sold, it > creates a new line even also the product is the same. Then i have my table > with x times the same product, in every month column zero values except the > amount for that month. > > Is there a way to show one product per row with every amount on every month > column? Try incorporating a DISTINCT somewhere in your SELECT. -- Jason Wong -> Gremlins Associates -> www.gremlins.com.hk Open Source Software Systems Integrators * Web Design & Hosting * Internet & Intranet Applications Development * -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] sql querry problem
Hi there, I do have 2 tables. One contains an update for the other one. Before inserting I would like to delete all the entries in the old table containing the same country_code like one in the new table. I tryed: delete o.* from Cities_update_imported n, test.cities o where n.country_code = o.country_code But does not work though:-( can anybody help on that? thanx, Andy -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Inserting images in postgres
Hi,
I made a table test in postgresql as follows
CREATE TABLE test (
number INT;
image OID
);
but the moment i try to insert an image as a normal user it say it
cant do so as a normaal user only superuser (/postgres) can do it.
the command for insert is as follows
INSERT INTO test VALUES ( 1,lo_import('/path/to/file'));
If I want to implement the changes whereby my normal user should
be able to insert into the dbase how do i do it ?
Do i have to edit any file.I saw pg_hba.conf and other file but
found no attributes near it and so also the SET and SHOW declaretions
Can anyone point out the error
Thanks
Sharmad
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL Query
Hi. I am having trouble after migrating an Access Database to MySQL. Basically the Access query I was using for my application was: SELECT Periode.LibPeriode, PaysPeriode.RefPaysPeriode, PaysPeriode.DateDebut, PaysPeriode.DateFin FROM Periode INNER JOIN (Pays INNER JOIN PaysPeriode ON Pays.RefPays = PaysPeriode.RefPays) ON Periode.RefPeriode = PaysPeriode.RefPeriode WHERE ((Pays.RefPays = 1) AND (PaysPeriode.DateDebut>#7/2/02#)) ORDER BY Periode.libPeriode, PaysPeriode.DateDebut However this wouldn't work in MySQL so I altered it to read... SELECT Periode.LibPeriode, PaysPeriode.RefPaysPeriode, PaysPeriode.DateDebut,PaysPeriode.DateFin FROM Periode, PaysPeriode, Pays WHERE Pays.RefPays = PaysPeriode.Refpays AND Periode.RefPeriode = PaysPeriode.RefPeriode AND PaysPeriode.DateDebut > 2002020700 AND Pays.RefPays = 1 ORDER BY Periode.libPeriode, PaysPeriode.DateDebut This is soo close to working but just not quite. The Access query just pulled out unique values for the Periode.LibPeriod field which was perfect. But the MySQL seems to pull them all out. I have tried GROUPING the result by the Periode.LibPeriode which gives me unique values in the field but I need the unique values with the highest startdate i.e. PaysPeriode.DateDebut but it always pulls out the first start date? Can anyone help? Thanks, Chris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL Query
At 1:01 PM +0100 5/3/02, CM wrote: >SELECT Periode.LibPeriode, PaysPeriode.RefPaysPeriode, >PaysPeriode.DateDebut,PaysPeriode.DateFin >FROM Periode, PaysPeriode, Pays >WHERE Pays.RefPays = PaysPeriode.Refpays >AND Periode.RefPeriode = PaysPeriode.RefPeriode >AND PaysPeriode.DateDebut > 2002020700 >AND Pays.RefPays = 1 >ORDER BY Periode.libPeriode, PaysPeriode.DateDebut > >This is soo close to working but just not quite. The Access query just >pulled out unique values for the Periode.LibPeriod field which was perfect. >But the MySQL seems to pull them all out. I have tried GROUPING the result >by the Periode.LibPeriode which gives me unique values in the field but I >need the unique values with the highest startdate i.e. PaysPeriode.DateDebut >but it always pulls out the first start date? I'd add a Max() function with the Group By, like... SELECT Periode.LibPeriode, PaysPeriode.RefPaysPeriode, MAX(PaysPeriode.DateDebut), PaysPeriode.DateFin FROM Periode, PaysPeriode, Pays WHERE Pays.RefPays = PaysPeriode.Refpays AND Periode.RefPeriode = PaysPeriode.RefPeriode AND PaysPeriode.DateDebut > 2002020700 AND Pays.RefPays = 1 GROUP BY Periode.libPeriode ORDER BY Periode.libPeriode, PaysPeriode.DateDebut I'm not certain why your Access query only pulled one record. From the skim, it seems as if it should pull all that match, since you didn't indicate you wanted only those distinctly, or a max value. Though often an Inner Join can act as a limiter as well, so that maybe why it worked. But anyway, the find all records, max or min of the value you want, group records (max, min, ave, count , etc. functions all REQUIRE the Group By, you'll generate an error if you forget it), then order them, should work. Alnisa -- . Alnisa Allgood Executive Director Nonprofit Tech (ph) 415.337.7412 (fx) 415.337.7927 (url) http://www.nonprofit-techworld.org (url) http://www.nonprofit-tech.org (url) http://www.tech-library.org . Nonprofit Tech E-Update mailto:[EMAIL PROTECTED] . transforming nonprofits through technology . -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] sql querry problem
the ansi syntax for delete is "delete from table where ???" You don't tell it what to delete since it will always delete whole rows. So just remove o.* . If you are in need of setting a field to blank you'll have to use an update, not a delete. <>< Ryan -Original Message- From: andy [mailto:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 3:02 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] sql querry problem Hi there, I do have 2 tables. One contains an update for the other one. Before inserting I would like to delete all the entries in the old table containing the same country_code like one in the new table. I tryed: delete o.* from Cities_update_imported n, test.cities o where n.country_code = o.country_code But does not work though:-( can anybody help on that? thanx, Andy -- 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] indexing
Hi all, More of a database question here, my apologies: I've been doing some reading on indexes but I thought I should get an experienced persons input. My question is about "Over Indexing". I have a MySQL MyISAM table that stores inventory transactions, in, out etc. I store numeric fields that are primary indexes in other tables, 2 datetime fields and a qty field, Paul DuBois' MySQL book says that anything used in a where clause or join clause is a candidate for an index. It turns out that 7 out of eight fields fit this description,1 primary key, 4 smallints, and 2 are datetime fields. Should I use all these indexes or is this overkill? Thanks for any input Steve. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] indexing
I could be wrong but I seem to remember that Primary keys are indexed automatically. Regardless, smallints and dates really are pretty easy to index. So go for it! Strings introduce a bit of overhead, but I've never heard anyone, nor would I, recommend holding back on indexing because you have too many. Remember, the server only uses the index when it can, or when you explicitly tell it to. Sometimes it will try to use an index and it shouldn't, but there's never really a reason not to use it on numeric fields, which all of yours really are (dates are numeric on the insides). <>< Ryan -Original Message- From: Steve Bradwell [mailto:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 9:24 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] indexing Hi all, More of a database question here, my apologies: I've been doing some reading on indexes but I thought I should get an experienced persons input. My question is about "Over Indexing". I have a MySQL MyISAM table that stores inventory transactions, in, out etc. I store numeric fields that are primary indexes in other tables, 2 datetime fields and a qty field, Paul DuBois' MySQL book says that anything used in a where clause or join clause is a candidate for an index. It turns out that 7 out of eight fields fit this description,1 primary key, 4 smallints, and 2 are datetime fields. Should I use all these indexes or is this overkill? Thanks for any input Steve. -- 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] Can´t connect on Mysql server
I have a aplication (PHP) into server Apache that needs to read a database Mysql in another server IIS/win2000). These servers are in diferent LAN. This aplicattion has a perffect funcionality into LAN where is the database, but when we try with remote conetion it doesn´t. The mensage is that: Warning: Can´t connect Mysql on (110) The number 110 in warning mensage is a port or error number? What can I do to stabilish this conection? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] teste
s -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] sql querry problem
"Delete from" does not work with multiple tables, AFAIK.. -Szii - Original Message - From: "andy" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, May 03, 2002 2:02 AM Subject: [PHP-DB] sql querry problem > Hi there, > > I do have 2 tables. One contains an update for the other one. Before > inserting I would like to delete all the entries in the old table containing > the same country_code like one in the new table. > > I tryed: > > delete o.* > from Cities_update_imported n, test.cities o > where n.country_code = o.country_code > > But does not work though:-( > > can anybody help on that? > > thanx, Andy > > > > > -- > 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] sql querry problem
That too... :-) You'll have to do two deletes, if your referential integrity is set up you'll have to delete the children before you can delete the parent as well. Thanks Szii.. I missed that part. <>< Ryan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, May 03, 2002 11:48 AM To: andy Cc: [EMAIL PROTECTED] Subject: Re: [PHP-DB] sql querry problem "Delete from" does not work with multiple tables, AFAIK.. -Szii - Original Message - From: "andy" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, May 03, 2002 2:02 AM Subject: [PHP-DB] sql querry problem > Hi there, > > I do have 2 tables. One contains an update for the other one. Before > inserting I would like to delete all the entries in the old table containing > the same country_code like one in the new table. > > I tryed: > > delete o.* > from Cities_update_imported n, test.cities o > where n.country_code = o.country_code > > But does not work though:-( > > can anybody help on that? > > thanx, Andy > > > > > -- > 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 Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] translating password($var) back to text
I am setting up a PHP-mySQL login script. One key feature I need is the ability automatically to e-mail people their password when they forget it. Obviously, I can store the password without using password() and retrieve it at any time. Is it possible to store the password using password() and retrieve a decoded value from mySQL? John Hughes __ Do You Yahoo!? Yahoo! Health - your guide to health and wellness http://health.yahoo.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] translating password($var) back to text
At 15:47 -0700 5/3/02, John Hughes wrote: >I am setting up a PHP-mySQL login script. One key feature I need is >the ability automatically to e-mail people their password when they >forget it. > >Obviously, I can store the password without using password() and >retrieve it at any time. Is it possible to store the password using >password() and retrieve a decoded value from mySQL? > >John Hughes According to the MySQL manual, PASSWORD() implements one-way encryption. You might want to consider ENCODE()/DECODE() instead. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
