Php mysql insert number

Insert numbers into a single field MySQL

I’m trying to insert diferent numebers (like «10 11 12») into a MySQL field (the numbers come from PHP), but when I do the query, the field only gets the first number. For example, I do:

UPDATE profile SET subcategory = '10 11 12' WHERE userId = 1 LIMIT 1 

if subcategory is type of int then it is converted to int so the first proper int which is 10 will be put to table

I don’t know what’s more sad, people suggesting that field length is changed or field type. It’s obvious this is bad database design. If you have multiple subcategories assigned to a single profile, you insert more rows, each row being subcategory_id linked to the same profile_id/user_id or whatever. Those are the basics. As for going the bad route — that’s personal choice. And the guys suggesting increasing field length/type just proves that reputation on this website means nothing when total newbies can obtain it.

3 Answers 3

This happen because you’re updating a number, probably an integer, so mysql do the job just for the first number.

UPDATE profile SET subcategory = 10 WHERE userId = 1 LIMIT 1 UPDATE profile SET subcategory = 11 WHERE userId = 1 LIMIT 1 UPDATE profile SET subcategory = 12 WHERE userId = 1 LIMIT 1 

You’ll just update the category with the third value (12).

I suggest you a user belonging to multiple subcategories so you’ll have to create another table. Eg: a table called subcategories with at least two fields: userId and subcategoryId. And then you could do something like this:

DELETE FROM subcategories WHERE userId=1 INSERT INTO subcategories (userId, subcategory) VALUES (1,10) INSERT INTO subcategories (userId, subcategory) VALUES (1,11) INSERT INTO subcategories (userId, subcategory) VALUES (1,12) 

The first line (delete) is used just to update the user’s subcategories, first you delete all older subcategories for the user and then you insert the new ones. In PHP you could use a foreach() to automatize the insertion of multiple values.

You could also have a non unique userId in the table profiles with an entry per user subcategory but it will complicate things.

Источник

PHP MySQL Insert Data

After a database and a table have been created, we can start adding data in them.

Here are some syntax rules to follow:

• The SQL query must be quoted in PHP
• String values inside the SQL query must be quoted
• Numeric values must not be quoted
• The word NULL must not be quoted

The INSERT INTO statement is used to add new records to a MySQL table:

To learn more about SQL, please visit our SQL tutorial.

In the previous chapter we created an empty table named «MyGuests» with five columns: «id», «firstname», «lastname», «email» and «reg_date». Now, let us fill the table with data.

Note: If a column is AUTO_INCREMENT (like the «id» column) or TIMESTAMP with default update of current_timesamp (like the «reg_date» column), it is no need to be specified in the SQL query; MySQL will automatically add the value.

The following examples add a new record to the «MyGuests» table:

Example (MySQLi Object-oriented)

$servername = «localhost»;
$username = «username»;
$password = «password»;
$dbname = «myDB»;

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) die(«Connection failed: » . $conn->connect_error);
>

$sql = «INSERT INTO MyGuests (firstname, lastname, email)
VALUES (‘John’, ‘Doe’, ‘john@example.com’)»;

if ($conn->query($sql) === TRUE) echo «New record created successfully»;
> else echo «Error: » . $sql . «
» . $conn->error;
>

Example (MySQLi Procedural)

$servername = «localhost»;
$username = «username»;
$password = «password»;
$dbname = «myDB»;

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) die(«Connection failed: » . mysqli_connect_error());
>

$sql = «INSERT INTO MyGuests (firstname, lastname, email)
VALUES (‘John’, ‘Doe’, ‘john@example.com’)»;

if (mysqli_query($conn, $sql)) echo «New record created successfully»;
> else echo «Error: » . $sql . «
» . mysqli_error($conn);
>

Example (PDO)

$servername = «localhost»;
$username = «username»;
$password = «password»;
$dbname = «myDBPDO»;

