Php insert with select in value syntax

Question

This php insert code is a part of my php script to import values from csv file. I want insert the fld_id by other value setting up in the csv file. Example of csv content: 123456789,title,description where:

data[0] is 123456789
data[1] is title
data[2] is description

If the insert runing on mysql client, the value is correctly insert, but from php page returns an error: the fld_idcat cannot be null.

This is a part of the php code:

/*other code*/
do {
    if ($data[0]) {
        mysql_query("
                INSERT INTO table_imports 
                (
                    fld_username,
                    fld_idcat,
                    fld_title,
                    fld_desc,
                    fld_userimport
                )
                VALUES
                (
                    '$Username',
                    (select id_cat from table_category where id_cat_random = '".addslashes($data[0])."'),
                    '".addslashes($data[1])."',
                    '".addslashes($data[2])."'
                    'userImport'                        
                )
        ");
    }
} while ($data = fgetcsv($handle,1000,",","'"));
header('Refresh: 6; URL=finishPage.php'); 
die(mysql_error());

/*other code*/

How to correct this php insert to pass the value 123456789 at the variable $data[0] inside the select?

I hope to explain my problem

Thanks

What is the exact error message? That doesn't look like a MySQL error. Your code doesn't even check for errors from MySQL. — Barmar, Mar 20 '17 at 19:02
[Please, don't use `mysql_*` functions in new code](http://stackoverflow.com/questions/12859942/). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [red box](http://uk.php.net/manual/en/function.mysql-connect.php)? Learn about [*prepared statements*](https://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you decide which one is best for you. — John Conde, Mar 20 '17 at 19:04
Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). — John Conde, Mar 20 '17 at 19:04
You don't know what's wrong because you don't check for errors in your code. Never assume the code is always going to work flawlessly. Use [`mysql_error()`](http://php.net/manual/en/mysqi.error.php) to get a detailed error message from the database. — John Conde, Mar 20 '17 at 19:05
I have updated the question. Please read again. The error is this: Column 'fld_idcat' cannot be null. The colum fld_idcat is configred on mysql table "not null". The script work fine without ".addslashes($data[0])." but with ".addslashes($data[0])." returned an error. — Frankie, Mar 20 '17 at 19:25
Try putting a value in there. Even an empty string will work. — tadman, Mar 20 '17 at 19:27
If the (select id_cat from table_category where id_cat_random = '".addslashes($data[0])."') is valorize with (select id_cat from table_category where id_cat_random = '123456789'), insert correctly the fld_id value. My problem is to pass the value to the variable inside the (SELECT ...) — Frankie, Mar 20 '17 at 19:32
@John Conde: Resolved. I have change the csv file format to UTF8. — Frankie, Mar 23 '17 at 20:29

score 0 · Accepted Answer · answered Mar 23 '17 at 20:27

0

For all. I have resolve my problem. The script work fine, but the value in the variable data[0] It was not passed because the csv file format was not UTF8. After converting the csv file format, the script worked properly.

answered Mar 23 '17 at 20:27

Frankie

490
8
23

Php insert with select in value syntax

1 Answers1