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:
?php
$todays_year = date(Y);
$todays_month = date(m);
$todays_day = date(d);
$tstamp = mktime(0, 0, 0, $todays_month, $todays_day, $todays_year);
$event_time = mysql_query(SELECT cal_id FROM egw_cal_dates where cal_start
$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 center\n;
echo HR\n;
do {
echo BFont
Size='10'$event[cal_title]nbsp;nbsp;nbsp;nbsp;-nbsp;nbsp;nbsp;$event[cal_location]/B/Font\n;
echo BR\n;
echo $event[cal_description];
echo BR\n;
echo HR\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: ?php $todays_year = date(Y); $todays_month = date(m); $todays_day = date(d); $tstamp = mktime(0, 0, 0, $todays_month, $todays_day, $todays_year); $event_time = mysql_query(SELECT cal_id FROM egw_cal_dates where cal_start $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 center\n; echo HR\n; do echo BFont Size='10'$event[cal_title]nbsp;nbsp;nbsp;nbsp;-nbsp;nbsp;nbsp;$event[cal_location]/B/Font\n; echo BR\n; echo $event[cal_description]; echo BR\n; echo HR\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
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:
?php
$todays_year = date(Y);
$todays_month = date(m);
$todays_day = date(d);
$tstamp = mktime(0, 0, 0, $todays_month, $todays_day, $todays_year);
$event_time = mysql_query(SELECT cal_id FROM egw_cal_dates where cal_start
$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 center\n;
echo HR\n;
do {
echo BFont
Size='10'$event[cal_title]nbsp;nbsp;nbsp;nbsp;-nbsp;nbsp;nbsp;$event[cal_location]/B/Font\n;
echo BR\n;
echo $event[cal_description];
echo BR\n;
echo HR\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
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:
?php
$todays_year = date(Y);
$todays_month = date(m);
$todays_day = date(d);
$tstamp = mktime(0, 0, 0, $todays_month, $todays_day, $todays_year);
$events = mysql_query(SELECT DISTINCT * FROM egw_cal, egw_cal_dates WHERE
egw_cal.cal_category='501'
and egw_cal_dates.cal_start '$tstamp' and
egw_cal.cal_id=egw_cal_dates.cal_id, $db);
if ($event = mysql_fetch_array($events)) {
echo center\n;
echo HR\n;
do {
echo BFont
Face='Times'$event[cal_title]nbsp;nbsp;nbsp;nbsp;-nbsp;nbsp;nbsp;$event[cal_location]/Font/B\n;
echo BR\n;
$start = date('F jS\, Y \a\t g:ia', $event[cal_start]);
echo Starting Date/Time:nbsp;nbsp; $start;
echo BR\n;
echo BR\n;
echo $event[cal_description];
echo BR\n;
echo HR\n;
} while ($event = mysql_fetch_array($events));
} else {
echo No Public Events Are Currently Scheduled...;
}
?
- Original Message -
From: Matthew Ferry [EMAIL PROTECTED]
To: php-db@lists.php.net
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:
?php
$todays_year = date(Y);
$todays_month = date(m);
$todays_day = date(d);
$tstamp = mktime(0, 0, 0, $todays_month, $todays_day, $todays_year);
$event_time = mysql_query(SELECT cal_id FROM egw_cal_dates where cal_start
$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 center\n;
echo HR\n;
do {
echo BFont
Size='10'$event[cal_title]nbsp;nbsp;nbsp;nbsp;-nbsp;nbsp;nbsp;$event[cal_location]/B/Font\n;
echo BR\n;
echo $event[cal_description];
echo BR\n;
echo HR\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 p $query /p; ...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
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
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
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, 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 a
href=editrecord.php?id=?=$row['id']?edit/a
On the edit form just grab the data of the $id passed on the url and put
those values on the input fields like input type=text name=field1
value=?=$row['field1']?
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 h1pricelist records/h1\n;
echo br;
echo \ntable cellspacing=3 cellpadding=3 border=1\ntr\n .
\nthproduct
code/ththtitle/ththlanguage/ththissue/ththcategory/th
thc
ost/th .
\n/tr;
while ($row = @ mysql_fetch_row($result))
{
echo \ntr;
foreach($row as $data)
echo \n\ttd $data /td;
echo \n/tr;
}
echo \n/table;
}
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
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
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...
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...
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...
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
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
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
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
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(PError performing service query : .
// mysql_error() . /P);
// 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(centerPh4Ticket #$ticketnum has been
// updated./h4/P/center);
// Echo(Thank you $custfname $custlname);
// Echo(pProblem: $custpdes/P);
//
//
// 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
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(PError performing service query : .
// mysql_error() . /P);
// 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(centerPh4Ticket #$ticketnum has been
// updated./h4/P/center);
// Echo(Thank you $custfname $custlname);
// Echo(pProblem: $custpdes/P);
//
//
// 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
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
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]
