Page 1 of 1

Errors with SEARCH form select criteria into Mysql

Posted: Mon Apr 19, 2004 5:00 am
by jamessirleaf
I am trying to get all of my form select criteria used in my sql query

Say I have a Mysql Database called Products. The options for products
in my form:

<form method ="post" action="database.php">



<select>
<selected> * </>

<option> Perfumes</option>
<option>Soap</option>


My select statement is "Select " "$select" from Products" .

The object is to be able to select and display only the soap criteria
if I want. Or, select and display only the Perfumes criteria if I want.

What I am getting, is

a). full details of all options with "*" selected. - Correct!

b). an error message prompting error with mysql__num_res or the
results of the query when I select "Perfumes".

c). Same error message as (b) above when I select "Soap".

Is there a way around this. I recon that MySQL should
render my form "select" criteria valid in its select statement.

Does anyone have any idea?

Regards,
James

Posted: Mon Apr 19, 2004 5:05 am
by JayBird
what does your database look like?

What fields?

I am guessing your query is wrong, but can't give you the solution without the above information.

Mark

Posted: Mon Apr 19, 2004 5:15 am
by jamessirleaf
The database is products.

The set up is as such


id primary key
cat_id 1
item_desc Perfumes
Level 1 Brut
Level 2 Chanel




id primary


Better yet, I need to access and upload the file. I will do this. Please
bear with me.

James

Posted: Mon Apr 19, 2004 5:24 am
by JayBird
from them fields how do you tell between soap and perfumes?

Posted: Mon Apr 19, 2004 10:30 am
by jamessirleaf
The Mysql database is valid and consists of the following;

id -int
cat_id - 2
item_title - Skincare
List_1 - GiftBag
List_2 - Lip-Balm
List_3 - Body Wash


id
cat_id -3
item_title -Honey & Blackseed
List_1 -Honey & Blackseed Giftbag
List-2 -not yet declared
List_3 -not yet declared

id
cat_id -4
item_title - Lavendar & Wildflowers
List_1 - Lavendar & Wildflowes Giftbag
List_2 - not yet declared
List_3 - not yet declared

note* Each of these items under their respective item_titles(s) are treated as a separate row. (I have only replaced the perfumes and soap listings
with Skincare, Honey & Blackseed and Lavendar & Wildflowers.

The Form Element calls for the following:

<form method="post" action="database.php"
<select name =<"select">
<option selected ="selected>*</option">
<option>Skincare</option>
<option>Honey & Blackseed</option>
<option>Lavendar & Wildflowers</option>
</select>

<input type ="submit" value="search"
</form>


The action demanded is in the file database.php.

$conn=mysql_connect("localhost");
mysql_select_db("Products",$conn);
$get_iteminfo ="SELECT".$select."from products";
$result=mysql_query($get_iteminfo);

for $counter=0;
$row=mysql_fetch_row($result);
$counter++ {
print ("<tr>");
foreach ($row as $key=>$value)
print("<td>$value</td>");

}

</table>
<?php print ("$counter") ?> results.
</body>
</html>

This is the entire script for the most part. When my select
criteria is "*", I get a display of all of the results on a
row by row basis. The display also shows "not yet declared"
for columns where no item is placed yet.


I believe that my problem is with the call to the Mysql database.
I am hoping that the .$select. criteria will pick up the
appropriate selected choice from the form.

Hope this is clearer than my previous posting.



James



i.e. Skincare |Giftbag |Lipbalm
Honey & Blackseed |Giftbag

Posted: Tue Apr 20, 2004 11:17 am
by jamessirleaf
I still need some help to resolve this issue. Alternatively, I could run
a condition statement where I do as follows:

if $_POST[select] = "haircare"

mysql_query="select * from products where item_title="haircare";

elseif $_POST[select] ="skincare"

mysql_query ="select * from products where item_title="skincare";

else

"SELECT " .$select. "from products";


When run using these conditional statements however, I get only
values for item 1 i.e. Haircare. although the information and printed
values are absolutely correct.

Will Appreciate some input.


James

Posted: Tue Apr 20, 2004 11:21 am
by magicrobotmonkey
I think you should have

<option value = "something"> Something </option>

in your form

Posted: Tue Apr 20, 2004 11:37 am
by jamessirleaf
You could be right. Maybe I could use the onchange function in javascript.

To be honest, I have tried that as well, but it is just not picking up the <select> options.

Posted: Tue Apr 20, 2004 11:38 am
by magicrobotmonkey
can you print_r($_POST); and see the values?

Posted: Tue Apr 20, 2004 11:44 am
by jamessirleaf
Chief,

You are right to assume that the problem lies with the form. the values
in $_POST are recognized but only for the current value of the <select>
option in the form.

Posted: Tue Apr 20, 2004 11:50 am
by magicrobotmonkey
yes only ones that are checked will be passed i think - post the results of that print_r...