if statment help

[jacko_162]

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..

[desjardins2010]

First off, where is the actual purchase being stored in the db I would assume in the ticket number?

[Zurev]

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.

[jacko_162]

yes upon purchase the ticket number is added and it relates to the auctionID or promoID

[jacko_162]

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.

[johnny86]

I think you could use MySQLs TIMEDIFF() function:

 

SELECT * FROM tickets WHERE username = 'username' AND TIMEDIFF(NOW() , purchaseDate) < '24:00:00'

[jacko_162]

I think you could use MySQLs TIMEDIFF() function:

 

SELECT * FROM tickets WHERE username = 'username' AND TIMEDIFF(NOW() , purchaseDate) < '24:00:00'

 

not working =(

[desjardins2010]

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..

[desjardins2010]

SELECT * FROM tickets WHERE username = 'username' AND HOUR(TIMEDIFF(NOW() , purchaseDate)) < 24

[desjardins2010]

that should work

[jacko_162]

didnt pull any date.

 

is it because the "purchaseDate" column isnt timestamp and is varchar?

[BlueSkyIS]

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.

[johnny86]

Use:

ALTER TABLE tickets MODIFY purchaseDate DATETIME

 

That will also try to convert all the values to you new column type 'DATETIME'

[desjardins2010]

yes, bluesky is right the data needs to be stored in this case I would use date/time

[jacko_162]

if i make it datetime does the above code neet to be altered to compensate for the change or can it remain?

[jacko_162]

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?

[johnny86]

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
}

[johnny86]

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
}

[johnny86]

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.

[jacko_162]

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!");	
}
?>

[johnny86]

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'

[jacko_162]

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.

[johnny86]

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?

[jacko_162]

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? 

[johnny86]

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];