[PHP] SQL statement - please help
Dear all Please, I realy need your help. I am trying to extract only those records that were entered during the past hour from my MySQL database. The following SQL statement extracts all the records. As soon as I change the INTERVAL to 1 HOUR I see all the records in my database. When I change CURDATE() to CURTIME() no records are extracted from the database. The cgtimeposted field is a time data type. Please advise me as to how I can formulate my SQL statement so that only those records entered within the past hour displays on my page. Here is my current SQL statement - this statement displays all the records: $sql = Select tblchatglobal.cgid, tblchatglobal.cgdateposted, tblchatglobal.cgtimeposted, tblchatglobal.uid, tblchatglobal.cgmsg, tblusers.uid, tblusers.uusername from tblchatglobal, tblusers where (tblchatglobal.uid = tblusers.uid) and (DATE_SUB(CURDATE(),INTERVAL 1 HOUR) = tblchatglobal.cgtimeposted) order by tblchatglobal.cgtimeposted desc; Jacques -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] SQL statement - please help
[snip] $sql = Select tblchatglobal.cgid, tblchatglobal.cgdateposted, tblchatglobal.cgtimeposted, tblchatglobal.uid, tblchatglobal.cgmsg, tblusers.uid, tblusers.uusername from tblchatglobal, tblusers where (tblchatglobal.uid = tblusers.uid) and (DATE_SUB(CURDATE(),INTERVAL 1 HOUR) = tblchatglobal.cgtimeposted) order by tblchatglobal.cgtimeposted desc; [/snip] One of the MySQL lists will be really helpful to you. http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html AND (DATE_SUB(CURDATE(),INTERVAL 1 HOUR)= tblchatglobal.cgtimeposted) CURDATE is wrong here, as it only returns a date. NOW() returns a datetime. Try AND (DATE_SUB(NOW(),INTERVAL 1 HOUR)= tblchatglobal.cgtimeposted) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL statement - please help
Hi, Thursday, March 24, 2005, 6:50:38 PM, you wrote: J Dear all J Please, I realy need your help. I am trying to extract only those records J that were entered during the past hour from my MySQL database. The J following SQL statement extracts all the records. As soon as I change the J INTERVAL to 1 HOUR I see all the records in my database. When I change J CURDATE() to CURTIME() no records are extracted from the database. The J cgtimeposted field is a time data type. J Please advise me as to how I can formulate my SQL statement so that only J those records entered within the past hour displays on my page. J Here is my current SQL statement - this statement displays all the records: J $sql = Select tblchatglobal.cgid, tblchatglobal.cgdateposted, J tblchatglobal.cgtimeposted, tblchatglobal.uid, tblchatglobal.cgmsg, J tblusers.uid, tblusers.uusername from tblchatglobal, tblusers where J (tblchatglobal.uid = tblusers.uid) and J (DATE_SUB(CURDATE(),INTERVAL 1 HOUR) J = tblchatglobal.cgtimeposted) order by J tblchatglobal.cgtimeposted desc; J Jacques You cannot do this with just a time field as what happens at midnight and now() becomes 00:00:00, you will need to work with a full date_time field. The other problem is CURDATE has no time component, it is just a date so subtracting 1 hour will always return 23:00:00. So change the time field to a timestamp and try ... AND tblchatglobal.cgtimeposted (DATE_SUB(NOW(),INTERVAL 1 HOUR)) -- regards, Tom -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] SQL statement - date
How should I formulate my sql statement to create a result set of members who registered between now and 7 days ago? I have tried the following and it is obviously incorrect: $sqlnewmembers = Select uid, uprofilename from tblusers where udatereg between (date() and date() - 7); Regards Jacques -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL statement - date
take a look here http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html the first example should help you... regards jesper Jacques wrote: How should I formulate my sql statement to create a result set of members who registered between now and 7 days ago? I have tried the following and it is obviously incorrect: $sqlnewmembers = Select uid, uprofilename from tblusers where udatereg between (date() and date() - 7); Regards Jacques -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] sql statement help needed
hello, i've had to change some of my tables to accomodate some greater flexibility in the application that uses it and because of this i need to go through and update all the records. i've done one table by hand and it had about 100 records and took about 20 minutes. but this next table has about 550 records and i really don't feel like doing this all by hand. i'm using MySQL Control Center to do this editing so i'd like to know if there's a single SQL statement i could use to update all the rows. here is a simple representation: products: (pay no attention to the poor choice in column names. this is a retrofitting and will be fixed in later versions.) +--+---+ | id | sequential_id | +--+---+ | PRDX-41 | 1 | | ABCX-01 | 2 | | FF00-11 | 3 | \/\/\/\/\/\/\/\/ | ETC0-99 | 500 | +--+---+ the 'prod_sequential_id' column was added later to the products_categories table. products_categories: +-+-+++ | id | prod_id | prod_sequential_id | cat_id | +-+-+++ | 1 | PRDX-41 | 0 | 41 | | 2 | PRDX-41 | 0 | 15 | | 3 | ABCX-01 | 0 | 13 | | 4 | FF00-11 | 0 | 89 | \/\/\/\/\/\/\/\/ | 610 | ETC0-99 | 0 | 41 | +-+-+++ so... as you can see, prod_sequential_id has all 0's in its column. it should contain the value of products.sequential_id WHERE products_categories.prod_id = products.id. the problem is that i'm not sure how to do this all in one statement (or if it's even possible): (i know the following does not work, but it's basically the logic i think i need.) UPDATE products_categories AS pc, products AS p SET pc.prod_sequential_id = p.id WHERE pc.prod_id = p.id; thanks for your help. chris. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] sql statement help needed
- Original Message - From: Chris W. Parker [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, July 01, 2004 3:33 AM Subject: [PHP] sql statement help needed hello, i've had to change some of my tables to accomodate some greater flexibility in the application that uses it and because of this i need to go through and update all the records. i've done one table by hand and it had about 100 records and took about 20 minutes. but this next table has about 550 records and i really don't feel like doing this all by hand. i'm using MySQL Control Center to do this editing so i'd like to know if there's a single SQL statement i could use to update all the rows. here is a simple representation: products: (pay no attention to the poor choice in column names. this is a retrofitting and will be fixed in later versions.) +--+---+ | id | sequential_id | +--+---+ | PRDX-41 | 1 | | ABCX-01 | 2 | | FF00-11 | 3 | \/\/\/\/\/\/\/\/ | ETC0-99 | 500 | +--+---+ the 'prod_sequential_id' column was added later to the products_categories table. products_categories: +-+-+++ | id | prod_id | prod_sequential_id | cat_id | +-+-+++ | 1 | PRDX-41 | 0 | 41 | | 2 | PRDX-41 | 0 | 15 | | 3 | ABCX-01 | 0 | 13 | | 4 | FF00-11 | 0 | 89 | \/\/\/\/\/\/\/\/ | 610 | ETC0-99 | 0 | 41 | +-+-+++ so... as you can see, prod_sequential_id has all 0's in its column. it should contain the value of products.sequential_id WHERE products_categories.prod_id = products.id. the problem is that i'm not sure how to do this all in one statement (or if it's even possible): (i know the following does not work, but it's basically the logic i think i need.) UPDATE products_categories AS pc, products AS p SET pc.prod_sequential_id = p.id WHERE pc.prod_id = p.id; why can't you do like this. UPDATE products_categories set prod_sequential_id = prod_id in one go. Thanks Binay thanks for your help. chris. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL statement
Curt Zirzow wrote: * Thus wrote Dan J. Rychlik ([EMAIL PROTECTED]): Well, we have a word for myself that over looks things and takes things literal. Its called pencil smoke It means dump, dunce, and lost. Jennifer was right, their was an error in my SQL query. technically it was an error in the code not the query. SELECT username, password, DATE_FORMAT(timestamp, '%d%m%y') as timestamp FROM custlogon; not as formatted_ts. It worked fine even on zeus it tested fine. Glad to hear it worked. For future reference, however, it can be a lot easier to help you if you could provided some essential information about your problem, some things to keep in mind: - What is the error message. - Show some code that surrounds the line number in the error. - If a variable is causing the problem, explain how that variable gets assigned. So, If you would have posted a message something like: --- I get this error in my php script: Notice: Undefined index: timestamp in C:\Program Files\Apache Group\Apache2\htdocs\Ameriforms\admintool\includes\getlogonhist.php on line 44 My line 44 looks like this: ?php echo $row['timestamp'];?/font/td The variable $row is filled in from a mysql_fetch_assoc() call And the query I'm executing is like this: SELECT username, password, DATE_FORMAT(timestamp, '%d%m%y') FROM custlogon --- So instead of 21 emails sent back and forth with trial and errors. The problem would have been easily solved. HTH, Curt -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] SQL statement
Hello, Im having a bit of trouble with the DATE_FORMAT function in mysql. $query = ('SELECT username, password, DATE_FORMAT(timestamp, '%d%m%y') FROM custlogon'); I know its failing because php doesnt like the quotation before the format parameters. Ive tried to fix this without any luck. Any Ideas ? DATE_FORMAT ( timestamp, ' %d%m%y ') Thanks in advance, -Dan
RE: [PHP] SQL statement
Dan J. Rychlik mailto:[EMAIL PROTECTED] on Tuesday, September 23, 2003 4:58 PM said: I know its failing because php doesnt like the quotation before the format parameters. Ive tried to fix this without any luck. Any Ideas ? It's failing because the ' before the %d is closing the string. Here's how I work out SQL stuffs: $sql = SELECT username\n . , password\n . , DATE_FORMAT(timestamp, '%d%m%y')\n .FROM custlogon; $query = ($sql); Althought it looks like it takes a lot more time to write (which is true) it looks great when you are debugging. echo pre$sql/pre; Alternatively you should be able to do this: $query = (SELECT username, password, DATE_FORMAT(timestamp, '%d%m%y') FROM custlogon); chris. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL statement
Ive used this $query = (SELECT username, password, DATE_FORMAT(timestamp, '%d%m%y') FROM custlogon); But I recieve unknown index timestamp. *shrug* - Original Message - From: Chris W. Parker [EMAIL PROTECTED] To: Dan J. Rychlik [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 7:07 PM Subject: RE: [PHP] SQL statement Dan J. Rychlik mailto:[EMAIL PROTECTED] on Tuesday, September 23, 2003 4:58 PM said: I know its failing because php doesnt like the quotation before the format parameters. Ive tried to fix this without any luck. Any Ideas ? It's failing because the ' before the %d is closing the string. Here's how I work out SQL stuffs: $sql = SELECT username\n . , password\n . , DATE_FORMAT(timestamp, '%d%m%y')\n .FROM custlogon; $query = ($sql); Althought it looks like it takes a lot more time to write (which is true) it looks great when you are debugging. echo pre$sql/pre; Alternatively you should be able to do this: $query = (SELECT username, password, DATE_FORMAT(timestamp, '%d%m%y') FROM custlogon); chris. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] SQL statement
Ive used this $query = (SELECT username, password, DATE_FORMAT(timestamp, '%d%m%y') FROM custlogon); But I recieve unknown index timestamp. *shrug* You are receiving the error on an array returned by fetch_array? If so, it is because the index is DATE_FORMAT(timestamp, '%d%m%y'), not timestamp, which isn't so great. You can see this by doing a print_r on the array to see what is actually in it. Use aliasing in your query to give it a better index. SELECT username, password, DATE_FORMAT(timestamp, '%d%m%y') as formatted_ts FROM custlogon or something like that. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL statement
Jennifer, you're right, I am using fetch_array... I tried to use your suggestion, and it failing as well, It wont even execute Do you have a better method of looping through all these records?? - Original Message - From: Jennifer Goodie [EMAIL PROTECTED] To: Dan J. Rychlik [EMAIL PROTECTED]; Chris W. Parker [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 7:15 PM Subject: RE: [PHP] SQL statement Ive used this $query = (SELECT username, password, DATE_FORMAT(timestamp, '%d%m%y') FROM custlogon); But I recieve unknown index timestamp. *shrug* You are receiving the error on an array returned by fetch_array? If so, it is because the index is DATE_FORMAT(timestamp, '%d%m%y'), not timestamp, which isn't so great. You can see this by doing a print_r on the array to see what is actually in it. Use aliasing in your query to give it a better index. SELECT username, password, DATE_FORMAT(timestamp, '%d%m%y') as formatted_ts FROM custlogon or something like that. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] SQL statement
I havent used mysql for a while... but here goes... http://www.mysql.com/doc/en/Date_and_time_functions.html DATE_FORMAT(date,format) Formats the date value according to the format string. mysql SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y'); - 'Saturday October 1997' mysql SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s'); - '22:23:00' it seems that DATE_FORMAT() is expecting a textual date as the first arguement. try this: $query = SELECT username, password, DATE_FORMAT(CURRENT_TIMESTAMP(), '%d%m%y') FROM custlogon; or if that doesnt work try: $query = SELECT username, password, DATE_FORMAT(NOW(), '%d%m%y') FROM custlogon; -Original Message- From: Dan J. Rychlik [mailto:[EMAIL PROTECTED] Sent: Wednesday, 24 September 2003 10:10 To: Chris W. Parker; [EMAIL PROTECTED] Subject: Re: [PHP] SQL statement Ive used this $query = (SELECT username, password, DATE_FORMAT(timestamp, '%d%m%y') FROM custlogon); But I recieve unknown index timestamp. *shrug* - Original Message - From: Chris W. Parker [EMAIL PROTECTED] To: Dan J. Rychlik [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 7:07 PM Subject: RE: [PHP] SQL statement Dan J. Rychlik mailto:[EMAIL PROTECTED] on Tuesday, September 23, 2003 4:58 PM said: I know its failing because php doesnt like the quotation before the format parameters. Ive tried to fix this without any luck. Any Ideas ? It's failing because the ' before the %d is closing the string. Here's how I work out SQL stuffs: $sql = SELECT username\n . , password\n . , DATE_FORMAT(timestamp, '%d%m%y')\n .FROM custlogon; $query = ($sql); Althought it looks like it takes a lot more time to write (which is true) it looks great when you are debugging. echo pre$sql/pre; Alternatively you should be able to do this: $query = (SELECT username, password, DATE_FORMAT(timestamp, '%d%m%y') FROM custlogon); chris. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php * This e-mail, including any attachments to it, may contain confidential and/or personal information. If you have received this e-mail in error, you must not copy, distribute, or disclose it, use or take any action based on the information contained within it. Please notify the sender immediately by return e-mail of the error and then delete the original e-mail. The information contained within this e-mail may be solely the opinion of the sender and may not necessarily reflect the position, beliefs or opinions of Salmat on any issue. This email has been swept for the presence of computer viruses known to Salmat's anti-virus systems. For more information, visit our website at www.salmat.com.au. * -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] SQL statement
Jennifer, you're right, I am using fetch_array... I tried to use your suggestion, and it failing as well, It wont even execute Do you have a better method of looping through all these records?? There's probably an error in my SQL syntax. What is mysql_error() telling you? If it was my query and my database I'd probably know what the problem is just by looking, but it isn't and I don't. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] SQL statement
try this: $query = SELECT username, password, DATE_FORMAT(CURRENT_TIMESTAMP(), '%d%m%y') FROM custlogon; or if that doesnt work try: $query = SELECT username, password, DATE_FORMAT(NOW(), '%d%m%y') FROM custlogon; [snip] original query as posted: SELECT username, password, DATE_FORMAT(timestamp, '%d%m%y') FROM custlogon [/snip] Unless timestamp is actually the name of a column in the database, in that case NOW() and CURRENT_TIMESTAMP() won't give the expected results. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL statement
On Tue, 23 Sep 2003 19:10:29 -0500 Dan J. Rychlik [EMAIL PROTECTED] wrote: Ive used this $query = (SELECT username, password, DATE_FORMAT(timestamp, '%d%m%y') FROM custlogon); But I recieve unknown index timestamp. *shrug* Is timestamp an actual columnname in your table? -- Raquel It is easy to take liberty for granted when you have never had it taken from you. --M. Grundler -- Raquel It is easy to take liberty for granted when you have never had it taken from you. --M. Grundler -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL statement
I dont believe it to be an error because Ive run this from the CLI on my zeus system. I have also echoed out the sql statement to read exactly what is being read. The other elements in the record are return as expected, except for the timestamp in which the DATE_FORMAT mysql function is called. I know its got to be problem with the string terminators and or the way the fetch_array reads the elements of a record in my database. - Original Message - From: Jennifer Goodie [EMAIL PROTECTED] To: Dan J. Rychlik [EMAIL PROTECTED]; Chris W. Parker [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 7:25 PM Subject: RE: [PHP] SQL statement Jennifer, you're right, I am using fetch_array... I tried to use your suggestion, and it failing as well, It wont even execute Do you have a better method of looping through all these records?? There's probably an error in my SQL syntax. What is mysql_error() telling you? If it was my query and my database I'd probably know what the problem is just by looking, but it isn't and I don't. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL statement
This didnt work as well. SELECT username, password, DATE_FORMAT(timestamp, '%d%m%y') as formatted_ts FROM custlogon; It caused my script to die and not execute at all - Original Message - From: Jennifer Goodie [EMAIL PROTECTED] To: Dan J. Rychlik [EMAIL PROTECTED]; Chris W. Parker [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 7:15 PM Subject: RE: [PHP] SQL statement Ive used this $query = (SELECT username, password, DATE_FORMAT(timestamp, '%d%m%y') FROM custlogon); But I recieve unknown index timestamp. *shrug* You are receiving the error on an array returned by fetch_array? If so, it is because the index is DATE_FORMAT(timestamp, '%d%m%y'), not timestamp, which isn't so great. You can see this by doing a print_r on the array to see what is actually in it. Use aliasing in your query to give it a better index. SELECT username, password, DATE_FORMAT(timestamp, '%d%m%y') as formatted_ts FROM custlogon or something like that. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] SQL statement
Jennifer, you're right, I am using fetch_array... I tried to use your suggestion, and it failing as well, It wont even execute There's probably an error in my SQL syntax. What is mysql_error() telling you? I dont believe it to be an error because Ive run this from the CLI on my zeus system. I have also echoed out the sql statement to read exactly what I know its got to be problem with the string terminators and or the way the fetch_array reads the elements of a record in my database. Why don't you humor me and tell me what the error is and show me the code that is generating it. A PHP error message and the output from mysql_error() will go a long way in debugging a problem. I can't really work with it stops working and it fails to execute, those don't tell me much except that there's probably a problem with the query. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] SQL statement
http://www.mysql.com/doc/en/Reserved_words.html [snip] 6.1.7 Is MySQL Picky About Reserved Words? A common problem stems from trying to create a table with column names that use the names of datatypes or functions built into MySQL, such as TIMESTAMP or GROUP. You're allowed to do it (for example, ABS is allowed as a column name). However, by default, in function invocations no whitespace is allowed between the function name and the following `(' character, so that a function call can be distinguished from a reference to a column name. [/snip] try renaming the field timestamp to something else and see if that helps? -Original Message- From: Dan J. Rychlik [mailto:[EMAIL PROTECTED] Sent: Wednesday, 24 September 2003 10:32 To: [EMAIL PROTECTED]; Chris W. Parker; [EMAIL PROTECTED] Subject: Re: [PHP] SQL statement This didnt work as well. SELECT username, password, DATE_FORMAT(timestamp, '%d%m%y') as formatted_ts FROM custlogon; It caused my script to die and not execute at all - Original Message - From: Jennifer Goodie [EMAIL PROTECTED] To: Dan J. Rychlik [EMAIL PROTECTED]; Chris W. Parker [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 7:15 PM Subject: RE: [PHP] SQL statement Ive used this $query = (SELECT username, password, DATE_FORMAT(timestamp, '%d%m%y') FROM custlogon); But I recieve unknown index timestamp. *shrug* You are receiving the error on an array returned by fetch_array? If so, it is because the index is DATE_FORMAT(timestamp, '%d%m%y'), not timestamp, which isn't so great. You can see this by doing a print_r on the array to see what is actually in it. Use aliasing in your query to give it a better index. SELECT username, password, DATE_FORMAT(timestamp, '%d%m%y') as formatted_ts FROM custlogon or something like that. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php * This e-mail, including any attachments to it, may contain confidential and/or personal information. If you have received this e-mail in error, you must not copy, distribute, or disclose it, use or take any action based on the information contained within it. Please notify the sender immediately by return e-mail of the error and then delete the original e-mail. The information contained within this e-mail may be solely the opinion of the sender and may not necessarily reflect the position, beliefs or opinions of Salmat on any issue. This email has been swept for the presence of computer viruses known to Salmat's anti-virus systems. For more information, visit our website at www.salmat.com.au. * -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL statement
Thank you for your time on this. I do apologize for being a pain. Here is my code... ?php // Function that runs reports on logon history function logonHist() { db_connect(); //establish connection $_qlogonhist = (SELECT username,host_info,status, DATE_FORMAT(timestamp, '%d%m%y') as formatted_ts FROM custlogon_hist); // Build query. //$_qlogonhist = ('SELECT * FROM custlogon_hist'); $_rlogonhist = mysql_query($_qlogonhist); ? table width=50% border=0 align=center cellpadding=0 cellspacing=0 ?php while ($row = mysql_fetch_array($_rlogonhist)) { ? tr td width=27%strongfont color=#00 size=2 face=Arial, Helvetica, sans-serifUsername/font/strong/td td colspan=3font color=#00 size=2 face=Arial, Helvetica, sans-serif ?php echo $row['username'];?/font/td /tr tr tdstrongfont color=#00 size=2 face=Arial, Helvetica, sans-serifHost Info /font/strong/td td colspan=3font color=#00 size=2 face=Arial, Helvetica, sans-serif ?php echo $row['host_info'];?/font/td /tr tr tdstrongfont color=#00 size=2 face=Arial, Helvetica, sans-serifStatus/font/strong/td td colspan=3font color=#00 size=2 face=Arial, Helvetica, sans-serif ?php echo $row['status'];?/font/td /tr tr tdstrongfont color=#00 size=2 face=Arial, Helvetica, sans-serifTime Stamp /font/strong/td td colspan=3font color=#00 size=2 face=Arial, Helvetica, sans-serif ?php echo $row['timestamp'];?/font/td /tr tr bgcolor=#99 tdnbsp;/td td width=38%nbsp;/td td colspan=2nbsp;/td /tr ?php } // end while ? /table ?php } // end function ? ERROR Username drychlik Host Info 127.0.0.1 Status OK Time Stamp Notice: Undefined index: timestamp in C:\Program Files\Apache Group\Apache2\htdocs\Ameriforms\admintool\includes\getlogonhist.php on line 44 - Original Message - From: Jennifer Goodie [EMAIL PROTECTED] To: Dan J. Rychlik [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 7:39 PM Subject: RE: [PHP] SQL statement Jennifer, you're right, I am using fetch_array... I tried to use your suggestion, and it failing as well, It wont even execute There's probably an error in my SQL syntax. What is mysql_error() telling you? I dont believe it to be an error because Ive run this from the CLI on my zeus system. I have also echoed out the sql statement to read exactly what I know its got to be problem with the string terminators and or the way the fetch_array reads the elements of a record in my database. Why don't you humor me and tell me what the error is and show me the code that is generating it. A PHP error message and the output from mysql_error() will go a long way in debugging a problem. I can't really work with it stops working and it fails to execute, those don't tell me much except that there's probably a problem with the query. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] SQL statement
http://www.mysql.com/doc/en/Reserved_words.html [snip] 6.1.7 Is MySQL Picky About Reserved Words? A common problem stems from trying to create a table with column names that use the names of datatypes or functions built into MySQL, such as TIMESTAMP or GROUP. You're allowed to do it (for example, ABS is allowed as a column name). However, by default, in function invocations no whitespace is allowed between the function name and the following `(' character, so that a function call can be distinguished from a reference to a column name. [/snip] If you keep scrolling on the page you gave the link for, you'll get to a list of reserved words and notice that timestamp is not reserved. In fact if you scroll even farther, you will see that it is explicitly allowed in mySQL, but that doesn't mean using it is a good idea. try renaming the field timestamp to something else and see if that helps? If you are using a reserved word and renaming is not an option you can use the backtick operator ` to escape it. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL statement
I tried this, and it failed. Thank you for the suggestion! - Original Message - From: Cody Phanekham [EMAIL PROTECTED] To: Dan J. Rychlik [EMAIL PROTECTED]; [EMAIL PROTECTED]; Chris W. Parker [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 7:43 PM Subject: RE: [PHP] SQL statement http://www.mysql.com/doc/en/Reserved_words.html [snip] 6.1.7 Is MySQL Picky About Reserved Words? A common problem stems from trying to create a table with column names that use the names of datatypes or functions built into MySQL, such as TIMESTAMP or GROUP. You're allowed to do it (for example, ABS is allowed as a column name). However, by default, in function invocations no whitespace is allowed between the function name and the following `(' character, so that a function call can be distinguished from a reference to a column name. [/snip] try renaming the field timestamp to something else and see if that helps? -Original Message- From: Dan J. Rychlik [mailto:[EMAIL PROTECTED] Sent: Wednesday, 24 September 2003 10:32 To: [EMAIL PROTECTED]; Chris W. Parker; [EMAIL PROTECTED] Subject: Re: [PHP] SQL statement This didnt work as well. SELECT username, password, DATE_FORMAT(timestamp, '%d%m%y') as formatted_ts FROM custlogon; It caused my script to die and not execute at all - Original Message - From: Jennifer Goodie [EMAIL PROTECTED] To: Dan J. Rychlik [EMAIL PROTECTED]; Chris W. Parker [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 7:15 PM Subject: RE: [PHP] SQL statement Ive used this $query = (SELECT username, password, DATE_FORMAT(timestamp, '%d%m%y') FROM custlogon); But I recieve unknown index timestamp. *shrug* You are receiving the error on an array returned by fetch_array? If so, it is because the index is DATE_FORMAT(timestamp, '%d%m%y'), not timestamp, which isn't so great. You can see this by doing a print_r on the array to see what is actually in it. Use aliasing in your query to give it a better index. SELECT username, password, DATE_FORMAT(timestamp, '%d%m%y') as formatted_ts FROM custlogon or something like that. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php * This e-mail, including any attachments to it, may contain confidential and/or personal information. If you have received this e-mail in error, you must not copy, distribute, or disclose it, use or take any action based on the information contained within it. Please notify the sender immediately by return e-mail of the error and then delete the original e-mail. The information contained within this e-mail may be solely the opinion of the sender and may not necessarily reflect the position, beliefs or opinions of Salmat on any issue. This email has been swept for the presence of computer viruses known to Salmat's anti-virus systems. For more information, visit our website at www.salmat.com.au. * -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] SQL statement
You need to change timestamp to formatted_ts in your php code. -Original Message- From: Dan J. Rychlik [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 5:45 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [PHP] SQL statement Thank you for your time on this. I do apologize for being a pain. Here is my code... ?php // Function that runs reports on logon history function logonHist() { db_connect(); //establish connection $_qlogonhist = (SELECT username,host_info,status, DATE_FORMAT(timestamp, '%d%m%y') as formatted_ts FROM custlogon_hist); // Build query. //$_qlogonhist = ('SELECT * FROM custlogon_hist'); $_rlogonhist = mysql_query($_qlogonhist); ? table width=50% border=0 align=center cellpadding=0 cellspacing=0 ?php while ($row = mysql_fetch_array($_rlogonhist)) { ? tr td width=27%strongfont color=#00 size=2 face=Arial, Helvetica, sans-serifUsername/font/strong/td td colspan=3font color=#00 size=2 face=Arial, Helvetica, sans-serif ?php echo $row['username'];?/font/td /tr tr tdstrongfont color=#00 size=2 face=Arial, Helvetica, sans-serifHost Info /font/strong/td td colspan=3font color=#00 size=2 face=Arial, Helvetica, sans-serif ?php echo $row['host_info'];?/font/td /tr tr tdstrongfont color=#00 size=2 face=Arial, Helvetica, sans-serifStatus/font/strong/td td colspan=3font color=#00 size=2 face=Arial, Helvetica, sans-serif ?php echo $row['status'];?/font/td /tr tr tdstrongfont color=#00 size=2 face=Arial, Helvetica, sans-serifTime Stamp /font/strong/td td colspan=3font color=#00 size=2 face=Arial, Helvetica, sans-serif ?php echo $row['timestamp'];?/font/td /tr tr bgcolor=#99 tdnbsp;/td td width=38%nbsp;/td td colspan=2nbsp;/td /tr ?php } // end while ? /table ?php } // end function ? ERROR Username drychlik Host Info 127.0.0.1 Status OK Time Stamp Notice: Undefined index: timestamp in C:\Program Files\Apache Group\Apache2\htdocs\Ameriforms\admintool\includes\getlogonhist.php on line 44 - Original Message - From: Jennifer Goodie [EMAIL PROTECTED] To: Dan J. Rychlik [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 7:39 PM Subject: RE: [PHP] SQL statement Jennifer, you're right, I am using fetch_array... I tried to use your suggestion, and it failing as well, It wont even execute There's probably an error in my SQL syntax. What is mysql_error() telling you? I dont believe it to be an error because Ive run this from the CLI on my zeus system. I have also echoed out the sql statement to read exactly what I know its got to be problem with the string terminators and or the way the fetch_array reads the elements of a record in my database. Why don't you humor me and tell me what the error is and show me the code that is generating it. A PHP error message and the output from mysql_error() will go a long way in debugging a problem. I can't really work with it stops working and it fails to execute, those don't tell me much except that there's probably a problem with the query. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] SQL statement
Thank you for your time on this. No problem. ?php // Function that runs reports on logon history function logonHist() { db_connect(); //establish connection $_qlogonhist = (SELECT username,host_info,status, DATE_FORMAT(timestamp, '%d%m%y') as formatted_ts FROM custlogon_hist); // Build query. //$_qlogonhist = ('SELECT * FROM custlogon_hist'); $_rlogonhist = mysql_query($_qlogonhist) or die(mysql_error()); For testing purposes add or die(mysql_error()) to your code as I have above. That way we can see if the error is in the query. ?php echo $row['timestamp'];?/font/td? Notice: Undefined index: timestamp in C:\Program Files\Apache Group\Apache2\htdocs\Ameriforms\admintool\includes\getlogonhist.ph p on line 44 timestamp is still undefined, we aliased the column to formatted_ts, so formatted_ts is the array index. I did this because I'm not certain you can use a column name that already exist as an alias becuase mySQL likes unique aliases and I didn't feel like looking up whether or not we could use 'timestamp' -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL statement
Well, we have a word for myself that over looks things and takes things literal. Its called pencil smoke It means dump, dunce, and lost. Jennifer was right, their was an error in my SQL query. SELECT username, password, DATE_FORMAT(timestamp, '%d%m%y') as timestamp FROM custlogon; not as formatted_ts. It worked fine even on zeus it tested fine. Thanks for your help Sincerely, Daniel - Original Message - From: Dan J. Rychlik [EMAIL PROTECTED] To: Cody Phanekham [EMAIL PROTECTED]; [EMAIL PROTECTED]; Chris W. Parker [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 7:50 PM Subject: Re: [PHP] SQL statement I tried this, and it failed. Thank you for the suggestion! - Original Message - From: Cody Phanekham [EMAIL PROTECTED] To: Dan J. Rychlik [EMAIL PROTECTED]; [EMAIL PROTECTED]; Chris W. Parker [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 7:43 PM Subject: RE: [PHP] SQL statement http://www.mysql.com/doc/en/Reserved_words.html [snip] 6.1.7 Is MySQL Picky About Reserved Words? A common problem stems from trying to create a table with column names that use the names of datatypes or functions built into MySQL, such as TIMESTAMP or GROUP. You're allowed to do it (for example, ABS is allowed as a column name). However, by default, in function invocations no whitespace is allowed between the function name and the following `(' character, so that a function call can be distinguished from a reference to a column name. [/snip] try renaming the field timestamp to something else and see if that helps? -Original Message- From: Dan J. Rychlik [mailto:[EMAIL PROTECTED] Sent: Wednesday, 24 September 2003 10:32 To: [EMAIL PROTECTED]; Chris W. Parker; [EMAIL PROTECTED] Subject: Re: [PHP] SQL statement This didnt work as well. SELECT username, password, DATE_FORMAT(timestamp, '%d%m%y') as formatted_ts FROM custlogon; It caused my script to die and not execute at all - Original Message - From: Jennifer Goodie [EMAIL PROTECTED] To: Dan J. Rychlik [EMAIL PROTECTED]; Chris W. Parker [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 7:15 PM Subject: RE: [PHP] SQL statement Ive used this $query = (SELECT username, password, DATE_FORMAT(timestamp, '%d%m%y') FROM custlogon); But I recieve unknown index timestamp. *shrug* You are receiving the error on an array returned by fetch_array? If so, it is because the index is DATE_FORMAT(timestamp, '%d%m%y'), not timestamp, which isn't so great. You can see this by doing a print_r on the array to see what is actually in it. Use aliasing in your query to give it a better index. SELECT username, password, DATE_FORMAT(timestamp, '%d%m%y') as formatted_ts FROM custlogon or something like that. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php * This e-mail, including any attachments to it, may contain confidential and/or personal information. If you have received this e-mail in error, you must not copy, distribute, or disclose it, use or take any action based on the information contained within it. Please notify the sender immediately by return e-mail of the error and then delete the original e-mail. The information contained within this e-mail may be solely the opinion of the sender and may not necessarily reflect the position, beliefs or opinions of Salmat on any issue. This email has been swept for the presence of computer viruses known to Salmat's anti-virus systems. For more information, visit our website at www.salmat.com.au. * -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL statement
* Thus wrote Jennifer Goodie ([EMAIL PROTECTED]): http://www.mysql.com/doc/en/Reserved_words.html [snip] 6.1.7 Is MySQL Picky About Reserved Words? A common problem stems from trying to create a table with column names that use the names of datatypes or functions built into MySQL, such as TIMESTAMP or GROUP. You're allowed to do it (for example, ABS is allowed as a column name). However, by default, in function invocations no whitespace is allowed between the function name and the following `(' character, so that a function call can be distinguished from a reference to a column name. [/snip] If you keep scrolling on the page you gave the link for, you'll get to a list of reserved words and notice that timestamp is not reserved. In fact if you scroll even farther, you will see that it is explicitly allowed in mySQL, but that doesn't mean using it is a good idea. try renaming the field timestamp to something else and see if that helps? If you are using a reserved word and renaming is not an option you can use the backtick operator ` to escape it. As you said ealier, I wouldn't leave reserve words in a table strucure. I've had people name columns like `from` using that backtick operator... good luck trying to select that field.. select from from table1 Do you know off hand if this backtick is ansi sql? Curt -- I used to think I was indecisive, but now I'm not so sure. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL statement
* Thus wrote Dan J. Rychlik ([EMAIL PROTECTED]): Well, we have a word for myself that over looks things and takes things literal. Its called pencil smoke It means dump, dunce, and lost. Jennifer was right, their was an error in my SQL query. technically it was an error in the code not the query. SELECT username, password, DATE_FORMAT(timestamp, '%d%m%y') as timestamp FROM custlogon; not as formatted_ts. It worked fine even on zeus it tested fine. Glad to hear it worked. For future reference, however, it can be a lot easier to help you if you could provided some essential information about your problem, some things to keep in mind: - What is the error message. - Show some code that surrounds the line number in the error. - If a variable is causing the problem, explain how that variable gets assigned. So, If you would have posted a message something like: --- I get this error in my php script: Notice: Undefined index: timestamp in C:\Program Files\Apache Group\Apache2\htdocs\Ameriforms\admintool\includes\getlogonhist.php on line 44 My line 44 looks like this: ?php echo $row['timestamp'];?/font/td The variable $row is filled in from a mysql_fetch_assoc() call And the query I'm executing is like this: SELECT username, password, DATE_FORMAT(timestamp, '%d%m%y') FROM custlogon --- So instead of 21 emails sent back and forth with trial and errors. The problem would have been easily solved. HTH, Curt -- I used to think I was indecisive, but now I'm not so sure. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Statement
this is the problem: $query = Select OSUSER From v$session Where USERNAME = (Select USER From DUAL) And AUDSID =(Select USERENV('SESSIONID') From DUAL); 1. mysql does NOT support subselects! 2. delete the in front of FROM 3. you have to quote v$session: v\$session start with a short query like: $query = select OSUSER from v\$session where USERNAME like 'foo'; then you have to recode the subselects with php-loops!!! michi -- GMX - Die Kommunikationsplattform im Internet. http://www.gmx.net -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL Statement
Sorry... michi.. I've ignored your advice to quote the script as : 3. you have to quote v$session: v\$session - yeah.. the script is work when I change the script right below : $query = Select OSUSER From v\$session Where USERNAME = (Select USER From DUAL) And AUDSID =(Select USERENV('SESSIONID') From DUAL); great thanks in advance to michi... ^_^ - Original Message - From: èdy kurniawan [EMAIL PROTECTED] Subject: Re: [PHP] SQL Statement thanks to michi for ASAP response... but I thought your opinion not actually right.. I've a sql : $query = Select dfl_desc From fmmis__datafls@dbhonda Where dfl_flag = (Select dpb_flag From fmadm_gs00_dtlppbs@dbhonda Where DPB_PARTNONSTOCK = '.$TT[$n].') And DFL_KDDOK ='PPM'; its a kind of subselect (or oracle-ist usual call it nested query) and it works properly. as your sugesstion, I've ever put a simple script : $query = Select OSUSER From v$session; and the error is still occured. Ora_Parse failed (ORA-00942: table or view does not exist O.k. but at all... i'm very appreciate for your response michi .. any other ideas ? sincerely, edyk - Original Message - From: [EMAIL PROTECTED] Sent: Wednesday, June 19, 2002 1:26 PM Subject: Re: [PHP] SQL Statement this is the problem: $query = Select OSUSER From v$session Where USERNAME = (Select USER From DUAL) And AUDSID =(Select USERENV('SESSIONID') From DUAL); 1. mysql does NOT support subselects! 2. delete the in front of FROM 3. you have to quote v$session: v\$session start with a short query like: $query = select OSUSER from v\$session where USERNAME like 'foo'; then you have to recode the subselects with php-loops!!! michi -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] SQL Statement
Hello PHP mania, I'm a newcomer right here, i just wanna ask a litle dummy question : I put the SQL below into my SQL Plus editor : ** Select OSUSER From v$session Where USERNAME = (Select USER From DUAL) And AUDSID =(Select USERENV('SESSIONID') From DUAL) * and it works with the output is my logon OS. when I's connecting with PHP and ORACLE.DLL with the complete script below : * ?php PutEnv(ORACLE_SID=ORASID); Putenv(ORACLE_HOME=c:\ORANT); $connection = Ora_Logon (blabla/blablabla@dbname,); if ($connection == false){ echo Ora_ErrorCode($connection).: .Ora_Error($connection).BR; exit; } $cursor = Ora_Open ($connection); if ($cursor == false){ echo Ora_ErrorCode($connection).: .Ora_Error($connection).BR; exit; } #check admin $query = Select OSUSER From v$session Where USERNAME = (Select USER From DUAL) And AUDSID =(Select USERENV('SESSIONID') From DUAL) ; $result = Ora_Parse ($cursor, $query); if ($result == false){ echo Ora_ErrorCode($cursor).: .Ora_Error($cursor).BR; exit; } $result = Ora_Exec ($cursor); if ($result == false){ echo Ora_ErrorCode($cursor).: .Ora_Error($cursor).BR; exit; } while (Ora_Fetch_Into ($cursor, $values)){ $admin = $values[0]; } echo H2 USER Anda sekarang : $admin; Ora_Close ($cursor); Ora_Logoff ($connection); ? *** the error occured : Warning: Ora_Parse failed (ORA-00942: table or view does not exist -- while processing OCI function OPARSE) in c:\phpdev\www\project\test\osuser.php on line 22 942: ORA-00942: table or view does not exist -- while processing OCI function OPARSE why its happen ? in the right rule of oracle, if this table or view does not exist (V$SESSION) my SQL will not work in SQL Plus editor. Please, help me ASAP... TIA, edyk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] SQL statement for clearing a table
Hi all, anybody knows the mysql statement for clearing the contents of a table and lieving the table itself intact? Wilbert - Pas de Deux Van Mierisstraat 25 2526 NM Den Haag tel 070 4450855 fax 070 4450852 http://www.pdd.nl [EMAIL PROTECTED] - -- PHP General 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] SQL statement for clearing a table
DELETE FROM tablename or TRUNCATE TABLE tablename lenar. Wilbert Enserink [EMAIL PROTECTED] wrote in message 001101c0fb15$5daa49e0$[EMAIL PROTECTED]">news:001101c0fb15$5daa49e0$[EMAIL PROTECTED]... Hi all, anybody knows the mysql statement for clearing the contents of a table and lieving the table itself intact? Wilbert - Pas de Deux Van Mierisstraat 25 2526 NM Den Haag tel 070 4450855 fax 070 4450852 http://www.pdd.nl [EMAIL PROTECTED] - -- PHP General 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 General 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] SQL statement for clearing a table
DELETE FROM table_name; adamw - Original Message - From: Wilbert Enserink [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, June 22, 2001 1:17 PM Subject: [PHP] SQL statement for clearing a table Hi all, anybody knows the mysql statement for clearing the contents of a table and lieving the table itself intact? Wilbert - Pas de Deux Van Mierisstraat 25 2526 NM Den Haag tel 070 4450855 fax 070 4450852 http://www.pdd.nl [EMAIL PROTECTED] - -- PHP General 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 General 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] SQL statement for clearing a table
try delete from table; Julia Quoting Wilbert Enserink ([EMAIL PROTECTED]): Hi all, anybody knows the mysql statement for clearing the contents of a table and lieving the table itself intact? Wilbert - Pas de Deux Van Mierisstraat 25 2526 NM Den Haag tel 070 4450855 fax 070 4450852 http://www.pdd.nl [EMAIL PROTECTED] - -- PHP General 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] -- [ Julia Anne Case ] [Ships are safe inside the harbor, ] [Programmer at large] [ but is that what ships are really for.] [ Admining Linux ] [ To thine own self be true. ] [ Windows/WindowsNT ] [ Fair is where you take your cows to be judged. ] -- PHP General 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] SQL statement for clearing a table
This SQL statement: DELETE FROM myTable; That should work... pretty much, unless you give a WHERE statement, it will do whatever you want (UPDATE, SELECT, DELETE, etc) to every record in the table. --Jason -- PHP General 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] SQL statement for clearing a table
if you have a numbered primary key, delete from table where primary_key 0 ? (to be handled with care) py - Original Message - From: Wilbert Enserink [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, June 22, 2001 1:17 PM Subject: [PHP] SQL statement for clearing a table Hi all, anybody knows the mysql statement for clearing the contents of a table and lieving the table itself intact? Wilbert - Pas de Deux Van Mierisstraat 25 2526 NM Den Haag tel 070 4450855 fax 070 4450852 http://www.pdd.nl [EMAIL PROTECTED] - -- PHP General 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 General 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] SQL statement for clearing a table
delete from tablename -Original Message- From: Wilbert Enserink [mailto:[EMAIL PROTECTED]] Sent: Friday, June 22, 2001 07:18 To: [EMAIL PROTECTED] Subject: [PHP] SQL statement for clearing a table Hi all, anybody knows the mysql statement for clearing the contents of a table and lieving the table itself intact? Wilbert - Pas de Deux Van Mierisstraat 25 2526 NM Den Haag tel 070 4450855 fax 070 4450852 http://www.pdd.nl [EMAIL PROTECTED] - -- PHP General 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 General 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] SQL statement for clearing a table
isn't it, DELETE FROM tablename; q. -Original Message- From: Wilbert Enserink [mailto:[EMAIL PROTECTED]] Sent: Friday, June 22, 2001 6:18 AM To: [EMAIL PROTECTED] Subject: [PHP] SQL statement for clearing a table Hi all, anybody knows the mysql statement for clearing the contents of a table and lieving the table itself intact? Wilbert - Pas de Deux Van Mierisstraat 25 2526 NM Den Haag tel 070 4450855 fax 070 4450852 http://www.pdd.nl [EMAIL PROTECTED] - -- PHP General 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 General 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] SQL statement for clearing a table
$query = delete * from table; table is empty mailto:[EMAIL PROTECTED] -Original Message- From: Wilbert Enserink [mailto:[EMAIL PROTECTED]] Sent: Freitag, 22. Juni 2001 14:18 To: [EMAIL PROTECTED] Subject: [PHP] SQL statement for clearing a table Hi all, anybody knows the mysql statement for clearing the contents of a table and lieving the table itself intact? Wilbert - Pas de Deux Van Mierisstraat 25 2526 NM Den Haag tel 070 4450855 fax 070 4450852 http://www.pdd.nl [EMAIL PROTECTED] - -- PHP General 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 General 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] SQL statement for clearing a table
No one has yet mentioned TRUNCATE TABLE x which is a lot faster on large tables because even DELETE FROM evaluates each row where TRUNCATE just clears the data. - Russ --- Toolshed Computer Productions - Professional PHP Hosting Hosting - Dedicated Servers - Design - Programming http://www.toolshed51.com -Original Message- From: Wilbert Enserink [mailto:[EMAIL PROTECTED]] Sent: Friday, June 22, 2001 5:18 AM To: [EMAIL PROTECTED] Subject: [PHP] SQL statement for clearing a table Hi all, anybody knows the mysql statement for clearing the contents of a table and lieving the table itself intact? Wilbert - Pas de Deux Van Mierisstraat 25 2526 NM Den Haag tel 070 4450855 fax 070 4450852 http://www.pdd.nl [EMAIL PROTECTED] - -- PHP General 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 General 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] SQL statement in PHP
Can anybody help with this If I put the following statement in PHP my browser tells me that there is an error on the line. //create sql statement $sql = "select Business_Name,Trading_Details where Business_Type = "Consultancy" and Bassingbourn != "0" from Main"; If I remove where Business_Type = "Consultancy" and Bassingbourn != "0" It work fine, but no variation of the above line works. Am I missing something really obvious. Please help as I am very quickly becoming bald. Matt. -- PHP General 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] SQL statement in PHP
Matt, Maybe the mistake is at the quotes... Just replace the double quotes inside the select statement by single quotes... Try something like: snip code $sql = "select Business_Name,Trading_Details where Business_Type='Consultancy' and Bassingbourn != '0' from Main"; /snip code ^ ^ ^ ^ Matt Davis wrote: Can anybody help with this If I put the following statement in PHP my browser tells me that there is an error on the line. //create sql statement $sql = "select Business_Name,Trading_Details where Business_Type = "Consultancy" and Bassingbourn != "0" from Main"; If I remove where Business_Type = "Consultancy" and Bassingbourn != "0" It work fine, but no variation of the above line works. Am I missing something really obvious. Please help as I am very quickly becoming bald. Matt. -- PHP General 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]