Anyway round this *SOLVED*

[cyprus]

The code below I think builds an array of rows. It goes on to be used in a dropdown list.

The data of Orderdate comes from a select query to a table. However the table column Orderdate is one of many other columns in the table. It, like other columns gets selected by the user as to what he wants to appear in the list.

2 questions - (1) how do I programatically add in other column names into the line of code below. (2) How do I omit the column Orderdate if its not one of the columns selected by the user?


$_rows[] = array("Orderdate"=>mysql_result($result,$i,"Orderdate"), "Value"=>$i);


Many thanks

[markbett]

http://www.w3schools.com/php/php_arrays.asp

http://www.tizag.com/phpT/arrays.php

first link is probably better....

if you dont want orderdate to appear throw an if in front of it so if"orderdate=="1" then{
run code
}

[cyprus]

Thanks, however how can I break up the line below as I have to include or exclude column names. Thanks

$_rows[] = array("Orderdate"=>mysql_result($result,$i,"Orderdate"), "Ordernumber"=>mysql_result($result,$i,"Ordernumber"), "Value"=>$i);

[markbett]

you have arrays within arrays (multi dimen.)  make an if statement to determine which ones get included then append them into the outter array accordingly...

[Barand]

The way to include/exclude column names is in the original SELECT clause of your query

DON'T use SELECT * FROM tablename (unless you really do need all the columns)

ALWAYS specify the columns you want eg
[code]<?php
$sql = "SELECT colname1, colname5 FROM tablename" ;
$res = mysql_query ($sql);
while ($row = mysql_fetch_assoc($res)) {
    $_rows[] = $row;
}
?>
[/code]

[cyprus]

Many thanks. I think its best for me to explain a bit more. I have a page/form which contains checkboxes allowing a user to select columns from a table which will find there way into a dropdown list. So the select query will not contain columns that are not wanted. However, when it gets to the stage of filling the list, which is designed/coded to give column spacing, the arrays error if some table column data does not get to them. What I am trying to do is modify my dropdown list code to be dynamically built before the data flows to it. Hope I am making sense.

One of the lines of code in the listbox creation is:

$_rows[] = array("Orderdate"=>mysql_result($result,$i,"Orderdate"), "Ordernumber"=>mysql_result($result,$i,"Ordernumber"), "Value"=>$i);

This so far is hard coded, but if a user does not select the Ordernumber column to be in the list, the above codeline is unhappy. Hope this makes it easier to understand where my problems are. Thanks again

[cyprus]

Thought it might help to post the code being used for the listbox. As you can see I have had to rem out parts of it to run without error when certain column information was not passed to it.

echo '</select name>';

  for ($i=0; $i<=$numrows-1; $i++):
  //print $numrows;
  $_rows[] = array("Orderdate"=>mysql_result($result,$i,"Orderdate"), "Ordernumber"=>mysql_result($result,$i,"Ordernumber"), "Value"=>$i);
  endfor;

  foreach ($_rows as $row):
  if (strlen($row['Ordernumber']) > $max['Ordernumber']) {$max['Ordernumber'] = strlen($row['Ordernumber']);}
 
  //if (strlen($row['Orderdate']) > $max['Orderdate']) {$max['Orderdate'] = strlen($row['Orderdate']);}
  //if (strlen($row['Caption']) > $max['Caption']) {$max['Caption'] = strlen($row['Caption']);}
  //if (strlen($row['Description']) > $max['Description']) {$max['Description'] = strlen($row['Description']);}
  $rows[] = $row;
  endforeach;

$contents = "<select style=\"font-family:monospace;\" name=\"selectbox\" multiple size=\"$numrows\">\r\n";
foreach ($rows as $row):
$contents .= "<option value=\"{$row['Value']}\">";

$contents .= sprintf("%-'#".($max['Ordernumber'] + 2)."s", $row['Ordernumber']);
//$contents .= sprintf("%-'#". ($max['Orderdate'] + 2)."s", $row['Orderdate']);
//$contents .= sprintf("%-'#". ($max['Caption'] + 4)."s", $row['Caption']);
//$contents .= sprintf("%-'#". ($max['Description'] + 2)."s", $row['Description']);

$contents .= "</option>\r\n";
endforeach;
$contents .= "</select>";
$contents = str_replace("#", "&nbsp;", $contents);
?>

