4 Replies - 567 Views - Last Post: 01 March 2013 - 10:00 AM Rate Topic: -----

#1 AlexxanderX  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 54
  • Joined: 13-January 12

MySQLi Where Like syntax

Posted 27 February 2013 - 06:12 AM

Hello! I have a problem with MySQLi:
if ($caut=="CodOferta")
        $commmand = $connec->query("SELECT * FROM ".$l." WHERE ".$caut."='".$_POST['cautare']."'") or die($mysqli->error.__LINE__);
else
{
	$commmand = $connec->query("SELECT * FROM ".$l." WHERE '".$caut."' LIKE '%".$_POST['cautare']."%'") or die($mysqli->error.__LINE__);
	echo $l."</br>";
}
if($commmand->num_rows > 0) 
{ ... }


When $caut = "CodOferta" it show the results, but when is not equal doesn't show me nothing!
I tried to in the else for $command to put
"SELECT * FROM ".$l." WHERE '".$caut."' LIKE '%test%'"
but still nothing.
$l is a variable and it's change his value every time( all code is in a while) and in one of tabels I have this:
Cod oferta |Adresa                              | Nume proprietar | Numar telefon | Adresa email
------------------------------------------------------------------------------------------------
0001       | -tested again and again AND FINAL- | Je Mapel        | -tested-      | i@hate.this 

( click on "view source" for a good view)
and I search in 2nd column, $caut = "Adresa".
How to resolve my problem, I searched on net but I can't find the solution to my code.

This post has been edited by AlexxanderX: 27 February 2013 - 06:16 AM


Is This A Good Question/Topic? 0
  • +

Replies To: MySQLi Where Like syntax

#2 codine24/7  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 54
  • Joined: 12-February 12

Re: MySQLi Where Like syntax

Posted 28 February 2013 - 01:39 AM

you should honestly be using prepared statements for user input.



ob_start();

$db_args = array('host'=>'','username'=>'','password'=>'','db'=>'');

$sql = 'SELECT * FROM tblname WHERE colname = ?';   

$mysql = new Mysqli($db_args['host'],$db_args['username'] , $db_args['password' , $db_args['db']]);     
//mysqli database connection object
        
        if( !$mysql->connect_errno )//if no connection error
        {
            $stmt = $mysql->stmt_init();//create statement
            
            if($stmt->prepare( $sql ))//prepare sql
            {
                $stmt->bind_param('s' , $filtered_user_input);
//bind prepared statement
                    
                $stmt->execute();
                
                $stmt->store_result();//not required but good practice
                
                $out_param_col_one = NULL;//out put paramaters
                $out_param_col_two = NULL;
                
                $stmt->bind_result($out_param ,  $out_param_col_two);

                while($stmt->fetch())
                {
                   echo $out_param_col_one.'<br />';
                   echo $out_param_col_two;//display data
                }

                $stmt->free_result();//not required

                $stmt->close();//close statement or prepare other sql
             }
                $mysql->close();//close database connection
           }
ob_end_flush();



prepared statements
http://php.net/manua...qli.prepare.php

mysqli
http://www.php.net/m...lass.mysqli.php

mysqli_stmt
http://www.php.net/m...mysqli-stmt.php

This post has been edited by codine24/7: 28 February 2013 - 01:42 AM

Was This Post Helpful? 2
  • +
  • -

#3 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,991
  • Joined: 08-June 10

Re: MySQLi Where Like syntax

Posted 01 March 2013 - 05:01 AM

AlexxanderX said:

How to resolve my problem, I searched on net but I can't find the solution to my code.

Look closely at the differences in how the $caut variable is used inside the queries.

Consider this:
/* This will work just fine: */
SELECT id FROM users
WHERE name = 'John';

/* But this will not: */
SELECT id FROM users
WHERE 'name' = 'John';


The problem with the second version is that the name of the "name" column is quoted like a string, which means the SQL server will treat it as a string. So rather than comparing the value of each "name" field to the string "John", it's comparing the string "name" to the string "John" for each row, which will obviously never be true.
Was This Post Helpful? 1
  • +
  • -

#4 AlexxanderX  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 54
  • Joined: 13-January 12

Re: MySQLi Where Like syntax

Posted 01 March 2013 - 06:52 AM

The if work correctly, but the else part is the problem. I corrected in:
$commmand = $connec->query("SELECT * FROM ".$l." WHERE ".$caut." LIKE '%test%'") or die($mysqli->error.__LINE__);


But when search( I search in all my tables( every time after a while $l get the value of next table name)) I see all tables infos, instead of searching for what I typed.
Was This Post Helpful? 0
  • +
  • -

#5 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,991
  • Joined: 08-June 10

Re: MySQLi Where Like syntax

Posted 01 March 2013 - 10:00 AM

OK. Try logging, or simply var_dumping the actual SQL queries that are being executed, so you can examine them and see what exactly is happening there. Might shed some light on what the problem is. (Usually does.)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1