[RESOLVED] Validation from two tables

Cappymoo

I am unable to figure out the problem with this script. For some reason it's only seeing the last entry from the users table and I need for it to see all entries to compare it with entries from the youth table. I have removed some code as it would be way to long to post all of it.

It's supposed to check the lastname and firstname from each table and if there is a match, then proceed to enter the data into the database, otherwise, post an error.
It will work if I use the last name in the users table, but with none other.

<?php
 session_start();session_destroy();
 session_start();
if($_POST["lastname1"] )
{
	include('connect-db.php');
	$result = mysql_query("SELECT lastname1, firstname1 FROM users") 
                or die(mysql_error());  

	while($row = mysql_fetch_array( $result )) {	
	$lastnameREG = 	$row[lastname1];
	$firstnameREG = $row[firstname1];	
	// print "$row[lastname1] - $row[firstname1] ";
	}
	if($_POST["lastname1"] == $lastnameREG && $_POST["firstname1"] == $firstnameREG )
	{
	$servername="xxxxx";
    $username="xxxxx";
    $password="xxxxx";
    $conn=  mysql_connect($servername,$username,$password)or die(mysql_error());
    mysql_select_db("test",$conn);
    $sql="insert into youth (registerBiathlon1,lastname1,firstname1,email,address1,city1,zipcode1,age1,birthdate1,gender1,school1,height1,weight1,beginner1,intermediate1,experienced1,medicalconditions1,medications1,registerBiathlon2,lastname2,firstname2,address2,city2,zipcode2,age2,birthdate2,gender2,school2,height2,weight2,beginner2,intermediate2,experienced2,medicalconditions2,medications2,registerBiathlon3, ..............etc.....)values
	('$_POST[registerBiathlon1]','$_POST[lastname1]','$_POST[firstname1]','$_POST[email]','$_POST[address1]','$_POST[city1]','$_POST[zipcode1]','$_POST[age1]','$_POST[birthdate1]','$_POST[gender1]','$_POST[school1]','$_POST[height1]','$_POST[weight1]','$_POST[beginner1]','$_POST[intermediate1]','$_POST[experienced1]','$_POST[medicalconditions1]','$_POST[medications1]','$_POST[registerBiathlon2]','$_POST[lastname2]','$_POST[firstname2]','$_POST[address2]','$_POST[city2]','$_POST[zipcode2]','$_POST[age2]','$_POST[birthdate2]','$_POST[gender2]','$_POST[school2]','$_POST[height2]','$_POST[weight2]','$_POST[beginner2]','$_POST[intermediate2]','$_POST[experienced2]','$_POST[medicalconditions2]','$_POST[medications2]',.........etc......";
    $result=mysql_query($sql,$conn) or die(mysql_error());		
    print "<h1>You have added your Youth Membership sucessfully</h1>";
	print "<br />";

print "<table border=\"0\" cellpadding=\"5\" cellspacing=\"5\">
<tr>
<td colspan=\"2\"><strong>User #1 Information:</strong></td>
<td align=\"right\" style=\"color: red\">$_POST[registerBiathlon1]</td>
<td colspan=\"5\">Check if also Registering for Laser Biathlon</td>
</tr>
<tr>
<td>Last Name</td><td colspan=\"2\" style=\"color: red\">$_POST[lastname1] - $lastnameREG</td>
<td>First Name</td><td colspan=\"4\" style=\"color: red\">$_POST[firstname1]</td>
</tr>
<tr>
<td>Mailing Address:</td>
<td style=\"color: red\">$_POST[address1]</td>
<td>City</td>
<td style=\"color: red\">$_POST[city1]</td>

<td colspan=\"2\" align=\"right\">Zipcode</td>
<td colspan=\"2\" style=\"color: red\">$_POST[zipcode1]</td>

</tr>
<tr>
<td>Age:</td>
<td style=\"color: red\">$_POST[age1]</td>
<td>Birthdate</td>
<td style=\"color: red\">$_POST[birthdate1]</td>
<td>Gender</td>
<td style=\"color: red\">$_POST[gender1]</td>
<td>School</td>
<td style=\"color: red\">$_POST[school1]</td>


</tr>
<tr>


<td colspan=\"1\">Height</td>
<td colspan=\"2\" style=\"color: red\">$_POST[height1]</td>
<td colspan=\"1\" align=\"right\">Weight</td>
<td colspan=\"2\" style=\"color: red\">$_POST[weight1]</td>
<td colspan=\"2\">needed for ski rental or purchase</td>

</tr>
<tr>


<td colspan=\"1\">Level of Experience</td>
<td colspan=\"1\" style=\"color: red\" align=\"right\">$_POST[beginner1] </td><td>Beginner</td>
<td colspan=\"1\" style=\"color: red\" align=\"right\">$_POST[intermediate1] </td><td>Intermediate</td>
<td colspan=\"1\" style=\"color: red\" align=\"right\">$_POST[experienced1] </td><td>Experienced</td>

</tr>
<tr>

<td colspan=\"1\">Medical conditions*</td><td colspan=\"2\" style=\"color: red\">$_POST[medicalconditions1]</td>

<td colspan=\"1\" align=\"right\">Medications</td><td colspan=\"2\" style=\"color: red\">$_POST[medications1]</td>

</tr>
</table>
	";


}
else print "You do not have a regular membership. Please sign up here prior to registering for the Youth Program";
}
else print"invaild input data";


 $to = $_REQUEST['email1'] ; 
 $from = "xxxx@xxxxxx.com" ; 
 $name = $_REQUEST['lastname1'] ; 
 $headers = "From: $from"; 
 $subject = "Youth Membership Form Sumbission"; 

 $fields = array(); 
$fields{"registerBiathlon1"} = "Register for xxxxxxxx"; 
 $fields{"lastname1"} = "Last Name 1"; 
 $fields{"firstname1"} = "First Name 1"; 
  $fields{"email"} = "Email";
 $fields{"address1"} = "Address 1"; 
 $fields{"city1"} = "City 1"; 
 $fields{"zipcode1"} = "Zipcode 1";
 $fields{"age1"} = "Age 1"; 
 $fields{"birthdate1"} = "Birthdate 1"; 	
 $fields{"gender1"} = "Gender 1"; 
 $fields{"school1"} = "School 1"; 	
 $fields{"height1"} = "Height 1"; 
 $fields{"weight1"} = "Weight 1"; 	
 $fields{"beginner1"} = "Beginner 1"; 
 $fields{"intermediate1"} = "Intermediate 1"; 	
 $fields{"experienced1"} = "Experienced 1"; 
 $fields{"medicalconditions1"} = "Medical Conditions 1";
 $fields{"medications1"} = "Medications 1"; 

 $body = "We have received the following information:\n\n"; foreach($fields as $a => $b){ 	$body .= sprintf("%20s: %s\n",$b,$_REQUEST[$a]); } 

 $headers2 = "From: xxxxx@xxxxxx.com"; 
 $subject2 = "Thank you for contacting us"; 
 $autoreply = "Thank you for contacting us. Somebody will get back to you as soon as possible, usualy within 48 hours. If you have any more questions, please consult our website at www.xxxxxxx.org";

 if($from == '') {print "You have not entered an email, please go back and try again";} 
 else { 
 if($name == '') {print "You have not entered a name, please go back and try again";} 
 else { 
 $send = mail($from, $subject, $body); 
 $send2 = mail($to, $subject2, $autoreply, $body); 
 if($send) 
 {print "<div align=\"center\"><FORM><INPUT TYPE=\"button\" onClick=\"window.print()\" value=\"Print Registration Form\"></FORM></div>";} 

 else 
 {print "We encountered an error sending your mail, please notify xxx@xxxx.org"; } 
 }
}
?>

bradgrafelman

The problem is with your while() loop that retrieves the results:

	while($row = mysql_fetch_array( $result )) {	
	$lastnameREG = 	$row[lastname1];
	$firstnameREG = $row[firstname1];	
	// print "$row[lastname1] - $row[firstname1] ";
	}

You're simply overwriting the value in those two variables over and over until you get to the last row in the result set (at which point the loop stops and you then use that last pair of values).

However, why are you trying to do this the most inefficient way possible? 😉 If you have a specific row you're looking for, why not add the criteria into a WHERE condition in the SQL query rather than looping through every single row in the DB?

Cappymoo

I tried doing a where clause

$result = mysql_query("SELECT lastname1, firstname1 FROM users WHERE lastname1="$_POST["lastname1"]" AND firstname1="$_POST["firstname1"]" ") 
                or die(mysql_error());

But I'm getting a syntax error and I'm not sure where or what is causing it.

Derokorian

Watch what happens when its highlighted as PHP code:

$result = mysql_query("SELECT lastname1, firstname1 FROM users WHERE lastname1="$_POST["lastname1"]" AND firstname1="$_POST["firstname1"]" ") 
                or die(mysql_error());

See the problem now? Try this instead.

$result = mysql_query("SELECT lastname1, firstname1 FROM users WHERE lastname1='$_POST[lastname1]' AND firstname1='$_POST[firstname1]' ") 
                or die(mysql_error());

Cappymoo

Your a genius!!! Thank you so much. My code is now working as intended goes to make a backup

bradgrafelman

Before you make a backup, you might want to...

... Note that user-supplied data should never be placed directly into a SQL query string, else your code will be vulnerable to SQL injection attacks and/or just plain SQL errors. Instead, you must first sanitize it with a function such as [man]mysql_real_escape_string/man (for string data). See the following man page for more info: [man]security.database.sql-injection[/man].

Cappymoo

Are you referring to something like this?

 $lastname1 = mysql_real_escape_string(htmlspecialchars($_POST['lastname1']));
 $firstname1 = mysql_real_escape_string(htmlspecialchars($_POST['firstname1']));

I have that in my edit file, but not my add. I'm not sure where I would even place it. I'm still new to PHP and have much to learn.

bradgrafelman

Cappymoo;10990610 wrote:

Are you referring to something like this?

 $lastname1 = mysql_real_escape_string(htmlspecialchars($_POST['lastname1']));
 $firstname1 = mysql_real_escape_string(htmlspecialchars($_POST['firstname1']));

Something like that, sure... although I wouldn't use [man]htmlspecialchars/man when storing data in the DB but rather only when retrieving and displaying it. (In other words, store the data in its most raw form in the DB, saving all of the conversions needed to display it until you actually do so.)

EDIT: Forgot to mention... I personally wouldn't bother creating superfluous variables either and instead just do something like:

$sql = sprintf(
               "SELECT col1 FROM foo WHERE col2=%s AND col3=%d",
               "'" . mysql_real_escape_string($_POST['stringVal']) . "'",
               $_POST['numericVal']
              );

with [man]sprintf/man.

Cappymoo;10990610 wrote:
I have that in my edit file, but not my add.

Why not?

Cappymoo;10990610 wrote:
I'm not sure where I would even place it.

You're just defining variables, so you can do that wherever you'd like... so long as it comes before you actually try use those variables, of course.

Cappymoo

Again, like I said I'm new to php and what you posted has eluded me.

If I'm reading this correctly, I should do the following?

include('connect-db.php');

$sql = sprintf(
               "SELECT lastname1, firstname1 FROM users WHERE lastname1=%s AND firstname1=%d",
               "'" . mysql_real_escape_string($_POST['lastname1']),
               $_POST['firstname1']
              );

Is that correct?

bradgrafelman

Well for one, I forgot to add an ending single quote after the string parameter (see my corrected post above).

Otherwise, sure, as long as $_POST['firstname1'] is a numeric value... "a (signed) decimal number" to be precise as stated in the manual for [man]sprintf/man (and hinted at in my example via the sample field name "numericVal").

Cappymoo

Ok, but we are dealing with a firstname and a last name, which obviously are not numeric values. So how does this code pertain to that?

After reading the sprintf page that you linked, my take on this is that I would put

$sql = sprintf(
               "SELECT lastname1, firstname1 FROM users WHERE lastname1=%s AND firstname1=%s",
               "'" . mysql_real_escape_string($_POST['lastname1'] . "'",
               "'" . mysql_real_escape_string($_POST['firstname'] . "'"
              );

Weedpacket

$sql = sprintf("SELECT lastname1, firstname1 from users where lastname='%s' AND firstname1='%s'",
   mysql_real_escape_string($_POST['lastname1'], mysql_real_escape_string($_POST['firstname']));

(Incidentally, this forum has [noparse]

...

[/noparse] tags designed specifically for marking up PHP code).

One thing to note is that this is approaching the idea of "parameterised queries", where you declare a query template with "slots" that get filled in when the query is executed. Something like this:

$query = $dbconn->prepare("SELECT lastname1, firstname1 from users where lastname=? AND firstname1=?");

$query->exec(array($_POST['lastname1'], $_POST['firstname']));
$result = $query->fetchAll();

Note that in this implementation all the business of escaping whatever needs to be escaped and quoting whatever needs to be quoted is handled internally.