0

I have two tables: 

CREATE TABLE `czujniki` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(20) COLLATE utf8_polish_ci NOT NULL,
  `name` varchar(20) COLLATE utf8_polish_ci NOT NULL,
  `address` varchar(20) COLLATE utf8_polish_ci NOT NULL,
  `desc` varchar(255) COLLATE utf8_polish_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;


CREATE TABLE `temperature` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_czujnik` int(11) NOT NULL,
  `time` int(11) NOT NULL,
  `temp` float NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

Also have 3 values address, time, and temp what is the best way to add this values to table?
Already I do it in PHP in this way: 

$z = mysqli_query($con, "SELECT `id` 
                        FROM `czujniki` 
                        WHERE `address` = {$address}");
  $row = mysqli_fetch_all($z,MYSQLI_NUM);
  $id = $row[0][0]
  $z = mysqli_query($con, "INSERT INTO `temperature` 
                                    (`id_czujnik`, `time`, `temp`) 
                            VALUES ({$id}, {$time}, {$temp})");

Is there a better way to do it?

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
MastaBot
  • 273
  • 2
  • 4
  • 16
  • 1
    Yes, as it is currently script is wide open to [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) 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) – RiggsFolly Feb 21 '18 at 23:38
  • (1) It looks like it'd work, but you could consolidate your queries into a single query with an `insert into ... select` query. (2) Most modern PHP scripts use [PDO](http://php.net/manual/en/ref.pdo-mysql.php) instead of `mysqli`. (3) You can generally ignore SQL injection if the script is only for personal use, but if there's anyway that people you don't trust will in anyway be using the code, then follow @RiggsFolly's advice. – smsalisbury Feb 21 '18 at 23:45
  • I'm not to good with SQL, can you help my build single query? – MastaBot Feb 22 '18 at 00:02

0 Answers0