number format on auto integ

General PHP Help
#1

Hi everyone, I have a script that takes the last number and adds 1 digit to it. The problem I have is with the format of the numbering, my number system is 0001 etc but when my script runs it just generates the number 2 and not 0002. Does anyone know to overcome this please?

my current code is:-
[php]<?php
include(’…/…/includes/mysqli_connect.php’);

if (mysqli_connect_errno($db)) {
trigger_error('Database connection failed: ’ . mysqli_connect_error(), E_USER_ERROR);
}

$client_proj_no = (“SELECT max(client_proj_no)+1 as client_proj_no FROM fms_tbl_projects WHERE client_id=’{$_SESSION[‘client_id_of_user’]}’”);
$result = mysqli_query($db, $client_proj_no) or trigger_error("Query Failed! SQL: $query - Error: ". mysqli_error($db), E_USER_ERROR);

if($result) {
while($row = mysqli_fetch_assoc($result)) {

			echo "<input name='client_proj_no' class='form-control' value ='".$row['client_proj_no']."' readonly>" ;
}

}

?>[/php]

thanks in advance

#2

Did you actually try looking for how to solve this, or did you just post the question? Many times in development, you need to do due diligence in researching first.

#3

Not really a PHP solution, but an MySQL solution. Maybe this will help?
[php]SELECT "lpad(client_proj-no, 3, ‘0’) as client_proj_no…[/php]

There’s a way to increment automatically the number into MySQL as well, just do a google search an you’ll find it.

#4

Another thought on this, Dan, is how you have it set up in your database.

Numbers in your MySQL database can be set as integer or decimal with a set number of digits.
I seldom add numbers to values inside of SQL queries. You can do it that way, but, it is hard to
debug. Well, in my humble opinion. Normally, I would grab the max number and then in the variable
add it in the PHP code. But, another issue is what you do with that number. In your code, you create this
new project number and then display it before it really exists in your database. You can’t display a project
number if it does not exist inside your database. You need to keep your project numbers unique. You
should study up on setting up a unique id using auto-increment instead.

What happens if two users display that same page. They would each get the same new project number.
This would cause huge problems in your code logic. Normally, when your user wants to create a new
project, you would have a page for them to enter whatever data you need to start it off and you would
use the “INSERT” query to insert a new project and have the project number be auto-increment in the
database setup for that field. Doing it that way, your project number would always be unique. Then, you
can select that new project and you will have the project number as an integer. Then, use the number
formatting function if you need to display it with leading zeros in place.

Hope that helps make you think about it some.

#5

[php]ALTER TABLE fms_tbl_projects CHANGE client_proj_no client_proj_no
smallint( 4 ) UNSIGNED ZEROFILL NOT NULL;[/php]

#6

hi guys, thanks for the posts so far…

astonecipher - yes I did research this problem before hand and would always try to sort myself so i can learn. I’ve had a similar problem which i fixed but this code didn’t work for this particular issue.

Ernie/Strider64 - sorry I wasn’t clear on my original post. i have an auto-increment for the ‘ID’ of the project, I have several clients so they can generate their own numbers based upon the previous one they raised. the ‘client_proj_no’ is what I wanted the client to see and I wanted to make the project number consistent and not jumbled dependant on who took which number.

so my database may look like this;-
id client_id client_proj_no
1 1 0001
2 1 0002
3 2 0001
4 2 0002
5 2 0003

does this make sense?

#7

so my database may look like this;-
id client_id client_proj_no
1 1 0001
2 1 0002
3 2 0001
4 2 0002
5 2 0003

couldn’t you not store the client project number as 1,2 3, etc…

then just pad the number with leading zeros like the first post I had?
[php]SELECT "lpad(client_proj-no, 3, ‘0’) as client_proj_no…[/php]

even if the project number got to say 102 it should output 0102.

#8

Strider64 showed you how to do LEFT-PADDING in your SELECT. But…

Dan, the issue is really that you are not thinking of this correctly. A number such as 123 does NOT contain
leading zeros. No such thing. You can fix this by changing it into a string and add any needed zero that
way. Either in the SELECT or, I know of two simple ways to do that in your code…

  1. Use sprintf(’%04d’, $proj_no); This will print four digits with leading zeros.

  2. Use str_pad($proj_no, 4, ‘0’, STR_PAD_LEFT); Pads zeros to left of number up to 4 digits.

Either should work for you. I did not test it. Look up both of those functions to learn the details…

Sponsor our Newsletter | Privacy Policy | Terms of Service