Need help with checking mysql for 3 words when its expecting 5

1TekPro · January 28, 2020, 2:45am

I am trying to make a word game where people guess a phrase. The database (setphrase) has 5 columns (word1, word2, word3, word4 and word5) that the phrase is stored in. This functions properly but if we set the phrase as 4 words, it no longer works. any help?
get url :

/actions.php?action=phrase&name=$(queryencode ${user})&word1=$(queryencode $(1))&word2=$(queryencode $(2))&word3=$(queryencode $(3))&word4=$(queryencode $(4))&word5=$(queryencode $(5))

Function from actions.php

        $sql="SELECT * FROM setphrase";
        $correctanswer=mysql_query($sql);
        while ($row = mysql_fetch_assoc($correctanswer)) {
            if($word1 == $row['word1'] && $word2 == $row['word2'] && $word3 == $row['word3'] && $word4 == $row['word4'] && $word5 == $row['word5']) {
                //check if ticket name exists
                $sql = "SELECT (name) FROM entrants WHERE name = '$name'";
                $result = mysql_query($sql) or trigger_error(mysql_error(), E_USER_ERROR);
                $row = mysql_fetch_row($result);
                    if ($row[0] > 0) {
                        //Exists - update tickets
                        $sql="UPDATE entrants SET tickets=tickets+1 WHERE name='$name'";
                        $result=mysql_query($sql);
                    } else {
                        //Doesn't exist - create ticket
                        $sql="INSERT INTO entrants (name, tickets) VALUES ('$name', 1)";
                        $result=mysql_query($sql);
                    }
                        
                //check if phrase name exists
                $sql = "SELECT (name) FROM phrase WHERE name = '$name'";
                $result = mysql_query($sql) or trigger_error(mysql_error(), E_USER_ERROR);
                $row = mysql_fetch_row($result);
                    if ($row[0] > 0) {
                        //Exists - update wins
                        $sql="UPDATE phrase SET wins=wins+1 WHERE name='$name'";
                        $result=mysql_query($sql);
                            echo "Congratulations $name! You have guessed the phrase and received an extra ticket. A win has also been added to your total wins on the leaderboard.";
                            //Delete the table rows
                            $sql="DELETE FROM setphrase";
                            $result=mysql_query($sql);
                    } else {
                        //Doesn't exist - create win
                        $sql="INSERT INTO phrase (name, wins) VALUES ('$name', 1)";
                        $result=mysql_query($sql);
                            echo "Congratulations $name! You have guessed the phrase and received an extra ticket. A win has also been added to your total wins on the leaderboard.";
                            //Delete the table rows
                            $sql="DELETE FROM setphrase";
                            $result=mysql_query($sql);
                    }
            } else {
                echo "Good guess, but that is not the right answer.";
            }
        }

benanamen · January 28, 2020, 3:39am

You have a much bigger problem. You are using dangerous obsolete code that is vulnerable to an Sql Injection Attack that has also been completely removed from PHP.

You need to use PDO with Prepared Statements.

1TekPro · January 28, 2020, 3:45am

Thank you for your reply. I understand the vulnerability and plan to correct that in the future (I will lookup tome tutorials on PDO). I’m just trying to figured out this issue first.

phdr · January 28, 2020, 8:54am

Because you didn’t post your code using Markdown or bbcode [code] [/code] tags, it’s not readable. Edit your 1st post to add one of these methods around the code.

Next, it’s possible to learn good programming practices while learning how to program. Whoever taught the original coder, don’t cover any fundamental practices. Some applicable practices -

If you find yourself repeating code for each possible value, it’s a sign you are doing something the hardest way possible. After about the 2nd or 3rd nested if() statement and repeated message output, a little voice should have been saying, is this the best way to write code?
It’s not necessary to run a SELECT query to decide if you should INSERT a new row or UPDATE an existing row. There’s a single query that does this. However, for the win and ticket data, you should NOT just maintain a value in a column, as there’s no audit trail to let you know if a programming mistake, a double form submission, or nefarious use/cheating altered the value. Read the next point.
You should insert a new row into a ‘win’ table and a ‘ticket’ table for each ‘event’ that causes a win or a change in the number of tickets. This alone would reduce 6 sets of logic/queries to just 2 sets.
When storing related data (the win and ticket tables), you should use the id (auto-increment integer primary index) from parent table as a foreign key in the child table(s).

1TekPro · January 28, 2020, 11:51am

Thank you for you helpful response.

I have corrected the nested ifs in step 1 of your reply.
I was thinking I needed separate ifs incase there wasnt a 4th or 5th word.
I originally tried the insert if not exist update, but it just wouldn’t work. maybe I coded it wrong.
I’m not sure what you mean in steps 3 and 4.

During my testing the get looks like this

/actions.php?action=phrase&name=user&word1=test&word2=test&word3=test&word4=&word5=)

astonecipher · January 28, 2020, 2:42pm

That is due to your scheme not being workable. It can be corrected, but I don’t know if that is the way it should be done.

1TekPro · January 28, 2020, 4:44pm

I’m not sure what you are telling me here. My scheme? If it can be corrected, what should the first steps be? How should it be done?

astonecipher · January 28, 2020, 4:53pm

Explain to me how you want it to work first.

1TekPro · January 28, 2020, 5:08pm

I would like this

/actions.php?action=phrase&name=$(queryencode ${user})&word1=$(queryencode $(1))&word2=$(queryencode $(2))&word3=$(queryencode $(3))&word4=$(queryencode $(4))&word5=$(queryencode $(5))

To be chaecked against a database to see if what they typed matches. I need it to have the option to ignore non used words.

if the database has ‘test’ ‘test’ ‘test’ ‘test’ ‘test’ and the user types test test test test test it works.
but
if the database has ‘test’ ‘test’ ‘test’ ‘’ ‘’ and the user types test test test it doesnt work.

astonecipher · January 28, 2020, 5:16pm

Not how it currently works.

How should it work. What does the system do and what does the user do?

Is it literally, guess a word five times?

1TekPro · January 28, 2020, 5:30pm

yeah guess the 5 words (phrase). a bot in a chat channel sends the get request using a makeshift custom api

I think I found why its not working. I don’t think the parameters are being sent at all if there are not 5 present.
/actions.php?action=phrase&name=$(queryencode ${user})&word1=$(queryencode $(1))&word2=$(queryencode $(2))&word3=$(queryencode $(3))&word4=$(queryencode $(4))&word5=$(queryencode $(5))
can php or java be used inside this? maybe an isset or something?

1TekPro · January 28, 2020, 5:48pm

ok I figured it out thanks to astonecipher getting me to ask myself questions
I needed to add a | (“or”) in my get request for the parameters I was sending then add an or ‘somethingelse’ in my querry

/actions.php?action=phrase&name=$(queryencode ${user})&word1=$(queryencode $(1))&word2=$(queryencode $(2))&word3=$(queryencode $(3))&word4=$(queryencode $(4)|somethingelse)&word5=$(queryencode $(5)|somethingelse)