Problem With Function

mike.fro · 07-21-2008, 01:23 AM

Hello All,
I'm wanting to see if anyone here can help me out. I'm building a small cms for a client that basically takes the google pagerank, technorati and alexa ranking and places them in a database. I then pull them from the db and display them. I'm having troubles updating the database. I keep getting the error "Unknown column 'www.michaelfroseth.com' in 'where clause'" (MichaelFrosethdotCom is one of 3 url's currently in the database, but it is the first one so i'm assuming that's why it's displaying that specific url).

Here is my PHP code for the function I am using:

PHP Code:


			
function addPageRank() {
    
    include('config.php');
    $sql = "SELECT * FROM `url`";
    $result = mysql_query($sql) or die(mysql_error());
    
    while($row = mysql_fetch_array($result)) {
    extract($row);
    
    $prURL = $row[url];
    $pagerankNum = pagerank($prURL);
    $alexa = "1000";
    $technorati ="50000";
    
    $sql2 = "UPDATE url SET pagerank=$pagerankNum, alexa=$alexa, technorati=$technorati  WHERE url=$prURL";
    $result2 = mysql_query($sql2) or die(mysql_error());
    
    }
    
    if(!$result2) {
    
    //show mySQL error
    
    mysql_error();
    
    } else {
    
    //successfully updated database with rankings
    
    echo("Success!");
    
    }
}

The technorati and alexa variables are pre-defined as I haven't created those functions yet. Any help would be greatly appreciated.

Enfernikus · 07-21-2008, 01:40 AM

have you tried just placing the information within backticks (I know I know, mysql specific SQL and such).

mike.fro · 07-21-2008, 01:48 AM

Quote:

Originally Posted by Enfernikus

have you tried just placing the information within backticks (I know I know, mysql specific SQL and such).

I apologize, could you please elaborate? I'm not quite sure what you mean by backticks.

Thank!

mike.fro · 07-21-2008, 02:00 AM

Alright, got it to work everyone. Had to change "WHERE url=$prURL" to "WHERE id=$row[id]"

That worked and updated successfully.

Thanks everyone!

Alex · 07-21-2008, 04:07 PM

Backticks are ` (Press the button to the left of 1). You should use them for escaping table & column names.
i.e.

Code:

INSERT INTO `atable`(`name`,`content`)....

You should use apostrophes for escaping values. i.e.

Code:

INSERT INTO `atable`(`name`,`content`) VALUES('The title value', 'The content')

If one or more of your values are strings, then you should probably escape them before using them in a query. Escaping them involves adding backslashes to characters which mysql could interpret as a command / operator.

Code:

INSERT INTO `atable`(`name`,`content`) VALUES('The title value', 'I'm the king of England')

That query will fail because of the apostrophe in I'm the kind of England. Mysql treats it as the end of the string, and then gets very confused when it sees

Code:

m the kind of England')

as it doesn't know how to interpret it.

To escape a string, use the mysql_real_escape function. It will automatically go through the string and escape (place a backslash in front of) any characters which could confuse MYSQL. The backslash will tell mysql to ignore the following character.

The backslashes which are used to escape characters such as ' will not be stored in the table.