Getting info from SQL databse

tapupartforpres · December 5, 2008

Hello. I have seen quite a few answers to this question, but wanted to know your opinion on the best method.

I have a registration form and an mysql database with our subscribers in it. I need to check if their last name is in the subscription mysql database for them to register free for the event? Any suggestions. I am looking at the mysql functions on php.net and wanted to know the best method. Thanks

Mchl · December 5, 2008

I'm not sure what exactly are you asking about.

If you want to know, what is the recommended way of connecting to mysql - its mysqli. On the other hand: mysql is the most popular.

gevans · December 5, 2008

Do you mean you're not going to let people with the same surname register to your site?

premiso · December 5, 2008

Do you mean you're not going to let people with the same surname register to your site?

I think he means, if their last name is in the DB, they get to register for free for that event and not have to pay....but I could be wrong.

tapupartforpres · December 5, 2008

Sorry. Yes Mchi is right. If their last name is in the database then they will be able to register for the event for free.

tapupartforpres · December 5, 2008

This is what I got now. I guess I don't know how to say if their name matches the field "last" in the mysql database then print the error code?

<?php


// Set the database access information as constants.
define('DB_USER', '****');
define ('DB_PASSWORD', '****');
define ('DB_HOST', '****');
define ('DB_NAME', '****');

// Make the connnection and then select the database.
$dbc = mysql_connect (DB_HOST, DB_USER, DB_PASSWORD); //or die ("Could not connect to DB");
mysql_select_db (DB_NAME); //or die ("Could not find DB");


// Select all rows with last name
$query = "SELECT * FROM **** WHERE last='$last'";
$result = mysql_query($query) 
    or die("Invalid query: " . mysql_error());
    
    //check results
    while($row = mysql_fetch_array($result)){
    	$match = $match+1;
    	}

	if($error >= "1"){
		echo"<table width='700'><tr><td><br><br><br>The information you have provided does not match our database of CBC Magazine paid subscribers.<br><br>If you paid to receive the CBC® Magazine regularly and should be in our database, please call Holly at 216.831.9557 or email her at ****, and she will process your $35 registration manually.<br><br>If you did not pay to receive the magazine, you can click back to continue your registration for Amplify at the $45 non-subscriber rate.<br><br>If your event registration is not processed and confirmed within 48 hours, A CDG representative will contact you. Thank you.</td></tr></table>";
		}

mysql_close($dbc);




$con = mysql_connect("****","****","****"); //Replace with your actual MySQL DB Username and Password
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("****", $con); //Replace with your MySQL DB Name
$first=mysql_real_escape_string($_POST['first']); //This value has to be the same as in the HTML form file
$last=mysql_real_escape_string($_POST['last']); //This value has to be the same as in the HTML form file
$company=mysql_real_escape_string($_POST['company']); 
$address=mysql_real_escape_string($_POST['address']); 
$city=mysql_real_escape_string($_POST['city']); 
$state=mysql_real_escape_string($_POST['state']); 
$zip=mysql_real_escape_string($_POST['zip']); 
$email=mysql_real_escape_string($_POST['email']); 
$phone=mysql_real_escape_string($_POST['phone']);  

$sql="INSERT INTO CCA_Registration_a (first,last,company,address,city,state,zip,email,phone) VALUES ('$first','$last','$company','$address','$city','$state','$zip','$email','$phone')"; /*form_data is the name of the MySQL table where the form data will be saved.
name and email are the respective table fields*/
if (!mysql_query($sql,$con)) {
die('Error: ' . mysql_error());
}

mysql_close($con);



// Configuration Settings  Holly gets email
$SendFrom =    "Paid Subscriber and CCA Award Registration <****@****.com>";
$SendTo =      "****@****.com";
$SubjectLine = "Paid Subscriber and CCA Award Registration";
$Divider =     "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~";

// Build Message Body from Web Form Input
$MsgBody = @gethostbyaddr($_SERVER["REMOTE_ADDR"]) . "\n$Divider\n";
foreach ($_POST as $Field=>$Value)
   $MsgBody .= "$Field: $Value\n";
$MsgBody .= "$Divider\n" . $_SERVER["HTTP_USER_AGENT"] . "\n";
$MsgBody = htmlspecialchars($MsgBody);  //make content safe

