[PHP-DB] sql select
if i have a statement
$result = mysql_query("SELECT
labor_id,labor_ord_no,labor_wkyr,works_orders_part_no,works_orders_qty,works
_orders_customer_id,labor_qty,labor_time,labor_qty*parts_time+parts_setup AS
timea FROM labor,works_orders,parts WHERE labor_ord_no=works_orders_ord_no
AND works_orders_part_no=parts_no AND time2 = labor_wkyr ORDER BY
labor_wkyr,works_orders_ord_no",$link);
This works fine but if i add AND timea > labor_time it fails
Regards
Steve Dodkins
IMPORTANT NOTICE The information in this e-mail is confidential and should
only be read by those persons to whom it is addressed and is not intended to
be relied upon by any person without subsequent written confirmation of its
contents. ebm-ZIEHL (UK) Ltd. cannot accept any responsibility for the
accuracy or completeness of this message as it has been transmitted over a
public network. Furthermore, the content of this e-mail is the personal
view of the sender and does not represent the advice, views or opinion of
our company. Accordingly, our company disclaim all responsibility and accept
no liability (including in negligence) for the consequences of any person
acting, or refraining from acting, on such information prior to the receipt
by those persons of subsequent written confirmation. In particular (but not
by way of limitation) our company disclaims all responsibility and accepts
no liability for any e-mails which are defamatory, offensive, racist or in
any other way are in breach of any third party's rights, including breach of
confidence, privacy or other rights. If you have received this e-mail
message in error, please notify me immediately by telephone. Please also
destroy and delete the message from your computer. Any form of reproduction,
dissemination, copying, disclosure, modification, distribution and/or
publication of this e-mail message is strictly prohibited. If you have
received this E-mail in error, or suspect that the message may have been
intercepted or amended, please notify ebm-ZIEHL (UK) Ltd on +44(0)1245
468555.
ebm-ZIEHL (UK) Ltd Chelmsford Business Park, Chelmsford, Essex CM2 5EZ
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] SQL Select?
Hello all
Is there any way of using a string in an SQL query instead of using the
table name. Something along the lines of
$result=mysql_query("SELECT * FROM $tablename",$db);
this doesnt work it come up with a parse error
thankyou
Grant
--
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 select
I don't think you can reference the aliased column timea in the select
clause, I know sql server won't let you do it..try and
(labor_qty*parts_time+parts_setup)>labor_time
-Original Message-
From: Steve Dodkins [mailto:Steve.Dodkins@;ebm-ziehl.co.uk]
Sent: Tuesday, November 12, 2002 10:16 AM
To: Php-Db (E-mail)
Subject: [PHP-DB] sql select
if i have a statement
$result = mysql_query("SELECT
labor_id,labor_ord_no,labor_wkyr,works_orders_part_no,works_orders_qty,works
_orders_customer_id,labor_qty,labor_time,labor_qty*parts_time+parts_setup AS
timea FROM labor,works_orders,parts WHERE labor_ord_no=works_orders_ord_no
AND works_orders_part_no=parts_no AND time2 = labor_wkyr ORDER BY
labor_wkyr,works_orders_ord_no",$link);
This works fine but if i add AND timea > labor_time it fails
Regards
Steve Dodkins
IMPORTANT NOTICE The information in this e-mail is confidential and should
only be read by those persons to whom it is addressed and is not intended to
be relied upon by any person without subsequent written confirmation of its
contents. ebm-ZIEHL (UK) Ltd. cannot accept any responsibility for the
accuracy or completeness of this message as it has been transmitted over a
public network. Furthermore, the content of this e-mail is the personal
view of the sender and does not represent the advice, views or opinion of
our company. Accordingly, our company disclaim all responsibility and accept
no liability (including in negligence) for the consequences of any person
acting, or refraining from acting, on such information prior to the receipt
by those persons of subsequent written confirmation. In particular (but not
by way of limitation) our company disclaims all responsibility and accepts
no liability for any e-mails which are defamatory, offensive, racist or in
any other way are in breach of any third party's rights, including breach of
confidence, privacy or other rights. If you have received this e-mail
message in error, please notify me immediately by telephone. Please also
destroy and delete the message from your computer. Any form of reproduction,
dissemination, copying, disclosure, modification, distribution and/or
publication of this e-mail message is strictly prohibited. If you have
received this E-mail in error, or suspect that the message may have been
intercepted or amended, please notify ebm-ZIEHL (UK) Ltd on +44(0)1245
468555.
ebm-ZIEHL (UK) Ltd Chelmsford Business Park, Chelmsford, Essex CM2 5EZ
--
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 select
Have you ran it by hand in MySQL from the command prompt with some test
data and gotten an error?
Adam
On Tue, 12 Nov 2002, Steve Dodkins wrote:
> if i have a statement
>
> $result = mysql_query("SELECT
> labor_id,labor_ord_no,labor_wkyr,works_orders_part_no,works_orders_qty,works
> _orders_customer_id,labor_qty,labor_time,labor_qty*parts_time+parts_setup AS
> timea FROM labor,works_orders,parts WHERE labor_ord_no=works_orders_ord_no
> AND works_orders_part_no=parts_no AND time2 = labor_wkyr ORDER BY
> labor_wkyr,works_orders_ord_no",$link);
>
> This works fine but if i add AND timea > labor_time it fails
>
>
>
> Regards
>
> Steve Dodkins
>
> IMPORTANT NOTICE The information in this e-mail is confidential and should
> only be read by those persons to whom it is addressed and is not intended to
> be relied upon by any person without subsequent written confirmation of its
> contents. ebm-ZIEHL (UK) Ltd. cannot accept any responsibility for the
> accuracy or completeness of this message as it has been transmitted over a
> public network. Furthermore, the content of this e-mail is the personal
> view of the sender and does not represent the advice, views or opinion of
> our company. Accordingly, our company disclaim all responsibility and accept
> no liability (including in negligence) for the consequences of any person
> acting, or refraining from acting, on such information prior to the receipt
> by those persons of subsequent written confirmation. In particular (but not
> by way of limitation) our company disclaims all responsibility and accepts
> no liability for any e-mails which are defamatory, offensive, racist or in
> any other way are in breach of any third party's rights, including breach of
> confidence, privacy or other rights. If you have received this e-mail
> message in error, please notify me immediately by telephone. Please also
> destroy and delete the message from your computer. Any form of reproduction,
> dissemination, copying, disclosure, modification, distribution and/or
> publication of this e-mail message is strictly prohibited. If you have
> received this E-mail in error, or suspect that the message may have been
> intercepted or amended, please notify ebm-ZIEHL (UK) Ltd on +44(0)1245
> 468555.
> ebm-ZIEHL (UK) Ltd Chelmsford Business Park, Chelmsford, Essex CM2 5EZ
>
>
>
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] sql select
Top man!!
Many thanks
Steve
-Original Message-
From: [EMAIL PROTECTED] [mailto:epeloke@;echoman.com]
Sent: 12 November 2002 16:02
To: Php-Db (E-mail)
Subject: RE: [PHP-DB] sql select
I don't think you can reference the aliased column timea in the select
clause, I know sql server won't let you do it..try and
(labor_qty*parts_time+parts_setup)>labor_time
-Original Message-
From: Steve Dodkins [mailto:Steve.Dodkins@;ebm-ziehl.co.uk]
Sent: Tuesday, November 12, 2002 10:16 AM
To: Php-Db (E-mail)
Subject: [PHP-DB] sql select
if i have a statement
$result = mysql_query("SELECT
labor_id,labor_ord_no,labor_wkyr,works_orders_part_no,works_orders_qty,works
_orders_customer_id,labor_qty,labor_time,labor_qty*parts_time+parts_setup AS
timea FROM labor,works_orders,parts WHERE labor_ord_no=works_orders_ord_no
AND works_orders_part_no=parts_no AND time2 = labor_wkyr ORDER BY
labor_wkyr,works_orders_ord_no",$link);
This works fine but if i add AND timea > labor_time it fails
Regards
Steve Dodkins
IMPORTANT NOTICE The information in this e-mail is confidential and should
only be read by those persons to whom it is addressed and is not intended to
be relied upon by any person without subsequent written confirmation of its
contents. ebm-ZIEHL (UK) Ltd. cannot accept any responsibility for the
accuracy or completeness of this message as it has been transmitted over a
public network. Furthermore, the content of this e-mail is the personal
view of the sender and does not represent the advice, views or opinion of
our company. Accordingly, our company disclaim all responsibility and accept
no liability (including in negligence) for the consequences of any person
acting, or refraining from acting, on such information prior to the receipt
by those persons of subsequent written confirmation. In particular (but not
by way of limitation) our company disclaims all responsibility and accepts
no liability for any e-mails which are defamatory, offensive, racist or in
any other way are in breach of any third party's rights, including breach of
confidence, privacy or other rights. If you have received this e-mail
message in error, please notify me immediately by telephone. Please also
destroy and delete the message from your computer. Any form of reproduction,
dissemination, copying, disclosure, modification, distribution and/or
publication of this e-mail message is strictly prohibited. If you have
received this E-mail in error, or suspect that the message may have been
intercepted or amended, please notify ebm-ZIEHL (UK) Ltd on +44(0)1245
468555.
ebm-ZIEHL (UK) Ltd Chelmsford Business Park, Chelmsford, Essex CM2 5EZ
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] SQL Select statement
I have a table with three simple columns: id(unique), memberID, and choice I want to find out which memberIDs have BOTH choice 2 AND choice 3. --> SELECT memberID from table where choice=2 AND choice = 3 That won't work (0 results, of course, because no row has two choices, they're mutually exclusive) ---> SELECT memberID from table where choice=2 OR choice = 3 That doesn't work (shows all memberIDs that chose either 2 or 3). Can I maybe join the table upon itself on the memberID? Other suggestions? thanks, bill hollett -- 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 Select?
Grant,
$link_id = mysql_connect($host, $usr, $pass) or die (mysql_error());
mysql_select_db($database, $link_id);
$sql="select fields from $table where criteria = $value";
$result = mysql_query($sql, $link_id) or die ("no results"); //return result
set to php
you still need to do something with $result.
also, if you use mysql_fetch_array instead of mysql_query, you get a
row-matched array.
Best regards,
Andrew
---
Andrew Hill - OpenLink Software
Director Technology Evangelism
Virtuoso, eBusinss iNtegration
http://www.openlinksw.com/virtuoso
On 3/24/01 3:55 PM, "Grant" <[EMAIL PROTECTED]> wrote:
> Hello all
>
> Is there any way of using a string in an SQL query instead of using the
> table name. Something along the lines of
>
> $result=mysql_query("SELECT * FROM $tablename",$db);
>
> this doesnt work it come up with a parse error
>
> thankyou
>
> Grant
>
>
--
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 Select?
In article <99jh2n$gr1$[EMAIL PROTECTED]>,
[EMAIL PROTECTED] ("Grant") wrote:
> Is there any way of using a string in an SQL query instead of using the
> table name. Something along the lines of
>
> $result=mysql_query("SELECT * FROM $tablename",$db);
>
> this doesnt work it come up with a parse error
"SELECT * FROM $tablename" should be sufficient to permit PHP variable
expansion. MySQL will also require a string value to be quoted, so if
$tablename is a string, change the query to "SELECT * FROM '$tablename'".
AFAIK, the missing inner quotes would not cause a PHP parse error, only a
MySQL error. So you may want to look for something else in your code that
could be causing the parse error.
--
CC
--
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 Select?
On Sat, Mar 24, 2001 at 08:55:13PM -, Grant wrote:
> Is there any way of using a string in an SQL query instead of using the
> table name. Something along the lines of
>
> $result=mysql_query("SELECT * FROM $tablename",$db);
>
> this doesnt work it come up with a parse error
Your problem is probably on a line above this one. Because this line
should be valid. I do stuff like this all the time and it works
without any problems so far.
".mysql_error($db));
?>
This allows you to see the sql that is actually being executed, and you
also get to see the actual mysql error.
--
Jason Stechschulte
[EMAIL PROTECTED]
--
It is, of course, written in Perl. Translation to C is left as an
exercise for the reader. :-) -- Larry Wall in <[EMAIL PROTECTED]>
--
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]
AW: [PHP-DB] SQL Select statement
try something like: select a.memberID from table a, table b where a.choice=2 and b.choice=3 and a.memberID = b.memberID; or select memberID, count(*) from table where choice=2 or choice=3 group by memberID having count(*) = 2; (i don't know what is more effizient, might depend on your indexes and the amount. the second sql asumes you only have one row per memberID - choice (noone should choose 3 2times ore more) mk -Ursprungliche Nachricht- Von: bill [mailto:[EMAIL PROTECTED]] Gesendet: Samstag, 17. Februar 2001 02:45 An: [EMAIL PROTECTED] Betreff: [PHP-DB] SQL Select statement I have a table with three simple columns: id(unique), memberID, and choice I want to find out which memberIDs have BOTH choice 2 AND choice 3. --> SELECT memberID from table where choice=2 AND choice = 3 That won't work (0 results, of course, because no row has two choices, they're mutually exclusive) ---> SELECT memberID from table where choice=2 OR choice = 3 That doesn't work (shows all memberIDs that chose either 2 or 3). Can I maybe join the table upon itself on the memberID? Other suggestions? thanks, bill hollett -- 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] -- 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 Select statement
Try this way : "select t1.memberID,t1.choice,t2.memberID, t2.choice from table as t1, table as t2 where t1.choice = 2 and t2.choice =3" It works, but you'll going to have as many records as the bigger number of records between choice=2 and choice = 3, no matter you use the distinct keyword or not. I think you can give your problem another approach, splitting it in two queries and joining the result sets through php script. Sometimes you can't put SQL to do all the work. HTH Jayme. -Mensagem Original- De: bill <[EMAIL PROTECTED]> Para: <[EMAIL PROTECTED]> Enviada em: sexta-feira, 16 de fevereiro de 2001 22:44 Assunto: [PHP-DB] SQL Select statement > I have a table with three simple columns: id(unique), memberID, and > choice > > > I want to find out which memberIDs have BOTH choice 2 AND choice 3. > > --> SELECT memberID from table where choice=2 AND choice = 3 > > That won't work (0 results, of course, because no row has two choices, > they're mutually exclusive) > > ---> SELECT memberID from table where choice=2 OR choice = 3 > > That doesn't work (shows all memberIDs that chose either 2 or 3). > > Can I maybe join the table upon itself on the memberID? > > Other suggestions? > > thanks, > > bill hollett > > > -- > 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] > -- 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]
AW: [PHP-DB] SQL Select statement
>think you can give your problem another approach, splitting it in two >queries and joining the result sets through php script. >Sometimes you can't put SQL to do all the work. That's true, but in most cases it is more efficient to leave the joins, orders etc. in the DB, because its more optimzed for such dumb processes ... mk -- 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 Select statement
Addressed to: "JJeffman" <[EMAIL PROTECTED]> "PHPDB" <[EMAIL PROTECTED]> "bill" <[EMAIL PROTECTED]> ** Reply to note from "JJeffman" <[EMAIL PROTECTED]> Sat, 17 Feb 2001 13:11:20 -0300 > > Try this way : > > "select t1.memberID,t1.choice,t2.memberID, t2.choice from table as t1, table > as t2 where t1.choice = 2 and t2.choice =3" > > It works, but you'll going to have as many records as the bigger number of > records between choice=2 and choice = 3, no matter you use the distinct > keyword or not. I > think you can give your problem another approach, splitting it in two > queries and joining the result sets through php script. > > Sometimes you can't put SQL to do all the work. Not all that often though... :) SELECT t1.memberID FROM Table AS t1, Table AS t2 WHERE t1.MemberID = t2.MemberID AND t1.Choice = 2 AND t2.Choice = 3; Unless you have more than one entry for a member of choice 2 or choice 3 this will give you a list of memberIDs which have both choice 2 and choice 3 records. If you find you have duplicate memberIDs, you can add a DISTINCT clause to the select. > > I have a table with three simple columns: id(unique), memberID, and > > choice > > > > > > I want to find out which memberIDs have BOTH choice 2 AND choice 3. Rick Widmer Internet Marketing Specialists http://www.developersdesk.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]
Re: [PHP-DB] SQL Select statement
> I want to find out which memberIDs have BOTH choice 2 AND choice 3. > > --> SELECT memberID from table where choice=2 AND choice = 3 > > That won't work (0 results, of course, because no row has two choices, > they're mutually exclusive) > Exactly, keep several rows for choices, have two tables members choices --- choiceid id memberid namechoice whatever for instance if somebody (member 21) chooses 2 and 3 there must be in the choices table choiceid memberid choice 1 21 2 2 21 3 then your sql statement will work. (* you might still add another field in case for instance there are several votaciones...) Lennin Arriola [EMAIL PROTECTED] -- 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]
