selecting from MSSQL using variable from $_POST not working

[FrankieGee]

Troubles abound.

 

What I am trying to achieve is to populate a form using data from a MSSQL database.

First, the user will select a company from a dropdown list **WORKING**

 

Then, the company name will appear on the form **WORKING**

 

Now, as the code is set below, the Customer Number, Address, etc WILL populate, but only from the last record in the database.

While the company name will change upon selection of the dropdown menu, everthing else remains the same.

 

I am using $_POST to populate the company name from the dropdown.

Here is my code:

echo "<table border ='0' width='90%'><tr><td align='center'><strong><font size='4'><font color='#008301'>Load Customer</font></strong></td></tr></table>";
echo "<table border ='1' width='90%'><tr><td>";
echo "<form method=\"post\" action='salesquote.php'>";
echo "<select name=\"pcsname\">";			
echo "<option>Select Company</option>";
$sql="select csname, cscode from customer
where sacode = $sacode 
order by csname";
				
    $rs=odbc_exec($conn,$sql);
    if (!$rs)
    echo"Error in SQL";

	while(odbc_fetch_row($rs))
        {
	  $csname=odbc_result($rs,"csname");
	  echo "<option value=\"$csname\">$csname</option>";
	}
		
		echo "</select>";	
		echo "<input type=\"submit\">";
		echo "</form>";
		echo "</td></tr>";
		echo "</table>";
		echo "</form>";
		echo "</tr></table>"; 
		echo "<br />";
  	odbc_close($conn);
echo "<table border ='0' width='100%'><tr><td align='center'><strong><font size='4'><font color='#008301'>Quote Information</font></strong></td></tr></table>";
echo "<table border ='1' width='100%'>";	
echo "<tr><td>Quote Date</td>";
echo "<td>";
echo date("F d, Y"); 
echo "</td></tr>";
echo "<tr><td>Inquiry Date</td>";
echo"<td><input type='text' size='20'></td></tr>";
echo "<tr><td>Sales Person $pcscode</td>";
echo "<td>";
?><?php include 'addons/loginArray.php';?><?php
echo "</td></tr>";
echo "</table>"; 
echo "<br />";
		
echo "<table border ='0' width='100%'><tr><td align='center'><strong><font size='4'><font color='#008301'>Customer Information</font></strong></td></tr></table>";
echo "<table border ='1' width='100%'>";
include 'addons/connection.php';
    $sql="select cscode, csphone, csaddr1, csaddr2, cscity, csst, cszip from customer
    where sacode = $sacode				
    order by csname";				

      $rs=odbc_exec($conn,$sql);
      if (!$rs)
        echo"Error in SQL";

      while(odbc_fetch_row($rs))
       {
			
	$cscode=odbc_result($rs,"cscode");
	$csphone=odbc_result($rs,"csphone");
	$csaddr1=odbc_result($rs,"csaddr1");
	$csaddr2=odbc_result($rs,"csaddr2");
	$cscity=odbc_result($rs,"cscity");
	$csst=odbc_result($rs,"csst");
	$cszip=odbc_result($rs,"cszip");	
      }
  					
  					
        echo "<tr><td width=\"40%\">Customer Number</td>";
  	echo"<td>$cscode</td></tr>";
  	echo "<tr><td>Company Name</td>";
  	echo "<td>$pcsname</td></tr>";
  	echo "<tr><td>Customer Phone</td>";
  	echo "<td>$csphone</td></tr>";
  	echo "<tr><td>Address 1</td>";
  	echo "<td>$csaddr1</td></tr>";
  	echo "<tr><td>Address 2</td>";
  	echo "<td>$csaddr2</td></tr>";
  	echo "<tr><td>Address 3</td>";
  	echo "<td>$cscity  $csst $cszip</td></tr>";
  odbc_close($conn);
echo "</table>"; 

If, however, I do somethiong like this:

 $sql="select cscode, csphone, csaddr1, csaddr2, cscity, csst, cszip from customer
    where sacode = $sacode				
    and
    csname = $pcsname";

I would expect this to give me the required information by referencing the company name already set above.

 

Instead, I get an SQL ERROR

 

Again, if I remove the line: csname = $pcsname I get the last record in the databse associated with $sacode.

any thoughts?

 

Thank you in advance

Edited June 7, 2013 by FrankieGee

[DavidAM]

 

    $sql="select cscode, csphone, csaddr1, csaddr2, cscity, csst, cszip from customer
    where sacode = $sacode				
    order by csname";				

      $rs=odbc_exec($conn,$sql);
      if (!$rs)
        echo"Error in SQL";

      while(odbc_fetch_row($rs))
       {
			
	$cscode=odbc_result($rs,"cscode");
...
	$cszip=odbc_result($rs,"cszip");	
      }
  					
  					
        echo "<tr><td width=\"40%\">Customer Number</td>";
  	echo"<td>$cscode</td></tr>";
...
  	echo "<td>$cscity  $csst $cszip</td></tr>";
  odbc_close($conn);
echo "</table>"; 

 

INSIDE the loop are you assigning the customer data to variables for each row. OUTSIDE the loop, you are echoing the customer data. Since each pass through the loop assigns the (current row) customer data to the variables, it is OVERWRITING the data from the previous row. The ECHO statements, after you close the loop, only has access to the variables set in the last pass of the LOOP (the last record). Move the ECHO statements inside the loop.

 

Also, your test to see if the query failed will echo "Error in SQL", but will then continue with the next statement trying to process the results, which will produce an error. It should probably be something like:

 

      $rs=odbc_exec($conn,$sql);
      if (!$rs) { # MAD query failed
        echo"Error in SQL";
} ELSE { # MAD query executed OK
      while(odbc_fetch_row($rs))
       {
			
	$cscode=odbc_result($rs,"cscode");
...
	$cszip=odbc_result($rs,"cszip");	

# MAD moved ECHO's inside the loop

        echo "<tr><td width=\"40%\">Customer Number</td>";
  	echo"<td>$cscode</td></tr>";
...
  	echo "<td>$cscity  $csst $cszip</td></tr>";
     } #MAD END WHILE
  odbc_close($conn);
} # MAD END IF {} ELSE {
echo "</table>"; 

[mac_gyver]

you are getting only the last record because your code that uses the fetched data is outside of an after the end of your while(){} loop. you would need to put the code that uses the fetched data inside of your while(){} loop.

 

if $pcsname is a string, you would need to enclose it in what ever string delimiters your database query needs. for mysql, this would be single-quotes around the string value, for mssql it may be the same or you might need to use escaped double-quotes around the string value.

Edited June 7, 2013 by mac_gyver

[FrankieGee]

@DavidAM and @Mac_Gyver,

 

Thank you very much. Both of your posts helped me to solve this issue.

Now... on to mode coding...

 

Thanks again