// Send E-Mail and Direct Browser to Confirmation Page
mail($SendTo, $SubjectLine, $MsgBody, "From: $SendFrom");
header("Location: $ThanksURL");

$msg_cbc = "Thank you for your registration to the Connectors' Choice Awards";


{

echo"<table width='535'><tr><td><b>Registration Information:</b></td><td></td></tr>";
echo"<tr><td bgcolor='#EEEEEE'>First Name: </td><td bgcolor='#EEEEEE'>".$first."</td></tr>";
echo"<tr><td>Last Name: </td><td>".$last."</td></tr>";
echo"<tr><td bgcolor='#EEEEEE'>Email: </td><td bgcolor='#EEEEEE'>".$email."</td></tr>";
echo"<tr><td>Company: </td><td>".$company."</td></tr>";
echo"<tr><td bgcolor='#EEEEEE'>Phone: </td><td bgcolor='#EEEEEE'>".$phone."</td></tr>";
echo"<tr><td>Address: </td><td>".$address."</td></tr>";
echo"<tr><td bgcolor='#EEEEEE'>City: </td><td bgcolor='#EEEEEE'>".$city."</td></tr>";
echo"<tr><td>State: </td><td>".$state."</td></tr>";
echo"<tr><td bgcolor='#EEEEEE'>Zip: </td><td bgcolor='#EEEEEE'>".$zip."</td></tr>";

echo"<table width='535'><tr><td> </td><td></td></tr><br/><br/>";

}

//customer gets email
mail($_POST['email'],"Connectors' Choice Awards Registration",$msg_cbc,"FROM: ****@****"); //Send email to them

?>

tapupartforpres · December 5, 2008

Again another question that goes to the wayside. Sorry to bother the "geniuses" here.

gevans · December 5, 2008

I jsut want to udnerstand before I do anything, are you switching databases half way through? i.e every user has there own database?

tapupartforpres · December 5, 2008

Sorry gevans. A little frusturated with this forum, my donations don't see seem to go far here. If I am not a novice, then my questions go unanswered. I thought that was the point of this forum.

Anyways, yes I am switching the database. The first call to the database is to check if they are a subscriber (their is a table that has 715 subscribers all with unique last names) and the second one is the populate the other database with their registration. Thank you.

gevans · December 5, 2008

OK, remember the forum isn't a sure thing, you can't get annoyed at people for not posting straight away. Also I'm not saying you haven't, but you usually get tagged if you've donated!!

Give me a few mins I'll have a look through your code..

revraz · December 5, 2008

We don't get paid to answer people's questions. Has nothing to do if you are a "novice" or not. Everyone comes here on their own time.

Sorry gevans. A little frusturated with this forum, my donations don't see seem to go far here. If I am not a novice, then my questions go unanswered. I thought that was the point of this forum.

premiso · December 5, 2008

Sorry gevans. A little frusturated with this forum, my donations don't see seem to go far here. If I am not a novice, then my questions go unanswered. I thought that was the point of this forum.

Anyways, yes I am switching the database. The first call to the database is to check if they are a subscriber (their is a table that has 715 subscribers all with unique last names) and the second one is the populate the other database with their registration. Thank you.

Acting like a 2 yearold and throwing a tantrum will not help your cause. I challenge you to go somewhere else and find even nearly the same support as you find here.

    while($row = mysql_fetch_array($result)){
       $match = $match+1;
       }
      
      if($error >= "1"){
         echo"<table width='700'><tr><td><br><br><br>The information you have provided does not match our database of CBC Magazine paid subscribers.<br><br>If you paid to receive the CBC® Magazine regularly and should be in our database, please call Holly at 216.831.9557 or email her at ****, and she will process your $35 registration manually.<br><br>If you did not pay to receive the magazine, you can click back to continue your registration for Amplify at the $45 non-subscriber rate.<br><br>If your event registration is not processed and confirmed within 48 hours, A CDG representative will contact you. Thank you.</td></tr></table>";
         }

