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
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
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?
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]
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?
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 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]
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
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]