[SOLVED] Is this a valid query?

[will_1990]

This is a generated query from phpmyadmin, however when i try and enter the corisponding data into my form it returns an error! Im not sure if the query is valid!

 

$q = "INSERT INTO BookTitle, BookCopy, Publisher, Author
(BtId, BtName, Value, BcId, DateAcquired, PubId, PubName, PubAddress, AuthorId, AuthorName)
VALUES ('$bkid', '$bn', '$v', '$bcid', '$da' , '$pubid' , '$pub', '$authid', '$auth', '$pubadd')";

 

This is it. I have checked that its connecting to the database fine but i am unsure of whether i have to connect a specific table aswell considering that i am inserting into 4 of them!

 

This may mean i need to split this up into 4 smaller queries is thsi correct?

 

thanks for any help or advice!

 

Will

[Guest]

I've never really tried to commit multiple inserts in one statement -- make it simple, split it into four queries. If all these queries are dependent on the success of the other, you may want to look into transactions (assuming your using a version of MySQL with INNOdb support).

 

If this is the case, you can use the SQL statements:

 

BEGIN TRANSACTION

COMMIT

ROLLBACK

 

If you don't know what these mean, google "MySQL transactions" or check out http://www.databasejournal.com/features/mysql/article.php/3382171/Transactions-in-MySQL.htm.

 

If you're using an older version of mysql, or a db that doesn't support it, you could mimic it in PHP by retrieving the last insert id of each insert query, and performing a DELETE query on each of them if one fails.

[will_1990]

Thanks alot for your advice, i am not using that type of database so it would mean alot of coversions so i have decided to to each one separately as suggested...

 

my first insert query is 

//connect to the database
   //database information
     $host      = "stocks"; // Host name
     $username  = "njpeddle"; // Mysql username
     $password  = "mysql5"; // Mysql password
     $db_name   = "njpeddle"; // Database name
 $db_table = "Staff"; //connecting to the staff table
 $table_bookTitle = "BookTitle"; //connect to booktitle table

  mysql_connect("$host", "$username", "$password")or die("cannot connect");
      mysql_select_db("$db_name")or die("cannot select DB");  

//queries

$q = "INSERT INTO '$table_bookTitle' 
(BtId, BtName, PubId, Value)
VALUES ('$BtId','$bn', '$pubid','$v')"; 

 

but i am now getting this error returned, i know it is connecting to the database OK but i have an else statement which echos the query and the data which it was meant to enter (which returns fine) so i don't know what the error is, any ideas what it could be?

 

thanks again!

 

[samshel]

when in doubt, us mysql_error(). it give u complete detailed information about the error,.

 

$q = "INSERT INTO '$table_bookTitle' 
(BtId, BtName, PubId, Value)
VALUES ('$BtId','$bn', '$pubid','$v')"; 

$res = mysql_query($q) or die (mysql_error());

[trq]

Your table identifier should not be surrounded by quotes.