Info:
I am using a current php version for this.
mysql version 5.0

My experience level is very low with php and extremely low with mysql

Intro:
I have a mysql table that has 4 columns. This includes image id, user id, user ip, rate points. These values are simply to store information for ratings somewhat similar to a poll.

The problem lies when I receive 2 ratings that come from the same IP address. Not only does it produce a mysql duplication error, it is not desired.

Solution:
My goal is to not display the rating box if the same IP address has rated before on the same rate_image id.
I have created a function called $user_ip that stores the ip address in long form as this equal to the rate_user_id. Next, I would like to search the database for the current rate_image_id that the user would be viewing in real time.

Out of the 1 rate_image_id, I'd like to search for a rate_user_id that is equal to the function $user_ip. If this record exists, I would like to return false. If this record does not exist the program will simply continue.

Here is the code:

Concerns:

-I am having a great deal of difficulty understanding the syntax used for working with mysql. All tutorials I have viewed never show the random apostrophes and periods. A syntax error is produced without them.
Understanding there purpose and use would be helpful.

- No values or arguments are sent through the display_box function. However a rate_image_id is needed from the current viewed page. This WHERE rate_image_id line has been copied from another function of the same file where the same idea must be accomplished.
I don't understand what the -> accomplishes in the partial line
$this->image_id
All the other lines have been created by me.

Any help would be greatly appreciated.

SO your SQL is looking for a prexisting rating for this IP address and image
If I were you I'd test to see if any rows were returned.
is trying to assign the value of the SQL query to user_id. its not a construct Ive seen before, but that doesn't mean much as its probably from a more recent MySQL library

here's a list of MySQL functions in PHP
PHP: MySQL Functions - Manual

belt and braces
put a constraint on the table that stores the rating such that the same IP address cannot set more than one rating per item. the constraint woudl take the form of a unique index on the userip address and image. that will stop inserting duplicate ratings

how you will reconcile that with the vast majority of ISP's that allocate IP Addresses dynamically. so a single IP address can legitimately be used by multiple users, and a user may legitimately use more tan one IP address over time.

Just for clarification or in addition if need:
This is a phpbb3 forum software gallery add-on. All these files can be downloaded. I am simply trying to allow a guest to rate images by modding rating.php. (file included in add-on)The developer is not fond of allowing guests to rate, as ip addresses can easily be changed ect. However I need to do it. I can now allow guests to rate however if they try to rate again I get a mysql error as already explained and mentioned.

______________

I'd love to print the values being "searched." However this function only accepts true or false.

If I remove this code and place it on a separate file with the addition of a line to access/logon to the DB, I create errors due to image_id not existing.

If I enter the image ID that exists manually, and print the value. Everything in quotes is printed to the screen with the values of the function.


12345 would be the calculated $user_ip. I have however simplified it.

I could very well be missing code to properly make this search work, found in other files.

From my understanding, I would still need to search the table/database for these values.

What code could I use that would make anything work?

Learned that mysql queries return either a type resource on success, or FALSE on error.

I have learned my original code results in a zero or false for my mysql search/select. This would evaluate the if statement to true every time.

There is a value in the database that should be discovered upon a search. However my mysql search/select is not working and I'm unsure what my code is lacking.

OK so you are having problems with SQL in PHP
one of the problems with PHP in particular is that it can be 'too' friendly and not fall over in a heap if it encounters serious problems
PHP may be hitting errors but continuing executing code
consider (at least whilst in development) changing the error status of the PHP interpreter
error_reporting(E_ALL)

another problem, commnon to all languages, is that you need to be certain that you are sendinbg valid SQL to the SQL engine. that means at least two things, one the SQL will 'compile' / pass the interpreters checks (ie is valid SQL) and that the SQL you are sending actually is what you think it is.

so there's a few steps to do
personally I'm not used to the style you are using
however you define a global variable $db, but from the code you are citing you dont' do anything to that variable until
my guess is you think you are using a class in $db, but at no point do you initialise that class. assuming its a db class wrapper at the very least I'd expect something to set the database, connection, user and password.

I'd suggest you go back to the site where you accquired this code and ask them whats going on.

