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 8 of 8
  1. #1
    New Coder
    Join Date
    Oct 2011
    Posts
    41
    Thanks
    27
    Thanked 0 Times in 0 Posts

    MySQL PHP SELECT WHERE LIKE

    Please Help I would appreciate it. My project is due on Friday.. I made a search form for a database table but it doesn't seem to be working. Here's the code, I've tried a couple different ways.
    PHP Code:
    public function search_ticket($whereClause){
            
    $tickets = array();
            
    $sql "SELECT * FROM scalpler_tickets WHERE artist LIKE %$whereClause% OR location LIKE %$whereClause% OR date LIKE %$whereClause%";
            
    $q $this->_db->prepare($sql);
            
    $q->execute(array($whereClause,$whereClause,$whereClause));
            foreach(
    $q as $ticket){
                
    $tickets[]= array('id'=>$ticket['id'],
                                                    
    'artist'=>$ticket['artist'],
                                                    
    'date'=>$ticket['date'],
                                                    
    'time'=>$ticket['time'],
                                                    
    'location'=>$ticket['location'],
                                                    
    'price'=>$ticket['price'],
                                                    
    'ticketsleft'=>$ticket['ticketsleft'],
                                                    
    'avator'=>$ticket['avator'],
                                                    
    'type'=>$ticket['type']);
            }
            return 
    $tickets;
        } 
    PHP Code:
    public function search_ticket($whereClause){
            
    $tickets = array();
            
    $sql "SELECT * FROM scalpler_tickets WHERE artist LIKE %?% OR location LIKE %?% OR date LIKE %?%";
            foreach(
    $this->_db->query($sql) as $ticket){
                
    $tickets[] = array('id'=>$ticket['id'],
                                        
    'artist'=>$ticket['artist'],
                                        
    'date'=>$ticket['date'],
                                        
    'time'=>$ticket['time'],
                                        
    'location'=>$ticket['location'],
                                        
    'price'=>$ticket['price'],
                                        
    'ticketsleft'=>$ticket['ticketsleft'],
                                        
    'avator'=>$ticket['avator'],
                                        
    'type'=>$ticket['type']);
            }
            return 
    $tickets;
        } 
    Last edited by matz0rz; 06-27-2013 at 04:05 AM.

  • #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    2,961
    Thanks
    2
    Thanked 305 Times in 297 Posts
    When you use wild-card % characters with prepared queries, you must add the % character(s) to the data in the php variable(s).

    When you prepare and run the query, the database/driver takes the string data values you supply and surrounds them with single-quotes, then uses that in the actual query statement. Your query results in the database forming this - LIKE %'something'% OR. If you add the % characters to the data in the variables, the query that is ran is a proper - LIKE '%something%' OR
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • Users who have thanked CFMaBiSmAd for this post:

    matz0rz (06-27-2013)

  • #3
    New Coder
    Join Date
    Oct 2011
    Posts
    41
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Thanks man!
    My query now looks like this if I do a vardump
    Code:
    'SELECT * FROM scalpler_tickets WHERE artist LIKE %"Tiesto"% OR date LIKE %"Tiesto"% OR location LIKE %"Tiesto"%'
    I get 'invalid foreach statement' as error
    PHP Code:
    public function search_ticket($whereClause){
            
    $tickets = array();
            
    $sql 'SELECT * FROM scalpler_tickets WHERE artist LIKE %"'.$whereClause.'"% OR date LIKE %"'.$whereClause.'"% OR location LIKE %"'.$whereClause.'"%';
            
    var_dump($sql);
            foreach(
    $this->_db->query($sql) as $ticket){
                
    $tickets[] = array('id'=>$ticket['id'],
                                        
    'artist'=>$ticket['artist'],
                                        
    'date'=>$ticket['date'],
                                        
    'time'=>$ticket['time'],
                                        
    'location'=>$ticket['location'],
                                        
    'price'=>$ticket['price'],
                                        
    'ticketsleft'=>$ticket['ticketsleft'],
                                        
    'avator'=>$ticket['avator'],
                                        
    'type'=>$ticket['type']);
            }
            return 
    $tickets;
        } 

  • #4
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    2,961
    Thanks
    2
    Thanked 305 Times in 297 Posts
    Your query statement should have place holders ? in it, not the actual php variables.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • Users who have thanked CFMaBiSmAd for this post:

    matz0rz (06-27-2013)

  • #5
    New Coder
    Join Date
    Oct 2011
    Posts
    41
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Thanks man I'll try it out in a minute and I'll let you know. Thanks

  • #6
    New Coder
    Join Date
    Oct 2011
    Posts
    41
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by CFMaBiSmAd View Post
    Your query statement should have place holders ? in it, not the actual php variables.
    How to I use a foreach on prepared queries :/ . Sorry if it's a stupid question lol.. This is what I got.
    PHP Code:
    public function search_ticket($whereClause){
            
    $tickets = array();
            
    $sql 'SELECT * FROM scalpler_tickets WHERE artist LIKE ? OR date LIKE ? OR location LIKE ?';
            
    $q $this->_db->prepare($sql);
            
    $q->execute(array($whereClause,$whereClause,$whereClause));
            
    var_dump($q);
            foreach(
    $q as $ticket){
                
    $tickets[] = array('id'=>$ticket['id'],
                                        
    'artist'=>$ticket['artist'],
                                        
    'date'=>$ticket['date'],
                                        
    'time'=>$ticket['time'],
                                        
    'location'=>$ticket['location'],
                                        
    'price'=>$ticket['price'],
                                        
    'ticketsleft'=>$ticket['ticketsleft'],
                                        
    'avator'=>$ticket['avator'],
                                        
    'type'=>$ticket['type']);
            }
            return 
    $tickets;
        } 
    vardump $q = 'SELECT * FROM scalpler_tickets WHERE artist LIKE ? OR date LIKE ? OR location LIKE ?'
    PHP Code:
    if(isset($_POST['searchSubmit'])){
                
    $query "%'".$_POST['searchEvents']."'%";
                
    $_SESSION['tickets'] = $this->model->search_ticket($query);    
            } 
    Code:
    <form name="searchForm" id="searchForm" method="post" enctype="multipart/form-data" action="events.php">
    	<input type="text" id="searchEvents" name="searchEvents" value="Artist, Location, Date">
    	<input type="submit" name="searchSubmit" id="searchSubmit" value="Search">
    </form>
    Last edited by matz0rz; 06-27-2013 at 02:30 AM.

  • #7
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    2,961
    Thanks
    2
    Thanked 305 Times in 297 Posts
    After the query successfully runs (you should have some logic to check), you can use one of the ->fetch methods to retrieve the rows. The ->fetchAll() method accomplishes the same thing as the loop you are trying to write.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • Users who have thanked CFMaBiSmAd for this post:

    matz0rz (06-27-2013)

  • #8
    New Coder
    Join Date
    Oct 2011
    Posts
    41
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Resolved!!!

    PHP Code:
    public function search_ticket($whereClause){
            
    $tickets = array();
            
    $sql ='SELECT * FROM scalpler_tickets WHERE artist LIKE '.$whereClause.' OR date LIKE '.$whereClause.' OR location LIKE '.$whereClause;
            
    $q $this->_db->prepare($sql);
            
    $q->execute();
            
    $data $q->fetchAll();
            
    var_dump($q);
            foreach(
    $data as $ticket){
                
    $tickets[] = array('id'=>$ticket['id'],
                                        
    'artist'=>$ticket['artist'],
                                        
    'date'=>$ticket['date'],
                                        
    'time'=>$ticket['time'],
                                        
    'location'=>$ticket['location'],
                                        
    'price'=>$ticket['price'],
                                        
    'ticketsleft'=>$ticket['ticketsleft'],
                                        
    'avator'=>$ticket['avator'],
                                        
    'type'=>$ticket['type']);
            }
            return 
    $tickets;
        } 
    PHP Code:
    if(isset($_POST['searchSubmit'])){
                
    $query "'%".$_POST['searchEvents']."%'";
                
    $_SESSION['tickets'] = $this->model->search_ticket($query);
                if(empty(
    $_SESSION['tickets'])) echo '<h1 style="color:white">WTF are you looking for???</h1>';
            } 


  •  

    Posting Permissions

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