Jump to content


Photo

move mysql record with changes


  • Please log in to reply
14 replies to this topic

#1 quasiman

quasiman
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts
  • LocationPortland, Oregon

Posted 06 July 2006 - 05:15 PM

I have two mysql tables, and I'd like to copy the records from one to the other.  The first table has an email address field - "user@domain.com", but when it's being copied I want to shorten it down to just "user".

Any ideas?

#2 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 06 July 2006 - 05:20 PM


substr($email,0,4);

update new table ok.
Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#3 jvrothjr

jvrothjr
  • Members
  • PipPipPip
  • Advanced Member
  • 300 posts
  • LocationOhio

Posted 06 July 2006 - 05:33 PM

That works ok if all user email are only 4 char long

try this have not tested the code but this would be the general layout

$strcnt = strlen($email);
for ($i=0; $i <= $strcnt; $i++) {
	$chkchar = (substr($email,$i,1))
	if ($chkchar = "@") {$i = $strcnt;} else {$username = $username + $chkchar;}
}

If you understand everything you know nothing!

http://rcchjr.awardspace.com/

#4 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 06 July 2006 - 06:06 PM


you dont need all that code do this ok.

$takeaway_email=(eregi_replace("@[a-z0-9\-]+\.[a-z0-9\-\.]+$","",$email));
Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#5 quasiman

quasiman
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts
  • LocationPortland, Oregon

Posted 06 July 2006 - 08:44 PM

Wow, thanks for the responses!

So would something like this work?

$takeaway_email=(eregi_replace("@[a-z0-9\-]+\.[a-z0-9\-\.]+$","",$email));

mysql_query("INSERT INTO table_name2 
	VALUES (firstname, email,....) 
	FROM table_name1 
	VALUES (firstname, $takeaway_email,....)") 
or die(mysql_error());


#6 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 06 July 2006 - 08:58 PM

select table // to get email address's.

put the new code // the code i provided.


insert to new table // insert new code in a diffrent table.

bobs your uncle new database got all names and no @whoever . coms

good luck.


Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#7 quasiman

quasiman
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts
  • LocationPortland, Oregon

Posted 07 July 2006 - 08:44 PM

Ok....I like that you're making me work for this  ;)

<?php

$dbhost = "localhost";
$dbuser = "mydbusername";
$mydbpass = "mydbpassword";
$dbtable1 = "1sttable";
$dbtable2 = "newtable";


$con = mysql_connect($dbhost,$dbuser,$dbpass);
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db($mydbase, $con);

//get the email address from the first table
$getmail = 'SELECT to_address FROM $dbtable1';
mysql_query($getmail) or die(mysql_error());

//get the other data that's important
$userdata = 'SELECT userlocation, yahooid,  FROM $dbtable1';
mysql_query($userdata) or die(mysql_error());

//take out the 'domain.com' part of the email address
$takeaway_email=(eregi_replace("@[a-z0-9\-]+\.[a-z0-9\-\.]+$","",$getmail));

//insert the username and other data into the 2nd table
$add_user = 'INSERT INTO $dbtable2 (username, userlocation, yahooid) VALUES('$takeaway_email', 'Portland', 'my_yahooid')';
mysql_query($add_user) or die(mysql_error());


?>


#8 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 07 July 2006 - 10:01 PM

make me lol....................

what's wrong know lol.....................
Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#9 quasiman

quasiman
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts
  • LocationPortland, Oregon

Posted 07 July 2006 - 10:07 PM

Well I'm just asking...is that a correct way to do it?  Am I missing anything?

#10 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 07 July 2006 - 10:11 PM

WHERE id='$id'

if its a website for members ok do this so that users info gets updataed ok.

or it's cool star 10/10


also dont forget this as you dont want sql injection 

if($_POST['submit']) {

code

}

Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#11 quasiman

quasiman
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts
  • LocationPortland, Oregon

Posted 07 July 2006 - 10:36 PM

I'll be using this as an include to the index page, so everytime the index is loaded this file runs with no output to the user that it's happened.

I don't really need to worry about sql injection for that do I?

#12 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 07 July 2006 - 10:41 PM

no but add

add this above new data entry ok.
$takeaway_email=$_POST['takeaway_email'];
$takeaway_email=stripslashes($takeaway_email);

good luck mate.

Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#13 quasiman

quasiman
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts
  • LocationPortland, Oregon

Posted 07 July 2006 - 10:49 PM

Thanks for all the help!!

#14 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 07 July 2006 - 10:52 PM

no problam ok
Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#15 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,022 posts

Posted 08 July 2006 - 07:33 AM

You need to worry about SQL injection if you are writing a value to your database which originates from a user-controlled source. These are $_GET, $_POST and $_COOKIE (GPC) and $_REQUEST (which gets all 3).

In these cases, if magic_quotes are off and not adding slashes automatically then you you need to addslashes().

Use stripslashes() when magic_quotes has added them but you want to display the $_POST['var'] etc on the page.

See http://www.php.net/g...agic_quotes_gpc


BTW, You can accomplish your update with
INSERT INTO table2 (a, b, email, c) 
SELECT a, b, SUBSTRING(email, 1, INSTR(email,'@')-1), c FROM table1
where a,b,c are your other column names
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users