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

Naomi · November 6, 2012, 11:02am

With my current project, I use PHP & MySQL database to talk to the game app, and I’m stuck with this PHP script problem. I scoured the web, but most of the sites simply solves more complex query problem, and maybe my problem is too simple for anyone not to find a solution and no need for any help. But hey, I admit defeat, and I’d so appreciate any help trouble-shooting this thing.

What I’m trying to do is to select a row with the least value from a table and update the flag column for that row. But it’s not working for me. If I break it into two queries, it works, but combining the two isn’t going anywhere. What am I doing wrong?

Naomi

[Edit: space added to the script]

THIS WORKS AND DO THE JOB:

$flag = 1;  
$query = "SELECT MIN(value) FROM ". $myTable;  
$data = mysqli\_query($dbh, $query);  
$dataSet = mysqli\_fetch\_row($data);  
$value = $dataSet[0];  
$queryUpdate = "UPDATE ". $myTable . " SET flag='" . $flag . "' WHERE value='" . $value. "'";  
$dataUpdate = mysqli\_query($dbh, $queryUpdate);

BUT I WANT TO COMBINE THESE SELECT QUERY AND UPDATE QUERY TO A SINGLE QUERY, but none of the following works. What am I doing wrong?

--Attempt #1a:  
$flag = 1;  
$query = "UPDATE ". $myTable . " SET flag='" . $flag . "' WHERE value=( SELECT MIN(value) )";  
$data = mysqli\_query($dbh, $query);  
  
--Attempt #1b (removed single quotes):  
$flag = 1;  
$query = "UPDATE ". $myTable . " SET flag=" . $flag . " WHERE value=( SELECT MIN(value) )";  
$data = mysqli\_query($dbh, $query);  
  
--Attempt #2a:  
$flag = 1;  
$query = "UPDATE ". $myTable . " SET flag='" . $flag . "' WHERE value=( SELECT MIN(value) FROM '". $myTable . "')'";  
$data = mysqli\_query($dbh, $query);  
  
--Attempt #2b (removed single quotes):  
$flag = 1;  
$query = "UPDATE ". $myTable . " SET flag=" . $flag . " WHERE value=( SELECT MIN(value) FROM ". $myTable . ")";  
$data = mysqli\_query($dbh, $query);  
  
--Attempt #3a:  
$flag = 1;  
$query = "UPDATE ". $myTable . " SET flag='" . $flag . "' ORDER BY value ASC LIMIT 1";  
$data = mysqli\_query($dbh, $query);  
  
--Attempt #3b(removed single quotes):  
$flag = 1;  
$query = "UPDATE ". $myTable . " SET flag=" . $flag . " ORDER BY value ASC LIMIT 1";  
$data = mysqli\_query($dbh, $query);

[import]uid: 67217 topic_id: 32713 reply_id: 332713[/import]

jkrassman · November 6, 2012, 11:56am

Naomi, you have to use an inner join!

UPDATE myTable t1  
 INNER JOIN (SELECT min(myfield) FROM myTable) t2  
 on t1.id = t2.id  
SET t1.flag = 25

With this type of query, you are referencing the same table with the alias t1 and t2. The match is created with the on t1.id = t2.id assuming that you key is named id. mySql is ten times harder then msSql

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

Naomi · November 6, 2012, 12:16pm

Ah, I broke down and signed up with Stackoverflow, and before I posted the very same question, their search engine found what appears to be relevant:

http://stackoverflow.com/questions/3559713/update-with-a-min-in-the-the-table

EDIT: Ugh, it turns out it’s a bit different query, and no matter what variation I tried out of it, it wouldn’t do…

Naomi [import]uid: 67217 topic_id: 32713 reply_id: 130034[/import]

jkrassman · November 6, 2012, 11:56am

Naomi, you have to use an inner join!

UPDATE myTable t1  
 INNER JOIN (SELECT min(myfield) FROM myTable) t2  
 on t1.id = t2.id  
SET t1.flag = 25

With this type of query, you are referencing the same table with the alias t1 and t2. The match is created with the on t1.id = t2.id assuming that you key is named id. mySql is ten times harder then msSql

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

Naomi · November 6, 2012, 12:34pm

Hey, Joakim, thank you so much for taking the time to reply-post. I’ve done LEFT OUTER JOIN to combine two separate tables in my queries, but this is the first time using the INNER JOIN. Once I get a hang of it, I’m sure I can combine more queries, which would be awesome.

Anyhow, I tried it, but it gives me the very same error: [text]Warning: mysqli_error() expects exactly 1 parameter, 0 given[/text], meaning, the query isn’t finding the row with min value at all…

Here’s what I did (mind you, I don’t quite understanding how the alias works at the moment):

$query = "UPDATE ". $myTable . " T1   
 INNER JOIN ( SELECT MIN(value) FROM ". $myTable . " )   
 ON T1.id = T2.id   
 SET T1.flag=" . $flag;  
$data = mysqli\_query($dbh, $query);  
echo("Query: " . $query . ", Table: " . $myTable . ", Flag: " . $flag. ", Error: " . mysqli\_error());

Where did I go wrong?

Naomi [import]uid: 67217 topic_id: 32713 reply_id: 130054[/import]

jkrassman · November 6, 2012, 12:37pm

