display mysql query result from array parameters php

Question

I'll do my best to explain i create a query to return rows for a search function ex : return row WHERE id LIKE = __

here's my code :

if(!empty($champs)){
$table[0]["prog"] = array('mydb.message','Message'); 

$table[1]["prog"]  = array('mydb.newtable','Username','nom','prenom');

as the table and columns to look into

            $nb_result =0;


            for($i =0 ; $i < count ($table); $i++)
            {                       
                $prog_tab = $table[$i]["prog"];         


                             $sql = sprintf("SELECT * 
                                            FROM %s 
                                            WHERE 1 ",
                                $prog_tab [0], 
                                DEFAULT_ACCESS_LEVEL);  

                for($j = 1; $j < count ($prog_tab ); $j++)
                {
                        $sql .= sprintf(" OR %s LIKE '%s' ",
                                $prog_tab [$j],

                                $this->ins_string("%".$champs."%"), 
                                DEFAULT_ACCESS_LEVEL);
                }   
                echo $sql;
                        /*$sql =  $table[$i]["user"][0]  . ' ---> ' . $sql."<br>"; */
            $query = mysql_query($sql) or die(mysql_error());

            while($rows = mysql_fetch_array($query)){
                if($table[$i]["prog"][1] == "Message"){
                    echo $rows['Sender']." : &nbsp".$rows['Message']."<br />";
                    }
                    else{
                        echo $rows['Username']."&nbsp".$rows['nom']."&nbsp".$rows['prenom']."<br />";
                    }
                }
            $nb_result += mysql_num_rows($query);

            }           


        echo "<br /><h1>".$nb_result."</h1>";
        }

the problem is when i display the query it returns all the rows from my 2 tables and just ignore the LIKE %$champs%

NOTE* when i display the query it seems fine : SELECT * FROM mydb.newtable WHERE 1 OR Username LIKE '%$champs%' OR nom LIKE '%$champs%' OR prenom LIKE '%$champs%' and $nb_result always returns 48 (amount of rows i have in the two tables combined )

Marc B · Accepted Answer · 2013-05-14 17:40:40Z

1

A where clause basically boils down a boolean decision for the database, "include this row, or don't include it". Since you're doing

SELECT ... WHERE 1 OR ... OR ... OR ...
                 ^--

you're ALWAYS producing a 'true' value, so all rows will match and be included.

Remember your boolean truth tables. true OR anything is true.

answered May 14, 2013 at 17:40

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

math487 Over a year ago

thank you very much!! i feel a bit stupid now ahah i forgot that 1 is for the AND

Collectives™ on Stack Overflow

display mysql query result from array parameters php

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related