Wrong Value Generation

azamatali

Hello Everybody,
I am having a serious problem in my hand and i am not able to resolve it..

I have a table in which the primary key is visitor id. I am tryin to generate the visitior id but it is giving me problems.

The code is as follows:

mysql_connect("localhost",$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query="select count(visitor_id) from IWPSD_PERSONAL";
$ctr=mysql_query($query);

if ($ctr==0)

$NextID=1;

else
$NextID=$ctr+1;

The $NextID in all the cases gets a value 4. Now i don't know from where does this number 4 comes, even when there is no records.

If i change the key generation logic , to suppose max of visitor id

mysql_connect("localhost",$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query="SELECT IFNULL(MAX(visitor_id)+1,1) FROM IWPSD_PERSONAL";

$NextID=mysql_query($query);

If i use this $NextID gives me, the value of 0 everytime.

The insert statement that i am using is as follows..

query= "INSERT DELAYED INTO IWPSD_PERSONAL VALUES ('$NextID','$title','$name','$address','$phone','$fax','$email')";
mysql_query($query);

Can anybody tell me where i am wrong?

tsinka

Hi,

I'd suggest to change the primary key field to an auto_increment field. This would increment the id automatically each time you insert a record.

The other problem

$query="select count(visitor_id) AS ctr from IWPSD_PERSONAL"; 
$res=mysql_query($query); 
$row = mysql_fetch_array($res);
$ctr = $row['ctr'];
if ($ctr==0) 

$NextID=1; 

else 
$NextID=$ctr+1;

You need to use mysql_fetch_fow ( or mysql_fetch_array or mysql_fetch_assoc to fetch the row). Your code just uses the result resource id.

Thomas

azamatali

I have tried to use the auto_increment , but it does not solves my problem .........

About the other suggestion, i would look into it and would tell you about it. ...............

But still can anybody tell me why i am gettin the ambigious values. ...

laserlight

But still can anybody tell me why i am gettin the ambigious values. ...

Because you are using the resource ID value, rather than the value you want.

Norman_Graham

Hi Azamatali

I think you could make your life easier:

ID fields should be auto_increment and should not be included in an insert statement. I don't know why you're trying to insert incremental IDs when MySQL does it for you.
Using COUNT() will give you the number of records, not the actual last visitor_id. You need to do one query to find out how many results there are and then get an array of the visitor IDs. You can then assign the value of the last table entry to a variable in the following convoluted and pointless manner:
```
$num_results=mysql_num_rows($query);

$id_array=mysql_fetch_array($query);

$ctr=$id_array[$num_results-1];

$NEXTID=$ctr+1;
```
However, I still think it's all a complete waste of time since using AUTO_INCREMENT automatically generates a new visitor ID every time a new table entry is made. You're trying to light a fire by rubbing sticks together when in fact there's a nice box of matches available.

Finally ... I recommend for your future sanity and your ability to write re-usable code that you stick to some standard syntax, i.e. block capitals for MySQL commands, lower-case only for database, table and field names, lower-case only for variables, block capitals for globals and so on. Debugging code which doesn't work simply because you've referred to a non-existent variable/table/value made non-existent by the fact that you've failed to name it the same way twice is annoying in the extreme - trust me. If you stick to standard syntax procedures, this will happen a lot less often.

HTH

Norm

azamatali

Thanks for all the help that you people have given me.

I have changed by code as tsinka suggested.. So now it is

mysql_connect("localhost",$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query="select count(visitor_id) from IWPSD_PERSONAL";
$row = mysql_fetch_array($res);
$ctr = $row['ctr'];

if ($ctr==0)

$NextID=1;

else
$NextID=$ctr+1;

But now when i insert a record in the database, it everytime gives the same value 1. So it does not solves the problem.

Norman Graham and other people have suggested me to use the auto_increment for the purpose that i have .I have not used auto_increment because i have the visitor_id in this table and at the same time i have to also insert the same visitor id into other table.
so that's why.. i am not using auto_increment.

Regarding the code given by Norman , I am going to use it and let know other people.

laserlight

You need to provide the code you use in insertion.

I have not used auto_increment because i have the visitor_id in this table and at the same time i have to also insert the same visitor id into other table.

Read up on [man]mysql_insert_id/man.

Norman_Graham

Originally posted by azamatali
But now when i insert a record in the database, it everytime gives the same value 1. So it does not solves the problem.

Of course it always gives 1 - the first value in the array is value 0, so your code tells you that $NEXTID must always be 1.

Inserting the visitor ID into another table is no problem. If the tables have identical numbers of results, you just allow both of them to increment simultaneously. If they don't, then you insert the data into the first table and then extract the visitor ID and insert it into the second table as a separate field (not an auto-increment field). However ... if you would explain a bit what it is you are actually trying to do, I bet some people in this forum would be able to suggest a better DB/table structure and a much easier way of achieving your goal.

HTH

Norm