PHP MySQLi INSERT not working, no errors
Different from this question, but similar in that I don’t get an error when adding information to my database.
$sql = "INSERT INTO 'nlcc_ver1'.'tUsers' ('userID', 'userName', 'userPassword', 'userHash', 'user_first_name', 'user_last_name', 'user_corps', 'is_admin', 'is_trg', 'is_sup', 'is_co') VALUES (NULL, '" . $userName . "', '" . $hash . "', '" . $salt . "', '" . $f_name . "', '" . $l_name . "', '" . $corps . "', '" . $admin . "', '" . $trg . "', '" . $sup . "', '" . $co . "')"; $hostname_Database = "localhost"; $database_Database = "nlcc_ver1"; $username_Database = "root"; $password_Database = ""; $mysqli = new mysqli($hostname_Database, $username_Database, $password_Database, $database_Database); if (mysqli_connect_errno()) < printf("Connect failed: %s\n", mysqli_connect_error()); exit(); >$result = $mysqli_query($mysqli, $sql); echo "Query run. Inserted UserID " . mysqli_insert_id($mysqli) . "
"; Line breaks inserted to avoid sideways scrolling. It says on the web page that mysqli_insert_id($mysqli) is 0, and nothing is added to the table on my database. I do not see an error connecting to the database appearing, and MySQL is running on my server, and phpinfo() shows both the MySQL and MySQLI extension loaded. This is just a development machine, so don’t worry about the security (i.e. no password). I have tried googling the problem, but am not finding too much. I don’t know about object oriented PHP programming with ->, I am used to using _. Is this method still supported?
mysqli query doesn’t insert row in database
Hope you guys can help me, I’ve been trying to figure out what seems to be my error but to no avail, here is my code:
**This is my index.php** query("INSERT INTO `borrowersprofile`(`lastname`, `firstname`, `middlename`) VALUES( '".$_POST['lastname']."', '".$_POST['firstname']."', '".$_POST['middlename']."', NOW())"); header("Location: index.php"); exit; > ?> I’ve been trying to check and even tried to remake the database but its still not adding data, btw, this is a school project so security doesn’t really matter, hope you guys can help me out! Thanks.
You have to check by printing your query and then run this query in phpmyadmin then you will know what is error with query.
Would be helpful to list any errors you’re getting on screen or in your log, and the database format. Where is NOW() expected to go? Looks like you don’t have a field for that.
2 Answers 2
As pointed out by @user328
1) Assuming the ID or primary key of your table is AUTO_INCREMENT , therefore you do not need to mention ID column in your query. Sql inserts it automatically for you.
2) You need to mention the column name where to save the value returned by now() .
if($_POST['submit']=='Borrow') < $mysqli->query("INSERT INTO `borrowersprofile`(`lastname`, `firstname`, `middlename`, `DATE`) VALUES( '".$_POST['lastname']."', '".$_POST['firstname']."', '".$_POST['middlename']."', NOW())"); header("Location: index.php"); exit; Try to create this table in your database and then use the script below.
CREATE TABLE `borrowersprofile` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `lastname` VARCHAR(100) NOT NULL, `firstname` VARCHAR(100) NOT NULL, `middlename` VARCHAR(100) NOT NULL, `datetime` DATETIME NOT NULL )ENGINE=InnoDB; prepare("INSERT INTO `borrowersprofile`( `lastname`, `firstname`, `middlename`, `datetime`) VALUES(?, ?, ?, NOW())") < $stmtint->bind_param("sss", $lastname, $firstname, $middlename); if ($stmtint->execute()) < $stmtint->close(); echo "User saved successfully!"; >else< die("Error Message:".$mysqli->error); > header("Location: index.php"); exit; > > ?> connect_error) < die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error); > ?> PHP mysqli_query — INSERT INTO not working, error unclear
I have a lot of variables that I’m adding into a database from all the product pages on my website. Here is my PHP code:
$args = (array( "post_type" => "page", "meta_key" => "_wp_page_template", "meta_value" => "products.php", "posts_per_page" => 50 )); $queryPages = get_posts($args); $con= mysqli_connect("localhost","pmdot","password","pmd_ot"); foreach($queryPages as $qp) < $qpID = $qp->ID; $name = get_post_meta($qpID, "pmd_name", true); $code = get_post_meta($qpID, "pmd_sku", true); $installation = get_post_meta($qpID, "pmd_installation", true); $age = get_post_meta($qpID, "pmd_agefrom", true); $length = get_post_meta($qpID, "pmd_length", true); $width = get_post_meta($qpID, "pmd_width", true); $height = get_post_meta($qpID, "pmd_height", true); $areal = get_post_meta($qpID, "pmd_areal", true); $areaw = get_post_meta($qpID, "pmd_areaw", true); $cfh = get_post_meta($qpID, "pmd_cfh", true); $weight = get_post_meta($qpID, "pmd_weight", true); $type[] = get_post_meta($qpID, "pmd_playtype", true); $accred[] = get_post_meta($qpID, "pmd_accreditation", true); $descr = get_post_meta($qpID, "pmd_shortdesc", true); $price = get_post_meta($qpID, "pmd_price", true); mysqli_query($con, 'INSERT INTO pmd_productdata (Product_ID, Product_Name, Product_Code, Product_Installation, Age_Range, Product_Length, Product_Width, Product_Height, Minimum_Area_L, Minimum_Area_W, C_F_H, Product_Weight, Play_Type, Accreditations, Product_Desc, Product_Price) VALUES ($qpID, $name, $code, $installation, $age, $length, $width, $height, $areal, $areaw, $cfh, $weight, "placeholder", "placeholder", $descr, $price') or die ('Error:' .mysqli_error($con)); > What this is doing is getting all the posts with the template products.php and then using all the custom fields to populate an SQL table with a query. NOTE: The arrays that you can see are not currently being used in the query as I thought that was the issue. Bonus points if someone can help me get all the values in that array to display in the respective field. All of the columns in my table have varchar(255) as their char base, just in case some are not INTs when they should be. For some reason, I’m getting this error:
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 '' at line 1 Now, as you can see the error is rather unclear, if anyone can shine some light on the situation I’d be very grateful. My question is: Why am I getting that error, and what does it mean?
EDIT [Resolved]
Unknown column '$qpID' in 'field list' 'VALUES (' . $qpID . ', ' . $name . ')' etc.; EDIT 2
The errors have now been fixed and the data is in the SQL Database, all I need now is to figure out how to get the 2 arrays to print all their values into the respective column.
PHP mysqli prepared statement INSERT error
I am currently trying to set up a prepared statement to allow users to sign up for my web page. My POST information passes correctly to my submit page from my form, and I am able to successfully insert ?’s upon submission if I remove the prepared statement, but I get an error with this current code.
bind_param('isssssssssi', $uid, $fn, $ln, $u, $p, $dob, $sx, $pn, $em, $a, $up); $stmt->execute(); $stmt->close(); > if (mysqli_query($mysqli, $query)) < $userid = mysqli_insert_id($mysqli); echo "Your user ID is ". $userid; >else < echo "Error: " . $query . "
" . mysqli_error($mysqli); > // display error if occurs var_dump($mysqli); mysqli_close($mysqli); ?> Error: INSERT INTO u (userid, fn, ln, username, p, dob, sx, pn, em, a, up) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) 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 '?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' at line 1 I have tried changing versions of php, I am currently running 5.3, but when I switch to anything beyond I get an error for mysqli class. I have tried back ticking and quoting the ?’s but that does not seem to work either. I am hoping someone can expand upon what is already available regarding prepared statement, because I have searched high and low and have been unable to find what my problem stems from. So, I guess my question is, how do I correctly pass my variables via a prepared statement, and what syntax do I need to use near the ? placeholders? Updated code:
if(isset($_POST['submit']))< $uid = 'NULL'; $fn = $_POST['fn']; $ln = $_POST['ln']; $u = $_POST['u']; $p = $_POST['p']; $dob = $_POST['dob']; $sx = $_POST['sx']; $pn = $_POST['pn']; $em = $_POST['em']; $a = $_POST['a']; $c = $_POST['cn']; $s = $_POST['s']; $z = $_POST['z']; require_once('../mysqli_connect_aimU.php'); $query = "INSERT INTO u (userid, fn, ln, username, p, dob, sx, pn, em, a) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; if (!$stmt = mysqli_prepare($mysqli, $query)) < echo "Error: ".$stmt->error; exit(); > if(!$stmt->bind_param('isssssssss', $uid, $fn, $ln, $u, $p, $dob, $sx, $pn, $em, $a))< echo "Error: ".$stmt->error; > if($stmt->execute())< $userid = $stmt->insert_id; echo "Your user ID is ".$userid; > else < echo "Error: ".$stmt->error; > $cityid= "SELECT id FROM c WHERE cn = '$c' LIMIT 1"; $result = mysqli_fetch_array($cityid); if ($result != true) < $query = "INSERT INTO c (cn) VALUES (?)"; if(!$stmt->bind_param('s', $cn)) < echo "insert error dawg".$stmt->error; > if(!$stmt->execute())< $cityid = $stmt->insert_id; echo "Your city ID is".$cityid; > else < $query = "INSERT INTO ucl (cid, uid) VALUES (?, ?)"; if(!$stmt = mysqli_prepare($mysqli, $query)) < echo "Error: ".$stmt->error; exit(); > if(!$stmt->bind_param('ss', $cityid, $userid))< echo "Error: ".$stmt->error; > if (!$stmt->execute())< echo "Error: ".$stmt->error; > > > MySQLi: $mysqli->query will not insert, and no error is returned
I am trying to insert simple data into my MySQL table via MySQLi, however it refuses to insert, with no error message reported. I’d like to stress that this query functions normally when entered directly into PhpMyAdmin (of course, with the variables replaced)
real_escape_string($_POST['username']); $p = crypto($_POST['password']); $e = $mysqli->real_escape_string($_POST['email']); $resp = recaptcha_check_answer ($privatekey, $_SERVER["REMOTE_ADDR"], $_POST["recaptcha_challenge_field"], $_POST["recaptcha_response_field"]); if (!$resp->is_valid) < $_SESSION['loginError'] = "The captcha was entered incorrectly!"; header("LOCATION: ./index.php?pg=1"); die(); >$amt = 0; if($data = $mysqli->prepare("SELECT * FROM users WHERE username=? OR email=?"))< $data->bind_param("ss", $u, $e); $data->execute(); $data->store_result(); $amt = $data->num_rows(); $data->close(); > if($amt != 0) < $_SESSION['loginError'] = "A user has already registered with either that email or username."; header("LOCATION: ./index.php?pg=1"); die(); >if(strlen($u) < 5)< $_SESSION['loginError'] = "A username must be greater than 5 characters long."; header("LOCATION: ./index.php?pg=1"); die(); >if(strlen($e) if(!strstr($e, "@")) < $_SESSION['loginError'] = "A valid email must be entered."; header("LOCATION: ./index.php?pg=1"); die(); >if(!ctype_alnum($u)) < $_SESSION['loginError'] = "Your username contains an invalid character."; header("LOCATION: ./index.php?pg=1"); die(); >$ip = $_SERVER['REMOTE_ADDR']."r"; $mysqli->query("INSERT INTO users (`username`, `password`, `email`, `perms`, `ip`) VALUES ('".$u."', '".$p."', '".$e."', '0', '".$ip."')"); header("LOCATION: ./index.php?win=1"); ?> I also should note that using both $mysqli->query and prepared statements both did not work. Here’s my phpinfo() under the MySQLi section:
Client API library version 5.0.95 Client API header version 5.0.91 MYSQLI_SOCKET /var/lib/mysql/mysql.sock Directive Local Value Master Value mysqli.default_host no value no value mysqli.default_port 3306 3306 mysqli.default_pw no value no value mysqli.default_socket no value no value mysqli.default_user no value no value mysqli.max_links Unlimited Unlimited mysqli.reconnect Off Off Last thing: I have a very similar insert statement being used in a different file that works. Thanks in advance