My sql query works in phpmyadmin, not in PHP script

@exha1984Aug 23.2010

At the beginning I was thinking Java or PHP to learn, people told me PHP is easier .. but with time I don’t believe they were right, sometimes it takes one day to find the problem.. here comes an example.

My sql query works in PhpMyAdmin, but not in PHP script, any idea how is that possible (with explanation plz)?

[code=php]$q_insert_t = “INSERT INTO tbl_test (test, type, good) VALUES (‘$test’, ‘$type’, ‘$good’) INSERT into t_evaluate (id_test, evaluation, language) VALUES (LAST_INSERT_ID(), ‘$evaluation’, ‘1’);”;[/code]

$good (boolean)
$language (english: 1 of french: 2)

Error it’s giving:

~~[QUOTE]~~

~~[SIZE=”2″][COLOR=”Red”]~~You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘INSERT into t_evaluate (id_test, evaluation, language) VALUES (LAST_INSERT_’ at line 3[/COLOR][/SIZE]

[/QUOTE]

Kinds,

to post a comment

PHP

15 Comments(s) _↴

@tirnaAug 23.2010 — #Looks like you just need to do some Basic Debugging 101 (post #1).

If you add the red code for debugging and post the output

[CODE] 
 $q_insert_t = "INSERT INTO tbl_test (test, type, good)
 VALUES ('$test', '$type', '$good')
 INSERT into t_evaluate (id_test, evaluation, language)
 VALUES (LAST_INSERT_ID(), '$evaluation', '1');"; 
 
 [COLOR=red]echo $q_insert_t; die(); // this displays on the screen the actual query about to be run[/COLOR] 
 [/CODE]

hopefully things will become clearer.

@exha1984authorAug 23.2010 — #echo() works!
INSERT INTO tbl_test (test, type, good) VALUES ('hello world', 'a', '0') INSERT into t_evaluate (id_test, evaluation, language) VALUES (LAST_INSERT_ID(), 'test verified', '1');[/QUOTE]

@tirnaAug 23.2010 — #Now copy and paste that output query to your phpMyAdmin and I suspect it won't work.

Hopefully the error message will tell you why.

Imo this is the best way to learn.

@exha1984authorAug 23.2010 — #Phpmyadmin says:
Your SQL query has been executed successfully[/QUOTE]

@exha1984authorAug 23.2010 — #My update and select queries are working fine on both interfaces: phpmyadmin and my php application, but insert is making me crazy right here!

@tirnaAug 23.2010 — #ok, I use SQLyog instead on phpMyAdmin.

Try adding a semi colon after the first insert command and remove the one after the second insert:

[CODE] 
 $q_insert_t = "INSERT INTO tbl_test (test, type, good)
 VALUES ('$test', '$type', '$good')[COLOR=red];[/COLOR]
 INSERT into t_evaluate (id_test, evaluation, language)
 VALUES (LAST_INSERT_ID(), '$evaluation', '1')";
 [/CODE]

Otherwise maybe the LAST_INSERT_ID() is causing the problem. I'm not sure why you are including that assuming id_test is an auto-increment column in your database table. If id_test is an auto-increment you should be able to use

[CODE] 
 $q_insert_t = "INSERT INTO tbl_test (test, type, good)
 VALUES ('$test', '$type', '$good')[COLOR=red];[/COLOR]
 INSERT into t_evaluate (evaluation, language)
 VALUES ('$evaluation', '1')";
 [/CODE]

@exha1984authorAug 23.2010 — #I did a semi colon change but the same error.

I need to insert id_test otherwise I don't know for which test my evaluation is done. So it is absolutely needed.

@exha1984authorAug 23.2010 — #I also tried this one (works perfectly in phpmyadmin):

[code=php]$q_insert_t = "INSERT INTO tbl_test (test, type, good)
 VALUES ('$test', '$type', '$good');
 INSERT into t_evaluate (id_test, evaluation, language)
 VALUES (select id_test from tbl_test order by id_test desc limit 1, '$evaluation', '1')";[/code]

@exha1984authorAug 23.2010 — #This one too:

[code=php]$q_insert_t = "INSERT INTO tbl_test (test, type, good)
 VALUES ('$test', '$type', '$good');
 INSERT into t_evaluate (id_test, evaluation, language)
 VALUES (".mysql_query("select id_test from tbl_test order by id_test desc limit 1").", '$evaluation', '1')"; [/code]

@tirnaAug 23.2010 — #Doesn't LAST_INSERT_ID() get the value if id_test for the previous record inserted and so are you now trying to add a new record with a value of id_test that equals the value of id_test in the previous inserted record.

What I am saying is - should you be doing an update instead of an insert for your second sql command in [FONT=Courier New][COLOR=#0000bb]$q_insert_t?[/COLOR][/FONT]

[FONT=Courier New][COLOR=#0000bb][/COLOR][/FONT]

[FONT=Courier New][COLOR=black]Or am I misunderstnding what you are trying to do?[/COLOR][/FONT]

@exha1984authorAug 23.2010 — #No, what I am trying is to insert in second table (t_evaluate) the last id (test_id) I am inserting with

[code=php]INSERT INTO tbl_test (test, type, good)
 VALUES ('$test', '$type', '$good');[/code]

But now, I don't think PHP works like MySql does. I have to make to call mysql_query() twice for two different strings like this:

[code=php]$q_insert_t1 = "INSERT INTO tbl_test (test, type, good)
 VALUES ('$test', '$type', '$good')";
 
 mysql_query($q_insert_t1) or die();
 
 $q_insert_t2 = "INSERT into t_evaluate (id_test, evaluation, language)
 VALUES (LAST_INSERT_ID(), '$evaluation', '1')";
 
 mysql_query($q_insert_t2) or die();[/code]

@exha1984authorAug 23.2010 — #And that works very good, and I have to make sure that both queries are successfully executed or none of them will be insert with this validation:

[code=php]if (!mysql_query($q_insert_t1) && !mysql_query($q_insert_t2)) {
 die ("adding test failed!" . mysql_error()); 

 }[/code]

We don't have transactions in mysql, so we need to do something in the code to make sure all necessary data is inserted.

@tirnaAug 23.2010 — #ok glad you sorted it out ?

I have never run multiple queries in mysql_query() but assumed you could.

I just googled the manual quickly for mysql_query() and it says

mysql_query() sends a unique query [B](multiple queries are not supported)[/B] to the currently active database on the server that's associated with the specified [I]link_identifier[/I].
[/QUOTE]

I guess I should have RTFM (Read The Fine Manual) first ?

@tirnaAug 23.2010 — #And that works very good, and I have to make sure that both queries are successfully executed or none of them will be insert with this validation:

[code=php]if (!mysql_query($q_insert_t1) && !mysql_query($q_insert_t2)) {
 die ("adding test failed!" . mysql_error()); 

 }[/code]

We don't have transactions in mysql, so we need to do something in the code to make sure all necessary data is inserted.[/quote]

As a double-check I would normally also make sure that mysql_affected_rows() returns a value of 1 for each query.....anyway, just food for thought. ?

@exha1984authorAug 23.2010 — #Yes, that's right, But I could knew that without manuals after so many differents ways I tried, it was cool anyway ?

I am looking for better ways to make sure both tables are altered (all data test and evaluation) are inserted.

I thought about if(!mysql_query()) and if(mysql_affected_rows()>0) but this is not good enough. It won't work 100%.

Also in #PHP _↴

Need help ASAP on file database If then Statement using Defined Variable auto generated url

Success!

Help @exha1984 spread the word by sharing this article on Twitter...

Tweet This

My sql query works in phpmyadmin, not in PHP script

15 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

My sql query works in phpmyadmin, not in PHP script

15 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

15 Comments(s) _↴

Also in #PHP _↴