[Resolved] problems with mysql queries...

[Fking]

i have webmasters sending traffic, using unique ID
i want to make a counter, who stores the hits from each ID, by day

for example, they send the traffic to, mydomain.com/partner.php?id=555

and in partner.php i have this code,

<?php
session_start();include 'partners/db.php'; //connect to db

$num = 0;
$day = date(l) ;
$sql_id_check = mysql_query("SELECT id FROM fking_grad WHERE id='$id'"); //try and select an id

$id_check = mysql_num_rows($sql_id_check); //turn the data into info

if ($id_check > 0) {  // if the person has an id (has to be bigger than 0)
mysql_query("UPDATE fking_grad SET $day=($id + 1) WHERE id='$id'");
} else {
mysql_query("INSERT INTO fking_grad (monday, tuesday, wednesday, thursday, friday)
                VALUES('$num', '$num', '$num', '$num', '$num',") or die (mysql_error());
}
?>

when execute it i got
"Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/xxxx/public_html/xxxxxxxxxx.com/partner.php on line 9
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2"

in the mysql i have columns for the id, and the days of the week

[devinemke]

sounds like register_globals is OFF. do this before your query:

$id = $_GET['id'];

[Fking]

getting the same error

[tekky]

maybe this...

mysql_query("INSERT INTO fking_grad (monday, tuesday, wednesday, thursday, friday)
VALUES('$num', '$num', '$num', '$num', '$num',")

should be this....

mysql_query("INSERT INTO fking_grad (monday, tuesday, wednesday, thursday, friday)
VALUES('$num', '$num', '$num', '$num', '$num'")

[Fking]

still the same error

btw the problem line 9 is this,
$id_check = mysql_num_rows($sql_id_check);

[tekky]

stupid question... but have you done mysql_connect?!?!?

(since you dont show it I have to ask now)

[Fking]

Originally posted by tekky
stupid question... but have you done mysql_connect?!?!?

(since you dont show it I have to ask now)

yes, the code is in db.php,

<? $connection = mysql_connect("localhost","myuser","mypass")
    or die ("Couldn't connect to server.");
$db = mysql_select_db("fking_grad", $connection)
    or die("Couldn't select database.");
?>

when i load only db.php, it's blank, so i guess it's ok?

[Fking]

the script isn't written by me, but i assume that

$id_check = mysql_num_rows($sql_id_check);

checks if there are any rows under the column "id" , right?

but i don't need that, i need to look for a row under the column id, which has the $id in it, ($id is coming from the url, and everytimie is different)

and if there isn't row, with value $id, to create it, if there is already such, row, to add 1.

so my questions is, how to search for row with concrete value, under the column "id"

[tekky]

if $id comes from the URL then try doing this at the top of your page...

echo '<pre>' .print_r($_GET). '</pre>';
and see what that outputs...

[Fking]

Originally posted by tekky
if $id comes from the URL then try doing this at the top of your page...

echo '<pre>' .print_r($_GET). '</pre>';
and see what that outputs...

the url i load is mydomain.com/partner.php?id=553 and it outputs...

Array ( [id] => 553 )
1

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/fking/public_html/mydomain.com/partner.php on line 9
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2

[tsinka]

Hi,

what do you get with this code:

<?php 
session_start();
include 'partners/db.php';

$num = 0; 
$day = date("l") ; 
$id_check = 0;
$id = 0;

if (isset($_GET['id']) && is_numeric($_GET['id']) && $_GET['id']>0) {
 $id = $_GET['id'];
 $query = "SELECT id FROM fking_grad WHERE id='$id'";
  echo "The SELECT query: $query<br>\n";
  $sql_id_check = mysql_query($query) or die("SQL error: ".mysql_error());
  $id_check = mysql_num_rows($sql_id_check); 
} else {
  die ("Error: no valid id!");
}

if ($id_check > 0) {
  $query = "UPDATE fking_grad SET $day=($id + 1) WHERE id='$id'";
  echo "The UPDATE query: $query<br>\n";
  mysql_query($query) or die("Update eror: ".mysql_error()); 
} else { 
  $query = "INSERT INTO fking_grad (monday, tuesday, wednesday, thursday, friday) VALUES ('$num', '$num', '$num', '$num', '$num')";
  echo "The INSERT Query: $query<br>\n";
mysql_query($query) or die ("Insert error: ".mysql_error()); 
} 
?> 

The insert query has a syntax error because of the , character at the end of the query. Replace it by ).

Another question:

shouldn't it be

SET $day=($day + 1)

instead of

SET $day=($id + 1) ?

Thomas

[Fking]

Originally posted by tsinka
Hi,

what do you get with this code:

<?php 
session_start();
include 'partners/db.php';

$num = 0; 
$day = date("l") ; 
$id_check = 0;
$id = 0;

if (isset($_GET['id']) && is_numeric($_GET['id']) && $_GET['id']>0) {
 $id = $_GET['id'];
 $query = "SELECT id FROM fking_grad WHERE id='$id'";
  echo "The SELECT query: $query<br>\n";
  $sql_id_check = mysql_query($query) or die("SQL error: ".mysql_error());
  $id_check = mysql_num_rows($sql_id_check); 
} else {
  die ("Error: no valid id!");
}

if ($id_check > 0) {
  $query = "UPDATE fking_grad SET $day=($id + 1) WHERE id='$id'";
  echo "The UPDATE query: $query<br>\n";
  mysql_query($query) or die("Update eror: ".mysql_error()); 
} else { 
  $query = "INSERT INTO fking_grad (monday, tuesday, wednesday, thursday, friday) VALUES ('$num', '$num', '$num', '$num', '$num')";
  echo "The INSERT Query: $query<br>\n";
mysql_query($query) or die ("Insert error: ".mysql_error()); 
} 
?> 

The insert query has a syntax error because of the , character at the end of the query. Replace it by ).

Another question:

shouldn't it be

SET $day=($day + 1)

instead of

SET $day=($id + 1) ?

Thomas [/B]

thanks, a lot for the code
i got this
"The SELECT query: SELECT id FROM fking_grad WHERE id='555'
SQL error: Table 'fking_grad.fking_grad' doesn't exist"

about your second question....
in the column for the current day it has to enter the updated value of the ID, +1
so i guess it has to be as it is.....

[tsinka]

Hi,

the message tells us that there is no such table fking_grad in database fking_grad. The table name is invalid.

Thomas

[Fking]

Originally posted by tsinka
Hi,

the message tells us that there is no such table fking_grad in database fking_grad. The table name is invalid.

Thomas

yes, because the table name is hits, anyway, i changed it and now works, this is the out put,
"
The SELECT query: SELECT id FROM hits WHERE id='4322'
The INSERT Query: INSERT INTO hits (monday, tuesday, wednesday, thursday, friday, saturday, sunday) VALUES ('0', '0', '0', '0', '0', '0', '0')
"

but when i execute it again, it shows
"The SELECT query: SELECT id FROM hits WHERE id='4322'
The INSERT Query: INSERT INTO hits (monday, tuesday, wednesday, thursday, friday, saturday, sunday) VALUES ('0', '0', '0', '0', '0', '0', '0')
Insert error: Duplicate entry '0' for key 1"

i have to make few changes of the code, because i want, if the ID is used for first time, to save it, to save the hit for the day, and the next time when this ID is used again, to update the value of the hits for the current day, or if it's the next day, to create a value for it too...

and this to happens with all IDs, used....
but i'm not sure how to do it

[tsinka]

Hi,

please post the table layout of hits.
Did you create unique indexes on the monday...saturday columns or is the column id a unique key but not an autoincrement. If it is a unique key you might need to change the insert query so that it also inserts the id.

Thomas

[Fking]

id is int(255)
the rest are, varchar(255)

i remember that tried, to make id autoincrement.....but there was a problem, i'm not sure what, and the guy who was helping me (thanks Richard :> ) suggested to make it int

oh, it looks too complicated for my level of knowledge.....i hope the attached screetshot helps

[tsinka]

Hi,

please post the index information (this is displayed on the overview page if you klick on the table name in the left menu).

You might need to change the INSERT query:

INSERT INTO hits (id,monday, tuesday, wednesday, thursday, friday, saturday, sunday) VALUES ('$id','$num', '$num', '$num', '$num', '$num', '$num', '$num')

Thomas

[Fking]

mm, this?

[tsinka]

Exactly, thanks 🙂

The column id is the primary key of that table and thus unique. You can't insert the same id in two different rows. Did you try the insert query of my last post ?

This should solve the problem with duplicate id values since the code checks if an id exists already.

Thomas

[Fking]

hehe, works!
thanks tsinka

i'll have to touch only few more things, and will work exactly as i need to, but will try on my own,
only tell me, how i can get the value from concrete column and row, i need them to echo, on the stats page, the hits for every day

i owe u big favour tsinka, if i can do something for you lmk (i owe few adult paysites, if interested for free access contact me :> )