Why even bother doing a while? Why not use mysql_num_rows

    $rows = mysql_num_rows($result);
      
      if($rows > 0){
         echo"<table width='700'><tr><td><br><br><br>The information you have provided does not match our database of CBC Magazine paid subscribers.<br><br>If you paid to receive the CBC® Magazine regularly and should be in our database, please call Holly at 216.831.9557 or email her at ****, and she will process your $35 registration manually.<br><br>If you did not pay to receive the magazine, you can click back to continue your registration for Amplify at the $45 non-subscriber rate.<br><br>If your event registration is not processed and confirmed within 48 hours, A CDG representative will contact you. Thank you.</td></tr></table>";
         }

And what you expect people to be waiting at your becking needs, it was not even an hour since you posted the problem then you threw a "tantrum" and started bashing the forums.

gevans · December 5, 2008

<?php
/*
!!!!!!! var $lost is not set !!!!!!!!!!!!!
*/


// Set the database access information as constants.
define('DB_USER', '****');
define ('DB_PASSWORD', '****');
define ('DB_HOST', '****');
define ('DB_NAME', '****');

// Make the connnection and then select the database.
$dbc = mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) or die ("Could not connect to DB");
mysql_select_db (DB_NAME) or die ("Could not find DB");


// Select all rows with last name
$query = "SELECT * FROM **** WHERE last='$last'";
$result = mysql_query($query) 
    or die("Invalid query: " . mysql_error());
    
//check results
if($row = mysql_fetch_array($result)){
mysql_close($dbc);
$con = mysql_connect("****","****","****"); //Replace with your actual MySQL DB Username and Password
if (!$con) die('Could not connect: ' . mysql_error());
mysql_select_db("****", $con); //Replace with your MySQL DB Name

$first=mysql_real_escape_string($_POST['first']); //This value has to be the same as in the HTML form file
$last=mysql_real_escape_string($_POST['last']); //This value has to be the same as in the HTML form file
$company=mysql_real_escape_string($_POST['company']); 
$address=mysql_real_escape_string($_POST['address']); 
$city=mysql_real_escape_string($_POST['city']); 
$state=mysql_real_escape_string($_POST['state']); 
$zip=mysql_real_escape_string($_POST['zip']); 
$email=mysql_real_escape_string($_POST['email']); 
$phone=mysql_real_escape_string($_POST['phone']);     

$sql="INSERT INTO CCA_Registration_a (first,last,company,address,city,state,zip,email,phone) VALUES ('$first','$last','$company','$address','$city','$state','$zip','$email','$phone')"; /*form_data is the name of the MySQL table where the form data will be saved.
name and email are the respective table fields*/
if (!mysql_query($sql,$con)) die('Error: ' . mysql_error());

mysql_close($con);

// Configuration Settings  Holly gets email
$SendFrom =    "Paid Subscriber and CCA Award Registration <****@****.com>";
$SendTo =      "****@****.com";
$SubjectLine = "Paid Subscriber and CCA Award Registration";
$Divider =     "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~";

// Build Message Body from Web Form Input
$MsgBody = @gethostbyaddr($_SERVER["REMOTE_ADDR"]) . "\n$Divider\n";
foreach ($_POST as $Field=>$Value)
   $MsgBody .= "$Field: $Value\n";
$MsgBody .= "$Divider\n" . $_SERVER["HTTP_USER_AGENT"] . "\n";
$MsgBody = htmlspecialchars($MsgBody);  //make content safe

// Send E-Mail and Direct Browser to Confirmation Page
mail($SendTo, $SubjectLine, $MsgBody, "From: $SendFrom");
header("Location: $ThanksURL");
/*
$msg_cbc = "Thank you for your registration to the Connectors' Choice Awards";


{

echo"<table width='535'><tr><td><b>Registration Information:</b></td><td></td></tr>";
echo"<tr><td bgcolor='#EEEEEE'>First Name: </td><td bgcolor='#EEEEEE'>".$first."</td></tr>";
echo"<tr><td>Last Name: </td><td>".$last."</td></tr>";
echo"<tr><td bgcolor='#EEEEEE'>Email: </td><td bgcolor='#EEEEEE'>".$email."</td></tr>";
echo"<tr><td>Company: </td><td>".$company."</td></tr>";
echo"<tr><td bgcolor='#EEEEEE'>Phone: </td><td bgcolor='#EEEEEE'>".$phone."</td></tr>";
echo"<tr><td>Address: </td><td>".$address."</td></tr>";
echo"<tr><td bgcolor='#EEEEEE'>City: </td><td bgcolor='#EEEEEE'>".$city."</td></tr>";
echo"<tr><td>State: </td><td>".$state."</td></tr>";
echo"<tr><td bgcolor='#EEEEEE'>Zip: </td><td bgcolor='#EEEEEE'>".$zip."</td></tr>";

echo"<table width='535'><tr><td> </td><td></td></tr><br/><br/>";

}

//customer gets email
mail($_POST['email'],"Connectors' Choice Awards Registration",$msg_cbc,"FROM: ****@****"); //Send email to them

*/
} else {
echo "<table width='700'><tr><td><br><br><br>The information you have provided does not match our database of CBC Magazine paid subscribers.<br><br>If you paid to receive the CBC® Magazine regularly and should be in our database, please call Holly at 216.831.9557 or email her at ****, and she will process your $35 registration manually.<br><br>If you did not pay to receive the magazine, you can click back to continue your registration for Amplify at the $45 non-subscriber rate.<br><br>If your event registration is not processed and confirmed within 48 hours, A CDG representative will contact you. Thank you.</td></tr></table>";
}

