Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

MYSQL help

mysqlhelp mysql

  • Please log in to reply
5 replies to this topic

#1 esial

esial

    CC Newcomer

  • Member
  • PipPip
  • 17 posts
  • Programming Language:PHP
  • Learning:PHP, JavaScript, Perl, PL/SQL, Transact-SQL

Posted 15 September 2012 - 08:47 AM

hi,
I wanted to copy email addresses from one table to another. i used this command below and it worked
"INSERT INTO data (email_address) SELECT email_address FROM cw_users";

What i need is, every time when new users register i want their email address to be added to the DATA table. With this above command every time i refresh all the email addresses gets inserted into the database AGAIN.

What should i do to get this fixed?
thanks,
  • 0

#2 VNFox

VNFox

    CC Devotee

  • Senior Member
  • PipPipPipPipPipPip
  • 648 posts
  • Programming Language:C#, PHP
  • Learning:Assembly

Posted 15 September 2012 - 06:07 PM

i don't understand ... it looks like you're inserting all the emails from cw_users ... everytime ... so you ended with lots of duplicated email.

You can look into MYSQL Trigger ... you can bu ild a tigger to do that ... but I'm pretty sure there is something wrong with your logic here even without the trigger.

You may only need to insert email for a new registered user only (user_id - maybe)
  • 0

www.pickmike.com
I don't just develop software. I find solutions to your business needs.


#3 esial

esial

    CC Newcomer

  • Member
  • PipPip
  • 17 posts
  • Programming Language:PHP
  • Learning:PHP, JavaScript, Perl, PL/SQL, Transact-SQL

Posted 15 September 2012 - 07:17 PM

that's right, i need to insert new registered users only to the DATA table.
Is there a command that you can help me with or direct me to some tutorials.
  • 0

#4 kernelcoder

kernelcoder

    CC Devotee

  • Expert Member
  • PipPipPipPipPipPip
  • 990 posts
  • Location:Dhaka
  • Programming Language:C, Java, C++, C#, Visual Basic .NET
  • Learning:Objective-C, PHP, Python, Delphi/Object Pascal

Posted 16 September 2012 - 02:33 AM

Well, as VNFox said, your task can be performed automatically by creating a MYSQL TRIGGER. I'm assuming that you have a MySQL database name 'test'. Following are the MySQL commands I wrote to test.
USE test; // selecting test database;

CREATE TABLE cw_users (email_address varchar(100)); // create cw_users table
CREATE TABLE data (email_address varchar(100)); // create data table

DELIMITER $$ /* change delimiter to something new than ; we need to set sql command in our trigger which will have ; as delimiter */

/* Creating the Trigger*/
CREATE TRIGGER copy_emailaddress_from_users_to_data AFTER INSERT ON cw_users FOR EACH ROW BEGIN INSERT INTO data VALUES(NEW.email_address); END$$

/* back our delimiter to ;*/
DELIMITER ;

/* Inserting an entry in the table cw_users*/
insert into cw_users VALUES ('mysql1@sql.com');

/* Now let us check the value in data table*/
SELECT * FROM data;
/* I hope you will see the following data in MySQL client console <img src='http://img.codecall.net/public/style_emoticons/<#EMO_DIR#>/smile.png' class='bbc_emoticon' alt=':)' /> */

+----------------+
| email_address |
+----------------+
| mysql1@sql.com |
+----------------+
;
  • 1

#5 esial

esial

    CC Newcomer

  • Member
  • PipPip
  • 17 posts
  • Programming Language:PHP
  • Learning:PHP, JavaScript, Perl, PL/SQL, Transact-SQL

Posted 16 September 2012 - 10:11 AM

Thank you very much.
I have another question?
Could you please let me know how to put this code in php. i'm getting errors. i also read that delimiters cannot be used in php.
  • 0

#6 kernelcoder

kernelcoder

    CC Devotee

  • Expert Member
  • PipPipPipPipPipPip
  • 990 posts
  • Location:Dhaka
  • Programming Language:C, Java, C++, C#, Visual Basic .NET
  • Learning:Objective-C, PHP, Python, Delphi/Object Pascal

Posted 16 September 2012 - 04:54 PM

I have another question?
Could you please let me know how to put this code in php. i'm getting errors. i also read that delimiters cannot be used in php.

That's the point -- you don't need to use delimiter with mysql_query method. I'm quoting from the manual about the query argument to mysql_query method -- "The query string should not end with a semicolon".

The following PHP code works fine for me --
<?php
$con = mysql_connect("localhost","root","root");
if (!$con) {
die('Could not connect: ' . mysql_error());
}

mysql_select_db("test2", $con);
mysql_query("CREATE TABLE cw_users (email_address varchar(100))");
mysql_query("CREATE TABLE data (email_address varchar(100))");

$result = mysql_query("CREATE TRIGGER copy_emailaddress_from_users_to_data AFTER INSERT ON cw_users FOR EACH ROW BEGIN INSERT INTO data VALUES(NEW.email_address); END");
if (!$result) {
die('Invalid query:CT --' . mysql_error());
}

mysql_query("INSERT INTO cw_users VALUES ('mysql1@sql.com')");

$result = mysql_query("SELECT * FROM data");
while($row = mysql_fetch_array($result)) {
echo $row['email_address'];
echo "<br />";
}

mysql_close($con);
?>


However, I don't think you need to put the trigger creating query into PHP script. You should create the trigger only once when you design your database. Are you creating the tables using PHP script? If yes, creating trigger from PHP is also ok.
  • 1





Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download