-1

I'm trying to execute 2 queries, but whenever I follow the guides online about multi queries, its not doing either of the queries.

What I'm trying to do on the first query is to INSERT or ADD whatever the user inputs on $HISTORY on the record that's currently on colHistory; I.E.:

Current data on colHistory:

A

User inputs 'B' on $HISTORY, the syntax should add 'B' on the 'A' that's currently on record, or 'AB'. Then use the second query to UPDATE all the other records or columns on this particular row.

Here's the code (Please note that the '...' means more code that's unnecessary):

 $query  = INSERT INTO tbInventory SET colHistory='$HISTORY' WHERE colSerno='$SERIALNUM';";
 $query .= "UPDATE tbInventory SET 

     colImage='$IMAGE',
     colSerno='$SERIALNUM',

     ...
     ...

     colHistory=''
     WHERE colSerno='$SERIALNUM'"; 

     mysqli_multi_query($con,$query);

Please note where I declared colHistory as '' before I insert the data from the form. I'm not sure if I'm doing it right on this part. Is there anything that I'm missing?

*Edit:

I have already tried executing the queries one by one as:

mysqli_query($con,"INSERT INTO tbInventory SET colHistory='$HISTORY' ");
mysqli_query($con,"UPDATE tbInventory SET   
...
...

colHistory=''
WHERE colSerno='$SERIALNUM'";

Yet it doesn't seem to work either, the whole thing gets ignored.

(** I have a script below the code block above where I could print the results already, and it does run)

Ruther Melchor
  • 69
  • 2
  • 10
  • why not just mysqli_query one by one ? You do not have to use it until you have no other choice or it has some benefits – SIDU Aug 04 '16 at 01:06
  • I've tried using that but the code block seems to ignore it, just like what it does using the multi-query code block above. – Ruther Melchor Aug 04 '16 at 01:09
  • Ahh. Your first SQL is invalid: insert into tbInventory(colHistory) values ('$HISTORY') -- please check MySQL insert manual – SIDU Aug 04 '16 at 01:21
  • I've tried the code above and it does work, but when I use it alongside the other query, it ignores it entirely. The problem seems to lie within my multi query rather than the singular queries above. – Ruther Melchor Aug 04 '16 at 02:03
  • so nothing works. You show zero error detection – Drew Aug 04 '16 at 02:39
  • @Drew I'm asking about the grounds of multiple queries since it's my first time using this particular function. I can't seem to run 2 queries on a single form as its ignoring both queries. – Ruther Melchor Aug 04 '16 at 02:47
  • I am suggesting that maybe the Server is talking to you and *you* are ignoring the error messages coming back. We can't see your code for error reporting because you chose to abbreviate the whole thing. – Drew Aug 04 '16 at 02:51

2 Answers2

2

Well I can tell you why your first query is broken. INSERT INTO tbInventory SET colHistory='$HISTORY'

This query is using UPDATE syntax but you are telling the query processor to expect INSERT INTO syntax. So the query is unable to execute.

Decide whether you are needing to UPDATE an existing record or INSERT a new one and alter your query to reflect that. (Change INSERT INTO to UPDATE or change "Set colHistory = '$ History'" to "Values ('$ History', 'col2Val', and so on..")

As for your second query, the syntax looks alright from what you have shown but since you didn't post the entire query its hard to say what is happening there. If you can show more of that query I can update this response.

Here's a good SO question on inserts vs updates.

What are differences between INSERT and UPDATE in MySQL?

Community
  • 1
  • 1
Luke
  • 838
  • 7
  • 17
  • What I'm trying to do is to INSERT or ADD whatever the user inputs on $HISTORY on the record that's currently on colHistory; I.E.: Current data on colHistory: A User inputs 'B' on $HISTORY, the syntax should add 'B' on the 'A' that's currently on record, or 'AB'; and then use the UPDATE function on the second query. – Ruther Melchor Aug 04 '16 at 03:15
  • Where is there a `WHERE` clause for `INSERT` stmts in the sql manual? Here is a [Page](http://dev.mysql.com/doc/refman/5.7/en/insert.html) – Drew Aug 04 '16 at 03:18
  • If you stepped back and learned PHP for a few days, like Error Reporting, if you did the same for SQL, we wouldn't be here right now. This is not a Day 1 Tutorial Site. I say that will all kindness – Drew Aug 04 '16 at 03:20
  • Thank you for pointing that out; is there a way that you could help me with regards of what I'm trying to achieve? Update a data in column or in colHistory without being able to alter or edit the current records on it? I'm using this entire syntax on my edit page. – Ruther Melchor Aug 04 '16 at 03:25
  • I'm adding a link to my answer with a good question on updates vs inserts. I'd recommend starting there. – Luke Aug 04 '16 at 03:30
  • Now I could see the difference between these two functions. Thanks for the heads up, @Luke. What I'm trying to do is to restrict any means of deletion to the colHistory record (which already has data on it), but allow addition into it. Any suggestions? – Ruther Melchor Aug 04 '16 at 04:34
  • An UPDATE statement takes a record in a database and changes one or more of the columns (fields of a record) in that record. No new record is created and the existing record is not deleted, nor are the values of the record changed unless they were specifically altered as part of the UPDATE statement – Luke Aug 04 '16 at 05:19
  • What I'm currently doing is calling the current record on the colHistory on a textarea which the user could see, the user could then modify the text in the textarea which then goes into a form and then gets injected in to the database. I'm trying to just let the user add new stuff to the same cell, same record, but not be able to modify the old one. – Ruther Melchor Aug 04 '16 at 06:01
0

I ended up dropping the multi-query method and I did got my intended results by somehow cheating:

I assigned the old data or the data that's currently on the colHistory cell, displayed it, but I disabled the textarea. I then created one more hidden textbox in the script with the old data in it and then hid it to the users view.

I then concatenated both textareas with the new one that I've created that the user could modify, emulating the results wanted.

Ruther Melchor
  • 69
  • 2
  • 10