PHP MYSQL database structure, multiple queries for one entry

Question

I'm building a fairly simple site to keep track of of some sales for work. It involves a mysql database with multiple tables for each entry. For the most part, the relationships are cut and dry. However, I have a comments table that will store multiple comments for the same sale. I have a foreign key in the comments table tied to the ID of the main sales table. I have a similar arrangement between a gross table and the sales table, in that it stores multiple gross amounts for the same saleID. What is the best way to insert these into the database? Currently, I'm inserting the comments and getting the ID of that row, then inserting the gross and getting the ID, then I make the insertion into the sales table using the comments id and the gross id. Is there a more efficient method rather than making 5 queries?

--Edit: Here is the current code. I'm pretty new to this so I had to look up procedures to see what you meant. It seems like it's basically what I'm doing, but creating a method for it instead, which I'll be doing once I figure out the most efficient way.

//Prepare the Queries
//Insert Gross Amounts Query
$grossQ = "INSERT INTO gross (commGross, storeGross, manGross, fiGross, flatGross) VALUES ('$commGross', '$storeGross', '$manGross', '$fiGross')";

//Insert Comments Query
$commentsQ = "INSERT INTO comments (comment, dealcomment, grosscomment) VALUES ('$otherComments', '$dealComments', $grossComments')";

//Insert Deal Query
$q = "INSERT INTO sales (make, model, location, vehtype, saletype, manager, county, flat, unitcount, armoramount, controlnumber, salesman, stock) VALUES ('$make', '$model', '$location', '$vehType', '$saleType', '$managerName', '$county', '$flat', '$unitCount', '$armorAmount', '$controlNumber', '$salesmanName1', '$stock')";

//Make database connection
$db = new db('palm_sales');

//Execute gross query and get the id of the row
$db->execute($grossQ);
$grossID = $db->getLastID();

//Execute comments query and get the id of the row
$db->execute($commentsQ);
$commentsID = $db->getLastID();

//Build Main Query
$q = "INSERT INTO sales (make, model, location, vehtype, saletype, manager, gross, comments, county, flat, unitcount, armoramount, controlnumber, salesman, stock) VALUES ('$make', '$model', '$location', '$vehType', '$saleType', '$managerName', '$grossID', '$commentsID', '$county', '$flat', '$unitCount', '$armorAmount', '$controlNumber', '$salesmanName1', '$stock')";

//Execute Main Query
$db->execute($q);
if(mysql_affected_rows() > 0)
{
    echo "Success!";    
}
else echo "Error: ".mysql_error();

Can you post your code? Most efficient way might be to split your insertion between procudere an trigger(s) — Emmanuel N
– Emmanuel N, Commented Jan 16, 2012 at 19:25
You should write a stored procedure, or simply use the sql transactions to assure that everything is ok or nok. — Felipe Cardoso Martins
– Felipe Cardoso Martins, Commented Jan 16, 2012 at 19:28
Efficient enough IMHO, having it in code is also easier to debug then the notiriously difficult to debug mysql procedures. I would however wrap them in a transaction. — Wrikken
– Wrikken, Commented Jan 16, 2012 at 19:29
I've also never used transactions, so I'll be toying around with them in this project as well. — Stewie
– Stewie, Commented Jan 16, 2012 at 19:45

Cal · Accepted Answer · 2012-01-16 19:29:26Z

1

It sounds like you're only performing 3 queries at the moment - mysql_insert_id() does not perform a new query, it gives your information about the insert just performed.

It's not clear why you need to insert into the sales table when you insert a comment or gross. I would imagine you'd have something like this:

TABLE sales     PK id
TABLE comments  PK id   FK sales_id
TABLE gross     PK id   FK sales_id

When you get a new sale, you insert into the sales table. To add a new comment or gross, you insert into that table, with a foreign key of the sales ID. What part of that is not working?

answered Jan 16, 2012 at 19:29

Cal

7,13227 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stewie Over a year ago

All of the information is collected in one form. I see what you're saying though. I am currently storing an FK comments_id and FK gross_id in the sales table as well, but that's not needed. I should insert the main sale first and then get the id of that for the comments and the gross.

Collectives™ on Stack Overflow

PHP MYSQL database structure, multiple queries for one entry

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related