[PHP-DB] Re: help w/Multiple Joins
hi, you have to create aliases for every column you want to fetch results from. you have to change your query like this: SELECT i1.img_path AS i1_path, i2.img_path AS i2_path, i3. you can leave out the AS if you want to.. hth, bastian $result = mysql_query(SELECT * FROM photo_album as p INNER JOIN images AS i1 ON p.img_1=i1.img_id INNER JOIN images AS i2 ON p.img_2=i2.img_id INNER JOIN images AS i3 ON p.img_3=i3.img_id INNER JOIN images AS i4 ON p.img_4=i4.img_id,$db)or die(mysql_error()); -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] right join
Hi How do I get the following select to exclude records where labor_qty from labor does not have a record? $result = mysql_query(SELECT labor_ord_no,works_orders_ord_no,works_orders_part_no,works_orders_qty,labor _qty FROM labor RIGHT JOIN works_orders ON labor_ord_no = works_orders_ord_no ORDER BY labor_qty,$link); 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] Re: right join
hi, did you try ...FROM labor RIGHT JOIN works_orders ON labor_ord_no = works_orders_ord_no WHERE labor_qty='' ORDER BY labor_qty,$link); ? i think that should do it regards, bastian How do I get the following select to exclude records where labor_qty from labor does not have a record? $result = mysql_query(SELECT labor_ord_no,works_orders_ord_no,works_orders_part_no,works_orders_qty,labor _qty FROM labor RIGHT JOIN works_orders ON labor_ord_no = works_orders_ord_no ORDER BY labor_qty,$link); -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Select Last row
Does 'select * from table order by id desc limit 1' do what you want? HTH Rich -Original Message- From: Afif [mailto:[EMAIL PROTECTED]] Sent: 21 November 2002 01:49 To: PHP DB Subject: [PHP-DB] Select Last row dear All, I have data and I want to get the last row of my table. how to select the last row, with the id is timestamp? and the the result will display in the prompt? sorry if my question is sound stupid, but I really confuse how to start this code -- Warm regards, Afif mailto:[EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Help with Date
Is there a way to convert a date 20/11/2002 to a Float number in PHP, since the date starts in 30/12/1899, just like the delphi treats the dates Thanks in Advance... Equipe Pratic Sistemas Rodrigo CorrĂȘa Fone: (14) 441-1700 [EMAIL PROTECTED] [EMAIL PROTECTED]
Re: [PHP-DB] Help with Date
Rodrigo, Is there a way to convert a date 20/11/2002 to a Float number in PHP, since the date starts in 30/12/1899, just like the delphi treats the dates =Check out UNIX timestamps (but watch the valid date range), =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] how to clear the array
If you have a variable: $myArray = array(); Can't you clear it by saying unset($myArray)? According to the documentation, it looks like you should be able to. -Original Message- From: Dave Smith [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 21, 2002 1:12 AM To: karthikeyan; [EMAIL PROTECTED] Subject: Re: [PHP-DB] how to clear the array How about: $my_array = array(); // clears the whole array (and makes a new one) Or: $my_array[$i] = null; // clears a single entry in the array at index $i --Dave karthikeyan wrote: How to clear an array? i used unset. But it destroys the array property of the variablke itself. I just need to clear the contents. Pls tell me how THakns Karthikeyan.S - Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now -- 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] how to clear the array
Sorry. Didn't read original post. Try this: $myArray = array('1','2','3'); $numElements = count($myArray); for($i=0;$i=$numElements;$i++){ unset($myArray[$i]); } If the code above doesn't work, check the link below. This all SOUNDS easy enough to accomplish (theoretically), but I've never actually done it before, so I'm not versed in the ins and outs of unsetting elements of an array. http://www.php.net/manual/en/function.unset.php -Original Message- From: Hutchins, Richard [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 21, 2002 8:30 AM To: [EMAIL PROTECTED] Subject: RE: [PHP-DB] how to clear the array If you have a variable: $myArray = array(); Can't you clear it by saying unset($myArray)? According to the documentation, it looks like you should be able to. -Original Message- From: Dave Smith [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 21, 2002 1:12 AM To: karthikeyan; [EMAIL PROTECTED] Subject: Re: [PHP-DB] how to clear the array How about: $my_array = array(); // clears the whole array (and makes a new one) Or: $my_array[$i] = null; // clears a single entry in the array at index $i --Dave karthikeyan wrote: How to clear an array? i used unset. But it destroys the array property of the variablke itself. I just need to clear the contents. Pls tell me how THakns Karthikeyan.S - Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now -- 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] IMAP Body to MySQL
I'm trying to write the body of a plain text email to a MySQL database. However, nothing gets inserted into the body field of the table when the following lines are executed. I've echoed the $body string back to the screen and nothing seems amiss. All the other fields are inserted correctly. I've tried changing the field type of the body column within MySQL, but that hasn't worked either. Right now, the body field is a TEXT column. $body = imap_body($inbox, $x); mysql_query (INSERT INTO inbox (subject, fromaddress, toaddress, ccaddress, date, size, unread, body) VALUES ('$subject', '$fromaddress', '$toaddress', '$ccaddress', '$date', '$size', '$unread', '$body')); Any help you could provide would be greatly appreciated. Thanks, Brian -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Get Last ID Inserted
Using Microsoft SQL does anyone know how to get the id of the row that you just inserted without clumsily trying to select the id back based on the same criteria of your insert (which might be overlapping)? -- Adam Voigt ([EMAIL PROTECTED]) The Cryptocomm Group My GPG Key: http://64.238.252.49:8080/adam_at_cryptocomm.asc signature.asc Description: This is a digitally signed message part
[PHP-DB] inserting html into mysql tables
hi, is there anything special i need to know in regards to creating a table to hold html data? I am building a CMS for a site using php and mysql. The various tables hold course information that the client wants to edit. I imagine it will be a case of dropping the cleaned html source from dreamweaver into a form box and posting it to the table. What would a good varchar(?) be for say 5K of data? cheers mikek -- Mike Karthauser Managing Director - Brightstorm Ltd Email[EMAIL PROTECTED] Web http://www.brightstorm.co.uk Tel 0117 9426653 (office) 07939 252144 (mobile) SnailmailUnit 8, 14 King Square, Bristol BS2 8JJ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] inserting html into mysql tables
Well for one, VARCHAR is limited to 255 characters, so I would use a TEXT type datafield if you want to store more then 255. On Thu, 2002-11-21 at 11:01, mike karthauser wrote: hi, is there anything special i need to know in regards to creating a table to hold html data? I am building a CMS for a site using php and mysql. The various tables hold course information that the client wants to edit. I imagine it will be a case of dropping the cleaned html source from dreamweaver into a form box and posting it to the table. What would a good varchar(?) be for say 5K of data? cheers mikek -- Mike Karthauser Managing Director - Brightstorm Ltd Email[EMAIL PROTECTED] Web http://www.brightstorm.co.uk Tel 0117 9426653 (office) 07939 252144 (mobile) SnailmailUnit 8, 14 King Square, Bristol BS2 8JJ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Adam Voigt ([EMAIL PROTECTED]) The Cryptocomm Group My GPG Key: http://64.238.252.49:8080/adam_at_cryptocomm.asc signature.asc Description: This is a digitally signed message part
[PHP-DB] Re: Get Last ID Inserted
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello Adam On 21 November 2002 at 10:47:15 -0500 (which was 15:47 where I live) Adam Voigt wrote Using Microsoft SQL does anyone know how to get the id of the row that you just inserted without clumsily trying to select the id back based on the same criteria of your insert (which might be overlapping)? select @@identity - -- Cheers, ___ David |David Elliott| Software Engineer| _| [EMAIL PROTECTED] | PGP Key ID 0x650F4534 | | No dinner with Mel Gibson?! - Dot Warner| -BEGIN PGP SIGNATURE- Version: 6.5.8ckt http://www.ipgpp.com/ iQA/AwUBPd0DwfmK8eZlD0U0EQI3fACgsv52o5AvhuroJIVYblYXTnkiDZYAn2Ao y1AeA+bR4KPOwZhZTAa2x7kr =f/lr -END PGP SIGNATURE- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Get Last ID Inserted
But if there are heavy operations on the site, will this not also pick up a different last inserted id, if in the split milisecond between the insert and the next mssql_query which has the @@identity say, another user does an insert? On Thu, 2002-11-21 at 11:03, David Elliott wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello Adam On 21 November 2002 at 10:47:15 -0500 (which was 15:47 where I live) Adam Voigt wrote Using Microsoft SQL does anyone know how to get the id of the row that you just inserted without clumsily trying to select the id back based on the same criteria of your insert (which might be overlapping)? select @@identity - -- Cheers, ___ David |David Elliott| Software Engineer| _| [EMAIL PROTECTED] | PGP Key ID 0x650F4534 | | No dinner with Mel Gibson?! - Dot Warner| -BEGIN PGP SIGNATURE- Version: 6.5.8ckt http://www.ipgpp.com/ iQA/AwUBPd0DwfmK8eZlD0U0EQI3fACgsv52o5AvhuroJIVYblYXTnkiDZYAn2Ao y1AeA+bR4KPOwZhZTAa2x7kr =f/lr -END PGP SIGNATURE- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Adam Voigt ([EMAIL PROTECTED]) The Cryptocomm Group My GPG Key: http://64.238.252.49:8080/adam_at_cryptocomm.asc signature.asc Description: This is a digitally signed message part
RE: [PHP-DB] Re: Get Last ID Inserted
I do see that picking up the incorrect ID, which is why we created a stored procedure which returned the affected row. -Original Message- From: Adam Voigt [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 21, 2002 11:13 AM To: David Elliott Cc: Adam Voigt on PHP-DB Subject: Re: [PHP-DB] Re: Get Last ID Inserted But if there are heavy operations on the site, will this not also pick up a different last inserted id, if in the split milisecond between the insert and the next mssql_query which has the @@identity say, another user does an insert? On Thu, 2002-11-21 at 11:03, David Elliott wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello Adam On 21 November 2002 at 10:47:15 -0500 (which was 15:47 where I live) Adam Voigt wrote Using Microsoft SQL does anyone know how to get the id of the row that you just inserted without clumsily trying to select the id back based on the same criteria of your insert (which might be overlapping)? select @@identity - -- Cheers, ___ David |David Elliott| Software Engineer | _| [EMAIL PROTECTED] | PGP Key ID 0x650F4534 | | No dinner with Mel Gibson?! - Dot Warner | -BEGIN PGP SIGNATURE- Version: 6.5.8ckt http://www.ipgpp.com/ iQA/AwUBPd0DwfmK8eZlD0U0EQI3fACgsv52o5AvhuroJIVYblYXTnkiDZYAn2Ao y1AeA+bR4KPOwZhZTAa2x7kr =f/lr -END PGP SIGNATURE- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Adam Voigt ([EMAIL PROTECTED]) The Cryptocomm Group My GPG Key: http://64.238.252.49:8080/adam_at_cryptocomm.asc -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Get Last ID Inserted
Please don't ban more for this example using VB, but this is how I did it a while ago in VB/ASP, generating and setting the RecID in one stroke. I assume you could just wrap this in PHP and it would work just as well. strSQL=Select newid() RecID = objConn.Execute(strSQL)(0).value Ryan Marrs wrote: I do see that picking up the incorrect ID, which is why we created a stored procedure which returned the affected row. -Original Message- From: Adam Voigt [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 21, 2002 11:13 AM To: David Elliott Cc: Adam Voigt on PHP-DB Subject: Re: [PHP-DB] Re: Get Last ID Inserted But if there are heavy operations on the site, will this not also pick up a different last inserted id, if in the split milisecond between the insert and the next mssql_query which has the @@identity say, another user does an insert? On Thu, 2002-11-21 at 11:03, David Elliott wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello Adam On 21 November 2002 at 10:47:15 -0500 (which was 15:47 where I live) Adam Voigt wrote Using Microsoft SQL does anyone know how to get the id of the row that you just inserted without clumsily trying to select the id back based on the same criteria of your insert (which might be overlapping)? select @@identity - -- Cheers, ___ David |David Elliott| Software Engineer | _| [EMAIL PROTECTED] | PGP Key ID 0x650F4534 | | No dinner with Mel Gibson?! - Dot Warner | -BEGIN PGP SIGNATURE- Version: 6.5.8ckt http://www.ipgpp.com/ iQA/AwUBPd0DwfmK8eZlD0U0EQI3fACgsv52o5AvhuroJIVYblYXTnkiDZYAn2Ao y1AeA+bR4KPOwZhZTAa2x7kr =f/lr -END PGP SIGNATURE- -- 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] MySQL Problem
Hi, I have a problem with an MySQL statement that is driving me mad, I'm using php to connect to the database. this is it : - SELECT LINE,SUM(IF(MONTH=2,WB.TEU,0)) AS '2' ,SUM(IF(MONTH=3,WB.TEU,0)) AS '3' ,SUM(IF(MONTH=4,WB.TEU,0)) AS '4' ,SUM(IF(MONTH=5,WB.TEU,0)) AS '5' ,SUM(IF(MONTH=6,WB.TEU,0)) AS '6' ,SUM(IF(MONTH BETWEEN 2 AND 6,WB.TEU,0)) AS '2 TO 6' FROM WB WHERE EAST_PORT='A' AND WEST_PORT='B' GROUP BY LINE what I want to produce with this is this : - LINE2 3 4 5 6 2 TO 6 A 133511181260139512766384 B 432 469 451 480 555 2387 C 504 554 575 517 764 2914 however what I get is this : - LINE2 3 4 5 6 2 TO 6 A 111812601395127663846384 B 469 451 480 555 23872387 C 554 575 517 764 29142914 the last column of figures (2 TO 6) has been replicated under 6 and pushed the values that were in 6 into 5 and so on! I can not see for the life of me why this should or could be happening, especially as the equivalent code in MS Access : - SELECT LINE, Sum(IIf(MONTH=2,TEU,0)) AS ['2'], Sum(IIf(MONTH=3,TEU,0)) AS ['3'], Sum(IIf(MONTH=4,TEU,0)) AS ['4'], Sum(IIf(MONTH=5,TEU,0)) AS ['5'], Sum(IIf(MONTH=6,TEU,0)) AS ['6'], SUM(IIF(MONTH BETWEEN 2 AND 6,TEU,0)) AS 'FEB TO JUNE' , SUM(IIF(MONTH BETWEEN 1 AND 6,TEU,0)) AS 'YTD' FROM DUMP WHERE (((DUMP.[EAST PORT])='HONG KONG') AND ((DUMP.[WEST PORT])='UK')) GROUP BY DUMP.LINE; gives the correct results. If anyone has any idea of what is going on here please let me know, I'm hoping its something simple I'm missing.. Thanks Dan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MySQL Problem
Maybe I'm missing something - but the MySQL statement looks right ... How are you displaying the results? -Original Message- From: Griffiths, Daniel [mailto:[EMAIL PROTECTED]] Sent: 21 November 2002 16:37 To: PHP List Subject: [PHP-DB] MySQL Problem Hi, I have a problem with an MySQL statement that is driving me mad, I'm using php to connect to the database. this is it : - SELECT LINE,SUM(IF(MONTH=2,WB.TEU,0)) AS '2' ,SUM(IF(MONTH=3,WB.TEU,0)) AS '3' ,SUM(IF(MONTH=4,WB.TEU,0)) AS '4' ,SUM(IF(MONTH=5,WB.TEU,0)) AS '5' ,SUM(IF(MONTH=6,WB.TEU,0)) AS '6' ,SUM(IF(MONTH BETWEEN 2 AND 6,WB.TEU,0)) AS '2 TO 6' FROM WB WHERE EAST_PORT='A' AND WEST_PORT='B' GROUP BY LINE what I want to produce with this is this : - LINE2 3 4 5 6 2 TO 6 A 133511181260139512766384 B 432 469 451 480 555 2387 C 504 554 575 517 764 2914 however what I get is this : - LINE2 3 4 5 6 2 TO 6 A 111812601395127663846384 B 469 451 480 555 23872387 C 554 575 517 764 29142914 the last column of figures (2 TO 6) has been replicated under 6 and pushed the values that were in 6 into 5 and so on! I can not see for the life of me why this should or could be happening, especially as the equivalent code in MS Access : - SELECT LINE, Sum(IIf(MONTH=2,TEU,0)) AS ['2'], Sum(IIf(MONTH=3,TEU,0)) AS ['3'], Sum(IIf(MONTH=4,TEU,0)) AS ['4'], Sum(IIf(MONTH=5,TEU,0)) AS ['5'], Sum(IIf(MONTH=6,TEU,0)) AS ['6'], SUM(IIF(MONTH BETWEEN 2 AND 6,TEU,0)) AS 'FEB TO JUNE' , SUM(IIF(MONTH BETWEEN 1 AND 6,TEU,0)) AS 'YTD' FROM DUMP WHERE (((DUMP.[EAST PORT])='HONG KONG') AND ((DUMP.[WEST PORT])='UK')) GROUP BY DUMP.LINE; gives the correct results. If anyone has any idea of what is going on here please let me know, I'm hoping its something simple I'm missing.. Thanks Dan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php _ This message has been checked for all known viruses by the MessageLabs Virus Control Centre. This message has been checked for all known viruses by the MessageLabs Virus Control Centre. * Notice: This email is confidential and may contain copyright material of Ocado Limited (the Company). Opinions and views expressed in this message may not necessarily reflect the opinions and views of the Company. If you are not the intended recipient, please notify us immediately and delete all copies of this message. Please note that it is your responsibility to scan this message for viruses. Company reg. no. 3875000. Swallowdale Lane, Hemel Hempstead HP2 7PY * -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] inserting html into mysql tables
You're also, at the very least, going to want to escape out the characters in the HTML code that could cause problems with your query (e.g. , '). To do this, you'd use the addslashes() function prior to inserting the data into the database. -Original Message- From: Adam Voigt [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 21, 2002 11:05 AM To: mike karthauser Cc: [EMAIL PROTECTED] Subject: Re: [PHP-DB] inserting html into mysql tables Well for one, VARCHAR is limited to 255 characters, so I would use a TEXT type datafield if you want to store more then 255. On Thu, 2002-11-21 at 11:01, mike karthauser wrote: hi, is there anything special i need to know in regards to creating a table to hold html data? I am building a CMS for a site using php and mysql. The various tables hold course information that the client wants to edit. I imagine it will be a case of dropping the cleaned html source from dreamweaver into a form box and posting it to the table. What would a good varchar(?) be for say 5K of data? cheers mikek -- Mike Karthauser Managing Director - Brightstorm Ltd Email[EMAIL PROTECTED] Web http://www.brightstorm.co.uk Tel 0117 9426653 (office) 07939 252144 (mobile) SnailmailUnit 8, 14 King Square, Bristol BS2 8JJ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Adam Voigt ([EMAIL PROTECTED]) The Cryptocomm Group My GPG Key: http://64.238.252.49:8080/adam_at_cryptocomm.asc -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MySQL Problem
the statement runs without errors, its just the output thats wrong (see the two tables) I'm displaying the results by doing a simple dump of the query, through my own code and have also tried through phpMyAdmin. -Original Message- From: Michael Hazelden [mailto:[EMAIL PROTECTED]] Sent: 21 November 2002 16:41 To: Griffiths, Daniel; PHP List Subject: RE: [PHP-DB] MySQL Problem Maybe I'm missing something - but the MySQL statement looks right ... How are you displaying the results? -Original Message- From: Griffiths, Daniel [mailto:[EMAIL PROTECTED]] Sent: 21 November 2002 16:37 To: PHP List Subject: [PHP-DB] MySQL Problem Hi, I have a problem with an MySQL statement that is driving me mad, I'm using php to connect to the database. this is it : - SELECT LINE,SUM(IF(MONTH=2,WB.TEU,0)) AS '2' ,SUM(IF(MONTH=3,WB.TEU,0)) AS '3' ,SUM(IF(MONTH=4,WB.TEU,0)) AS '4' ,SUM(IF(MONTH=5,WB.TEU,0)) AS '5' ,SUM(IF(MONTH=6,WB.TEU,0)) AS '6' ,SUM(IF(MONTH BETWEEN 2 AND 6,WB.TEU,0)) AS '2 TO 6' FROM WB WHERE EAST_PORT='A' AND WEST_PORT='B' GROUP BY LINE what I want to produce with this is this : - LINE2 3 4 5 6 2 TO 6 A 133511181260139512766384 B 432 469 451 480 555 2387 C 504 554 575 517 764 2914 however what I get is this : - LINE2 3 4 5 6 2 TO 6 A 111812601395127663846384 B 469 451 480 555 23872387 C 554 575 517 764 29142914 the last column of figures (2 TO 6) has been replicated under 6 and pushed the values that were in 6 into 5 and so on! I can not see for the life of me why this should or could be happening, especially as the equivalent code in MS Access : - SELECT LINE, Sum(IIf(MONTH=2,TEU,0)) AS ['2'], Sum(IIf(MONTH=3,TEU,0)) AS ['3'], Sum(IIf(MONTH=4,TEU,0)) AS ['4'], Sum(IIf(MONTH=5,TEU,0)) AS ['5'], Sum(IIf(MONTH=6,TEU,0)) AS ['6'], SUM(IIF(MONTH BETWEEN 2 AND 6,TEU,0)) AS 'FEB TO JUNE' , SUM(IIF(MONTH BETWEEN 1 AND 6,TEU,0)) AS 'YTD' FROM DUMP WHERE (((DUMP.[EAST PORT])='HONG KONG') AND ((DUMP.[WEST PORT])='UK')) GROUP BY DUMP.LINE; gives the correct results. If anyone has any idea of what is going on here please let me know, I'm hoping its something simple I'm missing.. Thanks Dan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php _ This message has been checked for all known viruses by the MessageLabs Virus Control Centre. This message has been checked for all known viruses by the MessageLabs Virus Control Centre. * Notice: This email is confidential and may contain copyright material of Ocado Limited (the Company). Opinions and views expressed in this message may not necessarily reflect the opinions and views of the Company. If you are not the intended recipient, please notify us immediately and delete all copies of this message. Please note that it is your responsibility to scan this message for viruses. Company reg. no. 3875000. Swallowdale Lane, Hemel Hempstead HP2 7PY * -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MySQL Problem
Still think we need to see the PHP code before determining that it is an SQL problem. Regards, J -Original Message- From: Jason Wong [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 21, 2002 11:55 AM To: [EMAIL PROTECTED] Subject: Re: [PHP-DB] MySQL Problem On Friday 22 November 2002 00:46, Griffiths, Daniel wrote: the statement runs without errors, its just the output thats wrong (see the two tables) I'm displaying the results by doing a simple dump of the query, through my own code and have also tried through phpMyAdmin. Not sure if this could be the problem -- where are you getting MONTH from and does it start at 0 or 1? -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * /* I saw Lassie. It took me four shows to figure out why the hairy kid never spoke. I mean, he could roll over and all that, but did that deserve a series? */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MySQL Problem
the month starts at 1, at the moment I'm just hard coding the month into the statment. -Original Message- From: Jason Wong [mailto:[EMAIL PROTECTED]] Sent: 21 November 2002 16:55 To: [EMAIL PROTECTED] Subject: Re: [PHP-DB] MySQL Problem On Friday 22 November 2002 00:46, Griffiths, Daniel wrote: the statement runs without errors, its just the output thats wrong (see the two tables) I'm displaying the results by doing a simple dump of the query, through my own code and have also tried through phpMyAdmin. Not sure if this could be the problem -- where are you getting MONTH from and does it start at 0 or 1? -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * /* I saw Lassie. It took me four shows to figure out why the hairy kid never spoke. I mean, he could roll over and all that, but did that deserve a series? */ -- 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] Re: Get Last ID Inserted
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello Adam On 21 November 2002 at 11:12:46 -0500 (which was 16:12 where I live) Adam Voigt rearranged electrons to get But if there are heavy operations on the site, will this not also pick up a different last inserted id, if in the split milisecond between the insert and the next mssql_query which has the @@identity say, another user does an insert? No. It picks up the last identity on that connection. So it does not mater how many other connections and what they are doing. It can only be wrong if you close off the connection and open another one. The other way is to put it all into one SQL statement (as long as you don't use mssql_? functions in PHP) e.g. (one I used earlier) ==8= begin transaction set nocount on declare @NewId int update item set name = 'Canada Delivery' where itemid = '428' insert into item (ItemTypeId, Name, LastUpdated) values (49,'Mexico Delivery',getutcdate()) set @NewId = @@identity insert into DelArea (itemid,StdEUR, StdGBP, StdUSD, HotEUR, HotGBP, HotUSD) values (@NewId,37,19,45,134,70,70) Update country set DelId = @NewId where itemid = 288 commit select @NewId NewDelId ==8= - -- Ti2GO,___ David |David Elliott| Software Engineer| _| [EMAIL PROTECTED] | PGP Key ID 0x650F4534 | | Do you think someone was BORGED when they made these up?| -BEGIN PGP SIGNATURE- Version: 6.5.8ckt http://www.ipgpp.com/ iQA/AwUBPd0TBPmK8eZlD0U0EQL1awCgmS57QwyvS+bz02XsLQtwJOSGSB8AoLmo I9VdgNIsp3GXkzWAX6I1jYpp =UXBQ -END PGP SIGNATURE- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Get Last ID Inserted
Ahh, thanks very much. On Thu, 2002-11-21 at 12:08, David Elliott wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello Adam On 21 November 2002 at 11:12:46 -0500 (which was 16:12 where I live) Adam Voigt rearranged electrons to get But if there are heavy operations on the site, will this not also pick up a different last inserted id, if in the split milisecond between the insert and the next mssql_query which has the @@identity say, another user does an insert? No. It picks up the last identity on that connection. So it does not mater how many other connections and what they are doing. It can only be wrong if you close off the connection and open another one. The other way is to put it all into one SQL statement (as long as you don't use mssql_? functions in PHP) e.g. (one I used earlier) ==8= begin transaction set nocount on declare @NewId int update item set name = 'Canada Delivery' where itemid = '428' insert into item (ItemTypeId, Name, LastUpdated) values (49,'Mexico Delivery',getutcdate()) set @NewId = @@identity insert into DelArea (itemid,StdEUR, StdGBP, StdUSD, HotEUR, HotGBP, HotUSD) values (@NewId,37,19,45,134,70,70) Update country set DelId = @NewId where itemid = 288 commit select @NewId NewDelId ==8= - -- Ti2GO,___ David |David Elliott| Software Engineer| _| [EMAIL PROTECTED] | PGP Key ID 0x650F4534 | | Do you think someone was BORGED when they made these up?| -BEGIN PGP SIGNATURE- Version: 6.5.8ckt http://www.ipgpp.com/ iQA/AwUBPd0TBPmK8eZlD0U0EQL1awCgmS57QwyvS+bz02XsLQtwJOSGSB8AoLmo I9VdgNIsp3GXkzWAX6I1jYpp =UXBQ -END PGP SIGNATURE- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Adam Voigt ([EMAIL PROTECTED]) The Cryptocomm Group My GPG Key: http://64.238.252.49:8080/adam_at_cryptocomm.asc signature.asc Description: This is a digitally signed message part
[PHP-DB] php_mcrypt
Has anyone gotten php_mcrypt working with the latest version of php on win32? Ryan -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Re: Get Last ID Inserted
From the MySQL docs: The most recently generated ID is maintained in the server on a per-connection basis. It will not be changed by another client. It will not even be changed if you update another AUTO_INCREMENT column with a non-magic value (that is, a value that is not NULL and not 0). So as long as you're using non-persistent connections, even on a site with heavy traffic it should work fine. -Micah At 11:14 AM 11/21/2002 -0500, you wrote: I do see that picking up the incorrect ID, which is why we created a stored procedure which returned the affected row. -Original Message- From: Adam Voigt [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 21, 2002 11:13 AM To: David Elliott Cc: Adam Voigt on PHP-DB Subject: Re: [PHP-DB] Re: Get Last ID Inserted But if there are heavy operations on the site, will this not also pick up a different last inserted id, if in the split milisecond between the insert and the next mssql_query which has the @@identity say, another user does an insert? On Thu, 2002-11-21 at 11:03, David Elliott wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello Adam On 21 November 2002 at 10:47:15 -0500 (which was 15:47 where I live) Adam Voigt wrote Using Microsoft SQL does anyone know how to get the id of the row that you just inserted without clumsily trying to select the id back based on the same criteria of your insert (which might be overlapping)? select @@identity - -- Cheers, ___ David |David Elliott| Software Engineer | _| [EMAIL PROTECTED] | PGP Key ID 0x650F4534 | | No dinner with Mel Gibson?! - Dot Warner | -BEGIN PGP SIGNATURE- Version: 6.5.8ckt http://www.ipgpp.com/ iQA/AwUBPd0DwfmK8eZlD0U0EQI3fACgsv52o5AvhuroJIVYblYXTnkiDZYAn2Ao y1AeA+bR4KPOwZhZTAa2x7kr =f/lr -END PGP SIGNATURE- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Adam Voigt ([EMAIL PROTECTED]) The Cryptocomm Group My GPG Key: http://64.238.252.49:8080/adam_at_cryptocomm.asc -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Check data exists in MySQL
Sorry about previous post (some stupid shortcut key made it send) Your row: $sql = INSERT INTO count VALUES('$dept', '$deptsub', '0'); should be: $sql = INSERT INTO count VALUES('$dept', '$deptsub', '0'); You missed the double quotes. Adam --- Original Message --- Hi, for some reason i'm getting an error when I try to run this query: __ $sql=SELECT num FROM count where dept='$dept' AND deptsub='$deptsub'; $page_count = mysql_query($sql,$db); # If does not exist in the database, insert a new entry if (!mysql_num_rows($page_count)){ $sql = INSERT INTO count VALUES('$dept', '$deptsub', '0'); $result = mysql_query($sql,$db); } #Update the count in the database $page_count = $page_count + 1; $sql=UPDATE count SET num='$page_count' WHERE dept='$dept' AND deptsub='$deptsub'; $result = mysql_query($sql, $db); __ My table looks like this: count: dept VARCHAR(32) deptsub VARCHAR(32) num INT(10) __ I'm getting the following error message: Parse error: parse error in /home/./public_html/public/index.php on line 25 (Which is the line: $sql=UPDATE count...) Any thoughts would b most helpful. Thanks, Gav
[PHP-DB] arrays
Hi everyone, I have a problem sorting my data, I have an array with a flush of values and years from a mysql database. When i have all the values i need php to only print the values, and then group it by year souch that if i had an array that looked like this: Array([2002] = 20,[2002] = 40,[2003] = 93) Then it would print 2002 = 60 2003 = 93 Thanks a lot Best regards, Martin A Jensen -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] FW: Selecting not on DB
Hi there, I have a problem that I cannot seem to solve. I'm using MySql and query it using PHP. I have create table ABC with field called number containing (1,4,6,10) How is to select all number form 1 to 10 which are not in that field so the output somehow like this : ++ | Number | ++ | 2 | | 3 | | 5 | | 7 | | 8 | | 9 | ++ SELECT * FROM abc WHERE Number NOT BETWEEN 1 AND 10; ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] INSERT question. 2 pointers.
hi Scott, 1. When you are using an insert where you dont want to use all columns: use this: $query = INSERT INTO accounts VALUES('.$accnts[0].', 'TIMESTAMP(10)','','','','',); (never have empty comma strings with nothing inside.) 2. Secondly, when you get errors, and you dont know where the error is do this: before executing the query, print out the query on the browser (comment out the query execution code) and then copy paste the query from the browser and then run the same in your mysql client, and you will know exactly where is the problem from the error generated. regards, *** Amit Wadhwa Dell International Services *** -Original Message- From: NIPP, SCOTT V (SBCSI) [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 21, 2002 12:46 AM To: '[EMAIL PROTECTED]' Subject: [PHP-DB] INSERT question... I have an INSERT statement that I cannot quiet get working. The table that this data is being put into has 7 columns, but I only care about putting in the data from the first two columns at this time. The first column is an array element, and the second column needs to be a timestamp. Here is the INSERT statement: $query = INSERT INTO accounts VALUES('.$accnts[0].', 'TIMESTAMP(10)', , , , ,); Here is the error being displayed on the web page: You have an error in your SQL syntax near ' , , ,)' at line 1 Thanks in advance for the help. Scott Nipp Phone: (214) 858-1289 E-mail: [EMAIL PROTECTED] Web: http:\\ldsa.sbcld.sbc.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] UPDATE mySQL database with IF condition
Hi.. I'm having some problem with an if condition when I'm trying to insert data into a mysql table.. the script works fine with no parse errors..but the table doesn't get updated like I want it to be.. and i'm almost certain that the code is correct... Here is part of the script.. I have a form where users can update their setting and also be able to upload a new picture if they wish Their information is already stored in a table and I wanted to give them the possibility to add a new picture or keep the same old one. here is the code: if ($picture == ) { $query = UPDATE authors SET name = '$name', email = '$email', website = '$website', username = '$username', password = '$password' WHERE username = '$SESSION_UNAME'; $result = mysql_query($query) or die (Error in query: $query. . mysql_error()); } else { $file_dir = usergraphics; if ( $picture_type == image/gif ) { copy ( $picture, $file_dir/$picture_name) or die (Couldn't Upload File); } $query = UPDATE authors SET name = '$name', email = '$email', website = '$website', picture = '$picture_name', username = '$username', password = '$password' WHERE username = '$SESSION_UNAME'; $result = mysql_query($query) or die (Error in query: $query. . mysql_error()); } When the user update his profile with a new picture, everything is entered in the database correctly but when he updates his profile and leaves the picture field blank in order to keep his current picture, the information is entered correctly in the databse but the old picture is deleted from the table.. How can I remedy it..? Thanks Kind Regards RRamroop - Ramroop Ravi Tel: (230) 7258829 Fax: (230) 3952095 E-mail: [EMAIL PROTECTED] Website: http://www.imagine-studio.com Fingerprint: 7400 945A D75D 5F76 E312 F7D4 D5C3 A078 FDEA 2984 Public Key: http://www.imagine-studio.com/pgp/raviramroop.asc -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Help with Date
Is there a way to convert a date 20/11/2002 to a Float number in PHP, since the date starts in 30/12/1899, just like the delphi treats the dates Yeah, of course. To convert 20/11/2002 into a float, just do echo (float)20/11/2002; and you get 20!! Or you can use strtotime() to convert it to a Unix timestamp (integer, number of seconds since Jan 1, 1970). ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php