Jump to content

PHP 5/MySQL Script Help

jay7981

By jay7981
in PHP Coding Help

Recommended Posts

jay7981 Member

jay7981

Posted
Posted

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);
?>

Link to comment
https://forums.phpfreaks.com/topic/273963-php-5mysql-script-help/
Share on other sites

Archived

This topic is now archived and is closed to further replies.

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.