Re: [PHP-DB] display fetched data in excel format
I was looking at this page: http://www-128.ibm.com/developerworks/opensource/library/os-phpexcel/#N10181 Hope this helps. Do you have a link on the pear solution as the pear example seems wrong on the above link? John santosh wrote: Hi All, I am using subquery to fetch data from mysql and display on html page . But client like to display data in excel and they can also save or download it. I have visited spreadsheet_excel_writer on pear.php.net. and decided to use CSV file. Please suggest me more to do how csv file can be created dynamically and pass as argv. Thanks Santosh -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] RES: [PHP] mysql joins
not sure how timestamps work in MySQL, but I've written this in Oracle: CREATE TABLE USaR ( UsID char(255) null, Firstname char(255) NULL, Surname char(255) NULL, Tel char(255) NULL, Cell char(255) NULL, Email char(255) NULL ) / CREATE TABLE Tracker( UsID CHAR(255) NULL, Points CHAR(255) NULL ) / CREATE TABLE Winners( UsiD CHAR(255) NULL, DateTime DATE NULL ) / /* Inserted some values in those tables and then executed: */ select us.usid, --I couldn't get the Firstname as it's not a group by element (?) Sum(tr.points) from usar us, --in mysql you'll have to do 'usar as us' tracker tr, --tracker as tr winners wn --winners as wn where us.usid = tr.usid --here is the join magic and us.usid = wn.usid --and here AND wn.datetime (SYSDATE - 14) --winner date has to be less than 14 days from today GROUP BY us.usid --separate per user; I hope this helps :) This will *NOT* bring you users that never won or have no points (since they don't have any record in winners table) Regards, Thiago -Mensagem original- De: Steven Macintyre [mailto:[EMAIL PROTECTED] Enviada em: terça-feira, 25 de março de 2008 09:21 Para: [EMAIL PROTECTED] Assunto: [PHP] mysql joins I have three tables, namely; User - UID - Firstname - Surname - Tel - Cell - Email Tracker - UID - Points Winners - UID - Datetime (-00-00 00:00:00) I need to get the following information from the above tables (in my logical sense) All users from user with sum(points) as points and datetime datetime + 14 days In English, the all users must be selected, excluding the ones that have won in the last 14 days and return all the information and the sum of points I suspect I would need to use joins here ... but have no clue how to do so ... I have read up a bit and can work out inner joins from three tables, but not coping with this problem above Can someone help me out with this please? Many thanks Steven -- PHP General 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] HTML CSS (Off Subject) 911 Help! :-)
On Tue, Mar 25, 2008 at 12:24 AM, Karl James [EMAIL PROTECTED] wrote: [snip!] I need some help on formatting issues. [snip!] Right now the page looks like this. [snip!] http://www.theufl.com/2003/reports/rosters/fantasyrosters.htm [snip!] It is almost, their. However, as you can tell the players name is not cooperating. I want the table to look similar to this one. http://www.webleaguemanager.com/demo/reports/FantasyRostersRpt.html [snip!] Karl, It looks like everything is as you described your vision now. Is that correct? For the future, your best bet when attempting to copy a design is to cheat and view the source of the design you want to mimic. -- /Daniel P. Brown Forensic Services, Senior Unix Engineer 1+ (570-) 362-0283 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Not updating certain fields in same row
Hi everyone, I am attempting to update a record for a login system while leaving certain fields untouched if they arn't changed, and am running into issues. Basically what I want to do, is say I have these fields: Field1 Field2 Field3 Field4 I update Field1 and Field3 but not Field2 and Field4. What I want to do is change the values in Field1 and Field3 without touching the values in Field2 and Field4. I have tried this code: $tab = \t; if (!isset($_POST['txtLoginName']) || empty($_POST['txtLoginName'])) { $loginName = mysqli_real_escape_string($chpwpostlink, $_POST['txtLoginName']); } else { $loginName = $tab; } which works the fields that I've changed, but if I don't submit a value in the form it sets the field to be blank in MySQL. Which is what I am trying to avoid. Any ideas? -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED]
Re: [PHP-DB] Not updating certain fields in same row
On Tue, Mar 25, 2008 at 12:59 PM, Jason Pruim [EMAIL PROTECTED] wrote: Hi everyone, I am attempting to update a record for a login system while leaving certain fields untouched if they arn't changed, and am running into issues. [snip!] I have tried this code: $tab = \t; if (!isset($_POST['txtLoginName']) || empty($_POST['txtLoginName'])) { $loginName = mysqli_real_escape_string($chpwpostlink, $_POST['txtLoginName']); } else { $loginName = $tab; } Mmm-hmm and exactly how do that work to update the database? The SQL query you're probably looking for would be like this: $sql = UPDATE users SET Field1='.mysql_real_escape_string($field1).',Field3='.mysql_real_escape_string($field3).' WHERE id='.mysql_real_escape_string($id).' LIMIT 1; -- /Daniel P. Brown Forensic Services, Senior Unix Engineer 1+ (570-) 362-0283 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Not updating certain fields in same row
On Mar 25, 2008, at 1:09 PM, Daniel Brown wrote: On Tue, Mar 25, 2008 at 12:59 PM, Jason Pruim [EMAIL PROTECTED] wrote: Hi everyone, I am attempting to update a record for a login system while leaving certain fields untouched if they arn't changed, and am running into issues. [snip!] I have tried this code: $tab = \t; if (!isset($_POST['txtLoginName']) || empty($_POST['txtLoginName'])) { $loginName = mysqli_real_escape_string($chpwpostlink, $_POST['txtLoginName']); } else { $loginName = $tab; } Mmm-hmm and exactly how do that work to update the database? The SQL query you're probably looking for would be like this: $sql = UPDATE users SET Field1 = '.mysql_real_escape_string ($field1).',Field3='.mysql_real_escape_string($field3).' WHERE id='.mysql_real_escape_string($id).' LIMIT 1; the actual query I'm using is this: $chpwsql = UPDATE current SET customerName='$customerName', loginName='$loginName', loginPassword='$PW', email='$email', adminLevel='$adminLevel' WHERE Record='$Record1'; What it is doing now is if I don't set a a field I am replacing the content of it with a tab, which isn't what I want. Basically what I'm looking for is if loginPassword hasn't changed... don't clear the contents of it. if it has changed, then update loginPassword -- /Daniel P. Brown Forensic Services, Senior Unix Engineer 1+ (570-) 362-0283 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Not updating certain fields in same row
I usually pre-populate the form with the values that are already in the record: (... using PEAR's MDB2 package -- http://pear.php.net/packages/MDB2 ) $query = SELECT * FROM member WHERE username = ' . $_POST['username'] . '; $result = $db-query($query); $member = $result-fetchRow(MDB2_FETCHMODE_ASSOC); foreach ($member as $key = $value) { $$key = $value; } form name=member_edit action=?= $_SERVER['PHP_SELF'] ? method=POST input type=hidden name=_submit value=1 / input type=text name=fname value=?= $fname ? / input type=text name=lname value=?= $lname ? / input type=text name=email value=?= $email ? / /form then when they submit, the record is just repopulated with whatever is in the form when they submit. ie -- only fields they changed get changed in the db. if($_POST['_submit']) { $update = UPDATE member SET .. blah, blah... } that way, none of the fields are blank unless they were in the db blank to begin with. and you can add client or server-side validation to prevent that. -- matt On Tue, Mar 25, 2008 at 11:59 AM, Jason Pruim [EMAIL PROTECTED] wrote: Hi everyone, I am attempting to update a record for a login system while leaving certain fields untouched if they arn't changed, and am running into issues. Basically what I want to do, is say I have these fields: Field1 Field2 Field3 Field4 I update Field1 and Field3 but not Field2 and Field4. What I want to do is change the values in Field1 and Field3 without touching the values in Field2 and Field4. I have tried this code: $tab = \t; if (!isset($_POST['txtLoginName']) || empty($_POST['txtLoginName'])) { $loginName = mysqli_real_escape_string($chpwpostlink, $_POST['txtLoginName']); } else { $loginName = $tab; } which works the fields that I've changed, but if I don't submit a value in the form it sets the field to be blank in MySQL. Which is what I am trying to avoid. Any ideas? -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED]
Re: [PHP-DB] Not updating certain fields in same row
On Mar 25, 2008, at 1:17 PM, Matt Anderton wrote: I usually pre-populate the form with the values that are already in the record: (... using PEAR's MDB2 package -- http://pear.php.net/packages/MDB2 ) $query = SELECT * FROM member WHERE username = ' . $_POST['username'] . '; $result = $db-query($query); $member = $result-fetchRow(MDB2_FETCHMODE_ASSOC); foreach ($member as $key = $value) { $$key = $value; } form name=member_edit action=?= $_SERVER['PHP_SELF'] ? method=POST input type=hidden name=_submit value=1 / input type=text name=fname value=?= $fname ? / input type=text name=lname value=?= $lname ? / input type=text name=email value=?= $email ? / /form then when they submit, the record is just repopulated with whatever is in the form when they submit. ie -- only fields they changed get changed in the db. if($_POST['_submit']) { $update = UPDATE member SET .. blah, blah... } that way, none of the fields are blank unless they were in the db blank to begin with. and you can add client or server-side validation to prevent that. -- matt Hi Matt, That's what I'm doing for most of the fields, but how would you handle a password that has been MD5'ed and includes some variables to make it harder to crack? :) ie: $PW = md5($salt$password); I can't undo the MD5 and I don't really want to... Just want to be able to change it rather then view what it is. On Tue, Mar 25, 2008 at 11:59 AM, Jason Pruim [EMAIL PROTECTED] wrote: Hi everyone, I am attempting to update a record for a login system while leaving certain fields untouched if they arn't changed, and am running into issues. Basically what I want to do, is say I have these fields: Field1 Field2 Field3 Field4 I update Field1 and Field3 but not Field2 and Field4. What I want to do is change the values in Field1 and Field3 without touching the values in Field2 and Field4. I have tried this code: $tab = \t; if (!isset($_POST['txtLoginName']) || empty($_POST['txtLoginName'])) { $loginName = mysqli_real_escape_string($chpwpostlink, $_POST['txtLoginName']); } else { $loginName = $tab; } which works the fields that I've changed, but if I don't submit a value in the form it sets the field to be blank in MySQL. Which is what I am trying to avoid. Any ideas? -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Not updating certain fields in same row
You can't use the function empty() because as soon as the form is submitted, the value of an empty field in the form, is a blank in the variable in your script, so basically your password field is NOT empty, it has a value, a blank. So you need to test your password field to NOT to be empty, also use a second password field, a confirmation field, so: $querystring=your SQL statement here; If (passwd1 is not blank) and (passwd1==passwd2) then $querystring.=passwd='passwd1' If not You keep intact your first SQL statement without touching the passwd field My two niquel cents! __ Miguel Guirao Aguilera, Linux+, ITIL Sistemas de Información Informática R8 Ext. 7540 -- -Original Message- -- From: Jason Pruim [mailto:[EMAIL PROTECTED] -- Sent: Tuesday, March 25, 2008 11:14 AM -- To: Daniel Brown -- Cc: php-db@lists.php.net -- Subject: Re: [PHP-DB] Not updating certain fields in same row -- -- -- On Mar 25, 2008, at 1:09 PM, Daniel Brown wrote: -- On Tue, Mar 25, 2008 at 12:59 PM, Jason Pruim [EMAIL PROTECTED] -- wrote: -- Hi everyone, -- -- I am attempting to update a record for a login system while leaving -- certain fields untouched if they arn't changed, and am running into -- issues. -- [snip!] -- -- I have tried this code: -- $tab = \t; -- if (!isset($_POST['txtLoginName']) || -- empty($_POST['txtLoginName'])) { -- -- $loginName = -- mysqli_real_escape_string($chpwpostlink, -- $_POST['txtLoginName']); -- } -- else -- { -- $loginName = $tab; -- } -- -- Mmm-hmm and exactly how do that work to update the database? -- The SQL query you're probably looking for would be like this: -- -- $sql = UPDATE users SET -- Field1 -- = -- '.mysql_real_escape_string -- ($field1).',Field3='.mysql_real_escape_string($field3).' -- WHERE id='.mysql_real_escape_string($id).' LIMIT 1; -- -- the actual query I'm using is this: -- -- $chpwsql = UPDATE current SET customerName='$customerName', -- loginName='$loginName', loginPassword='$PW', email='$email', -- adminLevel='$adminLevel' WHERE Record='$Record1'; -- -- What it is doing now is if I don't set a a field I am replacing the -- content of it with a tab, which isn't what I want. Basically what I'm -- looking for is if loginPassword hasn't changed... don't clear the -- contents of it. if it has changed, then update loginPassword -- -- -- -- -- -- -- /Daniel P. Brown -- Forensic Services, Senior Unix Engineer -- 1+ (570-) 362-0283 -- -- -- -- PHP Database Mailing List (http://www.php.net/) -- To unsubscribe, visit: http://www.php.net/unsub.php -- -- -- -- -- -- -- Jason Pruim -- Raoset Inc. -- Technology Manager -- MQC Specialist -- 3251 132nd ave -- Holland, MI, 49424-9337 -- www.raoset.com -- [EMAIL PROTECTED] -- -- -- -- -- -- -- 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] Not updating certain fields in same row
I encrypt my pw's the same way but I usually don't include the password on an edit my info page. I create a separate change password screen where I force them to type in their old password and then type a new one twice. if the encrypted, salted old password attempt does not match what is in the db, the form doesn't validate and the password is not changed. the lost password page is a different beast altogether but again, the password field is omitted from the edit form AND the update statement so it is never touched in this process. that goes for any other field you don't want the user to be able to edit too -- just don't put it in the form or the update and they can't mess with it. I usually have an is_admin (yes/no) field and don't want the user to change that of course. -- matt On Tue, Mar 25, 2008 at 12:24 PM, Jason Pruim [EMAIL PROTECTED] wrote: On Mar 25, 2008, at 1:17 PM, Matt Anderton wrote: I usually pre-populate the form with the values that are already in the record: (... using PEAR's MDB2 package -- http://pear.php.net/packages/MDB2 ) $query = SELECT * FROM member WHERE username = ' . $_POST['username'] . '; $result = $db-query($query); $member = $result-fetchRow(MDB2_FETCHMODE_ASSOC); foreach ($member as $key = $value) { $$key = $value; } form name=member_edit action=?= $_SERVER['PHP_SELF'] ? method=POST input type=hidden name=_submit value=1 / input type=text name=fname value=?= $fname ? / input type=text name=lname value=?= $lname ? / input type=text name=email value=?= $email ? / /form then when they submit, the record is just repopulated with whatever is in the form when they submit. ie -- only fields they changed get changed in the db. if($_POST['_submit']) { $update = UPDATE member SET .. blah, blah... } that way, none of the fields are blank unless they were in the db blank to begin with. and you can add client or server-side validation to prevent that. -- matt Hi Matt, That's what I'm doing for most of the fields, but how would you handle a password that has been MD5'ed and includes some variables to make it harder to crack? :) ie: $PW = md5($salt$password); I can't undo the MD5 and I don't really want to... Just want to be able to change it rather then view what it is. On Tue, Mar 25, 2008 at 11:59 AM, Jason Pruim [EMAIL PROTECTED] wrote: Hi everyone, I am attempting to update a record for a login system while leaving certain fields untouched if they arn't changed, and am running into issues. Basically what I want to do, is say I have these fields: Field1 Field2 Field3 Field4 I update Field1 and Field3 but not Field2 and Field4. What I want to do is change the values in Field1 and Field3 without touching the values in Field2 and Field4. I have tried this code: $tab = \t; if (!isset($_POST['txtLoginName']) || empty($_POST['txtLoginName'])) { $loginName = mysqli_real_escape_string($chpwpostlink, $_POST['txtLoginName']); } else { $loginName = $tab; } which works the fields that I've changed, but if I don't submit a value in the form it sets the field to be blank in MySQL. Which is what I am trying to avoid. Any ideas? -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED]
Re: [PHP-DB] Not updating certain fields in same row
On Tue, Mar 25, 2008 at 1:14 PM, Jason Pruim [EMAIL PROTECTED] wrote: the actual query I'm using is this: $chpwsql = UPDATE current SET customerName='$customerName', loginName='$loginName', loginPassword='$PW', email='$email', adminLevel='$adminLevel' WHERE Record='$Record1'; What it is doing now is if I don't set a a field I am replacing the content of it with a tab, which isn't what I want. Basically what I'm looking for is if loginPassword hasn't changed... don't clear the contents of it. if it has changed, then update loginPassword Okay, since you won't only want to rely on isset() here (in case someone accidentally hits a key into the field), try this: // NOTE: This assumes prior sanity checks and cleansing // of variables, and is written like so to avoid breaking // of the query due to mail client-enforced line breaks. $chpwsql = UPDATE current SET ; $chpwsql .= customerName='.$customername.',; $chpwsql .= loginName='.$loginName.',; if(preg_match('/^[a-z0-9]{5,16}$/i',$PW)) { // If it's between 5-16 case-insensitive alphanumeric // characters, it's all good. If you want to allow symbols, // simply modify the regexp accordingly. $chpwsql .= loginPassword='.$PW.',; } $chpwsql .= email='.$email.',; $chpwsql .= adminLevel='.$adminLevel',; $chpwsql .= WHERE Record='.$Record1.'; $chpwsql .= LIMIT 1; -- /Daniel P. Brown Forensic Services, Senior Unix Engineer 1+ (570-) 362-0283 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Not updating certain fields in same row
I might be way off here. Php.net tells me that: [quote] mysql_real_escape_string — Escapes special characters in a string for use in a SQL statement string **mysql_real_escape_string** ( string $unescaped_string [, resource $link_identifier ] ) [/quote] and you use [quote] mysqli_real_escape_string($chpwpostlink, $_POST['txtLoginName']); [/quote] I can't imagine that $_POST['txtLoginName'] is a resource identifier (which is the actual connection to your database). Also, this condition: [quote] if (!isset($_POST['txtLoginName']) || empty($_POST['txtLoginName'])) [/quote] is true if and only if no form element by the name txtLoginName existed on the previous page - and on top of that, empty() does the same as isset() and apart from that also checks whether, if the variable has been set, it has a value (0, , NULL or FALSE) that evaluates to FALSE. I don't understand why you'd want to fill the username with a tab either: \t. Maybe you can post your full code? Evert Daniel Brown wrote: On Tue, Mar 25, 2008 at 1:14 PM, Jason Pruim [EMAIL PROTECTED] wrote: the actual query I'm using is this: $chpwsql = UPDATE current SET customerName='$customerName', loginName='$loginName', loginPassword='$PW', email='$email', adminLevel='$adminLevel' WHERE Record='$Record1'; What it is doing now is if I don't set a a field I am replacing the content of it with a tab, which isn't what I want. Basically what I'm looking for is if loginPassword hasn't changed... don't clear the contents of it. if it has changed, then update loginPassword Okay, since you won't only want to rely on isset() here (in case someone accidentally hits a key into the field), try this: // NOTE: This assumes prior sanity checks and cleansing // of variables, and is written like so to avoid breaking // of the query due to mail client-enforced line breaks. $chpwsql = UPDATE current SET ; $chpwsql .= customerName='.$customername.',; $chpwsql .= loginName='.$loginName.',; if(preg_match('/^[a-z0-9]{5,16}$/i',$PW)) { // If it's between 5-16 case-insensitive alphanumeric // characters, it's all good. If you want to allow symbols, // simply modify the regexp accordingly. $chpwsql .= loginPassword='.$PW.',; } $chpwsql .= email='.$email.',; $chpwsql .= adminLevel='.$adminLevel',; $chpwsql .= WHERE Record='.$Record1.'; $chpwsql .= LIMIT 1; -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Not updating certain fields in same row
Correction: Also, this condition: [quote] if (!isset($_POST['txtLoginName']) || empty($_POST['txtLoginName'])) [/quote] is true if and only if no form element by the name txtLoginName existed on the previous page - and on top of that, empty() does the same as isset() and apart from that also checks whether, if the variable has been set, it has a value (0, , NULL or FALSE) that evaluates to FALSE. This condition is of course also true when the form element has existed but has no value. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Not updating certain fields in same row
On Mar 25, 2008, at 2:57 PM, Evert Lammerts wrote: I might be way off here. Php.net tells me that: [quote] mysql_real_escape_string — Escapes special characters in a string for use in a SQL statement string **mysql_real_escape_string** ( string $unescaped_string [, resource $link_identifier ] ) [/quote] and you use [quote] mysqli_real_escape_string($chpwpostlink, $_POST['txtLoginName']); [/quote] I can't imagine that $_POST['txtLoginName'] is a resource identifier (which is the actual connection to your database). It's not... see: http://us2.php.net/manual/en/function.mysqli-real-escape-string.php Notice the I after mysql Also, this condition: [quote] if (!isset($_POST['txtLoginName']) || empty($_POST['txtLoginName'])) [/quote] is true if and only if no form element by the name txtLoginName existed on the previous page - and on top of that, empty() does the same as isset() and apart from that also checks whether, if the variable has been set, it has a value (0, , NULL or FALSE) that evaluates to FALSE. I don't understand why you'd want to fill the username with a tab either: \t. I didn't want to... What I am attempting to do is if the field is NOT changed, don't touch it in the database. The main issue I had was with the password field for updating a password since Im not going to read a MD5 hash back to the user to be edited. Maybe you can post your full code? Evert Daniel Brown wrote: On Tue, Mar 25, 2008 at 1:14 PM, Jason Pruim [EMAIL PROTECTED] wrote: the actual query I'm using is this: $chpwsql = UPDATE current SET customerName='$customerName', loginName='$loginName', loginPassword='$PW', email='$email', adminLevel='$adminLevel' WHERE Record='$Record1'; What it is doing now is if I don't set a a field I am replacing the content of it with a tab, which isn't what I want. Basically what I'm looking for is if loginPassword hasn't changed... don't clear the contents of it. if it has changed, then update loginPassword Okay, since you won't only want to rely on isset() here (in case someone accidentally hits a key into the field), try this: // NOTE: This assumes prior sanity checks and cleansing // of variables, and is written like so to avoid breaking // of the query due to mail client-enforced line breaks. $chpwsql = UPDATE current SET ; $chpwsql .= customerName='.$customername.',; $chpwsql .= loginName='.$loginName.',; if(preg_match('/^[a-z0-9]{5,16}$/i',$PW)) { // If it's between 5-16 case-insensitive alphanumeric // characters, it's all good. If you want to allow symbols, // simply modify the regexp accordingly. $chpwsql .= loginPassword='.$PW.',; } $chpwsql .= email='.$email.',; $chpwsql .= adminLevel='.$adminLevel',; $chpwsql .= WHERE Record='.$Record1.'; $chpwsql .= LIMIT 1; -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Not updating certain fields in same row
Jason Pruim wrote: Hi everyone, I am attempting to update a record for a login system while leaving certain fields untouched if they arn't changed, and am running into issues. Basically what I want to do, is say I have these fields: Field1 Field2 Field3 Field4 I update Field1 and Field3 but not Field2 and Field4. What I want to do is change the values in Field1 and Field3 without touching the values in Field2 and Field4. I have tried this code: $tab = \t; if (!isset($_POST['txtLoginName']) || empty($_POST['txtLoginName'])) { $loginName = mysqli_real_escape_string($chpwpostlink, $_POST['txtLoginName']); } else { $loginName = $tab; } which works the fields that I've changed, but if I don't submit a value in the form it sets the field to be blank in MySQL. Which is what I am trying to avoid. Any ideas? $fields = array('Field1', 'Field2', 'Field3', 'Field4'); $update_fields = array(); foreach ($fields as $form_field_name) { if (!isset($_POST[$form_field_name]) || empty($_POST[$form_field_name]) || $_POST[$form_field_name] == '') { // ignore the blank/empty fields continue; } $update_fields[] = $form_field_name . =' . mysqli_real_escape_string($conn, $_POST[$form_field_name]) . '; } if (!empty($update_fields)) { $query = 'update table set '; $query .= implode(',', $update_fields); $query .= ' WHERE recordid='x'; } If you're just ignoring password fields, you have a confirm password box too don't you? You can also use that: $password_one = $_POST['password']; $password_confirm = $_POST['password_confirm']; if ($password_one == '' || $password_confirm == '') { // skip adding the pw field to the update query } -- 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] numeric string to single digit array
Can anyone tell me how how to convert a string of integers into an array of single digit integers. i.e. 1223123 into ['1','2,','2','3','1,'2','3'] ? When I retreive a column of single digit integers I end up with a long string of numbers. I think PHP is seeing this as one whole number and therefore is not splitting or exploding it. I hope I am wrong in my thinking and there is a simple solution, though I am not seeing it. Can anyone help me? Richard. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Re: **{SPAM}** [PHP-DB] numeric string to single digit array
Richard, Someone might have a quicker/better way, but what about: ?php $numbers = '1223123'; $numberarray = str_split($numbers,1); print_r($numberarray); ? Thanks, --Dustin Richard Dunne wrote: Can anyone tell me how how to convert a string of integers into an array of single digit integers. i.e. 1223123 into ['1','2,','2','3','1,'2','3'] ? When I retreive a column of single digit integers I end up with a long string of numbers. I think PHP is seeing this as one whole number and therefore is not splitting or exploding it. I hope I am wrong in my thinking and there is a simple solution, though I am not seeing it. Can anyone help me? Richard. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] numeric string to single digit array
Richard Dunne wrote: Can anyone tell me how how to convert a string of integers into an array of single digit integers. i.e. 1223123 into ['1','2,','2','3','1,'2','3'] ? $string = '12345'; $array = array(); for ($i = 0; $i strlen($string); $i++) { $array[] = $string[$i]; } I'm sure there's probably a better way but this is simple and easy to read. When I retreive a column of single digit integers I end up with a long string of numbers. From a database or something? What does your code look like? -- 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] numeric string to single digit array
Please always CC the mailing list so others can learn and also contribute answers. Also please don't top-post as it makes it hard to follow discussions. Richard Dunne wrote: I am using MySQL and retrieving a single column which consists of single digit integers. I have been doing a lot of chopping and changing of my code. $query =Select answer from answers where studentID ='A123456789'; What do you get if you run this query manually through phpmyadmin or a similar tool? -- 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] numeric string to single digit array
Sorry for the top-posting, it's my mail client, not my design. I honestly have not even looked at myphpadmin much, using the CLI mostly. - Original Message - From: Chris [EMAIL PROTECTED] Date: Wednesday, March 26, 2008 0:53 am Subject: Re: [PHP-DB] numeric string to single digit array Please always CC the mailing list so others can learn and also contribute answers. Also please don't top-post as it makes it hard to follow discussions. Richard Dunne wrote: I am using MySQL and retrieving a single column which consists of single digit integers. I have been doing a lot of chopping and changing of my code. $query =Select answer from answers where studentID ='A123456789'; What do you get if you run this query manually through phpmyadmin or a similar tool? -- 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] Table optimization ideas needed
Yes, Index can help a lot. But actually there has been five indices. The table takes 1.4G space while the indices take 2.3G. The select sentence is still slow. :( On Tue, Mar 25, 2008 at 11:50 AM, Chris [EMAIL PROTECTED] wrote: Shelley wrote: Hi all, I made a post a week ago to ask for the idea of the fastest way to get table records. Fyi, http://phparch.cn/index.php/mysql/35-MySQL-programming/126-fastest-way-to-get-total-records-from-a-table Look at the time even a 'count(1)' took. Then you can imagine how much time sql such as select a,b from table_name where c='d' will take. I have a lot of tables like that. So my questions is: What's your practice to optimize tables like that? I pretty much follow what I've said in this article: http://www.designmagick.com/article/16/PostgreSQL/How-to-index-a-database -- Postgresql php tutorials http://www.designmagick.com/ -- Regards, Shelley
Re: [PHP-DB] numeric string to single digit array
Richard Dunne wrote: Sorry for the top-posting, it's my mail client, not my design. I honestly have not even looked at myphpadmin much, using the CLI mostly. It's easy enough to click to another place in your mail client ;) So what happens when you run that query manually? -- 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] Table optimization ideas needed
Shelley wrote: Yes, Index can help a lot. But actually there has been five indices. The table takes 1.4G space while the indices take 2.3G. The select sentence is still slow. :( Post your exact query, table definition(s), indexes and see if anyone has some suggestions. If it's a mysql db, join one of the mysql lists and see if anyone has some suggestions (though they will ask for the same info). Same for any other db. -- 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] Table optimization ideas needed
+--+---+--+-+---++ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+---++ | id | int(11) | | PRI | NULL | auto_increment | | owner_id | int(11) | | MUL | 0 || | owner_name | varchar(50) | | | || | visitor_id | int(11) | | MUL | 0 || | visitor_name | varchar(100) | | | || | visit_time | timestamp | YES | | CURRENT_TIMESTAMP || | first_time | int(10) unsigned | | | 0 || | last_time| int(10) unsigned | | MUL | 0 || | visit_num| mediumint(8) unsigned | | | 0 || | status | tinyint(3) unsigned | | MUL | 0 || +--+---+--+-+---++ That's the table which has more than 20 million records. On Wed, Mar 26, 2008 at 10:20 AM, Chris [EMAIL PROTECTED] wrote: Shelley wrote: Yes, Index can help a lot. But actually there has been five indices. The table takes 1.4G space while the indices take 2.3G. The select sentence is still slow. :( Post your exact query, table definition(s), indexes and see if anyone has some suggestions. If it's a mysql db, join one of the mysql lists and see if anyone has some suggestions (though they will ask for the same info). Same for any other db. -- Postgresql php tutorials http://www.designmagick.com/ -- Regards, Shelley
Re: [PHP-DB] Table optimization ideas needed
Shelley wrote: +--+---+--+-+---++ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+---++ | id | int(11) | | PRI | NULL | auto_increment | | owner_id | int(11) | | MUL | 0 || | owner_name | varchar(50) | | | || | visitor_id | int(11) | | MUL | 0 || | visitor_name | varchar(100) | | | || | visit_time | timestamp | YES | | CURRENT_TIMESTAMP || | first_time | int(10) unsigned | | | 0 || | last_time| int(10) unsigned | | MUL | 0 || | visit_num| mediumint(8) unsigned | | | 0 || | status | tinyint(3) unsigned | | MUL | 0 || +--+---+--+-+---++ That's the table which has more than 20 million records. And what query are you running? What does: explain your_query_here; show? I can see indexes on at least owner_id, visitor_id, last_time and status, but at least one of those is across multiple columns ('MUL'). Can you show us the index definitions: show indexes from table_name; or show create table table_name; and just include the indexes at the bottom. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php