current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite
1

I am trying to add data into 3 table using PHP, atm I can only view the results of the tables that are joined .

RESULTS QUERY

  $sql = mysql_query("SELECT PART_ID, PART_DESC, SERIAL_NUM, PART.RACK_NUM, PART.PART_TYPE_ID,      PART_TYPE_DESC, LOCATION
    FROM PART
    INNER JOIN PART_TYPE ON PART.PART_TYPE_ID = PART_TYPE.PART_TYPE_ID
    INNER JOIN RACK ON RACK.RACK_NUM = PART.RACK_NUM

This will get all the rows from the PART table, and for each of the rows we find, match that row to a row in the PART_TYPE table (the condition being that they have the same PART_TYPE_ID). If no match between the PART and PART_TYPE tables can be found for a given row in the PART table, that row will not be included in the result.

My Insert Query This is where im having trouble

How do I add the data to the PART_ID, PART_TYPE and RACK tables?

<?php 
// Parse the form data and add inventory item to the system
if (isset($_POST['PART_ID'])) {
    $id = mysql_real_escape_string($_POST['PART_ID']);
    $PART_DESC = mysql_real_escape_string($_POST['PART_DESC']);
    $SERIAL_NUM = mysql_real_escape_string($_POST['SERIAL_NUM']);
    $RACK_NUM = mysql_real_escape_string($_POST['RACK_NUM']);
    $PART_TYPE_ID = mysql_real_escape_string($_POST['PART_TYPE_ID']);
    $LOCATION = mysql_real_escape_string($_POST['LOCATION']);
    $PART_TYPE_DESC = mysql_real_escape_string($_POST['PART_TYPE_DESC']);
    // See if that product name is an identical match to another product in the system
    $sql = mysql_query("SELECT PART_ID FROM PART WHERE PART_ID='$id' LIMIT 1");
    $productMatch = mysql_num_rows($sql); // count the output amount
    if ($productMatch > 0) {
        echo 'Sorry you tried to place a duplicate "Product Name" into the system, <a href="inventory_list.php">click here</a>';
        exit();
    }
    // Add this product into the database now
    **$sql = mysql_query("INSERT INTO PART (PART_ID, PART_DESC, SERIAL_NUM, RACK_NUM, PART_TYPE_ID) 
        VALUES('$id','$PART_DESC','$SERIAL_NUM','$RACK_NUM','$PART_TYPE_ID')") or die (mysql_error());**


     header("location: inventory_list.php"); 
    exit();
}
?>

enter image description here

share|improve this question
    
so what is your question or where is the problem ? –  Mian Khurram Ijaz May 30 '11 at 17:07
    
that is such an odd way to do queries.. use PDO –  llnk May 30 '11 at 17:08
    
in the insert query –  Michael Quiles May 30 '11 at 17:22
    
@yes 123 is there a more effective way of doing this ? –  Michael Quiles May 30 '11 at 17:24
1  
yes using pdo php.net/manual/en/pdo.prepare.php –  llnk May 30 '11 at 17:48
show 1 more comment

3 Answers

up vote 1 down vote

Micheal if I understood your problem you just need to do 2 other SQL INSERT to add data in the other table

$sql = mysql_query("INSERT INTO PART (PART_ID, PART_DESC, SERIAL_NUM, RACK_NUM, PART_TYPE_ID) 
        VALUES('$id','$PART_DESC','$SERIAL_NUM','$RACK_NUM','$PART_TYPE_ID')") or die (mysql_error());

$currentID = mysql_inserted_id();

$sql2 = mysql_query("INSERT INTO PART_TYPE [..]");
$sql3 = mysql_query("INSERT INTO RACK [..]");

You can use $currentID if you need the ID of the last record inersted into PART

But still I strongly suggest you to learn PDO http://php.net/pdo for sql

share|improve this answer
    
Yes that is correct will this resolve the results not showing items that arent in the PART_TYPE table? –  Michael Quiles May 30 '11 at 17:41
    
I don't get what's your problem –  llnk May 30 '11 at 17:43
    
When I add items they are not listed due to the inner joins, they do get added but they select query wont displpay any new items due to my insert statement. If it do a select statement onlu no the part table the new items appear. So if I add a new item with an ID of 1001 it wont be listed only item 1003 and so on will show up using the join statement. –  Michael Quiles May 30 '11 at 17:50
    
what about full outer join? –  llnk May 30 '11 at 17:51
    
outer join shows no results –  Michael Quiles May 30 '11 at 17:57
show 1 more comment
up vote 0 down vote

your table management is wrong, you never use arrows just to show that you are joining it with that table from this table, but rather from the key in first table to foreign key in the second table, that's what i would start from, maybe a better idea would be to join them using JOIN look up in google how joins are working, that may be the cause...

I agree with @yes123, that is the correct way to insert into tables, if you have a program called heidisql then use it, because there is a window to run your queries... that way to test if it is properly written also use mysql_error.

Debug, debug, and one more time debug your code.

share|improve this answer
add comment
up vote 0 down vote

Your tables are not correctly designed. Try this table structures .

In your base table Part. -

The columns in this should be:

Part_id
part_desc
serial_num

The part_type should have following columns:

part_type_id
part_type_desc
part_id -> foreign key to the parent table

The rack table should be:

Rack_num
location
part_id -> foreign key to the parent table.

So your select query to get all the part related information would be:

$sql="select * from part join part_type pt on tp.part_id=part.part_id join Rack_num rn on rn.part_id=part.part_id";

With this structure the data remains normalized. And is flexible, so if the parts are on multiple racks you just go to the rack table and add and new rack number and the part id.

share|improve this answer
    
Thanks for the answer this was for a bachelors assignment for creating an inventory site from scratch. I eventually figured it out and got an A on the assignment and the presentation. Sadly I didn't get a programming job and I am now working on my PhD, wow how time flies. –  Michael Quiles Mar 11 at 2:14
    
congrats on getting a A on your presentation. Dont worry about the job you will eventually get there.. keep trying. –  user2411276 Mar 11 at 18:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.