Struggling now for a day. Need to make a database in PHP (if not existing) and after make sure it is empty (if already was existing). But somehow I probably miss something essential and nothing happens. Looks like it just skips the creation and the delete part altogether.
<?php
error_reporting(E_ALL); ini_set('display_errors', 1);
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
//Database variables
$servername = "localhost";
$username = "Somename";
$password = "Verysecret";
$dbname = "TESTDB";
$temptable = "tablename";
//Open database
$conn = new mysqli($servername, $username, $password,$dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully to database: ",$dbname,"<br>" ;
?><br><?php
//Create table if not existing yet (syntax error here?)
echo "Creating table if non existing","<br>";
$conn->select_db('$dbname');
$sql = "CREATE TABLE IF NON EXIST `{$temptable}` (
`xml_date` datetime,
`xml_duration` int(2),
`xml_boat` VARCHAR(30),
`xml_itinerary` VARCHAR(30),
`xml_dep_arr` VARCHAR(30),
`xml_spaces` INT(2),
`xml_rate_eur` decimal(4,2),
`xml_rate_gbp` decimal(4,2),
`xml_rate_usd` decimal(4,2))";
//This part not showing up in output at all!
if(mysqli_query($conn, $sql)){
echo "Table created successfully";
} else {
echo "Table is not created successfully ";
}
//Deleting rows if table existed already (same syntax error here?)
echo "Making sure table is empty","<br>";
$sql = "DELETE * FROM `{$temptable}`";
mysqli_close($conn);
?>
All I see when I run (Localy with Mamp)is:
Connected successfully to database: TESTDB
Creating table if non existing Making sure table is empty
The Database is not created, when I create it myself in SequelPro before and add some rows.
Help, searching now a day! What am I doing wrong? Lost in quotes, back quotes, double quotes? Overseeing the obvious?
Let's iterate over what you used here.
DELETE *, is invalid since the asterisk is used for SELECT and not DELETE.
The basic syntax is DELETE FROM TABLE WHERE col_x = ? (with optional WHERE clause).
Example:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
Your table creation syntax is incorrect, the basic syntax is:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
as per the documentation.
which uses the keywords IF NOT EXISTS and not your IF NON EXIST.
You also didn't execute the DELETE query.
and check for errors on it also:
Plus, as Chris was so nice to point out in comments; variables don't get parsed in single quotes.
Either remove them from $conn->select_db('$dbname'); as in either
$conn->select_db($dbname);or set in double quotes:
$conn->select_db("$dbname");Edit:
If the goal here is to get rid of the table entirely (after and seeing your DELETE query), then both DELETE and TRUNCATE are not what you want to use here, but DROP TABLE.
Consult the documentation:
Basic example:
DROP [TEMPORARY] TABLE [IF EXISTS]
tbl_name [, tbl_name] ...
[RESTRICT | CASCADE]
Try this , i got the table created
<?php
error_reporting(E_ALL); ini_set('display_errors', 1);
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
//Database variables
$servername = "localhost";
$username = "detecttn_user";
$password = "Azer12345";
$dbname = "detecttn_teststack";
$temptable = "test ";
//Open database
$conn = new mysqli($servername, $username, $password,$dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully to database: $dbname <br>" ;
?><br><?php
//Create table if not existing yet (syntax error here?)
echo "Creating table if non existing","<br>";
$sql ="SHOW TABLES LIKE '$temptable'";
if(mysqli_query($conn, $sql)){
echo "Table exist";
} else {
echo "Table does not exist";
// if table not exist create it
$sql = "CREATE TABLE IF NOT EXISTS $temptable (
`xml_date` datetime,
`xml_duration` int(2),
`xml_boat` VARCHAR(30),
`xml_itinerary` VARCHAR(30),
`xml_dep_arr` VARCHAR(30),
`xml_spaces` INT(2),
`xml_rate_eur` decimal(4,2),
`xml_rate_gbp` decimal(4,2),
`xml_rate_usd` decimal(4,2))";
mysqli_query($conn, $sql);
$sql ="SHOW TABLES LIKE '$temptable'";
if(mysqli_query($conn, $sql)){
echo "Table created";
} else {
echo "Table creation failed";
}
}
//Deleting rows if table existed already (same syntax error here?)
echo "Making sure table is empty","<br>";
$sql2 = "TRUNCATE TABLE $temptable";
if(mysqli_query($conn, $sql2)){
echo "Table is empty";
} else {
echo "Error";
}
mysqli_close($conn);
?>