[Barand]

If you use my code the $_rows array is populated dynamically depending on the selected columns

[cyprus]

Thanks Barand, hope this makes it a bit clearer. The select query is user selected in what columns data is required. Its done before it reaches the code thats giving me grief.

The form/page is very basic at present, just getting the basics together. It.s a form that submits back to itself only to display a dropdown list of users selection of what table columns are displayed, and what product groups of 6 are held in the table. Its basically to display past customer orders. One day I will learn how to use the code display methods of this forum. 


<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Date</title>
</head>
View Archive Orders
<body bgcolor="#C0C0C0">

[color=blue]THIS BIT LOOKS AT WHAT GETS POSTED BACK AFTER SUBMISSION FROM THE USER CHECKBOXES BEING CHECKED. FROM THESE VALUES THE SELECT STATEMENT IS CONSTRUCTED (IE THE SELECT XXXXX AND THE WHERE XXXX)[/color]
<?
    if (isset($_POST['submit'])) {
    //$fields = 'op1'; // default
    $where = '';
    if (count($_POST['showfield']))
    $fields = join(',', $_POST['showfield']);
   
if (count($_POST['product'])) {
    $prodlist = join("','", $_POST['product']);
    $where = "WHERE GGroup IN ('$prodlist')";
    }
--------------------------------------------------

[color=blue]THIS WAS PUT HERE TO CHECK THE QUERY SELECT STATEMENT LOOKED CORRECT.[/color]
    //$sql = "SELECT $fields FROM ORDERS $where";
    //echo "<p>$sql</p>";
}

--------------------------------------------------

[color=blue]THIS IS JUST FOR THE START OF OPENING THE DB CONNECTION [/color]

$username="root";
// $password="password";
$database="";
mysql_connect(localhost,$username);


--------------------------------------------------

