...

View Full Version : Help with sql query



El_Heso
09-20-2011, 12:36 AM
Hi!
Is it possible to make this happen?



$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:


$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

mlseim
09-20-2011, 01:01 AM
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.



.

El_Heso
09-20-2011, 01:23 AM
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 :-)


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.



.

mlseim
09-20-2011, 03:26 AM
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?



.

El_Heso
09-20-2011, 04:59 PM
You got it :-)
admin selects for someone else from user site they pull the trigger


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?



.

El_Heso
09-20-2011, 05:00 PM
can you show a example for a checkbox how to write the value fields so they can be transmitted to the query?


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?



.

mlseim
09-20-2011, 08:21 PM
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

// 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>";

?>



.

El_Heso
09-20-2011, 10:01 PM
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?


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




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

// 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>";

?>



.

mlseim
09-20-2011, 10:41 PM
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.


.

El_Heso
09-21-2011, 12:43 AM
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



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;

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.


.

El_Heso
09-21-2011, 01:03 AM
i fixed it with this


$sql="SELECT DISTINCT ";



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



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;



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum