MySQL's LIKE condition query

CMellor · 10-24-2007, 02:36 AM

Hiya,

Am making a rating script and what I want is, when you have already voted in the particular rating bar, you cannot rate again, it just becomes static.

In the database where I store the votes, values and IP's of the rating, in the IP row, all the IP's are serialized.

Code:

a:1:{i:0;s:14:"81.102.225.228";}

I use this query to check if my IP is already stored in the database:

PHP Code:


			
<?php

// Query to check if the user has voted

$voted = mysql_query(sprintf("SELECT ip FROM %s

    WHERE ip LIKE '%s'

      AND id = '%d'",

        $table,

        $ip.'%',

        $id

)) or die(mysql_error());

?>

I then use this if statement to check the query:

PHP Code:


			
<?php

for($num = 1; $num <= $stars; $num++) {

    // If the user HASN'T voted

    if(!$voted) {

        $image .= '

        <li><a href="javascript:rate('.$num.')" title="'.$num.' out of '.$stars.'" class="star'.$num.'">&nbsp;</a></li>

        ';

    }

    else {

        $image .= '<li>&nbsp;</li>';

    }

}

?>

Now when I haven't voted, I can click the rating bar, but when I have rated, I can still click it! I'm pretty sure it has something to do with the LIKE condition, and because the IP row is serialized. Can I search within the serialized code for just the IP? Or something like that...

Anyone have an idea's?

Thanks,
- Chris.

bluesaga · 10-24-2007, 09:44 AM

I would say you are doing this completely wrong...

Serializing things in a database should just be used to unserialize with php when you need to store arrays that you cannot simply store in the database. Generally (always) speaking you would never attempt to search do a query on a serialized column.

You should store:
IP
Rating

In seperate column then do:

SELECT * FROM table WHERE ip = '192.168.0.1'

Using LIKE doesnt scale well either, which is another reason this is better.

If you do not understand this, please make another post and i'll try and explain a little better, or someone else will.

If you really want your script to work you need to add a wildcard to the prefix aswell as the affix to the IP variable in the sql query:

PHP Code:


			
<?php
// Query to check if the user has voted
$voted = mysql_query(sprintf("SELECT ip FROM %s
    WHERE ip LIKE '%s'
      AND id = '%d'",
        $table,
        '%'.$ip.'%',
        $id
)) or die(mysql_error());
?>

Notice:
'%'.$ip.'%',

Karl · 10-24-2007, 10:07 AM

I'd also suggest not using an IP as the primary key, consider a large network such as a College/University, only one person per network can vote. I'd suggest limiting voting to registered members (assuming you have registered members).

bluesaga · 10-24-2007, 12:20 PM

What i normally do, is have it for both.

have in the mysql:
id, rating, ip, user

When a user logs in/signs up, it updates the ratings table to update the user column.

This imo is the best approach for ratings as you do not force people to signup (which is tedious at the best of times) and handle registered users aswell.

Karl · 10-24-2007, 01:08 PM

But how does that overcome the network problem? 1 person votes on a 500 computer network and the other 499 users can't vote.

bluesaga · 10-24-2007, 01:12 PM

I'm not sure how i did it now, but i think it allowed people from the same ip address to post multiple ratings if they were logged in. But it would also allow IP addresses to rate a game once if they are not logged in.

I hope that makes sense?

Salathe · 10-24-2007, 02:38 PM

Going right back to the first post, you said that you can vote even if you've already voted before. This is not a SQL problem, it's a PHP problem. You have some code which you want to assign a boolean (true/false) value to the variable $voted but the way you go about it will not give false if the user has not voted:

PHP Code:


			
$voted = mysql_query(...);

Take a look at the PHP manual for mysql_query() and you'll see that the function returns either a resource, TRUE or FALSE in different circumstances. Only if the SQL statement causes an error will the function return false. If no rows are returned from the SQL query, the $voted variable will NOT be set to false! It will be set to a resource, regardless of the number of rows returned. Therefore, you can't simply check for true/false in the if statement -- you'd need to check if any rows were returned, with the mysql_num_rows() function.

To be able to use the if statement properly, you would need to change the assignment of the $voted variable to something more along the lines of:

PHP Code:


			
$voted = mysql_query(...) or die(mysql_error());
$voted = (bool) mysql_num_rows($voted) > 0;
// $voted now will be either true (1 or more rows returned) or false (zero rows)

Also, since you're not really interested in the row(s) returned from the database then why return rows/columns when you don't need to?

PHP Code:


			
SELECT
    'yay, found the ip' as result
FROM 
    mytable
WHERE 
    ip LIKE '%127.0.0.1%'
    AND id = 1
LIMIT 
    0,1;

CMellor · 10-24-2007, 06:02 PM

Ok I think I sorted it, I feel a bit dumb for not thinking about mysql_num_rows. Also I think there was a problem with the query, using sprintf, because I've tried it with a query on it's own and it's fine. With sprintf how do you determine numbers with decimals in them, like you'd find in an IP, because I don't think %d is the answer.

Anyway, I've sorted it out, thanks for the help :D You guys rock, it's nice knowing there's somewhere I can come for help.

Salathe · 10-24-2007, 06:11 PM

With regards to sprintf and IP addresses, they're just strings not numbers so you'd use %s.

Wildhoney · 10-24-2007, 07:48 PM

...And just to tie this thread up. You may wish to consider taking the advice of the posts further up and not serializing the data. LIKE is a slow piece of functionality for MySQL. How about using the IP address in its own designated column and making it a unique primary key? That way once MySQL finds the IP it's looking for, it won't continue trawling the entire table to see if there's another one.

CMellor · 10-24-2007, 09:19 PM

I'll definitely consider doing that method when I decide to write up a poll script I want to have.

As for my rating script, I have it all working now, I've tested it with other people and it all works great, and am pretty happy... I surprise myself sometimes with what I can achieve.

Thanks again!

bluesaga · 10-24-2007, 10:52 PM

It may work for now but i can guarantee you that using LIKE is not the method to use for this sort of thing. You cannot use a key on the column, and when this starts to increase in votes it will get bloody slow :)