?>

I've had a re-write of your code.

Notes:

- $last isn't set at the start of your code;

- there was a lot of code after a redirect header(Location etc..) I commented this out

I've re-written it to work in a way that made sense from your posts.

If it's doing something different to what you expected let me know!

tapupartforpres · December 5, 2008

Sorry people, I didn't mean that to come off like that. I was frusturated with previous posts like "pick up a book". Not this post, I should of been clearer on that, my bad. Didn't mean it for the people who really help like you guys. I know you do this on your free time and really do appreciate it.

Any help would be appreciated.

tapupartforpres · December 5, 2008

Hey gevans. I think I'm mixed up. I don't think it's correct. I see what you did and is starting to sink in.

1. I would need it to check the subscriber data and if they are not in the data it would show "you are not a member.."

2. If their last name does match the data it will display in a table (you commented this out) to make sure it is correct.

3. After they read over their information, they hit submit, then it sends it to the other database.

Thanks

gevans · December 5, 2008

OK, let me re-write what you just said to make sure I understand

1) User puts in details

1.1) If last name is not in database show "you are not a member" - end

1.2) If it is in database show their details - go to 2)

2) get them to read over their details and submit

3) once submitted the details get added to a new database

tapupartforpres · December 8, 2008

Hey gevens. Yes you are correct. Perfectly stated, sorry if I was a bit vague.

gevans · December 8, 2008

ok, first problem.

In the first query you're doing this;

$query = "SELECT * FROM **** WHERE last='$last'";

The var $last has not been set in the script. Is this coming from a form and what is it called in the form??

<input type-"text" name="last" /> - is it like that?

tapupartforpres · December 8, 2008

Hey G. Here is what I have in the index file (the form)

If you go here http://www.cbcmagazine.com/subscriber_paid/ and fill out some bunk information you will see what you get.

gevans · December 8, 2008

<?php
// Set the database access information as constants.
define('DB_USER', '****'); #add details
define ('DB_PASSWORD', '****'); #add details
define ('DB_HOST', '****'); #add details
define ('DB_NAME', '****'); #add details

// Make the connnection and then select the database.
$dbc = mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) or die ("Could not connect to DB");
mysql_select_db (DB_NAME) or die ("Could not find DB");

$last = (isset($_REQUEST['last'])) ? mysql_real_escape_string($_REQUEST['last']) : NULL;

// Select all rows with last name
$query = "SELECT * FROM **** WHERE last='$last'"; #add details
$result = mysql_query($query) 
    or die("Invalid query: " . mysql_error());
    
