Help with DB theory please

[forumnz]

Hi there,

 

Apologies if this is easy - I am quite good at programming but I can't get my head around this one.

I have a database table named 'headers' and and a database table named 'items'. Each header row, has a unique id, and each item row has a column called 'pid', which links each item to its header.

 

E.g.

Header Name (id 1)

    Item Name (pid 1)

    Item Name (pid 1)

    Item Name (pid 1)

 

Header Name (id 5)

    Item Name (pid 5)

    Item Name (pid 5)

 

So you will get the idea.

I want to create an edit page, where a user can edit the header names, and the item names, and also (and this can be on a separate page) add headers and users.

 

I am not asking for code, but perhaps an explanation of how it works?

 

Thanks heaps!

Sam.

[hokietoner]

This is pretty basic relational db stuff. Basically, each header is associated with 0 or more items. You would start by providing some way of creating headers, which I assume you can figure out how to do. On the page where the user can create items, you would probably have an HTML drop-down that's pre-filled with the list of headers. The value of each header in the drop-down would be the header ID. When the user fills out the information to create an item, they select a header and submit the form. The PHP will create a new item and the pid will be the ID of the header the user chose for that item.

 

Later on, if you want to get an item with its header name, you'll have to do a join query. You'll join the two tables on header.id = item.pid and you can get back rows that include the header name and all the rest of the item fields.

 

That's a real basic overview of it.

[forumnz]

Thanks, I understand that. How could I edit multiple items at the same time?

This is my main concern.

 

Thanks,

Sam.

[forumnz]

I'll try to explain what I'm saying...

I basically need to edit multiple rows from a db in one go. How does that work?

 

Thanks,

Sam,

[forumnz]

Please help?

[hokietoner]

Well, you could print out basically the entire table with form fields, looking something like this:

 

[Header Name] [item Name] [item field 1] [item field 2]

 

Header name would probably be a drop-down, Item name might be a text box, and so on. You could give these fields names such as header_i, name_i, field1_i, field2_i. where i is the row number. The user would edit the "table" and submit. On the other end when your script receives the post, you can loop through the results like this:

 

// If you want to use PDO to work with your database, it's very easy:

$stmt = $dbh->prepare("INSERT INTO items (pid, name, field1, field2) VALUES (:pid, :name, :field1, :field2)");

$stmt->bindParam(':pid', $pid);

$stmt->bindParam(':name', $name);

$stmt->bindParam(':field1', $field1);

$stmt->bindParam(':field2', $field2);

 

$i = 0;

 

while(isset($_GET["header_$i"])) {

  $pid = $_GET["header_$i"];

  $name = $_GET["name_$i"];

  $field1 = $_GET["field1_$i"];

  $field2 = $_GET["field2_$i"];

  $stmt->execute();

  $i += 1;

}

 

Or, you could just use the standard database interface and write the query inside the while loop. However, you'd have to properly escape the variables to prevent SQL injections.

 

Is this what you're stuck on? You can learn more about PDO here: http://us3.php.net/manual/en/ref.pdo.php