Cannot create table in PHP

Question

$table_name=strval($new_username).'_msgs';

@ $db=new mysqli('localhost','root','','newdatabase');

$query2="CREATE TABLE ? ( 'msg_id' int(20) auto_increment primary key,'sender'               varchar(50), 'content' varchar(2000), 'date' varchar(50) )";

          $stmt2=$db->prepare($query2);
          $stmt2->bind_param("s",$table_name);
          $stmt2->execute();

It gives this error message"Call to a member function bind_param() on a non-object"

Why are you dynamically creating new tables to begin with?!?!one There's so much wrong in this question on so many levels; the longer you look... — deceze, May 28 '13 at 06:20
To address your problem at hand though: *no placeholders for identifiers, only for data!* You cannot placehold a table name. That should give you some clues as to best practices right there. — deceze, May 28 '13 at 06:26
possible duplicate of [Can PHP PDO Statements accept the table name as parameter?](http://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-name-as-parameter) — Jocelyn, May 28 '13 at 16:29

score 4 · Answer 1 · answered May 28 '13 at 06:19

4

You can just add a field called "to" for example and you can write the receiver name there instead of creating new table for every user. This is bad practice.

make new field like

`receiver` varchar(50)

and you can get his messages with query like this:

SELECT * FROM `msgs` WHERE `receiver`='random_username'

answered May 28 '13 at 06:19

Deepsy

3,769
7
39
71

will it make a query to take long time to load when the no of user increases much???..eg we have 100 user each having 5000 msgs...i wanted to create new table for each user so that their msg will be saved seprately and query will be faster – user2427230 May 28 '13 at 07:02
@user Use **indexes**. A properly indexed database can handle 100 * 5000 rows without even breaking a sweat. – deceze May 28 '13 at 07:53
There are databases with few million rows and the request is served in few miliseconds. – Deepsy May 28 '13 at 19:02

score 0 · Answer 2 · edited May 23 '17 at 12:18

0

Your error says that $stmt is not valid

This is because your query is not getting prepared because of wrong syntax and invalid use of placeholder.

Remove quotes around the column names in create table

$query2="CREATE TABLE ? ( `msg_id` int(20) auto_increment primary key,
                         `sender` varchar(50), 
                        `content` varchar(2000), 
                         `date` varchar(50) 
                        )";

you can always debug the if statement is prepared or not using

$stmt2=$db->prepare($query2) or die($db->error);

You can come to know about the error.

See tutorial here

This was just you tell you debug trick .. In actual you cannot use placeholders for table names and identifiers ..

see this tag

As suggested by @Deepsy .. I also agree that this is bad practise .. This will create number of tables in your database thing which can be done by one table only

edited May 23 '17 at 12:18

Community

1
1

answered May 28 '13 at 06:21

alwaysLearn

6,882
7
39
67

Why downvote Please consider adding comment .. – alwaysLearn May 28 '13 at 06:34
Still missing the biggest problem about the misused placeholder. – deceze May 28 '13 at 06:42
@deceze Thanks for pointing it out . Learned one new thing :) . I dont mind downvote but I should know the reason so I can correct myself – alwaysLearn May 28 '13 at 06:58

score 0 · Answer 3 · answered May 28 '13 at 06:29

Look this

<?php
$mysqli = new mysqli(DB_SERVER,DB_USER,DB_PASS,DB_NAME);

if($mysqli->connect_errno) echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
echo $mysqli->host_info . "\n";

$query = "CREATE TABLE hwt_tools(
    id mediumint(8) NOT NULL AUTO_INCREMENT,
    user_id mediumint(8),
    category_id mediumint(8),
    target_url varchar(255),
    favicon_url varchar(255),
    desc_text text,
    UNIQUE KEY id (id)
);";

//Use this to safely create a table while testing
if(!$mysqli->query($query)) echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;

//Use this to drop existing table and start a new
//if(!$mysqli->query("DROP TABLE IF EXISTS hwt_tools") || !$mysqli->query($query)) echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;

?>

Cannot create table in PHP

3 Answers3