PDO Update Joined tables

[jiros1]

Thank you for your time.

 

I'm trying to update a Game in my database, I'm new with PDO.

I can edit the name but I don't know how to change the category. I select it from the table TBL_Category where TBL_Category.ID equals  JTBL_Game_Category.Category_ID.

 

How do I update it via a dropdown box so my Game's Category is changed?

 

This is my PHP code:

$Name = $_POST['Name'];
$id = $_POST['ID'];

$sql = "UPDATE TBL_Game
SET Name=?
WHERE id=?";

$statement = $db -> prepare($sql);
$statement -> execute(array($Name, $id));

header("Refresh:0");

 

My tables:

 

TBL_Game

ID= 1

Name= Game of thrones 

 

TBL_Category

ID= 1

Name= Boardgame

 

JTBL_Game_Category

ID 1

Game_ID= 1

Category_ID= 1

 

Thanks in advance

Edited October 9, 2015 by jiros1

[benanamen]

UPDATE JTBL_Game_Category

SET Category_ID=?

WHERE Game_ID=? AND Category_ID=?

[scootstah]

Your schema is a many-to-many relationship between games and categories, meaning that many games can have many categories. In your screenshot though you just have a dropdown, as if to suggest that any game can only have a single category. If that's the case you can remove the JTBL_Game_Category table and simple add a Category_ID field to your TBL_Game table.

 

As an aside, I recommend you use named parameters in PDO instead of ?'s. Once you have a bunch of parameters it's a huge pain to keep track of the order of them and such.

$sql = "UPDATE JTBL_Game_Category
SET Category_ID=:newCategoryId
WHERE Game_ID=:gameId AND Category_ID=:categoryId";

$statement = $db -> prepare($sql);
$statement -> execute(array(
    'categoryId'    => $categoryId,
    'gameId'        => $gameId,
    'newCategoryId' => $newCategoryId,
));

[benanamen]

 

 

I recommend you use named parameters in PDO instead of ?'s. Once you have a bunch of parameters it's a huge pain to keep track of the order of them and such.

 

 

Could you elaborate on that please. I use question marks and have no problems whatsoever regardless of how much data is being inserted. Either way, you still have to know the order and if your POST data names/variablenames are the same as the DB columns it is clear what your dealing with. And if there are 20 pieces of data going in, you just count 20 question marks. Mysql is more than happy to tell you if your question mark count is off. Named parameters is just more typing which means more prone to typos. I also never create extra variables out of the post data as I see many doing, the OP included. It is just not necessary whatsoever. Nevertheless, this one really comes down to the coders preference IMO, they are both the right way to do it.

 

I am just happy our brother @jiros1 is not using Mysql_*.

 

EDIT* Uh, my response more geared to doing insert. OP is doing an update. Still not a problem ever for me.

Edited October 9, 2015 by benanamen

[scootstah]

Which of these do you consider more readable and maintainable?

INSERT INTO person
(firstname, lastname, email, age, gender, height, weight, location) VALUES
(?,?,?,?,?,?,?,?);

INSERT INTO person
(firstname, lastname, email, age, gender, height, weight, location) VALUES
(:firstname, :lastname, :email, :age, :gender, :height, :weight, :location);

If you have complex queries with dynamic clauses and stuff, it quickly becomes chaotic with ?'s.

 

Either way, you still have to know the order

There is no order when using named parameters. You can bind values in whatever order you want.

Edited October 9, 2015 by scootstah

[benanamen]

To answer your question, the first example by far. The values of the second example is just throwing a bunch of duplicate data at me. All I need to know is that in the first example, their needs to be the same amount of question marks. There is no need to "read" question marks, only count them.

 

It is also, 112 characters vs 170 characters. Times that over an entire application and that is a whole lot of extra typing. Also, your not likely to misspell a question mark.

Edited October 9, 2015 by benanamen

[scootstah]

You're worried about typos, but not worried about accidentally mixing up the order of parameters? It would be a very easy thing to do with more complicated queries. The negligible amount of extra typing is not an excuse to forego readability/maintainability. If I open an application for the first time to track down a query, I don't want to spend time counting ?'s and trying to figure out where data should be getting inserted. I want to be able to identify what is going on as quickly as possible. If I see a named parameter called ":categoryId", it's pretty reasonable to assume that that is where the "categoryId" value is getting inserted.

[benanamen]

Typos is but a minute issue but is worth mentioning.

 

 

 

not worried about accidentally mixing up the order of parameters? It would be a very easy thing to do with more complicated queries.

 

Actually, on longer queries I have done exactly that so you got me there. Working on my own stuff easy enough with question marks. Other peoples stuff the named parameters would definitely be the better option. I guess I have been lucky, I rarely work on someone else's application. All my projects are ground up builds.  Even if they have an existing app, it ALWAYS has mysql_* and other bad code and is faster and cheaper to start from scratch.

[seandisanti]

Another $.02 on the named values vs ?'s. If another person is ever going to look at your code (that includes "Future You" who invariably ends up refactoring your stuff just because he thinks he knows better), then it's typically best not to make assumptions about their contextual wherewithal no matter how intuitive your code seems while you're writing it.

[jiros1]

Thank you for answering my question, based on your solution I now have this code and it works like a charm:

 

$Game_Name  = $_POST['Game_Name'];
$Game_ID  = $_POST['Game_ID'];
$Category_ID  = $_POST['Category_ID'];


//query
$sql = "UPDATE TBL_Game
SET Category_ID =?, Name =?
WHERE TBL_Game.ID =?
";


$statement = $db -> prepare($sql);
$statement -> execute(array(
$Category_ID,
$Game_Name,
$Game_ID,


));

Only thing left now is make it an object so my code is more maintainable. Thanks!