multiple columns in where clause query mysql

Question

selectbox with multiple columns from mysql database

The following code was the winning code from zgr024

       <?php 
    include '../config.php';
    $sql = "SELECT * FROM megabase";  
    $resultaat = mysql_query($sql) or die (mysql_error());  
    $domains = array();
    while ($row = mysql_fetch_array($resultaat))       
    { 
       if (!empty($row['domeinnaam1'])) $domains[] = $row['domeinnaam1'];
       if (!empty($row['domeinnaam2'])) $domains[] = $row['domeinnaam2'];
    }
?>

   <select size="1" name="domeinnaam">
<?php
    foreach ($domains as $domain)
    {
        echo "<option>$domain</option>";
    } 
?>
    </select>

But this code resulted in an expected error on the next page where i have the following code.

    <?php 

     //MySQL Database Connect
     include 'config.php';

    $domeinnaam=$_POST['domeinnaam']; 

    $data = 'SELECT * FROM megabase WHERE domeinnaam = "'.$domeinnaam.'"'; 
      $query = mysql_query($data) or die("Couldn't execute query. ". mysql_error()); 
      $data2 = mysql_fetch_array($query); 


    ?>

How do i change the last code to work with the first code:

I have tried:

        <?php 

         //MySQL Database Connect
         include 'config.php';

        $domeinnaam=$_POST['domeinnaam'];
       $domeinnaamquerry='domeinnaam1'.'domeinnaam2'.'domeinnaam3'.'domeinnaam4'.'domeinnaam5'.'do meinnaam6'.'domeinnaam7'.'domeinnaam8'.'domeinnaam9'.'domeinnaam10';

        $data = 'SELECT * FROM megabase WHERE $domeinnaamquerry = "'.$domeinnaam.'"'; 
          $query = mysql_query($data) or die("Couldn't execute query. ".             mysql_error()); 
          $data2 = mysql_fetch_array($query); 


        ?>

But i receive the following error: Couldn't execute query. Unknown column '$domeinnaamquerry' in 'where clause'

Okey got it working now it is sloppy but working thanks everyone for your input it was helpful. and thanks for the warnings.

here is the code

     <?php 

      //MySQL Database Connect
      include 'config.php';

     $domeinnaam=$_POST['domeinnaam']; 

     $data = 'SELECT * FROM megabase WHERE domeinnaam1="'.$domeinnaam.'" OR domeinnaam2="'.$domeinnaam.'" OR domeinnaam3="'.$domeinnaam.'" OR domeinnaam4="'.$domeinnaam.'" OR domeinnaam5="'.$domeinnaam.'" OR domeinnaam6="'.$domeinnaam.'" OR domeinnaam7="'.$domeinnaam.'" OR domeinnaam8="'.$domeinnaam.'" OR domeinnaam9="'.$domeinnaam.'" OR domeinnaam10="'.$domeinnaam.'"    '; 
       $query = mysql_query($data) or die("Couldn't execute query. ". mysql_error()); 
       $data2 = mysql_fetch_array($query); 


     ?>

Hint: read about [PHP strings](http://www.php.net/manual/en/language.types.string.php) and [SQL injection](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php). — Álvaro González, Mar 04 '13 at 16:07
There's a good bit wrong here. 1) You've used single ticks for `$data`, so `$domeinnaamquerry` doesn't get interpolated. 2) When you defined `$domeinnaamquerry`, you've glued all the column names together as one giant string with no delimiters. 3) Even if you didn't, you can't use one WHERE clause to compare one constant to multiple columns and 4) SQL injection. — Mr. Llama, Mar 04 '13 at 16:09
I hate to be the one to tell you this, but you need to redesign your database if you can. You need to make a new DomainNames table and set a FK to your megabase table. You should never have columnname1-columnname10 in a database. I understand if it is too late for you to do so now, but in the future, proper database design would have eliminated most of your problems. — Mike C., Mar 04 '13 at 16:13
Mike C thanks for that it is not something i do every day. And to be honest i only just found out that i couldnt add more domains to a record i was excel thinking instead of mysql thinking. — , Mar 04 '13 at 16:18
He is trying to get the value of a options selected in his previous page... what do any of these comments have to do with this question? — zgr024, Mar 04 '13 at 16:22

score 1 · Accepted Answer · answered Mar 04 '13 at 16:23

1

Try

<?php 

    //MySQL Database Connect
    include 'config.php';

    $domeinnaam=$_POST['domeinnaam']; 

    $data = "SELECT * FROM megabase WHERE domeinnaam1 = '$domeinnaam' OR domeinnaam2 = '$domeinnaam' "; 
    $query = mysql_query($data) or die("Couldn't execute query. ". mysql_error()); 
    $data2 = mysql_fetch_array($query); 

?>

answered Mar 04 '13 at 16:23

zgr024

1,175
1
12
26

Make sure you add the remaining columns to it and this should do what you need. – Mike C. Mar 04 '13 at 16:28
I just did this without having seen your answer zgr024 – Mar 04 '13 at 16:29
But thank you very much because it is the right code. Once again you nailed it. Superb. Now i go to the error on page 3 which is the last page. I try to crack it myself this time. Lets see if my brain wants to do some work. Haha – Mar 04 '13 at 16:29
I have some strange code i dont understand WHERE domeinnaam = ".'"'.$Key.'"'; how to add the OR function in here. I will open a new question for this one part 3 – Mar 04 '13 at 16:37
Alright Part 3: http://stackoverflow.com/questions/15206510/multiple-columns-in-where-clause-query-mysql – Mar 04 '13 at 16:42

score 0 · Answer 2 · answered Mar 04 '13 at 16:10

0

If you want a variable to be interpreted as its value in PHP, you can't use single quotes.

$foo = 'World';

echo 'Hello $foo'; // Hello $foo
echo "Hello $foo"; // Hello World

Thus, you want

$data = "SELECT * FROM megabase WHERE $domeinnaamquerry = '".$domeinnaam."'";

Although read this and this to understand why you REALLY don't want that.

answered Mar 04 '13 at 16:10

Dancrumb

26,597
10
74
130

As GigaWatt points out, there's a whole bunch of problems here; this only addresses 2 of them. – Dancrumb Mar 04 '13 at 16:10
Thnks Dancrumb, I get the following error now. And SQLInjection this is not for online use but thanks for the info! The error i receive now is: Couldn't execute query. Unknown column 'domeinnaam1domeinnaam2domeinnaam3domeinnaam4domeinnaam5domeinnaam6domeinnaam7domeinnaam8domeinnaam9domeinnaam10' in 'where clause' – Mar 04 '13 at 16:13
2

his problem is he's trying to dynamically populate the columnname in the where clause, not the data values. He's trying to make WHERE columnname1='bob' or columnname2='bob' or columnname3='bob'..etc... – Mike C. Mar 04 '13 at 16:15
Yes Mike C that is exactly what i need. – Mar 04 '13 at 16:17
I've just posted the answer he is looking for – zgr024 Mar 04 '13 at 16:24

multiple columns in where clause query mysql

2 Answers2

Linked