How to merge these two queries into one?

Question

Quite simply... How can I merge these two queries into one?

$sql = "UPDATE `data` SET `views` = `views` + 1 WHERE `id`=$id AND `source`='$source' AND `keyword`='$keyword'";

$sql = "UPDATE `settings` SET `count` = `count` + 1 WHERE `id`=$id";

EDIT:

Also, there's the matter of the first time data is entered, when the data to update does not yet exist in the data table.

My insert Query:

$sql = "INSERT INTO `_data` (`id`, `source`, `keyword`, `views`) VALUES ($id,'$source','$keyword',1)";

Is it possible to combine an insert of new data to one table, with an update to another table?

How these tables are linked to each other show their association — M Khalid Junaid, Aug 03 '15 at 06:36
means is there any relation between your data and settings table ? — M Khalid Junaid, Aug 03 '15 at 06:38
http://stackoverflow.com/questions/16763595/php-mysql-combine-update-queries — Hearner, Aug 03 '15 at 06:39
This is already answered in http://stackoverflow.com/questions/2044467/how-to-update-two-tables-in-one-statement-in-sql-server-2005 — KuKeC, Aug 03 '15 at 06:39
There is an ID that is the same in each, if that's what you mean? — JVC, Aug 03 '15 at 06:40

score 2 · Accepted Answer · answered Aug 03 '15 at 06:40

2

I would suggest you to use prepared statements.

$sql = "UPDATE data, settings
        SET data.views = data.views+1, settings.count = settings.count+1,
        WHERE data.id = $id AND data.source = $source AND data.keyword = $keyword AND settings.id = $id";

answered Aug 03 '15 at 06:40

Akshay

2,244
3
15
34

Ahhh that looks like exactly what I was imagining but couldn't quite make work. I'll try this now, Thanks! – JVC Aug 03 '15 at 06:42
Oops, second part of my question should have been, how to do the same thing but with an INSERT. I'll update my question. – JVC Aug 03 '15 at 06:49
You'd be making it complex, with insert and update in one query. If I were you, I would first check if the data exists (probably by making it a simple function), and depending on it's result, would either insert or update. – Akshay Aug 03 '15 at 06:54
Yeah that's how I'm doing it currently. But I seem to occasionally have a situation where the two counters get out of sync. I was hoping to roll everything into one query and potentially eliminate the issue. – JVC Aug 03 '15 at 07:00
Then use transactions for it buddy. You won't face any problems. – Akshay Aug 03 '15 at 07:04
Thanks for this, it got me the closest. I now have a single query for updates (which are the most frequent by far) and I use my original two query approach only when inserting. Looks like it will work nicely... thanks! – JVC Aug 03 '15 at 07:17

score 0 · Answer 2 · answered Aug 03 '15 at 07:00

Hello JOnathan van Clute,

If you want to merge two queries in php then you can try like this in your code.

 $sql = "UPDATE `data` SET `views` = `views` + 1 WHERE `id`=".$id." AND `source`='".$source."' AND `keyword`='".$keyword."';";

 $sql .= "UPDATE `settings` SET `count` = `count` + 1 WHERE `id`=".$id.";";

It will merge and append the query in your $sql variable. If you want fire one query and update two records at a time then it possible with one

Like as follows

 $sql ="UPDATE  `data` as dt, `settings` as st SET dt.views = dt.views + 1 , st.count = st.count` + 1 WHERE dt.id=".$data_id." AND dt.source='".$source."' AND dt.keyword='".$keyword."' AND st.id=".$setting_id.";

make sure about your $id variables here I used different varibles for different table like

setting = $setting_id and data= $data_id

the above query will fire once but it will update two table and two records as you can see we add two tables in UPDATE query so both way two records will be update.

Hope it will help you

Thank you ['}

How to merge these two queries into one?

2 Answers2