0

I'm new at this, sorry for my ignorance.

I have two php SQL queries that work just fine on their own:

INSERT INTO `table`.`lines` ( `id` , `raw_line` , `next_line` , `credit` , `timestamp` ) VALUES ( NULL , '$_POST[raw_line]', '', '$_POST[credit]', NOW( ) );;

and

UPDATE `lines` SET `next_line` = CONCAT('When ', `raw_line`)

I don't know how to combine/order the two in php so that the UPDATE processes automatically after the INSERT INTO. The INSERT INTO works but the UPDATE does not.

Thanks for any help!

Improve this question
3
  • 1
    You are missing a where clause in the update statement. please explain what "update does not work" means (error, no update, to much updates ...) Commented Aug 8, 2013 at 14:31
  • 1
    Just running them one after the other will work. And you are missing a Where in your UPDATE query Commented Aug 8, 2013 at 14:32
  • 1
    The UPDATE statement updates ALL rows in the table (there is no WHERE clause), so these two really can't be combined. It's odd that you are qualifying the tablename in the INSERT, but not the UPDATE. Also beware of Little Bobby Tables, including post data in SQL text is a SQL Injection vulnerability. Why do you need to store the same raw_line data twice? You could prepend the 'When ' literal when you do the select. Commented Aug 8, 2013 at 14:33

5 Answers 5

Reset to default
1

I think you need something like:

INSERT INTO `table`.`lines` ( `id` , `raw_line` , `next_line` , `credit` , `timestamp` ) VALUES ( NULL, '".$_POST[raw_line]."', 'When ".$_POST[raw_line]."', '".$_POST[credit]."', NOW( ) );
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Simply set the next_line field on INSERT. No need for a separate query.

INSERT INTO `table`.`lines` ( `id` , `raw_line` , `next_line` , `credit` , `timestamp` ) VALUES ( NULL , "$_POST['raw_line']", "When $_POST['raw_line']", "$_POST['credit']", NOW( ) );
Improve this answer

Comments

0

If you want to try something a bit different and expand your mysql skills try looking into triggers this will call a query automatically on the database when data is inserted into the first table.

Improve this answer

Comments

0

Run both queries serially.

$insertQuery = "INSERT INTO `table`.`lines` ( `id` , `raw_line` , `next_line` , `credit` , `timestamp` ) VALUES ( NULL, '".$_POST[raw_line]."', 'When ".$_POST[raw_line]."', '".$_POST[credit]."', NOW( ) )"; if( mysqli_query($connection, $insertQuery) ){ //on success run update query $updateQuery = "UPDATE `lines` SET `next_line` = CONCAT('When ', `raw_line`)"; mysqli_query($connection, $updateQuery); }
Improve this answer

1 Comment

NOTE: it's more efficient to run a single INSERT than it is to insert the row and then update the row to a longer length. It's less work for the database because it's one SQL statement vs two (avoids extra work obtaining locks, committing, logging), AND because we aren't changing the size of the row (which can lead to unnecessary fragmentation (row migration, row chaining, split blocks... depending on the storage engine.)
0

Create a trigger, note that you have to be the root user on your database:

DELIMITER $$ CREATE TRIGGER `update_next_line` BEFORE INSERT ON `table`.`lines` FOR EACH ROW BEGIN SET NEW.`next_line` = CONCAT('When ', NEW.`raw_line`); END$$ DELIMITER ;
Improve this answer

3 Comments

The right way to do this is a BEFORE INSERT trigger, and just set the value of the column... SET NEW.next_line = CONCAT('When ',NEW.raw_line
He said so that the UPDATE processes automatically after the INSERT INTO. I did miss those news though, so thank you.
It's not possible for an AFTER INSERT trigger on a table to UPDATE any rows in that same table; an attempt to perform such an UPDATE raises Error 1442. The NEW keyword can't be used in place of a table identifier in an UPDATE statement. Bottom line is that an AFTER INSERT trigger cannot achieve what the OP wants to achieve.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.