Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    New Coder
    Join Date
    Sep 2011
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with sql query

    Hi!
    Is it possible to make this happen?

    Code:
    $sql="SELECT 
    CONCAT(contact) Contact, 
    CONCAT(date) Date, 
    CONCAT(p1453_01) '1. How old are you?', 
    CONCAT(p1453_02) '2. Do you own a car?', 
    CONCAT(p1453_03) '3. Have your friends a car?' 
    FROM survey WHERE 
    p1453_02 
    like 
    'Yes%' 
    ORDER BY datum DESC";
    to this:
    Code:
    $sql="SELECT 
    CONCAT( get value from other place ) get value from other place , 
    CONCAT(get value from other place ) get value from other place , 
    CONCAT(get value from other place ) ' get value from other place ', 
    CONCAT(get value from other place ) ' get value from other place ', 
    CONCAT(get value from other place ) ' get value from other place ' 
    FROM survey WHERE 
    get value from other place  
    like 
    'get value from other place %' 
    ORDER BY datum DESC";
    I would like to set the value for each field from admin side of my site and then run the query
    Hope you guys understand what i mean

    Any advice?

    Regards Tony

  • #2
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,468
    Thanks
    8
    Thanked 1,085 Times in 1,076 Posts
    You build the string on admin side and use it on the other side ...

    // start the string ...
    $sql="SELECT ";

    // you loop here to add as many as you need ... each loop is different values ...
    $sql .= "CONCAT( $value1 ) $value2 ,";
    // end of loop.

    // continue adding on ...
    $sql .= "FROM survey WHERE ";

    etc ...

    Keep building (or appending to) $sql until you have the complete query string.
    Save it to either a cookie or SESSION variable.
    Now, when you go to any other script, recall the query and use it.



    .

  • #3
    New Coder
    Join Date
    Sep 2011
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    is there a way to make the string to the sql query as forms that the user in the admin only marks what info should be used in the query?

    Or must they write the quey?

    hope you get what i mean :-)

    Quote Originally Posted by mlseim View Post
    You build the string on admin side and use it on the other side ...

    // start the string ...
    $sql="SELECT ";

    // you loop here to add as many as you need ... each loop is different values ...
    $sql .= "CONCAT( $value1 ) $value2 ,";
    // end of loop.

    // continue adding on ...
    $sql .= "FROM survey WHERE ";

    etc ...

    Keep building (or appending to) $sql until you have the complete query string.
    Save it to either a cookie or SESSION variable.
    Now, when you go to any other script, recall the query and use it.



    .

  • #4
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,468
    Thanks
    8
    Thanked 1,085 Times in 1,076 Posts
    Sure ...

    Create a form with checkboxes or drop-down selects.
    The admin picks from the "ala carte" menu and clicks submit.
    The query string is built and processed, or whatever is supposed to happen.

    I think I'm understanding it.

    Or, are you trying to say this ...

    The admin selects what items to query, and that selection is saved
    somewhere, so anyone else only queries what the admin has selected?

    Is this something the admin is querying for themselves, or is the admin
    setting the query for everyone else?



    .
    Last edited by mlseim; 09-20-2011 at 03:28 AM.

  • #5
    New Coder
    Join Date
    Sep 2011
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You got it :-)
    admin selects for someone else from user site they pull the trigger

    Quote Originally Posted by mlseim View Post
    Sure ...

    Create a form with checkboxes or drop-down selects.
    The admin picks from the "ala carte" menu and clicks submit.
    The query string is built and processed, or whatever is supposed to happen.

    I think I'm understanding it.

    Or, are you trying to say this ...

    The admin selects what items to query, and that selection is saved
    somewhere, so anyone else only queries what the admin has selected?

    Is this something the admin is querying for themselves, or is the admin
    setting the query for everyone else?



    .

  • #6
    New Coder
    Join Date
    Sep 2011
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    can you show a example for a checkbox how to write the value fields so they can be transmitted to the query?

    Quote Originally Posted by mlseim View Post
    Sure ...

    Create a form with checkboxes or drop-down selects.
    The admin picks from the "ala carte" menu and clicks submit.
    The query string is built and processed, or whatever is supposed to happen.

    I think I'm understanding it.

    Or, are you trying to say this ...

    The admin selects what items to query, and that selection is saved
    somewhere, so anyone else only queries what the admin has selected?

    Is this something the admin is querying for themselves, or is the admin
    setting the query for everyone else?



    .

  • #7
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,468
    Thanks
    8
    Thanked 1,085 Times in 1,076 Posts
    Take this form as an example, the checkbox names are the same because it becomes an array ...

    <form action="setquery.php" method="post">
    <input type="checkbox" name="build[]" value="p1453-01"> This is item 1 <br />
    <input type="checkbox" name="build[]" value="p1453-02"> This is item 2 <br />
    <input type="checkbox" name="build[]" value="p1453-03"> This is item 3 <br />
    <input type="checkbox" name="build[]" value="p1453-04"> This is item 4 <br />
    <input type="submit" name="submit" value="Create Query">
    </form>

    Now in the PHP script "setquery.php", we'll bring in the array. The array will
    only contain the values that were checked.

    Try this as an example to test it out ...
    PHP Code:
    <?php

    // get checkbox array
    $checkarray=$_POST['build'];

    // I don't know where the 2nd value needs to come from.
    // I'm getting one from the checkbox array ... the other one?
    // Very hard to do, because I don't have a clue what your script is supposed to do.

    // start the string ...
    $sql="SELECT ";

    // ??
    $value2"something";

    foreach(
    $checkarray as $item){
    $sql .= "CONCAT( $item )  '$value2' , ";
    }

    // continue adding on ...
    $sql .= "FROM survey WHERE ";

    echo 
    "Here is the query so far ... <br />";
    echo 
    "<b>$sql</b>";

    ?>

    .

  • #8
    New Coder
    Join Date
    Sep 2011
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i have everything in same table if thats good for the task, but the items can be from 10 to 50 pieces so i dont know if your thing can handle that much.

    But this maybe dont care how many?
    PHP Code:
    foreach($checkarray as $item){ 
    $sql .= "CONCAT( $item )  '$value2' , "

    Quote Originally Posted by mlseim View Post
    Take this form as an example, the checkbox names are the same because it becomes an array ...

    <form action="setquery.php" method="post">
    <input type="checkbox" name="build[]" value="p1453-01"> This is item 1 <br />
    <input type="checkbox" name="build[]" value="p1453-02"> This is item 2 <br />
    <input type="checkbox" name="build[]" value="p1453-03"> This is item 3 <br />
    <input type="checkbox" name="build[]" value="p1453-04"> This is item 4 <br />
    <input type="submit" name="submit" value="Create Query">
    </form>

    Now in the PHP script "setquery.php", we'll bring in the array. The array will
    only contain the values that were checked.

    Try this as an example to test it out ...
    PHP Code:
    <?php

    // get checkbox array
    $checkarray=$_POST['build'];

    // I don't know where the 2nd value needs to come from.
    // I'm getting one from the checkbox array ... the other one?
    // Very hard to do, because I don't have a clue what your script is supposed to do.

    // start the string ...
    $sql="SELECT ";

    // ??
    $value2"something";

    foreach(
    $checkarray as $item){
    $sql .= "CONCAT( $item )  '$value2' , ";
    }

    // continue adding on ...
    $sql .= "FROM survey WHERE ";

    echo 
    "Here is the query so far ... <br />";
    echo 
    "<b>$sql</b>";

    ?>

    .

  • #9
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,468
    Thanks
    8
    Thanked 1,085 Times in 1,076 Posts
    I'm not even sure what your script will do ...
    No idea why you are using CONCAT ... or if there's a better way to query.

    My goal was to show you how to do the checkboxes and loop.
    The query part is an unknown factor.


    .

  • #10
    New Coder
    Join Date
    Sep 2011
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    this is weird, when i export it makes a duplicate of each row any idea what the problem is?

    I use CONCAT to change the text :-) here you see

    Code:
    case 1429: 
    $sql="SELECT ";
    $sql .= "CONCAT( projektnr ) Projektnr ,";
    $sql .= "CONCAT( foretag ) Företag ,";
    $sql .= "CONCAT( postadress ) Postadress ,";
    $sql .= "CONCAT( postnr ) Postnr ,"; 
    $sql .= "CONCAT( ort ) Ort ,";
    $sql .= "CONCAT( besoksadress ) Besöksadress ,";
    $sql .= "CONCAT( telenr ) Telenr ,"; 
    $sql .= "CONCAT( befattning ) Befattning ,"; 
    $sql .= "CONCAT( dirtele ) Dirtele ,"; 
    $sql .= "CONCAT( email ) Email ,"; 
    $sql .= "CONCAT( huvudbransch ) Huvudbransch ,"; 
    $sql .= "CONCAT( branschkod ) Branschkod ,"; 
    $sql .= "CONCAT( orgnr ) Orgnr ,"; 
    $sql .= "CONCAT( antanst ) Antanst ,"; 
    $sql .= "CONCAT( anstallda_text ) 'Anställda text' ,";
    $sql .= "CONCAT( omsattning_text ) 'Omsättning text' ,"; 
    $sql .= "CONCAT( omsattning ) 'Omsättning i tkr' ,"; 
    $sql .= "CONCAT( kommun ) Kommun ,";
    $sql .= "CONCAT( lan ) Län ,";
    $sql .= "CONCAT( datum ) Datum ,"; 
    $sql .= "CONCAT( p1429_01 ) '$fragekod1429_01' ,";
    $sql .= "CONCAT( p1429_02 ) '$fragekod1429_02' ,"; 
    $sql .= "CONCAT( p1429_03 ) '$fragekod1429_03' ,";
    $sql .= "CONCAT( p1429_04 ) '$fragekod1429_04' ,";
    $sql .= "CONCAT( p1429_05 ) '$fragekod1429_05' ,";
    $sql .= "CONCAT( p1429_06 ) '$fragekod1429_06' ";
    $sql .= " FROM survey WHERE ";
    $sql .= " $sfraga1429 like '$utslagsfraga1429' ORDER BY datum DESC ";
    
    //execute query
    $result = @mysql_query($sql)
    	or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());
    
    //if this parameter is included ($w=1), file returned will be in word format ('.doc')
    //if parameter is not included, file returned will be in excel format ('.xls')
    if (isset($w) && ($w==1))
    {
    	$file_type = "msword";
    	$file_ending = "doc";
    }else {
    	$file_type = "vnd.ms-excel";
    	$file_ending = "xls";
    }
    //header info for browser: determines file type ('.doc' or '.xls')
    header("Content-Type: application/$file_type");
    header("Content-Disposition: attachment; filename=" . date('Ymd') . ".system.$file_ending");
    header("Pragma: no-cache");
    header("Expires: 0");
    
    /*	Start of Formatting for Word or Excel	*/
    
    if (isset($w) && ($w==1)) //check for $w again
    {
    	//define separator (defines columns in excel & tabs in word)
    	$sep = "\n"; //new line character
    
    	while($row = mysql_fetch_row($result))
    	{
    		//set_time_limit(60); 
    		$schema_insert = "";
    		for($j=0; $j<mysql_num_fields($result);$j++)
    		{
    		//define field names
    		$field_name = mysql_field_name($result,$j);
    		//will show name of fields
    		$schema_insert .= "$field_name:\t";
    			if(!isset($row[$j])) {
    				$schema_insert .= "NULL".$sep;
    				}
    			elseif ($row[$j] != "") {
    				$schema_insert .= "$row[$j]".$sep;
    				}
    			else {
    				$schema_insert .= "".$sep;
    				}
    		}
    		$schema_insert = str_replace($sep."$", "", $schema_insert);
    		$schema_insert .= "\t";
    		print(trim($schema_insert));
    		//end of each mysql row
    		//creates line to separate data from each MySQL table row
    		print "\n----------------------------------------------------\n";
    	}
    }else{
    	//define separator (defines columns in excel & tabs in word)
    	$sep = "\t"; //tabbed character
    
    	//start of printing column names as names of MySQL fields
    	for ($i = 0; $i < mysql_num_fields($result); $i++)
    	{
    		echo mysql_field_name($result,$i) . "\t";
    	}
    	print("\n");
    	//end of printing column names
    
    	//start while loop to get data
    	while($row = mysql_fetch_row($result))
    	{
    		//set_time_limit(60); 
    		$schema_insert = "";
    		for($j=0; $j<mysql_num_fields($result);$j++)
    		{
    			if(!isset($row[$j]))
    				$schema_insert .= "NULL".$sep;
    			elseif ($row[$j] != "")
    				$schema_insert .= "$row[$j]".$sep;
    			else
    				$schema_insert .= "".$sep;
    		}
    		$schema_insert = str_replace($sep."$", "", $schema_insert);
    		//this corrects output in excel when table fields contain \n or \r
    		//these two characters are now replaced with a space
    		$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
    		$schema_insert .= "\t";
    		print(trim($schema_insert));
    		print "\n";
    	}
    }
    break;
    Quote Originally Posted by mlseim View Post
    I'm not even sure what your script will do ...
    No idea why you are using CONCAT ... or if there's a better way to query.

    My goal was to show you how to do the checkboxes and loop.
    The query part is an unknown factor.


    .

  • #11
    New Coder
    Join Date
    Sep 2011
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i fixed it with this
    Code:
    $sql="SELECT DISTINCT ";
    Quote Originally Posted by El_Heso View Post
    this is weird, when i export it makes a duplicate of each row any idea what the problem is?

    I use CONCAT to change the text :-) here you see

    Code:
    case 1429: 
    $sql="SELECT ";
    $sql .= "CONCAT( projektnr ) Projektnr ,";
    $sql .= "CONCAT( foretag ) Företag ,";
    $sql .= "CONCAT( postadress ) Postadress ,";
    $sql .= "CONCAT( postnr ) Postnr ,"; 
    $sql .= "CONCAT( ort ) Ort ,";
    $sql .= "CONCAT( besoksadress ) Besöksadress ,";
    $sql .= "CONCAT( telenr ) Telenr ,"; 
    $sql .= "CONCAT( befattning ) Befattning ,"; 
    $sql .= "CONCAT( dirtele ) Dirtele ,"; 
    $sql .= "CONCAT( email ) Email ,"; 
    $sql .= "CONCAT( huvudbransch ) Huvudbransch ,"; 
    $sql .= "CONCAT( branschkod ) Branschkod ,"; 
    $sql .= "CONCAT( orgnr ) Orgnr ,"; 
    $sql .= "CONCAT( antanst ) Antanst ,"; 
    $sql .= "CONCAT( anstallda_text ) 'Anställda text' ,";
    $sql .= "CONCAT( omsattning_text ) 'Omsättning text' ,"; 
    $sql .= "CONCAT( omsattning ) 'Omsättning i tkr' ,"; 
    $sql .= "CONCAT( kommun ) Kommun ,";
    $sql .= "CONCAT( lan ) Län ,";
    $sql .= "CONCAT( datum ) Datum ,"; 
    $sql .= "CONCAT( p1429_01 ) '$fragekod1429_01' ,";
    $sql .= "CONCAT( p1429_02 ) '$fragekod1429_02' ,"; 
    $sql .= "CONCAT( p1429_03 ) '$fragekod1429_03' ,";
    $sql .= "CONCAT( p1429_04 ) '$fragekod1429_04' ,";
    $sql .= "CONCAT( p1429_05 ) '$fragekod1429_05' ,";
    $sql .= "CONCAT( p1429_06 ) '$fragekod1429_06' ";
    $sql .= " FROM survey WHERE ";
    $sql .= " $sfraga1429 like '$utslagsfraga1429' ORDER BY datum DESC ";
    
    //execute query
    $result = @mysql_query($sql)
    	or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());
    
    //if this parameter is included ($w=1), file returned will be in word format ('.doc')
    //if parameter is not included, file returned will be in excel format ('.xls')
    if (isset($w) && ($w==1))
    {
    	$file_type = "msword";
    	$file_ending = "doc";
    }else {
    	$file_type = "vnd.ms-excel";
    	$file_ending = "xls";
    }
    //header info for browser: determines file type ('.doc' or '.xls')
    header("Content-Type: application/$file_type");
    header("Content-Disposition: attachment; filename=" . date('Ymd') . ".system.$file_ending");
    header("Pragma: no-cache");
    header("Expires: 0");
    
    /*	Start of Formatting for Word or Excel	*/
    
    if (isset($w) && ($w==1)) //check for $w again
    {
    	//define separator (defines columns in excel & tabs in word)
    	$sep = "\n"; //new line character
    
    	while($row = mysql_fetch_row($result))
    	{
    		//set_time_limit(60); 
    		$schema_insert = "";
    		for($j=0; $j<mysql_num_fields($result);$j++)
    		{
    		//define field names
    		$field_name = mysql_field_name($result,$j);
    		//will show name of fields
    		$schema_insert .= "$field_name:\t";
    			if(!isset($row[$j])) {
    				$schema_insert .= "NULL".$sep;
    				}
    			elseif ($row[$j] != "") {
    				$schema_insert .= "$row[$j]".$sep;
    				}
    			else {
    				$schema_insert .= "".$sep;
    				}
    		}
    		$schema_insert = str_replace($sep."$", "", $schema_insert);
    		$schema_insert .= "\t";
    		print(trim($schema_insert));
    		//end of each mysql row
    		//creates line to separate data from each MySQL table row
    		print "\n----------------------------------------------------\n";
    	}
    }else{
    	//define separator (defines columns in excel & tabs in word)
    	$sep = "\t"; //tabbed character
    
    	//start of printing column names as names of MySQL fields
    	for ($i = 0; $i < mysql_num_fields($result); $i++)
    	{
    		echo mysql_field_name($result,$i) . "\t";
    	}
    	print("\n");
    	//end of printing column names
    
    	//start while loop to get data
    	while($row = mysql_fetch_row($result))
    	{
    		//set_time_limit(60); 
    		$schema_insert = "";
    		for($j=0; $j<mysql_num_fields($result);$j++)
    		{
    			if(!isset($row[$j]))
    				$schema_insert .= "NULL".$sep;
    			elseif ($row[$j] != "")
    				$schema_insert .= "$row[$j]".$sep;
    			else
    				$schema_insert .= "".$sep;
    		}
    		$schema_insert = str_replace($sep."$", "", $schema_insert);
    		//this corrects output in excel when table fields contain \n or \r
    		//these two characters are now replaced with a space
    		$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
    		$schema_insert .= "\t";
    		print(trim($schema_insert));
    		print "\n";
    	}
    }
    break;


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •