Looking for help in getting proper search results from two MySQL tables

[percept]

I am building a site where there are several travel related products in a MySQL database. Each product has "x" amount of items. For example, Harrison Hot Springs Hotel has 3 items - Cash price, Points, and Points plus Cash.

I need to do a search on "items" that are equal to or less than the number of Points (item_points) and just can't seem to get it right. Their are two tables involved "products" and "prod_items".

This is the code I am using to get search results by product category and it works just fine. I'm sorry I've tried to indent my code properly so it's easier to read but don't know how to do that here.

//get product info and show
$get_prods = "select id, prod_name, city, prod_brief, valid_m, valid_d, valid_y, expiry_m, expiry_d, expiry_y from products where category='$category' order by city, prod_name";
$get_prods_res = mysql_query($get_prods) or die(mysql_error());

while ($prods = mysql_fetch_array($get_prods_res)) {
$prod_id = $prods[id];
$prod_name = stripslashes($prods[prod_name]);
$city = stripslashes($prods[city]);
$prod_brief = stripslashes($prods[prod_brief]);
$valid_m = $prods[valid_m];
$valid_d = $prods[valid_d];
$valid_y = $prods[valid_y];
$expiry_m = $prods[expiry_m];
$expiry_d = $prods[expiry_d];
$expiry_y = $prods[expiry_y];

echo "$prod_name - $city - <a href=\"prod_details.php?prod_id=$prod_id\">Details</a><br><br>
$prod_brief<br><br>
Purchase options for this product are valid until $expiry_m $expiry_d, $expiry_y <br>";

$prod_name = addslashes($prods[prod_name]);

//get item codes
$get_codes = "select item_code, item_desc, item_points, item_cash from prod_items where prod_name = '$prod_name' order by item_code";
$get_codes_res = mysql_query($get_codes) or die(mysql_error());

if (mysql_num_rows($get_codes_res) > 0) {
while ($codes = mysql_fetch_array($get_codes_res)) {
$prod_name = stripslashes($prods[prod_name]);
$item_code = $codes['item_code'];
$item_desc = $codes['item_desc'];
$item_desc = stripslashes($codes['item_desc']);
$item_points = $codes['item_points'];
$item_points = number_format($item_points);
$item_cash = $codes['item_cash'];
$item_cash = number_format($item_cash, 2);

if ($item_points == 0) {
echo "$item_code - $item_desc - \$$item_cash<br>";
}
elseif ($item_cash == 0.00) {
echo "$item_code - $item_desc - $item_points points<br>";
}
else {
echo " $item_code - $item_desc - $item_points points plus \$$item_cash <br>";
}
}

}

}

This is at http://www.saveontravelbc.com/index_2.php in case it helps to look.

Is there a way to modify this code to achieve my search results? or should I be starting from scratch somehow? Please, any help would be greatly appreciated.

[raf]

I'm sorry I've tried to indent my code properly so it's easier to read but don't know how to do that here.
Use the php button for that, or insert the tags [_php_] and [_/php_] (without the underscores.
I need to do a search on "items" that are equal to or less than the number of Points (item_points) and just can't seem to get it right. Their are two tables involved "products" and "prod_items".
Use a join --> a regular inner join. This will include all selected fields from both tables, for all records where the joined fields are equal. In your case (if you would only filter on the item_points), it will take all records from the prod_items table that meet the conditionin the where clause, and will then look inside the productstable if there is a record (one or more) with that productname. If no identical productname is found in the productstable, the record is not included. If 1 record is found inside the productstable with that name, the record from the prod_items table is included and the fields from the record in the productstable with the same productname, are added.
If more then one record is found, a record is included for each possible combination between the tables (so if you would have 5 records with the same productname inside products, and 50 items in your prod_itemtable with that productname, then you could end up with 250 records.)

But you realy should not include the product_name inside the prod_items table. You should include the id from the products table. And this id should be the primary key of that table, and should be a column of type auto-num. Or else you need to create an index on that id-column and specify that all values need to be unique. You then use the id inside the prod_itemstable to make the join. You need to use the id becaue
- Joins on numerical, indexed, variables are performed much quicker;
- You then avoid that duplicate productsnames cause the recordset to explode with unwanted records
- You then only need to update one record in one table if the productname needs to be changed
- You now have unnescecary data-redundancy

Your description doesn't quite match the querys you used, because if you only need the items, based on the point-values, then why include the category in the where clause? And i nowhere see you use a cutof for the points (you match against 0 but that's it ..)
Supposing that you only need the items, form a specific productcategory, with points not higher then $points (a variable you need to set to your cutof), then you would need a query like (you maybe need to change the order by clause or so). I am assuming that prod_items.item_points is a numerical column

$get_prods = "SELECT products.id, products.prod_name, products.city, products.prod_brief, products.valid_m,
products.valid_d, products.valid_y, products.expiry_m, products.expiry_d, products.expiry_y,
prod_items.item_code, prod_items.item_desc, prod_items.item_points, prod_items.item_cash
FROM products INNER JOIN prod_items ON products.prod_name=prod_items.prod_name
WHERE products.category='" . $category ."' and prod_items.item_points <=" . $points . "
ORDER BY products.city, products.prod_name";

This will first filter on the category AND on the items-points. Then look for joining records. So then you have all fields for all records in 1 query, and you don't need the 2° query inside the first loop (you should only use nested querys if there realy is no other option).

There is no nead to store all variables from the recordset into variables. Just refer to them like $prods[valid_m]
You should also check if the recordset isn't empty (--> if no matching records are returned) before your while loop. So your code should be

$get_prods = "SELECT products.id, products.prod_name, products.city, products.prod_brief, products.valid_m,
products.valid_d, products.valid_y, products.expiry_m, products.expiry_d, products.expiry_y,
prod_items.item_code, prod_items.item_desc, prod_items.item_points, prod_items.item_cash
FROM products INNER JOIN prod_items ON products.prod_name=prod_items.prod_name
WHERE products.category='" . $category ."' and prod_items.item_points <=" . $points . "
ORDER BY products.city, products.prod_name";
$result = mysql_query($get_prods) or die(mysql_error());

if (mysql_num_rows($result) == 0){
echo ('There are no records for category ' . $category . ' with ' . $points . ' points or less.
Please select other values.');
} else {
while ($row = mysql_fetch_array($result)) {
//your outputcode for both the products and items
}
}
mysql_free_result($result);


(i've used $row and $result because these are generaly used as recordarray-name and resultID-name, but you can replace them by whatever names you want. I usually only do that when i have more then 1 recordset open, and even then, I keep the $result and $row part and just add something to it ($resultitems and $rowitems etc . Other php-coders instantly know then $resultezerzed is a recordset, and that $rowfdfs is a record. Wheras $prods[valid_m] or (even worse) $valid_m would require some tracking and will probably cause more errors)

[percept]

raf I can't thank you enough... you have obviously spent "time" on helping me here... I am going to follow all of your recommendations in order to get it right the first time... I'm self teaching myself from a couple of books and they only go so far.

I need to get some sleep and then start fresh... I will let you know how I make out.

Shaking your hand with extreme gratitude...
percept

[percept]

I've changed the prod_items table to include the product id rather than name as you suggested and changed on my scripts for doing searches and it works fine... but I'm seeming to have problems inserting the id into the prod_items table when I insert a new product and it's items... the code I'm using for this is:



$add_prod = "INSERT INTO products values ( '' , now() , '$_POST[region]' , '$_POST[category]' ,
'$_POST[prod_name]' , '$_POST[city]' , '$_POST[prod_brief]' , '$_POST[prod_desc]' , '$_POST[prod_keywords]' ,
'$_POST[prod_image]' , '$_POST[prod_status]' , '$_POST[valid_m]' , '$_POST[valid_d]' , '$_POST[valid_y]',
'$_POST[expiry_m]' , '$_POST[expiry_d]' , '$_POST[expiry_y]')";
mysql_query($add_prod) or die(mysql_error());

//insert items

$query = "INSERT INTO prod_items values ('$_POST[prod_id]' , '$_POST[item_code]' ,
'$_POST[item_desc]' , '$_POST[item_points]' , '$_POST[item_cash]')";

$result = mysql_query($query)
or die ("Couldn't execute query.");



My guess is that MySQL doesn't know what $_POST[prod_id] is but I'm unsure how to get it inserted. With the code above it just inserts a "0" into the column.

[raf]

Wait a minute.

the 'id' column inside table products --> is it an auto_number column? If so, then you don't need to supplie a value for it (since it is automatically generated.
But off course, you need the generated value to insert it into the
prod_items table.

You can get that value with LAST_INSERT_ID() or the php function mysql_insert_id() (check the sticky on top of this forum for more info)

So your second query should look like
$query = "INSERT INTO prod_items values (" . mysql_insert_id() . ", '$_POST[item_code]' ,
'$_POST[item_desc]' , '$_POST[item_points]' , '$_POST[item_cash]')";

$_POST is a collection off all the formvalues --> the values of posted formfields, so the id can't be inthere, unless you're not using an auto_number column.

But you really shouldnt write your inserts like that.
The first small problem are the quotes. Only values for string-columns should be enclosed in quotes. Values for numerical columns (like int, smallint etc shouldn't be surrounded by quotes.
mySQL wount throw an error, but it's bad coding practice. And some other db's might throw errors, so you better get used to getting it right from the start.

A bigger problem is that you don't specify the columns.
You should always try to code as defensive as possible. Your query will be valid, but if you include an extra column in your table, thn you will get an error. Or if you reorder variables, then you would get the wrong values in some columns. Which will cause you to rewrite the querys.
If you specify the column, then it doesn't matter if you reorder the table or if you add extra columns in. You'll never need to change the query for that. And then you also don't need to supply a value for each column.

So you'd better write your query like
INSERT INTO products (var1, var2, var3) VALUES (valueforvar1, valueforvar2, 'valueforvar3')

If your table contains an auto_number column, then you don't include it in the columnlist or the valuelist

[percept]

thanks again...


the 'id' column inside table products --> is it an auto_number column? If so, then you don't need to supplie a value for it (since it is automatically generated.
But off course, you need the generated value to insert it into the
prod_items table.

You can get that value with LAST_INSERT_ID() or the php function mysql_insert_id() (check the sticky on top of this forum for more info)


Yes indeed the id is auto-increment in the products table, just needed a way to insert the value into the prod_items table so I will look at the two you suggest.




But you really shouldnt write your inserts like that.
The first small problem are the quotes. Only values for string-columns should be enclosed in quotes. Values for numerical columns (like int, smallint etc shouldn't be surrounded by quotes.

So you'd better write your query like
INSERT INTO products (var1, var2, var3) VALUES (valueforvar1, valueforvar2, 'valueforvar3')


thanks for this one, I had no idea that the way I was inserting values was going to be a problem... someone should re-write the book I bought on learning PHP/MySQL.

Just curious about the single quotes on the 'valueforvar3'? is this just a demonstration that this is a string variable?

cheers

[raf]

Just curious about the single quotes on the 'valueforvar3'? is this just a demonstration that this is a string variable?

Yes. I thought, i'll do two values for numerical columns and one for strings.

I had no idea that the way I was inserting values was going to be a problem... someone should re-write the book I bought on learning PHP/MySQL.

Did you got inserts like that from a book :eek: That's real bad.
I usually value coders by looking at how defensive they code, and not specifying your columnnames will sooner or later get you into problems. And because you don't get errors if you only reorder the columns, can can end up with a lott of corrupted records by the time the problem is detected.

If you need to know the correct syntax or need some examplecode for whatever mysql feature, then run a search at mysql.com
I doubt if you would find even 1 insert witch doesn't specify columnnames...

[percept]

well I've changed all my inserts over and I'm successful in getting the code to insert the product info into the productstable, but for some reason it won't execute the insertion of the product items into the prod_itemstable. I'm thinking that it has to do with the LAST_INSERT_ID() but don't know for sure. I've reviewed the info in the sticky and I've gone to MySQL.com, and a PHP site to see if I can find more info but as far as I can tell I can't see why this won't work.


//insert products
$add_prod = "INSERT INTO products (date_added, region, category, prod_name, city, prod_brief, prod_desc, prod_keywords,
prod_image, prod_status, valid_m, valid_d, valid_y, expiry_m, expiry_d, expiry_y)
VALUES (now(), '$region', '$category', '$prod_name', '$city', '$prod_brief', '$prod_desc', '$prod_keywords',
'$prod_image', '$prod_status', '$valid_m', $valid_d, $valid_y, '$expiry_m', $expiry_d, $expiry_y)";

$resultprds = mysql_query($add_prod) or die(mysql_error());

//insert items
$add_item = "INSERT INTO prod_items (prod_id, item_code, item_desc, item_points, item_cash)
VALUES (LAST_INSERT_ID(), '$item_code', '$item_desc', $item_points, $item_cash)";

$resultitms = mysql_query($add_item) or die ("Couldn't execute query.");


I'm wondering why I need to store the query results into a variable, is this so that if I want to display the inserted records I can just echo the variables? pardon my ignorance...

as mentioned, this code will insert the info into the products table, but then won't execute the next query. I will continue to research this but if you happen to spot my problem, please do let me know.

thank you...

[raf]

change:

$add_item = "INSERT INTO prod_items (prod_id, item_code, item_desc, item_points, item_cash)
VALUES (LAST_INSERT_ID(), '$item_code', '$item_desc', $item_points, $item_cash)";

into

$add_item = "INSERT INTO prod_items (prod_id, item_code, item_desc, item_points, item_cash)
VALUES (" . mysql_insert_id() . ", '$item_code', '$item_desc', $item_points, $item_cash)";


It might have been confusing, but reread that post. Inside PHP, you best use the php function

Also the 'id' is the auto-num column inside the productstable, but 'prod_id' inside the prod_items table, should be a regular column !! Certainly not the auto_num column.
It should be a normal column of a numerical type (the same one as the 'id'column) with a regular index (Not a unique one because you will have more then 1 item for a product !)

If that is all ok and it still doesn work, then add
echo $add_item ;
right after the second query and look at the printed string in your browser + post it here or paste it into the phpMyAdmin sql-tab (or somewhere in another db-frontends querywindow)

I'm wondering why I need to store the query results into a variable, is this so that if I want to display the inserted records I can just echo the variables?

No. You don't store the query results inthere. If you run a selectquery, for instance, then the variable will contain a number (result ID) that serves as a sort of key that you can use to identfy the actual recordset. But it doesn't contain the recordset itself.
so if you want to acces the recordset, you'd use something like

while ($row = mysql_fetch_assoc($result)){

or in regular language:
"als long as there are records in the recordset, store one record of the recordset, with identification= 'the value in $result' , inside $row

If the query fails (for instance if you use the wrong tablename), then $result will contain False.

If you do an insert, then $result will contain True is the insert is succesfull or False if there was an error.

So with
if (!$result){ // meaning : if result = False
echo 'Queryproblem';
}

You can check if the query was succesfull and perform some action on it.

[percept]

after changing the function over to mysql_insert_id I was still getting an error so I echoed the query and get this...


INSERT INTO prod_items (prod_id, item_code, item_desc, item_points, item_cash) VALUES (31, ' Test Code', 'test description', 75000, ) Couldn't execute query.


So my problem is that if I don't enter a value for item_cash I get the error... but if I do enter a value into the item_cash, it works fine. I don't understand this because this column is set at not null with a default of 0... therefore I would assume that the value for this column would be inserted as 0 if I leave it blank.

Do I have to specify in my insert code to insert the default value if a value is not entered? :o

[raf]

Do I have to specify in my insert code to insert the default value if a value is not entered?
No. But you need to specify a value foreach column in your columnlist --> the number of values always needs to be equal to the number of columns.

For string-columns, you could include '' (just two single quotes). Like
... values (34,'',45,'string')

or if you have an amptyy variable, that also ins't a problem. Like
$test=''

"... values (34,'". $test . "',45,'string')"

But for numerical values, you need to either include a dummyvalue like the set defaultvalue, or you could exclude the column from the columnlist, if you dynamically build the insertstatement.
Then the value of that column will automatically be set to the default. If you include it inside the columnlist then mySQL expects a value and wount look at the defaultvalue.

But the main rule is quite simple: you need to have as much values as columns, and an empty string is a value.

[percept]

Thank you...

If you include it inside the columnlist then mySQL expects a value and wount look at the defaultvalue.
This was exactly the explanation I was looking for. So if I have set up a form page for my client to insert their own records http://www.saveontravelbc.com/item_get_form.php then my way out would be to give them the simple instructions to input zeros if their is no value?

[raf]

my way out would be to give them the simple instructions to input zeros if their is no value?
Nonono! That isn't necessary and doesn't look professional!
And since your a designer, i can imagen that you don't want to spoil the layout with such unnescecary instructions...

The only indication i ever give is that the field is required or not (and not even always that).

When the form is processed, you simply check if there is a value, and if not, then you set one. Like

if (strlen($_POST['points']) < 1) {
$points = 0;
} else {
$points = trim($_POST['points']);
}

or if it is a required field

if (strlen($_POST['points']) < 1) {
$error .= '<br />You need to set a value for points';
} else {
$points = trim($_POST['points']);
}

(here i build one string with all the errors in, and at the end of the formprocessing i check if the strlen >= 1 and if so, i don't insert the record and reload the form with the error displayed at the top.
Or i store the error for each formfield in a seperate variable and set a veriable like $reload='yes' and then i check on that last variable to determine if i need to reload the form, or run the insert)
Inside your sql-stamement, you then use this variable instead of the $_POST['variable'].

if you have a lott of formfields and you want to save yourself some time, you can look for a class that does formvalidation (run a searchat phpclasses.org or hotscripts.com )

Formprocessing is almost the most important thing you need to learn to do serious databasedriven websites.
You should for instance use an addslashes() on all your string-values that you insert in the db + if you expect a numerical type, run a check if the value realy is numerical + check for sql-injections etc etc.

There are some good tutorials at hotscripts.com that start from the ground up until quite advance formhandling.

[percept]

You are so right about not wanting to put "instructions" on the form fields... and so my priority will be to learn more about Formprocessing.

In fact I think this may be my problem with my search by number of points that we have been working on. Right now it works great if you enter 40000 into the formfield, but if you enter 40,000 it gets stored as 40 so I'm assuming that MySQL is interpreting the comma as a stop. Without the comma it looks like crap and hard to read when you get high numbers.

I tried to fix this using the number_format() function but then I get a syntax error.... so yes indeed I do need to learn more about this form processing.

Thanks for all your help, my search by number of points is working (except the comma thing) and I used another comparison within the code so that it does not bring up "cash only" prices which it was doing because all "cash only" prices had a zero point value which is always less than the number of points entered.

This is so very exciting when things take shape... I can hardly wait to learn more, do more, expand and do flips with full twists...

[raf]

In fact I think this may be my problem with my search by number of points that we have been working on. Right now it works great if you enter 40000 into the formfield, but if you enter 40,000 it gets stored as 40 so I'm assuming that MySQL is interpreting the comma as a stop. Without the comma it looks like crap and hard to read when you get high numbers.
I suppose that your regional setting use the , as a decimal point. So your values should be formatted as 4.000.000;34.
But if you only expect integers, then you best just remove the , or . or spaces, before you insert the values. You can do this with str_replace()
Like

$markup = array(‘,’, ‘.’, ‘ ‘);
$points = trim(str_replace($markup, "", $_POST[‘points’]));

This replaces all elements of $markup, inside the posted value, wit an empty string à removes them.
This is so very exciting when things take shape... I can hardly wait to learn more, do more, expand and do flips with full twists...
Indeed. Once you get your toolbox filled with all the right tools, it becomes fun to make solid, stable, efficient and user-friendly pages.

[percept]

I've moved this post to a new thread named formatting strings using...