Database INSERT does not take place

Question

My code is as follows:

<?php
include("config.php");

$ip=$_SERVER['REMOTE_ADDR']; 

if($_POST['id'])
{
    $id=$_POST['id'];
    $id = mysql_escape_String($id);

    $ip_sql=mysql_query("select ip_add from Voting_IP where mes_id_fk='$id' and ip_add='$ip'");
    $count=mysql_num_rows($ip_sql);

    if($count==0)
    {
        $sql = "update Messages set up=up+1  where mes_id='$id'";
        mysql_query($sql);

        $sql_in = "insert into Voting_IP (mes_id_fk,ip_add) values ('$id','$ip')";
        mysql_query($sql_in) or die(mysql_error());
        echo "<script>alert('Thanks for the vote');</script>";


    }
    else
    {
        echo "<script>alert('You have already voted');</script>";
    }

    $result=mysql_query("select up from Messages where mes_id='$id'");
    $row=mysql_fetch_array($result);
    $up_value=$row['up'];
    echo "<img src='button.png' width='110' height='90'>";
    echo $up_value;

}
?>

My problem is that the insert process does not take place at all. The script tags echos an alert box. Even the img tag is echoed to the web page. But the insert process does not take place. The config file is fine. Note: This code works on my local machine which has PHP 5.3 but it does not work on the server which has PHP 5.2. Any advice?

Answer 1

The only explanation is that the $count==0 check is false. Try with this workaround:

$ip_sql=mysql_query("select count(*) from Voting_IP where mes_id_fk='$id' and ip_add='$ip'");
$rc=mysql_fetch_row($ip_sql);
$count=$rc[0];

instead of:

$ip_sql=mysql_query("select ip_add from Voting_IP where mes_id_fk='$id' and ip_add='$ip'");
$count=mysql_num_rows($ip_sql);

Answer 2

Have you tried messing around with the quotes? Someone please correct me if I'm wrong, but AFAIK, variables within single quotes don't get expanded in PHP.

$ip_sql=mysql_query("select ip_add from Voting_IP where mes_id_fk='".$id."' and ip_add='".$ip."'");

Answer 3

Looking at the answers and comments, it's time to get old school:

    $sql = "update Messages set up=up+1  where mes_id='$id'";
    echo $sql . '<br>';
    mysql_query($sql);

    $sql_in = "insert into Voting_IP (mes_id_fk,ip_add) values ('$id','$ip')";
    echo $sql_in . '<br>';
    mysql_query($sql_in) or die(mysql_error());
    echo "<script>alert('Thanks for the vote');</script>";

What are you looking for?

You are putting values in for $id and $ip - maybe one of them is empty or contains a character that is making the result "odd" in some way. By taking a good look at the raw query that you are about to execute, you'll see if the variable parts of it are upsetting things.

Answer 4

You're not checking if the first query succeeds:

$ip_sql=mysql_query("select ip_add from Voting_IP where mes_id_fk='$id' and ip_add='$ip'");
$count=mysql_num_rows($ip_sql);

There's no ... or die(mysql_error()) there, but this is most likely not the problem, because if the query was failing, you'd get an "invalid statement handle" type error when you do the mysql_num_rows() call immediately afterwards. As a stylistic tip, I'd suggest rewriting the first query as follows:

SELECT COUNT(*) FROM Voting_IP
WHERE (mes_id_fk = $id) AND (ip_add = $ip)

You're not using any of the retrieved values, just the row count, so there's no point in doing a "select *" type query, which forces the database to do at least SOME processing on all the possible values. If this system scales to very large numbers of votes and IPs, using the count() version will be more efficient.

You say the insert doesn't take place, but don't say which alert() occurs, which means either there's an error with the insert query, or your first query returns 0, and the whole block with the insert query is skipped.

Have you tried manually running the update/insert queries? You're not checking if the update succeeds, as there's no or die(mysql_error()) afterwards. Perhaps there's a foreign key error, a syntax error, etc...

Answer 5

If you're updating an entry based on an ID, then obviously you want to update and insert when the count is NOT zero.... or greater than zero,.. or 1

Simply taking out the == 0, should fix it

if($count)
{
    $sql = "update Messages set up=up+1  where mes_id='$id'";
    mysql_query($sql);

    $sql_in = "insert into Voting_IP (mes_id_fk,ip_add) values ('$id','$ip')";
    mysql_query($sql_in) or die(mysql_error());
    echo "<script>alert('Thanks for the vote');</script>";


}