Computer Science Canada

PHP/MySql assistance needed
Author:  nickison99 [ 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.
Author:  Amailer [ Mon Jul 12, 2004 11:51 pm ]
Post 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
Author:  wtd [ Tue Jul 13, 2004 1:41 am ]
Post 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);
Author:  nickison99 [ Tue Jul 13, 2004 8:00 am ]
Post 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.
Author:  wtd [ Tue Jul 13, 2004 5:07 pm ]
Post 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.
Author:  nickison99 [ Tue Jul 13, 2004 11:06 pm ]
Post subject: 
Thanks.
Author:  octopi [ Tue Jul 13, 2004 11:15 pm ]
Post 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.
Author:  nickison99 [ Wed Jul 14, 2004 8:58 am ]
Post subject: 
GREAT!!! It worked like a charm.
Nick
Author:  nickison99 [ Wed Jul 14, 2004 10:44 am ]
Post 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.
Author:  wtd [ Wed Jul 14, 2004 4:28 pm ]
Post 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
Author:  octopi [ Wed Jul 14, 2004 4:54 pm ]
Post 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.
Author:  wtd [ Wed Jul 14, 2004 6:18 pm ]
Post 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);
Author:  nickison99 [ Wed Jul 14, 2004 8:26 pm ]
Post subject: 
Once again - thanks a lot!
Nick
Author:  nickison99 [ Thu Jul 15, 2004 9:12 am ]
Post 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
Author:  octopi [ Thu Jul 15, 2004 3:42 pm ]
Post 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.
Author:  wtd [ Thu Jul 15, 2004 5:03 pm ]
Post subject: 
And for the love of Pete, man! Indent! Smile
Author:  nickison99 [ Thu Jul 15, 2004 6:29 pm ]
Post subject: 
octopi wrote:
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.

The table name that gets updated is "MainPlayerInfo" and the field that should be updated is "Team2005"(varchar(50) - will be char(2) later). The value that should be in there is the value from the pulldown menu named "New_Team2005". I have been tinkering with it for hours now and still cannot get it to work.
Author:  nickison99 [ Thu Jul 15, 2004 6:32 pm ]
Post subject: 
wtd wrote:
And for the love of Pete, man! Indent! Smile

Next time, If I need to post this again i will indent it. Whenever i start tinkering with code I don't care about the indentation until I get it to work. Very Happy
Author:  wtd [ Thu Jul 15, 2004 6:33 pm ]
Post subject: 
nickison99 wrote:
wtd wrote:
And for the love of Pete, man! Indent! Smile

Next time, If I need to post this again i will indent it. Whenever i start tinkering with code I don't care about the indentation until I get it to work. Very Happy


You might just find you get it to work faster by making it easier to read. Wink
Author:  nickison99 [ Thu Jul 15, 2004 6:37 pm ]
Post subject: 
True - (indentation in progress...)
For future refrence is there a program that is good for php editing that includes and indent feature(like Turing)?
THanks.
Nick
Author:  wtd [ Thu Jul 15, 2004 6:43 pm ]
Post subject: 
http://zend.com/store/evaluation.php?pid=77&download=1
Author:  octopi [ Thu Jul 15, 2004 11:07 pm ]
Post subject: 
nik wrote:
$getteam = mysql_query("SELECT * FROM Teams2004 WHERE TeamID ='$play[Team2004]'");


okay, so why then are you trying to read data from the 'Teams2004' table?
Author:  nickison99 [ Fri Jul 16, 2004 11:36 am ]
Post subject: 
That chunk of code reads the persons team number (from MainPlayerInfo) and checks the teams table to see what team the team number refers to.
Author:  octopi [ Fri Jul 16, 2004 12:06 pm ]
Post subject: 
This part makes no sense, based on your other code.

WHERE Player_ID='$HTTP_GET_VARS[id]'
Author:  nickison99 [ Fri Jul 16, 2004 12:09 pm ]
Post subject: 
I made one change to the code:
code:
echo "<FORM METHOD=POST ACTION=editteams3.php>";

is now:
code:
echo "<FORM METHOD=POST ACTION=editteams3.php?id=$play[Player_ID]>";
.
Author:  octopi [ Fri Jul 16, 2004 12:18 pm ]
Post subject: 
change

code:
Player_ID='$HTTP_GET_VARS[id]'



to

code:
Player_ID='$HTTP_POST_VARS[id]'


remove the
code:
.php?id=BLAH
from the action


add
code:
<input type=hidden name=id value=BLAH>


to your form.



Also read my other posts and follow the directions such as mysql_error()
tell me what those show, make sure that the update line is acctually being reached (does the
code:
if(isset($HTTP_POST_VARS['submit'])) {

acctually get reached.)

also give us a url of the hting, if you haven't already
Author:  nickison99 [ Fri Jul 16, 2004 12:24 pm ]
Post subject: 
Hi, Can we continue this through PM? There is some information on the site that I cannot give out so I would like to pm you the link instead.
Author:  nickison99 [ Fri Jul 16, 2004 1:26 pm ]
Post subject: 
Problem Fixed! Thanks to octopi and wtd for all their help!
Nick

:


You can syndicate this boards posts using the file backend.php or view the topic map using sitemap.php.

Terms of Use | Privacy Policy