Re: [PHP-DB] SQL Query - Using variable from another SQL Query
Thanks Everyone... After I sent that...I got thinking about doing both queries in one statement. So thats what I did. Its working fine... Here is the updated code: '$tstamp' and egw_cal.cal_id=egw_cal_dates.cal_id", $db); if ($event = mysql_fetch_array($events)) { echo "\n"; echo "\n"; do { echo "$event[cal_title]- $event[cal_location]\n"; echo "\n"; $start = date('F jS\, Y \a\t g:ia', $event[cal_start]); echo "Starting Date/Time: $start"; echo "\n"; echo "\n"; echo "$event[cal_description]"; echo "\n"; echo "\n"; } while ($event = mysql_fetch_array($events)); } else { echo "No Public Events Are Currently Scheduled..."; } ?> ----- Original Message - From: "Matthew Ferry" <[EMAIL PROTECTED]> To: Sent: Monday, February 12, 2007 11:14 AM Subject: [PHP-DB] SQL Query - Using variable from another SQL Query Hello Everyone Got a simple / stupid question. Worked on this all night. I'm over looking something very basic here. The query "event_time" brings back the calendar id for each event that is pending in the future. ie 12, 13, 14, 26 (There could be 100 of them out there) The second query "events" needs to meet both reqirements. 1 - cal_category='501' 2 - cal_id= a number from the "event_time" query I think i need to do a loop inside of a loop Thanks... Matt Here is my code: $tstamp", $db); $events = mysql_query("SELECT * FROM egw_cal WHERE cal_category='501' and cal_id='$event_time'\n", $db); if ($event = mysql_fetch_array($events)) { echo "\n"; echo "\n"; do { echo "$event[cal_title]- $event[cal_location]\n"; echo "\n"; echo "$event[cal_description]"; echo "\n"; echo "\n"; } while ($event = mysql_fetch_array($events)); } else { echo "No Public Events Are Currently Scheduled..."; } ?>
Re: [PHP-DB] SQL Query - Using variable from another SQL Query
This is a join - Read up on them, they're very useful and don't require the overhead of a sub-query. SELECT egw_cal.* FROM egw_cal_dates LEFT JOIN egw_cal using (cal_id) where egw_cal_dates.cal_start > $tstamp AND egw_cal.cal_category = '501' -Micah On 02/12/2007 08:14 AM, Matthew Ferry wrote: Hello Everyone Got a simple / stupid question. Worked on this all night. I'm over looking something very basic here. The query "event_time" brings back the calendar id for each event that is pending in the future. ie 12, 13, 14, 26 (There could be 100 of them out there) The second query "events" needs to meet both reqirements. 1 - cal_category='501' 2 - cal_id= a number from the "event_time" query I think i need to do a loop inside of a loop Thanks... Matt Here is my code: $tstamp", $db); $events = mysql_query("SELECT * FROM egw_cal WHERE cal_category='501' and cal_id='$event_time'\n", $db); if ($event = mysql_fetch_array($events)) { echo "\n"; echo "\n"; do { echo "$event[cal_title]- $event[cal_location]\n"; echo "\n"; echo "$event[cal_description]"; echo "\n"; echo "\n"; } while ($event = mysql_fetch_array($events)); } else { echo "No Public Events Are Currently Scheduled..."; } ?> -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Query - Using variable from another SQL Query
Try this as your SQL. It should give you all the results, then you can use PHP to sort it all out. SELECT * FROM egw_cal WHERE cal_category='501' and cal_id in (SELECT cal_id FROM egw_cal_dates where cal_start > $tstamp) -TG = = = Original message = = = Hello Everyone Got a simple / stupid question. Worked on this all night. I'm over looking something very basic here. The query "event_time" brings back the calendar id for each event that is pending in the future. ie 12, 13, 14, 26 (There could be 100 of them out there) The second query "events" needs to meet both reqirements. 1 - cal_category='501' 2 - cal_id= a number from the "event_time" query I think i need to do a loop inside of a loop Thanks... Matt Here is my code: $tstamp", $db); $events = mysql_query("SELECT * FROM egw_cal WHERE cal_category='501' and cal_id='$event_time'\n", $db); if ($event = mysql_fetch_array($events)) echo "\n"; echo "\n"; do echo "$event[cal_title]- $event[cal_location]\n"; echo "\n"; echo "$event[cal_description]"; echo "\n"; echo "\n"; while ($event = mysql_fetch_array($events)); else echo "No Public Events Are Currently Scheduled..."; ?> ___ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Query - Using variable from another SQL Query
Matthew Ferry wrote: Hello Everyone Got a simple / stupid question. Worked on this all night. I'm over looking something very basic here. The query "event_time" brings back the calendar id for each event that is pending in the future. ie 12, 13, 14, 26 (There could be 100 of them out there) The second query "events" needs to meet both reqirements. 1 - cal_category='501' 2 - cal_id= a number from the "event_time" query I think i need to do a loop inside of a loop Thanks... Matt Here is my code: $tstamp", $db); This returns a mysql result set...not the actual data... search php.net for the function mysql_fetch_array or others to actually *get* the data. (Some good examples there will help you sort this out!) $events = mysql_query("SELECT * FROM egw_cal WHERE cal_category='501' and cal_id='$event_time'\n", $db); if ($event = mysql_fetch_array($events)) { echo "\n"; echo "\n"; do { echo "$event[cal_title]- $event[cal_location]\n"; echo "\n"; echo "$event[cal_description]"; echo "\n"; echo "\n"; } while ($event = mysql_fetch_array($events)); } else { echo "No Public Events Are Currently Scheduled..."; } ?> -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] SQL Query - Using variable from another SQL Query
Hello Everyone Got a simple / stupid question. Worked on this all night. I'm over looking something very basic here. The query "event_time" brings back the calendar id for each event that is pending in the future. ie 12, 13, 14, 26 (There could be 100 of them out there) The second query "events" needs to meet both reqirements. 1 - cal_category='501' 2 - cal_id= a number from the "event_time" query I think i need to do a loop inside of a loop Thanks... Matt Here is my code: $tstamp", $db); $events = mysql_query("SELECT * FROM egw_cal WHERE cal_category='501' and cal_id='$event_time'\n", $db); if ($event = mysql_fetch_array($events)) { echo "\n"; echo "\n"; do { echo "$event[cal_title]- $event[cal_location]\n"; echo "\n"; echo "$event[cal_description]"; echo "\n"; echo "\n"; } while ($event = mysql_fetch_array($events)); } else { echo "No Public Events Are Currently Scheduled..."; } ?>
Re: [PHP-DB] SQL query error
Chris Carter wrote: What wrong with this syntax, its not giving any error on runtime but I am facing a blank page while paging. $query=" SELECT * FROM gurgaonmalls WHERE mallname = '$mallname' limit $eu, $limit "; Have you tried... echo " $query "; ...to unsure the variables have the values you expect them to have? Jeffrey -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] SQL query error
What wrong with this syntax, its not giving any error on runtime but I am facing a blank page while paging. $query=" SELECT * FROM gurgaonmalls WHERE mallname = '$mallname' limit $eu, $limit "; -- View this message in context: http://www.nabble.com/SQL-query-error-tf2831052.html#a7903857 Sent from the Php - Database mailing list archive at Nabble.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] SQL query
OK, this makes my day clear!! I have versión 3.23.49-3 of MySQL Thanks Dwight! -Original Message- From: Dwight Altman [mailto:[EMAIL PROTECTED] Sent: Jueves, 28 de Septiembre de 2006 11:32 a.m. To: php-db@lists.php.net Subject: RE: [PHP-DB] SQL query Check your version. Subselects were only added in MySQL Version 4.1. Regards, Dwight > -Original Message- > From: Edwin Cruz [mailto:[EMAIL PROTECTED] > Sent: Thursday, September 28, 2006 10:53 AM > To: 'Miguel Guirao'; php-db@lists.php.net > Subject: RE: [PHP-DB] SQL query > > Make sure that your second query is returning only one row, if it dont > help, try this: > $query="select email from usuarios where userName in (select username > from fussv where folio = 'FUSS-130-2006')" > > > MySQL think that you second query returns more than 1 row, that's why > mysql dont accept your query, is like trying to compare vs more than one > scalar value > > Regards! > > ++ > | Ing Edwin Cruz <[EMAIL PROTECTED]> | ++ > | Transportes Medel Rogero SA de CV | | | > | Desk: +52 (449) 910 30 90 x3054 | ++ > | MX Mobile: +52 (449) 111 29 03 | > | Aguascalientes, Mexico | > | http://www.medel.com.mx | > ++ > > > > > -Mensaje original- > > De: Miguel Guirao [mailto:[EMAIL PROTECTED] > > Enviado el: Jueves, 28 de Septiembre de 2006 09:09 a.m. > > Para: php-db@lists.php.net > > Asunto: [PHP-DB] SQL query > > > > > > > > > > Hello list, > > > > Whats wrong with my SQL query: > > > > $query="select email from usuarios where userName = (select > > username from fussv where folio = 'FUSS-130-2006')"; > > > > I get an error! > > I have tested the two individual sentences and they worked OK! > > > > --- > > Miguel Guirao Aguilera > > Logistica R8 TELCEL > > Tel. (999) 960.7994 > > > > > > Este mensaje es exclusivamente para el uso de la persona o > > entidad a quien esta dirigido; contiene informacion > > estrictamente confidencial y legalmente protegida, cuya > > divulgacion es sancionada por la ley. Si el lector de este > > mensaje no es a quien esta dirigido, ni se trata del empleado > > o agente responsable de esta informacion, se le notifica por > > medio del presente, que su reproduccion y distribucion, esta > > estrictamente prohibida. Si Usted recibio este comunicado por > > error, favor de notificarlo inmediatamente al remitente y > > destruir el mensaje. Todas las opiniones contenidas en este > > mail son propias del autor del mensaje y no necesariamente > > coinciden con las de Radiomovil Dipsa, S.A. de C.V. o alguna > > de sus empresas controladas, controladoras, afiliadas y > > subsidiarias. Este mensaje intencionalmente no contiene acentos. > > > > This message is for the sole use of the person or entity to > > whom it is being sent. Therefore, it contains strictly > > confidential and legally protected material whose disclosure > > is subject to penalty by law. If the person reading this > > message is not the one to whom it is being sent and/or is not > > an employee or the responsible agent for this information, > > this person is herein notified that any unauthorized > > dissemination, distribution or copying of the materials > > included in this facsimile is strictly prohibited. If you > > received this document by mistake please notify immediately > > to the subscriber and destroy the message. Any opinions > > contained in this e-mail are those of the author of the > > message and do not necessarily coincide with those of > > Radiomovil Dipsa, S.A. de C.V. or any of its control, > > controlled, affiliates and subsidiaries companies. No part of > > this message or attachments may be used or reproduced in any > > manner whatsoever. > > > > -- > > 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
RE: [PHP-DB] SQL query
Check your version. Subselects were only added in MySQL Version 4.1. Regards, Dwight > -Original Message- > From: Edwin Cruz [mailto:[EMAIL PROTECTED] > Sent: Thursday, September 28, 2006 10:53 AM > To: 'Miguel Guirao'; php-db@lists.php.net > Subject: RE: [PHP-DB] SQL query > > Make sure that your second query is returning only one row, if it dont > help, try this: > $query="select email from usuarios where userName in (select username > from fussv where folio = 'FUSS-130-2006')" > > > MySQL think that you second query returns more than 1 row, that's why > mysql dont accept your query, is like trying to compare vs more than one > scalar value > > Regards! > > ++ > | Ing Edwin Cruz <[EMAIL PROTECTED]> | ++ > | Transportes Medel Rogero SA de CV | | | > | Desk: +52 (449) 910 30 90 x3054 | ++ > | MX Mobile: +52 (449) 111 29 03 | > | Aguascalientes, Mexico | > | http://www.medel.com.mx | > ++ > > > > > -Mensaje original- > > De: Miguel Guirao [mailto:[EMAIL PROTECTED] > > Enviado el: Jueves, 28 de Septiembre de 2006 09:09 a.m. > > Para: php-db@lists.php.net > > Asunto: [PHP-DB] SQL query > > > > > > > > > > Hello list, > > > > Whats wrong with my SQL query: > > > > $query="select email from usuarios where userName = (select > > username from fussv where folio = 'FUSS-130-2006')"; > > > > I get an error! > > I have tested the two individual sentences and they worked OK! > > > > --- > > Miguel Guirao Aguilera > > Logistica R8 TELCEL > > Tel. (999) 960.7994 > > > > > > Este mensaje es exclusivamente para el uso de la persona o > > entidad a quien esta dirigido; contiene informacion > > estrictamente confidencial y legalmente protegida, cuya > > divulgacion es sancionada por la ley. Si el lector de este > > mensaje no es a quien esta dirigido, ni se trata del empleado > > o agente responsable de esta informacion, se le notifica por > > medio del presente, que su reproduccion y distribucion, esta > > estrictamente prohibida. Si Usted recibio este comunicado por > > error, favor de notificarlo inmediatamente al remitente y > > destruir el mensaje. Todas las opiniones contenidas en este > > mail son propias del autor del mensaje y no necesariamente > > coinciden con las de Radiomovil Dipsa, S.A. de C.V. o alguna > > de sus empresas controladas, controladoras, afiliadas y > > subsidiarias. Este mensaje intencionalmente no contiene acentos. > > > > This message is for the sole use of the person or entity to > > whom it is being sent. Therefore, it contains strictly > > confidential and legally protected material whose disclosure > > is subject to penalty by law. If the person reading this > > message is not the one to whom it is being sent and/or is not > > an employee or the responsible agent for this information, > > this person is herein notified that any unauthorized > > dissemination, distribution or copying of the materials > > included in this facsimile is strictly prohibited. If you > > received this document by mistake please notify immediately > > to the subscriber and destroy the message. Any opinions > > contained in this e-mail are those of the author of the > > message and do not necessarily coincide with those of > > Radiomovil Dipsa, S.A. de C.V. or any of its control, > > controlled, affiliates and subsidiaries companies. No part of > > this message or attachments may be used or reproduced in any > > manner whatsoever. > > > > -- > > 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 query
Make sure that your second query is returning only one row, if it dont help, try this: $query="select email from usuarios where userName in (select username from fussv where folio = 'FUSS-130-2006')" MySQL think that you second query returns more than 1 row, that's why mysql dont accept your query, is like trying to compare vs more than one scalar value Regards! ++ | Ing Edwin Cruz <[EMAIL PROTECTED]> | ++ | Transportes Medel Rogero SA de CV | | | | Desk: +52 (449) 910 30 90 x3054 | ++ | MX Mobile: +52 (449) 111 29 03 | | Aguascalientes, Mexico | | http://www.medel.com.mx | ++ > -Mensaje original- > De: Miguel Guirao [mailto:[EMAIL PROTECTED] > Enviado el: Jueves, 28 de Septiembre de 2006 09:09 a.m. > Para: php-db@lists.php.net > Asunto: [PHP-DB] SQL query > > > > > Hello list, > > Whats wrong with my SQL query: > > $query="select email from usuarios where userName = (select > username from fussv where folio = 'FUSS-130-2006')"; > > I get an error! > I have tested the two individual sentences and they worked OK! > > --- > Miguel Guirao Aguilera > Logistica R8 TELCEL > Tel. (999) 960.7994 > > > Este mensaje es exclusivamente para el uso de la persona o > entidad a quien esta dirigido; contiene informacion > estrictamente confidencial y legalmente protegida, cuya > divulgacion es sancionada por la ley. Si el lector de este > mensaje no es a quien esta dirigido, ni se trata del empleado > o agente responsable de esta informacion, se le notifica por > medio del presente, que su reproduccion y distribucion, esta > estrictamente prohibida. Si Usted recibio este comunicado por > error, favor de notificarlo inmediatamente al remitente y > destruir el mensaje. Todas las opiniones contenidas en este > mail son propias del autor del mensaje y no necesariamente > coinciden con las de Radiomovil Dipsa, S.A. de C.V. o alguna > de sus empresas controladas, controladoras, afiliadas y > subsidiarias. Este mensaje intencionalmente no contiene acentos. > > This message is for the sole use of the person or entity to > whom it is being sent. Therefore, it contains strictly > confidential and legally protected material whose disclosure > is subject to penalty by law. If the person reading this > message is not the one to whom it is being sent and/or is not > an employee or the responsible agent for this information, > this person is herein notified that any unauthorized > dissemination, distribution or copying of the materials > included in this facsimile is strictly prohibited. If you > received this document by mistake please notify immediately > to the subscriber and destroy the message. Any opinions > contained in this e-mail are those of the author of the > message and do not necessarily coincide with those of > Radiomovil Dipsa, S.A. de C.V. or any of its control, > controlled, affiliates and subsidiaries companies. No part of > this message or attachments may be used or reproduced in any > manner whatsoever. > > -- > 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] SQL query
Hello list, Whats wrong with my SQL query: $query="select email from usuarios where userName = (select username from fussv where folio = 'FUSS-130-2006')"; I get an error! I have tested the two individual sentences and they worked OK! --- Miguel Guirao Aguilera Logistica R8 TELCEL Tel. (999) 960.7994 Este mensaje es exclusivamente para el uso de la persona o entidad a quien esta dirigido; contiene informacion estrictamente confidencial y legalmente protegida, cuya divulgacion es sancionada por la ley. Si el lector de este mensaje no es a quien esta dirigido, ni se trata del empleado o agente responsable de esta informacion, se le notifica por medio del presente, que su reproduccion y distribucion, esta estrictamente prohibida. Si Usted recibio este comunicado por error, favor de notificarlo inmediatamente al remitente y destruir el mensaje. Todas las opiniones contenidas en este mail son propias del autor del mensaje y no necesariamente coinciden con las de Radiomovil Dipsa, S.A. de C.V. o alguna de sus empresas controladas, controladoras, afiliadas y subsidiarias. Este mensaje intencionalmente no contiene acentos. This message is for the sole use of the person or entity to whom it is being sent. Therefore, it contains strictly confidential and legally protected material whose disclosure is subject to penalty by law. If the person reading this message is not the one to whom it is being sent and/or is not an employee or the responsible agent for this information, this person is herein notified that any unauthorized dissemination, distribution or copying of the materials included in this facsimile is strictly prohibited. If you received this document by mistake please notify immediately to the subscriber and destroy the message. Any opinions contained in this e-mail are those of the author of the message and do not necessarily coincide with those of Radiomovil Dipsa, S.A. de C.V. or any of its control, controlled, affiliates and subsidiaries companies. No part of this message or attachments may be used or reproduced in any manner whatsoever. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL query...
From: "Muhammed Mamedov" <[EMAIL PROTECTED]> > Try this > > "SELECT DISTINCT(file_name), Count(file_name) AS cnt FROM $table_name WHERE > date > > BETWEEN '2003-10-01' AND '2003-12-31' group by file_name order by cnt; > > desc" If you're GROUPing by "file_name" then you don't need DISTINCT(file_name)... it's redundant. ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL query...
Try this "SELECT DISTINCT(file_name), Count(file_name) AS cnt FROM $table_name WHERE date > BETWEEN '2003-10-01' AND '2003-12-31' group by file_name order by cnt; > desc" Regards, Muhammed Mamedov tmchat.com - Original Message - From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, January 15, 2004 1:01 PM Subject: [PHP-DB] SQL query... > "SELECT DISTINCT(file_name), Count(file_name) FROM $table_name WHERE date > BETWEEN '2003-10-01' AND '2003-12-31' group by file_name order by ??? > desc" > > In the above sql statement, I'm trying to achieve: > > 1. select all file names, between two dates. > 2. list them, and order by the highest number of occurences of count() > > Basically, it's for a download tool we have, and my boss wants to easily > be able to see the top downloaded files. > It all works, but not the 'order by' bit... what do I have to order by... > it's not 'file_name', and 'order by count(file_name0' causes an error... > > thoughts? > > Cheers, > Tris... > > * > The information contained in this e-mail message is intended only for > the personal and confidential use of the recipient(s) named above. > If the reader of this message is not the intended recipient or an agent > responsible for delivering it to the intended recipient, you are hereby > notified that you have received this document in error and that any > review, dissemination, distribution, or copying of this message is > strictly prohibited. If you have received this communication in error, > please notify us immediately by e-mail, and delete the original message. > *** > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL query...
use alias for 'Count(file_name)' to use in order by clause F.x. "SELECT DISTINCT(file_name), Count(file_name) as file_count FROM $table_name WHERE date BETWEEN '2003-10-01' AND '2003-12-31' group by file_name order by file_count desc" Hope that solves it Nitin - Original Message - From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, January 15, 2004 4:31 PM Subject: [PHP-DB] SQL query... > "SELECT DISTINCT(file_name), Count(file_name) FROM $table_name WHERE date > BETWEEN '2003-10-01' AND '2003-12-31' group by file_name order by ??? > desc" > > In the above sql statement, I'm trying to achieve: > > 1. select all file names, between two dates. > 2. list them, and order by the highest number of occurences of count() > > Basically, it's for a download tool we have, and my boss wants to easily > be able to see the top downloaded files. > It all works, but not the 'order by' bit... what do I have to order by... > it's not 'file_name', and 'order by count(file_name0' causes an error... > > thoughts? > > Cheers, > Tris... > > * > The information contained in this e-mail message is intended only for > the personal and confidential use of the recipient(s) named above. > If the reader of this message is not the intended recipient or an agent > responsible for delivering it to the intended recipient, you are hereby > notified that you have received this document in error and that any > review, dissemination, distribution, or copying of this message is > strictly prohibited. If you have received this communication in error, > please notify us immediately by e-mail, and delete the original message. > *** > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] SQL query...
Ah... you can name the count().. live and learn, cheers dude... "brett king" <[EMAIL PROTECTED]> 15/01/2004 11:17 Please respond to <[EMAIL PROTECTED]> To <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> cc Subject RE: [PHP-DB] SQL query... "SELECT DISTINCT(file_name), Count(file_name) FROM $table_name WHERE date BETWEEN '2003-10-01' AND '2003-12-31' group by file_name order by ??? desc" In the above sql statement, I'm trying to achieve: 1. select all file names, between two dates. 2. list them, and order by the highest number of occurences of count() Basically, it's for a download tool we have, and my boss wants to easily be able to see the top downloaded files. It all works, but not the 'order by' bit... what do I have to order by... it's not 'file_name', and 'order by count(file_name0' causes an error... thoughts? Cheers, Tris... - can you not do this? "SELECT DISTINCT(file_name), Count(file_name)fcount FROM $table_name WHERE date BETWEEN '2003-10-01' AND '2003-12-31' group by file_name order by fcount desc" Please note that I have named count(file_name) fcount in the sql statement Hope this helps? Brett -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php * The information contained in this e-mail message is intended only for the personal and confidential use of the recipient(s) named above. If the reader of this message is not the intended recipient or an agent responsible for delivering it to the intended recipient, you are hereby notified that you have received this document in error and that any review, dissemination, distribution, or copying of this message is strictly prohibited. If you have received this communication in error, please notify us immediately by e-mail, and delete the original message. ***
RE: [PHP-DB] SQL query...
"SELECT DISTINCT(file_name), Count(file_name) FROM $table_name WHERE date BETWEEN '2003-10-01' AND '2003-12-31' group by file_name order by ??? desc" In the above sql statement, I'm trying to achieve: 1. select all file names, between two dates. 2. list them, and order by the highest number of occurences of count() Basically, it's for a download tool we have, and my boss wants to easily be able to see the top downloaded files. It all works, but not the 'order by' bit... what do I have to order by... it's not 'file_name', and 'order by count(file_name0' causes an error... thoughts? Cheers, Tris... - can you not do this? "SELECT DISTINCT(file_name), Count(file_name)fcount FROM $table_name WHERE date BETWEEN '2003-10-01' AND '2003-12-31' group by file_name order by fcount desc" Please note that I have named count(file_name) fcount in the sql statement Hope this helps? Brett -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] SQL query...
"SELECT DISTINCT(file_name), Count(file_name) FROM $table_name WHERE date BETWEEN '2003-10-01' AND '2003-12-31' group by file_name order by ??? desc" In the above sql statement, I'm trying to achieve: 1. select all file names, between two dates. 2. list them, and order by the highest number of occurences of count() Basically, it's for a download tool we have, and my boss wants to easily be able to see the top downloaded files. It all works, but not the 'order by' bit... what do I have to order by... it's not 'file_name', and 'order by count(file_name0' causes an error... thoughts? Cheers, Tris... * The information contained in this e-mail message is intended only for the personal and confidential use of the recipient(s) named above. If the reader of this message is not the intended recipient or an agent responsible for delivering it to the intended recipient, you are hereby notified that you have received this document in error and that any review, dissemination, distribution, or copying of this message is strictly prohibited. If you have received this communication in error, please notify us immediately by e-mail, and delete the original message. ***
RE: [PHP-DB] sql query, editing?
Create a form for editing the record Then on the display funtion just put a link on each record to that form and pass the id of that record like edit On the edit form just grab the data of the $id passed on the url and put those values on the input fields like Than just save the form result into the database with an UPDATE tablename SET filed1='$field1' ... WHERE id='$id'); ... Dont' forget the bit of code here are just examples and very insecure ... Need to work on the validation etc... Humberto Silva World Editing Portugal -Original Message- From: Louie Miranda [mailto:[EMAIL PROTECTED] Sent: quinta-feira, 15 de Janeiro de 2004 8:00 To: [EMAIL PROTECTED] Subject: [PHP-DB] sql query, editing? I have this code below, it fetches data on a mysql database. I was hoping you could give me a code hint on where could, my goal is to display this data on a browser which i did already and be able to edit it via a form. edit? -> table1=value -> table2=value I dont know where to start. please help me, i hope i can display all the data and have a button for editing and catch which one to edit. ## code ## $result = mysql_query("select product_code,title,language,issue,category,cost from iip_t_cp where issue = $issue and category = '$category' and language = '$language' and depleted = '$depleted'", $connect); $num_rows = mysql_num_rows($result); function display($result) { echo "pricelist records\n"; echo ""; echo "\n\n\n" . "\nproduct codetitlelanguageissuecategory< th>c ost" . "\n"; while ($row = @ mysql_fetch_row($result)) { echo "\n"; foreach($row as $data) echo "\n\t $data "; echo "\n"; } echo "\n"; } display($result); ## code ## -- - Louie Miranda http://www.axishift.com -- 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] sql query, editing?
I have this code below, it fetches data on a mysql database. I was hoping you could give me a code hint on where could, my goal is to display this data on a browser which i did already and be able to edit it via a form. edit? -> table1=value -> table2=value I dont know where to start. please help me, i hope i can display all the data and have a button for editing and catch which one to edit. ## code ## $result = mysql_query("select product_code,title,language,issue,category,cost from iip_t_cp where issue = $issue and category = '$category' and language = '$language' and depleted = '$depleted'", $connect); $num_rows = mysql_num_rows($result); function display($result) { echo "pricelist records\n"; echo ""; echo "\n\n\n" . "\nproduct codetitlelanguageissuecategoryc ost" . "\n"; while ($row = @ mysql_fetch_row($result)) { echo "\n"; foreach($row as $data) echo "\n\t $data "; echo "\n"; } echo "\n"; } display($result); ## code ## -- - Louie Miranda http://www.axishift.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] sql query
why does this select date(date_file_created) from files create the error error near near '(date_file_created) from files' at line 1 trying to extract the date part of the date_tile filed tia pete -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] sql query links
> i'm starting out in this whole thing, and i'm trying to program a zine > that > uses a sql database for content and calls the content into a template. > what > i am unclear on how to do is set up the links so that I can get the info > from the database. anyone willing to help me out? www.youdomain.com/your_file.php?content_identifier=somevalue ---John W. Holmes... PHP Architect - A monthly magazine for PHP Professionals. Get your copy today. http://www.phparch.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] sql query links
i'm starting out in this whole thing, and i'm trying to program a zine that uses a sql database for content and calls the content into a template. what i am unclear on how to do is set up the links so that I can get the info from the database. anyone willing to help me out? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] SQL Query
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY Adam On Sat, 7 Sep 2002, Bryan McLemore wrote: > Hi Guys I have written this SQL Query : > > CREATE TABLE tasks (id INT AUTO_INCREMENT, name VARCHAR(50), desc TEXT, address >VARCHAR(50), startDate DATE, lastWork DATE, progress INT(3)) > > I'm sending it in through php and I can't get it to work correctly. Please help. >Also, I would like to make id the primary key/index > > -Bryan > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] SQL Query
Hi Guys I have written this SQL Query : CREATE TABLE tasks (id INT AUTO_INCREMENT, name VARCHAR(50), desc TEXT, address VARCHAR(50), startDate DATE, lastWork DATE, progress INT(3)) I'm sending it in through php and I can't get it to work correctly. Please help. Also, I would like to make id the primary key/index -Bryan
RE: [PHP-DB] SQL query prob
It would unless you told it not to. Set a flag $lastContactType=''; Then on the first page you display something where then display it and then if ($lastContactType != $row['contactType']){ echo $row['contactType']; $lastContactType=$row['contactType']; } // if ($lastContactType != $row['contactType']) When contactType changes again, you'll display it again and reset the flag again. HTH, =C= * * Cal Evans * The Virtual CIO * http://www.calevans.com * -Original Message- From: Russ [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 17, 2002 8:44 PM To: PHP DB Mailing List (E-mail) Subject: [PHP-DB] SQL query prob I'll try that one again I have a query: $sql = "SELECT * FROM contacts ORDER BY ContactType"; There are two types of contacts: * Commissioners * Staff As they are ordered by the ContactType then 'Commissioners' are displayed first followed by 'Staff'. I'd like to be able to display a heading on the page at the point of the FIRST instance of 'Commissioners' and at the FIRST instance of 'Staff'. As I'm using a while loop in my PHP script then at present such a heading would be displayed atop *each* item would it not? I think I need to use some kind of COUNT() but am unsure how to deploy it. Can anyone help me out? Cheers. Russ Mr Russ Michell Web Applications Developer Itomic.com Email: [EMAIL PROTECTED] Tel: +61 (0)8 9321 3844 Fax: +61 (0)8 6210 1364 Post: PO Box 228, Innaloo, WA 6918, Australia Street: Suite 24, 158 William St, Perth, WA 6000, Australia "No proof of existence is not proof of non-existence." (Physicist: Stanton T. Friedman on Debunking Ufology) -- 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] SQL query prob
I'll try that one again I have a query: $sql = "SELECT * FROM contacts ORDER BY ContactType"; There are two types of contacts: * Commissioners * Staff As they are ordered by the ContactType then 'Commissioners' are displayed first followed by 'Staff'. I'd like to be able to display a heading on the page at the point of the FIRST instance of 'Commissioners' and at the FIRST instance of 'Staff'. As I'm using a while loop in my PHP script then at present such a heading would be displayed atop *each* item would it not? I think I need to use some kind of COUNT() but am unsure how to deploy it. Can anyone help me out? Cheers. Russ Mr Russ Michell Web Applications Developer Itomic.com Email: [EMAIL PROTECTED] Tel: +61 (0)8 9321 3844 Fax: +61 (0)8 6210 1364 Post: PO Box 228, Innaloo, WA 6918, Australia Street: Suite 24, 158 William St, Perth, WA 6000, Australia "No proof of existence is not proof of non-existence." (Physicist: Stanton T. Friedman on Debunking Ufology) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] sql query problem
Hello Chip, Could you please send your program to dig more.And also send your database schema. Do you want to select title,name from your_table_name where title='title1' and name='blue2'; Is the title and name field are unique.If it so,then the above query will helps you. Regards, SenthilVelavan.P - Original Message - From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, June 15, 2002 2:39 AM Subject: [PHP-DB] sql query problem > I have a database layout similar to this- > > id order title namepagecat > > 0 0 title1 blueap > 1 2 blue1 page1 ap > 2 3 blue2 page2 ap > 3 1 blue3 page3 ap > > I would like to get title1 (title column only) and blue2 (name column > only) like this- > title1 blue2 > > I am finding it difficult to write the proper select statement to get just > those items in > a web page using php/mysql. > > Suggestions? > -- > Chip Wiegand > Computer Services > Simrad, Inc > www.simradusa.com > [EMAIL PROTECTED] > > "There is no reason anyone would want a computer in their home." > --Ken Olson, president, chairman and founder of Digital Equipment > Corporation, 1977 > (They why do I have 9? Somebody help me!) > > -- > 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 query problem
The problem is they are parts of different rows. In a relational database rows only know about other rows through relationships. You have either: 1. Designed your database incorrectly. or 2. Are a genius with a unique concept that I do not grasp. <>< Ryan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, June 14, 2002 3:10 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] sql query problem I have a database layout similar to this- id order title namepagecat 0 0 title1 blueap 1 2 blue1 page1 ap 2 3 blue2 page2 ap 3 1 blue3 page3 ap I would like to get title1 (title column only) and blue2 (name column only) like this- title1 blue2 I am finding it difficult to write the proper select statement to get just those items in a web page using php/mysql. Suggestions? -- Chip Wiegand Computer Services Simrad, Inc www.simradusa.com [EMAIL PROTECTED] "There is no reason anyone would want a computer in their home." --Ken Olson, president, chairman and founder of Digital Equipment Corporation, 1977 (They why do I have 9? Somebody help me!) -- 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] sql query problem
I have a database layout similar to this- id order title namepagecat 0 0 title1 blueap 1 2 blue1 page1 ap 2 3 blue2 page2 ap 3 1 blue3 page3 ap I would like to get title1 (title column only) and blue2 (name column only) like this- title1 blue2 I am finding it difficult to write the proper select statement to get just those items in a web page using php/mysql. Suggestions? -- Chip Wiegand Computer Services Simrad, Inc www.simradusa.com [EMAIL PROTECTED] "There is no reason anyone would want a computer in their home." --Ken Olson, president, chairman and founder of Digital Equipment Corporation, 1977 (They why do I have 9? Somebody help me!) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] sql query
Thanks I will try that. "Beau Lebens" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > try replacing > > WHERE display=$custcatergory"); > > with > > WHERE display='$custcatergory'"); > > and also tru echoing the value of that SQL statement (assign it to a var > first) to make sure you are getting the right thing. > > HTH > > /b > > // -Original Message- > // From: CrossWalkCentral [mailto:[EMAIL PROTECTED]] > // Sent: Tuesday, 19 February 2002 9:31 AM > // To: [EMAIL PROTECTED] > // Subject: [PHP-DB] sql query > // > // > // I am having a minor problem doing a simple sql query. > // > // Error performing service query : You have an error in your > // SQL syntax near '' at line 1 > // > // Here is a snip of the code: Please let me know what you think. > // > // if ($submit): > // > // $status="0"; > // > // // REQUEST INFO FOR SERVICES > // $results = mysql_query( > // "SELECT * FROM supportsyscat WHERE display=$custcatergory"); > // if (!$results) { > // echo("Error performing service query : " . > // mysql_error() . ""); > // exit(); > // } > // > // // Display the text > // while ( $rows = mysql_fetch_array($results) ) { > // $dservice=$rows["display"]; > // $eservice=$rows["address"]; > // } > // > // > // $sql = "UPDATE supportsys SET " . > //"pdes='$custpdes', " . > //"sdes='$adminsdes', " . > //"status='$status' " . > //"WHERE ticket=$ticketnum"; > // > // if (mysql_query($sql)) { > // Echo("Ticket #$ticketnum has been > // updated."); > // Echo("Thank you $custfname $custlname"); > // Echo("Problem: $custpdes"); > // > // > // if ($statusclose =="true") { > // $status="Closed"; > // } else { > // $status ="Open"; > // } > // > // -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] sql query
try replacing WHERE display=$custcatergory"); with WHERE display='$custcatergory'"); and also tru echoing the value of that SQL statement (assign it to a var first) to make sure you are getting the right thing. HTH /b // -Original Message- // From: CrossWalkCentral [mailto:[EMAIL PROTECTED]] // Sent: Tuesday, 19 February 2002 9:31 AM // To: [EMAIL PROTECTED] // Subject: [PHP-DB] sql query // // // I am having a minor problem doing a simple sql query. // // Error performing service query : You have an error in your // SQL syntax near '' at line 1 // // Here is a snip of the code: Please let me know what you think. // // if ($submit): // // $status="0"; // // // REQUEST INFO FOR SERVICES // $results = mysql_query( // "SELECT * FROM supportsyscat WHERE display=$custcatergory"); // if (!$results) { // echo("Error performing service query : " . // mysql_error() . ""); // exit(); // } // // // Display the text // while ( $rows = mysql_fetch_array($results) ) { // $dservice=$rows["display"]; // $eservice=$rows["address"]; // } // // // $sql = "UPDATE supportsys SET " . //"pdes='$custpdes', " . //"sdes='$adminsdes', " . //"status='$status' " . //"WHERE ticket=$ticketnum"; // // if (mysql_query($sql)) { // Echo("Ticket #$ticketnum has been // updated."); // Echo("Thank you $custfname $custlname"); // Echo("Problem: $custpdes"); // // // if ($statusclose =="true") { // $status="Closed"; // } else { // $status ="Open"; // } // // -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] sql query
I am having a minor problem doing a simple sql query. Error performing service query : You have an error in your SQL syntax near '' at line 1 Here is a snip of the code: Please let me know what you think. if ($submit): $status="0"; // REQUEST INFO FOR SERVICES $results = mysql_query( "SELECT * FROM supportsyscat WHERE display=$custcatergory"); if (!$results) { echo("Error performing service query : " . mysql_error() . ""); exit(); } // Display the text while ( $rows = mysql_fetch_array($results) ) { $dservice=$rows["display"]; $eservice=$rows["address"]; } $sql = "UPDATE supportsys SET " . "pdes='$custpdes', " . "sdes='$adminsdes', " . "status='$status' " . "WHERE ticket=$ticketnum"; if (mysql_query($sql)) { Echo("Ticket #$ticketnum has been updated."); Echo("Thank you $custfname $custlname"); Echo("Problem: $custpdes"); if ($statusclose =="true") { $status="Closed"; } else { $status ="Open"; }
RE: [PHP-DB] SQL query
> Thanks but I think subselect is not possible with MYSQL. You're right. Here's a MySQL compatible alternative: SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id where table2.id IS NULL From the MySQL manual on sub-selects at: http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html -Original Message- From: Pierre Sent: Mon 11/12/2001 12:20 AM To: Gonzalez, Lorenzo; [EMAIL PROTECTED] Cc: Subject: Re: [PHP-DB] SQL query Thanks but I think subselect is not possible with MYSQL. Pierre - Original Message - From: Gonzalez, Lorenzo <[EMAIL PROTECTED]> To: Pierre <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Monday, November 12, 2001 1:08 PM Subject: RE: [PHP-DB] SQL query > in other RDBMs this is easily done with a subselect - don't know if it's > doable in MySQL or not, someone else can confirm, or you can try it > yourself... > > select * from table1 where table1.id not in (select table2.id); > > -Lorenzo > > -Original Message- > From: Pierre > Sent: Sun 11/11/2001 10:01 PM > To: [EMAIL PROTECTED] > Cc: > Subject: [PHP-DB] SQL query > > > > I have two tables, one call category, the other chapter > > Category table > [id][name] > [1][a] > [2][b] > [3][c] > [4][d] > > Chapter table > [id][FK_name] > [1][a] > [2][a] > [3][c] > > > I would like to get the list of names of the Category table that > are NOT present in the Chapter table. > On this example, I should find "b" and "d". > > Is it possible to get it directly from a SQL query ? (I am using > MYSQL). > > Thank for helping me. > > Pierre > > >
Re: [PHP-DB] SQL query
Thanks but I think subselect is not possible with MYSQL. Pierre - Original Message - From: Gonzalez, Lorenzo <[EMAIL PROTECTED]> To: Pierre <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Monday, November 12, 2001 1:08 PM Subject: RE: [PHP-DB] SQL query > in other RDBMs this is easily done with a subselect - don't know if it's > doable in MySQL or not, someone else can confirm, or you can try it > yourself... > > select * from table1 where table1.id not in (select table2.id); > > -Lorenzo > > -Original Message- > From: Pierre > Sent: Sun 11/11/2001 10:01 PM > To: [EMAIL PROTECTED] > Cc: > Subject: [PHP-DB] SQL query > > > > I have two tables, one call category, the other chapter > > Category table > [id][name] > [1][a] > [2][b] > [3][c] > [4][d] > > Chapter table > [id][FK_name] > [1][a] > [2][a] > [3][c] > > > I would like to get the list of names of the Category table that > are NOT present in the Chapter table. > On this example, I should find "b" and "d". > > Is it possible to get it directly from a SQL query ? (I am using > MYSQL). > > Thank for helping me. > > Pierre > > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] SQL query
in other RDBMs this is easily done with a subselect - don't know if it's doable in MySQL or not, someone else can confirm, or you can try it yourself... select * from table1 where table1.id not in (select table2.id); -Lorenzo -Original Message- From: Pierre Sent: Sun 11/11/2001 10:01 PM To: [EMAIL PROTECTED] Cc: Subject: [PHP-DB] SQL query I have two tables, one call category, the other chapter Category table [id][name] [1][a] [2][b] [3][c] [4][d] Chapter table [id][FK_name] [1][a] [2][a] [3][c] I would like to get the list of names of the Category table that are NOT present in the Chapter table. On this example, I should find "b" and "d". Is it possible to get it directly from a SQL query ? (I am using MYSQL). Thank for helping me. Pierre
[PHP-DB] SQL query
I have two tables, one call category, the other chapter Category table [id][name] [1][a] [2][b] [3][c] [4][d] Chapter table [id][FK_name] [1][a] [2][a] [3][c] I would like to get the list of names of the Category table that are NOT present in the Chapter table. On this example, I should find "b" and "d". Is it possible to get it directly from a SQL query ? (I am using MYSQL). Thank for helping me. Pierre
[PHP-DB] SQL query (OT)
hi: I have 2 tables, lesson and lesson_gle, where lesson holds lesson data and lesson_gle holds repeating data that are how the lesson correlates to various state standards (one lesson can have many correlations)...one particular lesson may have a math correlation as well as a language arts correlation. >From a couple of popups, I am passing numerical values for $level and $subject which pertain to a certain level (grade level) and subject (the correlations). I am decoding them using an array which I use to construct the query you see below. The query is working fine, HOWEVER, it only looks at the first correlation of a lesson...so if I ask for math, all of the lessons that have math as a second correlation are not returned, and I would like to be able to show ALL lessons that have a "MA" correlation. SELECT * FROM lesson, lesson_gle WHERE lesson.l_id = lesson_gle.l_id $cluster[$level] GROUP BY lesson.l_id HAVING lesson_gle.glenum LIKE '$subjaray[$subject]%' ORDER BY lesson.l_id $cluster = array (" ", "AND (lesson.grdlevel = '1' OR lesson.grdlevel = '2')", "AND (lesson.grdlevel = '3' OR lesson.grdlevel = '4' OR lesson.grdlevel = '5')", "AND (lesson.grdlevel = '6' OR lesson.grdlevel = '7' OR lesson.grdlevel = '8')", "AND (lesson.grdlevel = '9' OR lesson.grdlevel = '10' OR lesson.grdlevel = '11' or lesson.grdlevel = '12')"); $subjaray = array ("", "LA", "MA", "SS", "SC", "AT", "VA", "PE", "TH", "MU", "HE", "DA", "FL"); Thanks Luis __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]