Mysql create table and insert at the same time
I have been trying to figure out how I can bind these two Mysql(requests(?)) into one with no success. Basically I want it to work so when I create the table it should also add the values 0 and 0 to «start» and «end» rows. But I still want the «Create table if not exists» to be in effect for the INSERT INTO. So if the table exist don’t INSERT either.
1 Answer 1
You could do that with following single statement:
CREATE TABLE IF NOT EXISTS `$id` ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `start` VARCHAR(10) NOT NULL, `end` VARCHAR(10) NOT NULL, `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB SELECT '0' AS `start`, '0' AS `end`; You use the CREATE TABLE . SELECT syntax with selecting not from a table, but constant values and get the autoincrement value and the current_timestamp by default.
as of MySQL 5.5.6 or newer, see following excerpt from the manual, CREATE TABLE . SELECT:
As of MySQL 5.5.6, handling of CREATE TABLE IF NOT EXISTS . SELECT statements was changed for the case that the destination table already exists. This change also involves a change in MySQL 5.1 beginning with 5.1.51.
Previously, for CREATE TABLE IF NOT EXISTS . SELECT, MySQL produced a warning that the table exists, but inserted the rows and wrote the statement to the binary log anyway. By contrast, CREATE TABLE . SELECT (without IF NOT EXISTS) failed with an error, but MySQL inserted no rows and did not write the statement to the binary log.
MySQL now handles both statements the same way when the destination table exists, in that neither statement inserts rows or is written to the binary log. The difference between them is that MySQL produces a warning when IF NOT EXISTS is present and an error when it is not.
This change means that, for the preceding example, the CREATE TABLE IF NOT EXISTS . SELECT statement inserts nothing into the destination table as of MySQL 5.5.6.
How to insert values into a mysql database using php
I need help understanding how to insert values into a mysql database. I understand I will need to write a INSERT statement of the data that I get from the user. But I dont really understand where to put this insert statement and how to get it to run. Do I use pg_prepare and pg_execute? If someone could just help me set my code up to where I would run the insert statement I would greatly appreciate it! Thanks for the help in advance. HTML code
So there are basically 3 values that are inserted by the user, and I want to insert them all into the database when the submit button is pressed! PHP Code
connect_error)< exit('CON Error: ' . $mysqli->connect_errno . ' ' . $mysqli->connect_error); > $db = $_POST['database']; $evalue = $_POST['evalue']; $sequence = $_POST['BlastSearch']; print "Connected! Host info: " . $mysqli->host_info . "
\n"; $mysqli->close(); ?> 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();
>