469,356 Members | 2,013 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,356 developers. It's quick & easy.

Search engine with textbox, dynamic dropdown with MySql. Need Help

13
Hello everyone, this is my first post here so I will try to make it as clear as possible.

Firstly i have a database with a single table named "albums" with the following fields:
Expand|Select|Wrap|Line Numbers
  1. albumid    int(11)         
  2. genreid    varchar(30)     
  3. albumtitle    varchar(30)     
  4. albumauthor    varchar(30
  5. numberofsongs    int(11     
  6. price    int(11)
Then i have two php pages

Search.php :
Expand|Select|Wrap|Line Numbers
  1. <html>
  2.  
  3. <center>
  4.  
  5. </br></br></br></br>
  6.  
  7. <img src="guitarlogo.jpg" alt="Music Database"/>
  8.  
  9. <form method="GET" action="Result.php">
  10.  
  11. <h2><b>Search Music Database:</b></h2>
  12.  
  13. <input type="text" name="mts" />
  14. <input type="submit" name="submit" value="Search" />
  15. </form>
  16.  
  17. <?php
  18. $connection = mysql_connect("localhost","root",""); 
  19. $fields = mysql_list_fields("ea09039", "albums", $connection); 
  20. $columns = mysql_num_fields($fields); 
  21. echo "<form action=Result.php method=POST>"; 
  22.  
  23. echo "<select name=Field>";
  24. for ($i = 0; $i < $columns; $i++) { 
  25. echo "<option value=$i>"; 
  26. echo mysql_field_name($fields, $i); 
  27. echo "</select></form>"; 
  28. ?>
  29.  
  30. </center>
  31.  
  32. </html>
  33.  
and Result.php :

Expand|Select|Wrap|Line Numbers
  1. <?php
  2.  
  3. include("db.php");
  4.  
  5. $mts=$_GET["mts"];
  6. $Field=$_GET["Field"];
  7.  
  8.  
  9. $query="select * from albums where '$Field' like '%$mts%'";
  10.  
  11. $res2=mysql_query($query);
  12.  
  13. while($row=mysql_fetch_row($res2))
  14. {
  15. echo $row[2];
  16. echo " has author ";
  17. echo $row[3];
  18. echo " and costs ";
  19. echo $row[5];
  20. echo " Euro.";
  21. echo " And is from the genre ";
  22. echo $row[1];
  23. echo "<br/>";
  24. }
  25.  
  26. ?>
  27.  
This is as far as i have gotten with this, but i keep getting errors.
Please if someone can help I would be very thankful.
Dec 10 '10 #1

✓ answered by Samishii23

Expand|Select|Wrap|Line Numbers
  1. $_id = $_POST['Field'];
  2.  
  3. while($row=mysql_fetch_row($res2)) {
  4.     if ( $row['id'] == $_id ) {
  5.         $_name = $row['name'];
  6.         }
  7.     }
Or something like that. Basicly compare the id from the $_POST variable or instead of that...
Expand|Select|Wrap|Line Numbers
  1. $connection = mysql_connect("localhost","root",""); 
  2. $fields = mysql_list_fields("ea09039", "albums", $connection); 
  3. $columns = mysql_num_fields($fields); 
  4.  
  5. echo "<form action=Result.php method=GET>";
  6. echo "<input type=text name=mts/>";
  7. echo "<select name=Field>"; 
  8.  
  9. for ($i = 0; $i < $columns; $i++) { 
  10. echo "<option value='". mysql_field_name($fields, $i). "'>"; 
  11. echo mysql_field_name($fields, $i); 
  12. echo "</select>"; 
  13. echo "<input type=submit name=submit value=Search />";

12 6785
JKing
1,206 Expert 1GB
Okay, so what are your errors? What is it doing? What is it supposed to be doing?
Dec 10 '10 #2
Xicer
13
Right now it's just giving me a blank page.

I wrote the word "blue" in the textbox and selected "albumtitle" from the dropdown list(which is dynamically popullated from a table in a MySQL database).

This is what i get at the address bar after i do a search:
Expand|Select|Wrap|Line Numbers
  1. http://localhost/ea09039/HOMEWORK2/Result.php?mts=blue&submit=Search


As for what is it supposed to be doing,
I need it to take the input from the textbox, and search within the column from the table that is selected from the dropdown list which are in Search.php and show the results in Result.php.

I hope i was clear :)
Dec 10 '10 #3
JKing
1,206 Expert 1GB
It looks like you have two forms on your search.php page. The first form only has a text input for mts and a submit button. These are sent to result.php via GET.

Your second form has a select named field and this form is sent to result.php via POST however there is no submit button for this form.

On result.php you try to retrieve the field variable via $_GET but you have never sent it with GET or at all because it is part of another form that doesn't get submitted and if it were sent it would be in the $_POST.

Now since your $field variable is never populated your query fails and so does your while loop and you end up with no output.

The solution here is to make the select part of your first form.

Also use mysql_real_escape_string() on any variables you pass to the database.
Dec 10 '10 #4
Xicer
13
Okay thank you for the reply.
I restructured my code and I believe there is one more problem left.

Here is the code for Search.php
Expand|Select|Wrap|Line Numbers
  1. <html>
  2.  
  3. <center>
  4.  
  5. </br></br></br></br>
  6.  
  7. <img src="guitarlogo.jpg" alt="Music Database"/>
  8.  
  9. <h2><b>Search Music Database:</b></h2>
  10. <?php
  11. $connection = mysql_connect("localhost","root",""); 
  12. $fields = mysql_list_fields("ea09039", "albums", $connection); 
  13. $columns = mysql_num_fields($fields); 
  14.  
  15. echo "<form action=Result.php method=GET>";
  16. echo "<input type=text name=mts/>";
  17. echo "<select name=Field>"; 
  18.  
  19. for ($i = 0; $i < $columns; $i++) { 
  20. echo "<option value=$i>"; 
  21. echo mysql_field_name($fields, $i); 
  22. echo "</select>"; 
  23. echo "<input type=submit name=submit value=Search />";
  24. ?>
  25.  
  26.  
  27. </center>
  28.  
  29. </html>
  30.  
The problem is that the Field variable returns the number of the position on the table. For example If i select albumtitle from the dropdown the Field variable return the position of the column albumtitle which is 2. I need it to return the name of that column not it's position.

Here is the result of the address barr
Expand|Select|Wrap|Line Numbers
  1. http://localhost/ea09039/HOMEWORK2/Result.php?mts/=blue&Field=2&submit=Search
Here is the Results.php file again:
Expand|Select|Wrap|Line Numbers
  1. <?php
  2.  
  3. include("db.php");
  4.  
  5. $mts=$_GET["mts"];
  6. $Field=$_GET["Field"];
  7.  
  8.  
  9. $query="select * from albums where '$Field' like '%$mts%'";
  10.  
  11. $res2=mysql_query($query);
  12.  
  13. while($row=mysql_fetch_row($res2))
  14. {
  15. echo $row[2];
  16. echo " ka autor ";
  17. echo $row[3];
  18. echo " dhe kushton ";
  19. echo $row[5];
  20. echo " Euro.";
  21. echo " Dhe eshte nga zhanri ";
  22. echo $row[1];
  23. echo "<br/>";
  24. }
  25.  
  26. ?>
  27.  
Any ideas?
Dec 10 '10 #5
Samishii23
246 100+
If your looking for an auto complete drop down from a text box. You could try this: http://jqueryui.com/demos/autocomplete/
Dec 10 '10 #6
Xicer
13
Not what I'm looking for, but it might come in handy in the future. Thanks anyway
Dec 10 '10 #7
JKing
1,206 Expert 1GB
The problem is that the Field variable returns the number of the position on the table. For example If i select albumtitle from the dropdown the Field variable return the position of the column albumtitle which is 2. I need it to return the name of that column not it's position.
Well of course it is. You are setting all your option values equal to $i in your for loop and not the field name.
Dec 10 '10 #8
Xicer
13
Okay thank you i understand my mistake, but could you please show me how should the code look, i can not seem to figure it out.

P.S im new to php so please have patience with me :)
Dec 10 '10 #9
Samishii23
246 100+
Expand|Select|Wrap|Line Numbers
  1. $_id = $_POST['Field'];
  2.  
  3. while($row=mysql_fetch_row($res2)) {
  4.     if ( $row['id'] == $_id ) {
  5.         $_name = $row['name'];
  6.         }
  7.     }
Or something like that. Basicly compare the id from the $_POST variable or instead of that...
Expand|Select|Wrap|Line Numbers
  1. $connection = mysql_connect("localhost","root",""); 
  2. $fields = mysql_list_fields("ea09039", "albums", $connection); 
  3. $columns = mysql_num_fields($fields); 
  4.  
  5. echo "<form action=Result.php method=GET>";
  6. echo "<input type=text name=mts/>";
  7. echo "<select name=Field>"; 
  8.  
  9. for ($i = 0; $i < $columns; $i++) { 
  10. echo "<option value='". mysql_field_name($fields, $i). "'>"; 
  11. echo mysql_field_name($fields, $i); 
  12. echo "</select>"; 
  13. echo "<input type=submit name=submit value=Search />";
Dec 10 '10 #10
Xicer
13
Thank you Samishii23 the second part worked like a charm :D
Also thanks to everyone that contributed
Dec 10 '10 #11
Samishii23
246 100+
Let me make a note about doing that. When you pass data like this there is a higher chance of someone finding out what your data is because they know something about your Database, though it is a bit far fetched, but the concept and possibility is there. Your better off using the #'s rather then the Field name itself.

You could use a field name array and do something like this:
Expand|Select|Wrap|Line Numbers
  1. $fields = array ('id','name','date');
  2. $Field = $fields[$_POST['Field']];
Dec 10 '10 #12
Xicer
13
Thank you for the information, i will try it out.
Dec 10 '10 #13

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by vichet | last post: by
2 posts views Thread by sathyashrayan | last post: by
5 posts views Thread by Martien van Wanrooij | last post: by
3 posts views Thread by hazly | last post: by
reply views Thread by JJ_377 | last post: by
2 posts views Thread by nagendra802000 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.