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

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Xicer
    New Member
    • Dec 2010
    • 13

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

    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:
    Code:
    albumid	int(11)		 
    genreid	varchar(30)	 
    albumtitle	varchar(30)	 
    albumauthor	varchar(30
    numberofsongs	int(11	 
    price	int(11)
    Then i have two php pages

    Search.php :
    Code:
    <html>
    
    <center>
    
    </br></br></br></br>
    
    <img src="guitarlogo.jpg" alt="Music Database"/>
    
    <form method="GET" action="Result.php">
    
    <h2><b>Search Music Database:</b></h2>
    
    <input type="text" name="mts" />
    <input type="submit" name="submit" value="Search" />
    </form>
    
    <?php
    $connection = mysql_connect("localhost","root",""); 
    $fields = mysql_list_fields("ea09039", "albums", $connection); 
    $columns = mysql_num_fields($fields); 
    echo "<form action=Result.php method=POST>"; 
    
    echo "<select name=Field>";
    for ($i = 0; $i < $columns; $i++) { 
    echo "<option value=$i>"; 
    echo mysql_field_name($fields, $i); 
    } 
    echo "</select></form>"; 
    ?>
    
    </center>
    
    </html>
    and Result.php :

    Code:
    <?php
    
    include("db.php");
    
    $mts=$_GET["mts"];
    $Field=$_GET["Field"];
    
    
    $query="select * from albums where '$Field' like '%$mts%'";
    
    $res2=mysql_query($query);
    
    while($row=mysql_fetch_row($res2))
    {
    echo $row[2];
    echo " has author ";
    echo $row[3];
    echo " and costs ";
    echo $row[5];
    echo " Euro.";
    echo " And is from the genre ";
    echo $row[1];
    echo "<br/>";
    }
    
    ?>
    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.
  • JKing
    Recognized Expert Top Contributor
    • Jun 2007
    • 1206

    #2
    Okay, so what are your errors? What is it doing? What is it supposed to be doing?

    Comment

    • Xicer
      New Member
      • Dec 2010
      • 13

      #3
      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:
      Code:
      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 :)

      Comment

      • JKing
        Recognized Expert Top Contributor
        • Jun 2007
        • 1206

        #4
        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_esca pe_string() on any variables you pass to the database.

        Comment

        • Xicer
          New Member
          • Dec 2010
          • 13

          #5
          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
          Code:
          <html>
          
          <center>
          
          </br></br></br></br>
          
          <img src="guitarlogo.jpg" alt="Music Database"/>
          
          <h2><b>Search Music Database:</b></h2>
          <?php
          $connection = mysql_connect("localhost","root",""); 
          $fields = mysql_list_fields("ea09039", "albums", $connection); 
          $columns = mysql_num_fields($fields); 
          
          echo "<form action=Result.php method=GET>";
          echo "<input type=text name=mts/>";
          echo "<select name=Field>"; 
          
          for ($i = 0; $i < $columns; $i++) { 
          echo "<option value=$i>"; 
          echo mysql_field_name($fields, $i); 
          } 
          echo "</select>"; 
          echo "<input type=submit name=submit value=Search />";
          ?>
          
          
          </center>
          
          </html>
          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
          Code:
          http://localhost/ea09039/HOMEWORK2/Result.php?mts/=blue&[B]Field=2[/B]&submit=Search
          Here is the Results.php file again:
          Code:
          <?php
          
          include("db.php");
          
          $mts=$_GET["mts"];
          $Field=$_GET["Field"];
          
          
          [B]$query="select * from albums where '$Field' like '%$mts%'";[/B]
          
          $res2=mysql_query($query);
          
          while($row=mysql_fetch_row($res2))
          {
          echo $row[2];
          echo " ka autor ";
          echo $row[3];
          echo " dhe kushton ";
          echo $row[5];
          echo " Euro.";
          echo " Dhe eshte nga zhanri ";
          echo $row[1];
          echo "<br/>";
          }
          
          ?>
          Any ideas?

          Comment

          • Samishii23
            New Member
            • Sep 2009
            • 246

            #6
            If your looking for an auto complete drop down from a text box. You could try this: http://jqueryui.com/demos/autocomplete/

            Comment

            • Xicer
              New Member
              • Dec 2010
              • 13

              #7
              Not what I'm looking for, but it might come in handy in the future. Thanks anyway

              Comment

              • JKing
                Recognized Expert Top Contributor
                • Jun 2007
                • 1206

                #8
                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.

                Comment

                • Xicer
                  New Member
                  • Dec 2010
                  • 13

                  #9
                  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 :)

                  Comment

                  • Samishii23
                    New Member
                    • Sep 2009
                    • 246

                    #10
                    Code:
                    $_id = $_POST['Field'];
                    
                    while($row=mysql_fetch_row($res2)) {
                        if ( $row['id'] == $_id ) {
                            $_name = $row['name'];
                            }
                        }
                    Or something like that. Basicly compare the id from the $_POST variable or instead of that...
                    Code:
                    $connection = mysql_connect("localhost","root",""); 
                    $fields = mysql_list_fields("ea09039", "albums", $connection); 
                    $columns = mysql_num_fields($fields); 
                     
                    echo "<form action=Result.php method=GET>";
                    echo "<input type=text name=mts/>";
                    echo "<select name=Field>"; 
                     
                    for ($i = 0; $i < $columns; $i++) { 
                    [B]echo "<option value='". mysql_field_name($fields, $i). "'>"; [/B]
                    echo mysql_field_name($fields, $i); 
                    } 
                    echo "</select>"; 
                    echo "<input type=submit name=submit value=Search />";
                    Last edited by Samishii23; Dec 10 '10, 09:22 PM. Reason: Tabbed and hit enter... opps

                    Comment

                    • Xicer
                      New Member
                      • Dec 2010
                      • 13

                      #11
                      Thank you Samishii23 the second part worked like a charm :D
                      Also thanks to everyone that contributed

                      Comment

                      • Samishii23
                        New Member
                        • Sep 2009
                        • 246

                        #12
                        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:
                        Code:
                        $fields = array ('id','name','date');
                        $Field = $fields[$_POST['Field']];

                        Comment

                        • Xicer
                          New Member
                          • Dec 2010
                          • 13

                          #13
                          Thank you for the information, i will try it out.

                          Comment

                          Working...