jacko_162 Posted December 28, 2010 Share Posted December 28, 2010 Hi guys, hopfully you can help me write out an if Statment; basically i want to make an "IF" statment to check if the user has purchased a "ticket" within the last 24 hours, if so echo YES if not echo NO the 'ticket' database is setup as follows: auctionID promoID username charID ticketNumber ticketPrice purchaseDate - Example:" 20101228 11:59:25" it will include the WHERE clause of "username =$username" can someone help me please, im not good with time based stuff.. Quote Link to comment Share on other sites More sharing options...
desjardins2010 Posted December 28, 2010 Share Posted December 28, 2010 First off, where is the actual purchase being stored in the db I would assume in the ticket number? Quote Link to comment Share on other sites More sharing options...
Zurev Posted December 28, 2010 Share Posted December 28, 2010 86400 Seconds are in a day, since timestamps are measured in seconds we want to check if the purchase was made in the last 86400 seconds. As desjardins2010 pointed out, you are missing some information, so this is sort of generic. $inTheLastDay = (time()-strtotime($purchaseDate)<86400) ? TRUE : FALSE; echo ($inTheLastDay) ? "Yes" : "No"; The first line checks if the current time is less than one day after the purchase time, if it is (meaning they purchased in the last day), it returns true, otherwise it will return false. The next outputs Yes if the first line is true, and No if it's false. Quote Link to comment Share on other sites More sharing options...
jacko_162 Posted December 28, 2010 Author Share Posted December 28, 2010 yes upon purchase the ticket number is added and it relates to the auctionID or promoID Quote Link to comment Share on other sites More sharing options...
jacko_162 Posted December 28, 2010 Author Share Posted December 28, 2010 ok here is my ticketpurchase page. <?php session_start(); header('Location: index.php'); include "connect.php"; $id = $_SESSION['id']; $user = $_SESSION['user']; $ticketNumber = $_POST[ticketNumber]; //echo $_POST[ticketNumber]; $today = date("Ymd G:i:s"); $querySold = mysql_query("SELECT * FROM tickets WHERE promoID='$_POST[promoID]'&&ticketNumber='$ticketNumber';") or die(mysql_error()); //echo $querySold; $sold = mysql_fetch_assoc($querySold); //print_r($sold); //echo $sold; //check if ticket is sold if(empty($sold)!=FALSE){ $queryBal = mysql_query("SELECT user_iskbalance FROM users WHERE username = '$user';") or die(mysql_error()); //echo $querySold; //echo $user; $balArray = mysql_fetch_assoc($queryBal); $bal = $balArray[user_iskbalance]; $newBal = $bal-$_POST[ticketPrice]; //check if user has bought more than 1 ticket //check if he has the money to buy the ticket if($bal>=$_POST[ticketPrice]){ //remove the money $queryBalRemoveal = mysql_query("UPDATE `users` SET `user_iskbalance`='$newBal' WHERE `username`='$user';") or die(mysql_error()); //buy ticket $query = mysql_query("INSERT INTO tickets(promoID, username, charID, ticketNumber, ticketPrice, purchaseDate) VALUES ('$_POST[promoID]', '$user', '$id', '$_POST[ticketNumber]', '$_POST[ticketPrice]', '$today');") or die(mysql_error()); } else{ die("Insufficent balance. Please add more ISK") ; } } else{ die("Ticket has been Sold!"); } ?> there is already an if statement to check if user has the credit to purchase i wanted to add a second check to see if any tickets have been purchased in the last 24 hours, so check if user has balance AND bought a ticket within 24 hours. Quote Link to comment Share on other sites More sharing options...
johnny86 Posted December 28, 2010 Share Posted December 28, 2010 I think you could use MySQLs TIMEDIFF() function: SELECT * FROM tickets WHERE username = 'username' AND TIMEDIFF(NOW() , purchaseDate) < '24:00:00' Quote Link to comment Share on other sites More sharing options...
jacko_162 Posted December 28, 2010 Author Share Posted December 28, 2010 I think you could use MySQLs TIMEDIFF() function: SELECT * FROM tickets WHERE username = 'username' AND TIMEDIFF(NOW() , purchaseDate) < '24:00:00' not working =( Quote Link to comment Share on other sites More sharing options...
desjardins2010 Posted December 28, 2010 Share Posted December 28, 2010 that's not the correct format to use the TIMEDIFF function give me a few look at this but TIMEDIFF is def' the way to accomplish what you trying to do mate.. Quote Link to comment Share on other sites More sharing options...
desjardins2010 Posted December 28, 2010 Share Posted December 28, 2010 SELECT * FROM tickets WHERE username = 'username' AND HOUR(TIMEDIFF(NOW() , purchaseDate)) < 24 Quote Link to comment Share on other sites More sharing options...
desjardins2010 Posted December 28, 2010 Share Posted December 28, 2010 that should work Quote Link to comment Share on other sites More sharing options...
jacko_162 Posted December 28, 2010 Author Share Posted December 28, 2010 didnt pull any date. is it because the "purchaseDate" column isnt timestamp and is varchar? Quote Link to comment Share on other sites More sharing options...
BlueSkyIS Posted December 28, 2010 Share Posted December 28, 2010 purchaseDate should be a date or date-time for that SQL to work. a date should be stored as a date or date/time anyway. Quote Link to comment Share on other sites More sharing options...
johnny86 Posted December 28, 2010 Share Posted December 28, 2010 Use: ALTER TABLE tickets MODIFY purchaseDate DATETIME That will also try to convert all the values to you new column type 'DATETIME' Quote Link to comment Share on other sites More sharing options...
desjardins2010 Posted December 28, 2010 Share Posted December 28, 2010 yes, bluesky is right the data needs to be stored in this case I would use date/time Quote Link to comment Share on other sites More sharing options...
jacko_162 Posted December 28, 2010 Author Share Posted December 28, 2010 if i make it datetime does the above code neet to be altered to compensate for the change or can it remain? Quote Link to comment Share on other sites More sharing options...
jacko_162 Posted December 28, 2010 Author Share Posted December 28, 2010 ok changed it to date time and the following sql statment works; SELECT * FROM tickets WHERE username = '$username' AND HOUR(TIMEDIFF(NOW() , purchaseDate)) < 24 so back to original question, how do i add this to the above PHP page code as an if statment. also wanted to restrict users buying more than 1 ticket per auctionID how can i achieve this? Quote Link to comment Share on other sites More sharing options...
johnny86 Posted December 28, 2010 Share Posted December 28, 2010 If that statement returns rows from the database, then the user has bought tickets in the past 24 hours. Translated to if: if(mysql_affected_rows($connection) > 0) { // Tickets bought in the last 24 hours } else { // Not bought } Quote Link to comment Share on other sites More sharing options...
johnny86 Posted December 28, 2010 Share Posted December 28, 2010 And also when user tries to buy tickets you have to check: SELECT auctionid FROM tickets WHERE user = 'username' AND auctionid = 'auctionid_user_is_buying' If that returns a row, you'll know the user already has a ticket to that auction if(mysql_affected_rows($conn) > 0) { //Already bought a ticket to that auction } else { // Not bought } Quote Link to comment Share on other sites More sharing options...
johnny86 Posted December 28, 2010 Share Posted December 28, 2010 Or you could use SELECT COUNT(*) AS ticket_count FROM tickets WHERE user = 'username' AND auctionid = 'auctionid' If the result of that is greater than 0 -> user already has a ticket to that auction. Quote Link to comment Share on other sites More sharing options...
jacko_162 Posted December 28, 2010 Author Share Posted December 28, 2010 ok i understand the if statments. im not sure where they all need to sit in the following page code: <?php session_start(); header('Location: index.php'); include "connect.php"; $id = $_SESSION['id']; $user = $_SESSION['user']; $ticketNumber = $_POST[ticketNumber]; //echo $_POST[ticketNumber]; $today = date("Ymd G:i:s"); $querySold = mysql_query("SELECT * FROM tickets WHERE auctionID='$_POST[auctionID]'&&ticketNumber='$ticketNumber';") or die(mysql_error()); //echo $querySold; $sold = mysql_fetch_assoc($querySold); //print_r($sold); //echo $sold; //check if ticket is sold if(empty($sold)!=FALSE){ $queryBal = mysql_query("SELECT user_iskbalance FROM users WHERE username = '$user';") or die(mysql_error()); //echo $querySold; //echo $user; $balArray = mysql_fetch_assoc($queryBal); $bal = $balArray[user_iskbalance]; $newBal = $bal-$_POST[ticketPrice]; //check if he has the money to buy the ticket if($bal>=$_POST[ticketPrice]){ //remove the money $queryBalRemoveal = mysql_query("UPDATE `users` SET `user_iskbalance`='$newBal' WHERE `username`='$user';") or die(mysql_error()); //buy ticket $query = mysql_query("INSERT INTO tickets(auctionID, username, charID, ticketNumber, ticketPrice, purchaseDate) VALUES ('$_POST[auctionID]', '$user', '$id', '$_POST[ticketNumber]', '$_POST[ticketPrice]', '$today');") or die(mysql_error()); } else{ die("Insufficent balance. Please add more ISK") ; } } else{ die("Ticket has been Sold!"); } ?> Quote Link to comment Share on other sites More sharing options...
johnny86 Posted December 28, 2010 Share Posted December 28, 2010 I would change this row to return results also when the user has bought a ticket to that auction: $querySold = mysql_query("SELECT * FROM tickets WHERE auctionID='$_POST[auctionID]'&&ticketNumber='$ticketNumber';") or die(mysql_error()); TO: $querySold = mysql_query("SELECT * FROM tickets WHERE auctionID='$_POST[auctionID]' AND (ticketNumber='$ticketNumber' OR username = 'username');") or die(mysql_error()); Now the query will look up the tickets for $_POST[auctionID] and check if either of those conditions match in any of those auctions. Now you can just say that 'you have already bought a ticket to this auction or someone bought the ticket already' Quote Link to comment Share on other sites More sharing options...
jacko_162 Posted December 28, 2010 Author Share Posted December 28, 2010 I would change this row to return results also when the user has bought a ticket to that auction: $querySold = mysql_query("SELECT * FROM tickets WHERE auctionID='$_POST[auctionID]'&&ticketNumber='$ticketNumber';") or die(mysql_error()); TO: $querySold = mysql_query("SELECT * FROM tickets WHERE auctionID='$_POST[auctionID]' AND (ticketNumber='$ticketNumber' OR username = 'username');") or die(mysql_error()); Now the query will look up the tickets for $_POST[auctionID] and check if either of those conditions match in any of those auctions. Now you can just say that 'you have already bought a ticket to this auction or someone bought the ticket already' That worked thank you VERY much, now i can only purchase 1 ticket maximum =) now where do i place the code for checking if user has bought other tickets in last 24 hours and allow them to buy if they have and fail if they havnt. Quote Link to comment Share on other sites More sharing options...
johnny86 Posted December 28, 2010 Share Posted December 28, 2010 I don't really understand what you want to do an when? If user has bought ticket in the last 24 hours: you want to prevent user from buying new ticket? Quote Link to comment Share on other sites More sharing options...
jacko_162 Posted December 28, 2010 Author Share Posted December 28, 2010 its a complicated system. basically the below code enables players to buy a "promo" ticket and not normal tickets. I want to restrict these "promo" tickets somewhat with the following 2x rules; Users must ONLY be able to purchase 1 ticket ONLY (this is now working!!) User must of bought at least 1 "normal" ticket in the last 24 hours. This is the process form: <form action="buy_ticket2.php" method="post"> <input type="hidden" name="ticketNumber" value="<? echo($tt); ?>" /> <input type="hidden" name="promoID" value="<? echo $promoID; ?>" /> <input type="hidden" name="ticketPrice" value="<? echo $ticketPrice[ticketPrice]; ?>" /> <input type="submit" value="Buy Ticket <? echo $tt; ?>" class="btn"> </form> This is buy_ticket2.php <?php session_start(); header('Location: index.php'); include "connect.php"; $id = $_SESSION['id']; $user = $_SESSION['user']; $ticketNumber = $_POST[ticketNumber]; //echo $_POST[ticketNumber]; $today = date("Ymd G:i:s"); $querySold = mysql_query("SELECT * FROM tickets WHERE promoID='$_POST[promoID]' AND (ticketNumber='$ticketNumber' OR username = '$user');") or die(mysql_error()); //echo $querySold; $sold = mysql_fetch_assoc($querySold); //print_r($sold); //echo $sold; //check if ticket is sold if(empty($sold)!=FALSE){ $queryBal = mysql_query("SELECT user_iskbalance FROM users WHERE username = '$user';") or die(mysql_error()); //echo $querySold; //echo $user; $balArray = mysql_fetch_assoc($queryBal); $bal = $balArray[user_iskbalance]; $newBal = $bal-$_POST[ticketPrice]; //check if he has the money to buy the ticket if($bal>=$_POST[ticketPrice]){ //remove the money $queryBalRemoveal = mysql_query("UPDATE `users` SET `user_iskbalance`='$newBal' WHERE `username`='$user';") or die(mysql_error()); //buy ticket $query = mysql_query("INSERT INTO tickets(promoID, username, charID, ticketNumber, ticketPrice, purchaseDate) VALUES ('$_POST[promoID]', '$user', '$id', '$_POST[ticketNumber]', '$_POST[ticketPrice]', '$today');") or die(mysql_error()); } else{ die("Insufficent balance. Please add more ISK") ; } } else{ die("Ticket has been Sold!"); } ?> here is the Database Layout: auctionID - Example "23" promoID - Example "2" username - "Username" charID - "56934958" ticketNumber "8" ticketPrice "56000000" purchaseDate - Example:" 20101228 11:59:25" i can tell in the database if a user has purchased a ticket to a normal auction as the auctionID colum has data, if a ticket is bought to a promo auction this column stays empty. and vice versa. Hope that explains it a little more? Quote Link to comment Share on other sites More sharing options...
johnny86 Posted December 28, 2010 Share Posted December 28, 2010 What about this? Change all this to your code. Keep your old commented Haven't tested this one. $queryBal = mysql_query("SELECT t1.username, t2.user_iskbalance AS balance FROM tickets AS t1 LEFT JOIN users AS t2 ON t1.username = t2.username WHERE t1.username = '$user' AND HOUR(TIMEDIFF(NOW(), t1.purchaseDate)) < 24;") or die(mysql_error()); $balArray = mysql_fetch_assoc($queryBal); if(count($balArray) > 0) { // User has bought a ticket last 24 hour, // Get balance $bal = $balArray['balance']; } else { die("You haven't bought a ticket in the past 24 hours"); } //check if he has the money to buy the ticket if($bal>=$_POST[ticketPrice]){ $newBal = $bal-$_POST[ticketPrice]; Quote Link to comment 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.