PHP - Searching A Number Range Using Between

I think I've coded myself into a corner.  I have numerous items entered with measurement extremes - highest and lowest. Now I want somebody to be able to search for an item that could match a specific length.

So I could have several items that are entered into a database like this:

Code: [Select]
item_id  |   length  |   high_low_param
-----------+-----------+----------------------
1234      |    12 cm  |  high
-----------+-----------+---------------------
1234      |    6 cm    | low


If somebody searches for an item that is 8 cm long, I want the results to be item 1234 and any other items that span that range. I'm not sure how to do this? array_fill ? I don't know. I don't want to slow down the database too much, and I don't want to have to put an entry for every single length each item could be.

Any suggestions?

i'm stuck with searching on age query

i let users register their date of birth (they give in DAY, MONTH, YEAR)  --> column 3

Name    CalcAge    Timestamp    Timestamp calculated for query
Person 1   21   659948401   642754801
Person 2   39   91954801                  83746801
Person 3   15   849337201   848991601
Person 4   33   281257201   281170801
Person 5   23   596876401   600850801
Person 6   87   -1422809999   -1422723599

I came up with the following query to search between a minimum and maximum age (using AJAX):

Code: [Select]
//user enters a minimum and/ or max age - getting the querystring from URL (ajax)
$minAge = $_GET['minAge'];
$maxAge = $_GET['maxAge'];

//age to unix timestamp
function agetostamp($age){
$gebjaar = date(Y) - $age;
$timestamp = mktime (0,0,1,0,0,$gebjaar);
return $timestamp;
}

//making getvariables timestamp in order to compare with database
if (isset ($minAge)){
$minAge = agetostamp($minAge);
}
if (isset ($maxAge)){
$maxAge = agetostamp($maxAge);
}

//building query
query = "SELECT * FROM respondenten WHERE geslacht = '$sex'";

if(is_numeric($minAge) && $minAge > 0)
$query .= " AND geboortedatum <= '$minAge'";
 else if (is_numeric($minAge))
$query .= " AND geboortedatum >= '$minAge'";

if(is_numeric($maxAge) && $maxAge > 0)
$query .= " AND geboortedatum <= '$maxAge'";
 else if (is_numeric($minAge))
$query .= " AND geboortedatum >= '$maxAge'";
I encouter a lot of troubles working like this:

1) if i leave out the part of maxAge (so i only allow to set a minimum age in the search box) it works except for the fact that if i set the minimum age to 23, person 5 will not be included since 596876401 < 600850801 even though she is 23 and should be included.

2) if i add the part of also searching on a max age, i don't get any results

I know that remark 1 probably has to do with the fact that im using date(Y) - $age and that people that are already born this year will have a timestamp higher than the one calculated in the query and visa versa.

conclusion: i don't think i'm going to get out of this so i'm hoping sombody can help me, telling me how to build a query for selecting on age range

Hi..

I need help in getting the 3 Months Name from my table field FromMonth and ToMonth.
Here is the scenario.

First, I select FromMonth and ToMonth.
Ex.
FromMonth = 5
ToMonth = 7
So it means
FromMonth is May
ToMonth is July.

Now I save the MonthNumber to my database:

table- so_month
FromMonth = 5
ToMonth = 7

Now I need to get the between Months Name from FromMonth to ToMonth which are (May, June, July).

How does it possible?
Thank you so much.

I'm getting the dreaded " Invalid parameter number: number of bound variables does not match number of tokens" error and I've looked at this for days.  Here is what my table looks like:

|  id          | int(4)       | NO   | PRI | NULL    | auto_increment |
| user_id     | int(4)       | NO   |     | NULL    |                |
| recipient   | varchar(30)  | NO   |     | NULL    |                |
| subject     | varchar(25)  | YES  |     | NULL    |                |
| cc_email    | varchar(30)  | YES  |     | NULL    |                |
| reply       | varchar(20)  | YES  |     | NULL    |                |
| location    | varchar(50)  | YES  |     | NULL    |                |
| stationery  | varchar(40)  | YES  |     | NULL    |                |
| ink_color   | varchar(12)  | YES  |     | NULL    |                |
| fontchosen  | varchar(30)  | YES  |     | NULL    |                |
| message     | varchar(500) | NO   |     | NULL    |                |
| attachment  | varchar(40)  | YES  |     | NULL    |                |
| messageDate | datetime     | YES  |     | NULL    |        

Here are my params:

            $params = array(                 
               ':user_id' => $userid,                 
               ':recipient' => $this->message_vars['recipient'],                 
               ':subject' => $this->message_vars['subject'],                 
               ':cc_email' => $this->message_vars['cc_email'],                 
               ':reply' => $this->message_vars['reply'],                 
               ':location' => $this->message_vars['location'],                 
               ':stationery' => $this->message_vars['stationery'],                 
               ':ink_color' => $this->message_vars['ink_color'],                 
               ':fontchosen' => $this->message_vars['fontchosen'],                 
               ':message' => $messageInput,                 
               ':attachment' => $this->message_vars['attachment'],                 
               ':messageDate' => $date             );

Here is my sql:

$sql = "INSERT INTO messages (user_id,recipient, subject, cc_email, reply, location,stationery, ink_color, fontchosen, message,attachment) VALUES( $userid, :recipient, :subject, :cc_email, :reply, :location, :stationery, :ink_color, :fontchosen, $messageInput, :attachment, $date);";

And lastly, here is how I am calling it:
            $dbh = parent::$dbh;             
           $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);              
           if (empty($dbh))    return false;             
                  $stmt = $dbh->prepare($sql);             
                 $stmt->execute($params) or die(print_r($stmt->errorInfo(), true));              
           if (!$stmt) {    print_r($dbh->errorInfo());              }

I know my userid is valid and and the date is set above (I've echo'd these out to make sure).  Since the id is auto_increment, I do not put that in my sql (though I've tried that too), nor in my params (tried that too).  What am I missing?  I feel certain it is something small, but I have spent days checking commas, semi-colons and spelling.  Can anyone see what I'm doing wrong?

Hello, I have problem durring binding update query. I can't find what is causing problem.

public function Update(Entry $e)
    {
        try
        {
            $query = "update entry set string = $e->string,delimiter=$e->delimiter where entryid= $e->id";
            $stmt = $this->db->mysqli->prepare($query);
            $stmt->bind_param('ssi',$e->string,$e->delimiter,$e->id);

            $stmt->close();
           

        }
        catch(Exception $ex)
        {
            print 'Error: ' .$ex->getMessage();
        }
    }

When I run function update I'm getting next error:Warning: mysqli_stmt::bind_param(): Number of variables doesn't match number of parameters in prepared statement   Can you help me to solve this problem ?
Edited by danchi, 17 October 2014 - 10:25 AM.