Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2012
    Posts
    6

    Question Unanswered: Select a specific value equal to a calculated value to compare to

    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:

    Code:
    public function display_box()
    	{	
    		 		$user_ip = abs(ip2long($_SERVER['REMOTE_ADDR']));
    		        global $db;
    				
    				$sql = 'SELECT rate_user_id
    				FROM ' . GALLERY_RATES_TABLE . '
    				WHERE rate_image_id = ' . $this->image_id . '
    				AND rate_user_id = '. '".$user_ip."' ;
    			    $user_id = $db->sql_query($sql);
    			
    				if ($user_id == !"")
    				{	
    					return false ;
    				}
                /** more irrelevant code below
                      no errors are produced, however the if statement evaluates
                      to true everytime. This means at a minimum, a value equal to 
                      $user_ip has not been correctly selected in the database
                 */
    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.
    Last edited by mysqlissues; 01-01-12 at 18:10.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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.
    $user_id = $db->sql_query($sql);
    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.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2012
    Posts
    6
    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.

    ______________

    If I were you I'd test to see if any rows were returned.
    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.


    Code:
    SELECT rate_user_id FROM GALLERY_RATES_TABLE WHERE rate_image_id = 40 AND rate_user_id = 12345
    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.

    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
    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?

  4. #4
    Join Date
    Jan 2012
    Posts
    6

    Discover

    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.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    PHP Code:
    $user_ip abs(ip2long($_SERVER['REMOTE_ADDR']));
                    global 
    $db;
                    
                    
    $sql 'SELECT rate_user_id
                    FROM ' 
    GALLERY_RATES_TABLE '
                    WHERE rate_image_id = ' 
    $this->image_id '
                    AND rate_user_id = '
    '".$user_ip."' ;
                    
    $user_id $db->sql_query($sql);
                
                    if (
    $user_id == !"")
                    {    
                        return 
    false ;
                    } 
    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
    PHP Code:
    $user_id $db->sql_query($sql); 
    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'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jan 2012
    Posts
    6
    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

    PHP Code:
    public function display_box()
        {            
                    global 
    $db;
                    
    $get_user_ip $_SERVER['REMOTE_ADDR'];
                            
    $user_ip abs(ip2long($get_user_ip));
                    
                    
    $sql 'SELECT rate_user_id
                    FROM ' 
    GALLERY_RATES_TABLE '
                    WHERE rate_image_id = ' 
    .(int)$this->image_id '
                    AND rate_user_id = '
    . (int)$user_ip ;
                        (int)
    $result $db->sql_query($sql);
                                                    
                    if (
    $result == 61)
                    {    
                        return 
    false ;
                    } 
    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
    Last edited by mysqlissues; 01-03-12 at 14:34.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    PHP Code:
    $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'''falsefalse); 
    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'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jan 2012
    Posts
    6
    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.

    PHP Code:

    error_reporting
    (-1);

    $cnn=@mysql_connect("all correct info - 3 arguements") ;
    if (!
    $cnn)
      {
      die(
    'Could not connect: ' mysql_error());
      }

    $dbr mysql_select_db(databasename$cnn) ;
    if (!
    $dbr)
      {
      die(
    "Can't use test : " mysql_error());
      }

    $sql 'SELECT * FROM `phpbb_gallery_rates` ';

    $sqlr mysql_query($sql,$cnn) ;

    mysql_close($cnn);

    print 
    $sqlr
    Produces this:

    Resource id #2

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

    PHP Code:
    $image_id 41;
    $user_ip abs(ip2long($_SERVER['REMOTE_ADDR']));

    $sql 'SELECT rate_user_id
    FROM ' 
    phpbb_gallery_rates '
    WHERE rate_image_id =  '
    .$image_id .'  
    AND rate_user_id = '
    $user_ip 

    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.
    Last edited by mysqlissues; 01-03-12 at 22:22. Reason: additions

  9. #9
    Join Date
    Jan 2012
    Posts
    6
    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

    PHP Code:
    private function insert_rating$user_id$points$user_ip false)
        {
            global 
    $db$user;
            if (
    $user_id == ANONYMOUS)
            {
                    
    $get_user_ip abs(ip2long($_SERVER['REMOTE_ADDR']));
                    
    $this_image_id $this->image_id ;
                    
    $sql 'SELECT rate_user_id
                    FROM ' 
    GALLERY_RATES_TABLE '
                    WHERE rate_image_id = ' 
    $this_image_id '
                    AND rate_user_id = '
    $get_user_ip ;
                    
    $result $db->sql_query($sql);
                    
    $valuerows mysql_num_rows($result);                                
                    if (
    $valuerows 0)
                    {    
                        return 
    false ;
                    } 
            }
            
            if (
    $user_id == ANONYMOUS)
            {
            
    $user_id abs(ip2long($_SERVER['REMOTE_ADDR']));
            }
            
    $sql_ary = array(
                
    'rate_image_id'    => $this->image_id,
                
    'rate_user_id'    => $user_id,
                
    'rate_user_ip'    => ($user_ip) ? $user_ip $user->ip,
                
    'rate_point'    => $points,
            );
            
    $db->sql_query('INSERT INTO ' GALLERY_RATES_TABLE ' ' $db->sql_build_array('INSERT'$sql_ary));
        } 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •