mysqli_multi_query not inserting to mysql db

Question

I have used tutorials, examples and looked at numerous other questions about my problem and I still can't get it to work, I am relatively new to PHP and do not have any understanding of PDO. I have changed my code to mysqli rather than mysql to get rid of the depreciated code my university gave me but they have been less than helpful during this situation.

If anyone could shed some light onto this issue for me I would be very grateful. Below are my code samples:

<?php /*connect to the db */
   $link=mysqli_connect("dbhost","user","pass");
   mysqli_select_db("db",$link);

   /*checking connection*/
      if ($link->connect_errno) 
        throw new exception(sprintf("Could not connect: %s", $link->connect_error));

session_start();



        $insert_query="
        INSERT INTO testone_tbl (age,hours,flexibility,fastpaced,retailexp,
        workedus,conviction,permit,education)
        VALUES ('$age','$hours','$flexibility','$fastpaced','$retailexp','$workedus',
        '$conviction','$permit','$education'); 
        INSERT INTO testtwo_tbl 
        (contribute,insales,initiative,success,alternatives,targets,
        newthings,custfeed,incdevelop,standards,confident,stretch,
        opportunities,polite,ideas,deadline,supported,duties)
        VALUES ('$contribute','$insales','$initiative',
        '$success','$alternatives','$targets','$newthings',
        '$custfeed','$incdevelop','$standards','$confident','$stretch',
        '$opportunities','$polite','$ideas','$deadline','$supported','$duties')";


    /*execute multi_query*/


mysqli_multi_query ($link, $insert_query);/*error1*/
/*close connection*/
if(!$link>connect_errno) $link->close(); /*error2*/

?>

The data is both from the form this is written in (the last form) and sessions from the previous forms. However I am also getting this error: Warning: mysqli_multi_query() expects parameter 1 to be mysqli and Warning: mysqli_close() expects parameter 1 to be mysqliand I have been stuck on this the past few days! Thank you in advance.

Answer 1

You should first check with your web host if they have enabled multi-SQL-queries.

Some web hosts only allow single-SQL queries to help prevent against injection attacks.

If, however, you want to multi-insert to the same table, you could do it like this:

INSERT INTO tbl_name (col1, col2)
     VALUES ('?', '?'),
            ('?', '?'),
            ('?', '?'); # inserts 3 records to the same table in one query

Also, if you do have PDO available to you, use it!

With a PDO object, your queries will be safer by using prepared statements. Example:

$db = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);

$data = array($col1, $col2, $col3);
$sql = "INSERT INTO tbl_name (col1, col2, col3) VALUES ('?', '?', '?');";
$query = $db->prepare($sql); # prepares the sql statement
$query->execute($data); #binds the array of data to the ?
                        #question mark parameters, and executes.

If you create a database abstraction layer, you could change the database connection mode without having to rewrite your code which executes your queries.

Also, do you have a reason not to loop and query? Example:

$sql_array = array("INSERT INTO tbl_one(col1) VALUES '?';",
             "INSERT INTO tbl_two(col3) VALUES '?';");

function performAll($sql_array) {
    # execute all of the queries
}

It has occured to me that you may be using some function to access your database connection. Now that is not a problem, unless you actually try to access the database connection from within a function (in case you have not told us). Example:

$db = new PDO("...", $user, $pass);

$query = $db->prepare($sql); # works fine

function executeQuery($sql) {
    $query = $db->prepare($sql); # error: $db is not defined
                                 # within the scope of this function
    ...
}

To get around this, use the global keyword in PHP. Example:

$db = new PDO("...", $user, $pass);

function executeQuery($sql) {
    global $db; # use $db in the global scope
    $query = $db->prepare($sql); # works fine
    ...
}