PHP help - customised user queries!!!!

dragon2309

P.I Dragon
yeh, im going to go through and put somment blocks through it and break it up a bit, perhaps make it a lot easier to read in future. Thanks for your help. Oh while im here and to not make a new thread, do you know anything about "UNION" or "JOIN" in the query structure... something about joining multiple queries together, i need to do this because i cant for some reason query multiple tables int he usual way:

$query = SELECT * FROM table1, table2, table3 and so on...

that doesn work, comes up with something about "Price" being ambiguous... so yeh, how do i jin single table queries together, or get the one above to work properly...??

and dont code it for me, unless its like corporate style coding.... thanks

dragon :D
 

apj101

VIP Member
of course i know how to join tables for queries, and yes the comma is how its done.
Your problem here is that some fields in the query exist in both tables so when specifying your select conditions, including price, the query does not know whether to use the price field as in table 1 or the one in table 2.
The problem is that your database hasn;t been designed properly, i'm not have a go at your efforts, far from it, but just highlighting that ideally you shouldnt have the same field in both tables. And you should have a unique primary key (often automatically generated) for each item. So i'll try to illustrate as best i can.

You have one table we will call it products_table
In here you list the description of the product under the field = desc
You also have a unique primary key that is unique for each tuple in the table we will call in = key (your RDBMS can assign them incrementally for each item if you want)

You have a second table called Price_table
In here you have 1 field holding the price field called = price
You also have a unique primary key that is unique for each tuple in the table
e.g. so in your db this will have 3 tuple for the 3 prices 1.99, 2.99, and 3.99 (with a unique key for each)

You have a 3rd table between the 2 others called product_price_link
in here you have 2 fields, one contains the unique reference to the Products_table, the other contains the unique reference to the Price_table. So by selecting one product from the products_table you can get its primary reference key, use that key to look up the price reference key in the product_price_link table, and from that you can look up the price in the price_table.

I know it's complicated to write out in words, but its not really that hard, people draw what are called database schema's outlining the structure of the db.

To come back to your original problem, i cant quite figure out your db layout, tell me the names of all your tables and the fields and data within each of them (not all the data just a description of what the field is holding):)
 

dragon2309

P.I Dragon
well, i have a table for each type of jewellery table names are as follows:

ad_brac
ch_brac
ad_neck
ch_neck
ad_set
ch_set


the list goes on, but that will do for now.

in each table i have the same set of fields (which i think is going to be my falling point. Fields are as follows:

StockCode (my primary key)
Description
Price
StockCount (used to govern if it is returned in the queries)
URL
IMG
IMGURL

pretty self explanatory really, URL is the whole bit with the <a href... hyprlink in it, IMG is the location of the image, IMGURL is the URL that the hyperlink in the image points to (different to "URL")

so, yeh, i think that having the same fields in all tables is gonna give me some grief now.... and im in no position at the moment to start re-designing my database....

dragon
 

apj101

VIP Member
so, yeh, i think that having the same fields in all tables is gonna give me some grief now.... and im in no position at the moment to start re-designing my database....
your right at this stage development has moved to far along to redesign. I will post the correct/better schema for you later when i have more time so you can see what i should have been made like.

Because of the database layout you dont really have much call for join queries. But in the interest of completeness you can explicitily define the table in which your condition field is sitting by using a (.) period sign. Now like i said you cant really use it in this database but when i post the schema i will tell you how.

ok buddy :)
 

dragon2309

P.I Dragon
hi, thanks for all the help your putting into this, it is really appreciated... the latest page ive been working on is located at

http://www.simplytrue.co.uk/stocklist/querytest.php

ive integrated the menu into it (works best at 1024x768 res) and ive put more options in the drop down boxes, still have to format that text and make it look pretty though.

Again, thanks for all your help, look forward to seeing the schema of hwo it was supposed to be, i made the db a while back, and like now, i had no clue of exactly how it was supposed to be set up, so i figured seperate tables for categories was logical, logic never seems to work with computers.... :)

dragon
 

apj101

VIP Member
to be honest i had a think about it, and i dont think you need any seperate linker tables for this db, it is very simpe. I would have just put everything in one table, and had a flag field to tell what kind of jewellery it is. The create script would look like

CREATE TABLE Product (
Stock_code INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
Description LONGTEXT NULL,
Stock_Level INTEGER UNSIGNED NULL DEFAULT 0,
URL MEDIUMTEXT NULL,
IMGURL VARCHAR(255) NULL,
Price INTEGER UNSIGNED NOT NULL,
Type_2 VARCHAR(20) NOT NULL,
PRIMARY KEY(Stock_code)
);

schema4ud.jpg


all your products would sit in the one table
 

dragon2309

P.I Dragon
that looks simple enough, if i were to implement that and have all products in one table, it would eb a lot of work to begin with, then there would another massive block of work, all my add to cart and BUY buttons are linked to a paypal business account. i would have to log in and go thoguh every single item and change all the item numbers etc...

