[PHP-DB] Date
Can someone help me with the correct sytax here ! I'm trying to show results from the last two days(or week etc) This is what I've got SELECT . WHERE. AND ( (DATE_SUB(j.stellen.t_stellen_date INTERVAL 2 day) j.stellen.t_stellen_date) but it doesn't work ! What's wrong ? The t_stellen_date field is a date field ! Thanks -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Date
On Wednesday 22 May 2002 16:51, John Fishworld wrote: Can someone help me with the correct sytax here ! Please state which DB you're using. I assume it's MySQL. I'm trying to show results from the last two days(or week etc) This is what I've got SELECT . WHERE. AND ( (DATE_SUB(j.stellen.t_stellen_date INTERVAL 2 day) j.stellen.t_stellen_date) but it doesn't work ! Please state /how/ it doesn't work! What's wrong ? You're probably missing a comma before INTERVAL, and you're using the wrong formula: AND ((DATE_SUB(NOW(), INTERVAL 2 day) j.stellen.t_stellen_date) ... The t_stellen_date field is a date field ! Ok, we can hear you loud and clear, no need for the exclamation marks :) -- Jason Wong - Gremlins Associates - www.gremlins.com.hk Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * /* Why do they call it baby-SITTING when all you do is run after them? */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Date
Sorry yes its MySQL yes it was the missing comma causing the problem (thanks) but now I get You have an error in your SQL syntax near 'LIMIT 0, 30' at line 1 SELECT DISTINCT .. WHERE (t_city.t_city_id_city = t_citystate.t_citystate_id_city) AND (t_citystate.t_citystate_id_state = t_state.t_state_id_state) AND (t_city.t_city_id_city = j_niederlassung.t_niederlassung_city_id) AND (j_niederlassung.t_niederlassung_firm_id = j_firm.t_firm_id) AND (j_niederlassung.t_niederlassung_id = j_stellenniederlassung.t_stellenniederlassung_id_niederlassung) AND (j_stellenniederlassung.t_stellenniederlassung_id_stellen = j_stellen.t_stellen_id) AND (j_stellen.t_stellen_id = j_stellenbranchen.t_stellenbranchen_id_stellen) AND (j_stellen.t_stellen_id = j_stellentype.t_stellentype_id_stellen) AND (j_stellen.t_stellen_id = j_stellenberufe.t_stellenberufe_id_stellen) AND (j_stellenberufe.t_stellenberufe_id_berufe = j_berufe.t_berufe_id_berufe) AND (j_stellenbranchen.t_stellenbranchen_id_branchen = j_branchen.t_branchen_id_branchen) AND (j_stellentype.t_stellentype_id_type = j_type.t_type_id) AND (t_stellen_who != 3) AND ((t_stellen_wanted = 0) or (t_stellen_wanted = 2) or (t_stellen_wanted = 4)) AND ((DATE_SUB(NOW(), INTERVAL 2 day) j.stellen.t_stellen_date) LIMIT 0, 30 It works fine without the Date part. Okay no more exclamation marks :-)) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Date
On Wednesday 22 May 2002 17:23, John Fishworld wrote:
Sorry yes its MySQL
yes it was the missing comma causing the problem (thanks)
but now I get
You have an error in your SQL syntax near 'LIMIT 0, 30' at line 1
AND ((DATE_SUB(NOW(), INTERVAL 2 day) j.stellen.t_stellen_date) LIMIT 0,
30
My guess is that you've a missing ')' or an extra '(' somewhere.
--
Jason Wong - Gremlins Associates - www.gremlins.com.hk
Open Source Software Systems Integrators
* Web Design Hosting * Internet Intranet Applications Development *
/*
It looks like blind screaming hedonism won out.
*/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Date
Duh found it !
AND ((DATE_SUB('NOW()', INTERVAL 2 day) 'j.stellen.t_stellen_date')
should be
AND ( (DATE_SUB ('NOW()', INTERVAL 2 day) ) 'j.stellen.t_stellen_date')
missing close bracket :-))
On Wednesday 22 May 2002 17:23, John Fishworld wrote:
Sorry yes its MySQL
yes it was the missing comma causing the problem (thanks)
but now I get
You have an error in your SQL syntax near 'LIMIT 0, 30' at line 1
AND ((DATE_SUB(NOW(), INTERVAL 2 day) j.stellen.t_stellen_date) LIMIT
0,
30
My guess is that you've a missing ')' or an extra '(' somewhere.
--
Jason Wong - Gremlins Associates - www.gremlins.com.hk
Open Source Software Systems Integrators
* Web Design Hosting * Internet Intranet Applications Development *
/*
It looks like blind screaming hedonism won out.
*/
--
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 use iso8859-2 with PHP+Oracle
I put the characters '±æê³ñ󶼿' into the database with php script, and I find the following in my table: '1fj3qs6?'. I am using Oracle 8.1.7, PHP 4.1.1, Apache 1.3.22. I have set NLS_LANG=Polish_Poland.EE8ISO8859P2 in my environment and SetEnv=Polish_Poland.EE8ISO8859P2 in httpd.conf. The database nls_characterset and nls_nchar_characterset are also EE8ISO8859P2. Does anyone know what I do wrong? Krzysztof Socki [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Complicated query on very simple database - pointers sought
Hi all, I want to report some figures to my colleagues. My database (2 fields: Customer, Order date) contains over 15000 records dating back to 1999. I want to be able to show, in a web page, the following information Month Customers placing orders Orders placed Average Orders/Cust Average over prev 12 months. My work with MySQL has never been this detailed. Can anyone give me any pointers? Regards George in Edinburgh -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Complicated query on very simple database - pointers sought
Look into Aggregate Functions. They can let you do the things that you are looking for. SELECT Month(OrderDate) as 'Month', Count(DISTINCT Customer) as 'NumCustomersOrdering', Count(Customer) as 'TotalOrders' FROM tbl_Orders GROUP BY Month(OrderDate) will give you a list of months, and the number of Customer Orders. Throw in a distinct if you want to know how many different customers ordered. The GROUP BY clause basically tells it when to reset the counter. You can get sums, averages, counts, etc using aggregates. Check this page for more information: http://www.mysql.com/doc/G/r/Group_by_functions.html (I mainly use MSSQL, so the syntax might be slightly different - refer to the URL above for specifics). George Pitcher [EMAIL PROTECTED] wrote in message 03b701c20190$8f53fe00$630db092@HLATITUDE">news:03b701c20190$8f53fe00$630db092@HLATITUDE... Hi all, I want to report some figures to my colleagues. My database (2 fields: Customer, Order date) contains over 15000 records dating back to 1999. I want to be able to show, in a web page, the following information Month Customers placing orders Orders placed Average Orders/Cust Average over prev 12 months. My work with MySQL has never been this detailed. Can anyone give me any pointers? Regards George in Edinburgh -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] PHP + PostgreSQL
Hi, I am trying to configure a webserver running iplanet and php to connect to a postgresql server. My setup is the following: Web server: Solaris 2.6, iplanet 4.1, PHP 4.1 DB server: Linux 2.2.20 (debian woody), PostgreSQL 7.2.1. I have built PHP under Solaris with the configuration: ./configure --with-pgsql=/usr/local/pgsql --with-nsapi=/usr/netscape/server4 --enable-track-vars --enable-libgcc However I am not able to connect to the PostgreSQL server through the web server, I get a message complaining about the server running without the -I flag. However the server is running with the -I flag and I am able to connect to it from the web Solaris box using the pgsql client. Does anyone have a success story :) with a similar configuration? I would appreciate also pointers to tutorials/guides on PHP/PostgreSQL/Iplanet installation. Thanks in advance, Luiz. ___ Yahoo! Encontros O lugar certo para você encontrar aquela pessoa que falta na sua vida. Cadastre-se hoje mesmo! http://br.encontros.yahoo.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] PHP Mysql Select Distinct problem
Hi there everyone, I have the followingMySQL/PHP code: $query = "SELECT DISTINCT property_name,board,standard,description,region,country,state,city,address,zip,fax,telephone,email,url,image,image_type FROM llm WHERE property_name LIKE '%$property_name%' OR country LIKE '%$property_name%' OR city LIKE '%$property_name%' ORDER BY property_name ASC LIMIT $offset, $item_perpage";$querytotal = "SELECT count(DISTINCT property_name,board,standard,description,region,country,state,city,address,zip,fax,telephone,email,url,image,image_type) FROM llm WHERE property_name LIKE '%$property_name%' OR country LIKE '%$property_name%' OR city LIKE '%$property_name%' ORDER BY property_name ASC"; Which works great BUT there is 1 problem, I need to be able to pull the value ID from the database at the same time but WITHOUT it being a DISTINCT value, how can I do this? All the others need to be DISTINCT apart from ID, i'm very confused. Thanks for everything. Regards Chris Payne www.planetoxygene.com
RE: [PHP-DB] Re: Question about ODBC databases
Estimado Gerardo, estoy comenzado a desarrollar una solución utilizando un servicio en NT y un cliente en php (los estoy desarrollando en java). El servicio en NT no es más que un socket server que consulta vía ODBC a los archivos de bases de datos que están en el propio NT Server. Las soluciones de Benjamin Walling [[EMAIL PROTECTED]] y de John Lim [[EMAIL PROTECTED]] no se me habían ocurrido y me parecen por demás ingeniosas. El unico inconveniente que veo es que pasan la consulta por la URL y tengo entendido que esta tiene un tamaño máximo. De la pregunta que me haces, realmente no tengo idea de donde colocar la base de datos en el linux. En cuanto tenga la solución te cuento. Saludos Juan I'm developing a solution by using an NT service and a client into a php code (developed with java). The NT service is a socket server that query the datafiles through ODBC localized in the NT Server. The solutions proposed by Benjamin Walling and John Lim are pretty smart and I didn't occurs to me. But I found some troubles with this approach: as far as I know the URL has a maximum length. About your question, I don't know where locate the datafile in a Linux Server. ASAP I was finished my proposal of solution I'll tell you -Mensaje original- De: Gerardo Morales [mailto:[EMAIL PROTECTED]] Enviado el: Lunes 20 de Mayo de 2002 14:44 Para: [EMAIL PROTECTED] Asunto: Re: [PHP-DB] Re: Question about ODBC databases Gracias Juan. Sobre el mismo tema, aunque a la vez separado, en el caso de que la BD se pusiera en forma local, en que directorio se especifica? es necesario hacer algun cambio en php.ini para especificarle una localidad en especifico?? en el caso de que asi sea, alguien sabra si es posible utilizar MOUNT, NFSD o alguna otra opcion dentro de Linux??? Thanks, Another questions are the following, If I can add the Access DB in a local directory in the Linux server, which location should be (dev/, usr/, etc)?? If I add it in a especific path for example /htdocs/MyDB/Access how can I especified to PHP that read thiis path, Need i modified the php.ini file?? Can I Mount a Network File System on Linux, as I can mount the HD with the DB (win nt OS) to a local Path?? using NFSD, MOUNT or another option?? Thanks In advanced Juan Angel Ringhetti wrote: Gerardo, tuve el mismo problema. Compartir con Samba no te soluciona el problema porque ODBC no cruza servers sino que trabaja en el mismo server. Encontré una solucion con un ODBCBridge (www.easysoft.com) pero su licencia no es gratuita sino que tiene un período de trial. Si resolvés el problema avisame. Juan -- 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: Can't use iso8859-2 with PHP+Oracle
the character set of the database is perhaps ascii7bit. change it to ascii8bit or to your ISO characterset, using alter database command. Ascii8 is the most flexible and should be used if you want to support more than one language. Could be found here: http://technet.oracle.com/doc/oracle8i_816/server.816/a76966/ch3.htm#47136 Regards Michael Krzysztof Socki [EMAIL PROTECTED] schrieb im Newsbeitrag [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... I put the characters '±æê³ñ󶼿' into the database with php script, and I find the following in my table: '1fj3qs6?'. I am using Oracle 8.1.7, PHP 4.1.1, Apache 1.3.22. I have set NLS_LANG=Polish_Poland.EE8ISO8859P2 in my environment and SetEnv=Polish_Poland.EE8ISO8859P2 in httpd.conf. The database nls_characterset and nls_nchar_characterset are also EE8ISO8859P2. Does anyone know what I do wrong? Krzysztof Socki [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Can't use iso8859-2 with PHP+Oracle
The database nls_characterset and nls_nchar_characterset are also EE8ISO8859P2. Oops, overwrote this...hmm...don't really know then. EE8ISO8859P2 hmm...not WE8ISO8859P2? but don't know exactly. for german it is GERMAN_GERMANY.WE8ISO8859P1 Regards Michael Michael Virnstein [EMAIL PROTECTED] schrieb im Newsbeitrag [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... the character set of the database is perhaps ascii7bit. change it to ascii8bit or to your ISO characterset, using alter database command. Ascii8 is the most flexible and should be used if you want to support more than one language. Could be found here: http://technet.oracle.com/doc/oracle8i_816/server.816/a76966/ch3.htm#47136 Regards Michael Krzysztof Socki [EMAIL PROTECTED] schrieb im Newsbeitrag [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... I put the characters '±æê³ñ󶼿' into the database with php script, and I find the following in my table: '1fj3qs6?'. I am using Oracle 8.1.7, PHP 4.1.1, Apache 1.3.22. I have set NLS_LANG=Polish_Poland.EE8ISO8859P2 in my environment and SetEnv=Polish_Poland.EE8ISO8859P2 in httpd.conf. The database nls_characterset and nls_nchar_characterset are also EE8ISO8859P2. Does anyone know what I do wrong? Krzysztof Socki [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: PHP Mysql Select Distinct problem
MessageAccording to http://www.mysql.com/doc/D/I/DISTINCT_optimisation.html, DISTINCT is converted to a GROUP BY on all columns, DISTINCT combined with ORDER BY will in many cases also need a temporary table. Instead of using DISTINCT, consider adding GROUP BY for all the columns except ID. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Complicated query on very simple database - pointers sought
George Pitcher [EMAIL PROTECTED] wrote in message
03b701c20190$8f53fe00$630db092@HLATITUDE">news:03b701c20190$8f53fe00$630db092@HLATITUDE...
My database (2 fields: Customer, Order date) contains over 15000 records
dating back to 1999.
I want to be able to show, in a web page, the following information
Month Customers placing orders Orders placed
Average Orders/Cust Average over prev 12 months.
... if you know 'orders placed' and 'number of customers',
'average orders per customer' is easily calculated; and
'average over prev 12 months' doesn't fit well into this query,
would be better done in PHP.
How about
SELECT
MONTH(orderdate) AS month,
YEAR(orderdate) AS year,
COUNT(DISTINCT customer) AS customers
COUNT(customer) AS orders
FROM mytable
GROUP BY year,month
ORDER BY year,month ASC
then in PHP,
// month-to-name
$months = array(, Jan, Feb, Mar, Apr,
May, Jun, Jul, Aug, Sept, Oct,
Nov, Dec);
// for running 12-month average
$sum = 0;
$sum_num = 0;
$item = array();
$index = 0;
// for printing start-of-year-only years
$prevyear = 0;
echo
\ntabletheadtr
.thYear/th
.thMonth/th
.thCustomers placing orders/th
.thOrders placed/th
.thAverage orders per customer/th
.th12-month average/th
./tr\n/theadtbody;
while($row = mysql_fetch_array($res)) {
// check whether to print year value
if ($row['year'] == $prevyear)
$yr = ;
else
$yr = $prevyear = $row['year'];
// get month-name
$mon = $months[$row['month']];
// calculate this-month average orders per customer
$avg = $row['orders'] / $row['customers'];
// update 12-month average
$items[$index] = $avg;
$sum += $avg;
$sum_num++;
if ($sum_num 12) {
$sum -= $items[$index-12];
unset($items[$index-12]);
$sum_num--;
}
++$index;
$twelvemonth_avg = $sum / $sum_num;
// print table row
echo
\n\ttr
.td$yr/td
.td$mon/td
.td{$row['customers']}/td
.td{$row['orders']}/td
.td$avg/td
.td$twelvemonth_avg/td
.\tr;
}
echo \n/tbody/table;
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: 50.000 records, 2 values and 1.5 s to find out?!!
oh... on MySQL Andy [EMAIL PROTECTED] schrieb im Newsbeitrag [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... Hi there, I have a user table, containing a column on gender. Type is tinyint(1) and 1 is male 2 is female. No index. 5 entries This statement: SELECT sex, count(*) AS c FROM user GROUP BY sex takes 1.5 s to execute I am wondering if ti wuold be possible to find this out any faster ? SELECT count(*) AS c FROM .user where sex = 1 takes about half the time :-) So maybe some of you guys do have some experiance on that. This is just way to long. I appreciate every help on that, Thanx in advance, Andy -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] about MySQL config for PHP on regular user accounts
Good afternoon everyone, I have a site running on Apache 1.3.24, PHP 4.2.0, and MySQL 3.23.49 all done on a W2K box. Whenever I run as regular user instead of admin, my site runs but cannot make any db connections through the PHP scripts. I installed MySQL as a service, but it won't run when I switch from admin to joe blow user accounts. Why is the Apache service able to run on other user accounts but MySQL is not? I remember the option enable for all users when Apache installed, but MySQL gave no such option on install that I noted. Has anybody else had this problem? The site doesn't get enough hits to warrant a single-use computer, so I need to be able to have multiple user accounts. I looked at the service properties of mysql in control panel but found no options which could make a difference (or so I think). My apologies for posting this problem to the group. I realize it is off-topic, but I'm not sure where to post it after reviewing the MySQL faq at mysql.com and not finding anything. To Summarize, HERE'S THE QUESTION: How do you configure the mysql daemon to run under any user account on Windows? thanks, Kirk -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
