Programming C, C++, Java, PHP, Ruby, Turing, VB
Computer Science Canada 
Programming C, C++, Java, PHP, Ruby, Turing, VB  

Username:   Password: 
 RegisterRegister   
 PHP/MySql assistance needed
Index -> Programming, PHP -> PHP Help
Goto page 1, 2  Next
View previous topic Printable versionDownload TopicSubscribe to this topicPrivate MessagesRefresh page View next topic
Author Message
nickison99




PostPosted: Mon Jul 12, 2004 11:05 pm   Post subject: PHP/MySql assistance needed

Hi,
I have a field in my database which lists phone numbers. I was wondering if somebody could help me out here; I need some code to remove all brackets "(" , ")" and dashes "-" from the phone number (Phone_Number ) field in the db. Any help would be greatly appreciated. Thanks.
Sponsor
Sponsor
Sponsor
sponsor
Amailer




PostPosted: Mon Jul 12, 2004 11:51 pm   Post subject: (No subject)

code:

$phone_number = $row['phone_number']; // info from the database..

$phone_number = str_replace("(", "", $phone_number);
$phone_number = str_replace(")", "", $phone_number);
$phone_number = str_replace("-", "", $phone_number);

echo $phone_number;

that' should do it
wtd




PostPosted: Tue Jul 13, 2004 1:41 am   Post subject: (No subject)

Remove anything that isn't a number or a space. The collapse any spaces to a single space.

code:
$phone_number = // however you get it from the DB
$phone_number = preg_replace("/[^\d\s]/", "", $phone_number);
$phone_number = preg_replace("/\s+/", " ", $phone_number);
nickison99




PostPosted: Tue Jul 13, 2004 8:00 am   Post subject: (No subject)

Hi, THanks for the code. Is there any way that I can do the above to the database (MySQL) so that if I were to look up a phone number in the database it would all be numbers without any formating? Thanks.
wtd




PostPosted: Tue Jul 13, 2004 5:07 pm   Post subject: (No subject)

nickison99 wrote:
Hi, THanks for the code. Is there any way that I can do the above to the database (MySQL) so that if I were to look up a phone number in the database it would all be numbers without any formating? Thanks.


Any other database... fairly easily. MySQL is rather limited. Making changes to the contents of the field from PHP is your best bet.
nickison99




PostPosted: Tue Jul 13, 2004 11:06 pm   Post subject: (No subject)

Thanks.
octopi




PostPosted: Tue Jul 13, 2004 11:15 pm   Post subject: (No subject)

mysql_query("update TABLENAME set Phone_number = REPLACE(Phone_number,'(','')");
mysql_query("update TABLENAME set Phone_number = REPLACE(Phone_number,')','')");
mysql_query("update TABLENAME set Phone_number = REPLACE(Phone_number,'-','')");

that should work, change TABLENAME to the name of the table.
nickison99




PostPosted: Wed Jul 14, 2004 8:58 am   Post subject: (No subject)

GREAT!!! It worked like a charm.
Nick
Sponsor
Sponsor
Sponsor
sponsor
nickison99




PostPosted: Wed Jul 14, 2004 10:44 am   Post subject: (No subject)

Actualy I have another question...
I have a field in the db which stores the postal code (lets call it PostalCode) but I want to have two fields for it. One for the first 3 letters/numbers (lets call it Field1) and the second field (lets call it Field2) would be for the last 3 letters/numbers. The first 3 and the last 3 letters/numbers are separated by a space. Any assistance would be greatly appreciated.
wtd




PostPosted: Wed Jul 14, 2004 4:28 pm   Post subject: (No subject)

You'd use something like:

code:
SELECT CONCAT_WS(" ", my_table.postal_code_1, my_table.postal_code_2) AS postal_code FROM my_table
octopi




PostPosted: Wed Jul 14, 2004 4:54 pm   Post subject: (No subject)

mysql_query("update TABLENAME set Field1 = SUBSTRING_INDEX(PostalCode,' ',1), Field2=SUBSTRING_INDEX(PostalCode,' ',-1)");

