MySQL INSERT query no output

Question

Database Schema:

CREATE TABLE `db1`.`info`
(
  `Name` NOT NULL,
  `PhoneNumber` INT(15) NOT NULL,
  `Location` NOT NULL,
  `Var1` LONGTEXT NOT NULL,
  `Var2` LONGTEXT NOT NULL
) ENGINE = InnoDB;

connection.php:

$con = new mysqli("localhost","root","","db1") or die ("Conn Error");

index.php:

<?php
include_once("connection.php");
$name="New Person";
$phone="0121234567";
$loc="New York";
$var1="A";
$var2="B";

$insert="INSERT INTO info (Name, PhoneNumber, Location, Var1, Var2) VALUES ('$name','$phone','$loc','$var1','$var2')";

$result1 = $con->query($insert);
?>

I've just started to learn php and when I tried to do this (after the connection was created) and I was not getting any output. -EDIT- By output I mean it isn't getting added to the database.

So I looked around and found

if(!$result1){ //error-checking
    var_dump($result1);

and added it before ?> and the output I'm getting is bool(false). Why is that? And also, how do I correctly insert values into my table?

Thanks in advance.

INSERT query is not supposed to give you any output. Check your database to see if the values are getting added there. — Maximus2012, Mar 25 '16 at 19:45
`mysqli_query` returns `false` on error, but without your database schema it's impossible to say where the error is. Also, your database connection might not work. — Technoh, Mar 25 '16 at 19:50
Post the full code w/ connection setup. Also check the error method/prop to see what went wrong. — m02ph3u5, Mar 25 '16 at 19:58
@Maximus2012 My apologies. I meant that the values are not added to the table. — NewbieProgrammer, Mar 25 '16 at 20:02
You should look into this: http://stackoverflow.com/questions/17053466/how-to-display-errors-for-my-mysqli-query and this http://stackoverflow.com/questions/6575482/how-do-i-enable-error-reporting-in-php — Maximus2012, Mar 25 '16 at 20:04
@NewbieProgrammer look at all those fancy error methods/props http://php.net/manual/en/class.mysqli.php — m02ph3u5, Mar 25 '16 at 20:08
Your connection syntax does not look right to me. Is that what you have in your actual code ? — Maximus2012, Mar 25 '16 at 20:10
@Maximus2012 What doesn't look right? Is it the quotation marks? My bad. This isn't copy pasted from the code. — NewbieProgrammer, Mar 25 '16 at 20:12
You're still missing a closing parenthesis on your `new mysqli` call. If that's not the problem, try adding a `print_r($insert);` to see how your variables are expanding, and also show us your table definition. — Darwin von Corax, Mar 25 '16 at 20:16
You can always try doing `var_dump($con)` after establishing DB connection to see what the db connection object looks like. — Maximus2012, Mar 25 '16 at 20:18
Still not right. It should be `$con = new mysqli("localhost","root","","db1") or die ("Conn Error"); — Darwin von Corax, Mar 25 '16 at 20:27
@Maximus2012 var_dump($con) is giving me this: object(mysqli)#1 (19) { ["affected_rows"]=> int(0) ["client_info"]=> string(79) "mysqlnd 5.0.12-dev - 20150407 - $I...... There's a limit to the stuff I can paste here so are you looking for something specific in it? I'm sure I'm doing something very stupid and it's frustrating. — NewbieProgrammer, Mar 25 '16 at 20:29
The connection object looks okay. It might have something to do with your database schema. You should update the question with your database schema. Also, try adding error handling code as I mentioned in one of the comments earlier. — Maximus2012, Mar 25 '16 at 20:30
@DarwinvonCorax umm I did echo print_r($insert) but it just gave me the stuff I passed to it except that the variables have been replaced with the strings. Is that what is to be expected? — NewbieProgrammer, Mar 25 '16 at 20:31
@DarwinvonCorax Stupid mistake here but the connection is being established for sure. I'm trying to print data from the table and it works. — NewbieProgrammer, Mar 25 '16 at 20:34
@Maximus2012 Um, what's database schema? The column names and variable types it takes of the table? — NewbieProgrammer, Mar 25 '16 at 20:34
That's good. At the top of your code, add the lines `ini_set('display_errors', '1'); error_reporting(E_ALL);` (I should have suggested this first) and then check your server's `error.log` file for messages. — Darwin von Corax, Mar 25 '16 at 20:35
Your database schema is the table definitions. Showing us the `CREATE TABLE` statement would be enough. — Darwin von Corax, Mar 25 '16 at 20:36
@DarwinvonCorax I already put that, thanks to Maximus2012, not that I need it because I changed that in the cfg file to show all errors before I started the project. The error.log shows no new errors. — NewbieProgrammer, Mar 25 '16 at 20:45
@Maximus2012 The database schema is `CREATE TABLE 'db1'.'info' ('Name' NOT NULL, 'PhoneNumber' INT(15) NOT NULL, 'Location' NOT NULL, 'Var1' LONGTEXT NOT NULL, 'Var2' LONGTEXT NOT NULL ) ENGINE = InnoDB;` Except I replaced the single quotes with backticks. — NewbieProgrammer, Mar 25 '16 at 21:03

score 0 · Answer 1 · answered Mar 25 '16 at 21:33

0

You won't have any output while doing an insert. Here is an improve or your code in case of error:

$result1 = $con->query($insert) or die("Error while inserting data");

answered Mar 25 '16 at 21:33

Lord-Y

122
2
7

By output I mean the SQL database in MyPHPAdmin is not getting updated. Is that how it's supposed to be? – NewbieProgrammer Mar 26 '16 at 07:07
I did var_dump on result and got bool(false). So basically it is going to die. – NewbieProgrammer Mar 26 '16 at 07:28

score 0 · Answer 2 · answered Mar 25 '16 at 21:40

0

maybe what you are looking for is ?

$n = $con->affected_rows; // number of inserted rows in your case should be 1

and if you had any error you'll find it here

$error = mysqli_error($con);

answered Mar 25 '16 at 21:40

Arsonik

2,208
1
14
22

Ummm, I just want the database to get updated. After running the code, when I go to phpAdmin it should show up there, right? – NewbieProgrammer Mar 26 '16 at 07:08

score 0 · Accepted Answer · answered Mar 25 '16 at 21:50

Looking at your schema, I would speculate the problem is that you're trying to insert a string value ('$phone') into an INT column. To insert it as an INT you specify it without the quotes ('$name', $phone, '$loc'...)

This is one of the (many) reasons I like using prepared statements for this sort of thing: quoting and type-matching are handled for you automagically.

To perform your insert using a mysqli prepared statement, you would use the following code:

$insert = "INSERT INTO info
               (Name, PhoneNumber, Location, Var1, Var2)
             VALUES
               (?, ?, ?, ?, ?)";
$stmt = $con->prepare($insert);
$stmt->bind_param('sisss', $name, $phone, $loc, $var1, $var2);
if ($stmt->execute())
{
    echo "Yay!";
}
else
{
    echo "Error: {$stmt->errno}: {$stmt->error}";
}

The first argument of bind_param specifies the types of the following arguments, in order: 's' = string, 'i' = integer. When the query is executed, the current values of those variables are inserted in place of the placeholders ('?') in the same order.

`mysqli` prepare statement worked perfectly. Thank you so much. — NewbieProgrammer, Mar 27 '16 at 09:34

MySQL INSERT query no output

3 Answers3