Need help with award feature

[Miks]

I wanted to create an award system, I have most of it setup but now I need to add a request to some pages to check if the user has the award already, if they deserve it, and then issue it.

So for example I am checking to see if a user is Level 5 and if they have been given the award already

I know theres probably something out there similar but I am doing this to learn, plus it would be nice to make my own version.

Anyway, heres the code

Whats happening is even though I am level 5 and have the award it is still going to the statement that says "Award Added"

awardno = Each award has its own number which is how I'm going to check if they have it already, I have issued myself this award so I have award no 1 and I am level 5

 

<?php
require_once("globals.php");
$i=$db->query("SELECT * FROM uawards WHERE id={$userid}");

if ($ir['level'] == 5 && $i['awardno'] == 1 )
{
   echo "You are level 5 and you have the award";
}
else if ($ir['level'] == 5)
{
   echo "Award Added";
}
$h->endpage();
?>

 

I'm guessing its to do with this line? Somethings wrong but what?

if ($ir['level'] == 5 && $i['awardno'] == 1 )

Anyways, as always thanks for taking the time to look!!

[lucky3809]

Looks fine to me, it could be that the field is either mis spelled or not in your database uaward table, have you checked that?

if not that, next I would test it by

if ($i['awardno'] == 1)

{

echo "You have the award";

}

else

{

echo "Award Added";

}

That would determine if it is the awardno field that is giving you the problem, it could also be a simple fix such adding ticks to `id` in your query.

Or trying...

if ($ir['level'] == 5 && $i['awardno'] == 1)

{

echo "You are level 5 and you have the award";

}

else if ($ir['level'] == 5 && !$i['awardno'] == 1)

{

echo "Award Added";

}

[Miks]

Ok for some reason its not seeing if ($i['awardno'] == 1)

Spelling is correct, well I hope so as I've triple checked! I'm not sure what would be stopping it?

When I put the ticks in ' it gives me this error

QUERY ERROR:

Query was SELECT 'awardno' FROM 'uawards' WHERE 'id'=1

[lucky3809]

OMG Sorry... I know what is wrong now you are forgetting the $db->fetch_row($i)...

<?php
require_once("globals.php");
$i=$db->query("SELECT * FROM uawards WHERE id={$userid}");
$a=$db->fetch_row($i);
if ($ir['level'] == 5 && $a['awardno'] == 1 )
{
   echo "You are level 5 and you have the award";
}
else if ($ir['level'] == 5)
{
   echo "Award Added";
}
$h->endpage();
?>

[Miks]

Thanks [MENTION=65073]lucky3809[/MENTION] it works now :)

[Miks]

One last question on the same topic if you dont mind

I'm having trouble coming up with the right query to then assign them there award. I have tried the following

$db->query("INSERT INTO uawards WHERE reason=You got this award for reaching level 5 , awardno=1 , id=$userid");

$db->query("INSERT INTO uawards SET reason=You got this award for reaching level 5 , awardno=1 , id=$userid");

$db->query("UPDATE uawards SET reason='You got this award for reaching level 5' , awardno='1' , id='$userid'");

$db->query("UPDATE uawards SET reason='You got this award for reaching level 5' , awardno='1' , id='$userid' WHERE id='$userid'");

$db->query("UPDATE uawards INSERT reason='You got this award for reaching level 5' , awardno='1' , id='$userid' WHERE id='$userid'");

$db->query("INSERT reason=You got this award for reaching level 5 , awardno=1 , id=$userid WHERE id=$userid");

So I'm guessing I dont need to use UPDATE as its not updating a value but adding a new value to a table? This is the first time I have created another table and assigned the user ID as well as the value to that table etc. Well to be honest I'm doing a lot of firsts this week!

Heres the full code for the page

