MySQL database operation query

General PHP Help
#1

Hi All,

I have a php framework that has a MySQL database with several tables, in each table there is a ‘id’ column and a ‘client_id’ column, this is so I can have several clients all on the same database. They should only be querying their own data using the ‘client_id’ column.

My question is I have certain tables that use ‘a_i’ for the ‘id’ and this is used as the invoice number or order number, is there a way I can have each client to have their numbers be consecutive and not random (due to another client using the id)? for example to have numbering as 1, 2, 3… and not 1, 5, 11

My initial thought is no but I am no PHP genius, should I scrap the id as the invoice/order number and introduce another column which keeps the numbers consecutive for each client?

Your thoughts are welcome and appreciated.

#2

I don’t think you want invoice numbers that are sequential, it will show any customer how well each vendor is doing.

For example, if I ordered dog food from www.lukesallnatural.com and they gave me an invoice # of 1001 then 2 weeks later I order again and I get an invoice # of 1020. I will know in 14 days they generated 19 orders. This opens up the door for people to understand how much business a particular company is doing.

You can have 2 columns - Order number (sequential) then an encrypted version of that order number (That the customer sees) or something else.

#3

Hi Topcoder,

Yes you are correct and this is what my thoughts were in terms of the user seeing the numbering, but I would like to keep the a_i element for each user/customer so when they raise an order it would be;-
id = a_i 1, 2, 3
client_id = 1
order_no = a_i 1001, 1002, 1003

can this be done?

#4

It would be easier for me to visualize at if you listed each of the tables involved.

Table1

column1
column2

Table 2

column1
column2

#5

I completely agree with TopCoder on this. But, this kind of setup might work for you:

[php]set @new_invoice_number = (SELECT max(invoice_number) from orders where store_id = 2) + 1;

insert into orders (customer_id, store_id, invoice_number) values
(3,2, @new_invoice_number);[/php]

You could do the same thing with PHP, this is just an example using the database itself. I might even suggest working it into a stored procedure.

#6

astonecipher / Topcoder,

thanks guys I really appreciate your advice, I now understand how I need to proceed. I’ve added another column into my table called “client_order_no”, I think I can implement this concept on other tables such a projects, invoices etc.

My code for working this out is as follows… there is a big BUT, it doesnt work yet - can you take a look and advise please?
[php]<?php
include(’/include/mysqli_connect.php’);

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

$query = (“SELECT max(client_order_no) FROM orders WHERE client_id=$login_client_id + 1”);
$result = mysqli_query($db, $query) 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_order_no" class="form-control" value=".$row[] . "  >';

}

}

mysqli_close($db);
?>[/php]

#7

What doesn’t work, other than your echo is incorrec?

#8

Hi,

It echos the value “.$row[] .” within the input box but not the actual row query result (if this makes sense)

#9

To print an array variable in a string you have to escape it with brackets.

“{$row[‘I’d’]}”

You would end up with array though, because $row will be holding an array. If you are using an input, you might want to give it a type as well.

#10

thanks guys, I got it working perfectly now! Here’s my final code;-

[php]<?php
include(’/include/mysqli_connect.php’);

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

$client_order_no = (“SELECT max(client_order_no)+1 as client_order_no FROM orders WHERE client_id=$login_client_id”);
$result = mysqli_query($db, $client_order_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_order_no' class='form-control' value ='".$row['client_order_no']."'>" ;
}

}

mysqli_close($db);
?>[/php]

#11

Thanks for posting your solution, but your input still needs a type attribute.

#12

Hi astonecipher, I almost forgot this! Thanks for the heads up and again thanks for your help

#13

Just sayin…

#14

Auto correct on the phone…

Sponsor our Newsletter | Privacy Policy | Terms of Service