CodeIgniter Forums
need help with insert into a table via a select - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: need help with insert into a table via a select (/thread-23562.html)



need help with insert into a table via a select - El Forum - 10-14-2009

[eluser]Unknown[/eluser]
Hi All,

I have a very complex query in a legacy app that is taking too long to do. My plan for improving it is to create temporary table containing the data for the report so I can do summarizations easily against it. When I do the insert I get a strange error:

Undefined table data

I can run the same query successfully in phpmyadmin. Please help soon.

Here is my code:

if (! $this->db->table_exists('adams')) {
$sql = "CREATE TEMPORARY TABLE adams (
id INT NOT NULL AUTO_INCREMENT,
locationproduct INT,
location INT,
name VARCHAR(100),
address VARCHAR(150),
city VARCHAR(100),
state VARCHAR(2),
zip VARCHAR(5),
productid INT,
productname VARCHAR(100),
marketid INT,
marketname VARCHAR(150),
PRIMARY KEY(id),
UNIQUE (id)
)";
echo "$sql<br />";
$query = $this->db->query($sql);
}

if ($this->db->table_exists('adams')) {
echo "Before Delete<br />";
// make sure its empty
$sql = "DELETE FROM adams WHERE 1";
$query = $this->db->query($sql);

echo "Before First Insert<br />";
// make the initial fastest selection into that table

$sql = "INSERT INTO adams (locationproduct, location, productid) SELECT locations_products.id, locations_products.location, locations_products.product FROM locations_products";

echo "$sql<br />";
$query = $this->db->query($sql);


Thanks,

Dajomasoft


need help with insert into a table via a select - El Forum - 10-14-2009

[eluser]Unknown[/eluser]
sorry, forget about the previous post. I discovered my problem and it was me! Smile