This will take the value of 'PostalCode', and split it into two parts, and store those in the db.
wtd




PostPosted: Wed Jul 14, 2004 6:18 pm   Post subject: (No subject)

octopi wrote:
mysql_query("update TABLENAME set Field1 = SUBSTRING_INDEX(PostalCode,' ',1), Field2=SUBSTRING_INDEX(PostalCode,' ',-1)");

This will take the value of 'PostalCode', and split it into two parts, and store those in the db.


But of course first you'll need to create those fields. Smile

code:
ALTER TABLE my_table ADD COLUMN postal_code_1 VARCHAR(3);
ALTER TABLE my_table ADD COLUMN postal_code_2 VARCHAR(3);
nickison99




PostPosted: Wed Jul 14, 2004 8:26 pm   Post subject: (No subject)

Once again - thanks a lot!
Nick
nickison99




PostPosted: Thu Jul 15, 2004 9:12 am   Post subject: (No subject)

This is my last question in this topic... I Promise Very Happy
I am working with my db and I am trying to create a form that updates a persons information. Can someone tell me why the form doesn't update the persons record? I have been working on this for a while and I cannot figure it out.
code:
<?
include ("top.tpl");

require("XX.php");

mysql_connect("$host","$login","$pass") OR DIE
        ("There is a problem with the system.  Please notify your system administrator." .mysql_error());

mysql_select_db("$db") OR DIE
        ("There is a problem with the system.  Please notify your system administrator." .mysql_error());

if(isset($HTTP_POST_VARS['submit'])) {
mysql_query("UPDATE 'MainPlayerInfo' SET Team2004='".addslash($HTTP_POST_VARS[new_team])."' WHERE Player_ID='$HTTP_GET_VARS[id]'");
}


echo "<b>Players in the DB: </b><br>";

echo "<table border=1><tr><td><b>ID</b></td><td><b>Name</b></td><td><b>Assigned Team</b></td><td><b>New Team</b></td><td><b>&nbsp; </b></td></tr>";



$allplayers = mysql_query("SELECT * FROM MainPlayerInfo WHERE Player_ID ='$HTTP_GET_VARS[id]'");
WHILE($play = mysql_fetch_array($allplayers)) {

echo "<tr><td>$play[Player_ID]</td><td>$play[First_Name] $play[Last_Name]</td><td>";

$getteam = mysql_query("SELECT * FROM Teams2004 WHERE TeamID ='$play[Team2004]'");
WHILE($team = mysql_fetch_array($getteam)) {
echo "$team[Team_Name]</a></td><td>";

$nohw=1;

echo "<FORM METHOD=POST ACTION=editteams3.php>";
echo "<SELECT NAME=new_team>";
echo "<OPTION VALUE=$play[Team2004]>$team[Team_Name]</OPTION>";
echo "<OPTION>----------</OPTION>";

$grade = mysql_query("SELECT * from Teams2004  WHERE Division ='$play[Division2004]'");
WHILE ($grd = mysql_fetch_array($grade)) {
echo "<OPTION VALUE=\"$grd[Team2004]\">".$grd[Team_Name]."</OPTION>";
}
echo "</select></td><td><input type=submit name=submit value=\"Save Changes\"></form></td>";
}
}
echo "</table>";
if (!isset($nohw)) { echo "<P>There are no players in the db."; }
include ("bottom.tpl");
?>

I know the code is somewhat sloppy but hopefully somebody can help. Thanks.
Nick
octopi




PostPosted: Thu Jul 15, 2004 3:42 pm   Post subject: (No subject)

On your update line, why do you have single qoutes around the TABLENAME
and can you please describe the tables.
(Names, Fields/types)

you could try adding a
OR DIE
line to the end of the mysql_query
to see if it doesn't complete right, mysql_error should say why.
Display posts from previous:   
   Index -> Programming, PHP -> PHP Help
View previous topic Tell A FriendPrintable versionDownload TopicSubscribe to this topicPrivate MessagesRefresh page View next topic

Page 1 of 2  [ 28 Posts ]
Goto page 1, 2  Next
Jump to:   


Style:  
Search: