Search script *n00b*

[stoiko]

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

[Old Pedant]

For question 1, I recommend that you change the design completely.

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"??

[Old Pedant]

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

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.

[stoiko]

Originally Posted by Old Pedant

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

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

[Old Pedant]

"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.

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:

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!

[stoiko]

How about imageurl row? What type? Varchar?

[Old Pedant]

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.

[djm0219]

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).

[stoiko]

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?

[Old Pedant]

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.

[stoiko]

Originally Posted by Old Pedant

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?

[Old Pedant]

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.