Calling function in middle of mysql query.

[berde.jitendra]

Hi All,

 

I am working on system where i am storing column names in one table of database. To retrieve value of columns with separation of comma (exp. clol1, col2,col3..). for this i am using one function which i am calling middle of mysql query. but i am not getting fetch result over there. Below is the function i have to fetch column names.

 

function retrieve_columns()
{

$brand_id = $_SESSION['SESS_PRODUCT_CODE'];

global $columnlist;

$columns_query= "Select column_name from columns_list_to_display where brand_id=$brand_id";
$result=mysql_query($columns_query);
$row_num=mysql_num_rows($result);
$i=0;
while ($row = mysql_fetch_array($result))
   {  
     $columnlist = ""; 
     $i++;
 if($i != $row_num) { echo $row['column_name'].", "; } else { echo $row['column_name'].""; }	   	 
 }
}

 

I am writting query like as below:

$query  = "SELECT " . $columnlist . "FROM $userlead_table 
          LEFT JOIN notes ON $userlead_table.id = notes.leads_id 
   	      LEFT JOIN lead_status ON $userlead_table.status = lead_status.status_code
          WHERE ((Date(a.submitted)>='$datefrom' AND Date(a.submitted)<='$dateto'))
      ORDER BY a.submitted DESC"; 

 

Please let me know where i am doing wrong. Hope one of this forum member would have proper solution for it.

 

Thanks in advance.

 

Regards,

Jitendra

 

[trq]

Firstly, the idea of storing your columns in another database table is ridiculously inefficient and I see absolutely no reason for doing so.

 

Secondly, your not calling any function in the middle of your query.

 

Secondly, you should be returning the data, not echoing it.

[berde.jitendra]

Hi Thanks for your reply.

 

I am new for php mysql. Can you let me know how do wrtie query for it with return. It would be great help for me.

 

I do have multiple client and every client has different list of columns and there are hardly similarity between it. That why storing columns name in different table to create query of select as per logged client. Do you have any better and easy solution for it. then please let me know.

 

Thanks in advance.

 

Regards,

Jitendra Berde

[trq]

New to php yet you have clients? Ridiculous.

[berde.jitendra]

Hi Thorpe,

 

. It’s really a bad experience on this Forum. As admin you can say single "NO" if you dont want to help anyone post. But if you dont know the reality of person requirement then dont use your own experience logic at all. Anyone can ask a question if they stuck on it as you are running forum over here. It seems you are over confident on your knowledge and think all are are on same level  like you and they will not have any such normal question which you could not even think of it.

 

The project i am working is not for client it’s for my own practice session. However it’s good to know  that  phpfreaks admin is not that much friendly.

 

[trq]

Ok, well first things first. Column names such as col1 col2 col3 etc etc wreak of the need for a database refactor. Column names should describe the actual data they contain.

 

If you need multiple fields of similar name like that you need to look into "Database Normalisation". Google it.

[berde.jitendra]

Thank you for your reply Thorpe.

 

I have already Google it for this and took idea about it. However want to give brief idea about real requirement I am thinking on it. I would have two forms where only three fields will be common. (like: name, contact No. email .etc) and other will be different. If I store it in one table then it would create confusion while displaying the data of two different because both have only three fields same and other are different.

 

I want to run a query to columns list when particular user logged in who is admin for a particular form. Suppose I have two users. 1 is for first form and 2nd for 2nd form. Now 1st user would have different fields to display compare to 2nd user. My confusion is how system would know that particular fields are belonging to particular user if I store my both form data in single table. For example one form have country fields where 2nd don’t. In this case only for first form country field data will need to display. In 2nd from there is only state fields so it will display state data.

 

Both forms will display different column data in result. Want to create more dynamic where system will select columns and its title to display once particular user get logged in.

 

I hope I have explained my requirement clearly to you.

 

Thanks in advance.