PHP SELECT statements

dragon2309

dragon2309

P.I Dragon
hi all, perhaps just apj again, open to all. ok, so i have a normal SELECT statement. in the WHERE clause is it possible to put to different entities. For example, one record has two different "colours" assigned to it, for the example these colours are RED and BLUE. Is it possible in the WHERE clause to specify two different fields (colour 1 and colour 2) but with an "OR" , so say the user searches for RED, the query statement would see it as EITHER colour 1 OR colour2 == RED. The sme for blue, i would want it to search both colour 1 and coour 2 to see if either of them match the users search criteria.

Can this be done, apj101, you kinda know my db schema inside out already, and i know its not normal, so can it be done....

Im thinking the SELECT statement would look a little like this

"SELECT * FROM '$cat' WHERE 'colour1' OR 'colour2' = '$colour'"

If the above is achievable, can it be expanded to cover 3, 4, 5, 6 differnt colours or other attributes...

thanks, dragon :)
 
Last edited:
ok, done a bit of researching, how about this:

SELECT * FROM '$cat' WHERE '$colour' IN (colour1, colour2)

would that work...

dragon
 
im not hugely familiar with the IN statement, but i'm fairly sure that will not accomplish your task.

your best option at the moment is the multiple OR statements.


quick quesiton is there a fixed number of colours for each entry, who many
 
apj101 said:
im not hugely familiar with the IN statement, but i'm fairly sure that will not accomplish your task.
Click to expand...
oh apj, have faith in my research, sorry for the late reply btw... i was a bit busy, i did however get time to implement the IN statement, and it works flawlessly, my query is as follows

$query = "SELECT * FROM $cat WHERE $colour IN (colour1, colour2, colour3) AND StockCount > 0 ORDER BY StockCode DESC";

and thats it, yes, i ahve 3 set columns in every table, the third column is rarely used as it is supposed to be main colours only... It worked pretty much first time aswell, as a result the php document is now over 1000 lines long, LMFAO... mainly due to having an IF-echo statement for every single eventuality on the colour menu. there are about 20 colours, lol... and each IF statement contains the entire menu again just with the option SELECTED... its bloomin massive 50.1Kb is the file size

if you want to see it in action the go to www.simplytrue.co.uk/stocklist/colour1.php

if you want i can post the code for you to have a quick browse through, but it is emmensley long and.... whats the word im looking for......well, MASSIVE, lol... its a good job its just for a small home business, nothing corporate, or else this would def not do at all.

dragon
 
i didnt realise that you had a fixed number of colours 3 in this case, seems clearer now. As i mentioned before the schema is not ideal, and now with these colours it is really not ideal lol. for reference (as its too late to do anything now) you should have put the colour in a seperate table and used a linker table to alloacte the colours for each item in the products tables

i will post the correct schema when i get chance.
PS post your (in a file if you want) as i think it maybe time to move to a bit of jscript to get this file size down. Orignally i didn't want to do this as i was more concerned about understandability on you part but i feel you have got your head around this "IF" method well enough to move on
 
he he, i wasnt sure how a file would work, cos if you clicked no a lnik to it it might have started actually processing the PHP, lol, so ill just post it up and you can take it into a PHP file if you want to

get ready for this, its bloody huge

ha ha ha. i got the error "The text that you have entered is too long (55900 characters). Please shorten it to 30000 characters long." so yeh, ill go find shove it in a txt file an upload it,

whats next apj..??

dragon

*EDIT* - ok, download it HERE ---> colour1.txt (50.1Kb)
 
just to let you know, i know NOTHING about javascript, i customised that pink menu a hell of a lot, but only by reading the comments and figuring out what it did, lol
 
jscript is not that tough, its just code that runs on the cleint machine rather than on the server (as is the case in php). i'll try to fiddle with the code (i'll put comments in so you understand)
spend 30mins reading up on jscript try the w3schools web page (may be a bit advanced)
http://www.w3schools.com/js/default.asp
 
ok, ive been reading through the w3schools tutorials and it seems fairly straight forward. im currently looking at IF ELSE statements and its much the same as PHP in a way (and tats wrong, it should be if else, upper case causes errors apparently...) i cant see how doing this is going to cut down size, unless you know something a lot more advanced that im reading, lol, cause it still uses conventional IF statements.... does it not...??

dragon
 
apj, you never got back to me about any javascript, im not pressuring you at all, it works the way it is and this is purely out of ocnvenience and you being VERY helpful....

dragon
 
You must log in or register to reply here.
Back
Top