Functions inside mysql_query? Possible?

Entiranz · March 21, 2012

Hi, im trying to create a simple search engine where you can pick how many columns you wish to search for and what you wish to search for in a database, and im trying so make it so that you don't have to write any SQL code manually. Problem is, i can only think of one way of doing so and my attempts have so far failed.

This is the current code:

<html>
<body>
<?php

class Storage
{

   var $amount;
   var $ope = array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19);
   var $selope = array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19);
   var $selope2 = ", ";
   var $outprintln1 = 1;
   var $outprintln2 = 1;
   var $check1 = 0;
   var $check2 = 0;
   var $check3 = 1;
   var $check4 = 0;
   var $lastcheck = 0;
   var $BREAKER1 = 0;
   var $BREAKER2 = 0;
   var $BREAKER3 = 0;
   var $BREAKER4 = 0;
   var $BREAKERvar;

   function __construct($nr, $Cho1, $Cho2, $Cho3, $Cho4,
                        $Cho5, $Cho6, $Cho7, $Cho8, $Cho9,
                        $Cho10, $Cho11, $Cho12, $Cho13, $Cho14,
                        $Cho15, $Cho16, $Cho17, $Cho18, $Cho19, $Cho20, $BREAKval)
   {

      $this->amount = $nr;
      $this->ope[0] = $Cho1;
      $this->ope[1] = $Cho2;
      $this->ope[2] = $Cho3;
      $this->ope[3] = $Cho4;
      $this->ope[4] = $Cho5;
      $this->ope[5] = $Cho6;
      $this->ope[6] = $Cho7;
      $this->ope[7] = $Cho8;
      $this->ope[8] = $Cho9;
      $this->ope[9] = $Cho10;
      $this->ope[10] = $Cho11;
      $this->ope[11] = $Cho12;
      $this->ope[12] = $Cho13;
      $this->ope[13] = $Cho14;
      $this->ope[14] = $Cho15;
      $this->ope[15] = $Cho16;
      $this->ope[16] = $Cho17;
      $this->ope[17] = $Cho18;
      $this->ope[18] = $Cho19;
      $this->ope[19] = $Cho20;
      $this->BREAKERvar = $BREAKval;

   }

   function setresultinput()
   {
      
      while($this->BREAKER1 < $this->amount){

            if($this->ope[$check1] != NULL)
            {
            
               if($this->check1 == 0 || $this->check1 == 1 || $this->check1 == 2)
               {

                  $this->selope[$check2] = "`anställda`.`$this->ope[$check1]`";

               }
               elseif($this->check1 == 3 || $this->check1 == 4 || $this->check1 == 5 || $this->check1 == 6 ||
                      $this->check1 == 7 || $this->check1 == 8 || $this->check1 == 9 || $this->check1 == 10)
               {

                  $this->selope[$check2] = "`privat information`.`$this->ope[$check1]`";

               }
               elseif($this->check1 == 11 || $this->check1 == 12 || $this->check1 == 13 || $this->check1 == 14 || $this->check1 == 15)
               {
                  
                  $this->selope[$check2] = "`kontakt information`.`$this->ope[$check1]`";

               }
               elseif($this->check1 == 16 || $this->check1 == 17 || $this->check1 == 18 || $this->check1 == 19)
               {
                  
                  $this->selope[$check2] = "`avdelningar`.`$this->ope[$check1]`";

               }

               $this->check1++;
               $this->check2++;
               $this->BREAKER1++;
            }
            else
            {
            
               $this->check1++;

            }

         }

   }

   function getresultinput()
   {
      $this->check3 = $this->check2;
      $this->check2 = 1;
      while($this->BREAKER2 < $this->amount)
      {
         
         if($this->BREAKER3 < $this->check3){
            echo $this->selope[$this->check2] . ", ";
            $this->BREAKER3++;
         }
         else
         {
            echo $this->selope[$this->check2];
         }

         $this->check2++;
         $this->BREAKER2++;
      }

   }

   function SQL()
   {
      
      $con = mysql_connect("localhost", "root", "");

      mysql_select_db("chefens tabeller", $con);


         $result = mysql_query("SELECT `this->getresultinput()`
                                FROM `anställda` JOIN `avdelningar` ON (`anställda`.`Avdelning ID`=`avdelningar`.`ID`)
                                JOIN `kontakt information` ON (`anställda`.`Kontakt ID`=`kontakt information`.`ID`)
                                JOIN `privat information` ON (`anställda`.`PI ID`=`privat information`.`ID`)
                                WHERE (`anställda`.`ID` < 6)
                                LIMIT 0, 30")
                                or die (mysql_error());

      while($row = mysql_fetch_array($result))
      {
         while($this->BREAKER4 <= $this->amount)
         {
            
            echo $row[$this->selope[$this->lastcheck]] . " " ;
            if($this->check4 = $this->amount)
            {
            
               echo  $row[$this->selope[$this->lastcheck]];
            
            }
            $this->lastcheck++;
            $this->BREAKER4++;
         }
         echo "<br />";
         $this->lastcheck = 1;
         $this->BREAKER4 = 1;
      }
   }

}





?>
</body>
</html>

Its OOP, i dont know if that makes any difference, im a newbie

The problem is, i don't know if you can insert a function call inside mysql_query. If i can and im doin it wrong, please help me, and if i cant, ill just have to restructure the code to something simpler.

smerny · March 21, 2012

mysql_query takes a string. you can concatenate literal strings, string variables, functions that return strings...

your example is missing the $ identifier though

Psycho · March 21, 2012

The problem is, i don't know if you can insert a function call inside mysql_query.

No, you cannot. FWIW, you can create stored procedures within the database that are kind of like functions, but that is not what you want here. For example, if you let the user define the fields to be used in the query you would probably give them a list a select fields. So, let's assume the fields are named as an array <select name="field_list[]">

Then to build the SELECT portion of your query you would loop through those input values and create the list. (Note: the following is just a very basic example and does not encompass all validations/sanitizations that should be conducted)

//Get list of submitted fields for the select statement
//Remove empty values
$field_list = array_filter($_POST['field_list'];
//Put each field name in single quotes
foreach($field_list as &$field_name)
{
    $field_name = "'{$field_name}'";
}
//Implode the field list into a string to be used in the query
$select_str = implode(', ', $field_list);

//Use the dynamically created list of fields in the query
$query = "SELECT {$select_str}
          FROM table_name
          WHERE 1 = 1";

Now, that is just a basic example regarding the creation of the fields for the select part of the query. You would need to build similar logic for any other parts of the query that will be user defined (e.g. the WHERE clause, ORDER BY, etc.)

What you need to do is to dynamically create the query using PHP code. I'm not going to try and deconstruct your code to try and understand it, but here is an example.

Entiranz · March 21, 2012

Okay progress... i think.

The SQL isn't giving me any errors anymore, but the output isn't exactly was i was looking for.

This is a copy paste of what im getting:

0 01 12 23 34 45 56 67 78 89 910 1011 1112 1213 1314 1415 1516 1617 1718 1819 19

This is after having converted the array containing all field names into a string. In this output ive used 20 field names. My guess is that the function that filters through all field names has failed somewhere along the line, giving the array these values instead somehow. any advice? The code for it can be found above in my first post in the setresultinput() function.

Psycho · March 21, 2012

We have no way to determine if the output is right or wrong. We don't know what fields you expect to be included or which ones were actually included. The function is of no value to us without understanding the logic of your DB structure and what the inputs are.

For now, you need to focus on the Query that is created - not the results of the query. Just work on ONE part of the process. Once that works, move on to another process. As I showed above, you could work on the fields that are included in the SELECT portion of the query based upon the user selections. That's where I would start.

So, just work on processing the user input to get the SELECT portion. Then verify the results by echoing the query. You could test the generated query just to verify the fields are created correctly, but I wouldn't focus on the output.

Entiranz · March 21, 2012

Okay, ive made some progress and will try to explain clearly. The problem i had was that i never called upon the filter (clumsy of me) so the string had no specified values except for the random numbers i gave it in the start to declare the array. Now ive called on the filter and it works... kinda.

Lets say that one of the field names would be this: `table_name`.`field_name`

This is what im getting: `table_name`.`Array[array_number]`

the array part there is in the code written as such that it should take the value of the field name given in the form i've made. Instead of that, it just says Array[0 or sumthin] in the output. I might have made some sort of syntax error, i don't know. So lets focus on the filter function.

the filter function:

function setresultinput()
   {
      
      while($this->BREAKER1 < $this->amount){

            if($this->ope[$this->check1] != NULL)
            {
            
               if($this->check1 == 0 || $this->check1 == 1 || $this->check1 == 2)
               {

                  $this->selope[$this->check2] = "`anställda`.`$this->ope[$this->check1]`";

               }
               elseif($this->check1 == 3 || $this->check1 == 4 || $this->check1 == 5 || $this->check1 == 6 ||
                      $this->check1 == 7 || $this->check1 == 8 || $this->check1 == 9 || $this->check1 == 10)
               {

                  $this->selope[$this->check2] = "`privat information`.`$this->ope[$this->check1]`";

               }
               elseif($this->check1 == 11 || $this->check1 == 12 || $this->check1 == 13 || $this->check1 == 14 || $this->check1 == 15)
               {
                  
                  $this->selope[$this->check2] = "`kontakt information`.`$this->ope[$this->check1]`";

               }
               elseif($this->check1 == 16 || $this->check1 == 17 || $this->check1 == 18 || $this->check1 == 19)
               {
                  
                  $this->selope[$this->check2] = "`avdelningar`.`$this->ope[$this->check1]`";

               }

               $this->check1++;
               $this->check2++;
               $this->BREAKER1++;
            }
            else
            {
            
               $this->check1++;

            }

         }

   }

I hope its just some syntax error within the $this->selope[$this->check2] = "`avdelningar`.`$this->ope[$this->check1]`";.

I hope i don't have to post more code, feel free to yell at me if i do.

Entiranz · March 23, 2012

Ive managed to solve it!

Just what i thought it was, a syntax error. After fixing that and a few things in mysql_fetch_array ive managed to get it to work just like i want it to. Thanks for all your help, probably wouldnt have managed to get past the problem at the start without help.

Psycho · March 23, 2012

Glad you got it worked out. For future reference, I thought I'd let you know that I decided not to respond further because the information you were providing was lacking in clarity. You posted an over-elaborate complete class without only a hint of what the expected outcome should be and didn't provide any information as to what the input would be.

In the future you will get better responses if you can narrow down the problem area of code, explain what values are going in, what is being produced currently, and how you want that to be different.

Sign In

Functions inside mysql_query? Possible?

Recommended Posts

Entiranz

Link to comment

Share on other sites

smerny

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

Entiranz

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

Entiranz

Link to comment

Share on other sites

Entiranz

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information