[color=blue]THIS PART IS TO BYPASS OPENING THE DATABASE WITH THE QUERY WHEN THE PAGE IS FIRST OPENED, WITH NO BUILT UP SELECT QUERY[/color]
if($fields == "" || $where == "")
{
// Show the page when nothing is defined for $fields and $where
}
else
{

--------------------------------------------------

[color=blue]RUN THE QUERY WITH DB[/color]

$query = "SELECT $fields FROM ORDERS $where";
echo "<p>$query</p>";
@mysql_select_db($database) or die( "Unable to select database");   
$result = mysql_query($query) or die('Problem with query: ' . $query . '
'. mysql_error());
$numrows=mysql_numrows($result);
}

--------------------------------------------------

?>

[color=blue]THIS PART PUTS CHECKBOXES ON HTML PAGE, PLUS TEXT, AND REINSTATES THE STATUS OF THE CHECKBOXES (IE KEEPS THEM CHECKED OR UNCHECKED AFTER SUBMITTING) [/color]

<FORM method='post'>
Include Data<br>

<?
    $showfields = array (
        Orderdate => 'Order Date',
        Ordernumber => 'Order Number',
        Description => 'Caption',
        Duration => 'Duration',
    );
    foreach ($showfields as $id =>$prod) {
        if ($_POST['showfield']) {
            // was value of id in those posted?
            $chk = in_array($id, $_POST['showfield']) ? 'checked' : '';
        }
        else $chk = '';
        echo "<input type='checkbox' name='showfield[]' value='$id' $chk>$prod<br>";
       
    }

?>

<br>

Include Products<br>
<?
    $products = array (
        1 => 'Digital Betacam',
        2 => 'Betacam SP',
        3 => 'DVCPro',
        4 => 'HDCAM',
        5 => 'Mini DV'
    );
    foreach ($products as $id =>$prod) {
        if ($_POST['product']) {
            // was value of id in those posted?
            $chk = in_array($id, $_POST['product']) ? 'checked' : '';
        }
        else $chk = '';
        echo "<input type='checkbox' name='product[]' value='$id' $chk>$prod<br>";
       
    }

--------------------------------------------------

[color=blue]THIS PART CREATES THE DROPDOWN LIST. EARLY PART OF THE CODE PREVENTS LIST BEING DONE IF THERE IS AN INCOMPLETE SELECT QUERY, DATABASE HAS BEEN BYPASSED, NO DATA YET FOR LIST (IE PAGE FIRST LOAD)[/color]
if($fields == "" || $where == "")
{
// nothing defined for $fields and $where
}
else
{
echo '</select name>';

  for ($i=0; $i<=$numrows-1; $i++):
  [color=red]$_rows[] = array("Orderdate"=>mysql_result($result,$i,"Orderdate"), "Ordernumber"=>mysql_result($result,$i,"Ordernumber"), "Value"=>$i);[/color]
  endfor;

  foreach ($_rows as $row):
[color=red]  if (strlen($row['Ordernumber']) > $max['Ordernumber']) {$max['Ordernumber'] = strlen($row['Ordernumber']);}[/color]
 
[color=red]  //if (strlen($row['Orderdate']) > $max['Orderdate']) {$max['Orderdate'] = strlen($row['Orderdate']);}
  //if (strlen($row['Caption']) > $max['Caption']) {$max['Caption'] = strlen($row['Caption']);}
  //if (strlen($row['Description']) > $max['Description']) {$max['Description'] = strlen($row['Description']);}[/color]  $rows[] = $row;
  endforeach;

$contents = "<select style=\"font-family:monospace;\" name=\"selectbox\" multiple size=\"$numrows\">\r\n";
foreach ($rows as $row):
$contents .= "<option value=\"{$row['Value']}\">";

[color=red]$contents .= sprintf("%-'#".($max['Ordernumber'] + 2)."s", $row['Ordernumber']);
//$contents .= sprintf("%-'#". ($max['Orderdate'] + 2)."s", $row['Orderdate']);
//$contents .= sprintf("%-'#". ($max['Caption'] + 4)."s", $row['Caption']);
//$contents .= sprintf("%-'#". ($max['Description'] + 2)."s", $row['Description']);[/color]

[color=blue]THE STATEMENTS/CODE ABOVE ALL WORKS IF ORDERNUMBER/ORDERDATE/CAPTION/DESCRIPTION WERE SELECTED BY THE USER. HOWEVER IF THEY WERE NOT, THEN UNDERSTANDABLY THE CODE ERRORS[/color].


$contents .= "</option>\r\n";
endforeach;
$contents .= "</select>";
$contents = str_replace("#", "&nbsp;", $contents);
?>
<p>&nbsp;</p>
<?
echo "<div>Something Here</br>$contents</div>";

}
?>


<input type="submit" name="submit" value="Submit">
</FORM>


I don't know how ligible this will all be until I hit the button, only hope it makes it a bit clearer, thanks.

[Barand]

I still do not understand what should go in the dropdown.

is it

a ) data actually selected by the user

b ) order number and date even if they was not one of the selected fields

Perhaps if you describe what you are trying to do in the application. Posting only code that doesn't do what you want is confusing.

For example, once the user has selected the search criteria and fields to be displayed, are you then trying to give them a means of selecting one of the returned records so you can then display further details of the item?

[cyprus]

Thanks Barand. The form/page has checkboxes on that allow certain COLUMNS of a complete order table (at present everything thats in the database) As well as this they have checkboxes which allow for the list to contain 1-6 product groups of all records in the table (this goes into a where statement)

So basically the form allows a dynamic contruction of a select query which then gets relevant data. The problem arises when it gets to the dropdown list, which has non dynamically constructed lines of code. So if something gets ommitted from what it's expecting then it errors.

The page/form gets submitted back to itself, so the first pass there is no data for the dropdown to show anything. The dropdown code provides column spacing so the list looks uniform.

Once I can get passed this problem, I can look at providing the users a chance to select all orders between dates, but until then I am stuck.

Does that help? Thanks

[Barand]

I still do not understand what should go in the dropdown.

is it

a ) data actually selected by the user?

b ) order number and date even if they was not one of the selected fields?

and what do you want to happen when an item is selected from the dropdown?

[cyprus]

1. The dropdown list is filled with all rows of a table
2. The dropdown list shows all the user (checkbox panel selected) columns that he wants.

OrderDate  OrderNumber  Description Quantity UnitPrice etc.

3. The dropdown list can show only rows from certain product groups is so wished, again from checkbox selection (selecting 1-6 groups)

The list will show nothing on first page/form load, until checkboxes are checked and the form re submitted back to itself.

4. At this stage I need to do nothing on clicking a list item, its just for viewing.

Thanks for being patient, I know it's a bit of an odd one. Thanks

