Jump to content

PHP 5/MySQL Script Help

jay7981

By jay7981
in PHP Coding Help

 Share

Recommended Posts

jay7981 Member

jay7981

Posted
Posted (edited)

Hey Guys,

I need a little help. I have a registration form that when submitted places all information into a DB and then takes the submitter to a payment pageto pay a registration fee This works great. I also have paypal ipn setup to recieve the details of the payments and this also works great. And finally i have a page that outputs the information of the registration table with a field "Paid" that is either going to be Yes or No and this is where i am having issues. What i am trying to accomplish is have a script that will check both tables (reg/pament) and combine the information into variables so that i can build if statements to display weather or not the user has paid the reg fee. an example is below as well as the current code that i have inplace and working.

 

I was thinking of using a rand() to generate a random string of about 5 digits and during the registration process emailing it to the user and when they make thier payment require the random string so that there is a linking item that will always be there. Mainly to avoid the issue of someon not having the same email address for emailing and a different one for paypal. other than email there isn't much to link with. What do you think?

 

Reg Table

CREATE TABLE `XXX` (
`id` int(3) NOT NULL auto_increment,
`name` varchar(50) NOT NULL,
`department` varchar(100) NOT NULL,
`email` varchar(75) NOT NULL,
`member` varchar( NOT NULL,
`member_id` varchar(30) NOT NULL,
`chapter` varchar(255) NOT NULL,
`shirt` varchar(18) NOT NULL,
`class_1` varchar(10) NOT NULL,
`class_2` varchar(10) NOT NULL,
`attending` varchar(255) NOT NULL,
`paid` varchar(4) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;

 

 

Payment Table

CREATE TABLE `ZZZZ` (
`id` int(10) unsigned NOT NULL auto_increment,
`raw_log_id` int(10) default NULL,
`receiver_email` varchar(127) collate utf8_bin default NULL,
`payment_status` varchar(25) collate utf8_bin default NULL,
`pending_reason` varchar(25) collate utf8_bin default NULL,
`payment_date` varchar(100) collate utf8_bin default NULL,
`option_name1` varchar(64) collate utf8_bin default NULL,
`option_selection1` varchar(200) collate utf8_bin default NULL,
`option_name2` varchar(64) collate utf8_bin default NULL,
`option_selection2` varchar(200) collate utf8_bin default NULL,
`option_name3` varchar(64) collate utf8_bin default NULL,
`option_selection3` varchar(200) collate utf8_bin default NULL,
`option_name4` varchar(64) collate utf8_bin default NULL,
`option_selection4` varchar(200) collate utf8_bin default NULL,
`option_name5` varchar(64) collate utf8_bin default NULL,
`option_selection5` varchar(200) collate utf8_bin default NULL,
`option_name6` varchar(64) collate utf8_bin default NULL,
`option_selection6` varchar(200) collate utf8_bin default NULL,
`option_name7` varchar(64) collate utf8_bin default NULL,
`option_selection7` varchar(200) collate utf8_bin default NULL,
`option_name8` varchar(64) collate utf8_bin default NULL,
`option_selection8` varchar(200) collate utf8_bin default NULL,
`option_name9` varchar(64) collate utf8_bin default NULL,
`option_selection9` varchar(200) collate utf8_bin default NULL,
`memo` varchar(255) collate utf8_bin default NULL,
`shipping_method` varchar(100) collate utf8_bin default NULL,
`btn_id` varchar(50) collate utf8_bin default NULL,
`mc_gross` double default NULL,
`mc_fee` double default NULL,
`mc_shipping` double default NULL,
`mc_handling` double default NULL,
`shipping_discount` double default NULL,
`insurance_amount` double default NULL,
`handling_amount` double default NULL,
`shipping` double default NULL,
`tax` double default NULL,
`mc_currency` varchar(10) collate utf8_bin default NULL,
`txn_id` varchar(25) collate utf8_bin default NULL,
`txn_type` varchar(25) collate utf8_bin default NULL,
`first_name` varchar(75) collate utf8_bin default NULL,
`last_name` varchar(75) collate utf8_bin default NULL,
`address_street` varchar(200) collate utf8_bin default NULL,
`address_city` varchar(50) collate utf8_bin default NULL,
`address_state` varchar(40) collate utf8_bin default NULL,
`address_zip` varchar(20) collate utf8_bin default NULL,
`address_country` varchar(64) collate utf8_bin default NULL,
`address_status` varchar(25) collate utf8_bin default NULL,
`payer_email` varchar(127) collate utf8_bin default NULL,
`payer_status` varchar(25) collate utf8_bin default NULL,
`payment_type` varchar(25) collate utf8_bin default NULL,
`notify_version` varchar(50) collate utf8_bin default NULL,
`verify_sign` varchar(255) collate utf8_bin default NULL,
`address_name` varchar(130) collate utf8_bin default NULL,
`transaction_subject` varchar(150) collate utf8_bin default NULL,
`protection_eligibility` varchar(50) collate utf8_bin default NULL,
`ipn_status` varchar(25) collate utf8_bin default NULL,
`subscr_id` varchar(25) collate utf8_bin default NULL,
`custom` varchar(255) collate utf8_bin default NULL,
`reason_code` varchar(25) collate utf8_bin default NULL,
`contact_phone` varchar(25) collate utf8_bin default NULL,
`item_name` varchar(127) collate utf8_bin default NULL,
`item_number` varchar(127) collate utf8_bin default NULL,
`invoice` varchar(127) collate utf8_bin default NULL,
`for_auction` tinyint(10) default NULL,
`auction_buyer_id` varchar(75) collate utf8_bin default NULL,
`auction_closing_date` varchar(100) collate utf8_bin default NULL,
`auction_multi_item` double default NULL,
`creation_timestamp` timestamp NULL default CURRENT_TIMESTAMP,
`address_country_code` varchar(2) collate utf8_bin default NULL,
`payer_business_name` varchar(150) collate utf8_bin default NULL,
`receiver_id` varchar(15) collate utf8_bin default NULL,
`test_ipn` tinyint(1) default '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=4 ;

 

Current SQL Code: Displays the information from Reg table

I need to show if the user has paid by also looking up the payment table.


<table width="100%" border="0" cellspacing="2" cellpadding="2">
<tr>
<td align="center"><img src="../images/image.png" width="750" height="90" /></td>
</tr>
<tr>
<td align="center"><h1>Title</h1></td>
</tr>
<tr>
<td align="center"><h2>Sub Title</h2></td>
</tr>
<tr>
<td align="center"></td>
</tr>
<tr>
<td align="center"></td>
</tr>
</table>
<?php
include("../includes/sql-conn.php");

$db = mysql_connect ($hostname, $username, $password) or die ('Failed to connect to database: ' . mysql_error());
mysql_select_db($database);

$query = 'SELECT * FROM XXX WHERE class_1 = "Option A" ORDER BY Name ASC';
$result = mysql_query($query) or die ('Failed to query ' . mysql_error());
?>
<table width="100%">
<tr>
<th scope="col">Option A</th>
<th scope="col">Name</th>
<th scope="col">Department</th>
<th scope="col">Email</th>
<th scope="col">Member ID</th>
<th scope="col">Chapter</th>
<th scope="col">Shirt Size</th>
<th scope="col">Second Choice</th>
<th scope="col">Attending</th>
<th scope="col">Paid</th>
</tr>
<?php
while ($row = mysql_fetch_assoc($result))
{
$name = $row['name'];
$department = $row['department'];
$email = $row['email'];
$member = $row['member'];
$memberid = $row['member_id'];
$chapter = $row['chapter'];
$shirt = $row['shirt'];
$class_1 = $row['class_1'];
$class_2 = $row['class_2'];
$attending = $row['attending'];
$paid = $row['paid'];

if ($member == "Yes"){
$amount = "$30.00";
}else{
$amount = "$50.00";
}



?>
<tr>
<td> </td>
<td><div align="center"><?php echo "$name";?></div></td>
<td><div align="center"><?php echo "$department";?></div></td>
<td><div align="center"><?php echo "$email";?></div></td>
<td><div align="center">
 <?php if (!$memberid){echo "N/A";}else{ echo "$memberid";}?>
 </div></td>
<td><div align="center"><?php if (!$chapter){ echo "N/A"; }else{ echo "$chapter";} ?></div></td>
<td><div align="center"><?php echo "$shirt";?></div></td>
<td><div align="center"><?php echo "$class_2";?></div></td>
<td><div align="center"><?php echo "$attending";?></div></td>
<td><div align="center"><?php if ($paid == ""){ echo ("No Info"); }else{echo "$paid"; echo (" ( $amount)");}?></div></td>
</tr>
<?php

}
?>
</table>
<?php
mysql_free_result($result);
mysql_close($db);
?>
<hr />
<?php
$db2 = mysql_connect ($hostname, $username, $password) or die ('Failed to connect to database: ' . mysql_error());
mysql_select_db($database);

$query2 = 'SELECT * FROM XXX WHERE class_1 = "Option B" ORDER BY Name ASC';
$result2 = mysql_query($query2) or die ('Failed to query ' . mysql_error());
?>
<table width="100%">
<tr>
<th scope="col">Option B</th>
<th scope="col">Name</th>
<th scope="col">Department</th>
<th scope="col">Email</th>
<th scope="col">Member ID</th>
<th scope="col">Chapter</th>
<th scope="col">Shirt Size</th>
<th scope="col">Second Choice</th>
<th scope="col">Attending</th>
<th scope="col">Paid</th>
</tr>
<?php
while ($row = mysql_fetch_assoc($result2))
{
$name = $row['name'];
$department = $row['department'];
$email = $row['email'];
$member = $row['member'];
$memberid = $row['member_id'];
$chapter = $row['chapter'];
$shirt = $row['shirt'];
$class_1 = $row['class_1'];
$class_2 = $row['class_2'];
$attending = $row['attending'];
$paid = $row['paid'];

if ($member == "Yes"){
$amount = "$30.00";
}else{
$amount = "$50.00";
}

?>
<tr>
<td> </td>
<td><div align="center"><?php echo "$name";?></div></td>
<td><div align="center"><?php echo "$department";?></div></td>
<td><div align="center"><?php echo "$email";?></div></td>
<td><div align="center">
 <?php if (!$memberid){echo "N/A";}else{ echo "$memberid";}?>
 </div></td>
<td><div align="center"><?php if (!$chapter){ echo "N/A"; }else{ echo "$chapter";} ?></div></td>
<td><div align="center"><?php echo "$shirt";?></div></td>
<td><div align="center"><?php echo "$class_2";?></div></td>
<td><div align="center"><?php echo "$attending";?></div></td>
<td><div align="center"><?php if ($paid == ""){ echo ("No Info"); }else{echo "$paid"; echo (" ( $amount)");}?></div></td>
</tr>
<?php

}
?>
</table>
<?php
mysql_free_result($result2);
mysql_close($db2);
?>
<hr />
<?php
$db3 = mysql_connect ($hostname, $username, $password) or die ('Failed to connect to database: ' . mysql_error());
mysql_select_db($database);

$query3 = 'SELECT * FROM XXX WHERE class_1 = "Option C" ORDER BY Name ASC';
$result3 = mysql_query($query3) or die ('Failed to query ' . mysql_error());
?>
<table width="100%">
<tr>
<th scope="col">Option C</th>
<th scope="col">Name</th>
<th scope="col">Department</th>
<th scope="col">Email</th>
<th scope="col">Member ID</th>
<th scope="col">Chapter</th>
<th scope="col">Shirt Size</th>
<th scope="col">Second Choice</th>
<th scope="col">Attending</th>
<th scope="col">Paid</th>
</tr>
<?php
while ($row = mysql_fetch_assoc($result3))
{
$name = $row['name'];
$department = $row['department'];
$email = $row['email'];
$member = $row['member'];
$memberid = $row['member_id'];
$chapter = $row['chapter'];
$shirt = $row['shirt'];
$class_1 = $row['class_1'];
$class_2 = $row['class_2'];
$attending = $row['attending'];
$paid = $row['paid'];

if ($member == "Yes"){
$amount = "$30.00";
}else{
$amount = "$50.00";
}

?>
<tr>
<td> </td>
<td><div align="center"><?php echo "$name";?></div></td>
<td><div align="center"><?php echo "$department";?></div></td>
<td><div align="center"><?php echo "$email";?></div></td>
<td><div align="center">
 <?php if (!$memberid){echo "N/A";}else{ echo "$memberid";}?>
 </div></td>
<td><div align="center"><?php if (!$chapter){ echo "N/A"; }else{ echo "$chapter";} ?></div></td>
<td><div align="center"><?php echo "$shirt";?></div></td>
<td><div align="center"><?php echo "$class_2";?></div></td>
<td><div align="center"><?php echo "$attending";?></div></td>
<td><div align="center"><?php if ($paid == ""){ echo ("No Info"); }else{echo "$paid"; echo (" ( $amount)");}?></div></td>
</tr>
<?php
}
?>
</table>
<?php
mysql_free_result($result3);
mysql_close($db3);
?>

Edited by jay7981
Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.