php - Inserting ID from primary col to another table

Question

I want to insert 2 tables columns ids to another table I got the query but there is the annoying error.

I tried to solve this problem for hours none worked :(

This code:

$query = "INSERT INTO
    groups(
      group_school_id,
      group_teacher_id,
      group_name,
      group_note,
      group_system,
      group_students_count
    )
  VALUES(
      $group_shcool_id,
      $group_teacher_id,
      '$group_name',
      '$group_note',
      '$group_system',
      '$group_students_count'
    )";

this old:

<?php

$db['db_host'] = "localhost";
$db['db_user'] = "admin";
$db['db_pass'] = "1998";
$db['db_name'] = "ahlquran";

foreach ($db as $key => $value) {
    define(strtoupper($key), $value);
}

$con = mysqli_connect(DB_HOST,DB_USER,DB_PASS,DB_NAME);
mysqli_query($con, "SET NAMES 'utf8'");
}
?>

this new:

<?php

// if you are using php just don't forget to add php tags though

$db['db_host'] = "localhost";
$db['db_user'] = "admin";
$db['db_pass'] = "1998";
$db['db_name'] = "ahlquran";

foreach ($db as $key => $value) {
    define(strtoupper($key), $value);
}

//using try catch statements  
try{
  $conn = new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME, DB_USER, DB_PASS);
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  echo "Successfully Connected";
}
catch(PDOException $e){
  echo "Connection Failed" .$e->getMessage();
}

?>

its connects successfully but all my code use the old one, how to change to convert it? I dont know what pdo I like to learn it, it seems more pro type, but is there solution for this site only using mysqli?

sorry for the long post this is my 1st one, dont know how to explain enough

Thanks

give this error :

QUERY FAILED .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 ' , 'test', '', 'test' at line 11

I thought it will work fine like this but I think the problem with the query syntax.

标签： phpmysqlsql

Answer 1

Just an advice try to use PDO prepared statements example below:

$query = "INSERT INTO groups(group_school_id,
                group_teacher_id,
                group_name,
                group_note,
                group_system,
                group_students_count) 
    VALUES (:gsid,:gtid,:gname,:gnote,:gsystem,:gstudcount)";


//assuming  $conn is your object variable name for database connection

$stmnt = $conn->prepare($query);

$stmnt->execute(array(':gsid'=>$group_school_id,
                      ':gtid'=>$group_teacher_id,
                      ':gname'=>$group_name,
                      ':gnote'=>$group_note,
                      ':gsystem'=>$group_system,
                      ':gstudcount'=>$group_students_count));
    //to check if you have inserted data into your table
    $rows = $stmnt->rowCount();
    echo "Inserted ".$rows." row";

the :gsid are placeholders for your variables, also make sure that each variable passed are inline with column datatypes in your database

//if you are using php just don't forget to add php tags though

$dbhost = "localhost";
$dbname = "whatevername";
$dbuser = "root";
$dbpass = "";

//using try catch statements  
try{
  $conn = new PDO('mysql:host='.$dbhost.';dbname='.$dbname, $dbuser, $dbpass);
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  echo "Successfully Connected";
}
catch(PDOException $e){
  echo "Connection Failed" .$e->getMessage();
}