[Solved] Add UPDATE into existing SELECT script

Databases
1

Hi all

Recently had a similar issue, which was completed thanks.

My problem now is how I add a simple UPDATE into my SELECT query using the same table based on the same WHERE clause. I basically need to UPDATE a column (HITS) by 1.

How do I fit the below snippet into the main SELECT query?

UPDATE events_test SET HITS = HITS + 1 WHERE ID = ?;

Do I need a join? Another query?

$stmt = $mysqli->prepare("
  SELECT ID
  , DTEND
  , DTSTAMP
  , LOCATION
  , DTSTART
  , DESCRIPTION
  , SUMMARY
  
  FROM events_test
  WHERE ID = ?");

Thanks, Barry

2

I’m not sure I understand what you are asking. Why do you want to select and update in the same query? Presumably you already have the data you are updating your database with. UPDATE can be a standalone query, just like SELECT or DELETE

EDIT: On further review, are you trying to increment a column given the ID of the column? If so, the first statement you have there would run the way you want to.

3

The HITS column is part of the same table ‘events_test’
I simply want to update this column +1 within the same query if possible.

I haven’t selected it because I don’t need to show it, just update it.

That make sense?

Thanks, Barry

EDIT: Yes correct!
The script does work, I have tried it. I was hoping to add it into the SELECT script because I’m using the same table with the the same WHERE ?

4

From what I have seen, it looks like SELECT and UPDATE are two entirely separate queries, and I’m not sure they can be combined like that. One solution I found would be to write something called a procedure, but I have never used one and I get the feeling they are meant for something far more heave duty than what you need. In all honesty, it would probably be best to just run two queries.

5

Correct fenceman2

As mentioned, I was thinking/wondering if we could combine these into the same script, like we did with the last one using DUPLICATE KEY UPDATE… in the INSERT script.

In all honesty, it would probably be best to just run two queries.

Maybe you’re right, I just needed calcification really, though would be nice if we could join them. Anyhow, I’ll do some further searching though as you say, not really a major issue having two queries.

Thanks for your input.

I did try :expressionless:

$stmt = $mysqli->prepare("
  SELECT ID
  , DTEND
  , DTSTAMP
  , LOCATION
  , DTSTART
  , DESCRIPTION
  , SUMMARY
  
  FROM events_test
  
  UPDATE SET HITS = HITS + 1

  WHERE ID = ?");
6

No worries, the main difference between the two issues is both INSERT and UPDATE change rows in the table, while SELECT does not, so presumably SQL needs to do something different. Did that query you tried work at all? I would assume the reason behind it is because if you do it in one query, SQL does not know if you wanted the previous data or the recently updated data.

7

Fatal error: Call to a member function bind_param() on a non-object in /…

To do with the PHP prepare statement… not to sure myself.

I have this just underneath:

$stmt->bind_param(‘i’, $id);
$stmt->execute();
$stmt->bind_result($id, $end, $fulldate, $location, $start, $description, $summary);
$stmt->fetch();

8

Yeah I would presume its because SQL is working with two versions of the same row if you try it in one query, I’m not sure that there is a way to do it that would be more beneficial than running two queries.

9

Cool, thanks anyhow probably not worth all the messing for a small query.

Cheers, Barry

10

allow me to calcify for you – you nees two separate sql statements

11

allow me to calcify for you – you nees two separate sql statements

Thank you :slight_smile:

I have just set things up and have the below as a separate statement, works good.

$mysqli->query(“UPDATE events_test SET HITS = HITS + 1 WHERE ID = $id”);

Just one issue, not really an issue just that now every time I UPDATE the timestamp updates. I really only wanted this to UPDATE when details of the event where updated from the other script.

Is there a way to tell the timestamp not to update, ignore this column somehow with the small script above?

Thanks, Barry

12

I’ve just came across:

UPDATE table SET x=y, timestampColumn=timestampColumn WHERE a=b;

It seem it is possible to ignore the timestamp column when doing an update.
How could I fit this process into my current snippet?

Thanks, barry

13

I managed to fix it :smile:

MODIFIED is the timestamp column.

$mysqli->query(“UPDATE events_test SET HITS = HITS + 1, MODIFIED = MODIFIED WHERE ID = $id”);

This updates the hits but ignores the timestamp.

Does this seem correct?

I also ran the other script checking both INSERT and UPDATE after changing JSON then viewed and ran the UPDATE HITS… Perfect!!!

Thanks guys :sunglasses:

Barry

14

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.