Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    New to the CF scene
    Join Date
    Jan 2013
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Search script *n00b*

    A table 'models' with those rows:
    firstname
    id (random generated 5 digit number)
    phone
    email
    day (for birthday)
    month (for birthday)
    year (for birthday)
    gender
    height
    weight
    Question 1: What types do you suggest for each?

    Question (Request) 2: PHP script that searches from the database like this:
    1) textfield for firstname
    2) dropdown menu one for age (from) and dropdown menu two for age (to)
    From (...) To (...); The script calculates the age from the day month and year tables
    3) radio buttons for gender
    3) height dropdown that goes through 10 (140-149, 150-159...), even though the numbers are exact in the DB
    4) weight dropdown that goes through 5 (35-39; 40-44; 45-49...), even though the numbers are exact in the DB
    5) ID search

    When searching all the filled fields must be true, not just one of the filled.

    I think it should be easy to write the script but I don't really know much PHP...

    edit: this is what I did with the rows:
    firstname text
    id int(11)
    phone int(11)
    email text
    day int(11)
    month int(11)
    year int(11)
    gender text
    height int(11)
    weight int(11)

    It is fine, for now but is this possible:
    1) auto-generate ID
    2) only 2 options for gender, not just a text field
    Last edited by stoiko; 01-15-2013 at 10:00 PM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,658 Times in 4,620 Posts
    For question 1, I recommend that you change the design completely.
    Code:
    CREATE TABLE yourtablename (
        id INT AUTO_INCREMENT PRIMARY KEY,	
        firstname VARCHAR(50),
        phone VARCHAR(20),
        email VARCHAR(200),
        birthdate DATE,
        gender ENUM( 'M', 'F' ),
        height DECIMAL(10,2),
        weight DECIMAL(10,2)
    ) ENGINE INNODB;
    (1) *NEVER NEVER NEVER* use a TEXT field unless you REALLY need it. TEXT fields are expensive in terms of performance and have restrictions that VARCHAR fields do not.
    (2) *NEVER NEVER NEVER* represent a date and/or time value as anything *except* a DATETIME or DATE value (or possibly a TIMESTAMP, but not for birthdates).
    (2B) And it makes no sense at all to put the month, day, and year into separate fields. It makes doing queries that use date comparisons (e.g., datefield < '2013-1-1') almost impossible.
    (3) What would you do if somebody entered their weight as 154.6 ?? If your data type is INT, you wouldn't be able to hold the ".6" part. You could use FLOAT instead of DECIMAL, of course.
    (4) Most importantly: Don't use a random integer for your ID. It is *possible* that two individuals could end up with the same ID! Instead, use an AUTO_INCREMENT value, as shown. And make it your primary key, also as shown.
    (5) Note that I made GENDER and ENUM field. You could, if you wished, make it a CHAR(1) field, but the ENUM is a better choice.
    (6) PHONE should usually not be treated as an INT field. What do you do if somebody has a phone number such as "800-317-2201 Ext 37"??
    Last edited by Old Pedant; 01-15-2013 at 10:13 PM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,658 Times in 4,620 Posts
    This makes no sense:
    When searching all the filled fields must be true, not just one of the filled.
    Because you also have this requirement:
    5) ID search
    And since IDs must be unique, as soon as the user enters an id number, all the other search fields WILL NOT MATTER, since *ONLY* the record with that ID can be found.

    Hmmm...Maybe that sentence should read
    Code:
    When searching, ignore fields that have no value.  But all fields with a value must be considered.
    ??? I guess it could be read that way, actually. Okay.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    New to the CF scene
    Join Date
    Jan 2013
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    This makes no sense:
    Because you also have this requirement:
    And since IDs must be unique, as soon as the user enters an id number, all the other search fields WILL NOT MATTER, since *ONLY* the record with that ID can be found.

    Hmmm...Maybe that sentence should read
    Code:
    When searching, ignore fields that have no value.  But all fields with a value must be considered.
    ??? I guess it could be read that way, actually. Okay.
    Thank you for the help, really apreciating it.
    Yes, you are right, it is an error, because I first entered the 4 steps and then wrote this line, but I remembered that one could need to search just by ID so I added number 5 and forgot about that line. As for the phone, to be fair, I don't really get what do you mean with the Ext (well obviosly external, but that wouldn't really be of much use, since the database will include just people from the same region, besides I'm not even sure we have such a thing in Europe, however I see that if it is INT it can't start with a 0, I don't know why, and this will be useful), and I was thinking the input of weight and height to be by a dropdown menu, so I guess it wouldnt've been a problem, however decimal wouldn't hurt. So question one is answered, just one more little detail: if not possible to autogenerate the ID, can the auto increment start from other number than 0? Example: 101, 102, 103...

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,658 Times in 4,620 Posts
    "EXT" means "Extension number". In a business, you might call the company's main number and then ask for an "extension". That is, the (usually 3 to 5 digit) INTERNAL number for one person at the company.

    ********

    You can start an AUTO_INCREMENT any where.

    Code:
    CREATE TABLE yourtablename (
        id INT AUTO_INCREMENT PRIMARY KEY,	
        firstname VARCHAR(50),
        phone VARCHAR(20),
        email VARCHAR(200),
        birthdate DATE,
        gender ENUM( 'M', 'F' ),
        height DECIMAL(10,2),
        weight DECIMAL(10,2)
    ) ENGINE INNODB, AUTO_INCREMENT = 101;
    If you create your table using some tool and the tool does not give you the chance to specify that = 101 (or whatever starting number you want), one easy fix is to simply do:
    Code:
    INSERT INTO tablename ( ID ) VALUES( 100 );
    DELETE FROM tablename WHERE ID = 100;
    MySQL lets you override the AUTO_INCREMENT value. So you put in a record with an ID *one less* than where you want to start. Then delete that record. But now AUTO_INCREMENT remembers that the highest value was 100 and makes the next ID just one greater!
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    New to the CF scene
    Join Date
    Jan 2013
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    How about imageurl row? What type? Varchar?

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,658 Times in 4,620 Posts
    Yes. For any field that needs to hold less than, say, 1000 characters, you should opt for VARCHAR() for starters.

    MySQL does have a max record size: 8000 bytes (approx) for INNODB tables, 65535 for MYISAM tables.

    Anyway, if you find that you have created a table that gives a "record size too large" error, then and only then is the time to consider changing your largest VARCHAR() columns to TEXT columns.

    CLEARLY, in the case of the record described here, where we have way under 1000 bytes so far, there is no reason to avoid VARHCHAR(1000) for your imagetURL. But in reality, URLs can't be that long (not permitted by the browsers), so all is okay.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    Senior Coder djm0219's Avatar
    Join Date
    Aug 2003
    Location
    Wake Forest, North Carolina
    Posts
    1,314
    Thanks
    4
    Thanked 207 Times in 204 Posts
    Minor point but browsers support URLs well over 1000 characters though it is recommended that they not be that long. Even IE handles up to 2,083 characters (http://support.microsoft.com/kb/208427).
    Dave .... HostMonster for all of your hosting needs

  • #9
    New to the CF scene
    Join Date
    Jan 2013
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok, now some unusual problem: I found a script that with some work I was able to make it display what was needed (well at least a part of it), BUT I have a problem with displaying properly the results, because some of them are in Bulgarian (non-latin) - the problem is in the script, not the DB, it is all right in there... how to fix it?

    Here is the files:

    PHP Code:
    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN">
    <html>
    <head>
      <title>title</title>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
      </head>

    <body>
    <?php
        mysql_connect
    ("localhost""root""") or die("Error connecting to database: ".mysql_error());
        
    /*
            localhost - it's location of the mysql server, usually localhost
            root - your username
            third is your password
             
            if connection fails it will stop loading the page and display an error
        */
         
        
    mysql_select_db("models") or die(mysql_error());
        
    /* tutorial_search is the name of database we've created */
         
         
         
    ?>

    <?php
        $query 
    $_GET['query']; 
        
    // gets value sent over search form
         
        
    $min_length 0;
        
    // you can set minimum length of the query if you want
         
        
    if(strlen($query) >= $min_length){ // if query length is more or equal minimum length then
             
            
    $query htmlspecialchars($query); 
            
    // changes characters used in html to their equivalents, for example: < to &gt;
             
            
    $query mysql_real_escape_string($query);
            
    // makes sure nobody uses SQL injection
             
            
    $raw_results mysql_query("SELECT * FROM models
                WHERE (`firstname`='$query')"
    ) or die(mysql_error());
                 
            
    // * means that it selects all fields, you can also write: `id`, `title`, `text`
            // articles is the name of our table
             
            // '%$query%' is what we're looking for, % means anything, for example if $query is Hello
            // it will match "hello", "Hello man", "gogohello", if you want exact match use `title`='$query'
            // or if you want to match just full word so "gogohello" is out use '% $query %' ...OR ... '$query %' ... OR ... '% $query'
             
            
    if(mysql_num_rows($raw_results) > 0){ // if one or more rows are returned do following
                 
                
    while($results mysql_fetch_array($raw_results)){
                
    // $results = mysql_fetch_array($raw_results) puts data from database into array, while it's valid it does the loop
                 
                    
    echo "<p>".$results['id']."".$results['firstname']."".$results['birthdate']."".$results['gender']."".$results['height']."".$results['weight']."</p>";
                    
    // posts results gotten from database(title and text) you can also show id ($results['id'])
                
    }
                 
            }
            else{ 
    // if there is no matching rows do following
                
    echo "No results";
            }
             
        }
        else{ 
    // if query length is less than minimum
            
    echo "Minimum length is ".$min_length;
        }
    ?>
    </body>
    </html>
    I thought that the charset set to utf-8 or windows-1251 would help, but no, the cyrillic letters display as "?". What to do?

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,658 Times in 4,620 Posts
    Did you create the tables as UTF-8???

    To find out, do SHOW CREATE TABLE tablename and that will tell you what character set the DB is using.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #11
    New to the CF scene
    Join Date
    Jan 2013
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Did you create the tables as UTF-8???

    To find out, do SHOW CREATE TABLE tablename and that will tell you what character set the DB is using.
    How to change the coding for a table?

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,658 Times in 4,620 Posts
    When in doubt, Read The Fabulous Manual (RTFM):

    http://dev.mysql.com/doc/refman/5.5/en/alter-table.html

    To change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to a new character set, use a statement like this:

    ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;
    But read the warning below that. You may find that you want to convert the columns one at a time. Note that if you already have data in the table, it may *NOT* work to just change the character set, because the data is already INCORRECTLY stored with the wrong character set.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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