try $conn = new PDO(«mysql:host=$servername;dbname=$dbname», $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = «INSERT INTO MyGuests (firstname, lastname, email)
VALUES (‘John’, ‘Doe’, ‘john@example.com’)»;
// use exec() because no results are returned
$conn->exec($sql);
echo «New record created successfully»;
> catch(PDOException $e) echo $sql . «
» . $e->getMessage();
>

Источник

PHP MYSQL — Insert into without using column names but with autoincrement field

Obviously, it works fine if the fields are in the same order as the MySQL table fields. But, my table has an auto-increment id as it’s first field. What I want is to fill in all table names but the first (id) one. Suggestions?

5 Answers 5

Just use NULL as your first value, the autoincrement field will still work as expected:

INSERT INTO tblname VALUES (NULL, . 32 Fields . ) 

Very good. I’ve also found an alternate solution as follow: $resultx = mysql_query( «SHOW TABLE STATUS LIKE ‘diretorio'»); $auto_incr_val = mysql_result($resultx, 0, ‘Auto_increment’);

@Paulo: you have no idea what trouble you may open yourself up to by doing that. Use NULL — it’s the way MySQL designed it to work.

It is generally fine to use 0 to trigger auto_increment unless you enable NO_AUTO_VALUE_ON_ZERO mode. NULL, 0, and DEFAULT are all accepted values to trigger this. See dev.mysql.com/doc/refman/5.6/en/… for more info.

Similarly, NULL can be used for other columns which have default values defined, e.g. CURRENT_TIMESTAMP.

Insert NULL into the auto-increment field.

I recommend that unless this is a hack script, you use field names. The rationale is that your code will break if you ever add a field to the table or change their order.

Instead, be explicit with field names, and it will go much better in the future.

Use NULL or 0 to insert an auto-incremented value as shown below:

 -- Here INSERT INTO tblname VALUES (NULL, . 32 Fields . ) 

 -- Here INSERT INTO tblname VALUES (0, . 32 Fields . ) 

We should omit any column values when we try without column name in insert query,

Advise if above information is wrong.

Here’s a simple shortcut that I’ve used:

$fieldlist=$vallist=''; foreach ($_POST as $key => $value) < $fieldlist.=$key.','; $vallist.='\''.urlencode($value).'\','; >$fieldlist=substr($fieldlist, 0, -1); $vallist=substr($vallist, 0, -1); $sql='INSERT INTO customer_info ('.$fieldlist.') VALUES ('.$vallist.')'; 

Please note that this code would be vulnerable to SQL Injection and should be modified to accommodate PDO’s, but I felt this simplified script would more directly answer the question with regards to the originally posted code.

This code is vulnerable to SQL Injection. Read more here please: stackoverflow.com/questions/11939226/…

@IliaRostovtsev — While I agree with you and have switched over to PDO’s in order to avoid SQL injection, I feel there’s a benefit to answering the question as it pertains to the posted code

Yes, probably. It also worth mentioning aboug the possible problems. (I didn’t downvote, just in case)

Источник

PHP mySQL — Insert new record into table with auto-increment on primary key

Wondering if there is a shorthand version to insert a new record into a table that has the primary key enabled? (i.e. not having to include the key column in the query) Lets say the key column is called ID, and the other columns are Fname, Lname, and Website

$query = "INSERT INTO myTable VALUES ('Fname', 'Lname', 'Website')"; 

you’ve got the answer right there. Your example should work as expected (aasuming ID is an autoincrement).

I recommend against this type of insert sytnax in applications because if you add columns in the future (or change column order), you must come back and change your SQL statements, even if those columns have default values. If it’s a one time DB update, then it’s no big deal.

5 Answers 5

$query = "INSERT INTO myTable VALUES (DEFAULT,'Fname', 'Lname', 'Website')"; 

Also, you can specify the columns, (which is better practice):

$query = "INSERT INTO myTable (fname, lname, website) VALUES ('fname', 'lname', 'website')"; 

Yes I know it is better to set each column. But the table this is for will not be altered so I was just curious how to use the least amount of code to do the trick. Thanks!

«Will not be altered» is a pretty huge assumption. Maybe it’s a one-off table and you’re dropping it when you’re done with this current task, but otherwise who knows when business requirements will change in unexpected ways. In the vast majority of cases, the person who has to maintain the system next will greatly appreciate it if you use the second form with the column names.

Источник

PHP5 MySQL Вставить данные

После создания базы данных и таблицы мы можем начать добавлять данные.

Ниже приведены некоторые синтаксические правила:

• SQL запрос должен быть заключен в кавычки
• Строковых значений в SQL запросе должны быть заключены в кавычки
• Цифровые значения не должны заключатся в кавычки
• Слово NULL не должено заключатся в кавычки

Заявление INSERT INTO используется, чтобы добавить новые записи к таблице MySQL:

Чтобы узнать больше о SQL, пожалуйста, посетите наш Учебник SQL.

В предыдущей главе, мы создали пустую таблицу с именем «MyGuests» , с пятью столбиками: «id» , «firstname» , «lastname» , «email» и «reg_date» . Теперь давайте заполним таблицу данными.

Примечание: AUTO_INCREMENT ( «id» столбец) или TIMESTAMP ( «reg_date» столбец), не нужно указывать в SQL запросе; MySQL автоматически добавит заявление.

Следующие примеры добавляют новую запись в таблицу «MyGuests» :

Пример MySQLi — объектно-ориентированный

// Подключение к MySQL
$servername = «localhost»; // локалхост
$username = «root»; // имя пользователя
$password = «»; // пароль если существует
$dbname = «myDB»; // база данных

// Создание соединения
$conn = new mysqli($servername, $username, $password, $dbname);
// Проверка соединения
if ($conn->connect_error) die(«Ошибка подключения: » . $conn->connect_error);
>

// Установка данных в таблицу
$sql = «INSERT INTO MyGuests (firstname, lastname, email)
VALUES (‘Андрей’, ‘Щипунов’, ‘and-shhipunov@mail.ru’)»;

if ($conn->query($sql) === TRUE) echo «Успешно создана новая запись»;
> else echo «Ошибка: » . $sql . «
» . $conn->error;
>

// Закрыть подключение
$conn->close();
?>

Пример MySQLi — процессуальный

// Подключение к MySQL
$servername = «localhost»; // локалхост
$username = «root»; // имя пользователя
$password = «»; // пароль если существует
$dbname = «myDB»; // база данных

// Создание соединения
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Проверка соединения
if (!$conn) die(«Подключение не удалось: » . mysqli_connect_error());
>

// Установка данных в таблицу
$sql = «INSERT INTO MyGuests (firstname, lastname, email)
VALUES (‘Андрей’, ‘Щипунов’, ‘and-shhipunov@mail.ru’)»;

if (mysqli_query($conn, $sql)) echo «Успешно создана новая запись»;
> else echo «Ошибка: » . $sql . «
» . mysqli_error($conn);
>

// Закрыть подключение
mysqli_close($conn);
?>

Пример PDO

// Подключение к MySQL
$servername = «localhost»; // локалхост
$username = «root»; // имя пользователя
$password = «»; // пароль если существует
$dbname = «myDBPDO»; // база данных

// Создание соединения и исключения
try $conn = new PDO(«mysql:host=$servername;dbname=$dbname», $username, $password);
// Установить режим ошибки PDO в исключение
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Установка данных в таблицу
$sql = «INSERT INTO MyGuests (firstname, lastname, email)
VALUES (‘Андрей’, ‘Щипунов’, ‘and-shhipunov@mail.ru’)»;

// Используйте exec (), поскольку результаты не возвращаются
$conn->exec($sql);
echo «Успешно создана новая запись»;
>
catch(PDOException $e)
echo $sql . «
» . $e->getMessage();
>

// Закрыть подключение
$conn = null;
?>

Источник