+ Post New Thread
Results 1 to 10 of 10
Web Development Thread, MySQL Database Search in Coding and Web Development; Ive got a bit of knowledge of PHP but not enough to construct a working search box for the school ...
  1. #1
    wiggum123's Avatar
    Join Date
    May 2010
    Location
    Bristol
    Posts
    58
    Thank Post
    13
    Thanked 2 Times in 2 Posts
    Rep Power
    9

    MySQL Database Search

    Ive got a bit of knowledge of PHP but not enough to construct a working search box for the school site.
    I have created a table full of telephone numbers, names and departments. I have created a page that displays the info, all I need is a simple search to find records in a table named telephone.
    There are 6 rows
    ID (primary) - not really fussed about this one
    int_no - Internal Number
    ext_no - External Number
    mob_no - Mobile Number
    name -
    other - location / department info

    It would be amazing if I could construct some kind of ajax search, but this is not essential.

    I can connect to the db and table alright, the problem comes when I start searching. I want the user to be able to search the last 5 rows (I dont want users searching the ID as this will just confuse things) and display the results in a table. This is the code for the table which displays all the info. If I could use the same one that would save so much time!
    PHP Code:
    <?php
    $table 
    'telephone';
    $query "SELECT * FROM $table";
    $result mysql_query($query);
    $num mysql_numrows($result);
    $i=0;
    echo 
    '<table id="sortable" style="width: 100%;" align="center" border="0" cellspacing="0" cellpadding="5" >';
    echo 
    "<thead>";
    echo 
    "<tr>";
    echo 
    '<th style="text-align:center" scope="col">Internal Extension</th> <th style="text-align:center" scope="col">External Number</th> <th style="text-align:center" scope="col">Mobile Number</th> <th style="text-align:center" scope="col">Name / Department</th> <th style="text-align:center" scope="col">Other Info</th>';
    echo 
    "</tr>";
    echo 
    "</thead>";
    echo 
    "<tbody>";
    while (
    $i<$num){
        
    $int_no=mysql_result($result,$i,"int_no");
        
    $ext_no=mysql_result($result,$i,"ext_no");
        
    $mob_no=mysql_result($result,$i,"mob_no");
        
    $name=mysql_result($result,$i,"name");
        
    $other=mysql_result($result,$i,"other");
    echo 
    "<tr>";
    echo 
    '<td style="text-align:center" width="5px">'"$int_no</td>";
    echo 
    '<td style="text-align:center" width="100px">'"$ext_no</td>";
    echo 
    '<td style="text-align:center" width="100px">'"$mob_no</td>";
    echo 
    "<td>$name</td>";
    echo 
    "<td> $other</td>";
    echo 
    "</tr>";

    $i++;
    };
    echo 
    "</tbody>";
    echo 
    "</table>";

    ?>
    I have tried searching around to get a working search script but I'm starting to loose the will... Any help would be greatly appreciated

  2. #2
    zag
    zag is offline
    zag's Avatar
    Join Date
    Mar 2007
    Posts
    3,762
    Thank Post
    897
    Thanked 416 Times in 350 Posts
    Blog Entries
    12
    Rep Power
    86
    just add a $query2 variable to use LIKE whatever database field you want to search and loop the result 5 times only

  3. #3
    wiggum123's Avatar
    Join Date
    May 2010
    Location
    Bristol
    Posts
    58
    Thank Post
    13
    Thanked 2 Times in 2 Posts
    Rep Power
    9
    Thanks Zag
    How do I link the Like to a form search box?

  4. #4
    zag
    zag is offline
    zag's Avatar
    Join Date
    Mar 2007
    Posts
    3,762
    Thank Post
    897
    Thanked 416 Times in 350 Posts
    Blog Entries
    12
    Rep Power
    86
    Create a form, with a search box in it.

    In the form action properties load the same page when you submit the form but assign the $query2 variable to the SQL statement instead of the current $query

  5. Thanks to zag from:

    wiggum123 (25th November 2010)

  6. #5
    wiggum123's Avatar
    Join Date
    May 2010
    Location
    Bristol
    Posts
    58
    Thank Post
    13
    Thanked 2 Times in 2 Posts
    Rep Power
    9
    This is what I have changed, what am I doing wrong?

    PHP Code:
    <form id="directory_search" name="directory_search" method="GET" action="telephone-search.php">
      <label for="search_directory">Search:</label>
      <input type="text" name="search_directory" id="search_directory" />
      <input type="submit" name="dir_search" id="dir_search" value="Submit" />
    </form>
    <?php
    $dir_search 
    = ($_POST['search_directory']);
    $table 'telephone';
    $search "SELECT * FROM $table WHERE int_no LIKE $dir_search";
    $result mysql_query($search);
    $num mysql_numrows($result);
    ....
    bla bla bla

  7. #6

    webman's Avatar
    Join Date
    Nov 2005
    Location
    North East England
    Posts
    8,403
    Thank Post
    637
    Thanked 961 Times in 661 Posts
    Blog Entries
    2
    Rep Power
    319
    Quote Originally Posted by wiggum123 View Post
    There are 6 rows
    ID (primary) - not really fussed about this one
    int_no - Internal Number
    ext_no - External Number
    mob_no - Mobile Number
    name -
    other - location / department info
    These are your columns; not rows - the rows are the actual records/data that's inserted and stored there.

    Quote Originally Posted by wiggum123 View Post
    I want the user to be able to search the last 5 rows (I dont want users searching the ID as this will just confuse things) and display the results in a table.
    Got ya! You want them to be able to search for something, and have the DB look up data matching this; but only in the int_no, ext_no, mob_no, name, other fields. Yes? OK...

    Your query should look something like this:

    Code:
    SELECT * FROM telephone 
    WHERE (int_no LIKE '%$dir_search%') 
    OR (ext_no LIKE '%$dir_search%') 
    OR (mob_no LIKE '%$dir_search%') 
    OR (name LIKE '%$dir_search%') 
    OR (other LIKE '%$dir_search%')
    Hope that helps
    Last edited by webman; 25th November 2010 at 09:43 AM. Reason: Re-read question :)

  8. Thanks to webman from:

    wiggum123 (25th November 2010)

  9. #7
    budgester's Avatar
    Join Date
    Jan 2006
    Location
    Enfield, Middlesex
    Posts
    486
    Thank Post
    4
    Thanked 37 Times in 30 Posts
    Rep Power
    24
    Quote Originally Posted by wiggum123 View Post
    This is what I have changed, what am I doing wrong?

    PHP Code:
    <form id="directory_search" name="directory_search" method="GET" action="telephone-search.php">
      <label for="search_directory">Search:</label>
      <input type="text" name="search_directory" id="search_directory" />
      <input type="submit" name="dir_search" id="dir_search" value="Submit" />
    </form>
    <?php
    $dir_search 
    = ($_POST['search_directory']);
    $table 'telephone';
    $search "SELECT * FROM $table WHERE int_no LIKE $dir_search";
    $result mysql_query($search);
    $num mysql_numrows($result);
    ....
    bla bla bla
    Ok your using different request variables. $_GET and $_POST

    Try dropping in some debugs.

    var_dump($_GET);
    var_dump($_POST);
    var_dump($_REQUEST);

    Next why user $table variable if it is only used once, hard code it into the SQL string.

    Try changing

    $search = "SELECT * FROM $table WHERE int_no LIKE $dir_search";

    to

    $search = "SELECT * FROM $table WHERE int_no LIKE " .$dir_search;

    It's a bit cleaner and should parse better as the variable is not embeded in the string.

    And this script is definately insecure, see

    http://xkcd.com/327/

  10. Thanks to budgester from:

    wiggum123 (25th November 2010)

  11. #8
    wiggum123's Avatar
    Join Date
    May 2010
    Location
    Bristol
    Posts
    58
    Thank Post
    13
    Thanked 2 Times in 2 Posts
    Rep Power
    9
    Hey Guys

    Webman
    Thanks for the code, I've put that in and it works perfectly. Thank you very much!!

    budgester
    Thanks for pointing out the $POST and $GET issue, I had changed it at the top within the form, but I hadnt updated it within the PHP. I have also put the table name into the SQL query. That was left over from another version of the code, just hadnt updated it yet. As for the security issue, there shouldn't be any way of anyone adding data to this table unless they are authorised, and as I'm the only one with access to the table, this cant happen....right?

  12. #9
    budgester's Avatar
    Join Date
    Jan 2006
    Location
    Enfield, Middlesex
    Posts
    486
    Thank Post
    4
    Thanked 37 Times in 30 Posts
    Rep Power
    24
    Try it in the search box put "test; INSERT into telephone...... 'the rest of the insert statement';"

    Or be evil to yourself and try "test; DROP Table telephone;"


    Read

    AskBee.NET | php | SQL Injection | sql injection

    and

    PHP: mysql_real_escape_string - Manual

    You might also want to look at permissions on the database etc...

    Welcome to the nasty world of web based sql. Have fun.

    Now the question you have to ask is... do you care ?

  13. #10

    powdarrmonkey's Avatar
    Join Date
    Feb 2008
    Location
    Alcester, Warwickshire
    Posts
    4,859
    Thank Post
    412
    Thanked 777 Times in 650 Posts
    Rep Power
    182
    Try it in the search box put "test; INSERT into telephone...... 'the rest of the insert statement';"

    Or be evil to yourself and try "test; DROP Table telephone;"
    PHP injection is actually a bit more subtle than that; the default driver doesn't allow you to run two statements in the same call for exactly that reason.

SHARE:
+ Post New Thread

Similar Threads

  1. Mysql - Track database changes
    By danIT in forum Coding
    Replies: 4
    Last Post: 25th June 2011, 05:46 PM
  2. Replies: 6
    Last Post: 13th June 2011, 02:50 PM
  3. [Fog] Can't connect to mysql database
    By jack0w in forum O/S Deployment
    Replies: 12
    Last Post: 28th October 2009, 09:52 AM
  4. Scripting the backup of MySQL Database?
    By Quackers in forum Scripts
    Replies: 3
    Last Post: 3rd February 2009, 10:42 PM
  5. Creating a new database in php and mysql
    By StewartKnight in forum Web Development
    Replies: 18
    Last Post: 21st February 2007, 08:53 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •