2

Is there a way to check if a value exists in a mysql column? I have table songs, and there are some columns, one of them is called 'agent_ip' where i will put a list/array of all user ip's that will visit the site. I need to check if current user ip is present in column 'agent_ip'. Here is some of my code:

public function voteSong($song_id, $case, $agent_ip) {           
    $query = $this->link->prepare("SELECT * FROM songs WHERE id = ? LIMIT 1"); 
    $query->bindValue(1, $song_id);
    $query->execute();
    $rowcount = $query->rowCount();        

    if ($rowcount != 0)
    {
        if (!in_array($agent_ip, $r['ip']))
        {
            if ($case === 'like')
            {
                while($r = $query->fetch())
                {
                    $vote = $r['votes'] + 1;
                }
            } 
            elseif ($case === 'dislike')
            {
                while ($r = $query->fetch())
                { 
                    if ($r['votes'] > 0)
                    {
                        $vote = $r['votes'] - 1;
                    }
                    else
                    {
                        $vote = 0;
                    }
                }
            }
            $query = $this->link->prepare("UPDATE songs SET datetime = ?, votes = ?, agent_ip = ? WHERE id = ?"); 
            $query->execute(array(date("Y-m-d H:i:s"), $vote, $agent_ip, $song_id));
        }
    }
}

The line if(!in_array($agent_ip, $r['ip'])) contains the wrong function which won't work, but i need an alternative for mysql. $r['ip'] variable is data from the 'agent_ip' column which look like this 127.0.0.1, 127.0.0.1, 127.0.0.1 (using 127.0.0.1 just for example, every 127.0.0.1 is a different ip)

Improve this question
7
  • 1
    First off, please post your code as text, not a picture. Second... take another look at that line. What is $r? Where do you declare it? Commented Dec 22, 2014 at 23:19
  • 2
    $r isn't defined there. Commented Dec 22, 2014 at 23:19
  • 1
    No text; no help. No one will steal your precious code. Commented Dec 22, 2014 at 23:19
  • Definitely need to know what this mystery $r variable is... Commented Dec 22, 2014 at 23:21
  • What code editor you were using? Is it Notepad++? Commented Dec 22, 2014 at 23:22

2 Answers 2

Reset to default
2

If you're only checking against a single IP, why don't you just modify your query from:

"SELECT * FROM songs WHERE id = ? LIMIT 1"

To:

"SELECT * FROM songs WHERE id = ? AND agent_ip = ? LIMIT 1"

It seems a bit wasteful to query your whole result set when you are only querying against a specific IP and returning a single row.

EDIT: Your current method would be extremely inefficient, you are passing a unique agent_ip each time you want to query a song to check if the IP exists, that would be fine, but you are creating a new DB connection every time from which you pull back all info which belongs to that song.

Lets say we have 1 song, and 3IP's, currently the application would work like this:

1) Call the method, passing IP_1
2) Query the database getting all songs for ID1
3) Check if IP_1 is in the result set and do process

4) Call the method, passing IP_2
5) Query the database getting all songs for ID1
6) Check if IP_2 is in the result set and do process

7) Call the method, passing IP_3
8) Query the database getting all songs for ID1
9) Check if IP_2 is in the result set and do process

As you can see, there is a lot of repetition here which is going to hinder your apps performance as it scales, you would be so much better modifying your current function to accept a list of results for a song which is pre-queried only once and then recursively call a check function by passing that result array with your unique IP address.

UPDATE You stated I understand that i need to have 2 tables(1 = songs; 2 = votes). But i cannot imagine how i will get songs from database, arranged by votes quantity.

You should read SQL's JOIN documentation, the concept is simple - JOIN allows you to pull back a more detailed set of information based on what you want to query, in your example you may want to find out how many votes a specific song has.

Your tables may look like:

Songs 
SONG_ID     Primary Key
SONG_TITLE  
SONG_DURATION
SONG_TAGS

Votes
VOTE_ID     Primary Key
SONG_ID     Foreign Key - (references the song_id table)
VOTE_RES    Bool (either 0 for no, 1 for yes)
AGENT_IP    Who sent the vote

You could then find out how many people said they liked the song by performing a join:

SELECT * FROM songs 
JOIN votes 
ON songs.song_id = votes.song_id 
WHERE songs.song_id = 1 
AND votes.vote_res = 1;

This would return all the song with the id of 1 and all of its associated likes. Hope that helps a bit :)

Improve this answer
Sign up to request clarification or add additional context in comments.

12 Comments

I need to check every single ip that will access site.
I see, if that is the case why does your method signature accept the agent_ip parameter, as that would be unique and therefore redundant for how you are currently querying your system.
If i got you right, then the answer is: my method accept the agent_ip parameter because i have outside a function which takes the user ip, so i can check if this ip exists also in my 'array' in mysql column.
Thank you very much sir, you helped me a lot. Definitely have to read some more SQL documentation.
Just want to share the code i'm using now: SELECT * FROM songs LEFT JOIN votes ON songs.id = votes.song_id AND votes.vote_res = 1 GROUP BY songs.id ORDER BY (COUNT(votes.id)) DESC Hope it does not contain any mistakes, also, thanks again to @Alex
|
1

First you need to deserialize/decode the data from the column to the proper php array and then you can use in_array function. In your post edit you stated that you have a comma separated list of IP's, so to convert it to array you need to use an explode function:

$ip_list = explode(', ', $r['ip']);

now you can use the in_array function on the new array:

if(!in_array($agent_ip, $ip_list))
Improve this answer

3 Comments

That must be because your column in the table is named agent_ip and you're trying to get the 'ip' column from your result set. Try changing the $r['ip'] to $r['agent_ip']. And as Alex pointed out you should really rethink your architecture design.
Unfortunately, i changed that a while ago, after i posted code here, it's not working :(
Then there must be some other problems that are not directly related to your question. TBH I would drop the code you're writing right now and take Alex's advice to learn some more about SQL. Happy coding! :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.