Dont Know Where To Start!!!

[billynastie]

Hi, I am trying to write a piece of code which may seem simple to a few people but im struggling to work it out correctly im after advice first so I wont be posting any of my code until ive been pointed in a direction first just incase im just overlooking something. Basically im using a cms system with login function im pulling data from a table and outputting it and cycling through each line called items what I am trying to do is have a button or link underneath based on whether or not the user is logged in which i can do but next what I need to work out is im only allowing 40 clicks of the button and every time the button is clicked it inserts data into another table but I need this button to change when the database has had 40 entries placed into it and I also have multiple items each with a seperate unique id and it must also only change the button for the item which has had 40 clicks made on it all other items which havent had 40 clicks must still display the button.

Im not very good with code and write very scrappy code at first then clean it up when I have it working but as the code isnt working I dont want to post code that doesnt work in the first place or anyone who reads it will try to make it work and it doesnt as it is seriously flawed in a way but if you need to see the code to get an understanding of how im trying to get it to work then please say so.

I think I need to break it down and maybe work it out with a few functions but functions are not a strong point of mine and i do need to work the code out as when its fully working the code when the user clicks the button it will go to paypal and back again and upon coming back to the site it will insert a random code into the table after the 40 entries have been reached it will pick a winning ticket number and display it.

 

People are viewing but not replying so I can only presume it is because I have not put in any code so here goes 2 lots of code both of which to do the same thing first one is the one I tried on the CMS program the second is one that is wrote independent of the CMS program.

 

CODE 1

<?
mysql_connect(DB_HOST, DB_USERNAME, DB_PASSWORD) or die(mysql_error());
mysql_select_db(DB_NAME) or die(mysql_error());
$query = "SELECT * FROM items"; 
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
$item = $row['ID'];
$image = $row['image'];
$desc = $row['desc'];
echo "<table width='450' border='0' cellspacing='0' cellpadding='0'>
  <tr>
    <td><img src=\"http://adietech.dyndns.org/raffle/media/".$image."\" width=\"150\" height=\"150\" /></td>
    <td>".$desc."</td>
  </tr>
    <tr>
    <td></td>
    <td></td>
  </tr>
</table>";
echo "<br />";

