PHP & MySQL Problem - Checking a row for existing data

Hi all, I’ve got a blank on this - could be because it’s late but would really appreciate any help.

Essentially I have a rating script, but there’s one part I can’t quite get working right. This part needs to look through a field called ‘used_ips’ which contains all the ip’s which have voted and search through it for the current ip. If it finds the IP it should return a 1, otherwise a 0.

This is the offending code:

$voted=mysql_num_rows(mysql_query("SELECT used_ips FROM $dbname.$tbname".ratings." WHERE used_ips LIKE '%".$ip."%' AND id='".$id."' ")); 

I was trying to use mysql_num_rows as the query is restricted to 1 row anyway (by $id for the primary key), so if it find it we’ll get a 1 otherwise a 0.

That works in my head but my server throws up the following error every time - any ideas?

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\\www\\xampplite\\htdocs\\rate.php

SELECT 1 WHERE used_ips = 127.0.0.1 LIMIT 1

SELECT COUNT(*) > 0 …(etc)

If you cast the result of that query to an integer, it will be 0 or 1

let me guess the reason for doing the search – you want to know if the IP is already in there because you don’t want to add it twice, right?

there are better ways of doing this, if you’re interested

If it is not typo and you have the same line in your real file, you string concatenation is wrong.


$voted=mysql_num_rows(mysql_query("SELECT used_ips FROM $dbname.$tbname".ratings." WHERE used_ips LIKE '%".$ip."%' AND id='".$id."' "));

See the ratings word. What it is for?

String concatenation is correct, and ratings could be defined.


define('ratings', '.ratings');

Or, if it is not defined, PHP will treat it as the string “ratings”
PS. For any of you who like this and want to type all their strings this way, I encourage you to please don’t! :slight_smile:

Yes, and also as a certain part of the script won’t function when the IP is already present.

If you’re offering I’d love to hear a better way :slight_smile:

@ Rajug, ScallioXTX - I had to put that in there as I hadn’t defined the table - foolishly. I’ll change that in advance but didn’t think the problem was with the table selector?

Thanks for the suggestions guys, will give them a go!

okay, what do you currently do when you discover the IP already exists in the table? do you throw away the data and just issue an error message? or is there something that gets updated (e.g. date of last submission)?

If the IP is discovered the search should return a 1; while if it’s not present it should return a 0.

I’m worried that I’m storing the IP’s in the database wrong, is there a ‘best practice’ for this? It’s currently being stored in the field as:

a:3:{i:0;s:7:“1.1.3.1”;i:1;s:7:“1.1.2.1”;i:2;s:7:“1.1.4.1”;}

Just need a query to spot whether they are in there, as then the rating bars are greyed out…so there is normally no worry about a duplicate being entered.

you’re storing multiple IPs inside a single column value? whoa

you need to fix this first, because this will ~always~ perform poorly – the more rows you have, the slower it will get

Yeah am seeing that now. So what I want is another table for IP’s against ID’s?

Should imagine that might also solve the problem I have now?

yes, it would allow you to declare a UNIQUE constraint, which will eliminate the need for you to run a SELECT first before your INSERT

if you use 2 database statements to get a job done, and find a way to reduce this to 1 database statement, that’s a 100% performance improvement

Thanks for all your help guys, problems all solved and code much improved.

Now onto comments etc! :slight_smile:

so you declared a UNIQUE constraint and got rid of the SELECT?

Now it stores each IP against an item ID, obviously depending on the item that’s being rated. Seems to work well, as I have a couple of other things such as comments coming off of the ID.

Is that the sort of solution you had in mind?

could you show the portion of the code where you store a new rating? does it still include “checking a row for existing data” as mentioned in the title of this thread?

It does still check for the IP, I haven’t looked into it since the initial problem went away.

I’m assuming you mean that the script will attempt to insert to the db but find there’s a duplicate and therefore won’t submit the data…but I need it to check for data to decide whether the user gets to vote at all in the first place?

I thought the only way was –> User to page –> DB Query –> Depending on presence of data, area is greyed out or not greyed out –> If not greyed user votes using a seperate insert query.

The check code is still this:

$voted = mysql_num_rows(mysql_query("SELECT ip FROM $dbname.$tbprefix".ratingsip." WHERE gameid='$id_sent' AND ip='$ip_num' "));

If $voted is 1, the box is greyed out and there is an additional check to ensure he can’t force data in.

I admit there is much, much tidying & improving to be done!

okay, i get it, keep it the way it is

“greyed out or not” actually requires a SELECT, so…

:slight_smile:

Yup just using the output for more than 1 thing - thanks for the help though; my initial post was misleading!