How do I insert a variable returned from a select query?

[Scurvy]

I'm not sure what to title this actually

I have a registration form that collects a merchant_code from the refering URL
(www.bobssite.com?merchant_code=5001) as well as the information entered
by the visitor.
If I have a merchant_code of 5001 I want to insert a value of Bob into the level
field of my table. If merchant_code is 5002 I want Joe inserted into the level
field. To find these equalities I use the following:

<?PHP $level = mysql_query("SELECT level FROM merchants 
WHERE merchant_code = '$merchant_code'",$db);

if ($myrow = mysql_fetch_array($level)) {

  echo "
  <table border=0>
    <td>\n";

  echo "<tr>
      <td>\n";
  do {
    printf("</font><tr>
    <td>%s</td>
    </tr>
    <td>\n", $myrow["level"]);

  } while ($myrow = mysql_fetch_array($level));

echo "</font></table></center>
\n";

} else {

echo "<center>Sorry, no records were found!</center>";	

}
?>

This returns 'Bob' if code is 5001 and Joe if 5002.

Now what I want to do is insert the name "Bob" or "Joe" into the field "level" in
the table. This is currently the only way I know how to get info from a db. Thing is, I don't
know how to make this into a variable that can then be inserted back into a
different field.
I've tried using $level but nothing shows up. If I use echo $level I get 'resource ID #2 or sometimes #3.
I want this to be inserted with the other info that the visitor has entered.

$sql = "INSERT INTO xcart_customers
(firstname,lastname,email,password,login,usertype,referer,membership)
VALUES ('$firstname','$lastname','$email','$password1','$login','$usertype','$referer','$level')";

I'm hoping there's an easy way to do this 'cause I'm starting to think I'm over my head here.

Thanks for any help,
Scurvy

[LordShryku]

according to your first snippet, you should be able to use $myrow["level"]

$lever returns a resource id, because that's the ID of the Resource that was used when performing this query

$level = mysql_query("SELECT level FROM merchants

WHERE merchant_code = '$merchant_code'",$db);

[Scurvy]

ok that seems logical. Now, how do I put $myrow["level"] into the insert statement?
I tried it just like that and get parse errors

Parse error: parse error, unexpected T_CHARACTER, expecting T_STRING or T_VARIABLE or T_NUM_STRING in c:\apache\htdocs\pharmacy\danform2.php on line 118

(Probably from the mixture of ' and " in the statement.

I also tried:

$memberlevel = $myrow["level"];

and

$myrow["level"] = $memberlevel;

But they don't work either. In fact, they don't seem to do anything at all.

[LordShryku]

$sql = "INSERT INTO xcart_customers (
           firstname,
           lastname,
           email,
           password,
           login,
           usertype,
           referer,
           membership
        )
        VALUES ( 
           '".$firstname."',
           '".$lastname."',
           '".$email."',
           '".$password1."',
           '".$login."',
           '".$usertype."',
           '".$referer."',
           '".$myrow['level']."'
        )";

[Scurvy]

Well I don't get any errors now but it still doesn't put the value in the membership field.

[LordShryku]

I assume you're pulling the info and inserting all on the same page? Try using this to query the records instead...

$sql = "SELECT level 
        FROM   merchants 
        WHERE  merchant_code = '".$merchant_code."'";

$level = mysql_query($sql);
$rows = mysql_num_rows($level);
if($rows > 0) {
   echo "<table border=\"0\">";
   while($myrow = mysql_fetch_array($level)) {
      echo "<tr>
              <td>".$myrow['level']."</td>
           </tr>";
   }
   echo "</table>";
} 
else {
    die("<center>Sorry, no records were found!</center>");
}

[Scurvy]

That is correct. I tried that code with no result. In fact, it doesn't submit anything now. I changed the '?.$merchant_code.'=5001 but still doesn't work. I have it display and it says No records found.

[LordShryku]

Okay, show us the whole page, and how you are calling it. If this ('?.$merchant_code.'=5001) is what you're putting in the URL, that's the wrong way of doing it. It would be ?merchant_code=5001

[Scurvy]

This is the original code.

<html>

<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 1</title>
</head>
<body>
<?PHP
include ('i_header.asp');
include ('xcart_connect.inc');

if (isset($_POST['submit'])) {
  // process form
$timenow = date("Y-m-d h:i:s");
$login = $email;
$usertype = 'C';
$merchant_code = $_GET['merchant_code'];

?>
<br>

<?php

$sql = "INSERT INTO xcart_customers
(firstname,lastname,email,password,login,usertype,referer,
membership) VALUES
('$firstname','$lastname','$email','$password1','$login','$usertype',
'$referer','$level')"; 
}
$result = mysql_query($sql) or die (mysql_error());


  // Display successful submit

if($result)
{
    echo "<table align='center' width='60%'>
    <tr><td>Thank you $firstname, your registration is complete! 
 Click here to start saving money!<br>
    </td></tr></table>\n";

  // Begin Mail Action

// Customer
$to = "{$_POST['email']}";

// Administrative email addresses
$tojeff = "[e-mail]";
	// Resolves to Jeff
//  $tojoe = "[e-mail]";
	// Resolves to Joe
//  $toss = "";
	// Resolves to Dan

  // subject
$subadmin = "New Customer Registration";
$subcustomer = "customer registration";

// Customers email message

// End Customer Message

// admin message 

$msgadmin = "
<div align='center'>
<font face='Arial' size='2'>
<p>The following new customer was registered to the $site 
website and<br>inserted into the 
database at $timenow.</p>
<p></p>
<p align='left'>Source: $referer<br>
Name: $firstname $lastname<br>
Email: $email</p>
</font>
";

// headers
$headers  = "MIME-Version: 1.0\r\n";
$headers .= "Content-type: text/html; charset=iso-8859-1\r\n";

// additional headers
$headers .= "From: <[e-mail]>\r\n";

// Remove the comments to use the cc function
// $headers .= "Cc: [email][e-mail][/email]\r\n";

// and now mail it


mail($to, $subcustomer, $msgcustomer, $headers); // To Customer
mail($tojeff, $subadmin, $msgadmin, $headers);  // Mails to Jeff
//	mail($tojoe, $subadmin, $msgadmin, $headers); // Mails to Joe
//	mail($todan, $subadmin, $msgadmin, $headers); // Mails to Dan 

// End Mail Action

}
else
{
    echo "Insert failed";
}

} else{


  // display html and form

  ?>
<div align="center">
<table width='80%'>
	<tr>
		<td><b>Please fill out the form below to register.</b>
		</td>
	</tr>
	<tr>
		<td>
          <p align="center"><b>NOTE: Items marked with a <font color="#FF0000">*</font>
          are required fields</b>
		</td>
	</tr>
</table>

  <form method="post" action="<?php echo $_SERVER['PHP_SELF'] ; ?>">
 <?php echo "<input type='hidden' name='referer' value='{$_SERVER['HTTP_REFERER']}'>"; ?>
    <center>
    <table border="1" width="484">
      <tr>
         <td width="205">

  First Name:<b> <font color="#FF0000">*</font>
  </b>
         </td>
        <td width="263"><input type="Text" name="firstname" size="20"></td>
      </tr>
      <tr>
         <td width="205">

  Last Name:<b> <font color="#FF0000">*</font>
  </b>
         </td>
        <td width="263"><input type="Text" name="lastname" size="20"></td>
      </tr>

  <tr>
     <td width="205">

  Email:<b> <font color="#FF0000">*</font>
  </b>
         </td>
        <td width="263"><input type="Text" name="email" size="30"></td>
      </tr>

  <tr>
     <td width="205">

  Password:<b> <font color="#FF0000">*</font>
  </b>
         </td>
        <td width="263"><input type="Text" name="password1" size="20"></td>
      </tr>

  <tr>
     <td width="205">

  Re enter Password:<b> <font color="#FF0000">*</font>
  </b>
         </td>
        <td width="263"><input type="Text" name="password2" size="20"></td>
      </tr>
	  <tr>
         <td width="205">

  Re enter Password:<b> <font color="#FF0000">*</font>
  </b>
         </td>
        <td width="263"><?PHP echo $_SERVER['HTTP_REFERER'];  ?></td>
      </tr
<tr>
         <td width="205">

  Referrer:<b> <font color="#FF0000">*</font>
  </b>
         </td>

    <td width="263">	<?php
   $level = mysql_query("SELECT level FROM merchants WHERE
 merchant_code = '$merchant_code'",$db);

if ($myrow = mysql_fetch_array($level)) {

  echo "
  <table border=0>
    <td>\n";

  echo "<tr>
      <td>\n";
  do {
    printf("</font><tr>
    <td>%s</td>
    </tr>
    <td>\n", $myrow["level"]);

  } while ($myrow = mysql_fetch_array($level));

echo "</font></table></center>
\n";

} else {

echo "<center>Sorry, no records were found!</center>";	

}

?></td>
      </tr>

</table>
</center>
   <p align="center">
   <input type="Submit" name="submit" value="Submit Information">
  <input type="Reset" name="reset" value="Reset Form">

  </p>
  </form>

</div>

<?PHP

} // end if

?>
</body>

</html>

I appreciate your help LordShryku
Scurvy

[LordShryku]

Okay, so I'm scouring through this code (I deal with forms all day, so you owe me a beer :p), and here's what I guess....

1) You call the page at first with the merchant_code appended to the URL
2) Since you are using PHP_SELF as the form action, it loses the merchant ID upon submittion, because you do not include the $_SERVER['argv'].

IF this is so far correct, I'd suggest adding a hidden form field for the merchant code. I'm going through it all right now, haven't quite finished yet, but lemme know if this is true....

[Scurvy]

You are correct so far. I didn't know about $_SERVER['argv'] though.

[LordShryku]

Lotta guess work. The notice on including an asp page almost made me throw up, but I kept on :p

Give this a shot. Lemme know....

<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 1</title>
</head>
<body>

<?
include ('i_header.asp');
include ('xcart_connect.inc');

if(isset($_POST['submit'])) {
   $timenow = date("Y-m-d h:i:s");
   $usertype = "C";

   echo "<br>";

   $sql = "INSERT INTO xcart_customers (
              firstname,lastname,email,password,login,usertype,referer,membership
           )
           VALUES
           (
              '".$_POST['firstname']."','".$_POST['lastname']."','".$_POST['email']."','".$_POST['password1']."',
              '".$_POST['email']."','".$usertype."','".$_POST['referer']."','".$_POST['level']."'
           )"; 
   /* 
     Uncomment the below line to echo 
     the SQL to the screen and check
     to make sure all of the values
     look okay, and are in the right places 
   */

   // echo $sql

   $result = mysql_query($sql) or die (mysql_error());

   if($result) {
      echo "<table align=\"center\" width=\"60%\">
              <tr>
                <td>Thank you ".$_POST['firstname'].", your registration is complete!
                    Click here to start saving money!
                </td>
              </tr>
            </table>\n";

  $to = $_POST['email'];
  $tojeff = "[e-mail]";
  $tojoe = "[e-mail]";
  $toss = "";

  $subadmin = "New Customer Registration";
  $subcustomer = "customer registration";

  $msgadmin = "<div align=\"center\">
               <font face=\"Arial\" size=\"2\">
               <p>The following new customer was registered to the ".$site." website and
               <br>inserted into the database at ".$timenow.".</p>
               <p></p>
               <p align=\"left\">Source: ".$_POST['referer']."
               <br>Name: ".$_POST['firstname']." ".$_POST['lastname']."
               <br>Email: ".$_POST['email']."</p>
               </font>";

  $headers  = "MIME-Version: 1.0\r\n";
  $headers .= "Content-type: text/html; charset=iso-8859-1\r\n";
  $headers .= "From: <[e-mail]>\r\n";
  // $headers .= "Cc: [email][e-mail][/email]\r\n";

  mail($to, $subcustomer, $msgcustomer, $headers);
  mail($tojeff, $subadmin, $msgadmin, $headers);
  mail($tojoe, $subadmin, $msgadmin, $headers);
  mail($todan, $subadmin, $msgadmin, $headers);

   }
   else {
      echo "Insert failed";
   }

} 
else {
?>

<div align="center">
<table width="80%">
  <tr>
    <td><b>Please fill out the form below to register.</b></td>
  </tr>
  <tr>
    <td><p align="center"><b>NOTE: Items marked with a <font color="#FF0000">*</font>are required fields</b></td>
  </tr>
</table>

<form method="post" action="<?php echo $_SERVER['PHP_SELF'] ; ?>">
<input type="hidden" name="referer" value="<? echo $_SERVER['HTTP_REFERER']; ?>">
<input type="hidden" name="merchant_code" value="<? echo $_GET['merchant_code']; ?>">
<center>
<table border="1" width="484">
  <tr>
    <td width="205">First Name:<b> <font color="#FF0000">*</font></b></td>
    <td width="263"><input type="Text" name="firstname" size="20"></td>
  </tr>
  <tr>
    <td width="205">Last Name:<b> <font color="#FF0000">*</font></b></td>
    <td width="263"><input type="Text" name="lastname" size="20"></td>
  </tr>
  <tr>
    <td width="205">Email:<b> <font color="#FF0000">*</font></b></td>
    <td width="263"><input type="Text" name="email" size="30"></td>
  </tr>
  <tr>
    <td width="205">Password:<b> <font color="#FF0000">*</font></b></td>
    <td width="263"><input type="Text" name="password1" size="20"></td>
  </tr>
  <tr>
    <td width="205">Re enter Password:<b><font color="#FF0000">*</font></b></td>
    <td width="263"><input type="Text" name="password2" size="20"></td>
  </tr>
  <tr>
    <td width="205">Referrer:<b><font color="#FF0000">*</font></b></td>
    <td width="263"><?PHP echo $_SERVER['HTTP_REFERER'];  ?></td>
  </tr>
  <tr>
    <td width="205">Level:</td>
    <td width="263">

<?
   $level = mysql_query("SELECT level 
                         FROM   merchants 
                         WHERE  merchant_code = '".$_GET['merchant_code']."'");

   $rows = mysql_num_rows($level);
   if($rows > 0) {
      echo $myrow['level'];
      $hiddenLevel="<input type=\"hidden\" name=\"level\" value=\"".$myrow['level']."\">";
   }
   else {
      echo "Sorry, no records were found!";
   }
?>
    </td>
  </tr>
</table>
<?
echo $hiddenLevel;
?>
<p align="center">
<input type="Submit" name="submit" value="Submit Information">
<input type="Reset" name="reset" value="Reset Form">
</p>
</form>
</div>

<?
}
?>
</body>

</html> 

[Scurvy]

No change really. It submits everything but the 'level' field.
When I uncomment the echo sql; it shows everything going in but the 'level' in the VALUES area is empty.

INSERT INTO xcart_customers ( firstname,lastname,email,password,login,usertype,referer,
membership ) VALUES ( 'jeff','Simpson','[e-mail]','pw',
'[e-mail]','C','http://www.mysite.com/1news.htm','' )

[LordShryku]

Well...make sure the level is actually getting pulled then. I had it create a post var for level only if it was found in the database.

[Scurvy]

Well, it shows up if I echo it out my original way but still won't enter into the database.
I'm takin' a break for a while though.
You gave me some ideas that I'll try out after I get that beer I owe you 🙂
Your help has been well above what I expected.

BTW = WOOHOO!! YANKEES WIN, YANKEES WIN !!!!

hehe