[PHP-DB] UPDATE IF
I have a table in a mysql db that has 3 columns that I want to insert (update) data if the rowcolumn is empty (IS NULL). However I want to start at the first column, if this column is not null, then move to insert the data into column 2, if that is empty, then insert data into column 3. I have been looking over the manual and boards but am not finding a solution. Can anyone point me in the right direction. -- Gary __ Information from ESET Smart Security, version of virus signature database 5952 (20110314) __ The message was checked by ESET Smart Security. http://www.eset.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] UPDATE IF
On 14 March 2011 15:42, Gary gp...@paulgdesigns.com wrote: I have a table in a mysql db that has 3 columns that I want to insert (update) data if the rowcolumn is empty (IS NULL). However I want to start at the first column, if this column is not null, then move to insert the data into column 2, if that is empty, then insert data into column 3. I have been looking over the manual and boards but am not finding a solution. Can anyone point me in the right direction. -- Gary __ Information from ESET Smart Security, version of virus signature database 5952 (20110314) __ The message was checked by ESET Smart Security. http://www.eset.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php You would have to code it. I'm not a mysql guru (MSSQL is what I use), but ... UPDATE Table SET Column3 = CASE WHEN Column3 IS NULL AND Column2 IS NOT NULL AND Column1 IS NOT NULL THEN Value ELSE Column3 END, Column2 = CASE WHEN Column2 IS NULL AND Column1 IS NOT NULL THEN Value ELSE Column2 END, Column1 = CASE WHEN Column1 IS NULL THEN Value ELSE Column1 END WHERE IDColumn = ID something like that? -- Richard Quadling Twitter : EE : Zend @RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] UPDATE query
I am wondering if there is a way to do an UPDATE query where only some of the text changes. The column I need to modify is named “toll_free” What I need to search for is: 800- I need it to replace it with is 1-800- - BUT I don’t want to change instances of 1-800- - I need to leave the rest of the toll free phone number in tact. Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
Re: [PHP-DB] UPDATE query
On Tue, Mar 8, 2011 at 11:16 AM, Ron Piggott ron.pigg...@actsministries.org wrote: I am wondering if there is a way to do an UPDATE query where only some of the text changes. The column I need to modify is named “toll_free” What I need to search for is: 800- I need it to replace it with is 1-800- - BUT I don’t want to change instances of 1-800- - I need to leave the rest of the toll free phone number in tact. Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info Ron, I would strongly suggest that you be consistent in the data. Pick one version (1-800 or 800-) and stick with it. Its a simple matter to do a one time replace on that field to make them all consistent and from there on your programming logic for the update you want to run is then made much simpler. You can do an update with a LIKE but it may update more than what you want to update table set toll_free = '$some_value' where toll_free like '%800-###-' -- Bastien Cat, the other other white meat -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] UPDATE query
I was wondering this Bastien. Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info -Original Message- From: Bastien Koert Sent: Tuesday, March 08, 2011 11:20 AM To: Ron Piggott Cc: php-db@lists.php.net Subject: Re: [PHP-DB] UPDATE query On Tue, Mar 8, 2011 at 11:16 AM, Ron Piggott ron.pigg...@actsministries.org wrote: I am wondering if there is a way to do an UPDATE query where only some of the text changes. The column I need to modify is named “toll_free” What I need to search for is: 800- I need it to replace it with is 1-800- - BUT I don’t want to change instances of 1-800- - I need to leave the rest of the toll free phone number in tact. Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info Ron, I would strongly suggest that you be consistent in the data. Pick one version (1-800 or 800-) and stick with it. Its a simple matter to do a one time replace on that field to make them all consistent and from there on your programming logic for the update you want to run is then made much simpler. You can do an update with a LIKE but it may update more than what you want to update table set toll_free = '$some_value' where toll_free like '%800-###-' -- Bastien Cat, the other other white meat -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] UPDATE query
I found a way to do this Bastien: UPDATE `database`.`table` SET `toll_free` = CONCAT( '1-', `toll_free` ) WHERE `toll_free` LIKE '866-%' Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info -Original Message- From: Bastien Koert Sent: Tuesday, March 08, 2011 11:20 AM To: Ron Piggott Cc: php-db@lists.php.net Subject: Re: [PHP-DB] UPDATE query On Tue, Mar 8, 2011 at 11:16 AM, Ron Piggott ron.pigg...@actsministries.org wrote: I am wondering if there is a way to do an UPDATE query where only some of the text changes. The column I need to modify is named “toll_free” What I need to search for is: 800- I need it to replace it with is 1-800- - BUT I don’t want to change instances of 1-800- - I need to leave the rest of the toll free phone number in tact. Ron The Verse of the Day “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info Ron, I would strongly suggest that you be consistent in the data. Pick one version (1-800 or 800-) and stick with it. Its a simple matter to do a one time replace on that field to make them all consistent and from there on your programming logic for the update you want to run is then made much simpler. You can do an update with a LIKE but it may update more than what you want to update table set toll_free = '$some_value' where toll_free like '%800-###-' -- Bastien Cat, the other other white meat -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Update uploaded file help
Hi all, I am new to php, I am just learning it at the moment. I hope you can help me with my code. Thanks in advance! I want the page to allow users to edit or delete an existing uploaded file. But after testing the script in the browser the delete option is working 10 out 0f 10; but the edit is not. I don't know what I am doing wrong. Here's my code: ?php # edit_file.php // This page allows users to edit or delete existing URL records. $page_title = 'Edit a file'; // Check for a URL ID. if (isset($_GET['uid'])) { // Page is first accessed. $uid = (int) $_GET['uid']; } elseif (isset($_POST['uid'])) { // Form has been submitted. $uid = (int) $_POST['uid']; } else { // Big problem. $uid = 0; } //!-- BEGIN if ($uid = 0)-- if ($uid = 0) { // Do not proceed! echo 'pfont color=redThis page has been accessed incorrectly!/font/p'; include ('./includes/footer.html'); exit(); // Terminate execution of the script. }//!-- END if ($uid = 0)-- require_once ('./mysql_connect.php'); // Connect to the database. $counter=1; $name= $_FILES[$filename]['name']; $size=$_FILES[$filename]['size']; $type=$_FILES[$filename]['type']; $temp_name=$_FILES[$filename]['temp_name']; $error=$_FILES[$filename]['error']; //!-- BEGIN if (isset($_POST['submitted']))-- if (isset($_POST['submitted'])) { // Handle the form. //!-- BEGIN if ($_POST['which'] == 'delete')-- if ($_POST['which'] == 'delete') { // Delete the record. // This part is working fine. } else { // Edit the uploaded file . for ($i = 0; $i $counter; $i++) { // Handle the uploaded file. $filename = 'upload' . $i; $description = 'description' . $i; // Check for a file. if (isset($_FILES[$filename]) ($_FILES[$filename]['error'] != 2)) { // Check for a description if (!empty($_POST[$description])) { $d = ' . escape_data($_POST [$description]) . '; } else { $d = 'NULL } // Update the record to the database. $query = UPDATE uploads SET file_name='$name', file_size='$size', file_type='$type', description='$d' WHERE upload_id=$uid; $result = mysql_query ($query); if ($result) { // Return the upload_id from the database. $upload_id = mysql_insert_id(); // Move the file over. if (move_uploaded_file($_FILES[$filename]['tmp_name'], uploads/$upload_id)) { echo 'pNew file ' . ($i + 1) . ' has been uploaded!/p'; } else { // File could not be moved. echo 'pfont color=redFile ' . ($i + 1) . 'could not be moved./font/p'; }//end move_uploaded_file } else { // If the query did not run OK echo 'pfont color=redYoursubmission could not be processed due to a system error. We apologize for any inconvenience./font/p'; }//end if(result) } // End of if (isset($the_file)... } // End of FOR loop. } // End of Edit/Delete if-else. } // End of the main submitted conditional. // - DISPLAY THE FORM - //Retrieve the uploads's current information. $query = SELECT file_name, ROUND(file_size/1024) AS fs, file_type, description, DATE_FORMAT(date_entered, '%e %M %Y') AS d FROM uploads WHERE upload_id=$uid; $result = mysql_query ($query); //get all of the information first record list($file_name, $fs, $file_type, $d, $de)= mysql_fetch_array ($result, MYSQL_NUM); ? form action=edit_file.php method=post input type=hidden name=MAX_FILE_SIZE value=524288 / fieldsetlegendEdit a File:/legend pbSelect One:/b input type=radio name=which value=edit checked=checked / Edit input type=radio name=which value=delete / Delete/p ?php //Retrieve the uploads's current information. $query = SELECT file_name, ROUND(file_size/1024) AS fs, file_type, description, DATE_FORMAT(date_entered, '%e %M %Y') AS de FROM uploads WHERE upload_id=$uid; $result = mysql_query ($query); //print the current file's information in the browser while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { echo 'table border=0 width=100% cellspacing=3 cellpadding=3 align=center tr td align=left width=20%font size=+1File Name/font/td td align=center width=20%font size=+1File Size/font/td
[PHP-DB] UPDATE lot of registers
Hello everyone I dont know how to update a lot of registers on one table. I have two tables: Students and Signatures STUDENTS has got: id / name / signature SIGNATURES has got: id / name / classroom I want to use words and not numbers to write the signature on the STUDENTS table. Ive got a long list with signatures, and I must to change one signature... How do I do update SIGNATURE AND STUDENTS tables o sam time if 100 students has got the same signature and must update automatically? Thanks a lot + _ // Emiliano Boragina _ // Diseño Comunicación // + _ // emiliano.borag...@gmail.com / // 15 40 58 60 02 /// + _
Re: [PHP-DB] UPDATE lot of registers
Emiliano Boragina wrote: Hello everyone… I dont know how to update a lot of registers on one table. I have two tables: Students and Signatures STUDENTS has got: id / name / signature SIGNATURES has got: id / name / classroom I want to use words and not numbers to write the signature on the STUDENTS table. I’ve got a long list with signatures, and I must to change one signature... How do I do update SIGNATURE AND STUDENTS tables o sam time if 100 students has got the same signature and must update automatically? The better question would be why do you need to update two tables with the same information? Updating two tables at the same time will require using a transaction. If you're using mysql, this means the table has to be an 'innodb' table. The default 'myisam' doesn't support transactions. If you don't use a transaction, you'll potentially lose your changes. update signatures set name='New name here' where id in (1,2,3,4,5,6,7,8,9,10); server dies (power loss, hard drive crash, someone reboots it) after it updates record 5 but before it updates record 6. You now have half the table updated. Even worse would be it updates the first table but not the second - now your data is inconsistent. You can update multiple records at once quite easily. Assuming 'id' is a unique (or primary) key: Begin; update signatures set name='New name here' where id in (1,2,3,4,5,6,7,8,9,10); update students set name='New name here' where id in (1,2,3,4,5,6,7,8,9,10); commit; -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] UPDATE lot of registers
[ please always send to the mailing list ] Emiliano Boragina wrote: Hi Chris, thanks for your ask... This doubt was beacause I have two tables... one with categories (category id and category name), and the other with products (product id, product name, category id which the prod belong). The client, sometimes, change the category name. To there no problem using the in the in the products table. I'm still confused. If you're changing the category name, you only need to change the categories table. update categories set name='new name here' where category_id='X'; But, I must do a finder to search and list the products under category name which it belongs, it name and other items... So the finder is not so easy to do... To understand this, it'd be easier in two steps to start off with: 1) Get the category_id: select category_id from categories where category_name='XX'; 2) Then get the products: select product_id, product_name from products where category_id='X'; That can be combined in to one query: select product_id, product_name from products p inner join categories c on (p.category_id=c.category_id) where c.category_name='XX'; Or am I still missing a piece of the puzzle? -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] UPDATE instead of INSERT INTO
http://dev.mysql.com/doc/refman/5.0/en/replace.html Or as previously mentined: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html -Original Message- From: Ron Piggott [mailto:ron@actsministries.org] Sent: Saturday, September 05, 2009 1:19 PM To: php-db@lists.php.net Subject: [PHP-DB] UPDATE instead of INSERT INTO I made an application which lets users rate web site content. At the heart of the application is this mySQL query: INSERT INTO `ratings` ( `reference` , `content_type` , `content_reference` , `language` , `ip_address` , `rating_timestamp` , `rating` ) VALUES ( NULL , '$content_type','$content_reference' , '$language', '$ip_address', NOW( ) , '$user_rating' ); I am wondering if there is a way to check if the user has already voted (IE If there is already a matching record with the same content_type, content_reference, language, ip_address values) so all that happens is an UPDATE to the rating field? Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] UPDATE instead of INSERT INTO
I made an application which lets users rate web site content. At the heart of the application is this mySQL query: INSERT INTO `ratings` ( `reference` , `content_type` , `content_reference` , `language` , `ip_address` , `rating_timestamp` , `rating` ) VALUES ( NULL , '$content_type','$content_reference' , '$language', '$ip_address', NOW( ) , '$user_rating' ); I am wondering if there is a way to check if the user has already voted (IE If there is already a matching record with the same content_type, content_reference, language, ip_address values) so all that happens is an UPDATE to the rating field? Ron
Re: [PHP-DB] UPDATE instead of INSERT INTO
INSERT INTO ON DUPLICATE KEY UPDATE is what you're looking for. On 9/5/09, Ron Piggott ron@actsministries.org wrote: I made an application which lets users rate web site content. At the heart of the application is this mySQL query: INSERT INTO `ratings` ( `reference` , `content_type` , `content_reference` , `language` , `ip_address` , `rating_timestamp` , `rating` ) VALUES ( NULL , '$content_type','$content_reference' , '$language', '$ip_address', NOW( ) , '$user_rating' ); I am wondering if there is a way to check if the user has already voted (IE If there is already a matching record with the same content_type, content_reference, language, ip_address values) so all that happens is an UPDATE to the rating field? Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] UPDATE instead of INSERT INTO
Thanks - Original Message - From: Waynn Lue waynn...@gmail.com To: Ron Piggott ron@actsministries.org; php-db@lists.php.net Sent: Saturday, September 05, 2009 4:45 PM Subject: Re: [PHP-DB] UPDATE instead of INSERT INTO INSERT INTO ON DUPLICATE KEY UPDATE is what you're looking for. On 9/5/09, Ron Piggott ron@actsministries.org wrote: I made an application which lets users rate web site content. At the heart of the application is this mySQL query: INSERT INTO `ratings` ( `reference` , `content_type` , `content_reference` , `language` , `ip_address` , `rating_timestamp` , `rating` ) VALUES ( NULL , '$content_type','$content_reference' , '$language', '$ip_address', NOW( ) , '$user_rating' ); I am wondering if there is a way to check if the user has already voted (IE If there is already a matching record with the same content_type, content_reference, language, ip_address values) so all that happens is an UPDATE to the rating field? Ron No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.409 / Virus Database: 270.13.78/2347 - Release Date: 09/05/09 05:51:00 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] UPDATE syntax
Can anyone see something wrong with this syntax? UPDATE `advertisements` INNER JOIN `advertisements_rate_plans` ON `advertisements.rate_plan` = `advertisements_rate_plans.reference` SET `advertisements.displayed` = 0 WHERE `advertisements.start_date` = '2007-12-13' AND `advertisements.end_date` = '2007-12-13' AND `advertisements.approved` =1 AND `advertisements.paid` =1 AND `advertisements.displayed` =1 AND `advertisements_rate_plans.type` =2 I am getting this error message: #1054 - Unknown column 'advertisements.displayed' in 'field list' There is a column called displayed in table advertisements. I am on the http://dev.mysql.com/doc/refman/5.0/en/update.html page and I am missing something. Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] UPDATE syntax
Hi, I'm a beginner at PHP and MySql but a long time MS SQL server user. Maybe it should be `advertisements`.`displayed` instead? I just did a test and my Query browser doesn't like `interestcodes.id` but likes `interestcodes`.`id`. Notice the quotes (accents) on each side to the period. Gary -Original Message- From: Ron Piggott [mailto:[EMAIL PROTECTED] Sent: Thu, December 13, 2007 7:20 PM To: php-db@lists.php.net Subject: [PHP-DB] UPDATE syntax Can anyone see something wrong with this syntax? UPDATE `advertisements` INNER JOIN `advertisements_rate_plans` ON `advertisements.rate_plan` = `advertisements_rate_plans.reference` SET `advertisements.displayed` = 0 WHERE `advertisements.start_date` = '2007-12-13' AND `advertisements.end_date` = '2007-12-13' AND `advertisements.approved` =1 AND `advertisements.paid` =1 AND `advertisements.displayed` =1 AND `advertisements_rate_plans.type` =2 I am getting this error message: #1054 - Unknown column 'advertisements.displayed' in 'field list' There is a column called displayed in table advertisements. I am on the http://dev.mysql.com/doc/refman/5.0/en/update.html page and I am missing something. Ron -- 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] UPDATE syntax
Sorry, I didn't read the whole post, but wanted to point something out: `interestcodes`.`id` is valid interestcodes.id is valid `interestcodes.id` is going to try to find a column named itnerestcodes.id somewhere with no table association. The backticks/single-quotes/whatever-you-wanna-call-them are used to be explicit with a name. -TG - Original Message - From: Gary Wardell [EMAIL PROTECTED] To: php-db@lists.php.net Date: Thu, 13 Dec 2007 19:33:57 -0500 Subject: RE: [PHP-DB] UPDATE syntax Hi, I'm a beginner at PHP and MySql but a long time MS SQL server user. Maybe it should be `advertisements`.`displayed` instead? I just did a test and my Query browser doesn't like `interestcodes.id` but likes `interestcodes`.`id`. Notice the quotes (accents) on each side to the period. Gary -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] UPDATE with INNER JOIN?
Is it possible to do an UPDATE with an INNER JOIN --- I want the WHERE to use 2 tables in selecting which rows are going to be updated in table1. Could you provide me with a simple example if this is possible using the following variables: table1 table2 table1.field1 table1.field2 table2.field1 Thanks Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] UPDATE with INNER JOIN?
Ron Piggott wrote: Is it possible to do an UPDATE with an INNER JOIN --- I want the WHERE to use 2 tables in selecting which rows are going to be updated in table1. http://dev.mysql.com/doc/refman/5.0/en/update.html You can also perform UPDATE operations covering multiple tables. However, you cannot use ORDER BY or LIMIT with a multiple-table UPDATE. The table_references clause lists the tables involved in the join. Its syntax is described in Section 11.2.7.1, “JOIN Syntax”. Here is an example: UPDATE items,month SET items.price=month.price WHERE items.id=month.id; -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Update time/date stamp
I would like to print the date and time my database was last updated but don't know the query for that. Ilooked at several websites that list MySQL variables but didn't find any that seemed to be what I want. Can anyone help? Thanks. --- Pete Holsberg Columbus, NJ --- When the people are afraid of the government, that's tyranny. But when the government is afraid of the people, that's liberty. -- Thomas Jefferson -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Update time/date stamp
any particular table? or just the db in general? bastien From: Pete Holsberg [EMAIL PROTECTED] To: php-db@lists.php.net Subject: [PHP-DB] Update time/date stamp Date: Wed, 2 Feb 2005 10:56:08 -0500 (EST) I would like to print the date and time my database was last updated but don't know the query for that. Ilooked at several websites that list MySQL variables but didn't find any that seemed to be what I want. Can anyone help? Thanks. --- Pete Holsberg Columbus, NJ --- When the people are afraid of the government, that's tyranny. But when the government is afraid of the people, that's liberty. -- Thomas Jefferson -- 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] Update time/date stamp
On Wed, 2 Feb 2005, Bastien Koert wrote: any particular table? or just the db in general? It has just one table. --- Pete Holsberg Columbus, NJ --- When the people are afraid of the government, that's tyranny. But when the government is afraid of the people, that's liberty. -- Thomas Jefferson -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Update time/date stamp
is there a timestamp or data time field in the table? If so just quuery that field for the max value select max(dateTimeFieldName) from TableName bastien From: Pete Holsberg [EMAIL PROTECTED] To: Bastien Koert [EMAIL PROTECTED] CC: php-db@lists.php.net Subject: RE: [PHP-DB] Update time/date stamp Date: Wed, 2 Feb 2005 13:57:50 -0500 (EST) On Wed, 2 Feb 2005, Bastien Koert wrote: any particular table? or just the db in general? It has just one table. --- Pete Holsberg Columbus, NJ --- When the people are afraid of the government, that's tyranny. But when the government is afraid of the people, that's liberty. -- Thomas Jefferson -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Update time/date stamp
On Wed, 2 Feb 2005, Bastien Koert wrote: is there a timestamp or data time field in the table? If so just quuery that field for the max value select max(dateTimeFieldName) from TableName No there isn't. When I view the table with phpMyAdmin, it displays a table that includes time/date stamps for creation, last updated and last checked. Thanks. --- Pete Holsberg Columbus, NJ --- When the people are afraid of the government, that's tyranny. But when the government is afraid of the people, that's liberty. -- Thomas Jefferson -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Update time/date stamp
so select max(last_updated) bastien From: Pete Holsberg [EMAIL PROTECTED] To: php-db@lists.php.net Subject: RE: [PHP-DB] Update time/date stamp Date: Wed, 2 Feb 2005 14:06:35 -0500 (EST) On Wed, 2 Feb 2005, Bastien Koert wrote: is there a timestamp or data time field in the table? If so just quuery that field for the max value select max(dateTimeFieldName) from TableName No there isn't. When I view the table with phpMyAdmin, it displays a table that includes time/date stamps for creation, last updated and last checked. Thanks. --- Pete Holsberg Columbus, NJ --- When the people are afraid of the government, that's tyranny. But when the government is afraid of the people, that's liberty. -- Thomas Jefferson -- 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] Update time/date stamp
On Wed, 2 Feb 2005, Bastien Koert wrote: so select max(last_updated) bastien Error SQL-query : SELECT max( last_updated ) FROM Directory MySQL said: Unknown column 'last_updated' in 'field list' --- I see that I was unclear in my description of what I said. My table is called directory. When I open directory with phpMyAdmin, it displays several tables: 1) the structure of directory 2) Indexes 3) space usage, and 4) row statistic. In the fourth table, it displays the three time-date stamps along with format, number of rows, row length and row size. So the last-updated information is in A table but not the table called directory. Sorry for the confusion. From: Pete Holsberg [EMAIL PROTECTED] To: php-db@lists.php.net Subject: RE: [PHP-DB] Update time/date stamp Date: Wed, 2 Feb 2005 14:06:35 -0500 (EST) On Wed, 2 Feb 2005, Bastien Koert wrote: is there a timestamp or data time field in the table? If so just quuery that field for the max value select max(dateTimeFieldName) from TableName No there isn't. When I view the table with phpMyAdmin, it displays a table that includes time/date stamps for creation, last updated and last checked. Thanks. --- Pete Holsberg Columbus, NJ --- When the people are afraid of the government, that's tyranny. But when the government is afraid of the people, that's liberty. -- Thomas Jefferson -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php --- Pete Holsberg Columbus, NJ --- When the people are afraid of the government, that's tyranny. But when the government is afraid of the people, that's liberty. -- Thomas Jefferson -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Update time/date stamp
'show table status' will return a record set that you can parse to get the last update time bastien From: Pete Holsberg [EMAIL PROTECTED] To: php-db@lists.php.net Subject: RE: [PHP-DB] Update time/date stamp Date: Wed, 2 Feb 2005 15:00:34 -0500 (EST) On Wed, 2 Feb 2005, Bastien Koert wrote: so select max(last_updated) bastien Error SQL-query : SELECT max( last_updated ) FROM Directory MySQL said: Unknown column 'last_updated' in 'field list' --- I see that I was unclear in my description of what I said. My table is called directory. When I open directory with phpMyAdmin, it displays several tables: 1) the structure of directory 2) Indexes 3) space usage, and 4) row statistic. In the fourth table, it displays the three time-date stamps along with format, number of rows, row length and row size. So the last-updated information is in A table but not the table called directory. Sorry for the confusion. From: Pete Holsberg [EMAIL PROTECTED] To: php-db@lists.php.net Subject: RE: [PHP-DB] Update time/date stamp Date: Wed, 2 Feb 2005 14:06:35 -0500 (EST) On Wed, 2 Feb 2005, Bastien Koert wrote: is there a timestamp or data time field in the table? If so just quuery that field for the max value select max(dateTimeFieldName) from TableName No there isn't. When I view the table with phpMyAdmin, it displays a table that includes time/date stamps for creation, last updated and last checked. Thanks. --- Pete Holsberg Columbus, NJ --- When the people are afraid of the government, that's tyranny. But when the government is afraid of the people, that's liberty. -- Thomas Jefferson -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php --- Pete Holsberg Columbus, NJ --- When the people are afraid of the government, that's tyranny. But when the government is afraid of the people, that's liberty. -- Thomas Jefferson -- 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] Update multiple records
--- Jochem Maas [EMAIL PROTECTED] wrote: if (count($inds) 0 AND is_array($inds)) { $ind = '.implode(',', $inds).'; } looks like you want to do a select statement with $ind in the form of SELECT * FROM yourtable WHERE yourfield IN ($inds) Adding the IN clause did not help in any way. What I've been able to get so far is only the first record returned from the database (even though 2 exists) Using this result set it is picking up the correct user records: $query_rsLPINDS = sprintf(SELECT LurkProfiles_Industries.ProfileID, LurkProfiles_Industries.IndID, LurkerProfiles.ProfileID FROM LurkProfiles_Industries INNER JOIN LurkerProfiles ON (LurkProfiles_Industries.ProfileID = LurkerProfiles.ProfileID) INNER JOIN SignUp ON (LurkerProfiles.LurkID = SignUp.SignUpID) INNER JOIN StaIndTypes ON LurkProfiles_Industries.IndID = StaIndTypes.CareerIDs) WHERE LurkerProfiles.ProfileID = %s and LurkID = %s and hash = '%s', $colname__rsLPINDS,$colname2__rsLPINDS,$colname3__rsLPINDS); I've created the variable for IndID like this : $IndID = $rsLPINDS-Fields('IndID'); which when I do a print_r($IndID), prints out the first record. So at this point I'm trying to figure out what I need to make $IndID an array. Stuart -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Update multiple records
Stumbling across some posts online, I noticed [some of] my messages were being sent as evil ugly HTML - so I'm going to do my best to post from Thunderbird for the list. Gotta keep OE configured as is for all the lovely meeting scheduling. Stuart Felenstein wrote: --- Jochem Maas [EMAIL PROTECTED] wrote: if (count($inds) 0 AND is_array($inds)) { $ind = '.implode(',', $inds).'; } looks like you want to do a select statement with $ind in the form of SELECT * FROM yourtable WHERE yourfield IN ($inds) Adding the IN clause did not help in any way. What I've been able to get so far is only the first record returned from the database (even though 2 exists) Using this result set it is picking up the correct user records: $query_rsLPINDS = sprintf(SELECT LurkProfiles_Industries.ProfileID, LurkProfiles_Industries.IndID, LurkerProfiles.ProfileID FROM LurkProfiles_Industries INNER JOIN LurkerProfiles ON (LurkProfiles_Industries.ProfileID = LurkerProfiles.ProfileID) INNER JOIN SignUp ON (LurkerProfiles.LurkID = SignUp.SignUpID) INNER JOIN StaIndTypes ON LurkProfiles_Industries.IndID = StaIndTypes.CareerIDs) WHERE LurkerProfiles.ProfileID = %s and LurkID = %s and hash = '%s', $colname__rsLPINDS,$colname2__rsLPINDS,$colname3__rsLPINDS); I've created the variable for IndID like this : $IndID = $rsLPINDS-Fields('IndID'); which when I do a print_r($IndID), prints out the first record. So at this point I'm trying to figure out what I need to make $IndID an array. Stuart If I remember correctly, you're using some kind of database class that we spent some time pinning down how to pull the values out of. If that is indeed the case - you need to request the next record to be able to pull that records value for the field in. I don't remember what package you were using - but whatever that is, I think you need to be running across your result set (foreach || while ) and requesting each result as you go (or you need to access the property of the object that stores the whole result set/etc.). Whatever the case - I'm like 95% sure that -Fields('fieldname') was for last pulled rows value of this field or similar and was for pulling record by record. Cheers, - Martin Norland, Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Update multiple records
Having a problem here with updating multiple records in a table. First the table is like such: +-+--+ | RecordID [int] | IndID [int] | +-+--+ There is no auto inc column. User has a record number and then can have multiple rows in table with the same record number. i.e: +-+--+ | RecordID [int] | IndID [int] | +-+--+ |101 | 5 | +-+--+ |101 | 10 | +-+--+ |101 | 22 | +-+--+ I've setup the sql result set as such: SELECT * FROM `Profiles` INNER JOIN `Profiles_Industries` ON Profiles.ProfileID = Profiles_Industries.ProfileID) INNER JOIN IndTypes ON Profiles_Industries.IndID = IndTypes.CareerIDs) INNER JOIN SU ON (Profiles.kID = `SU.kID) WHERE Profiles.ProfileID = colname I have a list menu (multi select) using the IndTypes table and have named the element IndID[] I have the array variable to grab the IndID values from the record set. inds = implode(,,$_POST['IndID']); And I am attempting to highlight the IndID that exist in the users records in the multi select: ?php $selected = explode(,,$rsLPInds-Fields('IndID')); while(!$rsInds-EOF){ ? option value=?php echo $rsInds-Fields('CareerIDs')? ?php if (in_array($rsInds-Fields('IndID'),$selected)) { echo selected; } ? ?php echo $rsInds-Fields('CareerCategories')?/option ?php I'm sure this is all most likely confusing. :) Typical post from me ...sorry. None of this is working though. Perhaps updating multiple records is more difficult then I first imagined. Can anyone lend some advice , assistance ? Thank you, Stuart -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Update multiple records
-Original Message- From: Stuart Felenstein [mailto:[EMAIL PROTECTED] Sent: Monday, January 10, 2005 10:40 AM Subject: [PHP-DB] Update multiple records Having a problem here with updating multiple records in a table. [snip] Lets see - where to begin... SELECT * FROM `Profiles` INNER JOIN `Profiles_Industries` ON Profiles.ProfileID = Profiles_Industries.ProfileID) INNER JOIN IndTypes ON Profiles_Industries.IndID = IndTypes.CareerIDs) INNER JOIN SU ON (Profiles.kID = `SU.kID) WHERE Profiles.ProfileID = colname [snip] You list nothing indicating any sort of update, so forget that for the time being until things are working. That's your end goal, but it doesn't look to be what you're fighting with now. You have a very complicated (perhaps messy, perhaps not - it might all be required) select, and then you're trying to pull that information out and populate a select. INNER JOIN SU ON (Profiles.kID = `SU.kID) [snip] This line in particular clearly warrants attention. Cookie to whoever guesses what the' problem` might 'be. ?php $selected = explode(,,$rsLPInds-Fields('IndID')); while(!$rsInds-EOF){ ? option value=?php echo $rsInds-Fields('CareerIDs')? ?php if (in_array($rsInds-Fields('IndID'),$selected)) { echo selected; } ? ?php echo $rsInds-Fields('CareerCategories')?/option [snip] explode() turns an array into a string, you'll have trouble in_array()ing a string. You may want to start there. You don't list the select bit around your select, I'm assuming it's there - what actual behavior/output are you seeing? View source to be sure - you have to specify a select is a multiselect, otherwise it will just be a dropdown and select the first one you say is selected (or maybe the last, I've not messed with it in some time - actually, it's browser dependant, but I think they all tend to behave the same.) rsLPInds-Fields and rsInds-Fields - this is two objects/tables/etc. - you only mention the one, so it's hard to follow what data's where - to be sure. Try that stuff and get back to us. You definitely want to be using print_r() (possibly in pre tags) to see what information is actually stored in the results of your select statement, if there is any question there. - Martin Norland, Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Update multiple records
Norland, Martin wrote: -Original Message- From: Stuart Felenstein [mailto:[EMAIL PROTECTED] Sent: Monday, January 10, 2005 10:40 AM Subject: [PHP-DB] Update multiple records Having a problem here with updating multiple records in a table. [snip] Lets see - where to begin... SELECT * FROM `Profiles` INNER JOIN `Profiles_Industries` ON Profiles.ProfileID = Profiles_Industries.ProfileID) INNER JOIN IndTypes ON Profiles_Industries.IndID = IndTypes.CareerIDs) INNER JOIN SU ON (Profiles.kID = `SU.kID) WHERE Profiles.ProfileID = colname [snip] You list nothing indicating any sort of update, so forget that for the time being until things are working. That's your end goal, but it doesn't look to be what you're fighting with now. You have a very complicated (perhaps messy, perhaps not - it might all be required) select, and then you're trying to pull that information out and populate a select. INNER JOIN SU ON (Profiles.kID = `SU.kID) [snip] This line in particular clearly warrants attention. Cookie to whoever guesses what the' problem` might 'be. that will be a lone backtick then :-) now where's the cookiejar ;-) ?php $selected = explode(,,$rsLPInds-Fields('IndID')); while(!$rsInds-EOF){ ? option value=?php echo $rsInds-Fields('CareerIDs')? ?php if (in_array($rsInds-Fields('IndID'),$selected)) { echo selected; } ? ?php echo $rsInds-Fields('CareerCategories')?/option [snip] explode() turns an array into a string, you'll have trouble in_array()ing a string. You may want to start there. er Martin, no it does not. explode creates an array. the manual defines it as thus: array explode ( string separator, string string [, int limit]) possibly you got mixed up with implode() (or join() which is an alias of implode()) You don't list the select bit around your select, I'm assuming it's there - what actual behavior/output are you seeing? View source to be sure - you have to specify a select is a multiselect, otherwise it will just be a dropdown and select the first one you say is selected (or maybe the last, I've not messed with it in some time - actually, it's browser dependant, but I think they all tend to behave the same.) rsLPInds-Fields and rsInds-Fields - this is two objects/tables/etc. - you only mention the one, so it's hard to follow what data's where - to be sure. Try that stuff and get back to us. You definitely want to be using print_r() (possibly in pre tags) to see what information is actually stored in the results of your select statement, if there is any question there. - Martin Norland, Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Update multiple records
-Original Message- From: Jochem Maas [mailto:[EMAIL PROTECTED] Sent: Monday, January 10, 2005 11:59 AM Subject: Re: [PHP-DB] Update multiple records [snip] This line in particular clearly warrants attention. Cookie to whoever guesses what the' problem` might 'be. that will be a lone backtick then :-) now where's the cookiejar ;-) [snip] All my cookies are stored on my local drives - sorry, I can't give you access. explode() turns an array into a string, you'll have trouble in_array()ing a string. You may want to start there. er Martin, no it does not. explode creates an array. the manual defines it as thus: array explode ( string separator, string string [, int limit]) possibly you got mixed up with implode() (or join() which is an alias of implode()) [snip] That's what I get for troubleshooting before lunch! My apologies to the list and to anyone unfortunate enough to find that post with a search! I always get implode/explode mixed up (I think of an array as more organized than a string - so I think of a string as just an array that's been blown to smithereens). I guess the idea is that an array is like a bunch of pieces of a string after it's been blown up. I even checked the manual and dyslexically read it. 'Spose I owe everyone a cookie, and Jochem Maas two (plus first pick!). Cheers, - Martin Norland, Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Update multiple records
Stuart Felenstein wrote: Having a problem here with updating multiple records in a table. as Martin Norland (a heavy hitter on this list AFAIKT) already stated you don't seem to be at the stage of actually doing an update, no big deal - only you are doing yourself an injustice by given a misleading subject. stick to the matter at hand (one step at a time!) - by all means explain the context of the problem (e.g. your goal of multiple updates) but don't confuse the problem with the context! I'm sure this is all most likely confusing. :) Typical I took one look and thought 'no thanks', if _you_ think _your_ post is confusing how is anybody else supposed to make head or tail of it. post from me ...sorry. None of this is working though. don't be sorry, be pro-active and rewrite it until its idiot proof. its in your own best interest - the clearer you state your problem the greater the chance someone will/can help you. ok, so that was a bit of a rant, the idea being to educate people in the value of taking the time (and it often takes lots of time!) to formulate their problems (and what they have tried so far) properly - its in everyones interest because it increase the potential of everyone on the list to: a, get involved b, learn something new we want you to succeed, if only for the selfish reason that one day you'll have the capability to help out others. besides the more good PHP hackers there are the better it is for all of us - in terms of the perceived validity of PHP (especially at the enterprise level). Perhaps updating multiple records is more difficult then I first imagined. alas we all have stories of spending countless hours trying to figure out whats seems to be very simple problems! Can anyone lend some advice , assistance ? not directly, but you may want to look into making your code more readable/flexible by not constantly switching in and out of PHP mode (i.e. using ?php ? everytime you want to output something.) Thank you, Stuart -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Update multiple records
--- Jochem Maas [EMAIL PROTECTED] wrote: don't be sorry, be pro-active and rewrite it until its idiot proof. its in your own best interest - the clearer you state your problem the greater the chance someone will/can help you. Well I've started with a clean slate. Meaning I ditched what was starting to look like spaghetti and started over with a lot of print_r's. My select statement in the (intended)update page has 3 parameters in the where statement, that are passed over from the link using $GET_VARS. 1- is the recordID, 2-the userID 3-an encrypted code tied to the user. All those parameters show up fine (print_r) on said update page. The first problem I'm having is getting the update page to show which values currently exist. This is the multi select box and the (adodb) recordset listing all the options. I'm lacking something in the page though that allow it to see which values are already chosen in the database: select name=inds[] size=8 multiple id=inds[] ?php while(!$rsInds-EOF){ ? option value=?php echo rsInds-Fields('CareerIDs')? ?php if (count($IndID) 0 AND is_array($IndID)) { foreach ($IndID as $ind) { ? ?php if ($rsInds-Fields('IndID')== $ind) {echo SELECTED;} } }? ?php echo $rsInds-Fields('CareerCategories')?/option ?php $rsInds-MoveNext(); } $rsInds-MoveFirst(); I've added this in the script, which prints out fine once I've submitted the page. Not sure if I need something similar for the records that already exist ? if (count($inds) 0 AND is_array($inds)) { $ind = '.implode(',', $inds).'; } Stuart -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Update multiple records
Norland, Martin wrote: -Original Message- From: Jochem Maas [mailto:[EMAIL PROTECTED] Sent: Monday, January 10, 2005 11:59 AM Subject: Re: [PHP-DB] Update multiple records ... That's what I get for troubleshooting before lunch! My apologies to the list and to anyone unfortunate enough to find that post with a search! I always get implode/explode mixed up (I think of an array as more organized than a string - so I think of a string as just an array that's been blown to smithereens). I guess the idea is that an array is like a bunch of pieces of a string after it's been blown up. thats actually why I personally always use join() instead implode(). also it does help beginners, the fact that its possible to use 'array syntax' on strings e.g.: $str = 'mystring'; echo $str[1] . ' me God?'; // echos 'y me God?' - say it out loud people ;-) I even checked the manual and dyslexically read it. 'Spose I owe everyone a cookie, and Jochem Maas two (plus first pick!). I quick google lead me to suspect Martin is american... if that is true then I demand one of those bigass cookies with even bigger chocolate chips :-) Cheers, - Martin Norland, Database / Web Developer, International Outreach x3257 The opinion(s) contained within this email do not necessarily represent those of St. Jude Children's Research Hospital. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Update multiple records
Stuart Felenstein wrote: --- Jochem Maas [EMAIL PROTECTED] wrote: don't be sorry, be pro-active and rewrite it until its idiot proof. its in your own best interest - the clearer you state your problem the greater the chance someone will/can help you. I don't know about anyone else, but I haven't got a clue as to what the problem is. I know I'm fairly intelligent but I can't work it out. Well I've started with a clean slate. Meaning I always a good plan! ditched what was starting to look like spaghetti and started over with a lot of print_r's. My select statement in the (intended)update page has 3 is it an update script or a selection/view script or both? parameters in the where statement, that are passed over from the link using $GET_VARS. 1- is the recordID, 2-the userID 3-an encrypted code tied to the user. so you start with a page that submits three values to another page which works so that not the problem... All those parameters show up fine (print_r) on said update page. The first problem I'm having is getting the update page to show which values currently exist. what values are you talking about? currently exist where? This is the multi select box and the (adodb) recordset listing all the options. I'm lacking something in the page though that allow it to see which values are already chosen in the database: select name=inds[] size=8 multiple id=inds[] ?php while(!$rsInds-EOF){ ? option value=?php echo rsInds-Fields('CareerIDs')? ?php if (count($IndID) 0 AND is_array($IndID)) { use not AND unless you know what your doing! they are different. also you check the count() on $IndID before you check its an array! another thing: $IndID and $rsInds are pretty meaningless to the rest of the world, and they will be to you as well in 6 months time! use a few extra chars and give the vars meaningfully, easily idenfiable names (helps us guess what you are doing too!) foreach ($IndID as $ind) { ? ?php if ($rsInds-Fields('IndID')== $ind) {echo SELECTED;} } }? ?php echo $rsInds-Fields('CareerCategories')?/option ?php $rsInds-MoveNext(); } $rsInds-MoveFirst(); that looks evil; seeing as I a still have no idea what your actually stuck on I'll give you a quick lesson in writing neater/better code: // begin code sample --- $opts = array(); $IndID = (array) $IndID; while (!$rsInds-EOF) { /* is this option selected? */ $selected = in_array($rsInds-Fields('IndID'), $IndID) ? 'selected=selected' : ''; /* build the option */ $opts[] = sprintf('option value=%s %s%s/option', rsInds-Fields('CareerIDs'), $selected, $rsInds-Fields('CareerCategories')); $rsInds-MoveNext(); } /* output the complete select element, * you don't have to output directly!!! * you could stuff the strings into another * var and echo it out later (e.g. after all * processing has been done) */ echo 'select name=inds[] size=8 multiple id=inds[]'; echo join(\n, $opts); echo '/select'; $rsInds-MoveFirst(); // end code sample --- excuse the strange layout a, I like short lines. b, I find tertiary if statements very readable when spread across multiple lines. c, I'm try to avoid nasty linewrap due to mail setups. BTW - the above is not syntax checked. anyone care to say this is _not_ more readable? I've added this in the script, which prints out fine once I've submitted the page. Not sure if I need something similar for the records that already exist ? neither do I! if (count($inds) 0 AND is_array($inds)) { $ind = '.implode(',', $inds).'; } looks like you want to do a select statement with $ind in the form of SELECT * FROM yourtable WHERE yourfield IN ($inds) heh why not! Stuart -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Update / Delete / Insert quandry
I am working on update areas for my end users. This is where they can go in and update and / or edit information in their profile. The problem I'm having a hard time getting my hands around are those areas where they can have multiple entries. One of the tables allows for a user to enter up to 5 choices. The table would look like this: RecordID TypeID 101 3 101 5 101 9 .etc. So I can do a straight update - where if they wanted to update the example above and change the 3 to a 12 that works fine. What if they wanted to leave TypeID: 3 but delete 5 and 9. Or another scenario, if they wanted to leave the existing ones but add a fourth record. Is there a way I can handle all this in one fell swoop? I thought of deleting all current records first and letting user make all new choices. Not sure if this would work well if many users though. Any suggestions ? Thank you, Stuart -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Update / Delete / Insert quandry
On Thu, 6 Jan 2005 07:28:32 -0800 (PST), Stuart Felenstein [EMAIL PROTECTED] wrote: The problem I'm having a hard time getting my hands around are those areas where they can have multiple entries. One of the tables allows for a user to enter up to 5 choices. The table would look like this: I think you need to have a primary key that's unique per record. That would allow you to make modifications per record without worrying about the duplicated RecordID. As far as updates and additions go, I usually keep the two separate when designing applications. Being able to update multiple rows at once shouldn't be a problem, you just select all the data, display it to your users with the appropriate form elements (select boxes, check boxes and such), and index it in your form by the unique id field. I don't think it's necessary to delete the records and reload them, doing that adds database load and increases the chance that something will go wrong. I hope I've touched on the items that concern you, feel free to write back if you need more information. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Update / Delete / Insert quandry
--- Andrew Kreps [EMAIL PROTECTED] wrote: On Thu, 6 Jan 2005 07:28:32 -0800 (PST), Stuart Felenstein [EMAIL PROTECTED] wrote: The problem I'm having a hard time getting my hands around are those areas where they can have multiple entries. One of the tables allows for a user to enter up to 5 choices. The table would look like this: I think you need to have a primary key that's unique per record. That would allow you to make modifications per record without worrying about the duplicated RecordID. I don't think it would kill me to add an auto inc primary id. Currently both the recordID and the TypeID are set up as primary id's. Also have a unique index using both fields. The recordID though should be duplicated though as it ties the records from the various tables together to form the profile. As far as updates and additions go, I usually keep the two separate when designing applications. Being able to update multiple rows at once shouldn't be a problem, you just select all the data, display it to your users with the appropriate form elements (select boxes, check boxes and such), and index it in your form by the unique id field. Okay, makes sense to me. I don't think it's necessary to delete the records and reload them, doing that adds database load and increases the chance that something will go wrong. I hope I've touched on the items that concern you, feel free to write back if you need more information. Thank you, I think I'm straight for now! Stuart -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Update keeps failing with php/sqlite
Hi all, I made a toy database. Two tables are students (a collection of student info) and message (private messages I want individual students to read). The message table is indexed by a student's social security number. student last, first, ssn, address, city, state, zip, email message msg1, ssn I'm trying to update msg1, unsuccessfully. Everything *seems* to go perfectly. The $_POST variables exist and hold what I think they should hold, the SQL looks OK. I checked the sqlite website, and UPDATE is indeed supported. I've been scratching my head a lot over this one: The given code is (note My_Header() and My_Footer() just interject a little HTML, like html and the like): // We picked the class and student. Now display the current message. // if ( isset($_REQUEST['action']) $_REQUEST['action'] == 'editmsg' ) { $handle = sqlite_open($_SESSION['dbfile']) or die(Could not open database.); $query = SELECT msg1 FROM message . WHERE ssn = ' . sqlite_escape_string($_POST['ssn']) . '; $result = sqlite_query( $handle, $query ); if ( sqlite_num_rows($result) 0 ) $msg = sqlite_fetch_single($result); else $msg = ''; My_Header(Messages); ? form method=post action=? echo $_SERVER['PHP_SELF']; ??action=submitedit textarea name=msg cols=40 rows=8 ? echo +$msg+ ?/textarea pinput type=hidden name=ssn value=? echo $_POST['ssn'] ?/p pinput type=submit value=Edit/p /form ? My_Footer(); exit(0); } // If we're here, it's because we want to update the message in the // database. This performs the SQL using UPDATE. // if ( isset($_REQUEST['action']) $_REQUEST['action'] == 'submitedit' ) { $handle = sqlite_open($_SESSION['dbfile']) or die('Error in query: ' . sqlite_error_string(sqlite_last_error($handle))); $query = UPDATE message . SET msg1 = ' . sqlite_escape_string($_POST['msg']) . ' . WHERE ssn = ' . sqlite_escape_string($_POST['ssn']) . '; sqlite_query($handle, $query) or die('Error in query: ' . sqlite_error_string(sqlite_last_error($handle))); Header(Location: . $_SERVER['PHP_SELF'] . ?action=chose); exit(0); } ? Anything wrong with this code? I've tried to write as defensively as I can. Thanks! Pete -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Update multiple tables
From: Khalid Judeh [EMAIL PROTECTED] I have a script that make changes to the database in more than one sql statement, as i need to update more than one table at the same time, I want to know if there is a method where all of those changes are committed, or rolled back if some error occured(lets say in the last sql statement). When asking questions like this, it's a smart idea to actually mention what database you're using. Either way, you need to use transactions. Either use a database that supports them (most of them do, to varying degrees of complexity) or use a database abstraction layer that simulates them. ADOdb comes to mind, although I've never used that feature. http://adodb.sourceforge.net ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Update multiple tables
Hello, I have a script that make changes to the database in more than one sql statement, as i need to update more than one table at the same time, I want to know if there is a method where all of those changes are committed, or rolled back if some error occured(lets say in the last sql statement). thanks - Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage!
Re: [PHP-DB] Update multiple tables
On Wed, 25 Aug 2004 22:08:03 -0700 (PDT), Khalid Judeh [EMAIL PROTECTED] wrote: Hello, I have a script that make changes to the database in more than one sql statement, as i need to update more than one table at the same time, I want to know if there is a method where all of those changes are committed, or rolled back if some error occured(lets say in the last sql statement). thanks Use transactions : BEGIN TRANSACTION ...sql statements... COMMIT or ROLLBACK The implementation varies a little. Check out the documentation/manual of your database server for transactions. (PHP also has built-in functions for transactions such as mysqli_commit(), mysqli_rollback() and mysqli_autocommit() for mysqli.) HTH. ramil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Update data problem
Hi there, I hoped someone might be able to tell me why this is not working. I have a form that updates the table 'media' with the 3 values below, that and some other variables are the only data in the table. That works nicely. I then want to update one of the other columns ($E) based on the data I've just submitted - that is the multiplication of the two variables. I then want to input this variable back into the table. What happens is that the new values are used ($C), but the original value in the table ($G) is ignored. Why is this? I would have thought it was pretty straight-froward to extract 2 values from a table, multiply them together and then input it back as a new variable. Thanks Justin if($update) { mysql_pconnect(localhost,root,password); mysql_select_db(options); $query = UPDATE media SET A='$A', B='$B', C='$C'; $result = mysql_query($query); $query = SELECT C, G FROM media; $result = mysql_query($query); $E= $C*$G; $query = UPDATE media SET E='$E'; $result = mysql_query($query); }
Re: [PHP-DB] Update data problem
$E= $C*$G; Should be: $E = $C * $G; Never use quotation marks when doing math. Quotation marks are used for strings. Hope that helps. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Update Statement
you could use: for ($i=0; $i max; $i++) { mysql_query(update Table1, Table2 set Table1.field1 = Table2.field2 where Table1.no = Table2.no); } OR if you use mysqlt daemon (support for transactions, in this case is best) mysql_query('BEGIN'); for ($i=0; $i max; $i++) { if (! mysql_query(update Table1, Table2 set Table1.field1 = Table2.field2 where Table1.no = Table2.no)) { mysql_query('ROLLBACK'); die('ERROR - in query nº: '.$i); } } mysql_query(COMMIT'); Execute all or not execute none. I personally use adodb to connect to databases and control transactions. If what you really want is to execute then all in just one function call to the database, i don't know how can you do it with your mysql version. HTH - Original Message - From: Ng Hwee Hwee [EMAIL PROTECTED] To: DBList [EMAIL PROTECTED] Sent: Wednesday, February 18, 2004 2:14 AM Subject: [PHP-DB] Update Statement hi all, I would like to do the following: update Table1, Table2 set Table1.field1 = Table2.field2 where Table1.no = Table2.no however, i found that multiple updates can only work for MySQL version 4.0.4 but mine is 3.23.54... can anyone enlighten me on how I can achieve the same results with this older MySQL version? or can PHP 4.2.2 help me in any way? Thank you so much! Hwee -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Update Statement
hi, thank you for your reply but I can't do a multiple table update because my server is using MySQL with version lesser than 4.0... please enlighten me how I can write the same update statement without using multiple table update.. is there a way to use JOIN or something?? thanks thanks.. hwee - Original Message - From: Ricardo Lopes [EMAIL PROTECTED] To: Ng Hwee Hwee [EMAIL PROTECTED] Cc: PHP DB [EMAIL PROTECTED] Sent: Wednesday, February 18, 2004 5:41 PM Subject: Re: [PHP-DB] Update Statement you could use: for ($i=0; $i max; $i++) { mysql_query(update Table1, Table2 set Table1.field1 = Table2.field2 where Table1.no = Table2.no); } OR if you use mysqlt daemon (support for transactions, in this case is best) mysql_query('BEGIN'); for ($i=0; $i max; $i++) { if (! mysql_query(update Table1, Table2 set Table1.field1 = Table2.field2 where Table1.no = Table2.no)) { mysql_query('ROLLBACK'); die('ERROR - in query nº: '.$i); } } mysql_query(COMMIT'); Execute all or not execute none. I personally use adodb to connect to databases and control transactions. If what you really want is to execute then all in just one function call to the database, i don't know how can you do it with your mysql version. HTH - Original Message - From: Ng Hwee Hwee [EMAIL PROTECTED] To: DBList [EMAIL PROTECTED] Sent: Wednesday, February 18, 2004 2:14 AM Subject: [PHP-DB] Update Statement hi all, I would like to do the following: update Table1, Table2 set Table1.field1 = Table2.field2 where Table1.no = Table2.no however, i found that multiple updates can only work for MySQL version 4.0.4 but mine is 3.23.54... can anyone enlighten me on how I can achieve the same results with this older MySQL version? or can PHP 4.2.2 help me in any way? Thank you so much! Hwee -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Update Statement
hi all, I would like to do the following: update Table1, Table2 set Table1.field1 = Table2.field2 where Table1.no = Table2.no however, i found that multiple updates can only work for MySQL version 4.0.4 but mine is 3.23.54... can anyone enlighten me on how I can achieve the same results with this older MySQL version? or can PHP 4.2.2 help me in any way? Thank you so much! Hwee
[PHP-DB] Update problems
I'm trying to update a record in a MSSQL database. When I execute the following code on an IIS5 webserver, I get an error message that says: PHP Warning: odbc_do(): SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'Tom'., SQL state S0022 in SQLExecDirect in E:\web\phptest\dbtest.php on line 31 This is after I've submitted the name Tom in the form. Does anyone have any ideas?! Thanks very much in advance! -Kermit ?php $fname=$_POST['first']; if (!(is_null($fname))) { if (!($odbccon = odbc_connect(**, **, **))) { die(pCould not Connect./p); } else { echopConnected to Database./p; $sqlupdate=UPDATE UserInfo SET ZNum='112763', FirstName=$fname, LastName='Short', TA='00', Building='1197', Room='112', Div='FWO', Grp='IIM' WHERE ZNum='112763'; $sqlupdresults=odbc_do($odbccon, $sqlupdate); $query=SELECT * FROM UserInfo WHERE ZNum=112763; $qresults=odbc_do($odbccon, $query); odbc_result_all($qresults, border=1); odbc_close($odbccon); echo pConnection Closed./p; } } else { ? form action=?php echo $_SERVER['PHP_SELF'] ? method=post name=test First Name: input type=text name=first /br / input type=submit name=submit / /form ?php } ? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Update problems
you need quotes around the name FirstName=$fname otherwise it takes the value of $fname to be aa field name HTH Peter -Original Message- From: Kermit Short [mailto:[EMAIL PROTECTED] Sent: 20 January 2004 22:28 To: [EMAIL PROTECTED] Subject: [PHP-DB] Update problems I'm trying to update a record in a MSSQL database. When I execute the following code on an IIS5 webserver, I get an error message that says: PHP Warning: odbc_do(): SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'Tom'., SQL state S0022 in SQLExecDirect in E:\web\phptest\dbtest.php on line 31 This is after I've submitted the name Tom in the form. Does anyone have any ideas?! Thanks very much in advance! -Kermit ?php $fname=$_POST['first']; if (!(is_null($fname))) { if (!($odbccon = odbc_connect(**, **, **))) { die(pCould not Connect./p); } else { echopConnected to Database./p; $sqlupdate=UPDATE UserInfo SET ZNum='112763', FirstName=$fname, LastName='Short', TA='00', Building='1197', Room='112', Div='FWO', Grp='IIM' WHERE ZNum='112763'; $sqlupdresults=odbc_do($odbccon, $sqlupdate); $query=SELECT * FROM UserInfo WHERE ZNum=112763; $qresults=odbc_do($odbccon, $query); odbc_result_all($qresults, border=1); odbc_close($odbccon); echo pConnection Closed./p; } } else { ? form action=?php echo $_SERVER['PHP_SELF'] ? method=post name=test First Name: input type=text name=first /br / input type=submit name=submit / /form ?php } ? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Update problems
You might want to use single quotes FirstName='$fname' $sqlupdate=UPDATE UserInfo SET ZNum='112763', FirstName='$fname', LastName='Short', TA='00', Building='1197', Room='112', Div='FWO', Grp='IIM' WHERE ZNum='112763'; - Paul -Original Message- From: Peter Lovatt [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 20, 2004 5:17 PM To: Kermit Short; [EMAIL PROTECTED] Subject: RE: [PHP-DB] Update problems you need quotes around the name FirstName=$fname otherwise it takes the value of $fname to be aa field name HTH Peter -Original Message- From: Kermit Short [mailto:[EMAIL PROTECTED] Sent: 20 January 2004 22:28 To: [EMAIL PROTECTED] Subject: [PHP-DB] Update problems I'm trying to update a record in a MSSQL database. When I execute the following code on an IIS5 webserver, I get an error message that says: PHP Warning: odbc_do(): SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'Tom'., SQL state S0022 in SQLExecDirect in E:\web\phptest\dbtest.php on line 31 This is after I've submitted the name Tom in the form. Does anyone have any ideas?! Thanks very much in advance! -Kermit ?php $fname=$_POST['first']; if (!(is_null($fname))) { if (!($odbccon = odbc_connect(**, **, **))) { die(pCould not Connect./p); } else { echopConnected to Database./p; $sqlupdate=UPDATE UserInfo SET ZNum='112763', FirstName=$fname, LastName='Short', TA='00', Building='1197', Room='112', Div='FWO', Grp='IIM' WHERE ZNum='112763'; $sqlupdresults=odbc_do($odbccon, $sqlupdate); $query=SELECT * FROM UserInfo WHERE ZNum=112763; $qresults=odbc_do($odbccon, $query); odbc_result_all($qresults, border=1); odbc_close($odbccon); echo pConnection Closed./p; } } else { ? form action=?php echo $_SERVER['PHP_SELF'] ? method=post name=test First Name: input type=text name=first /br / input type=submit name=submit / /form ?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 Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Update Query
Hello, I ma new to the list and looking for some help with the following query. insert into Temp (CompId) SELECT distinct(company_id) from Associations where product_id=9; When I exe that statement directly on the mysql command line It exe's with no problem. However when I exe that through php $result = mysql_query($query) or die(Query failed : . mysql_error()); it fails every time. Any Ideas? Using MYSQL 3.23.54 PHP 4.1.2 Thanks Aaron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Update Query
How do you know it failed? What is the mysql_error() returning to you? I'd also recommend you echo out the query to the browser window before sending it so you can see exactly what's being sent to the database. That'll help you troubleshoot SQL problems on your own. -Original Message- From: Aaron Bryer [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 3:02 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Update Query Hello, I ma new to the list and looking for some help with the following query. insert into Temp (CompId) SELECT distinct(company_id) from Associations where product_id=9; When I exe that statement directly on the mysql command line It exe's with no problem. However when I exe that through php $result = mysql_query($query) or die(Query failed : . mysql_error()); it fails every time. Any Ideas? Using MYSQL 3.23.54 PHP 4.1.2 Thanks Aaron -- 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] Update Query
I did that below is the exact excerpt from the source of the web browser !--insert into Temp (CompId) SELECT distinct(company_id) from Associations where product_id=10-- The My Sql Error is Query failed : You have an error in your SQL syntax near '' at line 1 I have tried entering () around the select statement but that seamed to make more errors Thanks Aaron -Original Message- From: Hutchins, Richard [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 3:07 PM To: '[EMAIL PROTECTED]'; [EMAIL PROTECTED] Subject: RE: [PHP-DB] Update Query How do you know it failed? What is the mysql_error() returning to you? I'd also recommend you echo out the query to the browser window before sending it so you can see exactly what's being sent to the database. That'll help you troubleshoot SQL problems on your own. -Original Message- From: Aaron Bryer [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 3:02 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Update Query Hello, I ma new to the list and looking for some help with the following query. insert into Temp (CompId) SELECT distinct(company_id) from Associations where product_id=9; When I exe that statement directly on the mysql command line It exe's with no problem. However when I exe that through php $result = mysql_query($query) or die(Query failed : . mysql_error()); it fails every time. Any Ideas? Using MYSQL 3.23.54 PHP 4.1.2 Thanks Aaron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Update Query
Aaron, I'll tell you right now that I don't have ANY experience creating temporary tables. None. Haven't needed to go down that road yet. So, in the true spirit of the learning process, I'm going to do what anybody else would (and should) do: Take a wild guess. And here it is: I think your process for creating your temporary table is flawed. I've looked through the MySQL manual and found this: 6.5.3 CREATE TABLE Syntax CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [select_statement] So, without a really good understanding of what you're trying to select into the temporary table, your query might look like: CREATE TEMPORARY TABLE $tbl_temp SELECT distinct(company_id) from Associations where product_id=9; Then you can do whatever query you need to against the temporary table then, when you're done, you'll drop the temporary table. Your MySQL error is probably occurring because there is no table on which to perform the query you're sending to the database. I'm positive there are others on this list with more experience using temporary tables than I, but this might be a push in the right direction (I hope). Hope this helps. Rich -Original Message- From: Aaron Bryer [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 3:18 PM To: [EMAIL PROTECTED] Subject: RE: [PHP-DB] Update Query I did that below is the exact excerpt from the source of the web browser !--insert into Temp (CompId) SELECT distinct(company_id) from Associations where product_id=10-- The My Sql Error is Query failed : You have an error in your SQL syntax near '' at line 1 I have tried entering () around the select statement but that seamed to make more errors Thanks Aaron -Original Message- From: Hutchins, Richard [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 3:07 PM To: '[EMAIL PROTECTED]'; [EMAIL PROTECTED] Subject: RE: [PHP-DB] Update Query How do you know it failed? What is the mysql_error() returning to you? I'd also recommend you echo out the query to the browser window before sending it so you can see exactly what's being sent to the database. That'll help you troubleshoot SQL problems on your own. -Original Message- From: Aaron Bryer [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 3:02 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Update Query Hello, I ma new to the list and looking for some help with the following query. insert into Temp (CompId) SELECT distinct(company_id) from Associations where product_id=9; When I exe that statement directly on the mysql command line It exe's with no problem. However when I exe that through php $result = mysql_query($query) or die(Query failed : . mysql_error()); it fails every time. Any Ideas? Using MYSQL 3.23.54 PHP 4.1.2 Thanks Aaron -- 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 Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] update db with variables
Hi, I'm writing an application that will display the contents of a db in the browser, including the db-assigned id number. The user can then enter the id number in a form and the browser will display the record corresponding to that id number, with the data in editable text fields. The user edits the text fields and submits to an 'update' script using POST, and the update script puts the new values back into the database. In the update script, I'm trying to access the field names and values in the $_POST array, and update the db using a loop: while ($data = each($_POST)) { $name = $data[ 'key' ]; $value = $data[ 'value' ]; echo $name, $value; $result = mysql_query(UPDATE tablename SET $name = $value WHERE ID=$id) or die (unable to update record); } The echo statement shows me that the script dies on the first time through the loop. The application must be on line this week. Does anyone have any suggestions? Many thanks, Robbie -- -*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* Robbie Staufer NCAR/SCD 1850 Table Mesa Dr. Rm. 42 Boulder, CO. 80305 (303) 497-1836 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] update db with variables
put some single quotes around $value in your SQL statement. -Original Message- From: Robbie Staufer [mailto:[EMAIL PROTECTED] Sent: 24 September 2003 16:21 To: [EMAIL PROTECTED] Subject: [PHP-DB] update db with variables Hi, I'm writing an application that will display the contents of a db in the browser, including the db-assigned id number. The user can then enter the id number in a form and the browser will display the record corresponding to that id number, with the data in editable text fields. The user edits the text fields and submits to an 'update' script using POST, and the update script puts the new values back into the database. In the update script, I'm trying to access the field names and values in the $_POST array, and update the db using a loop: while ($data = each($_POST)) { $name = $data[ 'key' ]; $value = $data[ 'value' ]; echo $name, $value; $result = mysql_query(UPDATE tablename SET $name = $value WHERE ID=$id) or die (unable to update record); } The echo statement shows me that the script dies on the first time through the loop. The application must be on line this week. Does anyone have any suggestions? Many thanks, Robbie -- -*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* Robbie Staufer NCAR/SCD 1850 Table Mesa Dr. Rm. 42 Boulder, CO. 80305 (303) 497-1836 -- 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] update db with variables
From: Robbie Staufer [EMAIL PROTECTED] I'm writing an application that will display the contents of a db in the browser, including the db-assigned id number. The user can then enter the id number in a form and the browser will display the record corresponding to that id number, with the data in editable text fields. The user edits the text fields and submits to an 'update' script using POST, and the update script puts the new values back into the database. In the update script, I'm trying to access the field names and values in the $_POST array, and update the db using a loop: while ($data = each($_POST)) { $name = $data[ 'key' ]; $value = $data[ 'value' ]; echo $name, $value; $result = mysql_query(UPDATE tablename SET $name = $value WHERE ID=$id) or die (unable to update record); } The echo statement shows me that the script dies on the first time through the loop. Put quotes around your strings in your SQL: mysql_query(UPDATE tablename SET $name = '$value' WHERE ID=$id) Also, instead of dieing with your own error message, use mysql_error(), which would have given you a better error to act upon... or die(Error in update: . mysql_error()); ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] update db with variables
Quotes missing around $value. Try instead: mysql_query( UPDATE tablename SET {$name} LIKE '{$value}' WHERE ID={$id}) Of course, even though the code does not show, you have properly validated user input before firing this query...blink/ HTH Ignatius _ - Original Message - From: Robbie Staufer [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 5:20 PM Subject: [PHP-DB] update db with variables Hi, I'm writing an application that will display the contents of a db in the browser, including the db-assigned id number. The user can then enter the id number in a form and the browser will display the record corresponding to that id number, with the data in editable text fields. The user edits the text fields and submits to an 'update' script using POST, and the update script puts the new values back into the database. In the update script, I'm trying to access the field names and values in the $_POST array, and update the db using a loop: while ($data = each($_POST)) { $name = $data[ 'key' ]; $value = $data[ 'value' ]; echo $name, $value; $result = mysql_query(UPDATE tablename SET $name = $value WHERE ID=$id) or die (unable to update record); } The echo statement shows me that the script dies on the first time through the loop. The application must be on line this week. Does anyone have any suggestions? Many thanks, Robbie -- -*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* Robbie Staufer NCAR/SCD 1850 Table Mesa Dr. Rm. 42 Boulder, CO. 80305 (303) 497-1836 -- 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] update db with variables
Thanks to all. Single quotes around '$value' did the trick. Robbie CPT John W. Holmes wrote: From: Robbie Staufer [EMAIL PROTECTED] I'm writing an application that will display the contents of a db in the browser, including the db-assigned id number. The user can then enter the id number in a form and the browser will display the record corresponding to that id number, with the data in editable text fields. The user edits the text fields and submits to an 'update' script using POST, and the update script puts the new values back into the database. In the update script, I'm trying to access the field names and values in the $_POST array, and update the db using a loop: while ($data = each($_POST)) { $name = $data[ 'key' ]; $value = $data[ 'value' ]; echo $name, $value; $result = mysql_query(UPDATE tablename SET $name = $value WHERE ID=$id) or die (unable to update record); } The echo statement shows me that the script dies on the first time through the loop. Put quotes around your strings in your SQL: mysql_query(UPDATE tablename SET $name = '$value' WHERE ID=$id) Also, instead of dieing with your own error message, use mysql_error(), which would have given you a better error to act upon... or die(Error in update: . mysql_error()); ---John Holmes... -- -*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* Robbie Staufer NCAR/SCD 1850 Table Mesa Dr. Rm. 42 Boulder, CO. 80305 (303) 497-1836
[PHP-DB] UPDATE part of a column
Hi, I have a column in my table called address. When users add an address with a textarea they sometimes press return for a new line and this seems to be putting line breaks into the database, so I have 2 questions: 1. How can I clean the table, so for example changing: Ashfield Business CentreBR The Idlewells PrecinctBR Sutton-in-AshfieldBR to: Ashfield Business Centre, The Idlewells Precinct, Sutton-in-Ashfield, 2, Is there a way to make sure the data is 'clean' before entering into the table? Thanks for your help -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] UPDATE part of a column
on 23/9/03 1:03 pm, Shaun at [EMAIL PROTECTED] wrote: 1. How can I clean the table, so for example changing: Ashfield Business CentreBR The Idlewells PrecinctBR Sutton-in-AshfieldBR to: Ashfield Business Centre, The Idlewells Precinct, Sutton-in-Ashfield, 2, Is there a way to make sure the data is 'clean' before entering into the table? Do something like this: $pagetext=ereg_replace( BR, ,$pagetext); To strip the BRs -- 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] UPDATE part of a column
As a separate issue, I would consider seperating the address field into it's individual parts Street,area,city etc.. All different fields -Original Message- From: Shaun [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 2:03 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] UPDATE part of a column Hi, I have a column in my table called address. When users add an address with a textarea they sometimes press return for a new line and this seems to be putting line breaks into the database, so I have 2 questions: 1. How can I clean the table, so for example changing: Ashfield Business CentreBR The Idlewells PrecinctBR Sutton-in-AshfieldBR to: Ashfield Business Centre, The Idlewells Precinct, Sutton-in-Ashfield, 2, Is there a way to make sure the data is 'clean' before entering into the table? Thanks for your help -- 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 Query
Hi, I have (amogst others) three tables in my database named Bookings, User, and Representative. A User and a Representative are different types of user, however I now want to merge these tables into one - User. When a Booking is made, the User_ID and the Rep_ID are stored in the Booking table. I have now merged the two tables (User and Representative), how can I update the Rep_ID column in Bookings so that it refers to the User_ID for each Representative in the User table? Thanks for your help -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] UPDATE query
$query1 = UPDATE news,autori SET news.titolo='$titolo', news.testo='$testo', news.data='$data', news.nome='$nome', autori.mail='$mail' WHERE news.nome = autori.nome AND id='$id'; You can't do an update across tables the last time I checked. Even if it's possible in newer versions, using mysql_error() after your query will tell you what the error is. ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: Re: [PHP-DB] UPDATE query
Can someone please unsubscribe/boot the ydnarb.com address below? Every time I send a message to the list I receive a blank reply from the address. I assume this is happening for others, also. ---John Holmes... - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, July 14, 2003 2:32 PM Subject: Re: Re: [PHP-DB] UPDATE query -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Update MD5 field
Daniela, Why do you want to keep a calculated field inside your database? -- Beverly Steiner [EMAIL PROTECTED] -Original Message- From: Dani Matielo [mailto:[EMAIL PROTECTED] Sent: Monday, March 03, 2003 10:18 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Update MD5 field Hello, everybody my problem is the following: I just imported a csv file to a MySQL database that contains name and email fields. It has about 9k lines on it and I need a new field that orinally didn't exist called code thats suposed to be MD5(name.email) I know how to do this for new data, but I don't know how to update all the old ones I already have there. Thank you in advance, Daniela -- 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 errors
What wrong with this SQL $SQLSTATEMENT[UPDATE_CAT] = UPDATE cats SET cat_name = '$cat_name', WHERE cat_id = $catidforedit ; Error no : 1064 Details : You have an error in your SQL syntax near 'WHERE cat_id = 2 ' at line 4 Db : mysql -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Update errors
Remove the comma at the end of this line: cat_name = '$cat_name',//remove this comma -Original Message- From: Alawi shekh albaity [mailto:[EMAIL PROTECTED] Sent: Saturday, March 01, 2003 1:27 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] Update errors What wrong with this SQL $SQLSTATEMENT[UPDATE_CAT] = UPDATE cats SET cat_name = '$cat_name', WHERE cat_id = $catidforedit ; Error no : 1064 Details : You have an error in your SQL syntax near 'WHERE cat_id = 2 ' at line 4 Db : mysql -- 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] Update errors
Shouldn't have a comma after cat_name = '$cat_name' unless you are updating multiple fields. Gary Every Sr. UNIX Administrator Ingram Entertainment (615) 287-4876 Pay It Forward mailto:[EMAIL PROTECTED] http://accessingram.com -Original Message- From: Alawi shekh albaity [mailto:[EMAIL PROTECTED] Sent: Saturday, March 01, 2003 12:27 AM To: [EMAIL PROTECTED] Subject: [PHP-DB] Update errors What wrong with this SQL $SQLSTATEMENT[UPDATE_CAT] = UPDATE cats SET cat_name = '$cat_name', WHERE cat_id = $catidforedit ; Error no : 1064 Details : You have an error in your SQL syntax near 'WHERE cat_id = 2 ' at line 4 Db : mysql -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Update errors
On Saturday 01 March 2003 14:27, Alawi shekh albaity wrote: What wrong with this SQL $SQLSTATEMENT[UPDATE_CAT] = UPDATE cats SET cat_name = '$cat_name', Remove that trailing comma. WHERE cat_id = $catidforedit ; Error no : 1064 Details : You have an error in your SQL syntax near 'WHERE cat_id = 2 ' at line 4 Db : mysql -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * -- Search the list archives before you post http://marc.theaimsgroup.com/?l=php-db -- /* Massachusetts has the best politicians money can buy. */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] UPDATE doesn't work
Thank Jason! It counts now. So if i want to make something like Most view tutorial is $row[title] How can i do this? Regards, - Original Message - From: Jason Wong [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 05, 2003 1:44 PM Subject: Re: [PHP-DB] UPDATE doesn't work On Wednesday 05 February 2003 12:50, Le Hoang wrote: // Add 1 view to the view column $v = $row[view]; $vplus = $v+1; $view = mysql_query(update photoshop_tutorial where id=$id set view=$vplus); // Problem here! You should ALWAYS check the result of a call to mysql_query(): if ($view === false) { echo Fatal error: . mysql_error(); } Your problem is that the SQL for update is incorrect and it should be: UPDATE photoshop_tutorial SET view = $vplus WHERE id = $id You can also do the increment inside the SQL so: UPDATE photoshop_tutorial SET view = view + 1 WHERE id = $id -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * -- Search the list archives before you post http://marc.theaimsgroup.com/?l=php-db -- /* Tell a man there are 300 billion stars in the universe and he'll believe you. Tell him a bench has wet paint on it and he'll have to touch to be sure. */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] update
Hello, Newbie question. And can the same SQL portion of the code be written something like... query= UPDATE taablename SET (username, email, location) VALUEs( $username, $email, $location); Any reason why this Update command would not execute? // BUILD AND EXECUTE QUERY TO SAVE USER INFO INTO DATABASE TABLE query = UPDATE forumUsers SET username = '$name' WHERE userID = '$id'; result = @mysql_query($query); query2 = UPDATE forumUsers SET email = '$emai' WHERE userID = '$id'; result2 = @mysql_query($query2); query3 = UPDATE forumUsers SET Location = '$loc' WHERE userID = '$id'; result3 = @mysql_query($query3); / INFORM FLASH OF SUCCESS print /:result=Updated Thanks; // CLOSE LINK TO DATABASE SERVER ysql_close($link); ? Ike -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] update
UPDATE is for changing values in existing rows. It appears you want to add new information. Use INSERT. $query= INSERT tablename SET (username, email, location) VALUES( $username, $email, $location); Note that a WHERE condition is incorrect for INSERT. Your UPDATE syntax is not correct either. Note that you need only one query to update any or all of the columns in a row. Using your example: $query=UPDATE tablename SET username=$username, email=$email, location=$location WHERE userID=$id; Finally, your Flash of Success is not useful because it will print even when the query fails. Doug On Thu, 30 Jan 2003 13:34:41 -0500, Ike Austin wrote: Hello, Newbie question. And can the same SQL portion of the code be written something like... query= UPDATE taablename SET (username, email, location) VALUEs( $username, $email, $location); Any reason why this Update command would not execute? // BUILD AND EXECUTE QUERY TO SAVE USER INFO INTO DATABASE TABLE query = UPDATE forumUsers SET username = '$name' WHERE userID = '$id'; result = @mysql_query($query); query2 = UPDATE forumUsers SET email = '$emai' WHERE userID = '$id'; result2 = @mysql_query($query2); query3 = UPDATE forumUsers SET Location = '$loc' WHERE userID = '$id'; result3 = @mysql_query($query3); / INFORM FLASH OF SUCCESS print /:result=Updated Thanks; // CLOSE LINK TO DATABASE SERVER ysql_close($link); ? Ike -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] update
If you want to use WHERE clause, see the INSERT table SELECT ... syntax. Extremely convenient. You inherit all the flexibility of the SELECT statement. Ignatius - Original Message - From: Doug Thompson [EMAIL PROTECTED] To: Ike Austin [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, February 04, 2003 2:17 PM Subject: Re: [PHP-DB] update UPDATE is for changing values in existing rows. It appears you want to add new information. Use INSERT. $query= INSERT tablename SET (username, email, location) VALUES( $username, $email, $location); Note that a WHERE condition is incorrect for INSERT. Your UPDATE syntax is not correct either. Note that you need only one query to update any or all of the columns in a row. Using your example: $query=UPDATE tablename SET username=$username, email=$email, location=$location WHERE userID=$id; Finally, your Flash of Success is not useful because it will print even when the query fails. Doug On Thu, 30 Jan 2003 13:34:41 -0500, Ike Austin wrote: Hello, Newbie question. And can the same SQL portion of the code be written something like... query= UPDATE taablename SET (username, email, location) VALUEs( $username, $email, $location); Any reason why this Update command would not execute? // BUILD AND EXECUTE QUERY TO SAVE USER INFO INTO DATABASE TABLE query = UPDATE forumUsers SET username = '$name' WHERE userID = '$id'; result = @mysql_query($query); query2 = UPDATE forumUsers SET email = '$emai' WHERE userID = '$id'; result2 = @mysql_query($query2); query3 = UPDATE forumUsers SET Location = '$loc' WHERE userID = '$id'; result3 = @mysql_query($query3); / INFORM FLASH OF SUCCESS print /:result=Updated Thanks; // CLOSE LINK TO DATABASE SERVER ysql_close($link); ? Ike -- 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 doesn't work
Hello all, I'm a graphic designer making a site for giving tutorials, but since i'm just a newbie in coding, tons of problems have fallen on me. This is my photoshop_tutorial table structure id int not null auto_increment, title varchar(255) not null, author varchar(55) not null, description text not null, date int(14) not null, tutorial mediumtext not null, view int not null, primary key (id), unique id (id) In the show_photoshop_tutorial.php, i used this // Fetch info of the tutorial $result = mysql_query(select * from photoshop_tutorial where id=$id); while($row = mysql_fetch_array($result)) { // Format the date $postdate=date(d-m-Y,$row[date]); // Add 1 view to the view column $v = $row[view]; $vplus = $v+1; $view = mysql_query(update photoshop_tutorial where id=$id set view=$vplus); // Problem here! // Fetch user's info from another table $user = mysql_query(select * from site_users where displayname = '$row[author]'); $get_info = mysql_fetch_array($user); // Print out query results to the screen echo pb$row[title]/bbr; echo Author: ba href=\mailto: .$get_info[email].\$get_info[displayname]/a/b on b$postdate/b view ; echo p$row[description]; echo p$row[tutorial]; } But the view doedn't add 1 to the existing value Any solutions for that? Best regards, Le Hoang
Re: [PHP-DB] UPDATE doesn't work
On Wednesday 05 February 2003 12:50, Le Hoang wrote: // Add 1 view to the view column $v = $row[view]; $vplus = $v+1; $view = mysql_query(update photoshop_tutorial where id=$id set view=$vplus); // Problem here! You should ALWAYS check the result of a call to mysql_query(): if ($view === false) { echo Fatal error: . mysql_error(); } Your problem is that the SQL for update is incorrect and it should be: UPDATE photoshop_tutorial SET view = $vplus WHERE id = $id You can also do the increment inside the SQL so: UPDATE photoshop_tutorial SET view = view + 1 WHERE id = $id -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * -- Search the list archives before you post http://marc.theaimsgroup.com/?l=php-db -- /* Tell a man there are 300 billion stars in the universe and he'll believe you. Tell him a bench has wet paint on it and he'll have to touch to be sure. */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] - Update help
On Friday 31 January 2003 15:31, nikos wrote: I've download some patches for PHP 4.0.6. and I dont know how to use it. For example rfc1867.c.diff-4.0.6 file how can I run it and do the update? I have PH 7,2 Linux and Apache 1,32 Thank you If there's no compelling reason for you to be staying with 4.0.6 you really should be using a newer version. If you really do need 4.0.6 then check the archives on how to use the patch. -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * /* The rule is, jam to-morrow and jam yesterday, but never jam today. -- Lewis Carroll */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] - Update help
Hello list I've download some patches for PHP 4.0.6. and I dont know how to use it. For example rfc1867.c.diff-4.0.6 file how can I run it and do the update? I have PH 7,2 Linux and Apache 1,32 Thank you
[PHP-DB] UPDATE tbl problem...
Hi, I am having a small problem when it comes to updating some tables that I have. tblmembers and tbloptions are both linked together with the exact same userid #. here is the code i have been using... mysql_query(UPDATE tbloptions SET strColorPref='$tmpColorPref' strTextPref='$tmpTextPref' strLinkPref='$tmpLinkPref' strALinkPref='$tmpALinkPref' WHERE strIDent='$_SESSION[strIDent]'); mysql_query(UPDATE tblmembers SET strAction='Successfully updated user settings on $logdate.' WHERE strIDent='$_SESSION[strIDent]'); Now, heres where i am confused, the second update statement for tblmembers, works fine, no problems. The variables listed in both statements work, i have tested them. The second statement however does not seem to insert the data into the table at all. no errors are returned, all seems good, but the data isn't there. i have privileges set for both tables properly. i can't figure it out, if anyone can help, please reply. :) Hartleigh Burton www.channel-x.org -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] UPDATE tbl problem...
On Sunday 15 December 2002 09:24, Hartleigh Burton wrote: Hi, I am having a small problem when it comes to updating some tables that I have. tblmembers and tbloptions are both linked together with the exact same userid #. here is the code i have been using... mysql_query(UPDATE tbloptions SET strColorPref='$tmpColorPref' strTextPref='$tmpTextPref' strLinkPref='$tmpLinkPref' strALinkPref='$tmpALinkPref' WHERE strIDent='$_SESSION[strIDent]'); mysql_query(UPDATE tblmembers SET strAction='Successfully updated user settings on $logdate.' WHERE strIDent='$_SESSION[strIDent]'); Now, heres where i am confused, the second update statement for tblmembers, works fine, no problems. The variables listed in both statements work, i have tested them. The second statement however does not seem to insert the data into the table at all. no errors are returned, all seems good, but the data isn't there. i have privileges set for both tables properly. i can't figure it out, if anyone can help, please reply. :) I'm assuming that in your last paragraph you mean the FIRST statement doesn't work. First, no errors are returned because you're not testing for it? You should _always_ use something like: mysql_query('...') or die(mysql_error()); Second, the reason why it fails is because you need commas between the items in your SET clause: UPDATE table SET col1 = 'val1', col2 = 'val2' ... -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * /* Carson's Consolation: Nothing is ever a complete failure. It can always be used as a bad example. */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Update Query Help...
I am attempting to UPDATE a table and I have having absolutely ZERO success. Here is the query: $tmp = $_POST['sbcuid'].-.$_POST['system'][$a]; $update = UPDATE accounts SET atime='NOW()' WHERE \id-sys\='.$tmp.'; echo $update; $result1 = mysql_query($update, $Prod) or die(mysql_error()); echo mysql_affected_rows(); Please help me figure out why this is not working. I have tried quoting the column name 'id-sys' every way I can think of, but nothing works. The column is initially NULL, and I am attempting to update just the single column with a timestamp. Thanks in advance. 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
Re: [PHP-DB] Update Query Help...
$tmp = $_POST['sbcuid'].-.$_POST['system'][$a]; $update = UPDATE accounts SET atime='NOW()' WHERE \id-sys\='.$tmp.'; echo $update; $result1 = mysql_query($update, $Prod) or die(mysql_error()); echo mysql_affected_rows(); Try: $update = UPDATE accounts SET atime=NOW() WHERE id-sys='$tmp'; NOW() is a function, don't enclose it within quotes and make it a string. You don't put quotes around column names, either, only string values. ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Update Query Help...
is it a mysql field type of datetime? if so, you may want to use this instead $timestamp=date(Y-m-j H:i:s); $update = UPDATE accounts SET atime='$timestamp' WHERE id-sys='$tmp'; that puts the current timestamp in mysql format for entry into your table. Cheers Jason -Original Message- From: NIPP, SCOTT V (SBCSI) [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 10, 2002 1:35 PM To: '[EMAIL PROTECTED]' Subject: [PHP-DB] Update Query Help... I am attempting to UPDATE a table and I have having absolutely ZERO success. Here is the query: $tmp = $_POST['sbcuid'].-.$_POST['system'][$a]; $update = UPDATE accounts SET atime='NOW()' WHERE \id-sys\='.$tmp.'; echo $update; $result1 = mysql_query($update, $Prod) or die(mysql_error()); echo mysql_affected_rows(); Please help me figure out why this is not working. I have tried quoting the column name 'id-sys' every way I can think of, but nothing works. The column is initially NULL, and I am attempting to update just the single column with a timestamp. Thanks in advance. 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
Re: [PHP-DB] Update Query Help...
SCOTT, I am attempting to UPDATE a table and I have having absolutely ZERO success. Here is the query: $tmp = $_POST['sbcuid'].-.$_POST['system'][$a]; $update = UPDATE accounts SET atime='NOW()' WHERE \id-sys\='.$tmp.'; echo $update; $result1 = mysql_query($update, $Prod) or die(mysql_error()); echo mysql_affected_rows(); Please help me figure out why this is not working. I have tried quoting the column name 'id-sys' every way I can think of, but nothing works. The column is initially NULL, and I am attempting to update just the single column with a timestamp. Thanks in advance. NOW() is a function and should not be part of a quoted string. =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Update Query Help...
SCOTT, The list's crystal ball filter is down for maintenance. Show us the tbl schema and the debug print of $update. Then we won't be firing blind! =dn I understand that the column name does not normally need quotes, but without quotes on the column name I get a mysql error message about no column named 'id'. Unfortunately, I cannot even get the UPDATE statement working outside of PHP through an direct SQL statement. Everything looks like it should work, but the affected columns is always zero, and obviously the atime column is not getting the timestamp. This is quite frustrating. Thanks again for the help. Hopefully someone will come up with something to help me get this working. -Original Message- From: 1LT John W. Holmes [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 10, 2002 1:45 PM To: NIPP, SCOTT V (SBCSI); [EMAIL PROTECTED] Subject: Re: [PHP-DB] Update Query Help... $tmp = $_POST['sbcuid'].-.$_POST['system'][$a]; $update = UPDATE accounts SET atime='NOW()' WHERE \id-sys\='.$tmp.'; echo $update; $result1 = mysql_query($update, $Prod) or die(mysql_error()); echo mysql_affected_rows(); Try: $update = UPDATE accounts SET atime=NOW() WHERE id-sys='$tmp'; NOW() is a function, don't enclose it within quotes and make it a string. You don't put quotes around column names, either, only string values. ---John Holmes... -- 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] Update Query Help...
I have definitely isolated the problem. If I change the column name that the match is being performed on from 'id-sys' to 'id_sys' I can execute the query without having to worry about quoting the column name and everything works. I think that I am just going to leave the column name changed and take this as a lesson never to use '-' in a column name again. Thanks again for the help. Maybe we all learned something from this. -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 10, 2002 2:00 PM To: NIPP, SCOTT V (SBCSI); '1LT John W. Holmes'; [EMAIL PROTECTED] Subject: Re: [PHP-DB] Update Query Help... SCOTT, The list's crystal ball filter is down for maintenance. Show us the tbl schema and the debug print of $update. Then we won't be firing blind! =dn I understand that the column name does not normally need quotes, but without quotes on the column name I get a mysql error message about no column named 'id'. Unfortunately, I cannot even get the UPDATE statement working outside of PHP through an direct SQL statement. Everything looks like it should work, but the affected columns is always zero, and obviously the atime column is not getting the timestamp. This is quite frustrating. Thanks again for the help. Hopefully someone will come up with something to help me get this working. -Original Message- From: 1LT John W. Holmes [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 10, 2002 1:45 PM To: NIPP, SCOTT V (SBCSI); [EMAIL PROTECTED] Subject: Re: [PHP-DB] Update Query Help... $tmp = $_POST['sbcuid'].-.$_POST['system'][$a]; $update = UPDATE accounts SET atime='NOW()' WHERE \id-sys\='.$tmp.'; echo $update; $result1 = mysql_query($update, $Prod) or die(mysql_error()); echo mysql_affected_rows(); Try: $update = UPDATE accounts SET atime=NOW() WHERE id-sys='$tmp'; NOW() is a function, don't enclose it within quotes and make it a string. You don't put quotes around column names, either, only string values. ---John Holmes... -- 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] Update Query Help...
I have definitely isolated the problem. If I change the column name that the match is being performed on from 'id-sys' to 'id_sys' I can execute the query without having to worry about quoting the column name and everything works. I think that I am just going to leave the column name changed and take this as a lesson never to use '-' in a column name again. Thanks again for the help. Maybe we all learned something from this. If you want to use a column name like id-sys, which you shouldn't, though, as you've learned, you can enclose it in backticks, like `id-sys`. Note that those are not single quotes, they are backticks, to the left of the number 1 key. ---John Holmes... -- 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
[PHP-DB] update and join?
Hello to all! Quick questionis it possible to do an update query w/ a join yet? If not, what is your preferred method? Would you take care of it on the PHP side? I need to update a table w/ data from another table if certain conditions are true, i.e the fields in the table to be joined are NULL Any help as always is greatly appreciated Thanks, Cory -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] update and join?
Cory Hicks [mailto:[EMAIL PROTECTED]] wrote: Hello to all! Quick questionis it possible to do an update query w/ a join yet? If not, what is your preferred method? Would you take care of it on the PHP side? No, it's not possible, at least not with MySQL 3.x. MySQL 4.1, currently in development, will feature nested subqueries and multi-table-updates. I need to update a table w/ data from another table if certain conditions are true, i.e the fields in the table to be joined are NULL I have to do that a couple of times, and always do it this way: Build a SELECT statement, selecting all the data you will need in the updates, and the primary key for the table which needs updates. Fetch all the data in a nested array, like this: $a = array(primary key 1 = array (col1=data1,col3=data3), primary key 2 = array (col2=data2,col3=data3), ); and then, do the updates in a neat foreach loop: foreach ($a as $pkey=$data) { [ build update stmt from $data array ] dbquery ($stmt) } Hope this helps. Thomas -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] update and join?
Quick questionis it possible to do an update query w/ a join yet? If not, what is your preferred method? Would you take care of it on the PHP side? Probably depends on your database, but MySQL doesn't support it. I think MySQL 4.0 might. I need to update a table w/ data from another table if certain conditions are true, i.e the fields in the table to be joined are NULL You'll have to use a scripting language, or something outside of the database. Or maybe a INSERT INTO ... SELECT ... will help? ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Update involving a list field
Hi, Im using a form that updates a database entry. In it one of my fields is a drop down/list field. Whenever the value in this field is changed, instead of updating it to the new item, it is deleted entirely. I have the same field in my entry page that inserts just fine, but for some reason the update deletes the info. Is there something special about updating a recordset off of a dropdown field that I am missing? if it matters, im pulling the initial value of the field from the value in the database row.I can send the code for the page to anyone who needs more info. Shiloh
[PHP-DB] update db problem
I am making a web form for entering/editing/deleting items from a database, the entering and deleting parts work fine. The editing part is giving me some problems - Here's the code - ? include connect; if ($submit) { // if no ID then we're adding a new rma, else we're editing an existing rma if ($id) { $sql = UPDATE rma SET rma_no='$rma_no',rma_name='$rma_name',rec_date ='$rec_date',rma_status='$rma_status' WHERE id='$id'; } else { $sql = INSERT INTO rma (rma_no,rma_name,rec_date,rma_status) VALUES ('$rma_no','$rma_name','$rec_date','$rma_status'); } // run SQL against the DB $result = mysql_query($sql); echo Record updated/edited!p a href=\rma_input.php\List RMA's/a; } elseif ($delete) { // delete a record $sql = DELETE FROM rma WHERE id=$id; $result = mysql_query($sql); echo Deleted!pa href=\rma_input.php\List RMA's/a/p; } else { // this part happens if we don't press submit if (!$id) { // print the list if there is not editing $result = mysql_query(SELECT * FROM rma,$db); while ($myrow = mysql_fetch_array($result)) { printf(a href=\%s?id=%s\%s %s/a \n, $PHP_SELF, $myrow[id], $myrow[rma_no], $myrow[rma_name], $myrow[rec_date], $myrow [rma_status]); printf(a href=\%s?id=%sdelete=yes\(DELETE)/abr, $PHP_SELF, $myrow[id]); } } if ($id) { // editing so select a record $sql = SELECT * FROM rma WHERE id=$id; $result = mysql_query($sql); $myrow = mysql_fetch_array($result); $id = $myrow[id]; $rma_no = $myrow[rma_no]; $rma_name = $myrow[rma_name]; $rec_date = $myrow[rec_date]; $rma_status = $myrow[rma_status]; // print the id for editi ? input type=hidden name=id value=?php echo $id ? ? } } ? What is happening is when I click on an item on the list it will display all the fields except rma_status (a textarea form field). When I make any changes and press submit, the changes to fields other than rma_status, those fields are updated or left as is, but the rma_status textarea is wiped out. Also, instead of updating the current item in the db, it is submitting another row into the database, giving multiple rows of the same rma, but with each one showing it's edits over time. This would be fine for historical purposes, but not what I want. What am I doing wrong? -- Chip Wiegand Computer Services Simrad, Inc www.simradusa.com [EMAIL PROTECTED] There is no reason anyone would want a computer in their home. --Ken Olson, president, chairman and founder of Digital Equipment Corporation, 1977 (They why do I have 9? Somebody help me!) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Update/Insert
Hi to all, I have a form where users can insert or update a record. Now the way I have it, can just only do one thing either update or insert a new record. What I would like to do is: if it is a new record, execute the insert statement else execute the update statement. I don't know if it is possible to do it in PHP. Any help is greatly appreciate Thanks in advanced! Nato -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Update/Insert
You could try inserting it, and see if it succeeds. If its fails, then you know you have to update it. IE $sql= 'insert...' $result = mysql_query($sql); if(!$result) { $sql = 'update...'; mysql_query($sql); } -Chris At 01:34 PM 4/26/2002 -0400, you wrote: Hi to all, I have a form where users can insert or update a record. Now the way I have it, can just only do one thing either update or insert a new record. What I would like to do is: if it is a new record, execute the insert statement else execute the update statement. I don't know if it is possible to do it in PHP. Any help is greatly appreciate Thanks in advanced! Nato -- 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] Update/Insert
On Saturday 27 April 2002 01:34, Natividad Castro wrote: Hi to all, I have a form where users can insert or update a record. Now the way I have it, can just only do one thing either update or insert a new record. What I would like to do is: if it is a new record, execute the insert statement else execute the update statement. I don't know if it is possible to do it in PHP. if ... else is certainly available in PHP. -- Jason Wong - Gremlins Associates - www.gremlins.com.hk Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * /* Modesty is a vastly overrated virtue. -- J.K. Galbraith */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] update
Hi there, I have a system for updating whereby you select the country/continent from a db then edit the record and update it and it works perfectly - that is until there is a in the update $country set - so if it says Africa it updates fine, but if $country = Asia The Orient it doesn't update, any ideas how I can fix this? Thanks everyone. Chris
Re: [PHP-DB] update
Looking at the below, I think it might be spaces in the query - how can I rectify this? Thanks :-) Chris Hi there, I have a system for updating whereby you select the country/continent from a db then edit the record and update it and it works perfectly - that is until there is a in the update $country set - so if it says Africa it updates fine, but if $country = Asia The Orient it doesn't update, any ideas how I can fix this? Thanks everyone. Chris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php