[cyprus]

I know this does not work, as I do not know the correct syntax, but hopefully it will explain what I am trying to do.

My problem line is here when either Orderdate or Ordernumber has not been selected by the user to in the select query (in other words he does not want it included in his dropdown list.

[code]$_rows[] = array("Orderdate"=>mysql_result($result,$i,"Orderdate"), "Ordernumber"=>mysql_result($result,$i,"Ordernumber"), "Value"=>$i);[/code]
 
How can I split this up to be in the position of checking each section before finally putting it all into the $_rows[] array?

[code]$tryA=array("Orderdate"=>mysql_result($result,$i,"Orderdate");
  $tryB=array("Ordernumber"=>mysql_result  ($result,$i,"Ordernumber"),"Value"=>$i);

  $_rows[] = array($tryA & "," & $tryB);[/code] 

Just wish this was visual basic, could have walked away from it weeks ago. Thanks again
 

[Barand]

I've just been experimenting with sprintf() formatted lines in a monospace-font select dropdown. Unfortunately the spacing is not preserved as it removes all but a single space between each field value. Just as it would in the html body without pre /pre tags.

[Barand]

Try this
[code]
<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Date</title>
</head>
View Archive Orders
<body bgcolor="#C0C0C0">

<?php
    if (isset($_POST['submit'])) {
    //$fields = 'op1'; // default
    $where = '';
    if (count($_POST['showfield']))
    $fields = join(',', $_POST['showfield']);
   
if (count($_POST['product'])) {
    $prodlist = join("','", $_POST['product']);
    $where = "WHERE GGroup IN ('$prodlist')";
    }
    //$sql = "SELECT $fields FROM ORDERS $where";
    //echo "<p>$sql</p>";
}


//$username="root";
// $password="password";
$database="test2";                    // SUBSTITUTE YOUR DB NAME  ***********
mysql_connect(localhost);
mysql_select_db($database);

/*
THIS PART IS TO BYPASS OPENING THE DATABASE WITH THE QUERY WHEN THE PAGE IS FIRST OPENED, WITH NO BUILT UP SELECT QUERY
*/
if($fields == "" || $where == "")
{
    // Show the page when nothing is defined for $fields and $where
}
else
{

    //RUN THE QUERY WITH DB

    $query = "SELECT Ordernumber as id, $fields FROM ORDERS $where";
    echo "<p>$query</p>";
    $result = mysql_query($query) or die('Problem with query: ' . $query . '
    '. mysql_error());
    $numrows=mysql_num_rows($result);

    //  store returned data
    $data_rows = array();
    while ($row = mysql_fetch_assoc($result)) {
        $data_rows[] = $row;
    }
}


?>
<!--
THIS PART PUTS CHECKBOXES ON HTML PAGE, PLUS TEXT, AND REINSTATES THE STATUS OF THE CHECKBOXES (IE KEEPS THEM CHECKED OR UNCHECKED AFTER SUBMITTING)
-->
<FORM method='post'>
Include Data <BR>
<?php
    $showfields = array (
        Orderdate => 'Order Date',
        Ordernumber => 'Order Number',
        Description => 'Caption',
        Duration => 'Duration',
    );
    foreach ($showfields as $cname =>$alias) {
        if ($_POST['showfield']) {
            // was value of id in those posted?
            $chk = in_array($cname, $_POST['showfield']) ? 'checked' : '';
        }
        else $chk = '';
        echo "<input type='checkbox' name='showfield[]' value='$cname' $chk>$alias
<br>";
     
    }

?>



<BR>
Include Products<BR>

<?php
    $products = array (
        1 => 'Digital Betacam',
        2 => 'Betacam SP',
        3 => 'DVCPro',
        4 => 'HDCAM',
        5 => 'Mini DV'
    );
    foreach ($products as $id =>$prod) {
        if ($_POST['product']) {
            // was value of id in those posted?
            $chk = in_array($id, $_POST['product']) ? 'checked' : '';
        }
        else $chk = '';
        echo "<input type='checkbox' name='product[]' value='$id' $chk>$prod
<br>";
     
    }
/*
--------------------------------------------------

THIS PART CREATES THE DROPDOWN LIST. EARLY PART OF THE CODE PREVENTS LIST BEING DONE IF THERE IS AN INCOMPLETE SELECT QUERY, DATABASE HAS BEEN BYPASSED, NO DATA YET FOR LIST (IE PAGE FIRST LOAD)
*/
if($fields == "" || $where == "")
{
// nothing defined for $fields and $where
}
else
{
    // get data lengths
    $maxlen=array();
    foreach ($data_rows as $row)
        foreach($row as $cname=>$val)
            $maxlen[$cname] = max($maxlen[$cname], strlen($val));
   

    $contents = "<select style=\"font-family:monospace;\" name=\"selectbox\" multiple size=\"$numrows\">\r\n";

    foreach ($data_rows as $row) {
        $id = $row['id'];
        unset ($row['id']);
        $contents .= "<option value='$id'>";
        $line = '';
        foreach($row as $cname=>$val) {
            $line .= str_pad($val, $maxlen[$cname]+2, chr(160));  //pad with non breaking spaces
        }
        $contents .= $line;
        $contents .= "</option>\r\n";
    }
    $contents .= "</select>";
    ?>
    <p>&nbsp;</p>
    <?php
    echo "<div>Something Here</br>$contents</div>";

}
?>


<BR><input type="submit" name="submit" value="Submit">
</FORM>
[/code]

[cyprus]

Many thanks for trying, very much appreciated.

[Barand]

Was that how you wanted it?

[cyprus]

Sorry, my posting saying thanks very much for trying went out just as you must have been putting your posting up. Absolutely brilliant, works just as I wanted it to!!! I had been away for a while and only just been putting it in. I am so greatful to you, I have spent days/hours trying to get it done so I could move on. You have made my weekend, hope yours is going well, many, many thanks again.

[cyprus]

Slight problem - any ideas. I added a few more column selections for the user, and for some reason the columns start wandering out of line in columns 5,6,7. They follow a column 4 which has varying lengths, despite the fact that column 3 is okay following column 2 which has varying lengths. I tried increasing $maxlen[$cname]+2 to +3 but that just moved things along. Any suggestions, thanks

[Barand]

I get this with my test data
[pre]
2006-01-22  1  Widget    25  aaaa          bbbbbb        cccc      dddddddd
2006-02-01  2  Gizmo      15  aaaaaaaaaaa    bb            ccccccccc  dddd 
2006-03-01  3  Wotsit    10  aaaaaaaaaaaaa  bbbbbbbbbbbbb  cccc      dd   
2006-04-01  4  Something  50  aa            bbbb          ccc        d     
[/pre]

[Barand]

It looks like some of your fields may have extra spaces at the end. cjange the bit that gets the max field lengths to

[code]
    // get data lengths
    $maxlen=array();
    foreach ($data_rows as $k=>$row)
        foreach($row as $cname=>$val) {
            $data_rows[$k][$cname] = trim($val);
            $maxlen[$cname] = max($maxlen[$cname], strlen(trim($val)));
        }
[/code]

[cyprus]

Many thanks. I was thinking there may be an unwanted space somewhere, and whether there was a trim function. I tried your code and it made no difference, however I noticed it to be on one product item where it happens. I went into the database, and found the answer. I had tried adding spaces to the data in the field when I was trying to get columns in line with an ordinary dropdown list code. I removed the bodge that never worked, and hey ho all is well. Everything lines up perfectly. Many thanks again following me through on this nightmare. Best regards

[cyprus]

Just a quick one while this post is still fresh. There are 2 column values, Price and Quantity. Is there an easy way to add to the database data being retrieved for the list, the addition of "£" before the price value/data, and "off" after the quantity values. At the moment all prices look like eg 6.00 and the quanties eg 30.

If its a lot of work then I will give it a miss as you have done enough already. Thanks

[Barand]

Change that same bit of code again to this
[code]
    // get data lengths
    $maxlen=array();
    foreach ($data_rows as $k=>$row)
        foreach($row as $cname=>$val) {
            $val = trim($val);
            switch ($cname) {
                case 'Price':      $val = '£' . number_format($val,2); break;
                case 'Quantity':    $val = number_format($val, 0) . ' off'; break;
            }
            $data_rows[$k][$cname] = $val;
            $maxlen[$cname] = max($maxlen[$cname], strlen($val));
        }
[/code]

This will (I hope) give
Price = £1,234.56
Qty = 1,000 off