What I want to do is create a table then read a csv file in to populate the table. I’m trying to do this in the style I got from Kevin Yank’s book, “PHP & MySQL: Novice to Ninja”
So the first bit is, create a table from PHP.
This is from Kevin Yank:
// this is for an existing table with the columns joketext and jokedate
if (isset($_POST[‘joketext’]))
{
// log myself in
include $_SERVER[‘DOCUMENT_ROOT’] . ‘/includes/db.inc.php’;
try
{
$sql = 'INSERT INTO jokes SET
joketext = :joketext,
jokedate = CURDATE()';
$s = $pdo->prepare($sql);
$s->bindValue(':joketext', $_POST['joketext']);
$s->execute();
}
So, without any variables at first, I tried this, but when I log in to mysql, no new table:
(The db is allstudents and I have all privileges on this database)
try
{
$sql = ‘CREATE TABLE IF NOT EXIST
19BE2
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
studentnum INT NOT NULL,
studentname TEXT NOT NULL
) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;’;
$s = $pdo->prepare($sql);
$s->bindValue(‘19BE’);
$s->execute();
}
Of course I can log in to mysql and just create the table, but I would like to do that from PHP and have variables for table name and the column names, then populate the table from a csv file. I have quite a few to make!
Any tips please?
Edit:
I also tried this from stackoverflow. I get no error messages from catch or in error.log, but also no new table in mysql.
function makenewtable()
{
$table = $_POST['name'];
try
{
$db = new PDO("mysql:dbname=allstudents;host=localhost", "myusername", "mypassword" );
$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );//Error Handling
$sql ="CREATE TABLE IF NOT EXISTS $table(
ID INT( 11 ) AUTO_INCREMENT PRIMARY KEY,
Prename VARCHAR( 50 ) NOT NULL,
Name VARCHAR( 250 ) NOT NULL,
StreetA VARCHAR( 150 ) NOT NULL,
StreetB VARCHAR( 150 ) NOT NULL,
StreetC VARCHAR( 150 ) NOT NULL,
County VARCHAR( 100 ) NOT NULL,
Postcode VARCHAR( 50 ) NOT NULL,
Country VARCHAR( 50 ) NOT NULL);" ;
$db->exec($sql);
print("Created $table Table.\n");
}
catch(PDOException $e) {
echo $e->getMessage();//Remove or change message in production code
}
header('Location: .');
exit();
}
if (isset($_POST['?name']))
{
makenewtable();
}