I have taken a look at some examples from other pages within the game to try and help but :(

 

<?php
require_once("globals.php");
$i=$db->query("SELECT * FROM uawards WHERE id={$userid}");
$a=$db->fetch_row($i);

if ($a['awardno'] == 1 && $ir['level'] == 5)
{
   echo "You are level 5 and you have this award";
}

else if ($ir['level'] == 5)
{
$db->query("INSERT reason=You got this award for reaching level 5 , awardno=1 , id=$userid WHERE id=$userid");
   echo "Award Added";
}
$h->endpage();
?>

Edited March 28, 2015 by Miks

[Hedge]

try something like...

$db->query("INSERT INTO uawards (`reason`, `awardno`, `id`) VALUES ('You got this award for reaching level 5', 1, $userid)");

[Miks]

That didnt work, the brackets caused an unexpected T string error so I removed them and then it came up with a query error similar to this

QUERY ERROR:

Query was INSERT INTO 'uawards' VALUES '1', 'You got this award for reaching level 5', '1'

$db->query("INSERT INTO 'uawards' VALUES '$userid', 'You got this award for reaching level 5', '1'");

[Miks]

The page loads but doesnt display the success message and doesnt input the data with the below string, no query error though?

$db->query("INSERT INTO uawards (id, reason, awardno) VALUES ($userid, You got this award for reaching level 5, 1)");

 

<?php
require_once("globals.php");
$i=$db->query("SELECT * FROM uawards WHERE id={$userid}");
$a=$db->fetch_row($i);

if ($a['awardno'] == 1 && $ir['level'] == 5)
{
   echo "You are level 5 and you have this award";
}

else if ($ir['level'] == 5)
{
$db->query("INSERT INTO uawards (id, reason, awardno) VALUES ($userid, You got this award for reaching level 5, 1)"); 
   echo "Award Added";

}
$h->endpage();
?>

[AdamHull]

You have no quotes around the text you are adding to the database also add or die(mysql_error()); to the end of your query it will so the errors in the query

<?php
require_once("globals.php");
$i=$db->query("SELECT * FROM uawards WHERE id={$userid}");
$a=$db->fetch_row($i);

if ($a['awardno'] == 1 && $ir['level'] == 5)
{
   echo "You are level 5 and you have this award";
}

else if ($ir['level'] == 5)
{
$db->query("INSERT INTO uawards (id, reason, awardno) VALUES ('$userid', 'You got this award for reaching level 5', 1)");
   echo "Award Added";

}
$h->endpage();
?>

Edited March 28, 2015 by adamhull

[Coly010]

The page loads but doesnt display the success message and doesnt input the data with the below string, no query error though?

$db->query("INSERT INTO uawards (id, reason, awardno) VALUES ($userid, You got this award for reaching level 5, 1)");

 

<?php
require_once("globals.php");
$i=$db->query("SELECT * FROM uawards WHERE id={$userid}");
$a=$db->fetch_row($i);

if ($a['awardno'] == 1 && $ir['level'] == 5)
{
   echo "You are level 5 and you have this award";
}

else if ($ir['level'] == 5)
{
$db->query("INSERT INTO uawards (id, reason, awardno) VALUES ($userid, You got this award for reaching level 5, 1)"); 
   echo "Award Added";

}
$h->endpage();
?>

ok mate, your gonna have to look up SQL Statements.

this will work:

 

<?php
require_once("globals.php");
$i=$db->query("SELECT * FROM uawards WHERE id={$userid}");
$a=$db->fetch_row($i);

if ($a['awardno'] == 1 && $ir['level'] == 5)
{
   echo "You are level 5 and you have this award";
}

else if ($ir['level'] == 5)
{
$db->query("INSERT INTO uawards (id, reason, awardno) VALUES ($userid, 'You got this award for reaching level 5', 1)"); 
   echo "Award Added";

}
$h->endpage();
?>

 

When inserting data into a database through a query, a string must be surrounded in quotes. i.e apostrophe at the start and the end '

Next up, a tick is different to an apostrophe. Learn the difference:

Tick: `

Apostrophe: '

On my keyboard the tick key is beside 1 key, and below my ESC.

You use ticks when referencing which table and columns you want from your database.

$db->query("SELECT `username` FROM `users` WHERE `userid`=$userid");

A few SQL References to look at:

http://www.w3schools.com/sql/sql_select.asp - SELECT

http://www.w3schools.com/sql/sql_insert.asp - INSERT INTO

http://www.w3schools.com/sql/sql_update.asp - UPDATE

http://www.w3schools.com/sql/sql_datatypes_general.asp - DATA TYPES

Use the last one to check what data type is most appropriate for your needs.

For example, if you have an int that is only ever 0 or 1 int(1) would do the job, rather than int(11).

Now a few PHP things to look at:

http://www.w3schools.com/php/php_operators.asp - PHP OPERATORS - Pay particular attention to the comparison ones.

http://www.w3schools.com/php/php_if_else.asp - IF ~ ELSE ~ ELSEIF - Get very familiar with these, a lot of programming involves it.

http://www.w3schools.com/php/php_switch.asp - SWITCH - Is sometimes a useful alternative to IF/ELSE

[NonStopCoding]

im gona assume that your feild in the database can hold text or varchar if so check that it greater than for example

EDIT: Damn you's beat me to it haha

 

Uawards

> `user` int(20) not null default 0

> `text` varchar(40) NOT NULL

> `award` int(20) not null default 0

if you have it set to varchar(20) it will only hold 20 characters (thats including spaces i think correct me if i am wrong here :) )

also as adam said

 

$db->query("INSERT INTO `uawards` (`id`, `reason`, `awardno`)  "." VALUES ({$userid}, 'You got this award for reaching level 5', '1')");

[Coly010]

im gona assume that your feild in the database can hold text or varchar if so check that it greater than for example

EDIT: Damn you's beat me to it haha

 

Uawards

> `user` int(20) not null default 0

> `text` varchar(40) NOT NULL

> `award` int(20) not null default 0

if you have it set to varchar(20) it will only hold 20 characters (thats including spaces i think correct me if i am wrong here :) )

also as adam said

 

$db->query("INSERT INTO `uawards` (`id`, `reason`, `awardno`)  "." VALUES ({$userid}, 'You got this award for reaching level 5', '1')");

Yeah pretty sure space counts as a character

[Miks]

[MENTION=65530]Coly010[/MENTION] Thank you that was very helpful and informative as always, I greatly appreciate you taking the time out and to go into detail. I will take a read of your references this evening!

[Miks]

I have another problem, using the same code it wont add a second award, is this because my userid is already in the table? If I remove the first award from the database then it will add the second award but if there is already an award in the database it wont add the next one.

QUERY ERROR:

Query was INSERT INTO `uawards` (`id`, `reason`, `awardno`) VALUES ('1', 'You got this award for reaching level 10', '2')

 

<?php
require_once("globals.php");
$i=$db->query("SELECT * FROM uawards");
$a=$db->fetch_row($i);

if ($a['awardno'] == 2 && $ir['level'] >= 10)
{
   echo "You are level 5 and you have this award";
}

else if ($ir['level'] >= 10)
{
$db->query("INSERT INTO `uawards` (`id`, `reason`, `awardno`) VALUES ('$userid', 'You got this award for reaching level 10', '2')");
   echo "Award Added";

}
$h->endpage();
?>

 

I've tried

$db->query("INSERT INTO uawards (id, reason, awardno) VALUES ($userid, 'You got this award for reaching level 10', 2)");

and

$db->query("INSERT INTO `uawards` (`id`, `reason`, `awardno`) VALUES ('$userid', 'You got this award for reaching level 10', '2')");

Edited March 29, 2015 by Miks

[KyleMassacre]

That could be why. Your ID column may be set to a unique value. You can try to remove the keys from the table of any are set. I find it easier to have a id column that is a primary unique key that auto increments so it's easier to manage the table. So what you also can do is create a new column like that and remove the primary key from your current column. This way if you need to you can programmatically manage your table. Currently you have limited functionality of it like indexing, and deleting if need be

- - - Updated - - -

That could be why. Your ID column may be set to a unique value. You can try to remove the keys from the table of any are set. I find it easier to have a id column that is a primary unique key that auto increments so it's easier to manage the table. So what you also can do is create a new column like that and remove the primary key from your current column. This way if you need to you can programmatically manage your table. Currently you have limited functionality of it like indexing, and deleting if need be

[Miks]

[MENTION=68711]KyleMassacre[/MENTION] You were right, thank you, and I took your advise about having the AI id column aswell ☺

[Miks]

Update:

Award 1 gets added to the database and doesnt repeat itself when you visit the page with this code, so appears to work fine

 

<?php
require_once("globals.php");
$i=$db->query("SELECT * FROM `uawards` WHERE userid={$userid}");
$a=$db->fetch_row($i);

if ($a['awardno'] == 1 && $ir['level'] >= 5)
{
echo "You have the award";
}
else if ($ir['level'] >= 5)
{
$db->query("INSERT INTO `uawards` (`id`, `userid`, `awardno`) VALUES ('',{$userid}, '1')");
   echo "
<div class='desc2'>
<div class='citstat'>
<table class='mytable'><tr><td><center><BR>
<font color='green'>Congratulations!</font> you have earned an award!<BR><img src='https://cdn4.iconfinder.com/data/icons/pc_de_hamburg_icon_pack/32x32/bestseller.png'><BR>You have reached level 10<BR><BR></center></td></tr></table></div></div>
"; 
}
$h->endpage();
?>

 

Award 2 with pretty much the same code works but every time you refresh the page it adds itsel over and over agin. The same applies to award 3.......But when you visit award 1 it wont repeat itself.

Heres the code for award 2, its the same as award 1 but with the check values changed

 

<?php
require_once("globals.php");
$i=$db->query("SELECT * FROM `uawards` WHERE userid={$userid}");
$a=$db->fetch_row($i);

if ($a['awardno'] == 2 && $ir['level'] >= 10)
{
echo "You have the award";
}
else if ($ir['level'] >= 10)
{
$db->query("INSERT INTO `uawards` (`id`, `userid`, `awardno`) VALUES ('',{$userid}, '2')");
   echo "
<div class='desc2'>
<div class='citstat'>
<table class='mytable'><tr><td><center><BR>
<font color='green'>Congratulations!</font> you have earned an award!<BR><img src='https://cdn4.iconfinder.com/data/icons/pc_de_hamburg_icon_pack/32x32/bestseller.png'><BR>You have reached level 10<BR><BR></center></td></tr></table></div></div>
"; 
}

 

Here is a screenshot of the table structure

[ATTACH]1852[/ATTACH]

I've spent hours trying to figure it out, maybe it was my table structure? so i spent time reading up on Primary, Index, Unique values etc Then I started reading up on fetch_row and read somewhere that it only looks at one row? Then I started reading up on fetch array, assoc, object etc I also thought it might be to do with my statements and my checks....

Anyway, a few hours later and I'v learned a bit more but cant work out why its not working and repeating the other awards but not award 1

Edited March 29, 2015 by Miks

[Coly010]

Update:

Award 1 gets added to the database and doesnt repeat itself when you visit the page with this code, so appears to work fine

 

<?php
require_once("globals.php");
$i=$db->query("SELECT * FROM `uawards` WHERE userid={$userid}");
$a=$db->fetch_row($i);

if ($a['awardno'] == 1 && $ir['level'] >= 5)
{
echo "You have the award";
}
else if ($ir['level'] >= 5)
{
$db->query("INSERT INTO `uawards` (`id`, `userid`, `awardno`) VALUES ('',{$userid}, '1')");
   echo "
<div class='desc2'>
<div class='citstat'>
<table class='mytable'><tr><td><center><BR>
<font color='green'>Congratulations!</font> you have earned an award!<BR><img src='https://cdn4.iconfinder.com/data/icons/pc_de_hamburg_icon_pack/32x32/bestseller.png'><BR>You have reached level 10<BR><BR></center></td></tr></table></div></div>
"; 
}
$h->endpage();
?>

 

Award 2 with pretty much the same code works but every time you refresh the page it adds itsel over and over agin. The same applies to award 3.......But when you visit award 1 it wont repeat itself.

Heres the code for award 2, its the same as award 1 but with the check values changed

 

<?php
require_once("globals.php");
$i=$db->query("SELECT * FROM `uawards` WHERE userid={$userid}");
$a=$db->fetch_row($i);

if ($a['awardno'] == 2 && $ir['level'] >= 10)
{
echo "You have the award";
}
else if ($ir['level'] >= 10)
{
$db->query("INSERT INTO `uawards` (`id`, `userid`, `awardno`) VALUES ('',{$userid}, '2')");
   echo "
<div class='desc2'>
<div class='citstat'>
<table class='mytable'><tr><td><center><BR>
<font color='green'>Congratulations!</font> you have earned an award!<BR><img src='https://cdn4.iconfinder.com/data/icons/pc_de_hamburg_icon_pack/32x32/bestseller.png'><BR>You have reached level 10<BR><BR></center></td></tr></table></div></div>
"; 
}

 

Here is a screenshot of the table structure

[ATTACH]1852[/ATTACH]

I've spent hours trying to figure it out, maybe it was my table structure? so i spent time reading up on Primary, Index, Unique values etc Then I started reading up on fetch_row and read somewhere that it only looks at one row? Then I started reading up on fetch array, assoc, object etc I also thought it might be to do with my statements and my checks....

Anyway, a few hours later and I'v learned a bit more but cant work out why its not working and repeating the other awards but not award 1

 

Your problem is that your SELECT query is now retrieving two rows from the database. That means there are two values stored in $i['awardno']. The if statement doesn't know which to use so returns false. (That's in Lay man terms)

You need to loop through each result you get from the database or add a second condition to your where clause

[NonStopCoding]

to collaborate what Coly010 said you will require a while loop

Lets say we have a table structure

Users

*Uid (auto increment)

* userid (int value of 15)

* username (varchar(40))

and we want to pull all users usernames from it

$var = $db->query("SELECT `username` FROM `users` ORDER BY `username` DESC");
while($an = $db->fetch_row($var))
{
    // echo out there names
    echo $an['username'];
}

[Coly010]

[MENTION=70347]NonStopCoding[/MENTION] thanks, I was on my phone so couldn't give an example :D

[Miks]

I dont seem to understand, I cant get it to work right

$i=$db->query("SELECT `awardno` FROM `uawards` WHERE userid={$userid}");
$a = $db->fetch_row($i);

if ($ir['level'] <= 9 && $a['awardno'] == 2)
{
while($a = $db->fetch_row($i))
{
echo "You have the award already";
}
}
else if ($ir['level'] >= 10)
{
$db->query("INSERT INTO `uawards` (`id`, `userid`, `awardno`) VALUES ('',{$userid}, '2')");
   echo "
<div class='desc2'>
<div class='citstat'>
<table class='mytable'><tr><td><center><BR>
<font color='green'>Congratulations!</font> you have earned an award!<BR><img src='https://cdn4.iconfinder.com/data/icons/pc_de_hamburg_icon_pack/32x32/bestseller.png'><BR>You have reached level 10<BR><BR></center></td></tr></table></div></div>
"; 

}

 

Its failing and going straight to the else if statement and adding the award again.....Need a smoke break!

[Veramys]

$i=$db->query("SELECT `awardno` FROM `uawards` WHERE userid={$userid}");

while($a = $db->fetch_row($i))
{
if ($ir['level'] <= 9 && $a['awardno'] == 2)
{
echo "You have the award already";
die($h->endpage());
}
else if ($ir['level'] >= 10)
{
$db->query("INSERT INTO `uawards` (`id`, `userid`, `awardno`) VALUES ('',{$userid}, '2')");
   echo "
<div class='desc2'>
<div class='citstat'>
<table class='mytable'><tr><td><center>
<font color='green'>Congratulations!</font> you have earned an award!<img src='https://cdn4.iconfinder.com/data/icons/pc_de_hamburg_icon_pack/32x32/bestseller.png'>You have reached level 10</center></td></tr></table></div></div>
"; 

}
}

 

Try this perhaps?

[Miks]

Thanks but when I changed it to your suggestion it then added the award and every time I loaded the page it would duplicate on the screen and the database

So first visit it added one award and displayed 1 award, the second time it would display two awards and add the award twice, then 4 times and then 8 times etc

[Veramys]

Ok take out the while completely. Wasn't paying much attention lol (watching tv while coding. So try this.

$i=$db->query("SELECT `awardno` FROM `uawards` WHERE userid={$userid}");
$a = $db->fetch_row($i);
if ($ir['level'] <= 9 && $a['awardno'] == 2)
{
echo "You have the award already";
die($h->endpage());
}
else if ($ir['level'] >= 10)
{
$db->query("INSERT INTO `uawards` (`id`, `userid`, `awardno`) VALUES ('',{$userid}, '2')");
   echo "
<div class='desc2'>
<div class='citstat'>
<table class='mytable'><tr><td><center>
<font color='green'>Congratulations!</font> you have earned an award!<img src='https://cdn4.iconfinder.com/data/icons/pc_de_hamburg_icon_pack/32x32/bestseller.png'>You have reached level 10</center></td></tr></table></div></div>
"; 

}