I don't work much with php/mysql, but I need what I thought would be a relatively straightforward task: to check if a table exists and create it if it doesn't. I can't even get a useful error message and there's no table being created in the db. There is obviously something wrong with my syntax.
<?php
session_start();
error_reporting(E_ALL);
ini_set('display_errors', 1);
// 1. CONNECT TO THE DB SERVER, confirm connection
mysql_connect("localhost", "root", "") or die(mysql_error());
echo "<p>Connected to MySQL</p>";
$mysql_connexn = mysql_connect("localhost", "root", ""); // redundant ?
// 2. CONNECT TO THE SPECIFIED DB, confirm connection
$db = "weighttracker";
mysql_select_db($db) or die(mysql_error());
echo "<p>Connected to Database '$db'</p>";
$db_connexn = mysql_select_db($db)or die(mysql_error("can\'t connect to $db"));
// 3. if table doesn't exist, create it
$table = "WEIGHIN_DATA";
$query = "SELECT ID FROM " . $table;
//$result = mysql_query($mysql_connexn, $query);
$result = mysql_query($query, $mysql_connexn);
if(empty($result)) {
echo "<p>" . $table . " table does not exist</p>";
$query = "CREATE TABLE IF NOT EXISTS WEIGHIN_DATA (
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
DATE DATE NOT NULL,
VALUE SMALLINT(4) UNSIGNED NOT NULL
)"
}
else {
echo "<p>" . $table . "table exists</p>";
} // else
?>
The “if not exists” statement used in the creation of the table, first will analyze the list of all tables present in that schema, then if there is no table with the name of the table, which is going to be created, it will successfully create the table, else it will execute the command successfully without creating the ...
Every CREATE DATABASE IF NOT EXISTS statement is replicated, whether or not the database already exists on the source. Similarly, every CREATE TABLE IF NOT EXISTS statement without a SELECT is replicated, whether or not the table already exists on the source.
SELECT column_name FROM INFORMATION_SCHEMA . COLUMNS WHERE TABLE_SCHEMA =[Database Name] AND TABLE_NAME =[Table Name]; If the above query returns a result then it means the column exists, otherwise you can go ahead and create the column.
6. To create a database only if it doesn't already exist, which clause is used? Explanation: The 'CREATE DATABASE' statement supports many optional values. To create a database named 'my_db' only if it doesn't already exist, we write 'CREATE DATABASE IF NOT EXISTS my_db'.
A few things.
There was a missing semi-colon ;
in and at the end of )"
if(empty($result)) {
echo "<p>" . $table . " table does not exist</p>";
$query = "CREATE TABLE IF NOT EXISTS WEIGHIN_DATA (
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
DATE DATE NOT NULL,
VALUE SMALLINT(4) UNSIGNED NOT NULL
)" // <--- right there
which would have caused/thrown a parse error, such as:
Parse error: syntax error, unexpected '}' in...
Amongst other errors as shown in my comments from your originally posted code.
Plus, you were not using mysql_query
in your table creation.
Here is a mysqli_
method, where I commented out your original codes.
Sidenote: You're using ID
for your column in $query = "SELECT ID FROM " . $table;
and yet you create your table and column as id
in lowercase; both lettercase must match.
<?php
session_start();
error_reporting(E_ALL);
ini_set('display_errors', 1);
$DB_HOST = "xxx"; // put your own data
$DB_NAME = "xxx"; // put your own data
$DB_USER = "xxx"; // put your own data
$DB_PASS = "xxx"; // put your own data
$conn = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
if($conn->connect_errno > 0) {
die('Connection failed [' . $conn->connect_error . ']');
}
/*
// 1. CONNECT TO THE DB SERVER, confirm connection
mysql_connect("localhost", "root", "") or die(mysql_error());
echo "<p>Connected to MySQL</p>";
$mysql_connexn = mysql_connect("localhost", "root", ""); // redundant ?
// 2. CONNECT TO THE SPECIFIED DB, confirm connection
$db = "weighttracker";
mysql_select_db($db) or die(mysql_error());
echo "<p>Connected to Database '$db'</p>";
$db_connexn = mysql_select_db($db)or die(mysql_error("can\'t connect to $db"));
// 3. if table doesn't exist, create it
$table = "WEIGHIN_DATA";
$query = "SELECT ID FROM " . $table; // that should be id and not ID
//$result = mysql_query($mysql_connexn, $query);
$result = mysql_query($query, $mysql_connexn);
*/
$table = "WEIGHIN_DATA";
$query = "SELECT ID FROM " . $table; // that should be id and not ID
//$result = mysql_query($mysql_connexn, $query); // your original code
// however connection comes last in mysql method, unlike mysqli
$result = mysqli_query($conn,$query);
if(empty($result)) {
echo "<p>" . $table . " table does not exist</p>";
$query = mysqli_query($conn,"CREATE TABLE IF NOT EXISTS WEIGHIN_DATA (
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
DATE DATE NOT NULL,
VALUE SMALLINT(4) UNSIGNED NOT NULL
)");
}
else {
echo "<p>" . $table . "table exists</p>";
} // else
?>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With