i think it is far too late in development to begin to implement this. Afterall, it is just a small family business, the website is a sideline to actual sales made by going to public events etc... so it isnt crucial, the current page, posted in my last post is far FAR more advanced than i ever hoped this site would get, so (for now) i thin i will draw the line.

However, i am going to implement a colour selection, this would mean adding a primary and secondary colour field to each item but it would allow users to search for jewellery by colour, and now if i think about it, they arent going to want to search ALL categories are they...?? they most probably know what they are looking for already when they select a colour, like they may need a blue/silver adult bracelet etc... so not being able to query several tables is not a big problem at all

So, for now i think this is the end, undoubtedly i will run into more problems because i lack the knoledge on the technical side of PHP...

An official THANKYOU to apj101, and i would just like to ask "where is cromewell and mgoldb2" in the first post i "called" them and apj answered, subsequently answered EVERY SINGLE question i asked, kudos man.

Thanks, dragon... ;)

*EDIT* - can i just ask what program made that table layout image....??
 
Last edited:

apj101

VIP Member
sure you can ask, i was made in dbdesigner 4.0
this is a great leightwieght app for building whole dbs, it will then compile you create tables for you into mysql and a few others. The app is a bit buggy and could use some more development but its free!
http://www.fabforce.net/dbdesigner4/

also dragon, you really need to add an option within the price drop down for "all prices" and in style for "all styles", because if a user searchs for just one or two items and finds the out of stock sign, then they will quickly lose interest and leave.
You need to make to whole experience as quick and simple as possible.

As for cromewell and mgold I'm sure they are about, we dealt with this pretty quick so they probably didn;t want to get involved mid-fix.

glad to help, just as a thankyou be sure to name your first born child apj :D
 
Last edited:

dragon2309

P.I Dragon
ok, the All Prices one sounds easy enough, kinda, but what did you mean with the ALL Styles, did you mean styles as in Adult Necklace, Child Necklace, Adult Sets etc...

and first bron beign called apj, errrm... ok, i suppose thats erasonable enough, lol

dragon
 

dragon2309

P.I Dragon
ok, fell at the forst hurdle, lol. ALL PRICES, i have to set a value for it in the drop down menu to enter into the query, but i dont actaully know how to include multiple values in a WHERE clause. Query at the moment is:

$query = "SELECT * FROM $cat WHERE Price='$price' AND StockCount > 0";

so what do i actually enter to get multiple results of price... or this another one of those things where i really should have designed my database better.... i've tried it with '£1.99' OR '£2.99' OR '£3.99' etc... but it only ever returns records for the first value in that lot (£1.99). I tiried AND but that didnt work, tried seperating them by commas, that didnt work, any ideas....??

dragon
 

apj101

VIP Member
you need to have an if block that evalutes the price chosen,
if it is = "All prices" then
create a sqlquery that doens't specify a price
else
create a sqlquery that does specify a price just like before


You could do some more fancier coding than that, using not nulls commands and like '%' but thats overkill
 

dragon2309

P.I Dragon
oooh, ok, so i would need to replace the current query with two sepearate queries in an IF statement... sounds simple enough, ill go see now

thanks, dragon
 

dragon2309

P.I Dragon
ok, im getting into this now, i actaully coded it myself for once, woo

this is what ive got as my new query block:

if ($price == 'ALL')
$query = "SELECT * FROM $cat WHERE StockCount >0";
else
$query = "SELECT * FROM $cat WHERE Price='$price' AND StockCount > 0";

looks god to me and seems to work properly, can you confirm. its on the same page as last time...

thanks, dragon
 

apj101

VIP Member
code looks good, you may want to add an ORDER BY in the statement since as the db grows it is likely that the price order will screw up.

if ($price == 'ALL')
$query = "SELECT * FROM $cat WHERE StockCount >0 ORDER BY Price ASC";
else
$query = "SELECT * FROM $cat WHERE Price='$price' AND StockCount > 0 ORDER BY Price ASC";


be sure to change ASC to DESC if you want to order them the other may ;)
 

dragon2309

P.I Dragon
ok, i added the ORDER BY clause into it jsut to give the results some sort of order other than that of the database order. Something has ome to my attention though, when i have been using this... Say you selcted £2.99 - Adult Sets, no results come up, so you then want to change £2.99 to £3.99 to do a different search, but wait a minute, the menu boxes have reverted to the first value.... this being £1.99 and Adult Bracelets.

What i would like is for the boxes to stay the same as what has been selected. Seeing as we are storing the values selected for each box in PHP SELF, surely there is a way to recall those values and have it set the menu positions to those values..... no or is this really advanced stuff....

dragon
 

apj101

VIP Member
you would have to echo the html for the combo box in php. using the value picked up from the post

i'll post more later, gotta go train
 

dragon2309

P.I Dragon
ok, it seems good in theory, but how you would go about implementing it i have no idea... Talk to you later then.

dragon
 

apj101

VIP Member
its easy lets do it step by step, i want you to figure it out :)
first thing to do is move your form code (thats everything in <FORM>....</FORM>
into the php block, and have it echo out to the page
should just be a case of wrapping echo quotes (') around it
 
Top