//check results
if($row = mysql_fetch_array($result)){
   mysql_close($dbc);
   $con = mysql_connect("****","****","****"); #add details
   if (!$con) die('Could not connect: ' . mysql_error());
   mysql_select_db("****", $con); #add details

   $first=mysql_real_escape_string($_POST['first']); //This value has to be the same as in the HTML form file
   $last=mysql_real_escape_string($_POST['last']); //This value has to be the same as in the HTML form file
   $company=mysql_real_escape_string($_POST['company']); 
   $address=mysql_real_escape_string($_POST['address']); 
   $city=mysql_real_escape_string($_POST['city']); 
   $state=mysql_real_escape_string($_POST['state']); 
   $zip=mysql_real_escape_string($_POST['zip']); 
   $email=mysql_real_escape_string($_POST['email']); 
   $phone=mysql_real_escape_string($_POST['phone']);     

   $sql="INSERT INTO CCA_Registration_a (first,last,company,address,city,state,zip,email,phone) VALUES ('$first','$last','$company','$address','$city','$state','$zip','$email','$phone')"; /*form_data is the name of the MySQL table where the form data will be saved.
   name and email are the respective table fields*/
   if (!mysql_query($sql,$con)) die('Error: ' . mysql_error());

   mysql_close($con);

   // Configuration Settings  Holly gets email
   $SendFrom =    "Paid Subscriber and CCA Award Registration <****@****.com>";
   $SendTo =      "****@****.com";
   $SubjectLine = "Paid Subscriber and CCA Award Registration";
   $Divider =     "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~";

   // Build Message Body from Web Form Input
   $MsgBody = @gethostbyaddr($_SERVER["REMOTE_ADDR"]) . "\n$Divider\n";
   foreach ($_POST as $Field=>$Value)
      $MsgBody .= "$Field: $Value\n";
   $MsgBody .= "$Divider\n" . $_SERVER["HTTP_USER_AGENT"] . "\n";
   $MsgBody = htmlspecialchars($MsgBody);  //make content safe

   // Send E-Mail and Direct Browser to Confirmation Page
   mail($SendTo, $SubjectLine, $MsgBody, "From: $SendFrom");
   header("Location: $ThanksURL");

   $msg_cbc = "Thank you for your registration to the Connectors' Choice Awards";


   {

   echo"<table width='535'><tr><td><b>Registration Information:</b></td><td></td></tr>";
   echo"<tr><td bgcolor='#EEEEEE'>First Name: </td><td bgcolor='#EEEEEE'>".$first."</td></tr>";
   echo"<tr><td>Last Name: </td><td>".$last."</td></tr>";
   echo"<tr><td bgcolor='#EEEEEE'>Email: </td><td bgcolor='#EEEEEE'>".$email."</td></tr>";
   echo"<tr><td>Company: </td><td>".$company."</td></tr>";
   echo"<tr><td bgcolor='#EEEEEE'>Phone: </td><td bgcolor='#EEEEEE'>".$phone."</td></tr>";
   echo"<tr><td>Address: </td><td>".$address."</td></tr>";
   echo"<tr><td bgcolor='#EEEEEE'>City: </td><td bgcolor='#EEEEEE'>".$city."</td></tr>";
   echo"<tr><td>State: </td><td>".$state."</td></tr>";
   echo"<tr><td bgcolor='#EEEEEE'>Zip: </td><td bgcolor='#EEEEEE'>".$zip."</td></tr>";

   echo"<table width='535'><tr><td> </td><td></td></tr><br/><br/>";

   }

   //customer gets email
   mail($_POST['email'],"Connectors' Choice Awards Registration",$msg_cbc,"FROM: ****@****"); //Send email to them
   

} else {
   echo "<table width='700'><tr><td><br><br><br>The information you have provided does not match our database of CBC Magazine paid subscribers.<br><br>If you paid to receive the CBC® Magazine regularly and should be in our database, please call Holly at 216.831.9557 or email her at ****, and she will process your $35 registration manually.<br><br>If you did not pay to receive the magazine, you can click back to continue your registration for Amplify at the $45 non-subscriber rate.<br><br>If your event registration is not processed and confirmed within 48 hours, A CDG representative will contact you. Thank you.</td></tr></table>";
}
?>

tapupartforpres · December 8, 2008

Gevans, it worked like a charm. I appreciate it. I will be making a donation next week in your honor. Thanks!

gevans · December 8, 2008

Glad to hear it!! got there eventually

Sign In

Getting info from SQL databse

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information