if(FRONTEND_LOGIN AND !$wb->is_authenticated()) {
echo"Your Need To Be Logged In And A Member To Buy A Raffle Ticket Click <a href='http://adietech.dyndns.org/raffle/pages/login.php'>Here</a>";
} elseif(FRONTEND_LOGIN AND $wb->is_authenticated()) {

$query = "SELECT *, COUNT(itemID) FROM raffles GROUP BY itemID"; 
$result = mysql_query($query) or die(mysql_error());
// Print out result
while($row = mysql_fetch_array($result)){
$total = $row['COUNT(itemID)'];
if ($total <= '4') {
echo"<form action=\"http://adietech.dyndns.org/raffle/index.php\" method=\"post\"><input type=\"hidden\" name=\"submit\" value=\"true\"><input type=\"hidden\" name=\"id\" value=\"".$item."\"><input type=\"hidden\" name=\"user\" value=\"".$wb->get_display_name()."\">";
for ($i = 0, $z = strlen($a = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890')-1, $s = $a{rand(0,$z)}, $i = 1; $i != 32; $x = rand(0,$z), $s .= $a{$x}, $s = ($s{$i} == $s{$i-1} ? substr($s,0,-1) : $s), $i=strlen($s)); 
echo "<input type=\"hidden\" name=\"ticket\" value=\"".$s."\">
<input name=\"buy\" type=\"submit\" value=\"Buy A Ticket\" /></form>";
} else {
$query = "SELECT *, COUNT(itemID) FROM raffles GROUP BY itemID"; 
$result = mysql_query($query) or die(mysql_error());
// Print out result
while($row = mysql_fetch_array($result)){
$total = $row['COUNT(itemID)'];
if ($total > '4') {
$itemid = $row['itemID']; 
$query = "SELECT * FROM raffles WHERE itemID = $itemid LIMIT 1";
$reply = mysql_query($query);
$rows = mysql_fetch_array($reply);
echo $rows['number'];
echo "Raffle Closed";
}
}

}}
if ($_POST['submit']=='true') {
$id = $_POST['id'];
$user = $_POST['user'];
$ticket = $_POST['ticket'];

$sql ="INSERT INTO raffles (ID, username, number, itemID) VALUES('', '$user', '$ticket', '$id')";
mysql_query($sql) or die(mysql_error());
}
?>

 

CODE 2

<?
mysql_connect('localhost', 'root', '') or die(mysql_error());
mysql_select_db('raffle') or die(mysql_error());
$query = "SELECT * FROM items"; 
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
$item = $row['ID'];
echo $item;
echo "<br />";
}
$query = "SELECT *, COUNT(itemID) FROM raffles GROUP BY itemID"; 
$result = mysql_query($query) or die(mysql_error());
// Print out result
while($row = mysql_fetch_array($result)){
$total = $row['COUNT(itemID)'];
if ($total <= '4') {
echo"FORM";
} else {
$query = "SELECT *, COUNT(itemID) FROM raffles GROUP BY itemID"; 
$result = mysql_query($query) or die(mysql_error());
// Print out result
while($row = mysql_fetch_array($result)){
$total = $row['COUNT(itemID)'];
if ($total > '4') {
$itemid = $row['itemID']; 
$test = "SELECT * FROM items WHERE ID = $itemid";
$answer = mysql_query($test);
$res = mysql_fetch_array($answer);
$winner = $res['winner'];
if ($winner != '') {
echo "Raffle Closed And The Winner Is ".$winner;
} else {
$query = "SELECT * FROM raffles WHERE itemID = $itemid ORDER BY RAND() LIMIT 1";
$reply = mysql_query($query);
$rows = mysql_fetch_array($reply);
$user = $rows['username'];
$insert ="UPDATE items SET winner = '$user'";
mysql_query($insert);
$number = $rows['number'];
echo $user." - ".$number;
}
}
}
}
}

?>

[ignace]

I need to work out is im only allowing 40 clicks of the button and every time the button is clicked it inserts data into another table but I need this button to change when the database has had 40 entries placed into it

 

Well you have got a few options here:

 

First option will probably won't work in your current design as you presumably want to limit 40 for a number of articles and don't want to create a table for each and every article:

 

Limit 40 rows at database level:

CREATE TABLE tableName (
    #table defintion
) MAX_ROWS = 40;

 

Second option is at application level:

 

I am assuming you are using a MyISAM table if you don't then you may pass on this one unless you use SQL_CALC_FOUND_ROWS and SELECT FOUND_ROW() afterwards (altough this is slower then a count(*) on a MyISAM table) Why MyISAM? because MyISAM stores the number of rows by default therefor if you query SELECT count(*) FROM table it will return the number of rows rather quickly as it doesn't have to calculate it anymore.

 

$query = 'SELECT count(*) found_rows FROM table';
$result = mysql_query($query, $db);
list($found_rows) = mysql_fetch_array($result, MYSQL_NUM);
if (40 < $found_rows) {
    //limit reached
}

[billynastie]

Hi thanks for you reply but I want one table to store the ticket numbers for each item not a table for each item for example say im running 2 items one ID1 one ID2 when it does the insert I need it to be able to insert into one table the data from either one of the item so limiting my table to only accept 40 entries is not an option but it does need to be able to insert 40 for ID1 and 40 for ID2 then no more basically it needs to read the database first and regular like every two seconds before allowing another entry and it needs to check how many entries have been inserted against each ID and if it returns 40 it shows IE: purchasing closed and the winner is.

 

[ignace]

basically it needs to read the database first and regular like every two seconds before allowing another entry and it needs to check how many entries have been inserted against each ID and if it returns 40 it shows IE: purchasing closed and the winner is.

 

That is what this is for:

 

Second option is at application level:

 

I am assuming you are using a MyISAM table if you don't then you may pass on this one unless you use SQL_CALC_FOUND_ROWS and SELECT FOUND_ROW() afterwards (altough this is slower then a count(*) on a MyISAM table) Why MyISAM? because MyISAM stores the number of rows by default therefor if you query SELECT count(*) FROM table it will return the number of rows rather quickly as it doesn't have to calculate it anymore.

 

$query = 'SELECT count(*) found_rows FROM table';
$result = mysql_query($query, $db);
list($found_rows) = mysql_fetch_array($result, MYSQL_NUM);
if (40 < $found_rows) {
    //limit reached
}

 

You will need to modify the query though as you need to count() only a sub-section of the table (those who's foreign key matches the primary key of your other table)

 

SELECT SQL_CALC_FOUND_ROWS, .. FROM table1 JOIN table2 ON table1.pk = table2.fk

 

Afterwards retrieve the found rows:

 

SELECT found_rows() found_rows

[billynastie]

Hi im slightly understanding the code especially the bit about joining the tables what ive having problems with is this is where im upto so far and I think im doing it worng because im using both methods.

 

<?
mysql_connect('localhost', 'root', '') or die(mysql_error());
mysql_select_db('raffle') or die(mysql_error());
$query = 'SELECT count(*) itemID FROM raffles';
$result = mysql_query($query);
$calc ='SELECT SQL_CALC_FOUND_ROWS, itemID FROM items JOIN raffles ON items.ID = raffles.itemID';
$number = mysql_query($calc);


list($itemID) = mysql_fetch_array($result, MYSQL_NUM);
if (4 < $itemID) {
echo "limit reached";
}


?>

[billynastie]

Hi I dont think ive explained what I am trying to do correctly or in full im writing a site when people can purchase a unique ticket number when the user is not logged into the site it still shows the item but underneath it shows a link to a login page and register page but when a user is logged in it shows a buy now button that bit is simple the next stage is where im having the problems the site is only accepting 40 entries per item and when the buy now button is pressed it assigns a unique number into a database and reduces the persons limit for bids by one once 40 entries have been accepted it closes sales and displays the winner and the wohle script has to run say every two seconds to make sure that it hasnt hit the 40 entries already also all items are in one database table and all entries for all items are in another table plus user accounts. Is this something I need a professional coder for or can someone please help me.

[ignace]

CREATE TABLE concerts (
    concerts_tickets_on_sale INTEGER,
);

 

Are there tickets available?

 

SELECT * FROM concerts WHERE concerts_id = $id AND concerts_tickets_on_sale > 0

 

Once a ticket is sold for a concert:

 

LOCK TABLES concerts WRITE; UPDATE concerts SET concerts_tickets_on_sale = concerts_tickets_on_sale - 1 WHERE concerts_id = $id; UNLOCK TABLES;

[billynastie]

Again thanks for the useful tip but not ticket are not available I want it to generate a random ticket number upon entering the data into the database.