not able to create mysql table

Question

i have a table tracker_item that looks like this

tracker_item

id  heading    trackerid
1    name         1
2    location     1
3    age          1
4    candidate    2
5    area         2

I wish to create different database table according to trackerid using the parameters that are below heading. E.g acc to the above table tracker_item i want that 2 tables should get created

table1

id  name location age


table 2
id  candidate  area

The code that i tried is

$sql="SELECT * FROM `tracker_item` where trackerid='".$trackerid."' ";
$result = mysqli_query($con, $sql);
if(mysqli_num_rows($result)>0)
    {
        while($row = mysqli_fetch_assoc($result))
            {
                echo $sql1 = "CREATE TABLE item (id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,

                        ".

                        $row['heading']


                        ."  VARCHAR(30) NOT NULL,


                resume VARCHAR(50)
                    )";

            }
                if (mysqli_query($con, $sql1))
                    {
                        echo "Table created successfully";
                    } 
                else 
                    {
                        echo "Error creating table: " . mysqli_error($con);
                    }  
    }

output for $sql1 that i got was

CREATE TABLE item (id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL, resume VARCHAR(50) )
CREATE TABLE item (id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, location VARCHAR(30) NOT NULL, resume VARCHAR(50) )
CREATE TABLE item (id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, age VARCHAR(30) NOT NULL, resume VARCHAR(50) )

Instead of multiple tables i would like to get o/p of $sql1 look like the following so that a table can be created in database

CREATE TABLE item ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL, location VARCHAR(30) NOT NULL, age VARCHAR(50) )

Can anyone please tell how it can be done

Answer 1

You just need to modify your while loop and do the CREATE before the loop. You only want the loop to add columns via concatenation:

if(mysqli_num_rows($result)>0)
   {
   $sql1 = "CREATE TABLE item (id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, ";
   while($row = mysqli_fetch_assoc($result))
   {
      $sql1 .= $row['heading']."  VARCHAR(30) NOT NULL, ";
   }
   $sql1 .= "resume VARCHAR(50)) ";

   if (mysqli_query($con, $sql1))
   {
       echo "Table created successfully";
   } 
   else 
   {
       echo "Error creating table: " . mysqli_error($con);
   }  
}

Concatenate the entire query in this way and then execute the query.

Output:

CREATE TABLE item (id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL, location VARCHAR(30) NOT NULL, age VARCHAR(30) NOT NULL, candidate VARCHAR(30) NOT NULL, area VARCHAR(30) NOT NULL, resume VARCHAR(50))

---

I hate when people say "I'm not that far along..." or "This site will not be public..." or "It's only for school, so security doesn't matter...". If teachers and professors are not talking about security from day one, they're doing it wrong. Challenge them. They're teaching sloppy and dangerous coding practices which students will have to unlearn later. I also hate it when folks say, "I'll add security later..." or "Security isn't important now..." or "Ignore the security risk...". If you don't have time to do it right the first time, when will you find the time to add it later?