[PHP-DB] Assistance on Query
Hi People, I would like some assistance on the following scenario. I have to pull out all records for a particular userid (easy enough) and then only show those entries where the follwing occurs. These records from the table will contain either an entry in the services_type field or the non_services_type field. What I need to do is show only those where the number of consecutive records that contain an entry in the non_services_type field is greater than or equal to 3 so example:- record 1 contains an entry in non_services_type record 2 contains an entry in services_type record 3 contains an entry in non_services_type record 4 contains an entry in non_services_type record 5 contains an entry in non_services_type record 6 contains an entry in services_type so I would need to display records 3,4,5 only Can anyone assist me with this? Cheers, Shannon -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: Adding Up MySQL Results
snip
I run this mysql query: select
sum(quantity)*price,price from sale
where dvd_id=1 group by price;
I get this result:
sum(quantity)*price price
450.00 15.00
29.99 29.99
I want to display the sum of the values in the first
column
(450.00+29.99), but I cannot figure out how to do
this! Can someone
clue me in?
/snip
?php
// Multiplying with varaibles
$factorA = number_format(system_value('sum');
$factorB = number_format(system_value('price'), 0);
$product = $factorA + $factorB;
echo $product;
?
Something like that?
You'll have to obviously play with this to make it
work for you. But calculations work like your Windows
Calculator number keypad.
+ = plus
- = minus
* = multiply
etc...
Look at these:
http://au.php.net/manual/en/function.number-format.php
http://au.php.net/round
J
Find local movie times and trailers on Yahoo! Movies.
http://au.movies.yahoo.com
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Assistance on Query
Shannon Doyle wrote: Hi People, I would like some assistance on the following scenario. I have to pull out all records for a particular userid (easy enough) and then only show those entries where the follwing occurs. These records from the table will contain either an entry in the services_type field or the non_services_type field. What I need to do is show only those where the number of consecutive records that contain an entry in the non_services_type field is greater than or equal to 3 so example:- record 1 contains an entry in non_services_type record 2 contains an entry in services_type record 3 contains an entry in non_services_type record 4 contains an entry in non_services_type record 5 contains an entry in non_services_type record 6 contains an entry in services_type so I would need to display records 3,4,5 only Can anyone assist me with this? Could you explain what the logic behind doing this is. i.e. Why? (it might help to understand the problem, and possibly give a solution that does not rely on 'consecutiveness') I'm guessing you are using MySQL which is a relational DB, that means the order of records stored is essentially abstracted from the DB user, for instance using an ORDER BY clause will change the order of the result and therefore the 'consectiveness' of the values. It looks as if you will need to post-process the result set in PHP to get what you want, alternatively your DB may support stored procedures which could be an alternative (but harder to do than to do it in PHP) i.e. the stored procedure works out the correct rows and then outputs the result when you something like: SELECT * FROM getConsecServiceRows( 3 ) rgds, Jochem Cheers, Shannon -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] No communication between PHP 5.0.3 +MySQL 4.1.9
Will somebody please help me!!! I'm trying to set up my web server for PHP and MySQL. I've installed PHP 5.0.3 and it works fine. I've installed MySQL 4.1.9 and it works fine. BUT, I can't access MySQL from PHP. I have put the php_mysqli.dll and libmysql.dll in the C:\PHP folder, removed the ';' from the beginning of the extension=php_mysqli.dll line in php.ini, but I just can't get the two talking to one another. I just get the following message: Fatal error: Call to undefined function mysqli_connect() in c:\Inetpub\wwwroot\stmary1\administration\handle_rotas.php on line 30 In my php file, line 30 reads: $connection = mysqli_connect($host,$user,$password); with each variable being defined already. I'm using IIS 5 as my webserver. Please can somebody help. James Brown -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] No communication between PHP 5.0.3 +MySQL 4.1.9
Thanks Micah, but I've already tried that one. James Micah Stevens [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Have you set the extensions_dir properly? It may not be looking in c:\PHP for the extension.. -Micah On Sunday 16 January 2005 10:37 am, J A Brown wrote: Will somebody please help me!!! I'm trying to set up my web server for PHP and MySQL. I've installed PHP 5.0.3 and it works fine. I've installed MySQL 4.1.9 and it works fine. BUT, I can't access MySQL from PHP. I have put the php_mysqli.dll and libmysql.dll in the C:\PHP folder, removed the ';' from the beginning of the extension=php_mysqli.dll line in php.ini, but I just can't get the two talking to one another. I just get the following message: Fatal error: Call to undefined function mysqli_connect() in c:\Inetpub\wwwroot\stmary1\administration\handle_rotas.php on line 30 In my php file, line 30 reads: $connection = mysqli_connect($host,$user,$password); with each variable being defined already. I'm using IIS 5 as my webserver. Please can somebody help. James Brown -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] No communication between PHP 5.0.3 +MySQL 4.1.9
Well, even if that's not the exact issue, the problem almost certainly lies in the inability of php to properly load the extension. That's where you should look. The connection to MySQL is not in question here. -Micah On Sunday 16 January 2005 12:42 pm, J A Brown wrote: Thanks Micah, but I've already tried that one. James Micah Stevens [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Have you set the extensions_dir properly? It may not be looking in c:\PHP for the extension.. -Micah On Sunday 16 January 2005 10:37 am, J A Brown wrote: Will somebody please help me!!! I'm trying to set up my web server for PHP and MySQL. I've installed PHP 5.0.3 and it works fine. I've installed MySQL 4.1.9 and it works fine. BUT, I can't access MySQL from PHP. I have put the php_mysqli.dll and libmysql.dll in the C:\PHP folder, removed the ';' from the beginning of the extension=php_mysqli.dll line in php.ini, but I just can't get the two talking to one another. I just get the following message: Fatal error: Call to undefined function mysqli_connect() in c:\Inetpub\wwwroot\stmary1\administration\handle_rotas.php on line 30 In my php file, line 30 reads: $connection = mysqli_connect($host,$user,$password); with each variable being defined already. I'm using IIS 5 as my webserver. Please can somebody help. James Brown -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Assistance on Query
Hi Jochem, My apologies, when I typed consecutive, I meant by date. Basically the query is for a report. The client wants to be able to see when there have been 3 or more entries of non_services_type between services_type entries, and then display these entries. I am using MySQL and would Order the entries by date. I hope that clears things up a little. - Shannon -Original Message- From: Jochem Maas [mailto:[EMAIL PROTECTED] Sent: Monday, January 17, 2005 1:37 AM To: Shannon Doyle Cc: php-db@lists.php.net Subject: Re: [PHP-DB] Assistance on Query Shannon Doyle wrote: Hi People, I would like some assistance on the following scenario. I have to pull out all records for a particular userid (easy enough) and then only show those entries where the follwing occurs. These records from the table will contain either an entry in the services_type field or the non_services_type field. What I need to do is show only those where the number of consecutive records that contain an entry in the non_services_type field is greater than or equal to 3 so example:- record 1 contains an entry in non_services_type record 2 contains an entry in services_type record 3 contains an entry in non_services_type record 4 contains an entry in non_services_type record 5 contains an entry in non_services_type record 6 contains an entry in services_type so I would need to display records 3,4,5 only Can anyone assist me with this? Could you explain what the logic behind doing this is. i.e. Why? (it might help to understand the problem, and possibly give a solution that does not rely on 'consecutiveness') I'm guessing you are using MySQL which is a relational DB, that means the order of records stored is essentially abstracted from the DB user, for instance using an ORDER BY clause will change the order of the result and therefore the 'consectiveness' of the values. It looks as if you will need to post-process the result set in PHP to get what you want, alternatively your DB may support stored procedures which could be an alternative (but harder to do than to do it in PHP) i.e. the stored procedure works out the correct rows and then outputs the result when you something like: SELECT * FROM getConsecServiceRows( 3 ) rgds, Jochem Cheers, Shannon -- 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] Assistance on Query
Shannon Doyle wrote:
Hi Jochem,
My apologies, when I typed consecutive, I meant by date. Basically the query
is for a report. The client wants to be able to see when there have been 3
or more entries of non_services_type between services_type entries, and then
display these entries.
I am using MySQL and would Order the entries by date.
I hope that clears things up a little.
yeah! I can't think how to do that offhand in SQL but in php it would go
a little like this...(I'm assuming an array of results is being passed -
where each item in the array is an object.):
function filter3ConsecutiveNST($rows)
{
$output = $tmp = array();
$cNSTcount = 0;
foreach ($rows as $row) {
if ($row-type == 'non_service_type') {
$cNSTcount++;
if ($cNSTcount == 3) {
$output =+ $tmp;
$output[] = $row;
$tmp = array();
} else if ($cNSTcount 3) {
$output[] = $row;
} else {
$tmp[] = $row;
}
} else {
$tmp = array();
$cNSTCount = 0;
}
}
return $output;
}
hope that give you an idea.
- Shannon
-Original Message-
From: Jochem Maas [mailto:[EMAIL PROTECTED]
Sent: Monday, January 17, 2005 1:37 AM
To: Shannon Doyle
Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] Assistance on Query
Shannon Doyle wrote:
Hi People,
I would like some assistance on the following scenario.
I have to pull out all records for a particular userid (easy enough) and
then only show those entries where the follwing occurs.
These records from the table will contain either an entry in the
services_type field or the non_services_type field. What I need to do is
show only those where the number of consecutive records that contain an
entry in the non_services_type field is greater than or equal to 3
so example:-
record 1 contains an entry in non_services_type
record 2 contains an entry in services_type
record 3 contains an entry in non_services_type
record 4 contains an entry in non_services_type
record 5 contains an entry in non_services_type
record 6 contains an entry in services_type
so I would need to display records 3,4,5 only
Can anyone assist me with this?
Could you explain what the logic behind doing this is. i.e. Why?
(it might help to understand the problem, and possibly give a solution
that does not rely on 'consecutiveness')
I'm guessing you are using MySQL which is a relational DB, that means
the order of records stored is essentially abstracted from the DB user,
for instance using an ORDER BY clause will change the order of the
result and therefore the 'consectiveness' of the values.
It looks as if you will need to post-process the result set in PHP to
get what you want, alternatively your DB may support stored procedures
which could be an alternative (but harder to do than to do it in PHP)
i.e. the stored procedure works out the correct rows and then outputs
the result when you something like:
SELECT * FROM getConsecServiceRows( 3 )
rgds,
Jochem
Cheers,
Shannon
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Dealing With MySQL's new Time Calculations
I have an interesting problem I'm trying to solve. Right now, I'm keeping track of sessions starting and ending times with a PHP datetime field, and calculating the total time spent in all sessions with the new PHP time command... something like this: $total_minutes = mysql_query(select ((sum(time_to_sec(timediff(tourney.finish, tourney.start/ (60*60)) FROM tourney WHERE tourney.closed = '1' AND tourney.userid = '$userid'); but the problem is that in some cases, there are overlapping sessions or tourneys. That is, someone might have a tourney.start at 10:00 with a tourney.finish at 10:45 and start another tourney.start at 10:15 and finish at 10:55. right now, i'd get the sum of both sessions, 45 minutes + 40 minutes = 85 minutes, where I'd rather have the calculation of 10:00 (the earliest start) to 10: 55 (the latest finish), for a total of 55 minutes. I can't get my head around the right logic to use to get to an answer like that. There may be hundreds of registered tourneys, so testing one against the rest to see if there is a matching time seems very wasteful and sloppy. Any suggestions on how I might proceed? Perhaps a one time query when I add the record into the database (that would make changing the record difficult) to compare the session time to other session times somehow? Thanks in advance Jake mail2web - Check your email from the web at http://mail2web.com/ . -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Security Question
Hi everyone, I have a security question, I want to see if I am right or wrong. I have programmed a system with PHP and MySQL, the main system resides on a secure server, but the client wants the login page on a NON-Secure server for marketing purposes. Am I the only one who thinks this is a major security concern? Chris -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.6.13 - Release Date: 1/16/2005
Re: [PHP-DB] Security Question
If it submits to a secure server the form data will be encrypted before transmission I believe. At least that's my understanding, and that seems to be how ebay does it for example. Once you log-in, it submits to a secure page. -Micah On Sunday 16 January 2005 06:38 pm, Chris Payne wrote: Hi everyone, I have a security question, I want to see if I am right or wrong. I have programmed a system with PHP and MySQL, the main system resides on a secure server, but the client wants the login page on a NON-Secure server for marketing purposes. Am I the only one who thinks this is a major security concern? Chris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Security Question
Hi It is better from a security point of view to have a secure login. The secure server encrypts the data between the browser and the server, making it impossible to read on its journey from you to the server. However whether it is a major security problem is another question. To view the traffic somebody must have access to the servers that route your request, which isn't easy. They then have to spot your traffic amongst all the other web traffic. If it is the login for your Swiss bank account where you hid the million you made without declaring tax then it should be secure - no question. On the other hand if it is just to login to see when your books will be delivered, with no sensitive financial information then the risk is smaller and it is unlikely that anyone is trying too hard to get your login, so an insecure login carries less risk. You could always host the login page on a non secure server but post the form to a secure server. Peter -Original Message- From: Micah Stevens [mailto:[EMAIL PROTECTED] Sent: 17 January 2005 02:46 To: php-db@lists.php.net Subject: Re: [PHP-DB] Security Question If it submits to a secure server the form data will be encrypted before transmission I believe. At least that's my understanding, and that seems to be how ebay does it for example. Once you log-in, it submits to a secure page. -Micah On Sunday 16 January 2005 06:38 pm, Chris Payne wrote: Hi everyone, I have a security question, I want to see if I am right or wrong. I have programmed a system with PHP and MySQL, the main system resides on a secure server, but the client wants the login page on a NON-Secure server for marketing purposes. Am I the only one who thinks this is a major security concern? Chris -- 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] Security Question
But what I'm saying is that if you're submitting a form from an unsecured page, to a script on a secure server, the data will still be encrypted. Anyone know this for sure to be correct? It sure makes sense this way. On Sunday 16 January 2005 07:27 pm, Peter Lovatt wrote: Hi It is better from a security point of view to have a secure login. The secure server encrypts the data between the browser and the server, making it impossible to read on its journey from you to the server. However whether it is a major security problem is another question. To view the traffic somebody must have access to the servers that route your request, which isn't easy. They then have to spot your traffic amongst all the other web traffic. If it is the login for your Swiss bank account where you hid the million you made without declaring tax then it should be secure - no question. On the other hand if it is just to login to see when your books will be delivered, with no sensitive financial information then the risk is smaller and it is unlikely that anyone is trying too hard to get your login, so an insecure login carries less risk. You could always host the login page on a non secure server but post the form to a secure server. Peter -Original Message- From: Micah Stevens [mailto:[EMAIL PROTECTED] Sent: 17 January 2005 02:46 To: php-db@lists.php.net Subject: Re: [PHP-DB] Security Question If it submits to a secure server the form data will be encrypted before transmission I believe. At least that's my understanding, and that seems to be how ebay does it for example. Once you log-in, it submits to a secure page. -Micah On Sunday 16 January 2005 06:38 pm, Chris Payne wrote: Hi everyone, I have a security question, I want to see if I am right or wrong. I have programmed a system with PHP and MySQL, the main system resides on a secure server, but the client wants the login page on a NON-Secure server for marketing purposes. Am I the only one who thinks this is a major security concern? Chris -- 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] Assistance on Query
Can you do what you require by grouping by your type. setting a count
field and then a where count 3? However if you then need to know the
details from the three consecutive fields then I don;t think it can be
done in SQL so you'll need to revert to php.
graeme
Jochem Maas wrote:
Shannon Doyle wrote:
Hi Jochem,
My apologies, when I typed consecutive, I meant by date. Basically
the query
is for a report. The client wants to be able to see when there have
been 3
or more entries of non_services_type between services_type entries,
and then
display these entries.
I am using MySQL and would Order the entries by date.
I hope that clears things up a little.
yeah! I can't think how to do that offhand in SQL but in php it would
go a little like this...(I'm assuming an array of results is being
passed - where each item in the array is an object.):
function filter3ConsecutiveNST($rows)
{
$output = $tmp = array();
$cNSTcount = 0;
foreach ($rows as $row) {
if ($row-type == 'non_service_type') {
$cNSTcount++;
if ($cNSTcount == 3) {
$output =+ $tmp;
$output[] = $row;
$tmp = array();
} else if ($cNSTcount 3) {
$output[] = $row;} else {
$tmp[] = $row;
}
} else {
$tmp = array();
$cNSTCount = 0;
}
}
return $output;
}
hope that give you an idea.
- Shannon
-Original Message-
From: Jochem Maas [mailto:[EMAIL PROTECTED] Sent: Monday, January
17, 2005 1:37 AM
To: Shannon Doyle
Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] Assistance on Query
Shannon Doyle wrote:
Hi People,
I would like some assistance on the following scenario.
I have to pull out all records for a particular userid (easy enough)
and
then only show those entries where the follwing occurs.
These records from the table will contain either an entry in the
services_type field or the non_services_type field. What I need to
do is
show only those where the number of consecutive records that contain an
entry in the non_services_type field is greater than or equal to 3
so example:-
record 1 contains an entry in non_services_type
record 2 contains an entry in services_type
record 3 contains an entry in non_services_type
record 4 contains an entry in non_services_type
record 5 contains an entry in non_services_type
record 6 contains an entry in services_type
so I would need to display records 3,4,5 only
Can anyone assist me with this?
Could you explain what the logic behind doing this is. i.e. Why?
(it might help to understand the problem, and possibly give a
solution that does not rely on 'consecutiveness')
I'm guessing you are using MySQL which is a relational DB, that means
the order of records stored is essentially abstracted from the DB
user, for instance using an ORDER BY clause will change the order of
the result and therefore the 'consectiveness' of the values.
It looks as if you will need to post-process the result set in PHP to
get what you want, alternatively your DB may support stored
procedures which could be an alternative (but harder to do than to do
it in PHP) i.e. the stored procedure works out the correct rows and
then outputs the result when you something like:
SELECT * FROM getConsecServiceRows( 3 )
rgds,
Jochem
Cheers,
Shannon
--
Experience is a good teacher, but she sends in terrific bills.
Minna Antrim
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Dealing With MySQL's new Time Calculations
Set your select to group by userid, select on min(tourney.start) value and max(tourney.start) value where closed = 1 etc. graeme. [EMAIL PROTECTED] wrote: I have an interesting problem I'm trying to solve. Right now, I'm keeping track of sessions starting and ending times with a PHP datetime field, and calculating the total time spent in all sessions with the new PHP time command... something like this: $total_minutes = mysql_query(select ((sum(time_to_sec(timediff(tourney.finish, tourney.start/ (60*60)) FROM tourney WHERE tourney.closed = '1' AND tourney.userid = '$userid'); but the problem is that in some cases, there are overlapping sessions or tourneys. That is, someone might have a tourney.start at 10:00 with a tourney.finish at 10:45 and start another tourney.start at 10:15 and finish at 10:55. right now, i'd get the sum of both sessions, 45 minutes + 40 minutes = 85 minutes, where I'd rather have the calculation of 10:00 (the earliest start) to 10: 55 (the latest finish), for a total of 55 minutes. I can't get my head around the right logic to use to get to an answer like that. There may be hundreds of registered tourneys, so testing one against the rest to see if there is a matching time seems very wasteful and sloppy. Any suggestions on how I might proceed? Perhaps a one time query when I add the record into the database (that would make changing the record difficult) to compare the session time to other session times somehow? Thanks in advance Jake mail2web - Check your email from the web at http://mail2web.com/ . -- Experience is a good teacher, but she sends in terrific bills. Minna Antrim -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Dealing With MySQL's new Time Calculations
Sorry that should have been max(tourney.finish) :-[ graeme graeme wrote: Set your select to group by userid, select on min(tourney.start) value and max(tourney.start) value where closed = 1 etc. graeme. [EMAIL PROTECTED] wrote: I have an interesting problem I'm trying to solve. Right now, I'm keeping track of sessions starting and ending times with a PHP datetime field, and calculating the total time spent in all sessions with the new PHP time command... something like this: $total_minutes = mysql_query(select ((sum(time_to_sec(timediff(tourney.finish, tourney.start/ (60*60)) FROM tourney WHERE tourney.closed = '1' AND tourney.userid = '$userid'); but the problem is that in some cases, there are overlapping sessions or tourneys. That is, someone might have a tourney.start at 10:00 with a tourney.finish at 10:45 and start another tourney.start at 10:15 and finish at 10:55. right now, i'd get the sum of both sessions, 45 minutes + 40 minutes = 85 minutes, where I'd rather have the calculation of 10:00 (the earliest start) to 10: 55 (the latest finish), for a total of 55 minutes. I can't get my head around the right logic to use to get to an answer like that. There may be hundreds of registered tourneys, so testing one against the rest to see if there is a matching time seems very wasteful and sloppy. Any suggestions on how I might proceed? Perhaps a one time query when I add the record into the database (that would make changing the record difficult) to compare the session time to other session times somehow? Thanks in advance Jake mail2web - Check your email from the web at http://mail2web.com/ . -- Experience is a good teacher, but she sends in terrific bills. Minna Antrim -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
