increasing column value by one (if exist)

[2millionways]

I am trying to increase value of column (step_status#) by one if for example step_status1, 2 and so on exist

 

/* check if column step_status exist if does add 1 */

$sql="SELECT * FROM heck_tickets";
$result=@mysql_query($sql);


if ($result == $columname)

{
for($k=0; $k=empty($k); $k++);

}
$columname="step_status{$k}";

$sql = "alter table hesk_tickets add column $columname enum ('0','1')";

 

Thank you for help.

[tivrfoa]

I am trying to increase value of column (step_status#) by one if for example step_status1, 2 and so on exist

 

/* check if column step_status exist if does add 1 */

$sql="SELECT * FROM heck_tickets";
$result=@mysql_query($sql);


if ($result == $columname)

{
for($k=0; $k=empty($k); $k++);

}
$columname="step_status{$k}";

$sql = "alter table hesk_tickets add column $columname enum ('0','1')";

 

Thank you for help.

are you trying to increase the value of the column or add columns to the table hesk_tickets?

what's the schema of the table hesk_tickets (what columns does it has?)?

[2millionways]

Thanks for reply! :-) I am trying to add a new column to heck_tickets. There are probably 15 different columns in heck_tickets. But it does not matter where it will at them. So if step_status1 exist the script will add step_status2 and so on.

 

Thanks again for help.

[Mark Baker]

Thanks for reply! :-) I am trying to add a new column to heck_tickets. There are probably 15 different columns in heck_tickets. But it does not matter where it will at them. So if step_status1 exist the script will add step_status2 and so on.

Surely you'd be better creating a new table for ticket_step_status and adding rows to that rather than dynamically adding new columns to your tickets table

[2millionways]

Probably .. anyway because I know little about php this makes my life easier as I want to echo all step_staus together with other value from that row. So it would be the easiest way (that what I think). Anyway if it is possible what I wanted to achieve I would really appreciate your help.

[2millionways]

If you want to know detail: every new ticket with reference number would have number of steps that apply to it. Steps are added to own table but at the same time I want them to be created in hesk_tickets table where I can define whether each step is accomplished or not accomplished.

 

My head is hurting me now  ???

 

If you could tell me how I can increase columns with increased number for now I always get with the above code:

 

MySQL said:
Duplicate column name 'step_status'

 

 

[premiso]

Your code is wrong.

 

/* check if column step_status exist if does add 1 */

$sql="SELECT * FROM heck_tickets";
$result=mysql_query($sql);

if ($row = mysql_fetch_array($result)) {
       $bTrue = true;
        $i=1;
        while ($bTrue) {
             if (!isset($row['step_status' . $i])) {
                 $bTrue = false;
                 $columname = 'step_status' . $i;
             }else {
                 $i++;
             }
        }
       $sql = "alter table hesk_tickets add column $columname enum ('0','1')";
}

 

Would be the proper way. However, this is not good on the server and each time you add a new column to the table, it adds it to each row. So yea. I would do a separate table as suggested as it will save you time, efficiency and in the long run it will make your life a TON easier.

[2millionways]

Thanks for advice. I have tried the above code. It adds first columns step_status1, but then it returns:

 

Can't execute SQL: alter table hesk_tickets add column step_status1 enum ('0','1')

 

MySQL said:

Duplicate column name 'step_status1'

 

[premiso]

$sql="SELECT * FROM hesk_tickets";

 

You had a typo in your first SQL statement. heck vs hesk.

[2millionways]

Table name is hesk_, if the name was incorrect it would return different error ..

[premiso]

It was a typo I just saw at the top of the script I posted you. If you fixed before you ran it, great than that is not the problem. However if you did not and you do have a table named "heck" then yea that is the problem.

 

Other than that I looked over my code, and there are no issues as far as I can tell. For some reason it is showing a non-valid column name as being set.