You missed one alias, the T2…I think it should look like this:

$query = "UPDATE ". $myTable . " T1   
 INNER JOIN ( SELECT MIN(value) FROM ". $myTable . " ) T2  
 ON T1.id = T2.id   
 SET T1.flag=" . $flag;

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

Naomi · November 6, 2012, 12:16pm

Ah, I broke down and signed up with Stackoverflow, and before I posted the very same question, their search engine found what appears to be relevant:

http://stackoverflow.com/questions/3559713/update-with-a-min-in-the-the-table

EDIT: Ugh, it turns out it’s a bit different query, and no matter what variation I tried out of it, it wouldn’t do…

Naomi [import]uid: 67217 topic_id: 32713 reply_id: 130034[/import]

Naomi · November 6, 2012, 12:34pm

Hey, Joakim, thank you so much for taking the time to reply-post. I’ve done LEFT OUTER JOIN to combine two separate tables in my queries, but this is the first time using the INNER JOIN. Once I get a hang of it, I’m sure I can combine more queries, which would be awesome.

Anyhow, I tried it, but it gives me the very same error: [text]Warning: mysqli_error() expects exactly 1 parameter, 0 given[/text], meaning, the query isn’t finding the row with min value at all…

Here’s what I did (mind you, I don’t quite understanding how the alias works at the moment):

$query = "UPDATE ". $myTable . " T1   
 INNER JOIN ( SELECT MIN(value) FROM ". $myTable . " )   
 ON T1.id = T2.id   
 SET T1.flag=" . $flag;  
$data = mysqli\_query($dbh, $query);  
echo("Query: " . $query . ", Table: " . $myTable . ", Flag: " . $flag. ", Error: " . mysqli\_error());

Where did I go wrong?

Naomi [import]uid: 67217 topic_id: 32713 reply_id: 130054[/import]

Naomi · November 6, 2012, 1:57pm

Thank you, Joakim. I appreciate all your help.

Edit: I added T2 and tried it again, and [text]mysqli_error($dbh)[/text] returns this error:
Error: Unknown column ‘T2.id’ in ‘on clause’

Hmmm…

Naomi

P.S. I also tried this other script, and I get different error message:

$query = "UPDATE ". $myTable . " SET flag=" . $flag . " WHERE value=( SELECT MIN(value) FROM ". $myTable . ")";  
$data = mysqli\_query($dbh, $query);  
-- error message I get for this one is:  
-- Error: You can't specify target table 'myTable' for update in FROM clause

Okay, now that I understand what it doesn’t like, I have something to go on with… [import]uid: 67217 topic_id: 32713 reply_id: 130064[/import]

jkrassman · November 6, 2012, 1:57pm

Naomi, print the query to the console and paste it in here so I can take a look at it.

What tools are you using when querying the database?

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

jkrassman · November 6, 2012, 12:37pm

You missed one alias, the T2…I think it should look like this:

$query = "UPDATE ". $myTable . " T1   
 INNER JOIN ( SELECT MIN(value) FROM ". $myTable . " ) T2  
 ON T1.id = T2.id   
 SET T1.flag=" . $flag;

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

Naomi · November 6, 2012, 1:59pm

OMG, very helpful someone on Stackoverflow responded with a fix for the INNER JOIN method:

http://stackoverflow.com/questions/13259250/php-mysql-how-to-combine-update-and-select-with-min-function-queries

I’ll give it a shot.

Thanks again, Joakim, for directing me to use INNER JOIN.

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

jkrassman · November 6, 2012, 2:00pm

Ahh, whats your primary key column named? I was assuming it was “id”.

ON T1.id = T2.id

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

jkrassman · November 6, 2012, 2:13pm

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]

Naomi · November 6, 2012, 2:19pm

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]

Naomi · November 6, 2012, 1:57pm

Thank you, Joakim. I appreciate all your help.

Edit: I added T2 and tried it again, and [text]mysqli_error($dbh)[/text] returns this error:
Error: Unknown column ‘T2.id’ in ‘on clause’

Hmmm…

Naomi

P.S. I also tried this other script, and I get different error message:

$query = "UPDATE ". $myTable . " SET flag=" . $flag . " WHERE value=( SELECT MIN(value) FROM ". $myTable . ")";  
$data = mysqli\_query($dbh, $query);  
-- error message I get for this one is:  
-- Error: You can't specify target table 'myTable' for update in FROM clause

Okay, now that I understand what it doesn’t like, I have something to go on with… [import]uid: 67217 topic_id: 32713 reply_id: 130064[/import]

jkrassman · November 6, 2012, 1:57pm

Naomi, print the query to the console and paste it in here so I can take a look at it.

What tools are you using when querying the database?

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

Naomi · November 6, 2012, 1:59pm

OMG, very helpful someone on Stackoverflow responded with a fix for the INNER JOIN method:

http://stackoverflow.com/questions/13259250/php-mysql-how-to-combine-update-and-select-with-min-function-queries

I’ll give it a shot.

Thanks again, Joakim, for directing me to use INNER JOIN.

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

jkrassman · November 6, 2012, 2:00pm

Ahh, whats your primary key column named? I was assuming it was “id”.

ON T1.id = T2.id

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

jkrassman · November 6, 2012, 2:40pm

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]