Im trying to create some temporary tables in mysql using php

Question

Hi I'm trying to create some temporary tables in mysql using php

my code:

<?php
mysql_connect("localhost","root","sahan");//database connection
mysql_select_db("callcenter");

$maketemp="create table #temp1 (id varchar(50),date varchar(50),csc varchar(50),effectedareas varchar(50),agent varchar(50))";

$data1 = mysql_query($maketemp)
 or die(mysql_error());

?>

but it gives an error "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1"

My MySQL version is as below

MySQL Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (i686) using readline 5.1

I'm not much fluent in php coding please can some one help me??

[MySQL Temporary Tables](http://www.tutorialspoint.com/mysql/mysql-temporary-tables.htm) — bummi, Sep 23 '14 at 05:33

score 1 · Answer 1 · edited May 23 '17 at 12:05

1

I do not know why you want a table name like that but you need indentifier quotes (backticks) for it to work.

`#temp1`

You need quotes or else it will considered a comment. #

$maketemp = "CREATE TABLE `#temp1` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `date` varchar(50),
    `csc` varchar(50),
    `effectedareas` varchar(50),
    `agent` varchar(50),
    PRIMARY KEY (`id`)
) COLLATE = 'utf8_general_ci'
";

Please refrain from using mysql_* functions, they are deprecated. Click here.

edited May 23 '17 at 12:05

Community

1
1

answered Sep 23 '14 at 05:33

Kevin

41,694
12
53
70

1

Just came here to say that, +1 – Azrael Sep 23 '14 at 05:34
1

you did see: " temporary table "? – bummi Sep 23 '14 at 06:04
@bummi yes quite enlightening also, if you wan't you can also post it as an answer. +1 – Kevin Sep 23 '14 at 06:06
@bummi its just odd, why would anyone name their table with a hashtag. for that i do not know – Kevin Sep 23 '14 at 06:07
It's usual SQL-Server syntax for temporary tables. – bummi Sep 23 '14 at 06:09

score 0 · Answer 2 · answered Sep 23 '14 at 05:47

0

Here problem is declaring varchar for primay key with auto increment. So make id datatype to int. then its working

$maketemp = "CREATE TABLE `#temp1` (
    `id` VARCHAR(50) NOT NULL AUTO_INCREMENT,
    `date` varchar(50),
    `csc` varchar(50),
    `effectedareas` varchar(50),
    `agent` varchar(50),
    PRIMARY KEY (`id`)
) COLLATE = 'utf8_general_ci'
";

If you want to create temporary table, In my sql, there is temporary table functionality as http://www.mysqltutorial.org/mysql-temporary-table/

answered Sep 23 '14 at 05:47

Vamsi Krishna

161
10

Where did you find "varchar for primay key with auto increment" in the question? – bummi Sep 23 '14 at 05:57
We can create varchar with auto increment using triggers. – Vamsi Krishna Sep 23 '14 at 09:57
CONCAT('PROD', LPAD(LAST_INSERT_ID(), 3, '0')); – Vamsi Krishna Sep 23 '14 at 09:58

score 0 · Answer 3 · answered Sep 23 '14 at 06:21

0

Taken from MySQL Temporary Tables
Temporary tables were added in MySQL version 3.23. If you use an older version of MySQL than 3.23, you can't use temporary tables, but you can use heap tables.

You are mixing up SQLServer syntax with MySQL syntax. While you are able to create a temporary table in SQLServer with #table or a gloabal temporary table with ##table in MySQL you have to use the keyword TEMPORARY

CREATE TEMPORARY TABLE temp1 (
         id varchar(50)
         ,date varchar(50)
         ,csc varchar(50)
         ,effectedareas varchar(50)
         ,agent varchar(50)
         )

answered Sep 23 '14 at 06:21

bummi

27,123
14
62
101

thank you bummi, this code works on mysql of course. but in php it doesnt work. it gives an error – user3916665 Sep 23 '14 at 06:41
I'm not familiar with PHP so I can just assume that the link provided by @Ghost [Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) might answer the PHP part of your question. – bummi Sep 23 '14 at 06:49

Im trying to create some temporary tables in mysql using php

3 Answers3

Linked