Using a WHERE clause is not valid on an INSERT INTO statement (for obvious reasons).
[dev.mysql.com...]

Maybe you should be using UPDATE instead?

Thanks FalseDawn

UPDATE was more work but worked in the end.

I have anothe rquestion dont see the point in starting a new thred for it though.

Becuase of the data in the db, most of the fields will only have a number in them like so 4 these are properties with sleep amounts IE 4 is suitable for 4 people, BUT some properties are available for 4 or 6 people, what would be the best way to do this? and what TYPE of mysql column is best

4,6
4/6
4 or 6

How would I build my select statement for this search, currently I am using

select * from table where sleeps ='$sleeps'

I know using LIKE'%$sleeps' but that would find any even with 14 or even 16

Can a select statement be manipulated to suit?

Thanks again

I'd use unsigned tinyint columns (as many as are required) for each "sleeps" value you need.
eg sleepval1, sleepval2, sleepval3...
(up to whatever the maximum number of "sleep values" you think you will need)
Queries can then easily be built - eg

"sleeps at least 4 people" would be:
... WHERE sleepval1>=4 OR sleepval2>=4 OR...

"sleeps 6 people" would be
... WHERE sleepval1=6 OR sleepval2=6 OR...

etc

Of course, if you don't need this flexibility (say you only have a very limited number of combinations, for example 4, 4/6 and 6, i.e. 3 combinations), then you could just use a single numeric column (tinyint again), and represent each combination by a number in that column - eg:
0 = sleeps 4
1 = sleeps 4/6
2 = sleeps 6
...
etc

Although this is arguably not as expandable, it works fine for small sets of combinations.

[edited by: FalseDawn at 5:07 pm (utc) on Oct. 31, 2006]

I think you want a INSERT INTO tbl (a,b,c) SELECT a,b,c from tbl2 where ref = 456

Right?

Hi txbakers

That is correct, BUT I have already done it with a update statement thanks, all data is in.

Back to the sleeps bit, I have done as FalseDawn guided me, adding extra columns to the db sleeps2 sleeps3 etc and updated them to suit.

Now I have had to change my select statements the search works like this. They can select 3 different searches from 2 select boxes, sleeps AND location, it works fine for either sleeps OR location but doesnt work when sleeps AND location are selected, it only gets the location, here is my script for handling the requests.

if (isset($location) &&!$sleeps){
$sql = "select * from data where location ='$location' OR location2 ='$location'";
}
else if (isset($sleeps) &&!$location){
$sql = "select * from data where sleeps ='$sleeps' OR sleeps2 ='$sleeps' OR sleeps3 ='$sleeps' OR sleeps4 ='$sleeps' OR sleeps5 ='$sleeps' OR sleeps6 ='$sleeps'";
}
else{
$sql = "select * from data where location ='$location' OR location2 ='$location' AND sleeps ='$sleeps' OR sleeps2 ='$sleeps' OR sleeps3 ='$sleeps' OR sleeps4 ='$sleeps' OR sleeps5 ='$sleeps' OR sleeps6 ='$sleeps'";
}

The extra location2 is for the same purpose, (its on the border of 2 villages) not that they move the property around ;)

The new additions seem to work ok for example theres one property that sleeps from 8 to 14 so I have setup the respective columns in the db with 8 10 12 14, 8 is the original sleeps column unchanged so when properties get returned in the results they still show the lower figure, I will change that later, when searching for sleeps 10 that property comes up so the sleeps2 sleeps3 etc seems to work.

Is there something I need to change now in one of my if statements?

change:

$sql = "select * from data where location ='$location' OR location2 ='$location' AND sleeps ='$sleeps' OR sleeps2 ='$sleeps' OR sleeps3 ='$sleeps' OR sleeps4 ='$sleeps' OR sleeps5 ='$sleeps' OR sleeps6 ='$sleeps'";

to:

$sql = "select * from data where (location ='$location' OR location2 ='$location') AND (sleeps ='$sleeps' OR sleeps2 ='$sleeps' OR sleeps3 ='$sleeps' OR sleeps4 ='$sleeps' OR sleeps5 ='$sleeps' OR sleeps6 ='$sleeps')";

Note the parentheses.

[edited by: FalseDawn at 4:04 pm (utc) on Nov. 1, 2006]

[edited by: txbakers at 12:18 pm (utc) on Nov. 2, 2006]
[edit reason] fixing code tag [/edit]

Hey FalseDawn

Thanks for the help in this, since I last posted I have completely changed the db, removed sleeps3 slpees4 sleeps5 and just kept sleeps and sleeps2, reason, if a property sleeps 4 or 6 I can enter MIN and MAX then do a BETWEEN search, your example

"sleeps at least 4 people" would be:
... WHERE sleepval1>=4 OR sleepval2>=4 OR...

Made me think a bit harder, ouch, I had also added the () around each bit yesterday already, heres what I am now using and it works perfectly.

if (isset($location) &&!$sleeps){
$sql = "select * from data where location ='$location' OR location2 ='$location'";
}
else if (isset($sleeps) &&!$location){
$sql = "select * from data where $sleeps BETWEEN sleeps AND sleeps2";
}
else{
$sql = "select * from data where (location ='$location' OR location2 ='$location') AND ($sleeps BETWEEN sleeps AND sleeps2)";
}
EDIT I also HAD to make all other properties MIN/MAX fields contain the same numbers rather than blank ie 4 4 or 6 6 so they came back in the results

Thanks for the pointers.
Cheers