SQL statement seems valid...PHP says it's not.

[stimulus]

I have a script in its infant stages that looks at a table with the
following fields:
id smallint(6) NOT NULL auto_increment,
status tinyint(1) NOT NULL default '1',
date char(225) NOT NULL default '',
description char(225) NOT NULL default '',
url char(225) NOT NULL default '',
category char(225) NOT NULL default '',

The code below is designed to:
1. Determine what "categories" are in the table
2. get the 5 newest records from each different category
3. slap them into a variable
4. empty the table of all records
5. insert the records in the variable (step 2) back into the table

I can take the SQL statement the script outputs and it works fine if I just give it directly to mySQL. The script, however, returns the following:

You have an error in your SQL syntax near ';INSERT INTO
`tablename` (status, date, description, url, category) VALUES 
('1'' at line 1

Here's the bulk of the code...anyone see any glaring problems?
Thanks in advance.

// get list of categories in the database
$query = "SELECT DISTINCT `category` from `{$table}`";
$result = mysql_db_query($database, $query, $connection) 
  or die ("Error in query: $query. " . mysql_error());

// iterate through resultset
while(list($category) = mysql_fetch_row($result))
{
	// get 5 newest records in each category and add them to reinsert_sql variable
	$query2 = "SELECT `id`, `status`, `date`, `description`, `url`, `category` FROM `{$table}` 
	WHERE category='$category' order by date DESC LIMIT 0, 5";
	$result2 = mysql_db_query($database, $query2, $connection) 
	  or die ("Error in query: $query2. " . mysql_error());

	while(list($id, $status, $date, $description, $url, $category) = mysql_fetch_row($result2))
	{
	$reinsert_sql .= "INSERT INTO `{$table}` (status, date, description, url, category) VALUES 
	('1', '$date', '$description', '$url', '$category');";
	}
}

$empty_sql = "DELETE FROM `{$table}`";
mysql_db_query($database, $empty_sql, $connection) 
  or die ("Error in query: $empty_sql. " . mysql_error());
mysql_db_query($database, $reinsert_sql, $connection) 
  or die ("Error in query: $reinsert_sql. " . mysql_error());

[milanisti]

in php mode you can't place "insert query" command more than one.
your code must be change like this :

while(list($category) = mysql_fetch_row($result))
{
// get 5 newest records in each category and add them to reinsert_sql variable
$query2 = "SELECT id, status, date, description, url, category FROM {$table} WHERE category='$category' order by date DESC LIMIT 0, 5";
$result2 = mysql_db_query($database, $query2, $connection) or die ("Error in query: $query2. " . mysql_error());
$empty_sql = "DELETE FROM {$table}";
mysql_db_query($database, $empty_sql, $connection) or die ("Error in query: $empty_sql. " . mysql_error());

    while(list($id, $status, $date, $description, $url, $category) = mysql_fetch_row($result2))
    {
    $reinsert_sql = "INSERT INTO `{$table}` (status, date, description, url, category) VALUES ('1', '$date', '$description', '$url', '$category')";

mysql_db_query($database, $reinsert_sql, $connection) or die ("Error in query: $reinsert_sql. " . mysql_error());

    }

}

CMIIW

[stimulus]

hmmm....

That's just seems to empty the table.
I haven't had any luck finding the issue with your code either.

I'm stuck both ways.
:rolleyes:

[stimulus]

in php mode you can't place "insert query" command more than one.

Now that I've been messing with this a while longer, I'm not sure if this is the case. Does anyone else have a definite answer?

For that matter, other ideas for why I can't seem to make either iteration work?

[Weedpacket]

Originally posted by stimulus


Now that I've been messing with this a while longer, I'm not sure if this is the case. Does anyone else have a definite answer?

Definitely is the case; so it won't work because of all the garbage it finds after the INSERT statement (the ; and everything following it).

[stimulus]

makes sense. thanks for clarifying.

I love your sig, by the way.

cheers.