for me I'd write the SQL access a bit differently
either pass a valid db connection as part of the function call (yes you coudl use a global variable but I hate 'em and they are easy to screw up). OR as this is your own code I'd consider creating my own connection inside this function.

before writing my own fucntion I'd want to investigate this $db class. my guess is that your source code uses this class as a db object. either the global call isn't required in this function (ie the $db class is used elsewhere within the application) or you need to provide the class with some initialisation code.

any interaction with MySQL generates an error code however by using a class unless the class wrapper provides a means of viewing those errors you are stuffed.

a butchered veriosn of my sql access code
$cnn=@mysql_connect(<ipaddressofhost>,<userid>,<pa ssword) or die("Failed to open connection ".mysql_errno().": ".mysql_error()));
$dbr=@mysql_select_db($dbn,$cnn) or die ("Failed to open db:".mysql_errno().": ".mysql_error()));
$sql = "<my sql statement>";
$sqlr=@mysql_query($sql,$cnn) or die ("Failed to open rs:$sql ".mysql_errno().": ".mysql_error()));
if mysql_num_rows($sqlr) > 0
{ //insert code here if we have found rows in the db
} else
{ //insert code here if we haven't
}

in your case you want to return false if rows are found, otherwise return true

I've spent the last 24+ hrs looking in to this.

Thanks for those pointers. I was able to use the php error reporting command to solve another problem I had completely unrelated. That is excellent as I did not know it existed.

I have tried for a very long amount of time to use the code you have provided to produce some sort of mysql search results. Both in the original file and a new file. I added if statements to see if the connection and select_database lines were working properly rather than an or. It seems that it will connect, however I can not access the database. I tried even using the code provided from phpmysqladmin.

I used the following code from what I learned in the rating.php file

image_id == rate_image_id

Where the image_id is equal to 41, for some reason the value of 61 is returned. I do not understand where the value 61 comes from during this search.
Where the image_id is equal to 40, for some reason the value of 60 is returned. Also do not understand how this is pulled.

These values do not exist in the rates table at all. However they seem closely related to the image_id being off by 20. But represent nothing close to what the rate_user_id should be.

Not sure what is going on.

This however is some explanation showing how some of the functions work.

http://wiki.phpbb.com/Using_the_phpBB3.0_DBAL

As said before I think your problem is the $db variable
You define $db as a global variable in this module. the code looks like its expecting $db to be some form of class, I'd guess its going to be an abstraction class for db interaction.

at no point between the declaration of the variable do you instantiate the class variable set it up with parameters that define what database to use, what user id and what password

if you look at the link you provided you at least those
however you aslo need to tell the php interpreter what $db is. from your reference it looks like its a class and the name of the class file to use is mysql in the includes/db/ directory of your PHP server.

either you need to go to a PHPBB forum to get support on this or you need to work out how to merge your code with the existing PHPBB.

I'd suggest you check to see if the variable $db is initialised elsewhere as a global. if so then it will already have your connection details. to find out comment out the global $db line and re run the script


if its not defined elsehwere then you need to initialise the class
include($phpbb_root_path . 'includes/db/' . $dbms . '.' . $phpEx);

$db = new $sql_db();
// we're using bertie and bertiezilla as our example user credentials. You need to fill in your own ;D
$db->sql_connect('localhost', 'bertie', 'bertiezilla', 'phpbb', '', false, false);
where the values for localhost, bertie etc are as described in the reference you gave and should be available to you from your ISP or whoever set up the account
you will need the
the server name OR IP address of the My_SQL server
the name of the database for your system
userid and password for the account

if you know the server name, database name, userid and password then you could create your own database connection for now.. just to get it working

$cnn=@mysql_connect(<ipaddressofhost>,<userid>,<pa ssword) or die("Failed to open connection ".mysql_errno().": ".mysql_error()));
$dbr=@mysql_select_db($dbn,$cnn) or die ("Failed to open db:".mysql_errno().": ".mysql_error()));
$sql = "<my sql statement>";
$sqlr=@mysql_query($sql,$cnn) or die ("Failed to open rs:$sql ".mysql_errno().": ".mysql_error()));
if mysql_num_rows($sqlr) > 0
{ //insert code here if we have found rows in the db
} else
{ //insert code here if we haven't
}

I commented out $db on the original file and this error was given:

Fatal error: Call to a member function sql_query() on a non-object in line (int)$result = $db->sql_query($sql);

Does this mean variable $db is initialised elsewhere as a global?


Edit:________________________

I also on a completely new file have the following code producing the following results.

Produces this:

Resource id #2

I can now also determine the rows for a specific query.


I seem to be getting the correct stuff on a seperate file but can't use this code in the original file.
The code that works great standalone produces:

SQL ERROR [ mysql4 ]

[]

in the original file.

I think you may have just helped me solve my problem.

I changed the location of where I placed the mysql "select" code. I placed it in the same location the mysql "insert" code exists. It returns false if there is a duplicate and adds the rate to the database if not. This code is only ran when the original user ID == ANONYMOUS. If so it is replaced by a user id == to user ip.

Seems to work so far until I run in to the next bug.

Thanks a ton for the help