bschultz Posted June 7, 2007 Share Posted June 7, 2007 I need to write an authentication script. Here are the details: I run a site that streams audio broadcasts of college sports games. The listener needs to purchase the subscription. I have shopping cart setup to handle the sales. The cart writes everything to a database. The listener (customer) can purchase a single days "ticket" or the "season ticket" so that they can listen to EVERY game. The authentication will allow the listener in to the "listen" page (that has an embedded player) only if they've purchased the subscription. The cart doesn't have a "products" field so I can't check against the product ID to see if they have purchased the single day "ticket" or the "season" ticket. Here's where I need some help. I need to check the database (for a single day ticket) so that if the email_address field of the DB exists and the date_purchased field equals the current date, it will authenticate the user. If not, it ships them off to the shopping cart. ONE NOTE...THE DATE PURCHASED FIELD IN THE DB IS CURDATE + TIME (2007-06-06 23:45:15). I'd need to check against the date...not the time. Also, I need to check the DB (for a season ticket) so that if the email_address field of the DB exists and the order_total equals 75.00, it will authenticate the user. If not, it ships them off to the shopping cart. Can anyone point me in the right direction? Thanks. Brian Quote Link to comment https://forums.phpfreaks.com/topic/54541-solved-mysql-authentication/ Share on other sites More sharing options...
fenway Posted June 7, 2007 Share Posted June 7, 2007 Where are you having trouble? Quote Link to comment https://forums.phpfreaks.com/topic/54541-solved-mysql-authentication/#findComment-269945 Share on other sites More sharing options...
bschultz Posted June 7, 2007 Author Share Posted June 7, 2007 Thanks Fenway. I don't know how to get the date changed to strip out the time and also how to build the if else loop for the two different DB checks. Quote Link to comment https://forums.phpfreaks.com/topic/54541-solved-mysql-authentication/#findComment-269998 Share on other sites More sharing options...
bschultz Posted June 7, 2007 Author Share Posted June 7, 2007 Here's what I've come up with...and it doesn't work. Parse error on line 33...expecting a "(" on this line if order_total = "75.00" { Can someone tell me if I'm even in the close to getting this right? Thanks! <?php $conn1 = mysql_connect(localhost,xxx,xxx); if (!$conn1) { echo "Unable to connect to DB: " . mysql_error(); exit; } if (!mysql_select_db("cart")) { echo "Unable to select mydbname: " . mysql_error(); exit; } $sql1 = "SELECT customers_email_address, date_purchased, order_total FROM orders WHERE customers_email_address = '$_POST[myemail]'"; $result1 = mysql_query($sql1); if (!$result1) { echo "Could not successfully run query ($sql1) from DB: " . mysql_error(); exit; } if (mysql_num_rows($result1) == 0) { echo '<meta http-equiv=Refresh content=1;url="error.php">'; } while ($row1 = mysql_fetch_assoc($result1)) { if order_total = "75.00" { session_start(); echo "<meta http-equiv=Refresh content=1;url='listen2.php'>"; } if order_total = "5.00" { if date_purchased = CURDATE { session_start(); echo "<meta http-equiv=Refresh content=1;url='listen2.php'>"; } }} mysql_free_result($result1); ?> Quote Link to comment https://forums.phpfreaks.com/topic/54541-solved-mysql-authentication/#findComment-270080 Share on other sites More sharing options...
bubblegum.anarchy Posted June 7, 2007 Share Posted June 7, 2007 At a glance: Wrap if conditions in paranthesis and add a variable identifier to order_title and date_purchased, and I do not think CURDAT is anything in PHP Quote Link to comment https://forums.phpfreaks.com/topic/54541-solved-mysql-authentication/#findComment-270345 Share on other sites More sharing options...
bschultz Posted June 7, 2007 Author Share Posted June 7, 2007 CURDATE is a MySQL function...like I said in my original post, I didn't know how to change the date in the cart DB to strip the time...I was trying to see what CURDATE would return. As for the rest of your suggestion, that seemed to work...but I must not be writing this correctly, because it didn't return the results I wanted. <?php $conn1 = mysql_connect(localhost,xxx,xxx); if (!$conn1) { echo "Unable to connect to DB: " . mysql_error(); exit; } if (!mysql_select_db("cart")) { echo "Unable to select mydbname: " . mysql_error(); exit; } $sql1 = "SELECT customers_email_address, date_purchased, order_total FROM orders WHERE customers_email_address = '$_POST[myemail]'"; $result1 = mysql_query($sql1); if (!$result1) { echo "Could not successfully run query ($sql1) from DB: " . mysql_error(); exit; } if (mysql_num_rows($result1) == 0) { echo '<meta http-equiv=Refresh content=1;url="error.php">'; } while ($row1 = mysql_fetch_assoc($result1)) { if ($order_total = "75.00") { session_start(); echo "<meta http-equiv=Refresh content=1;url='listen2.php'>"; } if ($order_total = "5.00") { if ($date_purchased = (CURDATE)) { session_start(); echo "<meta http-equiv=Refresh content=1;url='listen2.php'>"; } }} mysql_free_result($result1); ?> If I enter the correct email address, it logs me in...no matter what is in $date_purchased. Quote Link to comment https://forums.phpfreaks.com/topic/54541-solved-mysql-authentication/#findComment-270352 Share on other sites More sharing options...
bubblegum.anarchy Posted June 7, 2007 Share Posted June 7, 2007 The PHP processor does not understand MySQL functions. The PHP forum may be more appropriate for PHP related issues. Quote Link to comment https://forums.phpfreaks.com/topic/54541-solved-mysql-authentication/#findComment-270358 Share on other sites More sharing options...
bschultz Posted June 7, 2007 Author Share Posted June 7, 2007 but I'm in the middle of a MySQL query...now I'm confused!! Quote Link to comment https://forums.phpfreaks.com/topic/54541-solved-mysql-authentication/#findComment-270370 Share on other sites More sharing options...
bubblegum.anarchy Posted June 8, 2007 Share Posted June 8, 2007 $sql1 = "SELECT customers_email_address, date_purchased, order_total FROM orders WHERE customers_email_address = '$_POST[myemail]'"; The MySQL query starts at the end of $sql1 = " and ends at "; - everything between the quotes is the MySQL query Quote Link to comment https://forums.phpfreaks.com/topic/54541-solved-mysql-authentication/#findComment-270384 Share on other sites More sharing options...
bschultz Posted June 8, 2007 Author Share Posted June 8, 2007 OK...I split this into 2 queries...and it still turns up false...I've even tried removing the CURDATE reference in the 2nd query for testing purposes, and it still turns up false. <?php session_start(); $conn1 = mysql_connect(localhost,xxx,xxx); if (!$conn1) { echo "Unable to connect to DB: " . mysql_error(); exit; } if (!mysql_select_db("cart")) { echo "Unable to select mydbname: " . mysql_error(); exit; } $sql1 = "SELECT customers_email_address, date_purchased, order_total FROM orders WHERE customers_email_address = '$_POST[myemail]' AND order_total = '75.00'"; $result1 = mysql_query($sql1); if (!$result1) { echo "Could not successfully run query ($sql1) from DB: " . mysql_error(); exit; } if (mysql_num_rows($result1) == 0) { //echo '<meta http-equiv=Refresh content=1;url="error.php">'; echo "You are not subscribed to the Season Ticket...but I'll check to see if your ticket is for today's game<br><br>"; } while ($row1 = mysql_fetch_assoc($result1)) { echo "Your season ticket is valid...enjoy the game...<meta http-equiv=Refresh content=1;url='listen2.php'>"; } mysql_free_result($result1); $sql2 = "SELECT customers_email_address, date_purchased, order_total FROM orders WHERE customers_email_address = '$_POST[myemail]' AND order_total = '5.00' AND date_purchased = CURDATE"; $result2 = mysql_query($sql1); if (!$result2) { echo "Could not successfully run query ($sql2) from DB: " . mysql_error(); exit; } if (mysql_num_rows($result2) == 0) { //echo '<meta http-equiv=Refresh content=1;url="error.php">'; echo "You are not subscribed to the daily ticket for today...please purchase one to listen. I'll redirect you soon... <meta http-equiv=Refresh content=10;url='/shop/'>"; } while ($row2 = mysql_fetch_assoc($result2)) { echo "Your single day ticket is valid...enjoy the game...<meta http-equiv=Refresh content=1;url='listen2.php'>"; } mysql_free_result($result2); ?> Quote Link to comment https://forums.phpfreaks.com/topic/54541-solved-mysql-authentication/#findComment-270509 Share on other sites More sharing options...
bschultz Posted June 8, 2007 Author Share Posted June 8, 2007 I tried to just test the query for the "single day ticket". I changed the CURDATE to CURRENT_DATE: Still turns up false: <?php session_start(); $conn1 = mysql_connect(localhost,xxx,xxx); if (!$conn1) { echo "Unable to connect to DB: " . mysql_error(); exit; } if (!mysql_select_db("cart")) { echo "Unable to select mydbname: " . mysql_error(); exit; } $sql1 = "SELECT customers_email_address, date_purchased, order_total FROM orders WHERE customers_email_address = '$_POST[myemail]' AND order_total = '5.00' AND date_purchased >= 'CURRENT_DATE():00:00:00' and date_purchased <= 'CURRENT_DATE():23:59:59;'"; $result1 = mysql_query($sql1); if (!$result1) { echo "Could not successfully run query ($sql1) from DB: " . mysql_error(); exit; } if (mysql_num_rows($result1) == 0) { //echo '<meta http-equiv=Refresh content=1;url="error.php">'; echo "You are not subscribed to the Season Ticket...but I'll check to see if your ticket is for today's game<br><br>"; } while ($row1 = mysql_fetch_assoc($result1)) { echo "Your season ticket is valid...enjoy the game...<meta http-equiv=Refresh content=1;url='listen2.php'>"; } mysql_free_result($result1); ?> Quote Link to comment https://forums.phpfreaks.com/topic/54541-solved-mysql-authentication/#findComment-270534 Share on other sites More sharing options...
bubblegum.anarchy Posted June 8, 2007 Share Posted June 8, 2007 What is this: 'CURRENT_DATE():00:00:00' Quote Link to comment https://forums.phpfreaks.com/topic/54541-solved-mysql-authentication/#findComment-270560 Share on other sites More sharing options...
bschultz Posted June 8, 2007 Author Share Posted June 8, 2007 The format of the cart DB is 2007-06-07 06:12:33 So, with that code date_purchased >= 'CURRENT_DATE():00:00:00' and date_purchased <= 'CURRENT_DATE():23:59:59;'"; I'm trying to check that if the person has purchased the "single game ticket" today, they would be authenticated Thanks for the help! Quote Link to comment https://forums.phpfreaks.com/topic/54541-solved-mysql-authentication/#findComment-270564 Share on other sites More sharing options...
bubblegum.anarchy Posted June 8, 2007 Share Posted June 8, 2007 Try... date_format(date_purchased, '%Y-%m-%d') = CURRENT_DATE Quote Link to comment https://forums.phpfreaks.com/topic/54541-solved-mysql-authentication/#findComment-270573 Share on other sites More sharing options...
bschultz Posted June 8, 2007 Author Share Posted June 8, 2007 That worked...THANK YOU! Quote Link to comment https://forums.phpfreaks.com/topic/54541-solved-mysql-authentication/#findComment-270579 Share on other sites More sharing options...
fenway Posted June 8, 2007 Share Posted June 8, 2007 There's also a DATE() function that does the same thing. Quote Link to comment https://forums.phpfreaks.com/topic/54541-solved-mysql-authentication/#findComment-271064 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.