[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 811
  • Last Modified:

mysql_query

Hello,

I want to insert information input from a PHP form into 2 different DB table using ; mysql_query("INSERT INTO table name (column name) VALUES (column value)");

One will put all of the fields on the form into a table, the other table will only contain one field from the form.  Can I write both mysql_ queries wtihin th <?php   ?> tags, or will each query have to be within it's own <?php  ?> tags?  either way what should the code look like?

This is what the current mysql_query looks like:
<?php exit(); }
$sql = mysql_query("INSERT INTO users (firstname, lastname, address, city, state, zipcode, phone, email, username, password, market1, production1, market2, production2, market3, production3, doingbusiness, anythingelse, spokenanyone, securitieslicensed, brokerdealer, signupdate, decrypted_password, activated) VALUES ('$txtFirstName', '$txtLastName', '$txtAddress', '$txtCity', '$mnuState', '$txtZipCode', '$txtPhone',  '$txtEmail', '$txtUserName', '$txtPassword', '$chkMarket1', '$mnuProduction1', '$chkMarket2', '$mnuProduction2', '$chkMarket3', '$mnuProduction3', '$txtDoingBiz', '$txtMisc', '$txtSpokenDFW', '$rdoLicensed', '$rdoBrokerDealer', '$signupdate', '$decrypted_pass', '$txtActivated')") or die (mysql_error());
                        if(!$sql){
                      echo 'There has been an error creating your account. Please contact the webmaster.';
                        } else {
                      $userid = mysql_insert_id();
0
JParra72
Asked:
JParra72
5 Solutions
 
Daniel WilsonCommented:
>>Can I write both mysql_ queries wtihin th <?php   ?> tags

Certainly.

Looks good so far ...
0
 
vibrazyCommented:
Yeah you can add within the
<?php
//

?>

<?php
//
 
 
$sql1 = mysql_query("INSERT INTO users (firstname, lastname, address, city, state, zipcode, phone, email, username, password, market1, production1, market2, production2, market3, production3, doingbusiness, anythingelse, spokenanyone, securitieslicensed, brokerdealer, signupdate, decrypted_password, activated) VALUES ('$txtFirstName', '$txtLastName', '$txtAddress', '$txtCity', '$mnuState', '$txtZipCode', '$txtPhone',  '$txtEmail', '$txtUserName', '$txtPassword', '$chkMarket1', '$mnuProduction1', '$chkMarket2', '$mnuProduction2', '$chkMarket3', '$mnuProduction3', '$txtDoingBiz', '$txtMisc', '$txtSpokenDFW', '$rdoLicensed', '$rdoBrokerDealer', '$signupdate', '$decrypted_pass', '$txtActivated')") or die (mysql_error());
                        if(!$sql){
                      echo 'There has been an error creating your account. Please contact the webmaster.';
                        } else {
                      $userid = mysql_insert_id();
}
 
$sql2 = mysql_query("INSERT INTO users (firstname, lastname, address, city, state, zipcode, phone, email, username, password, market1, production1, market2, production2, market3, production3, doingbusiness, anythingelse, spokenanyone, securitieslicensed, brokerdealer, signupdate, decrypted_password, activated) VALUES ('$txtFirstName', '$txtLastName', '$txtAddress', '$txtCity', '$mnuState', '$txtZipCode', '$txtPhone',  '$txtEmail', '$txtUserName', '$txtPassword', '$chkMarket1', '$mnuProduction1', '$chkMarket2', '$mnuProduction2', '$chkMarket3', '$mnuProduction3', '$txtDoingBiz', '$txtMisc', '$txtSpokenDFW', '$rdoLicensed', '$rdoBrokerDealer', '$signupdate', '$decrypted_pass', '$txtActivated')") or die (mysql_error());
                        if(!$sql2){
                      echo 'There has been an error creating your account. Please contact the webmaster.';
                        } else {
                      $userid = mysql_insert_id();
}
 
 
?>

Open in new window

0
 
hieloCommented:
>>Can I write both mysql_ queries wtihin th <?php   ?> tags
Yes

>>either way what should the code look like?

<?php exit(); }
$sql = mysql_query("INSERT INTO users (firstname, lastname, address, city, state, zipcode, phone, email, username, password, market1, production1, market2, production2, market3, production3, doingbusiness, anythingelse, spokenanyone, securitieslicensed, brokerdealer, signupdate, decrypted_password, activated) VALUES ('$txtFirstName', '$txtLastName', '$txtAddress', '$txtCity', '$mnuState', '$txtZipCode', '$txtPhone',  '$txtEmail', '$txtUserName', '$txtPassword', '$chkMarket1', '$mnuProduction1', '$chkMarket2', '$mnuProduction2', '$chkMarket3', '$mnuProduction3', '$txtDoingBiz', '$txtMisc', '$txtSpokenDFW', '$rdoLicensed', '$rdoBrokerDealer', '$signupdate', '$decrypted_pass', '$txtActivated')") or die (mysql_error()); 
                        if(!$sql){
                      echo 'There has been an error creating your account. Please contact the webmaster.';
                        } else {
                      $userid = mysql_insert_id(); 
//at this point you can insert values into your other table:
mysql_query("INSERT INTO otherTable (field) VALUES($userid)") or die(mysql_error());
...
?>

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
JParra72Author Commented:
Thanks for the input.  I have gone and implemented your suggestions and every looks like is running as it should..  

now, what if I want to add info from a different form to the same table,  how can I prevent the same record from being inout twice?  In which case I would just prevent the record from being input without actually having to send an error message to the user.

Thanks
0
 
hieloCommented:
you would typically have contraints in your table that would NOT allow duplicate insertion of certain fields. For example, if you are developing a forum, it would require unique usernames. So in your db table definition, you would need to make sure that the username field is unique. If you try to execute a query which would create a duplicate username, then the insert would fail.

alternatively, you can first execute a select query to see if the data you are trying to insert already exists and if so, do not insert, otherwise proceed with the insertion.
0
 
JParra72Author Commented:
What would the select query syntax look like?
0
 
hieloCommented:
Well, if you are trying to execute:
INSERT INTO TableName(field1,field2) Values(1,4)

then your select would be:
SELECT 1 as `duplicate` FROM TableName where field1=1 AND field2=4

So, if your query returns NO records then you continue with your insert otherwise do nothing:


$result = mysql_query("SELECT 1 as `duplicate` FROM TableName where field1=1 AND field2=4") or die(mysql_error());
 
if( mysql_num_rows($result) == 0 )
{
  mysql_query("INSERT INTO ....");
}

Open in new window

0
 
hieloCommented:
BTW: this:
1 as `duplicate`
is irrelevant. It will NOT be there if no records are returned, but records are returned, you are still NOT using that for anything. It is just a "place holder" so that something is actually returned by the query.
0
 
JParra72Author Commented:
Ok
Check out thr attached snippet.  Does that look right to you?  It's not adding the record into the db table.
I guess there is no connection getting to the db?  How can I resolve this?  Thanks
<?php  //Inserts email into Email database			  
$signupdate = date("d-M-y");
$sql_email_check = mysql_query("SELECT email FROM opt_in_emails WHERE email='$txtEmail'");
if (mysql_num_rows($sql_email_check) == 0)
{mysql_query("INSERT INTO opt_in_emails (sign_up_date, first_name, last_name, email) VALUES ('$signupdate', '$txtFirstName', '$txtLastName', '$txtEmail')"); }
    				  
				    ?>      

Open in new window

0
 
hieloCommented:
The date needs to be in yyyy-mm-dd format. Read below:
<?php
//somewhere here you need to connect to the db first
//so if you are not already connected to the db, the statements below will fail
//you need to connect first
 
//Inserts email into Email database			  
$signupdate = date("Y-m-d");
$sql_email_check = mysql_query("SELECT email FROM opt_in_emails WHERE email='$txtEmail'") or die( "Error executing: " . mysql_error() );
if (mysql_num_rows($sql_email_check) == 0)
{
	mysql_query("INSERT INTO opt_in_emails (sign_up_date, first_name, last_name, email) VALUES ('$signupdate', '$txtFirstName', '$txtLastName', '$txtEmail')");
}
    				  
?>      

Open in new window

0
 
JParra72Author Commented:
Hielo,

Thanks for putting up with me.  Hielo in Spanish means ice (cool).  Anyway, what does the syntax to connect to the db look like?  Where on the form is the code for the connection placed?
0
 
hieloCommented:

<?php
//provide the db connection details
mysql_connect("localhost","username","password") or die( mysql_error() );
 
//specify the name of your database
mysql_select_db("databaseNameHere") or die(mysql_error() );
 
//somewhere here you need to connect to the db first
//so if you are not already connected to the db, the statements below will fail
//you need to connect first
 
//Inserts email into Email database			  
$signupdate = date("Y-m-d");
$sql_email_check = mysql_query("SELECT email FROM opt_in_emails WHERE email='$txtEmail'") or die( "Error executing: " . mysql_error() );
if (mysql_num_rows($sql_email_check) == 0)
{
	mysql_query("INSERT INTO opt_in_emails (sign_up_date, first_name, last_name, email) VALUES ('$signupdate', '$txtFirstName', '$txtLastName', '$txtEmail')");
}
 
?> 

Open in new window

0
 
JParra72Author Commented:
thank you all
0
 
hieloCommented:
you are welcome
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now