Handling database table fields in php - Creating dynamic table using php

Question

I would like to create a dynamic table with fields like this.

    table_name: book_list

    book1(int(2))   book2(int(2))....   book44(int(2)

I have a php code like this.

    <?php
    $dbhost = 'localhost';
    $dbuser = 'root';
    $dbpass = '';
    $conn = mysql_connect($dbhost, $dbuser, $dbpass);
    for($i=1;$i<=44;$i++){

    $sql = 'CREATE TABLE IF NOT EXISTS `book_list` (
      book_'.$i. 'int(2) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1';

    mysql_select_db('book_db');
    $retval = mysql_query( $sql, $conn );
    }
    ?>

The above code needs some tweaks, that is to be done for generating the dynamic table.

Any help will be more appreciable.

Thanks, Kimz

PS: I know the MYSQL is getting depreciated but still this is for my client and he wants only mysql and not pdo or mysqli. looks crazy. but still

Once table get created you need a alter query to add more columns. — Rikesh
– Rikesh, Commented Jun 18, 2014 at 8:55
yup, how? i'm not big into php and loops. could you help with the above code. — user3350885
– user3350885, Commented Jun 18, 2014 at 8:56
do you want to create new table or add column in existing table? — Sadikhasan
– Sadikhasan, Commented Jun 18, 2014 at 8:58
Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. — Kermit
– Kermit, Commented Jun 23, 2014 at 2:06

Adam Bengtsson · Accepted Answer · 2014-06-18 09:09:18Z

1

You should start building your query outside of the for-loop and then just loop over the fields you want. Something like this (not tested):

<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);

$sql = 'CREATE TABLE IF NOT EXISTS `book_list` (';

for($i=1;$i<=44;$i++){
    $sql .= 'book_'.$i. ' int(2) NOT NULL';
    if($i < 44) {
        $sql .= ', ';
    }
}

$sql .= ') ENGINE=InnoDB DEFAULT CHARSET=latin1';

mysql_select_db('book_db');
$retval = mysql_query( $sql, $conn );

?>

edited Jun 18, 2014 at 9:09

answered Jun 18, 2014 at 8:59

Adam Bengtsson

1561 gold badge2 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3350885 Over a year ago

wow, wow. wow. excellent. one more thing is . the code is simply superb. one small change. change $sql .= 'book_'.$i. ' int(2) NOT NULL' to $sql .= 'book_'.$i. ' int(2) NOT NULL';

Mateusz Odelga · Accepted Answer · 2014-06-18 08:57:48Z

0

You can use some ORM tools, e.g. http://redbeanphp.com/

But the best option is use 3NF of database.

Instead adding bookNo as column to table, add column Number

sample.

CREATE TABLE IF NOT EXISTS `book_list` (
   number int(2) NOT NULL 
   book int(2) NOT NULL
)

References

answered Jun 18, 2014 at 8:57

Mateusz Odelga

3542 silver badges7 bronze badges

Collectives™ on Stack Overflow

Handling database table fields in php - Creating dynamic table using php

2 Answers 2

1 Comment

References

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

References

Comments

Your Answer

Sign up or log in

Post as a guest

Related