IF / THEN statement using data from MySQL

[Rob (SA)]

Hi,

The query should be against each tournaments name "I_wish_to play" is the field heading listing the tournament name.

Players can enter more than one event but only enter an event once.

Once the specific tournament has reached a field size of 80 - the function "CLOSED" should effect.

The second script is theone that does the counting per tournament and I thought this would be more suitable?

Your ideas are welcomed - Thank you

[traq]

... Not sure if mysql requires the '' around columns.

they're not single quotes ( ' ), they're backtics ( ` ). They tell MySQL that the value is a proper name of a column or table, and not a keyword (e.g., if -for some reason- you had a column named SELECT or LIMIT:

"SELECT select FROM table WHERE limit=2 LIMIT 1";
-- doesn't work
SELECT `select` FROM `table` WHERE `limit`=2 LIMIT 1";
-- works fine

of course, a better solution is to not use keywords as column names, but it's good practice... I suppose it could happen by accident sometime, if a new keyword were introduced, or something).

Rob, I'm having a hard time following some of this. Can you show me the structure (column names, types, description of info contained) of your database table?

[Rob (SA)]

Hi,

Thanks for the reply.

I have treid to create a zip file but the size is 411Kb of the structure of my MYSQL table.

Not sure of another method I can use here.

Regards
Rob

[Rob (SA)]

Hi Folks,

The form and the counter works fine as can be veiwed on this page

What I need is for a script that will recognise the value (number) calculated on the table seen left on the link presented above.

This page is dynamic in nature however the page that requires this script is static in nature.

I hope this provides some clarity.

Regards
Rob

[bluewalrus]

We need to know the names of the tables being used and the columns/relation between them.

Thanks Traq didn't know the exact usage of the `s. I don't name my tables or columns with keywords or at least haven't yet.

[Rob (SA)]

Hi,

There is only one table being used . . ."_Form_Nr_3"

This form comprises of all information submitted from this page

The field headings are as per the entry form.

"Handicap" is the Handicap of the player
"I_wish_to_play" is the tournament name
"surname" is the surname of the player

The only difference is that the new database will be "_Form_Nr_4"

I hope this helps

[bluewalrus]

So you have table _Form_Nr_3 which contains the following columns?

Name
Surname
Date of Birth
Cell Phone
Home Phone
Work Phone
Email
SAJGF_Division
Personal_Division
Handicap

Is SAJGF_Division the tournament name?

I think you should redo the table into 3 tables. This layout is going to be hard to work with once you get a lot of data in there unless this is not how it is set up.

[Rob (SA)]

Hi,

The information you have there is correct.

There is also the option on this form to select a tournament and this appears under the column "I_wish_to_play"

SAJGF_Divison is combobox where the divison the players belongs to resides. There are 13 divisions.

This is set up already and has worked in previous events - the only thing required is the script to get the OPEN and CLOSED function to operate when the number of players for each tournament reaches 80.

The page has been setup and the script will have to be slightly difdferent for each tournament.

If it can be done dynamically then that would be wow but I dont have that much time unfortunatly.

[traq]

Rob,

Unfortunately, linking to your live pages is not very helpful in this case. All of the PHP and MySQL action is happening on your server, and we can not see any of it from the web.

If you already have a script that counts entries (as seen in the "total entries" box on this page), then I'm not sure what the problem is here. Is there some reason you can't use that same code again on your other page?

(I would assume it's something like this)

// name of tournament you wish to count entries from
// maybe from a form entry?
$tournament = '';

//  count the total entries with that tournament name
$c = mysql_query("SELECT count(*) from `_Form_Nr_3` WHERE `I_wish_to_play` = '$tournament'");

//  set "open" or "closed" based on that number
$openORclosed = $c >= 80 ? 'Closed' : 'Open';

// if there were 80 entries, then $openORclosed would hold the value "Closed"
// if there were fewer, $openORclosed would hold the value "Open". 

Thanks Traq didn't know the exact usage of the `s. I don't name my tables or columns with keywords or at least haven't yet.

I generally don't either, though there are some times when it makes a lot of sense for your column to be named "limit" or "set" or something. I think the main reason is forward-compatibility.

[Rob (SA)]

Hi,

The page wher ethis script is to work is statci and there is quite a bit of reptition.

Folk like yourself would know how I can incorpoarte this count option to the page. I dont have that experiance yet.

If this is the method then I will try it as a query in this page like you suggest but I do need advice.

Ther are 30 tournaments - so am i understanding that this script will then be included to each row that I have created, just with another tournament name in it?

<tr class="alt">
<td>KEMPTON PARK</td>
<td>05 JANUARY 2011</td>
<td>?</td>
<?php
if($num_rows >= 80){
echo '<td><font style="font-size:11px" color="Red" face="Arial">CLOSED</td>
<td>&nbsp;</td>';
}
else {
echo '<td><b><font style="font-size:11px"  color="Blue" face="Arial">OPEN</b></td>
<td><form action="https://www.payfast.co.za/eng/process" method="post"> 
<input type="hidden" name="cmd" value="_paynow"> 
<input type="hidden" name="receiver" value="junior@hayesgolf.co.za"> 
<input type="hidden" name="item_name" value="Payment - Kempton Park - Glacier Series"> 
<input type="hidden" name="item_description" value="payment"> 
<input type="hidden" name="amount" value="120.00"> 
<input type="hidden" name="return_url" value="http://www.gnjgf.co.za/entryform.php"> 
<input type="hidden" name="cancel_url" value="http://www.gnjgf.co.za/Glacier_Main.html"> 
<input type="submit" style="width:66px;height:20px;background-color:#B5CCE4;font-size:10px; font-family: arial, sans-serif;" value="PAY NOW"> 
</td>
';
}
?>
</tr>

<tr class="alt2">
<td>GLENDOWER</td>
<td>27 FEBRUARY 2011</td>
<td>?</td>
<?php
if($num_rows >= 80){
echo '<td><font style="font-size:11px" color="Red" face="Arial">CLOSED</td>
<td>&nbsp;</td>';
}
else {
echo '<td><b><font style="font-size:11px"  color="Blue" face="Arial">OPEN</b></td>
<td><form action="https://www.payfast.co.za/eng/process" method="post"> 
<input type="hidden" name="cmd" value="_paynow"> 
<input type="hidden" name="receiver" value="junior@hayesgolf.co.za"> 
<input type="hidden" name="item_name" value="Payment - Glendower - Glacier Series"> 
<input type="hidden" name="item_description" value="payment"> 
<input type="hidden" name="amount" value="120.00"> 
<input type="hidden" name="return_url" value="http://www.gnjgf.co.za/entryform.php"> 
<input type="hidden" name="cancel_url" value="http://www.gnjgf.co.za/Glacier_Main.html"> 
<input type="submit" style="width:66px;height:20px;background-color:#B5CCE4;font-size:10px; font-family: arial, sans-serif;" value="PAY NOW"> 
</td>
';
}
?>
</tr>