Problem with updating db

Ali_Baba2

Heya,

This is my first post and i am trying to learn php and mysql by making some simple scripts.

First of all, let me explain what i try to do here:
I have a script that will show from what country the visitor is.

I thought it would be nice for a next project to take this variable to a database and add a counter to it.

Show when a visitor is on my site the script tell me he is for example for the USA.

Put this country into the database and add a +1 to the counter.
if country already exist then add only a + 1 to the counter.

its kind of a statform, but to me its only a learning object to manipulate data from the database.

I use Mysql version 4.0.2a and PHP 4

this is the script.

include ("db_connect.inc");
// select the specific database name we want to access.
$dbname='counter';
if (!mysql_select_db($dbname)) die(sql_error());
echo ("database selection succes<br>");

// selecting table and retrieving results
$q = "SELECT * FROM country_count WHERE country_name, country_count=" .$country_name;
$result = mysql_db_query($dbname,$q);
echo ("database query succes<br>");


if ($result)
{
// if countryname already present then set counter plus one for that country
$country_count = 'country_count' +1

$sql = mysql_db_query("country_count", "UPDATE country_count SET country_count", $dbname);
$result = mysql_db_query($sql);
while($row = mysql_fetch_row($result))
echo $row[0]; 

echo ("updated country counter by one<br>");
echo ("$country_name<br>");
echo ("$country_count<br>");
exit;
}

// if country is not in database table insert it and setting counter to one

echo ("country not in database");
country_count +1;
$country_name = 'country_name';

$string = "INSERT INTO COUNTRY_COUNT (country_name, country_count) VALUES('$country_name', '$country_count')"; 
print $string . "<br>"; 
$query = mysql_query($string) or die(mysql_error());

echo ("$country_name - $country_count");
echo("exit");
exit;

I also want to ask what is the difference between mysql_query and mysql_db_query ?? the first one dont work with me..is this oke?

rachel2004

I didn't proofread your code in-depth, but here are some answers:

mysql_db_query is deprecated. Use mysql_query() in place of it in your code.
sql_error() is not a PHP function. If that's not a custom function, then you should use mysql_error().
You cannot refer to the columns returned form a query as simply 'columnName'. You need to use something like:

$row['columnName']

$row[0], $row[1], etc.

Again, I don't have the time to look at it further, so maybe someone else will spot out things I didn't list.

Ali_Baba2

well, it seems that the version of mysql i'm running doesnt understand mysql_query().

I get an error like "not a valid link resource"

anybody a thought about it??

rachel2004

It maybe necessary for you to specify your connection resource with the use of the function.

$result = mysql_query($sql, $connection)
    or die("Couldn't execute query: ".mysql_error());

You need to look in your file db_connect.inc and see what your connection resource is. Put that in place of "$connection"

Ali_Baba2

Man Man Man, I dont get it.

Can someone update me a bit....
mysql_db_query is no longer accepted however its the only query that works. I have mysql version 4.

<?php 
include ("country.php");
// set your infomation.
$dbhost='localhost';
$dbusername=xxx;
$dbuserpass=xxx;

// connect to the mysql database server.
$link_id = mysql_connect ($dbhost, $dbusername, $dbuserpass);

// select the specific database name we want to access.
$dbname='counter';
if (!mysql_select_db($dbname)) die(mysql_error);
echo ("database selection succes<br>");

// selecting table and retrieving results
$result = $msql_query = "SELECT * FROM user_country WHERE country_name, country_count=" .$country_name;
if(!mysql_query($result)) die(mysql_error());

echo ("database query succes<br>");


if ($result)
{
// if countryname already present then set counter plus one for that country
$result[$country_count]++;
$sql = mysql_query("UPDATE user_country SET country_count = '$country_count'", $dbname);
$result = mysql_query($sql);
while($row = mysql_fetch_row($result))
echo $row[0];
echo ("updated country counter by one<br>");

exit;
}

// if country is not in database table insert it and setting counter to one

echo ("country not in database<br>");
$country_name = 'country_name';
$country_count = '1';
$sql = mysql_query("INSERT into user_country (country_name, country_count) VALUES ('$country_name', '$country_count'",$dbname);
$result = mysql_query($sql);
while($row = mysql_fetch_row($result))
echo $row[0];

echo("exit");
exit;

?>

now there is a syntax error....can someone tell me the right syntax, because I cant tell it from all outdated tutorials.

brockn

// selecting table and retrieving results
$result = $msql_query = "SELECT * FROM user_country WHERE country_name, country_count=" .$country_name;

I am 99% sure that syntax does not exist. mysql_query is not a variable its a function and as far as I know you can never "set = something = to something else".

// selecting table and retrieving results
$result = msql_query("SELECT * FROM user_country WHERE country_name, country_count=" .$country_name);

After a brief look over your code it appears as though you have more than just one syntax error. Keep in my that mysql_query RETURNS a result, but you have to set that value to a variable.

$result = mysql_query("THE SQL STATEMENT GOES HERE");

rachel2004

Try using $link_id:

if (!mysql_select_db($dbname, $link_id))
{
    die(mysql_error());
}
echo ("database selection succes<br>");

As for your query, what tutorial told you to write it the way you have it:

$result = $msql_query = "SELECT * FROM user_country WHERE country_name, country_count=" .$country_name;

try this instead:

$result = mysql_query("SELECT * FROM user_country WHERE country_name, country_count='$country_name'";

Again, there may be other errors, but start with those changes.

Ali_Baba2

Thank you for sticking a helping hand.
You were right about the syntax. It helped me alot.
after alot of error and searching through the forum i was able to resolve alot.

Now I am at a point where no more errors occor and you guessed it. Its not working as i thought it would be.

It seems not inserting the data into the database.
I added a number of echo's to reveal its content over the script.

here is the content of the echo's.

database selection succes
database query succes
country not in database
countryname: NETHERLANDS
country count: 1
databasename: counter
Error :Query was empty

The netherlands is ok, because thats where i am.
database is also good

but the query error is new to me and i understand from it, thats it is empty (duhhhh!!) alright alright. so logical that the inserting went wrong somewhere.

the script sofar.

<?php 
include ("country.php");
// set your infomation.
$dbhost='localhost';
$dbusername=xxx;
$dbuserpass=xxx;

// connect to the mysql database server.
$link_id = mysql_connect ($dbhost, $dbusername, $dbuserpass);

// select the specific database name we want to access.
$dbname='counter';
if (!mysql_select_db($dbname)) die(mysql_error);
echo ("database selection succes<br>");

// selecting table and retrieving results
$result = mysql_query("SELECT * FROM user_country WHERE country_name, country_count=" .$country_name);

//if(!mysql_query($result)) die(mysql_error());

echo ("database query succes<br>");


if ($result)
{
// if countryname already present then set counter plus one for that country
$result[$country_count]++;
$sql = mysql_query("UPDATE user_country SET country_count = '$country_count'", $dbname);
$result = mysql_query($sql);
while($row = mysql_fetch_row($result))
echo $row[0];
echo $row[1];
echo ("updated country counter by one<br>");
echo ("$country_name<br>");
echo ("$country_count<br>");
exit;
}

// if country is not in database table insert it and setting counter to one

echo ("country not in database<br>");
echo ("countryname: $country_name<br>");
$country_count = '1';
echo ("country count: $country_count<br>");
echo ("databasename: $dbname<br>");
$sql = mysql_query("INSERT into user_country (country_name, country_count) VALUES ('$country_name', '$country_count');");
$result = mysql_query($sql) or die('Error :' . mysql_error().'<br>'.$sql); 


while($row =  mysql_fetch_row($result))
echo $row[0];

echo("exit");
exit;

?>

brockn

When it errors out make it print $sql and see what the actual $sql variable looks like.

Also I did not see what $country_name was get set? (I looked briefly)

rachel2004

I must be staring at my monitor too long that I didn't catch a mistake with your query. Instead of this:

$result = mysql_query("SELECT * FROM user_country WHERE country_name, country_count=" .$country_name);

try this:

$sql = "SELECT *
            FROM user_country
            WHERE country_name = '$country_name'";

$result = mysql_query($sql, $link_id)
    or die("Problem with query: ".mysql_error());

If you want to use country_count in your query, you need to specify a quantity (as I do not know what it should be) and use AND:

SELECT * FROM user_country WHERE country_name = '$country_name' AND country_count= '10';

Regarding your while loop:

while($row = mysql_fetch_row($result))
echo $row[0];
echo $row[1];
echo ("updated country counter by one<br>");
echo ("$country_name<br>");
echo ("$country_count<br>");
exit;

... only the first echo statement will be repeated while the boolean is TRUE in your while loop. You can use curly braces { } if you had wanted more than one line to repeat for as long as the while statement was true.

Also, the following is not correct:

$sql = mysql_query("INSERT into user_country (country_name, country_count) VALUES ('$country_name', '$country_count');");
$result = mysql_query($sql) or die('Error :' . mysql_error().'<br>'.$sql);

Try changing it to this:

$sql = "INSERT into user_country (country_name, country_count) VALUES ('$country_name', '$country_count')";
$result = mysql_query($sql) or die('Error :' . mysql_error().'<br>'.$sql);

Notice that I removed the function mysql_query in the $sql statement. It is not needed since you are creating a variable called $result to hold the results of you mysql_query function. Also, notice that I took out the semicolon ( ; ) from within the INSERT statement. It needs to come after the closing quote.

Again, my usual disclaimer: I did not look too deep at your code nor did I test it... so if this still doesn't work, let me know. But before that, understand why I made changes and see if similar problems exist elsewhere in this script or in your other scripts.

Ali_Baba2

Thank you for being so helpfull and im still learning it.
your contribution is a big help.

but here is another part I dont understand

// selecting table and retrieving results
$sql = "SELECT * FROM user_country WHERE country_name = '$country_name'"; 
$result = mysql_query($sql, $link_id) 
    or die("Problem with query: ".mysql_error());

print $sql;
print $result;

the print $sql says: $sql = "SELECT * FROM user_country WHERE country_name = 'Netherlands'";

this is looking good to me.

This means he is looking in the DB for the name Netherlands

but the print $result says: Resource id#4 ???
what does this mean??, do I need a line of code to convert is back to Netherlands??

If you want to use country_count in your query, you need to specify a quantity (as I do not know what it should be) and use AND:

Well ,what i want is...find the country in the DB...IF found then add a plus 1 to the counter of that country

I have a table with two colums. one for the countries and one for the counters.

country.php is looking by IP-adres from what country the visitor is.
and I want to save the number of visits from a country.

Like : Netherlands : 45
USA : 10
germany : 1

somthing like that.

So basicly I need the country_count lookup too or does take this one with it in the select procedure??

hmmm after reading it back i can guess your guestions about this.

TABLE : user_country

country_name----------country_count

|---Netherlands-|--------45---------- |

So when the select part search for $country_name in the DB
does it retrieve the 45 also??
because how do I add a +1 to the counter of the netherlands so it reads the next time 46?

so i need something like : ( i know this is wrong)

$sql = "UPDATE user_country SET country_count = '$country_count+1'";
[\php]

but only if the country has been found in the DB otherwise it
needs to add the country (for example Belgium) to the DB
with a new counter of 1

so the table will look like:
country_name----------country_count
---------------------------------------------
|--Netherlands---|------45-------------|
|--Belgium-------- |-------1------------- |
---------------------------------------------

I hope i cleared something up.

brockn

I am short on time so I only read the first part of your post.

BUT you cannot print $result's you need to use $row = mysql_fetch_array($result). Then column 1 is $row[0] column 2 is $row[1].

Ali_Baba2

Well, after hours and hours staring and trying. I've started a new page. searched for some counters in this forum and found some usefull to me.

Now i think i got close to the solution but i need you to check since i cant check if it works with other countries.

or can someone tell from the code it will make another field with a counter??

<?php 
include ("country.php");
// set your infomation.
$dbhost='localhost';
$dbusername=xxx;
$dbuserpass=xxx;
$dbname='counter';


// connect to the mysql database server.
$link_id = mysql_connect ($dbhost, $dbusername, $dbuserpass);

// select the specific database name we want to access.
if (!mysql_select_db($dbname)) die(mysql_error);
echo ("database selection succes<br>");

$sql = "SELECT * FROM user_country WHERE country_name = '$country_name'"; 
$result = mysql_query($sql, $link_id) or die("Problem with query: ".mysql_error());
$row = mysql_fetch_row($result);

if ($row == $country_name)
{
$hits = 1;
$sql = "INSERT into user_country (country_name, hits) VALUES ('$country_name', '$hits')";
$result = mysql_query($sql, $link_id) or die("Problem with query: ".mysql_error());
}
else if ($row !== $country_name)
{
mysql_query("Update user_country SET hits=hits+1 WHERE country_name='$country_name'");    //update DB to reflect new view 
$sql1 = "SELECT * FROM user_country WHERE country_name = '$country_name'";  
$result = mysql_query($sql1, $link_id);
$row = mysql_fetch_array($result);
echo ("$row[0]<br>");
echo ("$row[1]<br>");
}

?>

the first row gives now the country and the second one gives the count.

can someone from outside the netherlands check if its counting
for them aswell ??

link

rachel2004

Your link didn't work for me. Maybe someone else will have luck, or maybe check if it is working.

I made some small modifications to make it easier for me to read the code. However, you should not see a performance hit if you use what I have instead of what you have. I also added some commentss into the body of the code where I was unsure what you were attempting to echo.

For your insert into the "hits" column, it didn't look right to me, so I modified it. If my code for that part isn't correct, then by all means, just change it back to what you had. Here's the code:

$row = mysql_fetch_array($result, MYSQL_ASSOC);

if ($row['country_name'] == $country_name)
{
    $hits = $row['hits'] + 1;
    $sql = "INSERT into user_country 
            (country_name, hits) 
            VALUES 
            ('$country_name', '$hits')";
    $result = mysql_query($sql, $link_id) 
        or die("Problem with query: ".mysql_error());
}
else if ($row['country_name'] !== $country_name)
{
    mysql_query("Update user_country SET hits=hits+1 WHERE country_name='$country_name'");    //update DB to reflect new view
    $sql1 = "SELECT * FROM user_country WHERE country_name = '$country_name'";  

    $result = mysql_query($sql1, $link_id);
    $row = mysql_fetch_array($result);
    echo ("$row[0]<br>"); // not sure what $row[0] is
    echo ("$row[1]<br>"); // not sure what $row[1] is
}
else
{
    die("<p>Country name could not be identified in the database.</p>");
}

Ali_Baba2

thank you rachel2004

the code is still working only this line:

if ($row['country_name'] == $country_name)

he doesnt reconize 'country_name' because it is undefined.
any idea what to do about it.

those two echo's below just printing the result of the query

like $row[0] = Netherlands
and $row[1] = 34 (number of hits from that country)

the link may work now (firewall was blocking port 80)

rachel2004

From looking at your code, you should have a column in your table called country_name... did you follow my code exactly? You'll notice that I am using mysql_fetch_array() instead of mysql_fetch_row(). So, we just need a little bit of results checking. Change these lines:

if ($row['country_name'] == $country_name)

else if ($row['country_name'] !== $country_name)

... to these:

if (!empty($row['country_name']) && $row['country_name'] == $country_name)

else if (!empty($row['country_name']) && $row['country_name'] !== $country_name)

If this doesn't work, then I have some other changes to make, but I didn't want to modify your existing code too much. Please let me know how it looks.

rachel2004

I looked over your code again and noticed another area that will need to change:

echo ("$row[0]<br>"); 
echo ("$row[1]<br>");

If you want to continue using $row[0] and $row[1] instead of $row['country_name'] and $row['hits'], you will need to change my use of the mysql_fetch_array function from:

$row = mysql_fetch_array($result, MYSQL_ASSOC);

to this:

$row = mysql_fetch_array($result);

Ali_Baba2

I tried your suggestions but somehow MYSQL_ASSOC isnt understood.

script sofar:

<?php 
include ("country.php");
// set your infomation.
$dbhost='localhost';
$dbusername=xxx;
$dbuserpass=xxx;
$dbname='counter';


// connect to the mysql database server.
$link_id = mysql_connect ($dbhost, $dbusername, $dbuserpass);

// select the specific database name we want to access.
if (!mysql_select_db($dbname)) die(mysql_error);
echo ("database selection succes<br>");

$sql = "SELECT * FROM user_country WHERE country_name = '$country_name'"; 
$result = mysql_query($sql, $link_id) or die("Problem with query: ".mysql_error());
$row = mysql_fetch_array($result);

if ($row[0] == '$country_name')
{
$hits = 1;
$sql = "INSERT into user_country (country_name, hits) VALUES ('$country_name', '$hits')";
$result = mysql_query($sql, $link_id) or die("Problem with query: ".mysql_error());
}
else if ($row[0] !== '$country_name')
{
mysql_query("Update user_country SET hits=hits+1 WHERE country_name='$country_name'");    //update DB to reflect new view 
$sql1 = "SELECT * FROM user_country WHERE country_name = '$country_name'";  
$result = mysql_query($sql1, $link_id);
$row = mysql_fetch_array($result);
print $row[0];
print $row[1];
}

?>

[URL=http://82.161.32.98\useronline3.php]try link[/URL]

Ali_Baba2

I tried your suggestions but somehow MYSQL_ASSOC isnt understood.

script sofar:

<?php 
include ("country.php");
// set your infomation.
$dbhost='localhost';
$dbusername=xxx;
$dbuserpass=xxx;
$dbname='counter';


// connect to the mysql database server.
$link_id = mysql_connect ($dbhost, $dbusername, $dbuserpass);

// select the specific database name we want to access.
if (!mysql_select_db($dbname)) die(mysql_error);
echo ("database selection succes<br>");

$sql = "SELECT * FROM user_country WHERE country_name = '$country_name'"; 
$result = mysql_query($sql, $link_id) or die("Problem with query: ".mysql_error());
$row = mysql_fetch_array($result);

if ($row[0] == '$country_name')
{
$hits = 1;
$sql = "INSERT into user_country (country_name, hits) VALUES ('$country_name', '$hits')";
$result = mysql_query($sql, $link_id) or die("Problem with query: ".mysql_error());
}
else if ($row[0] !== '$country_name')
{
mysql_query("Update user_country SET hits=hits+1 WHERE country_name='$country_name'");    //update DB to reflect new view 
$sql1 = "SELECT * FROM user_country WHERE country_name = '$country_name'";  
$result = mysql_query($sql1, $link_id);
$row = mysql_fetch_array($result);
print $row[0];
print $row[1];
}

?>

[URL=http://82.161.32.98\useronline3.php]try link[/URL]

rachel2004

What problem are you still having at this point? You didn't specify. If you are receiving error messages, please list the error messages so we can determine if the problem is a syntax error or a problem with our SQL statements.

Also, could you list the columns and data type of each columns in your table "user_country".

In your link, you have a backslash when you need a forward slash.