[RESOLVED] PHP: combining UPDATE, SELECT (with min function) queries

Solar2D General Questions/Discussion
#21

Naomi,

Thats statement could be very aggresive and update recordsets that has the same values. I am refering to the statement in the thread at stackoverflow. The join has to be joined over an unique identifier (field) and not the value field…

Thats the only difference between my inner join and the one at stackoverflow.

A table should have a field with a primary key (unique). For example if you have a table named person, it would for example contain these fields: id, name, surname, telephone, state, age

“id” could be an integer that the database keeps track of, and automatically increases for every new record. Nothing you have to keep track of. This “id” is the key to everything and this is the field you always should look up your data against. Not the other fields.

Update Person set name = ‘kim’ where state = ‘NY’ - will affect all rows with the state of NY.

update Person set name = ‘kim’ where id = 1 - will affect this row only.

So for my sample with the innerjoin and the above layout of the table would be:

  
--  
  
UPDATE Person t1 -- This t1 is the alias for the first table we are joining   
 INNER JOIN (SELECT min(age) FROM Person) t2 --this would be the second alias for the same table and we are selecting the person with the lowest age. Alias is t2.  
 on t1.id = t2.id --this would tell the query that the id has to be same on both rows, since it is unique.  
SET t1.age = 25 --Update the age in the first table alias we created.  
  
--

The query above will update the person in the table that is the youngest and set hes age to 25.

Joakim

[import]uid: 81188 topic_id: 32713 reply_id: 130071[/import]

#22

Hey, Joakim, at the moment, this single query as you see it is all there is to it (and by the way, yeah, I do understand that if there are two rows with the very same minimum value, then both rows would be affected.) Anyhow, I just needed to figure this out before I start adding complexity (like if-statements, etc.) And this particular table will not have more than 25 rows. It can’t get all that complex, surely.

And honestly, I don’t see how this query can be tighter, but I do know I should convert all of my PHP scripts to prepared statements and stored procedures. One of the person on Stackoverflow commented on how I absolutely should use proper SQL placeholders so as not to destroy my site. (Well, he then followed up nicely, so its all good. Besides, I’ve been aware of my needing to use prepared queries for a while now. I just wasn’t ready to change/convert everything yet…)

Cheers,
Naomi

Edit: Just in case you are curious, here’s the link to Stackoverflow post I made:

http://stackoverflow.com/questions/13259250/php-mysql-how-to-combine-update-and-select-with-min-function-queries
[import]uid: 67217 topic_id: 32713 reply_id: 130080[/import]

#23

Thank you, Joakim. Yeah, that’s where it went wrong, it looks like.

Anyhow, following the exact code supplied on Stackoverflow got my thing working. Woohoo – but by looking at the comments on Stackoverflow, it sounds like I’m on the path to destroy my own site by not using prepared queries and/or proper SQL placeholders. How scary. I’ve read about prepared statements quite a while ago (when I first started reading the PHP book) but couldn’t quite understand it. What I need is super simple example to help me get started with it.

And yeah, I do know I probably have to spend years learning this thing to be able to write super incredible codes/scripts. But then, I can’t be simply spending months learning how to write an incredibly tight and genius scripts/codes before I actually start building what I need, can I?

Anyhow, here’s the final query that worked:

-- I think MINTALBE can be replaced with any table alias (like T1)  
$query = "UPDATE ". $myTable . "   
 INNER JOIN ( SELECT MIN(value) as value FROM ". $myTable . " ) MINTABLE   
 ON " . $myTable . ".value = MINTABLE.value  
 SET flag=" . $flag;  
$data = mysqli\_query($dbh, $query);

Now that I understand the basics with this type of combining queries, it will help tightening up some of the queries I’ve set up. And yeah, I will work on this prepared queries thingy.

Cheers,
Naomi
[import]uid: 67217 topic_id: 32713 reply_id: 130073[/import]

#24

Great, but - yes there is always a but. I am not sure how your table look like and how much data there is - but that query you got there is doing this in a more simplified version…

Update myTable set flag = $flag where flag is lowest
So if there are more records with a minimum value, all those records will be affected.

Joakim [import]uid: 81188 topic_id: 32713 reply_id: 130078[/import]

#25

Hey, Joakim, at the moment, this single query as you see it is all there is to it (and by the way, yeah, I do understand that if there are two rows with the very same minimum value, then both rows would be affected.) Anyhow, I just needed to figure this out before I start adding complexity (like if-statements, etc.) And this particular table will not have more than 25 rows. It can’t get all that complex, surely.

And honestly, I don’t see how this query can be tighter, but I do know I should convert all of my PHP scripts to prepared statements and stored procedures. One of the person on Stackoverflow commented on how I absolutely should use proper SQL placeholders so as not to destroy my site. (Well, he then followed up nicely, so its all good. Besides, I’ve been aware of my needing to use prepared queries for a while now. I just wasn’t ready to change/convert everything yet…)

Cheers,
Naomi

Edit: Just in case you are curious, here’s the link to Stackoverflow post I made:

http://stackoverflow.com/questions/13259250/php-mysql-how-to-combine-update-and-select-with-min-function-queries
[import]uid: 67217 topic_id: 32713 reply_id: 130080[/import]