current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:
up vote 1 down vote favorite

I am not sure if I am doing the query right but i am just a beginner in PHP and postgresql hence my code.

what i am trying to accomplish is a search using date picker which will give data from a date:

<?php
$output = '';
if(isset($_POST['search'])) {
    $searchq = $_POST['search'];

    $query = ("SELECT trees.tree_type,tree_solds.transaction_id,tree_solds.actual_height,tree_solds.selling_height,tree_solds.sub_total,transactions.date_purchased FROM tree_solds
                                                    left join trees on tree_solds.tree_id = trees.id
                                                    left join transactions on transactions.id = tree_solds.transaction_id
                                                    WHERE date_purchased LIKE $searchq ");


                                        $result = pg_query($query); 
                                        if (!$result) { 
                                            echo "Problem with query " . $query . "<br/>"; 
                                            echo pg_last_error(); 
                                            exit(); 
                                        } 


                        $count = pg_num_rows($result);
                        if ($count == 0) {
                            $output = 'No Data on that date!';
                        } else {
                            while ($row = pg_fetch_array($result)) {
                               $output .= printf ("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>", htmlspecialchars($myrow['transaction_id']), htmlspecialchars($myrow['date_purchased']), htmlspecialchars($myrow['tree_type']), htmlspecialchars($myrow['actual_height']), htmlspecialchars($myrow['selling_height']), number_format($myrow['sub_total'], 2));
                            }
                        }
                    }
                ?>

HTML form

 <form action="location4.php" method="post">
 <input type="text" class="span2" name="search" value="" data-date-format="yyyy-mm-dd" id="dp2">

 <button type="submit" class="btn btn-default">Submit</button>
 </form>
 <?php print("$output");?>

Keep getting this error and no result.

Warning: pg_query(): Query failed: ERROR: operator does not exist: timestamp without time zone ~~ integer LINE 4: ... WHERE date_purchased LIKE 2014-... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. in /Applications/MAMP/htdocs/xmastool/location4.php on line 50 Problem with query SELECT trees.tree_type,tree_solds.transaction_id,tree_solds.actual_height,tree_solds.selling_height,tree_solds.sub_total,transactions.date_purchased FROM tree_solds left join trees on tree_solds.tree_id = trees.id left join transactions on transactions.id = tree_solds.transaction_id WHERE date_purchased LIKE 2014-12-07 ERROR: operator does not exist: timestamp without time zone ~~ integer LINE 4: ... WHERE date_purchased LIKE 2014-... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

share|improve this question
    
Do you want to match timestamps considering dates and ignoring hours, minutes and seconds? – tsnorri Dec 9 '14 at 14:28
    
just matching the date i what i am after without the hours, minutes and seconds. – user984264 Dec 9 '14 at 14:39
up vote 0 down vote

Use like this: WHERE date_purchased LIKE '%$searchq%' if you want just a part of it.

If you want exact match then WHERE date_purchased = '$searchq'

NOTE: % is a joker character in the LIKE, check the manual how to use.

share|improve this answer
    
added the '%$searchq%' but still got an error almost same as the other one it showed the result WHERE date_purchased LIKE '%2014-12-07%' as part of the error. – user984264 Dec 9 '14 at 14:40
    
What is the type of date_purchased column? – lolka_bolka Dec 9 '14 at 14:47
    
date_purchased is a timestamp – user984264 Dec 9 '14 at 15:52
    
Then check this: stackoverflow.com/questions/4799615/… – lolka_bolka Dec 9 '14 at 16:07
up vote 0 down vote

Try this

pg_query_params('SELECT ... WHERE DATE_TRUNC('day', date_purchased) = $1', array($searchq))

See [the documentation for details. You should always make sure that query parameters are properly escaped (i.e. not concatenated verbatim to the query) to prevent SQL injection.

share|improve this answer
up vote 0 down vote

solved the issue due to the tip of polka_bolka.

 WHERE TO_CHAR(date_purchased, 'yyyy-mm-dd') LIKE '%$searchq%' ")

converted the date_purchased to char.

<?php
$output = '';
if(isset($_POST['search'])) {
    $searchq = $_POST['search'];

    $query = ("SELECT trees.tree_type,tree_solds.transaction_id,tree_solds.actual_height,tree_solds.selling_height,tree_solds.sub_total,transactions.date_purchased FROM tree_solds
                                                    left join trees on tree_solds.tree_id = trees.id
                                                    left join transactions on transactions.id = tree_solds.transaction_id
                                                    WHERE TO_CHAR(date_purchased, 'yyyy-mm-dd') LIKE '%$searchq%' ");


                                        $result = pg_query($query); 
                                        if (!$result) { 
                                            echo "Problem with query " . $query . "<br/>"; 
                                            echo pg_last_error(); 
                                            exit(); 
                                        } 


                        $count = pg_num_rows($result);
                        if ($count == 0) {
                            $output = 'No Data on that date!';
                        } else {
                            while ($row = pg_fetch_array($result)) {
                               $output .= printf ("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>", htmlspecialchars($row['transaction_id']), htmlspecialchars($row['date_purchased']), htmlspecialchars($row['tree_type']), htmlspecialchars($row['actual_height']), htmlspecialchars($row['selling_height']), number_format($row['sub_total'], 2));
                            }
                        }
                    }
                ?>
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.