Insert

This query keeps failing and i can't figure out why... any ideas?


//get content form post

$w1 = $_POST["w1"];
$w2 = $_POST["w2"];
$w3 = $_POST["w3"];
$w4 = $_POST["w4"];
$w5 = $_POST["w5"];
$w6 = $_POST["w6"];
$w7 = $_POST["w7"];
$w8 = $_POST["w8"];
$s1 = $_POST["s1"];
$s2 = $_POST["s2"];
$s3 = $_POST["s3"];
$s4 = $_POST["s4"];
$s5 = $_POST["s5"];
$s6 = $_POST["s6"];
$s7 = $_POST["s7"];
$s8 = $_POST["s8"];
$p1 = $_POST["p1"];
$p2 = $_POST["p2"];
$p3 = $_POST["p3"];
$p4 = $_POST["p4"];
$p5 = $_POST["p5"];
$p6 = $_POST["p6"];
$p7 = $_POST["p7"];
$p8 = $_POST["p8"];


include ("dbConfig.php");

//Prepare Query
$clearTable = "DELETE * FROM `main_page`";
$mainPageResult = mysql_query($clearTable);

if ( !mysql_insert_id() )
{
//Redirect with fail
echo '<META HTTP-EQUIV="Refresh" Content="0; URL=index.php?selection=main&msg=fail">';
}

$mainPageQ = "INSERT INTO `main_page` (w1,w2,w3,w4,w5,w6,w7,w8,s1,s2,s3,s4,s5,s6,s7,s8,p1,p2,p3,p4,p5,p6,p7,p8) VALUES (".$w1.", ".$w2.", ".$w3.", ".$w4.", ".$w5.", ".$w6.", ".$w7.", ".$w8.", ".$s1.", ".$s2.", ".$s3.", ".$s4.", ".$s5.", ".$s6.", ".$s7.", ".$s8.", ".$p1.", ".$p2.", ".$p3.", ".$p4.", ".$p5.", ".$p6.", ".$p7.", ".$p8.")";
// Run query
$mainPageResult = mysql_query($mainPageQ);


if ( !mysql_insert_id() )
{
//Redirect with fail
echo '<META HTTP-EQUIV="Refresh" Content="0; URL=index.php?selection=main&msg=fail">';
}




I am trying to erase the contents of the row and insert these in its place

all contents are numbers (int)

Here is the insert statement for the database table (if you need it)

INSERT INTO `[SERVERNAME`.`main_page`
(`id`,
`w1`,
`w2`,
`w3`,
`w4`,
`w5`,
`w6`,
`w7`,
`w8`,
`s1`,
`s2`,
`s3`,
`s4`,
`s5`,
`s6`,
`s7`,
`s8`,
`p1`,
`p2`,
`p3`,
`p4`,
`p5`,
`p6`,
`p7`,
`p8`)
VALUES
(
{id: INT},
{w1: INT},
{w2: INT},
{w3: INT},
{w4: INT},
{w5: INT},
{w6: INT},
{w7: INT},
{w8: INT},
{s1: INT},
{s2: INT},
{s3: INT},
{s4: INT},
{s5: INT},
{s6: INT},
{s7: INT},
{s8: INT},
{p1: INT},
{p2: INT},
{p3: INT},
{p4: INT},
{p5: INT},
{p6: INT},
{p7: INT},
{p8: INT}
);

All help appreciated!

Have you echoed $mainPageQ to the browser to see what the actual query about be run actually is?

I did that, but i can not point out what the problem actually is... All i want to do is to replace all the content in the row (id = 1) and put the content that the user submits.

Is there an easier way to do this?

So show us what the echo of $mainPageQ looks like.

You really have only ONE record in this MAIN table???

It looks like:
INSERT INTO `main_page` (w1,w2,w3,w4,w5,w6,w7,w8,s1,s2,s3,s4,s5,s6,s7,s8,p1,p2,p3,p4,p5,p6,p7,p8) VALUES (1, 2, 3, 4, 5, 6, 0, 0, 13, 14, 15, 16, 17, 0, 0, 0, 7, 8, 9, 10, 11, 12, 19, 20)

and yeah, that is not the "main" table, its supposed to contain the information about the content that shows up in the main page.

would you supply us with a show create table statement please. I think you may have other issues going on such as a need for some normalisation.

bazz

CREATE TABLE `main_page` (
`id` int(11) NOT NULL DEFAULT '1',
`w1` int(11) NOT NULL,
`w2` int(11) NOT NULL,
`w3` int(11) NOT NULL,
`w4` int(11) NOT NULL,
`w5` int(11) NOT NULL,
`w6` int(11) NOT NULL,
`w7` int(11) NOT NULL,
`w8` int(11) NOT NULL,
`s1` int(11) NOT NULL,
`s2` int(11) NOT NULL,
`s3` int(11) NOT NULL,
`s4` int(11) NOT NULL,
`s5` int(11) NOT NULL,
`s6` int(11) NOT NULL,
`s7` int(11) NOT NULL,
`s8` int(11) NOT NULL,
`p1` int(11) NOT NULL,
`p2` int(11) NOT NULL,
`p3` int(11) NOT NULL,
`p4` int(11) NOT NULL,
`p5` int(11) NOT NULL,
`p6` int(11) NOT NULL,
`p7` int(11) NOT NULL,
`p8` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1$$

thanks, can you elaborate on what the column names mean, please. It will help you later if now, you give them meaningful names. I began my cms a bit like you have and had I not followed advice from someone esle around here, I would be so confused now, where it has grown to 178 tables. :eek:

Anyhoo back you your question, I would approach the issue a bit like this:

assign a new col name for last_updated. so your insert statement now could include 'ON DUPLICATE KEY UPDATE'. then run a query to delete all from main_page where last_updated != (select max(last_updated) from main_page. (pseudo code)

Alternatively, you could assign a 'flag' col to hold a value that distiguishes the new records. then use the other value, in your delete statement and then; set the 'new' value to what the deleted one was.

food for thought I hope.

Thank you for your reply, I think your alternative method will be the best for me since i am not too experienced in MySQL and only know the bare minimum required for me to function. I use alto of reference material when doing MySQL querys because of this.

The column names are there to represent a position of a page s1 = Software 1 and this is for the featured section so they can be changed by the admin in the admin web page. each category (w, S, P) has 8 spaces which can be filled and that's why i need this to be able to change the shown items in those spaces.

So let me know exactly how your alternative method will work.

Thank again!