Solved

I've got to create a table on the fly based on this loop. How?

Posted on 2014-09-26
4
92 Views
Last Modified: 2014-09-26
Here's my code:

$jorja = "select distinct posted_day from verizon order by posted_day";
$jorja_query=mysqli_query($cxn, $jorja)
or die("Jorja didn't happen.");
while($jorja_row=mysqli_fetch_assoc($jorja_query))
{
  $harry = "CREATE TABLE IF NOT EXISTS $jorja_row['posted_day'] (
    `id` int(11) NOT NULL AUTO_INCREMENT,
  `actor_id` varchar(150) NOT NULL,
  `actor_display_name` varchar(150) NOT NULL,
  `posted_time` varchar(150) NOT NULL,
  `display_name` varchar(150) NOT NULL,
  `geo_coords_0` varchar(150) NOT NULL,
  `geo_coords_1` varchar(150) NOT NULL,
  `location_name` varchar(150) NOT NULL,
  `posted_day` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;";

   $harry_query=mysqli_query($cxn,$harry);
}

Open in new window


I want to create a table based on the distinct "posted_day" values. I'm not getting an error, but I'm not getting my table.

What am I missing?
0
Comment
Question by:brucegust
  • 2
  • 2
4 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 40346803
Wrap the recordset records in {}

$harry = "CREATE TABLE IF NOT EXISTS {$jorja_row['posted_day']} (

Open in new window


Else PHP will just see $jorja_row as the variable.
0
 

Author Comment

by:brucegust
ID: 40346828
Gary, here's my code:

$jorja = "select distinct posted_day from verizon where posted_day<>'0000-00-00' order by posted_day";
$jorja_query=mysqli_query($cxn, $jorja)
or die("Jorja didn't happen.");
while($jorja_row=mysqli_fetch_assoc($jorja_query))
{
extract($jorja_row);
  $harry = "CREATE TABLE IF NOT EXISTS '{$jorja_row['posted_day']}' (
    `id` int(11) NOT NULL AUTO_INCREMENT,
  `actor_id` varchar(150) NOT NULL,
  `actor_display_name` varchar(150) NOT NULL,
  `posted_time` varchar(150) NOT NULL,
  `display_name` varchar(150) NOT NULL,
  `geo_coords_0` varchar(150) NOT NULL,
  `geo_coords_1` varchar(150) NOT NULL,
  `location_name` varchar(150) NOT NULL,
  `posted_day` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;";
echo $harry;

Open in new window


I introduced the curly braces and ran it - no errors, but not table. I threw in the single quotes just to see if that would make a difference, again, no error but no table.

Here's what is "echo-ed..."

TABLE IF NOT EXISTS '2013-11-06' ( `id` int(11) NOT NULL AUTO_INCREMENT, `actor_id` varchar(150) NOT NULL, `actor_display_name` varchar(150) NOT NULL, `posted_time` varchar(150) NOT NULL, `display_name` varchar(150) NOT NULL, `geo_coords_0` varchar(150) NOT NULL, `geo_coords_1` varchar(150) NOT NULL, `location_name` varchar(150) NOT NULL, `posted_day` date NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

What do you think?
0
 
LVL 58

Accepted Solution

by:
Gary earned 500 total points
ID: 40346860
Use backticks around the table name (like in the col names) and remove all the extraneous spaces in the sql, I ran the SQL with these changes and it was fine.

$harry = "CREATE TABLE IF NOT EXISTS `{$jorja_row['posted_day']}` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`actor_id` varchar(150) NOT NULL,
`actor_display_name` varchar(150) NOT NULL,
`posted_time` varchar(150) NOT NULL,
`display_name` varchar(150) NOT NULL,
`geo_coords_0` varchar(150) NOT NULL,
`geo_coords_1` varchar(150) NOT NULL,
`location_name` varchar(150) NOT NULL,
`posted_day` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;";

Open in new window

0
 

Author Closing Comment

by:brucegust
ID: 40346899
Perfect!
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
000webhost.com default error log 1 40
PHP Syntax Error 4 32
Email called spam 5 36
How does PHP Storm display on Linux high resolution laptops? 1 36
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question