PHP MySQL SELECT INSERT

Report · Jan 10, 2011

I have a registration page with a html form. I have created an insert record server behavior that insert all the data into a MySQL database table fine. What I like to do additionally is to insert the data from a single row, actually the last row, into another table in the same mysql database. I have heard of MySQL insert-select but I can't seem to make it work on my own. Is insert-select possible with Dreamweaver? If not, what should I use?

I appreciate any help

Report · Jan 10, 2011

Inserting all data into another table is redundant. You only need to pass the primary key to a new table when necessary and use the JOIN argument in your query to JOIN data from seperate tables for one user.

Report · Jan 10, 2011

Sorry if I was misleading. I like to pass the data from 3 columns of the last row of the table to another table.

Report · Jan 10, 2011

Sorry if you misunderstood my previous reply. Passing more than the primary key to another table is redundant.

Report · Jan 10, 2011

Show us the select insert statement that you tried and is not working. As TS stated, inserting redundant data is usually not necessary, but there are definitely exceptions.

Report · Jan 10, 2011

Here's the entire php code but the last part in bold is not working:

<?php require_once('Connections/myConn.php'); ?>

<?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

if (PHP_VERSION < 6) {

$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

}

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {

case "text":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "long":

case "int":

$theValue = ($theValue != "") ? intval($theValue) : "NULL";

break;

case "double":

$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

break;

case "date":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "defined":

$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

break;

}

return $theValue;

}

// *** Redirect if username exists

$MM_flag="MM_insert";

if (isset($_POST[$MM_flag])) {

$MM_dupKeyRedirect="already-exits.html";

$loginUsername = $_POST['userName'];

$LoginRS__query = sprintf("SELECT userName FROM new_members WHERE userName=%s", GetSQLValueString($loginUsername, "text"));

mysql_select_db($database_myConn, $myConn);

$LoginRS=mysql_query($LoginRS__query, $myConn) or die(mysql_error());

$loginFoundUser = mysql_num_rows($LoginRS);

//if there is a row in the database, the username was found - can not add the requested username

if($loginFoundUser){

$MM_qsChar = "?";

//append the username to the redirect page

if (substr_count($MM_dupKeyRedirect,"?") >=1) $MM_qsChar = "&";

$MM_dupKeyRedirect = $MM_dupKeyRedirect . $MM_qsChar ."requsername=".$loginUsername;

header ("Location: $MM_dupKeyRedirect");

exit;

}

$editFormAction = $_SERVER['PHP_SELF'];

if (isset($_SERVER['QUERY_STRING'])) {

$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);

}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "register")) {

$insertSQL = sprintf("INSERT INTO new_members (userName, password, confirmPassword, email, confirmEmail, datingSite, notifyMe, agreeToTerms) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)",

GetSQLValueString($_POST['userName'], "text"),

GetSQLValueString($_POST['password'], "text"),

GetSQLValueString($_POST['confirmPassword'], "text"),

GetSQLValueString($_POST['email'], "text"),

GetSQLValueString($_POST['confirmEmail'], "text"),

GetSQLValueString($_POST['datingSite'], "text"),

GetSQLValueString(isset($_POST['notifyMe']) ? "true" : "", "defined","'Y'","'N'"),

GetSQLValueString(isset($_POST['agreeToTerms']) ? "true" : "", "defined","'Y'","'N'"));

mysql_select_db($database_myConn, $myConn);

$Result1 = mysql_query($insertSQL, $myConn) or die(mysql_error());

$insertGoTo = "selectplan.php";

if (isset($_SERVER['QUERY_STRING'])) {

$insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";

$insertGoTo .= $_SERVER['QUERY_STRING'];

}

header(sprintf("Location: %s", $insertGoTo));

}

mysql_select_db($database_myConn, $myConn);

$query_rsNotify = "INSERT INTO be_notified (new_members.email, new_members.datingSite, new_members.userName) SELECT new_members.userName, new_members.email, new_members.datingSite FROM new_members WHERE new_members.notifyMe = 'y' ORDER BY newMemberID DESC LIMIT 1 ";

$rsNotify = mysql_query($query_rsNotify, $myConn) or die(mysql_error());

$row_rsNotify = mysql_fetch_assoc($rsNotify);

$totalRows_rsNotify = mysql_num_rows($rsNotify);

?>

Report · Jan 10, 2011

What exactly is the reason that you're trying to enter information into the be_notified table? FWIW your query is not correct for insert because you have not declared values. You should execute a SELECt query to SELECT the data that you'd like to INSERT and then execute an INSERT query with values from the SELECT query like the example below. Also you're trying to execute the query after the header is sent, which will not work because the user will have been redirected before the query is run. So move the query before the header function. As previously mentioned: this is completely redundant IMO.

mysql_select_db($database_myConn, $myConn);

$Result1 = mysql_query($insertSQL, $myConn) or die(mysql_error());

$query_rsNotify2 = "SELECT userName, email, datingSite FROM new_members WHERE notifyMe = 'y' ORDER BY newMemberID DESC LIMIT 1 ";

$rsNotify2 = mysql_query($query_rsNotify2, $myConn) or die(mysql_error());

$row_rsNotify2 = mysql_fetch_assoc($rsNotify2);

$insertSQL2 = sprintf("INSERT INTO be_notified (email, datingSite, userName) VALUES (%s, %s, %s)",

GetSQLValueString($row_rsNotify2['email'], "text"),

GetSQLValueString($row_rsNotify2['datingSite'], "text"),

GetSQLValueString($row_rsNotify2['userName'], "text"));

$Result12 = mysql_query($insertSQL2, $myConn) or die(mysql_error());

$insertGoTo = "selectplan.php";

if (isset($_SERVER['QUERY_STRING'])) {

$insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";

$insertGoTo .= $_SERVER['QUERY_STRING'];

}

header(sprintf("Location: %s", $insertGoTo));

}

?>

Report · Jan 10, 2011

The order of your insert fields is not the same as your select fields which is probably why it is failing.

Also, I don't really see a need for be_notified table. Can't those fields just be added to the user table? Also, why is the table called new_user? Is there a regular 'user' table?

What happens when 2 users register at the same time? User1 inserts into the new_user table and before the be_notified script fires, another users registers. Now the first be_notified scripts inserts the wrong data.

Also, why inser the confirm_password and confirm_email data? There should never be a reason that those fields are different - it needs to be validated before your insert.

Report · Jan 10, 2011

Ah, yes as TS found, you are redirecting before the sql gets executed. But it will still not work after you fix that because of the field order issue.

Report · Jan 10, 2011

Thank you both. The reason for two tables is being notified is a feature not all users select to have and some that will select it need to be notified when a post about them has been written and they need to be notified privately. Does that explain it?

If you think this is redundant, then how should it be done?

Report · Jan 10, 2011

being notified is a setting that is already in table 1. So, again, why put it into table 2? It should be done by SELECT-ing data from table1 WHERE being_notified = yes AND (optional) person_written_about = parameter passed to query identifying the user id that something was written about.

Report · Jan 10, 2011

Yes of course, you're absolutely right. No wonder I was struggling with it so much. Thank you and by the way your fix also worked!